Dependent objects such as LOB segments cannot be included in the analysis. For example, if you display a list of tables on the Tables page in the Administration section of Enterprise Manager Database Control , you can select a table and then select the Run Segment Advisor command from the Actions menu.
This method enables you to include the schema object's dependent objects in the Segment Advisor run. For example, if you select a table and select the Run Segment Advisor command, Enterprise Manager displays the table's dependent objects, such as partitions, index segments, LOB segments, and so on.
You can then select dependent objects to include in the run. You can schedule the job to run immediately, or can take advantage of advanced scheduling features offered by the Scheduler.
Follow the wizard steps to schedule the Segment Advisor job, and then click Submit on the final wizard page. The Advisor Central page reappears, with the new Segment Advisor job at the top of the list under the Results heading.
If you don't see your job, use the search fields above the list to display it. Check the status of the job. Do not use your browser's Refresh icon. You use package procedures to create a Segment Advisor task, set task arguments, and then execute the task. Table shows the procedures that are relevant for the Segment Advisor.
Use this procedure to create the Segment Advisor task. Use this procedure to identify the target object for segment space advice. The parameter values of this procedure depend upon the object type. Table lists the parameter values for each type of object. Use this procedure to describe the segment advice that you need. Table shows the relevant input parameters of this procedure. Parameters not listed here are not used by the Segment Advisor.
TRUE : Findings are generated on all segments specified, whether or not space reclamation is recommended. FALSE : Findings are generated only for those objects that generate recommendations for space reclamation. If the table is not partitioned, the table segment is analyzed without any dependent segments like index or LOB segments. If the table is partitioned, the Segment Advisor analyzes all table partitions and generates separate findings and recommendations for each.
The Segment Advisor creates several types of results: recommendations, findings, actions, and objects. You can view results in the following ways:. If a segment would benefit from a segment shrink or reorganization, the Segment Advisor generates a recommendation for the segment.
Table shows examples of generated findings and recommendations. Findings are a report of what the Segment Advisor observed in analyzed segments.
Findings include space used and free space statistics for each analyzed segment. Not all findings result in a recommendation. There may be only a few recommendations, but there could be many findings.
For row chaining advice, the Automatic Segment Advisor generates findings only, and not recommendations. If the Automatic Segment Advisor has no space reclamation recommendations to make, it does not generate findings. Every recommendation is associated with a suggested action to perform: either segment shrink or online redefinition reorganization. All findings, recommendations, and actions are associated with an object. Table defines the columns in this view to query for information on the analyzed segments.
You can correlate the objects in this view with the objects in the findings, recommendations, and actions views. You can view the following types of results:. You can also view a list of the segments that were analyzed by the last Automatic Segment Advisor run.
On the database Home page, under the Space Summary heading, click the numeric link next to the title Segment Advisor Recommendations. If any recommendations are present, click in the Select column to select a tablespace, and then click Recommendation Details. The Recommendation Details page appears. You can initiate the recommended activity from this page shrink or reorganize.
See Figure Otherwise, to get to this page, on the database Home page, under Related Links, click Advisor Central. Check that your task appears in the list under the Results heading. If it does not, complete these steps See Figure :. Do not use your browser's refresh icon. Select a tablespace in the list, and then click Recommendation Details. The Chained Row Analysis page appears, showing all segments that have chained rows, with a chained rows percentage for each.
To view the list of segments that were analyzed by the last Automatic Segment Advisor run:. Use the search fields above the list to limit the segments displayed. Refer to Oracle Database Reference for a description of these views.
The table contents summarize the possible outcomes. The command to execute. Enable row movement of the table schema. Perform re-org on the object object , estimated savings is xxx bytes. Note: This finding is for objects with reclaimable space that are not eligible for online segment shrink.
The following query returns recommendations by the most recent run of the Auto Segment Advisor, with the suggested command to run to follow the recommendations:. The Automatic Segment Advisor is run by a Scheduler job. The following are examples of modifications that you can make:.
The Edit Job page appears. This is the generic Scheduler page that enables you to modify job attributes. Modify job attributes as needed, including enabling or disabling the job. Click the Help link at the top of the page for information on the Scheduler and on modifying job attributes. Modify the job schedule, job resource consumption, or other job attributes using the generic Scheduler pages in Enterprise Manager. To adjust the job schedule, modify the window group SYS.
The following views display information specific to the Automatic Segment Advisor. For details, see Oracle Database Reference. You use online segment shrink to reclaim fragmented free space below the high water mark in an Oracle Database segment.
The benefits of segment shrink are these:. Compaction of data leads to better cache utilization, which in turn leads to better online transaction processing OLTP performance. The compacted data requires fewer blocks to be scanned in full table scans, which in turns leads to better decision support system DSS performance.
Segment shrink is an online, in-place operation. DML operations and queries can be issued during the data movement phase of segment shrink. Concurrent DML operation are blocked for a short time at the end of the shrink operation, when the space is deallocated. Indexes are maintained during the shrink operation and remain usable after the operation is complete.
Segment shrink does not require extra disk space to be allocated. Segment shrink reclaims unused space both above and below the high water mark. In contrast, space deallocation reclaims unused space only above the high water mark. In shrink operations, by default, the database compacts the segment, adjusts the high water mark, and releases the reclaimed space. Segment shrink requires that rows be moved to new locations. Therefore, you must first enable row movement in the object you want to shrink and disable any rowid-based triggers defined on the object.
Shrink operations can be performed only on segments in locally managed tablespaces with automatic segment space management ASSM. Within an ASSM tablespace, all segment types are eligible for online segment shrink except these:.
Before invoking online segment shrink, view the findings and recommendations of the Segment Advisor. For more information, see "Using the Segment Advisor". The remainder of this section discusses the command line method. You can shrink space in a table, index-organized table, index, partition, subpartition, materialized view, or materialized view log.
Refer to Oracle Database SQL Reference for the syntax and additional information on shrinking a database object, including restrictions.
When you specify COMPACT , Oracle Database defragments the segment space and compacts the table rows but postpones the resetting of the high water mark and the deallocation of the space until a future time. This option is useful if you have long-running queries that might span the operation and attempt to read from blocks that have been reclaimed.
The defragmentation and compaction results are saved to disk, so the data movement does not have to be redone during the second phase. When you deallocate unused space, the database frees the unused space at the unused high water mark end of the database segment and makes the space available for other segments in the tablespace.
The following statements deallocate unused space in a segment table, index or cluster :. The KEEP clause is optional and lets you specify the amount of space retained in the segment.
When creating tables and other data structures, you need to know how much space they will require. Each datatype has different space requirements. Views and packages that are unique to a particular schema object are described in the chapter of this book associated with that object. This section describes views and packages that are generic in nature and apply to multiple schema objects. The following query returns the name and size of each index segment in schema hr :.
For example, the following query identifies the extents allocated to each index segment in the hr schema and the size of each of those extents:. For example, the following query reveals the amount of free space available as free extents in the SMUNDO tablespace:.
The segment has the maximum number of extents allowed by the data block size, which is operating system specific. The following query returns the names, owners, and tablespaces of all segments that satisfy any of these criteria:. Once you have identified a segment that cannot allocate additional extents, you can solve the problem in either of two ways, depending on its cause:.
Drop and re-create the segment, giving it a larger INITIAL storage parameter setting so that it does not need to allocate so many extents. This section discusses those procedures and contains the following sections:. The size of a database table can vary greatly depending on tablespace storage attributes, tablespace block size, and many other factors. The procedure has two variants. The first variant uses average row size to estimate size. The second variant uses column information to estimate table size.
Both variants require as input the following values:. The table specified in this DDL statement must be an existing table. The default is NULL. The results returned by this procedure depend on statistics gathered on the segment.
Therefore, be sure to obtain statistics shortly before executing this procedure. In the absence of recent statistics, the procedure does not issue an error, but it may return inappropriate results.
The procedure returns the following values:. The procedure retrieves the space use totals from the Automatic Workload Repository or computes current space use and combines it with historic space use changes retrieved from Automatic Workload Repository.
Specify NULL otherwise. The default is " NOW ". This setting is useful when the result table will be displayed as a table rather than a chart, because you can see more clearly how the actual recording interval relates to the requested reporting interval. The procedure returns a table, each of row of which provides space use information on the object for one interval. If the return table is very large, the results are pipelined so that another application can consume the information as it is being produced.
The output table has the following columns:. Records are not produced for values of TIME that precede the oldest recorded statistics for the object. This information is useful because there is no guaranteed reporting interval for object size use statistics, and the actual reporting interval varies over time and from object to object. Current in-memory statistics can be collected across all instances in a cluster and treated as the "recorded" value for the present time.
In a Real Application Clusters environment, the rules for recording statistics allow each instance to choose independently which objects will be selected. The output returned by this procedure is an aggregation of values recorded across all instances in a RAC environment. Skip Headers. There are two ways to specify alert thresholds for both locally managed and dictionary managed tablespaces: By percent full For both warning and critical thresholds, when space used becomes greater than or equal to a percent of total space, an alert is issued.
By free space remaining in kilobytes KB For both warning and critical thresholds, when remaining space falls below an amount in KB, an alert is issued. New tablespaces are assigned alert thresholds as follows: Locally managed tablespace —When you create a new locally managed tablespace, it is assigned the default threshold values defined for the database. Note: In a database that is upgraded from version 9.
This setting effectively disables the alert mechanism to avoid excessive alerts in a newly migrated database. All legitimate Oracle experts publish their Oracle qualifications. Oracle technology is changing and we strive to update our BC Oracle support information.
If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page. All rights reserved by Burleson. Environment: Only one database per instance and one schema per application. DB2 V8 8. For Good this month. If you would have any old stats that might also give a deviation. No Account? Sign up. By signing in, you agree to our Terms of Use and Privacy Policy. Already have an account? Sign in. By signing up, you agree to our Terms of Use and Privacy Policy.
0コメント