How to use Oracle monitoring indexes

Oracle metric index usage

Oracle provides a monitoring index method to determine whether an index is used. If indexes are not used, you can delete them to reduce unnecessary statement overhead. Because a large number of unnecessary indexes on a table may reduce the performance of DML statements and put pressure on database performance. Therefore, in the production environment, you can regularly monitor and analyze the use of database indexes based on business growth, especially the indexes on some large tables, to improve the performance of database transaction commit.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 19 20

-- View the index SQL> Selectindex_name, table_name, num_rows From dba_indexes I Where I. table_name = 'website _ VIEW_TB '; INDEX_NAME TABLE_NAME NUM_ROWS tables ---------- descriwebsite_view_tb 79284331 ORDERNO_IDX WEBSITE_VIEW_TB 2021984 descriwebsite_view_tb 938174 -- enable index monitoring SQL> ALTER INDEX MONITORING USAGE; index altered -- View Index monitoring SQL> Select * From v $ object_usage; INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING begin --------- begin CURRTIME_IDX WEBSITE_VIEW_TB YES NO 08/20/2013 14:19:48

 

Note: If the index monitoring function is enabled, you can use the v $ object_usage view to view the index records being monitored. The USED column indicates whether the index is USED when the index monitoring function is enabled, the MONITORING column indicates whether index MONITORING is enabled, START_MONITORING indicates the start time of enabling index MONITORING, and END_MONITORING indicates the end time of enabling index MONITORING.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 28 29 30 31 32 33 34 35 36 38 39 40 41 42 43 44 45 46 48 49 50 51

-- Execute the query statement and use the monitored index SQL> Select Count (*) From log. WEBSITE_VIEW_TB t 2 Where t. currtime between to_date ('2017-1-27 ', 'yyyy-mm-dd') And to_date ('2017-1-28', 'yyyy-mm-dd'); COUNT (*) ---------- 750603 -- the USED column value of the record is changed to YES, indicating that the index has been USED. SQL> Select * From v $ object_usage; INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING begin MONITORING ----------------- -------- summary -------- analyze WEBSITE_VIEW_TB YES 08/20/2013 14:19:48 -- disable INDEX MONITORING after the analysis is complete, because MONITORING also occupies certain resources, SQL> ALTER INDEX CURRTIME_IDX NOMONITORING USAGE; Index altered -- you can see that the MONITORING column is changed to NO, And the END_MONITORING column is filled, index stop monitoring SQL> Select * From v $ object_usage; INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING stopping MONITORING -------- indexing without WEBSITE_VIEW_TB NO YES 08/20/2013 14:19:48 08/20/2013 14:32:18 -- run the query again, SQL> Select Count (*) From log. WEBSITE_VIEW_TB t 2 Where t. currtime between to_date ('2017-1-27 ', 'yyyy-mm-dd') And to_date ('2017-1-28', 'yyyy-mm-dd'); COUNT (*) ---------- 750603 SQL> Select * From v $ object_usage; INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING too many bytes -------- too many bytes WEBSITE_VIEW_TB NO YES 08/20/2013 14:19:48 08/20/2013 14:32:18 -- enable index MONITORING again, the corresponding MONITORING record value has changed. SQL> alter index CURRTIME_IDX monitoring usage; Index altered SQL> Select * From v $ object_usage; INDEX_NAME TABLE_NAME monitoring used START_MONITORING END_MONITORING ---------- ---- -------------------------------------------- CURRTIME_IDX WEBSITE_VIEW_TB yes no 08/20/2013 14:34:15

Summary:

Although the v $ object_usage table can record the index monitoring and usage status, it cannot count the number and frequency of index usage, this parameter only records whether the index has been used during the time period when index monitoring is enabled.