ORACLE 監控索引的使用

相關檢視:
user_indexes
user_ind_columns
v$object_usage

1. 檢視錶aa有哪些索引

SQL> select table_name,index_name from user_indexes
2 where table_name='AA';

TABLE_NAME                     INDEX_NAME
------------------------------ ----------------------------
AA                             IDX_FUNC
AA                             IDX_BITM

已用時間: 00: 00: 00.09
SQL>

2. 監控制定索引

SQL> alter index idx_func monitoring usage;

索引已更改。

已用時間: 00: 00: 00.39

SQL>select * from aa where rownum<100;

3. 檢視索引是否被用

SQL> select * from v$object_usage;

未選定行

SQL>

4.查完索引是否被用後,記得要關閉索引監控

SQL> alter index idx_func nomonitoring usage;

5.監控資料庫所有的索引

等資料庫運的足夠多的sql語句時,查詢v$object_usage

開啟所有索引監控指令碼

spool run_monitoring.sql

select 'alter index ' ||owner||'.'|| index_name|| ' monitoring usage'
    from dba_indexes;
    where owner not in ('SYS','SYSTEM')
spool off

然後執行指令碼 run_monitoring.sql

@run_monitoring.sql