Oracle检查索引的使用情况

/

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;

/