Index Usage - Tom Kytes

Following was used by Tom to analyze usage of Indexes in 9i

We want to see if the T_PK index is used for any queries ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> create table t ( x int, 2 constraint t_pk primary key(x) ); Table created. ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> insert into t 2 select rownum 3 from all_objects 4 where rownum <= 100 5 / 100 rows created. ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> set echo off Enter to continue ================================================================= We'll enable monitoring on the index now ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> select index_name,monitoring, used,start_monitoring,end_monitoring 2 from v$object_usage; no rows selected ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> alter index t_pk monitoring usage 2 / Index altered. ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> select index_name,monitoring, used,start_monitoring,end_monitoring 2 from v$object_usage; INDEX_NAME MONITORING USED START_MONITORING END_MONITORING ------------ ---------- ---- ------------------- ------------------- T_PK YES NO 05/26/2003 10:06:32 ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> set echo off Enter to continue ================================================================= Using RBO as we are by default, a count(*) won't use the index ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> set autotrace on explain ops$tkyte@ORA920.US.ORACLE.COM> select count(*) from t; COUNT(*) ---------- 100 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (AGGREGATE) 2 1 TABLE ACCESS (FULL) OF 'T' ops$tkyte@ORA920.US.ORACLE.COM> set autotrace off ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> select index_name,monitoring, used,start_monitoring,end_monitoring 2 from v$object_usage; INDEX_NAME MONITORING USED START_MONITORING END_MONITORING ------------ ---------- ---- ------------------- ------------------- T_PK YES NO 05/26/2003 10:06:32 ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> set echo off Enter to continue ================================================================= An insert won't either ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> insert into t values (-1); 1 row created. ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> select index_name,monitoring, used,start_monitoring,end_monitoring 2 from v$object_usage; INDEX_NAME MONITORING USED START_MONITORING END_MONITORING ------------ ---------- ---- ------------------- ------------------- T_PK YES NO 05/26/2003 10:06:32 ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> set echo off Enter to continue ================================================================= But if we analyze the table, a count(*) will use the index and we would have a record of it ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> analyze table t compute statistics 2 / Table analyzed. ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> set autotrace on explain ops$tkyte@ORA920.US.ORACLE.COM> select count(*) from t; COUNT(*) ---------- 101 Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1) 1 0 SORT (AGGREGATE) 2 1 INDEX (FULL SCAN) OF 'T_PK' (UNIQUE) (Cost=1 Card=101) ops$tkyte@ORA920.US.ORACLE.COM> set autotrace off ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> ops$tkyte@ORA920.US.ORACLE.COM> select index_name,monitoring, used,start_monitoring,end_monitoring 2 from v$object_usage; INDEX_NAME MONITORING USED START_MONITORING END_MONITORING ------------ ---------- ---- ------------------- ------------------- T_PK YES YES 05/26/2003 10:06:32