|
① 执行存储
[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 OF <BUF_LIMIT> BYTES.
-- -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 OF <BUF_LIMIT> BYTES.
-- -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]# |
|
②
调用存储
/ 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 ....................BEGIN......................... date=`date +%Y_%m_%d` echo $date echo echo .........oracle ................ echo #echo .........EXEC PROCEDURE........... #su - oracle << EOF #export NLS_LANG=‘SIMPLIFIED CHINESE_CHINA.AL32UTF8‘ #sqlplus "地址" @/u01/idx_check/p_idx_check.sql> /u01/idx_check/p_idx_check.log #EOF echo echo .........CALL PROCEDURE........... su - oracle << EOF export NLS_LANG=‘SIMPLIFIED CHINESE_CHINA.AL32UTF8‘ sqlplus "地址" @/u01/idx_check/idx_check.sql> /u01/idx_check/idx_check_RAC.log EOF echo echo .....................END........................... |
|
④ 发邮件[日期判断脚本]
[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 |
原文：http://blog.itpub.net/28602568/viewspace-1362044/