How the Oracle monitoring index is used

Oracle Monitoring Index Usage

Oracle provides a way to monitor indexes to determine if the index is being used. If the indexes are not used, you can delete them to reduce the overhead of unnecessary statements. Because of the large number of unnecessary indexes on the table, the performance of the DML statement can be reduced, and the performance of the database is stressed. Therefore, the production environment to monitor and analyze the use of database indexes regularly according to business growth, especially the indexes on some large tables, improve the performance of database transaction submission.

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

--View the index on the table sql> selectindex_name,table_name,num_rows from dba_indexes i Where i.table_name = ' WEBSITE_VIEW_TB ';   index_name                      table_name                        num_rows------------------------------------ ----------------------------------currtime_idx                    website_view_tb                   79284331 orderno_idx                     website_view_tb                    2021984 Ordersource_idx                WEBSITE_VIEW_TB                     938174  --Open index monitoring sql> ALTER index CURRTIME_IDX monitoring USAGE;   Index altered  --View index monitoring 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:19:48

Note: If index monitoring is turned on, the V$object_usage view allows you to view the index records that are being monitored, and the used column indicates whether the index is being used during the index monitoring process, monitoring column indicates whether index monitoring is turned on, Start_ Monitoring indicates the start time for index monitoring, end_monitoring indicates the end time when index monitoring is turned on

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-27--28 29---30 31--32 33 34 35 36 37 38-39 40 41 42 45 46 47 48 49 50 51

-Executes the query statement using the monitored index sql> Select Count (*) from log. WEBSITE_VIEW_TB T   2 Where t.currtime between to_date (' 2013-1-27 ', ' yyyy-mm-dd ') and to_date (' 2013-1-28 ', ' Yyyy-mm-dd ');     count (*)----------    750603  --you can see that the Used column value of the record has changed to Yes, indicating that the index was used. Sql> Select * from V$object_usage;   index_name                      table_name                      Monitoring used  start_monitoring    End_monitoring----------------------------  -----------------------------                 --------------- --------   ------------------------------ --------currtime_idx                    website_view_tb                 yes         yes        08/20/2013 14:19:48  --after analysis, turn off index monitoring, because monitoring will also occupy a certain resource sql> ALTER index Currtime_idx Nomonitoring USAGE;   Index altered  --you can see monitoring columns becoming no,end_monitoring columns filled, index stops monitoring sql> Select * from V$object_usage;   index_name                      table_name                     monitoring used  start_monitoring   end_monitoring -----------------------------------------------------------------    -------------------  --------  ------------------------------- ---------------------------Currtime_idx                    website_view_tb                no           yes      08/20/ 2013 14:19:48      08/20/2013 14:32:18  -Execute query again, monitor record unchanged sql> Select Count (*) from log . WEBSITE_VIEW_TB T   2 Where t.currtime between to_date (' 2013-1-27 ', ' yyyy-mm-dd ') and to_date (' 2013-1-28 ', ' Yyyy-mm-dd ');     count (*)----------    750603   sql> Select * from V$object_usage;   index_name                      table_name                      Monitoring used  Start_monitoring    end_monitoring-----------------------------------------------------------------     ------------------- --------  ------------------------------- ---------------- -----------currtime_idx                    website_view_tb                 no           yes       08/20/2013 14:19:48      08/20/2013 14:32:18  --turn on 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

Summarize:

Although the V$object_usage table can record the status of index monitoring and usage, it does not count the number and frequency of indexes being used, but only records whether the index has been used in the time period when index monitoring is turned on, which is worth noting.