Oracle索引监控(monitor index)(二)
_emp_empno on tb_emp(empno);
-->收集统计信息
scott@CNMMBO> exec dbms_stats.gather_table_stats('SCOTT','TB_EMP',cascade=>true);
-->查看索引信息
scott@CNMMBO> @idx_info
Enter value for owner: scott
Enter value for table_name: tb_emp
Table Name INDEX_NAME CL_NAM CL_POS STATUS IDX_TYP DSCD
------------------------- ------------------------------ -------------------- ------ -------- --------------- ----
TB_EMP I_TB_EMP_EMPNO EMPNO 1 VALID NORMAL ASC
-->查看索引使用情况
-->此时use列为NO，表明索引未被使用到
scott@CNMMBO> @idx_usage_tb
Enter value for 1: tb_emp
Enter value for 2: all
Enter value for 2: all
Table Name INDEX_NAME USE START_MONITORING END_MONITORING
------------------------- ------------------------------ --- ------------------- -------------------
TB_EMP I_TB_EMP_EMPNO NO 03/19/2013 17:43:49
-->实施即席查询
scott@CNMMBO> select empno,ename,job from tb_emp where empno=7788;
EMPNO ENAME JOB
---------- ---------- ---------
7788 SCOTT ANALYST
-->再次查看时USE列已经为YES
scott@CNMMBO> @idx_usage_tb
Enter value for 1: tb_emp
Enter value for 2: all
Enter value for 2: all
Table Name INDEX_NAME USE START_MONITORING END_MONITORING
------------------------- ------------------------------ --- ------------------- -------------------
TB_EMP I_TB_EMP_EMPNO YES 03/19/2013 17:43:49
-->禁用索引监控
scott@CNMMBO> alter index I_TB_EMP_EMPNO nomonitoring usage;
Index altered.
b、schema级别的索引监控
-->切换到另外一个数据库cnbo1
scott@CNMMBO> conn goex_admin/xxxxx@cnbo1
Connected.
-->下面的查询表明没有表开启索引监控
goex_admin@CNBO1> @idx_usage;
no rows selected
-->开启索引监控
goex_admin@CNBO1> @idx_monitor_on
INDEX_NAME MON USE START_MONITORING END_MONITORING
------------------------------ --- --- ------------------- -------------------
PK_AAH YES NO 03/19/2013 17:48:32
IDX_GOAAE1 YES NO 03/19/2013 17:48:32
PK_GOAAT YES NO 03/19/2013 17:48:32
PK_GOAACTL YES NO 03/19/2013 17:48:32
....... ................
-->关闭索引监控
goex_admin@CNBO1> @idx_monitor_off
INDEX_NAME MON USE START_MONITORING END_MONITORING
------------------------------ --- --- ------------------- -------------------
PK_GOARL NO NO 03/19/2013 17:48:30 03/19/2013 17:50:02
IDX_GOAQU1 NO NO 03/19/2013 17:48:30 03/19/2013 17:50:02
IDX_GOAQU2 NO NO 03/19/2013 17:48:30 03/19/2013 17:50:02
-->连接到原来的db，查看曾经开启索引监控的使用情况
goex_admin@CNBO1> conn scott/tiger@cnmmbo
Connected.
goex_admin@CNMMBO> @idx_usage
Enter value for input_owner: GOEX_ADMIN
Enter value for input_owner: GOEX_ADMIN
OWNER INDEX_NAME Table Name MON USE START_MONITORING END_MONITORING
--------------- ------------------------------ ------------------------- --- --- ------------------- ----------------
SCOTT I_TB_EMP_EMP