CREATE TABLE TEST ( ID NUMBER(10), NAME VARCHAR2(32) ); CREATE INDEX IDX_TEST_ID ON TEST(ID); INSERT INTO TEST SELECT 1001, 'Kerry' FROM DUAL UNION ALL SELECT 1002, 'Ken' FROM DUAL UNION ALL SELECT 1003, 'Jimmy' FROM DUAL UNION ALL SELECT 1004, 'Jack' FROM DUAL; COMMIT; execute dbms_stats.gather_table_stats(ownname => 'ETL', tabname =>'TEST', estimate_percent =>DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO');
啟用對索引IDX_TEST_ID的監控 ALTER INDEX IDX_TEST_ID MONITORING USAGE; 此時觀察V$OBJECT_USAGE表數據的變化,如下所示,MONITORIN字段值變為YES,表示索引IDX_TEST_ID已經被置於監控狀態。USED字段為NO表示暫時沒有SQL使用該索引
SQL> COL INDEX_NAME FOR A20 SQL> COL TABLE_NAME FOR A10 SQL> COL MONITORING FOR A10 SQL> COL USED FOR A10 SQL> COL START_MONITORING FOR A20 SQL> COL END_MONITORING FOR A20 SQL> SELECT * FROM V$OBJECT_USAGE; INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING ------------ ---------- ---------- ---- -------------------- ---------------- IDX_TEST_ID TEST YES NO 11/28/2015 14:57:41
此時我們執行下面SQL,因為此時使用全表掃描,那麼索引IDX_TEST_ID依然沒有被使用,此時可以查看V$OBJECT_USAGE進行驗證。
SQL> SET AUTOTRACE ON; SQL> SELECT * FROM TEST WHERE ID =1001; ID NAME ---------- -------------------------------- 1001 Kerry Execution Plan ---------------------------------------------------------- ---------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 9 | 2 | | 1 | TABLE ACCESS FULL| TEST | 1 | 9 | 2 | ---------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version Statistics ---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 0 physical reads 0 redo size 578 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed
clip_image001 如下所示,此時索引IDX_TEST_ID依然沒有被使用。 clip_image002 我們使用索引提示強制下面SQL使用索引IDX_TEST_ID SELECT /*+ INDEX(TEST IDX_TEST_ID) */* FROM TEST WHERE ID =1001; 此時你就會發現USED的值變為了YES了。 ALTER INDEX IDX_TEST_ID NOMONITORING USAGE; 執行上面命令後,在V$OBJECT_USAGE表中,就會更新表TEST記錄的END_MONITORING、MONITORING的值。 如果你又啟用監控索引使用情況,那麼系統會更新START_MONITORING、END_MONITORING字段的值(END_MONITORING的值更新為NULL)。如果刪除表 TEST,此時你會發現V$OBJECT_USAGE對象中關於表TEST的記錄也不見了。 注意:SELECT * FROM V$OBJECT_USAGE; 只能查看當前用戶下被監控的索引信息。即使sys、system用戶也不能查看其它用戶的信息。 在測試過程中有個小疑問,就是在准備測試環境時,如果不對表收集統計信息的話,那麼即使SQL走全表掃描,你依然發現V$OBJECT_USAGE中索引被標記使用了。如下所示
DROP TABLE TEST PURGE; CREATE TABLE TEST ( ID NUMBER(10), NAME VARCHAR2(32) ); CREATE INDEX IDX_TEST_ID ON TEST(ID); INSERT INTO TEST SELECT 1001, 'Kerry' FROM DUAL UNION ALL SELECT 1002, 'Ken' FROM DUAL UNION ALL SELECT 1003, 'Jimmy' FROM DUAL UNION ALL SELECT 1004, 'Jack' FROM DUAL; COMMIT; ALTER INDEX IDX_TEST_ID MONITORING USAGE; SQL> SET AUTOTRACE ON; SQL> SELECT * FROM TEST WHERE ID =1001; ID NAME ---------- -------------------------------- 1001 Kerry Execution Plan ---------------------------------------------------------- ---------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | ---------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 31 | 2 | | 1 | TABLE ACCESS FULL| TEST | 1 | 31 | 2 | ---------------------------------------------------------- Note ----- - 'PLAN_TABLE' is old version Statistics ---------------------------------------------------------- 7 recursive calls 0 db block gets 10 consistent gets 0 physical reads 0 redo size 578 bytes sent via SQL*Net to client 492 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed SQL> SELECT * FROM V$OBJECT_USAGE; INDEX_NAME TABLE_NAME MONITORING USED START_MONITORING END_MONITORING ------------ ---------- ---------- ------ ----------- ------------------- IDX_TEST_ID TEST YES YES 11/28/2015 15:11:46
那麼為什麼呢? 猜測是在解析生成執行計劃時,用到了索引的一些信息,導致V$OBJECT_USAGE表中的字段USED被標記為YES。 如果我們想在系統中監控所有的索引,那麼我們可以通過下面腳本實現監控數據庫所有的索引。注意我們要排除一些系統表的索引、以及LOB indexes。原因有下面兩個: 1:LOB indexes不能修改,否則會報ORA-22864錯誤(ORA-22864: cannot ALTER or DROP LOB indexes)。 2:ORA-00701: object necessary for warmstarting database cannot be altered ORA-00701: object necessary for warmstarting database cannot be altered 00701. 00000 - "object necessary for warmstarting database cannot be altered"
*Cause: Attempt to alter or drop a database object (table, cluster, or index) which are needed for warmstarting the database. *Action: None. SET PAGES 999; SET HEADING OFF; SPOOL run_monitor.sql SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' MONITORING USAGE;' FROM DBA_INDEXES WHERE INDEX_TYPE != 'LOB' AND OWNER NOT IN ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN') ; SPOOL OFF; @run_monitor.sql
此時使用下面腳本就能查出那些索引是未使用索引,當然監控索引時長非常重要,太短的話有可能導致查詢出來的數據有問題,一般建議監控一周後即可,OLAP系統則需要適當延長監控的時間。
SELECT I.TABLE_OWNER, T.TABLE_NAME, I.INDEX_NAME, U.USED, U.START_MONITORING, U.END_MONITORING FROM USER_TABLES T INNER JOIN USER_INDEXES I ON T.TABLE_NAME = I.TABLE_NAME INNER JOIN V$OBJECT_USAGE U ON U.TABLE_NAME = I.TABLE_NAME AND I.INDEX_NAME = U.INDEX_NAME WHERE I.TABLE_OWNER=SYS_CONTEXT('USERENV','CURRENT_USER')
另外,查找沒有使用索引的SQL語句。如下所示statspack_unused_indexes.sql
col owner heading "Index Owner" format a30 col index_name heading "Index Name" format a30 set linesize 95 trimspool on pagesize 80 select * from (select owner, index_name from dba_indexes di where di.index_type != 'LOB' and owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN') minus select index_owner owner, index_name from dba_constraints dc where index_owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN') minus select p.object_owner owner, p.object_name index_name from stats$snapshot sn, stats$sql_plan p, stats$sql_summary st, stats$sql_plan_usage spu where st.sql_id = spu.sql_id and spu.plan_hash_value = p.plan_hash_value and st.hash_value = p.plan_hash_value and sn.snap_id = st.snap_id and sn.dbid = st.dbid and sn.instance_number = st.instance_number and sn.snap_id = spu.snap_id and sn.dbid = spu.snap_id and sn.instance_number = spu.instance_number and sn.snap_id between &begin_snap and &end_snap and p.object_type = 'INDEX' ) where owner not in ('SYS', 'SYSMAN', 'SYSTEM', 'MDSYS', 'WMSYS', 'TSMSYS', 'DBSNMP', 'OUTLN') order by 1, 2 / 這裡是另一個腳本用來跟蹤未使用的索引並展示給所有索引的調用計數。最重要的是,這個腳本顯示了多列索引引用的列(這個腳本執行時間較長,資源開銷較大。) col c1 heading 'Begin|Interval|time' format a20 col c2 heading 'Search Columns' format 999 col c3 heading 'Invocation|Count' format 99,999,999 break on c1 skip 2 accept idxname char prompt 'Enter Index Name: ' ttitle 'Invocation Counts for index|&idxname' select to_char(sn.begin_interval_time,'yy-mm-dd hh24') c1, p.search_columns c2, count(*) c3 from dba_hist_snapshot sn, dba_hist_sql_plan p, dba_hist_sqlstat st where st.sql_id = p.sql_id and sn.snap_id = st.snap_id and p.object_name = '&idxname' group by begin_interval_time,search_columns;