A-A+
有关索引-查看某索引是否使用过
查看某索引是否使用过
创建测试表及索引
SQL> drop table yallonking; Table dropped. SQL> create table yallonking as select rownum id,'yallonking' name from dual connect by rownum<1000; Table created. SQL> create index idx_id on yallonking(id); Index created.
（一）通过对索引的监控来检查
SQL> alter index idx_id monitoring usage;
Index altered.
SQL> select index_name,monitoring,used from v$object_usage;
INDEX_NAME MON USE
------------------------------ --- ---
IDX_ID YES NO
SQL> set autot traceonly
SQL> select * from yallonking;
999 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2582482548
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 24975 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS FULL| YALLONKING | 999 | 24975 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
131 recursive calls
0 db block gets
96 consistent gets
0 physical reads
0 redo size
28941 bytes sent via SQL*Net to client
1111 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
999 rows processed
上边的sql明显没有使用到索引，所以在下边的查询中也没有监控到使用的迹象。
SQL> set autot off SQL> select index_name,monitoring,used from v$object_usage; INDEX_NAME MON USE ------------------------------ --- --- IDX_ID YES NO
下边使用用到索引的sql查询
SQL> set autot traceonly
SQL> select * from yallonking where id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 3725498274
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| YALLONKING | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_ID | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=2)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
12 recursive calls
3 db block gets
17 consistent gets
1 physical reads
544 redo size
474 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
此刻上边可以看见在使用了该索引的sql后，在v$object_usage中有了相关记录
SQL> set autot off SQL> select index_name,monitoring,used from v$object_usage; INDEX_NAME MON USE ------------------------------ --- --- IDX_ID YES YES SQL> alter index idx_id nomonitoring usage; Index altered.
（二）查看历史视图判断索引使用的情况
SQL> select d.object_name,d.operation,d.options,count(1) from dba_hist_sql_plan d,dba_hist_sqlstat h where d.object_owner <> 'SYS' and d.operation like '%INDEX%' and d.sql_id = h.sql_id group by d.object_name,d.operation,d.options order by 1,2,3; OBJECT_NAME OPERATION OPTIONS COUNT(1) ------------------------------- ------------------------------ ------------------------------ ---------- AQ$_QUEUES_CHECK INDEX RANGE SCAN 7 AQ$_QUEUE_TABLES_PRIMARY INDEX UNIQUE SCAN 17 MGMT_CURRENT_SEVERITY_IDX_03 INDEX RANGE SCAN 1 MGMT_JOB_EXEC_IDX01 INDEX RANGE SCAN 15 MGMT_JOB_EXEC_SUMM_IDX04 INDEX RANGE SCAN 4 MGMT_JOB_PK INDEX UNIQUE SCAN 15 MGMT_METRICS_IDX_01 INDEX RANGE SCAN 6 MGMT_METRICS_PK INDEX RANGE SCAN 1 MGMT_METRIC_COLLECTIONS_PK INDEX UNIQUE SCAN 12 MGMT_METRIC_COLLECTIONS_REP_PK INDEX FULL SCAN 12 MGMT_PARAMETERS_IDX_01 INDEX RANGE SCAN 6 OBJECT_NAME OPERATION OPTIONS COUNT(1) ------------------------------- ------------------------------ ------------------------------ ---------- MGMT_PERFORMANCE_NAMES_PK INDEX UNIQUE SCAN 2 MGMT_SYSTEM_ERROR_LOG_01 INDEX SKIP SCAN 1 MGMT_SYSTEM_PERF_LOG_IDX_01 INDEX RANGE SCAN 6 MGMT_TARGETS_IDX_01 INDEX UNIQUE SCAN 5 RLM$JOINQKEY INDEX FULL SCAN 1 RLM$SCHACTIONORDER INDEX FULL SCAN 1 SYS_C003978 INDEX UNIQUE SCAN 6 SYS_C003981 INDEX UNIQUE SCAN 6 SYS_IOT_TOP_49769 INDEX FULL SCAN 6 20 rows selected.
发现上边没有出现监控的索引的记录，是因为数据还没有写到历史视图中，故在此手动生成一次快照即可
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(); PL/SQL procedure successfully completed. SQL> select d.object_name,d.operation,d.options,count(1) from dba_hist_sql_plan d,dba_hist_sqlstat h where d.object_owner <> 'SYS' and d.operation like '%INDEX%' and d.sql_id = h.sql_id group by d.object_name,d.operation,d.options order by 1,2,3; OBJECT_NAME OPERATION OPTIONS COUNT(1) ------------------------------- ------------------------------ ------------------------------ ---------- AQ$_QUEUES_CHECK INDEX RANGE SCAN 7 AQ$_QUEUE_TABLES_PRIMARY INDEX UNIQUE SCAN 17 IDX_ID INDEX RANGE SCAN 2 MGMT_CURRENT_METRICS_PK INDEX FULL SCAN 1 MGMT_CURRENT_SEVERITY_IDX_03 INDEX RANGE SCAN 1 MGMT_JOB_EXEC_IDX01 INDEX RANGE SCAN 20 MGMT_JOB_EXEC_SUMM_IDX04 INDEX RANGE SCAN 4 MGMT_JOB_PK INDEX UNIQUE SCAN 20 MGMT_METRICS_IDX_01 INDEX RANGE SCAN 6 MGMT_METRICS_PK INDEX RANGE SCAN 1 MGMT_METRIC_COLLECTIONS_PK INDEX UNIQUE SCAN 12 OBJECT_NAME OPERATION OPTIONS COUNT(1) ------------------------------- ------------------------------ ------------------------------ ---------- MGMT_METRIC_COLLECTIONS_REP_PK INDEX FULL SCAN 12 MGMT_PARAMETERS_IDX_01 INDEX RANGE SCAN 7 MGMT_PERFORMANCE_NAMES_PK INDEX UNIQUE SCAN 2 MGMT_SYSTEM_ERROR_LOG_01 INDEX SKIP SCAN 1 MGMT_SYSTEM_PERF_LOG_IDX_01 INDEX RANGE SCAN 6 MGMT_TARGETS_IDX_01 INDEX UNIQUE SCAN 6 MGMT_TARGET_PROPERTIES_PK INDEX RANGE SCAN 1 MGMT_TARGET_ROLLUP_TIMES_PK INDEX SKIP SCAN 1 RLM$JOINQKEY INDEX FULL SCAN 1 RLM$SCHACTIONORDER INDEX FULL SCAN 1 SYS_C003978 INDEX UNIQUE SCAN 6 OBJECT_NAME OPERATION OPTIONS COUNT(1) ------------------------------- ------------------------------ ------------------------------ ---------- SYS_C003981 INDEX UNIQUE SCAN 6 SYS_IOT_TOP_49769 INDEX FULL SCAN 6 24 rows selected.
