ITPub博客

首页 > 数据库 > Oracle > Oracle 以月为单位检查索引的使用情况(邮件反馈)

Oracle 以月为单位检查索引的使用情况(邮件反馈)

原创 Oracle 作者:maohaiqing0304 时间:2014-12-09 14:59:02 1 删除 编辑


标题:Oracle 以月为单位检查索引的使用情况(邮件反馈)

作者:lōττéry©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]


  执行存储
  调用存储
  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 OF 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 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]# 



调用存储
[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 ....................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


注释:
--本文只是针对索引使用进行简单统计
--可以针对filter/MERGE JOIN CARTESIAN/index skip scan 对其整理  【表名字可以和索引名字相同】


  【源于本人笔记】 若有书写错误,表达错误,请指正...


此条目发表在 Oracle 分类目录。将固定连接加入收藏夹。


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28602568/viewspace-1362044/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
擅长SQL编写及SQL优化,分析瓶颈,性能调优、故障处理,根据实际情况定制备份策略; 擅长编写脚本来实现自动化功能,600+SQL优化经验案例,为人热爱学习,喜欢钻研技术,对工作认真负责。

注册时间:2013-03-13

  • 博文量
    121
  • 访问量
    2295746