监控索引使用次数及使用时间

        我们都知道索引监控，但是索引监控只能知道监控的索引是否被使用，并不能得到索引使用率。其实我们可以利用执行计划来得到索引使用率，只是，如果语句使用了绑定变量，执行了语句多次，都的的确确使用了索引，但是因为执行计划是一致的，且一直放在库缓存中，因此统计的索引使用次数为1次。使用该方法是通过v$sql_plan中获取索引使用率。
        在10g之后，可以利用awr来获取。视图为dba_hist_sql_plan，使用方法和v$sql_plan一样。awr保存的执行计划中，并不是每条语句都会保存，所以做测试的时候难免出问题，   因为awr默认是保存topsql语句靠前的，如果做测试，可以调整下参数
exec dbms_workload_repository.modify_snapshot_settings(topnsql=>'MAXIMUM');

----后面，我会贴从执行计划中获取索引使用率的脚本

----这里先用我自己的方法去得到索引使用率和使用时间：
思路：开启索引监控后，使用索引一次后就会修改 USED 列为 YES ，而再次开启索引监控后，USED列并又会变为NO，因此脚本就是利用该特性，创建一张记录表，用于记录索引使用的，然后重新开启索引监控。

----1.开启索引监控

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' MONITORING USAGE;' enable_monitor,

       'ALTER INDEX ' || owner || '.' || index_name ||

       ' NOMONITORING USAGE;' disable_monitor

  FROM dba_indexes

 WHERE INDEX_TYPE != 'LOB'

   and owner IN

       (SELECT username FROM dba_users WHERE account_status = 'OPEN')

   AND owner NOT IN ('SYS',

                     'SYSTEM',

                     'PERFSTAT',

                     'MGMT_VIEW',

                     'MONITOR',

                     'SYSMAN',

                     'DBSNMP')

   AND owner not like '%SYS%';

 

----为什么要这样来创建，可以找找v$object_usage该视图是怎么创建的
=============》

ALTER INDEX SCOTT.SHALL_ID_ZHONG MONITORING USAGE;

ALTER INDEX SCOTT.PK_EMP MONITORING USAGE;

ALTER INDEX SCOTT.PK_DEPT MONITORING USAGE;

ALTER INDEX HR.EMP_EMP_ID_PK MONITORING USAGE;

 

----2.查看索引监控情况

select * from v$object_usage;

select * from (select z.name||'.'||io.name index_named, t.name table_named,

        decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitord,

        decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,

        ou.start_monitoring,

        ou.end_monitoring

 from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou  ,sys.user$ z

 Where  i.obj# = ou.obj#

   and io.obj# = ou.obj#    And io.owner#=z.user#

   and t.obj# = i.bo#   )

  Order By 4 Desc,2 Desc;

 

----3.创建记录索引使用表

create table db_moniter_index as (

select z.name user_named,io.name index_named, t.name table_named,

        decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitord,

        decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,

        ou.start_monitoring,

        ou.end_monitoring

 from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou  ,sys.user$ z

 Where  i.obj# = ou.obj#

   and io.obj# = ou.obj#    And io.owner#=z.user#

   and t.obj# = i.bo# and 1=2);

 

----添加一个字段，记录日期

alter table db_moniter_index add use_date date default sysdate;

 

select * from db_moniter_index;

 

----4.创建存储过程

----用于统计索引时间

create or replace procedure p_moniter_index as

begin

  insert into sys.db_moniter_index

    select z.name,

           io.name index_named,

           t.name table_named,

           decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitord,

           decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,

           ou.start_monitoring,

           ou.end_monitoring,

           sysdate

      from sys.obj$         io,

           sys.obj$         t,

           sys.ind$         i,

           sys.object_usage ou,

           sys.user$        z

     Where i.obj# = ou.obj#

       and io.obj# = ou.obj#

       And io.owner# = z.user#

       and t.obj# = i.bo#

       and bitand(i.flags, 65536) <> 0

       and bitand(ou.flags, 1) <> 0;

  --and z.name='监控用户';

  commit;

  for v_sql in (select z.name || '.' || io.name aa

                  from sys.obj$         io,

                       sys.obj$         t,

                       sys.ind$         i,

                       sys.object_usage ou,

                       sys.user$        z

                 where i.obj# = ou.obj#

                   and io.obj# = ou.obj#

                   And io.owner# = z.user#

                   and t.obj# = i.bo#

                   and ou.flags = 1) loop

    --  DBMS_Utility.Exec_DDL_Statement(v_sql.aa);

    execute immediate 'alter index ' || v_sql.aa || ' monitoring usage';

  end loop;

end;

 

----5.设置job为每5分钟监控一次记录

var job1 number

 

begin

  dbms_job.submit(:job1,'p_moniter_index;',sysdate,'sysdate+5/1440');

end;

/

 

----手动执行一次job

exec dbms_job.run(:job1);

 

select * from dba_jobs;    ----找到刚刚执行的job号为63

 

----6.开始测试

select * from db_moniter_index;

select * from v$object_usage;

select * from (select z.name||'.'||io.name index_named, t.name table_named,

        decode(bitand(i.flags, 65536), 0, 'NO', 'YES') monitord,

        decode(bitand(ou.flags, 1), 0, 'NO', 'YES') used,

        ou.start_monitoring,

        ou.end_monitoring

 from sys.obj$ io, sys.obj$ t, sys.ind$ i, sys.object_usage ou  ,sys.user$ z

 Where  i.obj# = ou.obj#

   and io.obj# = ou.obj#    And io.owner#=z.user#

   and t.obj# = i.bo#   )

  Order By 4 Desc,2 Desc;

 

----使用索引：

select count(id) from scott.shall;

select count(*) from scott.dept;

select employee_id from hr.employees where employee_id=100;

 

----等job执行，或者手动触发job执行

begin

 dbms_job.run(63);

end;

 

----运行job后，再次执行语句

select count(id) from scott.shall;

select count(*) from scott.dept;

select * from hr.employees;

----再次调用job

begin

 dbms_job.run(63);

end;

----再次执行语句

select * from scott.shall;

select count(*) from scott.dept;

select * from hr.employees;

----再次调用job

begin

 dbms_job.run(63);

end;

 

----最后查看下索引使用次数

select user_named,index_named,table_named,count(*) from db_moniter_index group by user_named,index_named,table_named;

SCOTT                PK_DEPT           DEPT         3

SCOTT                SHALL_ID_ZHONG         SHALL       2

HR      EMP_EMP_ID_PK           EMPLOYEES            1

------当然利用监控索引的方法会有一定开销，所以如果不是一定要统计重要表的索引使用次数、使用时间，不必这样来操作


下面的从执行计划中获取的统计记录：
reference    小麦苗： http://blog.itpub.net/26736162/viewspace-2120752/

（1） 从v$sql_plan中获取

 WITH Q AS (

        SELECT

            S.OWNER         A_OWNER,

            INDEX_NAME        A_INDEX_NAME,

            INDEX_TYPE        A_INDEX_TYPE,

            SUM(S.bytes) / 1048576  A_MB

         FROM DBA_SEGMENTS S,

            DBA_INDEXES I

         WHERE 

       INDEX_NAME = SEGMENT_NAME

       and s.owner in (select username from dba_users where account_status = 'OPEN')

          and s.owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'MGMT_VIEW', 'MONITOR', 'SYSMAN', 'DBSNMP')

         and s.owner not like '%SYS%'

         GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE

    --     HAVING SUM(S.BYTES) > 1048576 

    )

    SELECT /*+ NO_QUERY_TRANSFORMATION(S) */

        A_OWNER                  OWNER,

          A_INDEX_NAME                INDEX_NAME,

        A_INDEX_TYPE                INDEX_TYPE,

        A_MB                    MB,

        DECODE (OPTIONS, null, '    -',OPTIONS) INDEX_OPERATION,

        COUNT(OPERATION)              NR_EXEC

     FROM Q,

        v$sql_plan d

     WHERE

        D.OBJECT_OWNER(+)= q.A_OWNER AND

        D.OBJECT_NAME(+) = q.A_INDEX_NAME

    GROUP BY

        A_OWNER,

           A_INDEX_NAME,

        A_INDEX_TYPE,

        A_MB,

        DECODE (OPTIONS, null, '    -',OPTIONS)

    ORDER BY

        A_OWNER,

         A_INDEX_NAME,

        A_INDEX_TYPE,

        A_MB DESC,

        NR_EXEC DESC ;

（2）从awr视图dba_hist_sql_plan中获取

 WITH Q AS (

        SELECT

            S.OWNER         A_OWNER,

            INDEX_NAME        A_INDEX_NAME,

            INDEX_TYPE        A_INDEX_TYPE,

            SUM(S.bytes) / 1048576  A_MB

         FROM DBA_SEGMENTS S,

            DBA_INDEXES I

         WHERE 

       INDEX_NAME = SEGMENT_NAME

       and s.owner in (select username from dba_users where account_status = 'OPEN')

          and s.owner NOT IN ('SYS', 'SYSTEM', 'PERFSTAT', 'MGMT_VIEW', 'MONITOR', 'SYSMAN', 'DBSNMP')

         and s.owner not like '%SYS%'

         GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE

    --     HAVING SUM(S.BYTES) > 1048576 

    )

    SELECT /*+ NO_QUERY_TRANSFORMATION(S) */

        A_OWNER                  OWNER,

          A_INDEX_NAME                INDEX_NAME,

        A_INDEX_TYPE                INDEX_TYPE,

        A_MB                    MB,

        DECODE (OPTIONS, null, '    -',OPTIONS) INDEX_OPERATION,

        COUNT(OPERATION)              NR_EXEC

     FROM Q,

        DBA_HIST_SQL_PLAN d

     WHERE

        D.OBJECT_OWNER(+)= q.A_OWNER AND

        D.OBJECT_NAME(+) = q.A_INDEX_NAME

    GROUP BY

        A_OWNER,

           A_INDEX_NAME,

        A_INDEX_TYPE,

        A_MB,

        DECODE (OPTIONS, null, '    -',OPTIONS)

    ORDER BY

        A_OWNER,

         A_INDEX_NAME,

        A_INDEX_TYPE,

        A_MB DESC,

        NR_EXEC DESC;

 

----查看AWR保留的时间段

select to_char (min(BEGIN_INTERVAL_TIME), 'yyyy-mm-dd hh24:mi:ss')

    || '  ----  ' ||

    to_char (max(END_INTERVAL_TIME), 'yyyy-mm-dd hh24:mi:ss')

from dba_hist_snapshot;

 

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

转载于:http://blog.itpub.net/30130773/viewspace-2123715/