13.2. Using Segment Management Tools
Oracle 10g provides a number of new ways to manage segments in the database. To use disk space more efficiently and to reduce the I/O required to access a segment, segment shrink functionality compacts the space within a segment and optionally moves the high watermark (HWM), freeing up space for other segments.
The Segment Advisor, one of many advisors in Oracle 10g, can analyze one segment or all the segments within a tablespace and determine if a segment is a good candidate for a segment shrink operation. In addition, the Segment Advisor can help you estimate the initial size of a table, as well as predicting growth patterns given your estimates for table inserts, updates, and deletes over time.
Finally, Oracle 10g provides a way to both monitor index space usage as well as to help you identify whether an index is used at all. We will also present the pros and cons of rebuilding the index versus coalescing the index.
13.2.1. Segment Shrink
If rows were added only to tables, then segment shrink would not be needed. However, deletes and updates to a table—and ultimately the index—leave many blocks with fewer or no rows. While this space can be used by future inserts or updates, you have no guarantee that the space will be reused, if ever. In addition, because the HWM only stays the same or gets larger, full table scans must read every block, whether or not it is empty.
The following sections discuss the benefits of segment shrink and cover a few of the restrictions regarding the types of segments you can shrink and where the segments must reside. Finally, you will see some practical examples of how segment shrink works, both using the command line and EM Database Control.
13.2.1.1. An Overview of Segment Shrink
Segment shrink compresses the data blocks in a table or index and optionally moves the HWM down, making the unused space available for other segments in the tablespace. In addition to making full table scans more efficient, a shrunken segment makes even single I/Os for individual data blocks more efficient, because more rows are retrieved for each I/O. Indexes that are shrunk are also more efficient for the same reason: During an index range scan operation, more index entries are read for each I/O, reducing overall I/O for the query.
There are other benefits to segment shrink. Some or all chained rows in a table may be eliminated by performing a segment shrink operation, however, it is not guaranteed that all chained rows will be repaired because not all blocks may be accessed in a segment shrink operation.
Figure 13.10 shows a sparsely populated table segment before and after a shrink operation.
Before Oracle 10g, the HWM could be moved down only if the segment was moved or truncated. While online table redefinition or Create Table As Select (CTAS) operations can provide similar results to segment shrink, those methods must temporarily provide double the amount of space occupied by the table. Segment shrink is online and in place, requiring a negligible amount of extra space and remaining available during the entire operation, except for a brief period when the HWM is moved.
Figure 13.10. Segment before and after shrink
13.2.1.2. Segment Shrink Restrictions and Considerations
Segment shrink operations have one major restriction: Segments managed with freelists cannot be shrunk; in other words, the tablespace containing the segment must be defined with automatic segment space management.
The most common types of segments can be shrunk, such as
Heap-organized and index-organized tables
Indexes
Partitions and subpartitions
Materialized views and materialized view logs
Other segment types or segment with specific characteristics cannot be shrunk, such as
Clustered tables
Tables with LONG columns
Tables with on-commit or ROWID-based materialized views
LOB segments
IOT mapping tables or overflow segments
Tables with function-based indexes
During a segment shrink operation, the ROWID may change for a row when it moves between blocks. Therefore, segments that rely on ROWIDs being constant, such as an application that maintains ROWIDs as pointers in another table, cannot be shrunk. In any case, ROW MOVEMENT must be enabled for table segments that are candidates for shrink operations.
All indexes are maintained and useable both during and after the shrink operation.
13.2.1.3. Performing Segment Shrink
To perform segment shrink, you can use either SQL commands or EM Database Control. If you have hundreds of segments to shrink, a series of batch jobs with SQL commands submitted overnight is most likely the best way to perform the operation. For only one or two shrink operations on an occasional basis, EM Database Control is probably the fastest and easiest to use.
13.2.1.3.1. SQL Commands and Segment Shrink
As mentioned previously, segment shrink operations may change the ROWID of one or more rows of a table segment. Therefore, row movement on the segment must be enabled before the segment can be shrunk. In the following example, you'll enable row movement for the HR.EMPLOYEES table:
SQL> alter table hr.employees enable row movement; Table altered.
NOTE
The ROW MOVEMENT capability appeared in Oracle 8i to allow rows to move between partitions of a partitioned table.
Shrinking the space in a segment is performed as an extension to the ALTER TABLE or ALTER INDEX command, with the SHRINK SPACE clause, as shown here:
SQL> alter table hr.employees shrink space; Table altered.
In this example, the table HR.EMPLOYEES is shrunk, and the HWM is moved in the same operation.
Although the table is available for use by all users while the shrink operation is in progress, the I/O throughput may be decreased. Therefore, it may be advantageous to split the operation into two commands using the COMPACT clause to compress the rows without moving the HWM, as shown here:
SQL> alter table hr.employees shrink space compact; Table altered.
At a later time, when the database is not as busy, you can complete the rest of the operation by omitting the COMPACT clause.
SQL> alter table hr.employees shrink space; Table altered.
Any fragmentation that has occurred in the mean time is addressed, and the HWM is moved. Whether the operation is performed all at once or in two steps, only a small number of rows are locked at any given time. Conversely, a user DML command may lock one or more rows and temporarily prevent segment shrink from completing compaction of the rows. When the HWM is moved, the entire table is locked for a brief amount of time.
Another potential benefit of splitting the operation into two parts is based on PL/SQL code that may have cursors open while the segment is being accessed. With the COMPACT option, all cursors defined on the segment remain valid; without the COMPACT option, all cursors on the segment are invalidated.
Another option available with the ALTER TABLE ... SHRINK SPACE command is the CASCADE keyword. When CASCADE is specified, all dependent objects, such as indexes, are also shrunk. In the following example, you'll use the CASCADE example to shrink all the indexes defined on the HR.EMPLOYEES table:
SQL> alter table hr.employees shrink space cascade; Table altered.
Without the CASCADE keyword, you would have to identify all the indexes defined on the table and execute a series of commands instead of just one command.
SQL> select index_name from dba_indexes where 2 table_name = 'EMPLOYEES' and owner = 'HR'; INDEX_NAME ------------------------------ EMP_EMAIL_UK EMP_EMP_ID_PK EMP_DEPARTMENT_IX EMP_JOB_IX EMP_MANAGER_IX EMP_NAME_IX 6 rows selected. SQL> alter index hr.emp_email_uk shrink space; Index altered. SQL> alter index hr.emp_emp_id_pk shrink space; Index altered. SQL> alter index hr.emp_department_ix shrink space; Index altered. SQL> alter index hr.emp_job_ix shrink space; Index altered. SQL> alter index hr.emp_manager_ix shrink space; Index altered. SQL> alter index hr.emp_name_ix shrink space; Index altered.
13.2.1.3.2. EM Database Control and Segment Shrink
Performing segment shrink with EM Database Control is even easier. From the Administration tab on the EM Database Control database Home tab, click the Tables link under the Schema heading. Search for the tables you want to shrink and select the Shrink Segment action, as shown in Figure 13.11.
The EM Database Control screen gives you all the options that are available on the command line, including the COMPACT and the CASCADE options (see Figure 13.12).
Figure 13.11. Selecting tables for segment shrink
Figure 13.12. The EM Database Control segment shrink options
Another benefit to using EM Database Control is that the segment shrink operation will be submitted as a job and run in the background, allowing you to immediately perform other tasks with EM Database Control.
13.2.2. Segment Advisor
Oracle's Segment Advisor provides several types of functionality to manage the space occupied by database segments such as tables and indexes. This functionality is available through both EM Database Control and PL/SQL procedures.
The Segment Advisor can provide advice regarding a particular table, schema, or tablespace that contains segments that are good candidates for shrink operations. In addition, using the data collected within the AWR, the Growth Trend Report can help predict how much space a segment will occupy based on previous growth patterns. Finally, Segment Resource Estimation can help make preliminary sizing estimates for a table given the column datatypes and the estimated number of rows in the table.
13.2.2.1. EM Database Control and Segment Advisor
As with nearly all Oracle Database 10g features, EM Database Control provides an intuitive graphical interface to make the most common segment analysis tasks easy to perform. In addition to the ability to perform a complete analysis on all segments within a tablespace, EM Database Control can use data in the AWR to use segment growth patterns to predict future space usage needs. Plus, EM Database Control provides a Segment Resource Estimation tool to help size a table's space usage needs even before it is created.
13.2.2.1.1. Segment Advisor
To use the Segment Advisor, select the Advisor Central link under any tab. Click Segment Advisor, which brings you to the Segment Advisor screen (see Figure 13.13).
Click Continue, which brings you to the Segment Advisor: Tablespaces screen, where you select the tablespaces to be analyzed (see Figure 13.14). In this example, the USERS tablespace is added to the list.
After clicking Next, you can specify how long to run the analysis on the Segment Advisor: Options screen (see Figure 13.15). Because the USERS tablespace is relatively small, you will not specify a time limit. But for much larger tablespaces, you may want to prevent I/O contention— even during a non-peak time interval—and settle for a limited analysis. In Figure 13.15, the results of the analysis are to be retained for 30 days.
Clicking Next opens the Segment Advisor: Schedule screen (see Figure 13.16). Here, you can set up the task name and the scheduling options; in this example, the job will run immediately.
Figure 13.13. Segment Advisor
Figure 13.14. Selecting tablespaces for the Segment Advisor
Figure 13.15. Segment Advisor options
Figure 13.16. Task scheduling options
In the last screen of the Segment Advisor—Segment Advisor: Review—you have one more chance to review the analysis options and to review the SQL commands that will be submitted to perform the analysis. Figure 13.17 shows the summary.
Figure 13.17. Segment Advisor task summary
Clicking the Show SQL button, you can review the anonymous PL/SQL procedures that will perform the tasks just configured:
DECLARE taskname varchar2(100); taskdesc varchar2(128); task_id number; object_id number; advMode varchar2(25); timeLimit varchar2(25); numDaysToRetain varchar2(25); objectName varchar2(100); objectType varchar2(100); BEGIN taskname := 'SHRINK_9926180';
taskdesc := 'Get shrink advice based on object growth trend';
advMode :='COMPREHENSIVE';
numDaysToRetain :='30';
dbms_advisor.create_task('Segment Advisor',?,
taskname,taskdesc,NULL);
dbms_advisor.create_object(taskname, 'TABLESPACE',
'USERS', ' ', ' ', NULL, object_id);
dbms_advisor.set_task_parameter(taskname,
'MODE', advMode);
dbms_advisor.set_task_parameter(taskname,
'RECOMMEND_ALL', 'TRUE'),
dbms_advisor.set_task_parameter(taskname,
'DAYS_TO_EXPIRE', numDaysToRetain);
END;
DECLARE
taskname varchar2(100);
BEGIN
taskname := 'SHRINK_9926180';
dbms_advisor.reset_task(taskname);
dbms_advisor.execute_task(taskname);
END;Clicking Submit submits the SQL commands to be run. A few moments later, click the Refresh button on the Advisor Central screen. In Figure 13.18, notice that the Segment Advisor task has completed.
Click the link containing the job name of the task you just ran—in this case, SHRINK_9926180—to see that there are no segments in the USERS tablespace that can benefit from a shrink operation early in the evening (see Figure 13.19).
Emphasizing the dynamic nature of space management in any database, you may run the analysis again and find that there is now a table that can benefit from a shrink operation: the HR.EMPLOYEES_HIST table. You can shrink the HR.EMPLOYEES_HIST table by selecting one of the recommendations at the bottom of the results screen for task name SHRINK_6871100 in Figure 13.20. These options are identical to those used in Figures 13.11 and 13.12, except that you can perform a shrink operation on more than one table at a time.
Figure 13.18. Advisor Central task completion
Figure 13.19. Segment Advisor recommendations at 7:21 P.M.
Figure 13.20. Segment Advisor recommendations at 11:12 P.M.
13.2.2.1.2. Growth Trend Report
The Growth Trend Report, based on the AWR data collected at 30-minute intervals or when space-related server-generated alerts are triggered, helps to predict future growth trends for selected segments. Given the predicted growth pattern, you know when space will need to be added to support segment growth.
To access the Growth Trend Report, start at the Administration tab and click the Tables link under the Schema heading. In Figure 13.21, you want to predict the growth of the HR.EMPLOYEES_HIST table.
Click the table name, select the Segments tab, and enter a future date to see when more space should be allocated to the segment. In this example, the user entered 5/16/04. Once you've clicked the Refresh button, the results of the analysis appear, as shown in the example in Figure 13.22.
This report was run on May 8, 2004, and although the overall usage is predicted to be relatively flat, the Segment Advisor has predicted that the amount of space allocated for the segment will rise dramatically within the next week.
As with the Segment Advisor, the Growth Trend Report is supported only for locally managed tablespaces.
Figure 13.21. Growth Trend Report segment selection
Figure 13.22. Growth Trend Report segment analysis
13.2.2.1.3. Segment Resource Estimation
The Segment Resource Estimation tool gives you a good estimate of how much disk space a new segment will require. While it is not directly a part of the Segment Advisor, it is a point-in-time analysis tool to give you sizing advice so you can estimate space usage for a new segment given the columns, datatypes, sizes, and PCTFREE for the segment.
To use Segment Resource Estimation, start at the Administration tab from the EM Database Control home page and click the Tables link. Instead of searching for an existing table, click the Create link. As you can see in Figure 13.23, a table called HR.EMPLOYEE_REVIEW with three columns is created.
Click the Estimate Table Size link, enter an estimated row count of 5000 for the first year, and click the Estimate Table Size link again. In Figure 13.24, notice that 5,000 rows of the table will occupy just more than 13MB, with the allocated space at 14MB.
Figure 13.23. EM Database Control: Create Table
Figure 13.24. Estimating table size
13.2.2.2. Segment Advisor within PL/SQL
Although the Segment Advisor is easy to use from EM Database Control, sometimes you may want to perform some of these operations from within a PL/SQL procedure. For example, you may want to automate the advisors in a nightly batch job.
To access the Segment Advisor functionality within PL/SQL, use the package DBMS_ADVISOR. Because DBMS_ADVISOR is used with the AWR for all advisors within the Oracle 10g advisory framework, not all procedures within DBMS_ADVISOR are applicable to all advisors, and the parameters for a particular procedure will also vary depending on the advisor. For the Segment Advisor, you typically use the following procedures:
CREATE_TASK
CREATE_OBJECT
SET_TASK_PARAMETER
EXECUTE_TASK
DELETE_TASK
CANCEL_TASK
We will explain each of these procedures in the following sections and provide two examples of analyzing a table using the Segment Advisor and implementing Segment Advisor recommendations.
13.2.2.2.1. CREATE_TASK
As the name implies, CREATE_TASK creates a new advisor task. For the Segment Advisor, the procedure requires the text string Segment Advisor, a variable to contain the assigned task number, a task name, and a description. Here is an example:
dbms_advisor.create_task
('Segment Advisor', :task_id, task_name,
'Free space in OE.CUSTOMERS', NULL);After the task is created, the assigned task number is stored in the SQL*Plus variable task_id. The unique task name is automatically generated if you leave task_name null; otherwise, Oracle will use the task name specified. In both cases, the task name must be unique among all tasks created by a particular user. Assigning a task name or description can help identify the results when querying the advisor-related data dictionary views.
13.2.2.2.2. CREATE_OBJECT
The CREATE_OBJECT procedure specifies an object to be analyzed within the task. For the Segment Advisor, the object to be analyzed is typically a table or index; for other advisors, such as the SQL Access Advisor, the object to be analyzed is a SQL statement. To create a task object that will analyze the OE.CUSTOMERS table, use the following syntax:
dbms_advisor.create_object
(task_name, 'TABLE', 'OE', 'CUSTOMERS',
NULL, NULL, object_id);The PL/SQL variable object_id is assigned a unique identifier for this object. The NULL parameters are not needed for advisor objects within the Segment Advisor.
13.2.2.2.3. SET_TASK_PARAMETER
The SET_TASK_PARAMETER procedure allows you to specify any additional parameters needed to run the analysis for the database objects specified with CREATE_OBJECT. In the case of the Segment Advisor, you have a Boolean parameter called RECOMMEND_ALL that you set to TRUE for the analysis on the table. Here is an example:
dbms_advisor.set_task_parameter
(task_name, 'RECOMMEND_ALL', 'TRUE'),When set to TRUE, the parameter RECOMMEND_ALL provides recommendations for all objects specified by the user, not just the objects eligible for segment shrink. Objects not eligible for segment shrink include objects such as tables that don't have ROW MOVEMENT enabled or tables that reside in tablespaces that do not have automatic segment space management enabled.
13.2.2.2.4. EXECUTE_TASK
Once all the tasks are created and their parameters specified, EXECUTE_TASK performs the analysis. The only parameter specified is the task name generated in a previous step in your code by the CREATE_TASK procedure. Here is an example:
dbms_advisor.execute_task(task_name);
To view the status of the executing task, especially for a long-running task such as a full tablespace analysis, the data dictionary view DBA_ADVISOR_LOG contains the task name, the start and stop time, the current status, and estimated percentage complete for the task.
13.2.2.2.5. DELETE_TASK
The DELETE_TASK procedure removes a single advisor task from the AWR, even if the task has not been executed yet. Here is an example:
dbms_advisor.delete_task(task_name);
13.2.2.2.6. CANCEL_TASK
The CANCEL_TASK procedure terminates a currently executing task. Because all advisor procedures are synchronous, the CANCEL_TASK procedure must be called from a different session for the same user account. Here is an example:
dbms_advisor.cancel_task(task_name);
13.2.2.2.7. Analyzing a Table Using Segment Advisor
The code examples that follow call these procedures to determine if the table OE.CUSTOMERS wneeds to be shrunk.
The last change to the table OE.CUSTOMERS added a field called CUST_COMMENTS to contain any suggestions, complaints, or information about the customer:
SQL> alter table oe.customers
add (cust_comments varchar2(2000));
Table altered.After a number of months using this new field, you realize that the comments should be broken out by date and decide to create a new table to hold a timestamp and a comment for that particular date and time. After the new table is implemented and the comments moved to the new table, drop the column from the OE.CUSTOMERS table:
SQL> alter table oe.customers drop (cust_comments); Table altered.
You realize that this table may be a good candidate for segment shrink and decide to use a PL/SQL procedure to analyze the table:
-- SQL*Plus variable to contain the task ID
variable task_id number
-- PL/SQL block follows
declare
task_name varchar2(100);
task_descr varchar2(100);
object_id number;
begin
task_name := ''; -- unique name generated
-- by create_task
task_descr := 'Free space in OE.CUSTOMERS';
dbms_advisor.create_task
('Segment Advisor', :task_id, task_name,
task_descr, NULL);
dbms_advisor.create_object
(task_name, 'TABLE', 'OE', 'CUSTOMERS',
NULL, NULL, object_id);
dbms_advisor.set_task_parameter
(task_name, 'RECOMMEND_ALL', 'TRUE'),
dbms_advisor.execute_task(task_name);
end;
PL/SQL procedure successfully completed.Using the SQL*Plus PRINT command, identify the task ID number to use in your data dictionary queries:
SQL> print task_id
TASK_ID
----------
680Using this task number, you can query the data dictionary view DBA_ADVISOR_FINDINGS to see the recommendations:
SQL> select owner, task_id, task_name, type, 2 message, more_info from dba_advisor_findings 3 where task_id = 680;
OWNER TASK_ID TASK_NAME TYPE ---------- ---------- ------------ ----------- SYS 680 TASK_680 INFORMATION MESSAGE ---------------------------------------------------------- Enable row movement of the table OE.CUSTOMERS and perform shrink, estimated savings is 775878 bytes. MORE_INFO ---------------------------------------------------------- Allocated Space:983040: Used Space:205110: Reclaimable Space :775878: 1 row selected.
Note that the Segment Advisor reminds you to enable row movement for the table; this is a required prerequisite before a shrink can be performed. The space in each block occupied by the CUST_COMMENTS column is unused, and by compacting this table, you can reclaim almost 80 percent of the allocated space.
13.2.2.2.8. Implementing Segment Advisor Recommendations
To shrink the table OE.CUSTOMERS, you need to enable row movement:
SQL> alter table oe.customers enable row movement; Table altered.
Because you have no applications or triggers that depend on the ROWIDs of this table, leave row movement enabled.
Next, perform the shrink operation; the data dictionary view DBA_ADVISOR_ACTIONS provides the SQL for the shrink operation:
SQL> select task_id, task_name, command, attr1 from
2 dba_advisor_actions where task_id = 680;
TASK_ID TASK_NAME COMMAND
---------- ------------ ---------------
680 TASK_680 SHRINK SPACE
ATTR1
------------------------------------------------------
alter table "OE"."CUSTOMERS" shrink space1 row selected. SQL> alter table "OE"."CUSTOMERS" shrink space; Table altered.
The shrink operation requires a negligible amount of disk space, and the table is available to other users during the shrink operation, except for a short period of time at the end of the shrink operation to move the HWM. Because this table is relatively large, you may consider performing this operation in two steps, the first time with the COMPACT option to free the space and the second time without the COMPACT option to move the HWM.
Finally, remove this task from the AWR, because you have no need to retain this information once the segment has been shrunk:
SQL> execute dbms_advisor.delete_task('TASK_680'),
PL/SQL procedure successfully completed.13.2.3. Index Space Monitoring
While you may use Segment Advisor to analyze a table and its associated indexes, you can perform this analysis manually for an individual index of interest by using data dictionary views and some specific SQL commands, as we will demonstrate in this section. We will also show you how to rebuild and coalesce an index and show you the SQL commands that you can use to determine if an index has been used or not.
13.2.3.1. Monitoring Index Space Usage
If index entries are inserted, updated, and deleted frequently, an index may lose its space efficiency over time; Oracle b-tree indexes, however, will always stay balanced. For a given index, you can determine if an index needs to be rebuilt by validating the structure of the index and checking the value of PCT_USED in the data dictionary view INDEX_STATS, as in this example:
SQL> analyze index emp_name_ix validate structure;
Index analyzed.
SQL> select pct_used from index_stats where name = 'EMP_NAME_IX';
PCT_USED
----------
33Over time, if the PCT_USED value starts to decline, you can either rebuild the index, or drop and re-create the index.
13.2.3.2. Rebuilding versus Coalescing Indexes
Both rebuilding an index or coalescing the index have their pros and cons. Table 13.4 compares the two methods.
If you decide to coalesce the index, you can do it using an ALTER INDEX command, as in this example:
SQL> alter index emp_name_ix coalesce; Index altered.
| Rebuilding an Index | Coalescing an Index |
|---|---|
| Can move index to another tablespace | Cannot move index to another tablespace |
| Requires double the amount of disk space if performed online | Does not require additional disk space |
| Creates a new tree and adjusts tree height if branch | Coalesces index leaf blocks within each necessary |
If you decide to rebuild the index, you can do it online, as in this example:
SQL> alter index emp_name_ix rebuild online; Index altered.
Because you are using the ONLINE keyword, the current index is left intact while a new copy of the index is built, allowing users to access the old index with SELECT statements or other DML statements. Any changes to the old index are saved in a special table known as a journal table. Once the index rebuild is complete, the changes recorded in the journal table are merged into the new index. Once the merge operation is complete, the data dictionary is updated and the old index is dropped. The index is available nearly 100 percent of the time that the rebuild operation is in progress, other than a very short amount of time at the end of the rebuild when the old index is swapped with the new copy of the index. One of the downsides, however, of rebuilding an index online is that you temporarily need enough disk space to store another copy of the index while the rebuild occurs.
13.2.3.3. Identifying Unused Indexes
Starting with Oracle 9i, Oracle has a feature that can monitor an index and set a flag in the dynamic performance view V$OBJECT_USAGE. To turn on the monitoring process, you use the MONITORING USAGE clause of the ALTER INDEX statement.
To see if the EMP_NAME_IX index is going to be used during the day, turn on the monitoring process with this statement:
SQL> alter index hr.emp_name_ix monitoring usage; Index altered.
Next, check V$OBJECT_USAGE to make sure the index is being monitored:
SQL> select index_name, table_name, monitoring, 2 used, start_monitoring 3 from v$object_usage where index_name = 'EMP_NAME_IX'; INDEX_NAME TABLE_NAME MON USE START_MONITORING ------------- ---------------- --- --- ------------------- EMP_NAME_IX EMPLOYEES YES NO 06/02/2004 08:57:44 1 row selected.
During the day, one of the HR employees runs this query:
SQL> select employee_id from employees
2 where last_name = 'King';
EMPLOYEE_ID
-----------
100
156
2 rows selected.At around 5 P.M., check V$OBJECT_USAGE again to see if the index was used:
SQL> select index_name, table_name, monitoring, 2 used, start_monitoring 3 from v$object_usage where index_name = 'EMP_NAME_IX'; INDEX_NAME TABLE_NAME MON USE START_MONITORING ------------- ---------------- --- --- ------------------- EMP_NAME_IX EMPLOYEES YES YES 06/02/2004 08:57:44 1 row selected.
NOTE
Because V$OBJECT_USAGE is a dynamic performance view, the contents will not be retained in the view once the database is shut down and restarted.
Because the index is being used at least once during the day, you probably want to keep the index pending further analysis. Your last step is to turn off monitoring with the NOMONITORING USAGE clause and check the V$OBJECT_USAGE view one more time to verify this:
SQL> alter index hr.emp_name_ix nomonitoring usage; Index altered. SQL> select index_name, table_name, monitoring, 2 used, end_monitoring 3 from v$object_usage where index_name = 'EMP_NAME_IX'; INDEX_NAME TABLE_NAME MON USE END_MONITORING ------------ ----------------- --- --- ------------------- EMP_NAME_IX EMPLOYEES NO YES 06/02/2004 17:00:40 1 row selected.
|
