① 执行存储
② 调用存储
③ 1,2步骤脚本
④ 发邮件[日期判断脚本]
⑤ 定时任务
① 执行存储 [root@lottery idx_check]# cat p_idx_check.sql
CREATE OR REPLACE PACKAGE P_IDX_CHECK IS --INSERT PROCEDURE P_INSERT(USERNAME VARCHAR2, USERNAME1 VARCHAR2, USERNAME2 VARCHAR2); --统计使用比 PROCEDURE P_analyze(USERNAME VARCHAR2, USERNAME1 VARCHAR2, USERNAME2 VARCHAR2); END P_IDX_CHECK; / / CREATE OR REPLACE PACKAGE BODY P_IDX_CHECK IS
/*---------------------------------------------------------- | AUTHOR:lottery | | USERS :以月为单位检查索引的使用情况(邮件反馈)且维护索引 | | BLOG : HTTP://BLOG.ITPUB.NET/28602568/ | -----------------------------------------------------------*/
/* 注释: 工作中是否有一些现有的索引,从来/几个月都没有被用过的情况?
索引创建情况 存在的情况 1) 初步设计建的; 当时业务加索引有意义,后续业务优化,通过其他表或者通过其他方式解决了... 2) 优化SQL建的; 数据量不太大时优化加的,后续生产数据量会越来越大.这样的索引需要监控是否有意了 3) 残留的索引; 1,2种情况,或生产上DBA优化SQL,并没有效果,后忙于他事忘了的情况...等等...(可能会发生) 那是不是说这些索引创建了之后就真的都会用到呢?如果没有用到 不会对INSERT UPDATE DELETE有性能问题么!
?主要的问题来了 怎么知道哪些索引 长时间没有用过呢? 如下存储 就是为了实现这个情况...
?可能有人就好奇了 我想看索引使用情况直接通过DBA_HIST_SQL_PLAN/GV$SQL_PLAN...等ORACLE自带的视图查就好了,还写什么存储呢? 就是因为ORACLE自带的视图 不是永久保存的; 且主题想统计几个月的索引使用情况,若几个月都不用的索引,且采取的时间包含多次全部业务,那一直没用的索引是否可以删除呢?(要根据情况定) DBA_HIST_SQL_PLAN/GV$SQL_PLAN...等ORACLE自带的视图的好处: ①、可以了解一定时间的索引情况...能对现有索引了解下也是件好事; ②、是以月为单位检查索引的使用情况(邮件反馈)且维护索引 的基础; 注意一点,删除索引为了保险最好至少已月为单位去监控 一直不用的索引进行清理维护;
大体思路: 上文有讲到ORACLE自带的视图可以读取近期的索引使用情况; 所以整个存储很简单每日或每几个小时 定时将[ORACLE自带的视图可以读取近期的索引使用情况]的视图进行INSERT一个表... [本文用的GV$SQL_PLAN视图] 以月为单位的去统计下使用情况....再自动发邮件给各组组长分析下...
大体思路看完了相比您应该也知道怎么做了...废话不多说了..往下走吧....
*/
PROCEDURE P_INSERT(USERNAME VARCHAR2, USERNAME1 VARCHAR2, USERNAME2 VARCHAR2) IS --传入需要统计的索引的所在用户 T_EXIST NUMBER(3 );
BEGIN
-- 判断 保留每日或每几个小时 GV$SQL_PLAN的中间表MHQ_SQL_PLAN是否存在.. -- 不存在直接路径创建表,,存在直接INSERT
SELECT COUNT (1) INTO T_EXIST FROM USER_TABLES T WHERE T.TABLE_NAME = 'MHQ_SQL_PLAN' AND ROWNUM = 1 ;
IF T_EXIST = 0 THEN EXECUTE IMMEDIATE (' CREATE TABLE MHQ_SQL_PLAN TABLESPACE USERS NOLOGGING AS SELECT P.SQL_ID, P.OBJECT_NAME FROM GV$SQL_PLAN P WHERE TO_CHAR(P.TIMESTAMP, ''YYYY-MM-DD HH24'') > TO_CHAR(SYSDATE - 12 / 24, ''YYYY-MM-DD HH24'')--12个小时内 AND OBJECT_NAME IS NOT NULL AND P.OPERATION = ''INDEX'' AND OBJECT_OWNER IN (''' || UPPER(USERNAME) || ''',''' || UPPER(USERNAME1) || ''',''' || UPPER(USERNAME2) || ''')' );
ELSE NULL; EXECUTE IMMEDIATE (' INSERT INTO MHQ_SQL_PLAN NOLOGGING SELECT P.SQL_ID, P.OBJECT_NAME FROM GV$SQL_PLAN P WHERE TO_CHAR(P.TIMESTAMP, ''YYYY-MM-DD HH24'') > TO_CHAR(SYSDATE - 12 / 24, ''YYYY-MM-DD HH24'') --12个小时内 AND OBJECT_NAME IS NOT NULL AND P.OPERATION = ''INDEX'' AND OBJECT_OWNER IN (''' || UPPER(USERNAME) || ''',''' || UPPER(USERNAME1) || ''',''' || UPPER(USERNAME2) || ''')' );
COMMIT;
END IF ;
--判断SYSDATE是否是当月的最后一天 --如果今天是最后月底将通过MHQ_SQL_PLAN表统计使用比..
IF TO_CHAR(LAST_DAY(SYSDATE ), 'YYYY_MM_DD') /*LASTDAY*/ != TO_CHAR( SYSDATE, 'YYYY_MM_DD' ) /*TODAY*/ THEN NULL; ELSE
P_IDX_CHECK.P_ANALYZE(USERNAME, USERNAME1, USERNAME2); --通过MHQ_SQL_PLAN表统计使用比 步骤
END IF ;
END P_INSERT;
/* **通过MHQ_SQL_PLAN表统计使用比 步骤 一个月用到索引的记录已记录到MHQ_SQL_PLAN表中; 但该表不包含未使用的索引记录,所以需要与DBA_INDEXES 进行关联(依据DBA_INDEXES查询 ∴DBA_INDEXES LEFT JOIN MHQ_SQL_PLAN) 继续完善 带上表名称和字段名称...具体看游标处
*/
PROCEDURE P_ANALYZE(USERNAME VARCHAR2, USERNAME1 VARCHAR2, USERNAME2 VARCHAR2) IS P_TABLE_NAME VARCHAR2(100 ); P_INDEX_NAME VARCHAR2(100 ); P_COLUMN_NAME VARCHAR2(4000 ); P_COUNT NUMBER(10 ); --一个月索引的使用次数量
CURSOR C_CONS IS --游标 SELECT IC.TABLE_NAME, TO_CHAR(WM_CONCAT(IC.COLUMN_NAME)) COLUMN_NAME, I.INDEX_NAME, ( CASE WHEN P.COUNT_ IS NULL THEN 0 ELSE COUNT_ END) COUNT_ FROM (SELECT INDEX_NAME FROM DBA_INDEXES I WHERE I.OWNER IN (UPPER(USERNAME), UPPER(USERNAME1), UPPER(USERNAME2))) I LEFT JOIN (SELECT OBJECT_NAME, COUNT(1 ) COUNT_ FROM MHQ_SQL_PLAN P GROUP BY P.OBJECT_NAME) P ON I.INDEX_NAME = P.OBJECT_NAME JOIN DBA_IND_COLUMNS IC ON I.INDEX_NAME = IC.INDEX_NAME AND IC.TABLE_OWNER IN (UPPER(USERNAME), UPPER(USERNAME1), UPPER(USERNAME2)) GROUP BY IC.TABLE_NAME, I.INDEX_NAME, COUNT_ ORDER BY COUNT_, TABLE_NAME;
BEGIN
--打印各列注释,避免长度不一,看着乱,特加右对齐函数RPAD DBMS_OUTPUT.PUT_LINE(RPAD( 'TABLE_NAME', 31 , ' ') || RPAD( 'INDEX_NAME', 31 , ' ') || RPAD( 'COLUMN_NAME', 55 , ' ') || RPAD( 'COUNT', 10 , ' ')); DBMS_OUTPUT.PUT_LINE(RPAD( '-', 31 , '-') || RPAD( '-', 31 , '-') || RPAD( '-', 55 , '-') || RPAD( '-', 10 , '-'));
-- 打印各列注释 接下来是打印索引的分析情况了 -- 避免如下报错 加DBMS_OUTPUT.ENABLE -- ERRORS RAISED: -- -20000, ORU-10027: BUFFER OVERFLOW, LIMIT OFBYTES. -- -20000, ORU-10028:LINE LENGTH OVERFLOW, LIMIT OF 32767 BYTES PER LINE. DBMS_OUTPUT.ENABLE( 1000000); -- 仅仅是设置整个过程的全部输出缓冲区大小,不能控制是否在客户端显示输出信息。
--ENABLE具体信息: -- PROCEDURE ENABLE (BUFFER_SIZE IN INTEGER DEFAULT 20000); -- PRAGMA RESTRICT_REFERENCES(ENABLE,WNDS,RNDS); -- ENABLE CALLS TO PUT, PUT_LINE, NEW_LINE, GET_LINE AND GET_LINES. -- CALLS TO THESE PROCEDURES ARE NOOPS IF THE PACKAGE HAS -- NOT BEEN ENABLED. SET DEFAULT AMOUNT OF INFORMATION TO BUFFER. -- CLEANUP DATA BUFFERED FROM ANY DEAD SESSIONS. MULTIPLE CALLS TO -- ENABLE ARE ALLOWED. -- INPUT PARAMETERS: -- BUFFER_SIZE -- AMOUNT OF INFORMATION, IN BYTES, TO BUFFER. VARCHAR2, NUMBER AND -- DATE ITEMS ARE STORED IN THEIR INTERNAL REPRESENTATION. THE -- INFORMATION IS STORED IN THE SGA. AN ERROR IS RAISED IF THE -- BUFFER SIZE IS EXCEEDED. IF THERE ARE MULTIPLE CALLS TO ENABLE, -- THEN THE BUFFER_SIZE IS GENERALLY THE LARGEST OF THE VALUES -- SPECIFIED, AND WILL ALWAYS BE >= THAN THE SMALLEST VALUE -- SPECIFIED. CURRENTLY A MORE ACCURATE DETERMINATION IS NOT -- POSSIBLE. THE MAXIMUM SIZE IS 1,000,000, THE MINIMUM IS 2000.
FOR P_C_CONS IN C_CONS LOOP --利用C_CONS游标对应列值 P_TABLE_NAME := P_C_CONS.TABLE_NAME; P_INDEX_NAME := P_C_CONS.INDEX_NAME; P_COLUMN_NAME := P_C_CONS.COLUMN_NAME; P_COUNT := P_C_CONS.COUNT_;
--循环打印索引1个月的使用情况 详情 DBMS_OUTPUT.PUT_LINE(RPAD(P_TABLE_NAME, 31, ' ' ) || RPAD(P_INDEX_NAME, 31, ' ' ) || RPAD(P_COLUMN_NAME, 56, ' ' ) || RPAD(P_COUNT, 11, ' ' ));
--PUT_LINE具体信息 -- PROCEDURE PUT_LINE(A VARCHAR2); -- PRAGMA RESTRICT_REFERENCES(PUT_LINE,WNDS,RNDS); -- PUT A PIECE OF INFORMATION IN THE BUFFER FOLLOWED BY AN END-OF-LINE -- MARKER. WHEN RETRIEVED BY GET_LINE(S), THE NUMBER AND DATE ITEMS -- WILL BE FORMATED WITH TO_CHAR USING THE DEFAULT FORMATS. IF YOU -- WANT ANOTHER FORMAT THEN FORMAT IT EXPLICITLY. GET_LINE(S) RETURN -- "LINES" AS DELIMITED BY "NEWLINES". SO EVERY CALL TO PUT_LINE OR -- NEW_LINE WILL GENERATE A LINE THAT WILL BE RETURNED BY GET_LINE(S). -- INPUT PARAMETERS: -- A -- ITEM TO BUFFER -- ERRORS RAISED: -- -20000, ORU-10027: BUFFER OVERFLOW, LIMIT OFBYTES. -- -20000, ORU-10028:LINE LENGTH OVERFLOW, LIMIT OF 32767 BYTES PER LINE.
--PUT_LINE另种方式(UTL_FILE)HTTP://DOCS.ORACLE.COM/CD/E11882_01/APPDEV.112/E40758/U_FILE.HTM#ARPLS72681
END LOOP ;
END P_ANALYZE;
-- 扩展: -- DBMS_OUTPUT.ENABLE 和 SET SERVEROUTPUT ON 区别 -- HTTPS://ASKTOM.ORACLE.COM/PLS/ASKTOM/F?P=100:11:0::::P11_QUESTION_ID:1968769482859
END P_IDX_CHECK; /
|
② 调用存储 [root@lottery idx_check]# cat idx_check.sql / set serveroutput on begin -- Call the procedure sys.p_idx_check.p_insert(username => 'FWSC', username1 => 'FWSB', username2 => 'FWSW'); end; / exit; |
③ 1,2步骤脚本 [root@lottery idx_check]# cat idx_check.sh #!/bin/sh #su - oracle echo echo echo |
④ 发邮件[日期判断脚本] [root@lottery idx_check]# cat day_check.sh #!/bin/sh today=`date +%d` last_day=`cal | xargs | awk '{print $NF}'` if [ "$today" = "$last_day" ]; then /bin/mail -s "ORACLE_RAC环境以月为单位检查索引的使用情况(邮件反馈)" QQ@qq.com < /u01/idx_check/idx_check_RAC.log -- -f QQ1@qq.com # -s 主题 # QQ@qq.com => 收件人 # QQ1@qq.com => 发件人 fi [root@lottery idx_check]# |
⑤ 定时任务 [root@lottery idx_check]# crontab -l #索引检查 55 12,23 * * * /bin/sh /u01/idx_check/idx_check.sh > /u01/idx_check/idx_check_RAC.log#避免gv$sqlarea时间久的被清除,可每12,23:55追加sql_plan #发送邮件 58 23 * * * /bin/sh /u01/idx_check/day_check.sh |
原文链接:Oracle检查索引的使用情况,转载请注明来源!