oracle的monitor脚本在哪,Oracle索引监控(monitor index)

最新推荐文章于 2022-02-07 09:32:08 发布

weixin_39941792

最新推荐文章于 2022-02-07 09:32:08 发布

阅读量141

点赞数

文章标签： oracle的monitor脚本在哪

Oracle索引监控(monitor index) 合理的为数据库表上创建战略性索引，可以极大程度的提高查询性能。但事实上日常中我们所创建的索引并非战略性索引，恰恰是大量冗余或是根本没有用到的索引，这些索引耗用了大量的存储空间，导致DML性能低下。Oracle 提供了索引监控特性来粗略判断未使用到的索引。本文描述如何使用Oracle 索引的监控。 1、冗余索引的弊端大量冗余和无用的索引导致整个数据库性能低下，耗用了大量的CPU与I/O开销，具体表现如下: a、耗用大量的存储空间(索引段的维护与管理) b、增加了DML完成的时间 c、耗用大量统计信息(索引)收集的时间 d、结构性验证时间 f、增加了恢复所需的时间 2、单个索引监控 a、对于单个索引的监控，可以使用下面的命令来完成 alter index monitoring usage; b、关闭索引监控 alter index nomonitoring usage; c、观察监控结果(查询v$object_usage视图) select * from v$object_usage 3、schema级别索引监控(不含SYS用户) a、直接执行脚本来开启索引监控 robin@SZDB:~/dba_scripts/custom/sql> more idx_monitor_on.sql SET HEADING OFF FEEDBACK OFF TERMOUT OFF ECHO OFF; SET PAGESIZE 0; SPOOL /tmp/mnt_idx.sql SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' MONITORING USAGE;' FROM dba_indexes WHERE owner IN (SELECT username FROM dba_users WHERE account_status = 'OPEN') AND owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'MGMT_VIEW', 'MONITOR', 'SYSMAN', 'DBSNMP'); SPOOL OFF; @/tmp/mnt_idx.sql; SET HEADING ON FEEDBACK ON TERMOUT ON; SET PAGESIZE 80; SELECT index_name, monitoring, used, start_monitoring, end_monitoring FROM v$object_usage; ho rm -rf /tmp/mnt_idx.sql b、禁用索引监控 robin@SZDB:~/dba_scripts/custom/sql> more idx_monitor_off.sql SET HEADING OFF FEEDBACK OFF TERMOUT OFF ECHO OFF; SET PAGESIZE 0; SPOOL /tmp/un_mnt_idx.sql SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' NOMONITORING USAGE;' FROM dba_indexes WHERE owner IN (SELECT username FROM dba_users WHERE account_status = 'OPEN') AND owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'MGMT_VIEW', 'MONITOR', 'SYSMAN', 'DBSNMP'); SPOOL OFF; @/tmp/un_mnt_idx.sql; SET HEADING ON FEEDBACK ON TERMOUT ON; SET PAGESIZE 80; SELECT index_name, monitoring, used, start_monitoring, end_monitoring FROM v$object_usage; ho rm -rf /tmp/un_mnt_idx.sql c、查看索引监控结果 set linesize 190 SELECT u.name owner, io.name index_name, t.name table_name, DECODE (BITAND (i.flags, 65536), 0, 'NO', 'YES') monitoring, DECODE (BITAND (ou.flags, 1), 0, 'NO', 'YES') used, ou.start_monitoring start_monitoring, ou.end_monitoring end_monitoring FROM sys.user$ u, sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou WHERE i.obj# = ou.obj# AND io.obj# = ou.obj# AND t.obj# = i.bo# AND u.user# = io.owner# AND u.name=decode(upper('&input_owner'),'ALL',u.name,upper('&input_owner')); 4、演示索引监控 a、单个索引监控 -->演示环境 scott@CNMMBO> select * from v$version where rownum<2; BANNER ---------------------------------------------------------------- Oracle Database 10g Release 10.2.0.3.0 - 64bit Production -->创建测试表 scott@CNMMBO> create table tb_emp as select * from emp; -->为测试表创建索引 scott@CNMMBO> create index i_tb_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_EMPNO TB_EMP NO YES 03/19/2013 17:43:49 03/19/2013 17:46:04 GOEX_ADMIN ACC_GRP_EXT_INFO_TBL_LOG_PK ACC_GRP_EXT_INFO_TBL_LOG YES YES 02/22/2013 15:58:42 GOEX_ADMIN IDX_TDCL_CONTRACT_NUM TRADE_CLIENT_TBL YES YES 02/22/2013 15:58:42 GOEX_ADMIN IDX_TDCL_SETTLED_DATE TRADE_CLIENT_TBL YES YES 02/22/2013 15:58:42 GOEX_ADMIN IDX_TDCL_ACC_NUM TRADE_CLIENT_TBL YES YES 02/22/2013 15:58:41 GOEX_ADMIN IDX_TDCL_INSTRU_ID TRADE_CLIENT_TBL YES YES 02/22/2013 15:58:42 5、索引监控的建议与弊端 a、选择数据库高峰期实施索引监控，以及尽可能使用较长的监控周期来判断索引是否被使用 b、可以对特定时间段实施多次监控以判断索引的使用频率(粗略值) c、索引监控在一定程度上耗用系统资源，一旦监控完毕后应即时关闭以避免其带来的额外开销 d、索引监控仅仅从索引的使用与否来描述索引使用，并未提供详细的索引使用频率，b点提到的方法也只是粗略值 (Oracle 监控索引的使用率参见：http://blog.csdn.net/robinson_0612/article/details/8823133) 转自：http://www.2cto.com/database/201303/196854.html