Oracle 监控索引使用率脚本分享_oracle

Oracle提供了索引监控特性来判断索引是否被使用。在Oracle 10g中，收集统计信息会使得索引被监控，在Oracle 11g中该现象不复存在。尽管如此，该方式仅提供的是索引是否被使用。索引被使用的频率未能得以体现。下面的脚本将得到索引的使用率，可以很好的度量索引的使用情况以及根据这个值来判断当前的这些索引是否可以被移除或改进。

1、索引使用频率报告

--运行环境
SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

--获得当前数据库索引的使用频率
SQL> @idx_usage_detail.sql
Enter value for 1: GO_ADMIN
Enter value for 2: 100
                                         Index
Table name           Index name           Index type    Size MB Index operation    Executions
------------------------------ ------------------------------ ------------ ----------- --------------------- ----------
ACC_POS_CASH_PL_TBL_ARC    PK_ACC_POS_CASH_PL_ARCH_TBL  NORMAL     3,328.00 RANGE SCAN          99
                                            SAMPLE FAST FULL SCAN     8
                                            UNIQUE SCAN          3
                                            SKIP SCAN           2
****************************** ****************************** ************ -----------            ----------
sum                                     13,312.00               112

ACC_POS_CASH_TBL_ARC      PK_ACC_POS_CASH_ARCH_TBL    NORMAL     2,560.00 RANGE SCAN          168
                                            UNIQUE SCAN          14
                                            SAMPLE FAST FULL SCAN     12
                                            SKIP SCAN           1
****************************** ****************************** ************ -----------            ----------
sum                                     10,240.00               195

ACC_POS_HIST_TBL        ACC_HIST_TRANS_DATE_IDX    NORMAL      384.00 RANGE SCAN          917
                                            SKIP SCAN          210
                                            SAMPLE FAST FULL SCAN     4
                                            FAST FULL SCAN         1
                PK_ACC_POS_HIST_TBL      NORMAL      192.00 UNIQUE SCAN          7
                                            SAMPLE FAST FULL SCAN     3
                TRANS_NUM_IDX         NORMAL      232.00 RANGE SCAN          41
                                            SAMPLE FAST FULL SCAN     3
                                            FAST FULL SCAN         1
****************************** ****************************** ************ -----------            ----------
sum                                      2,616.00              1,187

ACC_POS_INT_TBL        ACC_POS_INT_10DIG_IDX     FUNCTION-    2,622.00 RANGE SCAN          59
                               BASED NORMAL

                                            SAMPLE FAST FULL SCAN     4
                                            FAST FULL SCAN         2
                PK_ACC_POS_INT_TBL       NORMAL     2,496.00 RANGE SCAN          65
                                            FAST FULL SCAN        53
                                            UNIQUE SCAN          14
                                            SKIP SCAN           13
                                            SAMPLE FAST FULL SCAN     1
****************************** ****************************** ************ -----------            ----------
sum                                     20,346.00               211

ACC_POS_STOCK_TBL_ARC     PK_ACC_POS_STOCK_ARCH_TBL   NORMAL     18,977.00 RANGE SCAN          177
                                            SAMPLE FAST FULL SCAN     10
                                            UNIQUE SCAN          4
                                            SKIP SCAN           3
****************************** ****************************** ************ -----------            ----------
sum                                     75,908.00               194

STK_TBL_ARC          PK_STK_ARCH_TBL        NORMAL      920.00 RANGE SCAN          126
                                            UNIQUE SCAN          38
                                            SKIP SCAN           17
                                            SAMPLE FAST FULL SCAN     2
****************************** ****************************** ************ -----------            ----------
sum                                      3,680.00               183

STK_TBL_LOG          PK_STK_TBL_LOG         NORMAL      480.00 UNIQUE SCAN          56
****************************** ****************************** ************ -----------            ----------
sum                                       480.00                56

TRADE_BROKER_CHRG_TBL_ARC   PK_TRADE_BROKER_CHRG_TBL_ARC  NORMAL      128.00    -            0
                UNI_TDBK_CHRG_ARC       NORMAL      104.00 RANGE SCAN          283
****************************** ****************************** ************ -----------            ----------
sum                                       232.00               283

TRADE_BROKER_JOURNAL_TBL_ARC  IDX_TDBK_JRNL_ARC_ENTRY_DT   NORMAL      168.00    -            0
                IDX_TDBK_JRNL_ARC_INSTRU_ID  NORMAL      144.00 FULL SCAN           1
                IDX_TDBK_JRNL_ARC_STOCK_CD   NORMAL      144.00 FULL SCAN           1
                IDX_TDBK_JRNL_ARC_TRADED_PRICE NORMAL      144.00 FULL SCAN           1
                PK_TRADE_BROKER_JOURNAL_ARC  NORMAL      200.00    -            0
****************************** ****************************** ************ -----------            ----------
sum                                       800.00                3

TRADE_CLIENT_CHRG_TBL_ARC   IDX_TDCL_CHRG_ARC_GRP_REF_ID  NORMAL      704.00 RANGE SCAN         3,537
                PK_TRADE_CLIENT_CHRG_TBL_ARC  NORMAL     1,539.00 RANGE SCAN          24
                                            SAMPLE FAST FULL SCAN     2
                UNI_TDCL_CHRG_ARC       NORMAL     1,216.00 RANGE SCAN         1,103
                                            FAST FULL SCAN         3
                                            SAMPLE FAST FULL SCAN     2
****************************** ****************************** ************ -----------            ----------
sum                                      7,430.00              4,671

TRADE_CLIENT_DTL_TBL_ARC    IDX_TDCL_DTL_ARC_ACTION_N_STUS NORMAL      312.00    -            0
                IDX_TDCL_DTL_ARC_ACT_TD_PRICE NORMAL      184.00 FULL SCAN           1
                IDX_TDCL_DTL_ARC_REF_ID    NORMAL      344.00 RANGE SCAN         4,623
                                            FAST FULL SCAN         1
                                            FULL SCAN           1
                IDX_TDCL_DTL_ARC_TRADED_PRICE NORMAL      184.00    -            0
                PK_TRADE_CLIENT_DTL_TBL_ARC  NORMAL      432.00    -            0
                UNI_TDCL_DTL_ARC_TRADE_DTL_ID NORMAL      272.00    -            0
****************************** ****************************** ************ -----------            ----------
sum                                      2,416.00              4,626

TRADE_CLIENT_TBL_ARC      IDX_TDCL_ARC_ACC_NUM      NORMAL      152.00 RANGE SCAN          534
                IDX_TDCL_ARC_GRP_REF_ID    NORMAL      120.00 RANGE SCAN          550
                                            FAST FULL SCAN         1
                IDX_TDCL_ARC_INPUT_DATE    NORMAL      120.00 RANGE SCAN         7,231
                IDX_TDCL_ARC_PL_STK      NORMAL      144.00 SKIP SCAN          156
                                            RANGE SCAN           3
                                            FULL SCAN           1
                IDX_TDCL_ARC_TRADE_DATE    NORMAL      120.00 RANGE SCAN        12,778
                PK_TRADE_CLIENT_TBL_ARC    NORMAL      160.00 RANGE SCAN          37
                UNI_TDCL_ARC_REF_ID      NORMAL      112.00 UNIQUE SCAN         157
                                            FAST FULL SCAN         8
                                            SAMPLE FAST FULL SCAN     1
****************************** ****************************** ************ -----------            ----------
sum                                      1,560.00              21,457

--Author : Robinson
--Blog  : http://blog.csdn.net/robinson_0612

"Showed only indexes in GO_ADMIN schema whose size > 100 MB in period:"

30.01.2013-07.04.2013

2、结果分析与建议

a、上面的结果列出了当前数据库中schema为GOEX_ADMIN且索引大小大于100MB的索引的使用频率。
b、由于当前的数据库为标准版，没有分区表功能，所以可以看到很多arc结尾的表，且索引很大，如ACC_POS_STOCK_TBL_ARC上索引达到19G。
c、表ACC_POS_CASH_PL_TBL_ARC上的主键PK_ACC_POS_CASH_PL_ARCH_TBL上范围扫描最多，总计被使用次数为112次。
d、对于上述列出的被使用的次数为0的那些索引，应考虑索引的设置是否合理。
e、过大的索引应考虑能否使用索引压缩。
f、最后列出的是报告的schema名称以及索引大小的过滤条件、索引被收集的日期。注，索引列的大小sum求和有些不准确。

3、获得索引使用频率脚本

--该脚本作者为Damir Vadas，感谢Damir Vadas的贡献
robin@SZDB:~/dba_scripts/custom/sql> more idx_usage_detail.sql
/* ---------------------------------------------------------------------------
 CR/TR# :
 Purpose : Shows index usage by execution (find problematic indexes)

 Date  : 22.01.2008.
 Author : Damir Vadas, damir.vadas@gmail.com

 Remarks : run as privileged user
      Must have AWR run because sql joins data from there
      works on 10g >    

      @index_usage SCHEMA MIN_INDEX_SIZE

 Changes (DD.MM.YYYY, Name, CR/TR#):
     25.11.2010, Damir Vadas
           added index size as parameter
     30.11.2010, Damir Vadas
           fixed bug in query

--------------------------------------------------------------------------- */

set linesize 140
set pagesize 160

clear breaks
clear computes

break on TABLE_NAME skip 2 ON INDEX_NAME ON INDEX_TYPE ON MB
compute sum of NR_EXEC on TABLE_NAME SKIP 2
compute sum of MB on TABLE_NAME SKIP 2

SET TIMI OFF
set linesize 140
set pagesize 10000
set verify off
col OWNER noprint
col TABLE_NAME for a30 heading 'Table name'
col INDEX_NAME for a30 heading 'Index name'
col INDEX_TYPE for a15 heading 'Index type'
col INDEX_OPERATION for a21 Heading 'Index operation'
col NR_EXEC for 9G999G990 heading 'Executions'
col MB for 999G990D90 Heading 'Index|Size MB' justify right

    WITH Q AS (
        SELECT
            S.OWNER         A_OWNER,
            TABLE_NAME        A_TABLE_NAME,
            INDEX_NAME        A_INDEX_NAME,
            INDEX_TYPE        A_INDEX_TYPE,
            SUM(S.bytes) / 1048576  A_MB
         FROM DBA_SEGMENTS S,
            DBA_INDEXES I
         WHERE S.OWNER = '&&1'
          AND I.OWNER = '&&1'
          AND INDEX_NAME = SEGMENT_NAME
         GROUP BY S.OWNER, TABLE_NAME, INDEX_NAME, INDEX_TYPE
        HAVING SUM(S.BYTES) > 1048576 * &&2
    )
    SELECT /*+ NO_QUERY_TRANSFORMATION(S) */
        A_OWNER                  OWNER,
        A_TABLE_NAME                TABLE_NAME,
        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_TABLE_NAME,
        A_INDEX_NAME,
        A_INDEX_TYPE,
        A_MB,
        DECODE (OPTIONS, null, '    -',OPTIONS)
    ORDER BY
        A_OWNER,
        A_TABLE_NAME,
        A_INDEX_NAME,
        A_INDEX_TYPE,
        A_MB DESC,
        NR_EXEC DESC
;

PROMPT "Showed only indexes in &&1 schema whose size > &&2 MB in period:"

SET HEAD OFF;
select to_char (min(BEGIN_INTERVAL_TIME), 'DD.MM.YYYY')
    || '-' ||
    to_char (max(END_INTERVAL_TIME), 'DD.MM.YYYY')
from dba_hist_snapshot;

SET HEAD ON
SET TIMI ON

4、补充说明
    脚本使用了2个替代变量，一个是schema，一个是索引的大小。缺省情况下，对于那些较小的索引以及仅仅运行一至两次的sql语句的历史执行计划不会被收集到DBA_HIST_SQL_PLAN。因此执行脚本时索引大小输入的建议值是100。如果需要收集所有的历史sql执行计划来判断索引是否被使用，需要修改statistics_level为all或者修改snapshot的收集策略。收集策略对系统性能有一定的影响，以及耗用大量磁盘空间，因此Prod环境应慎用(UAT和DEV则无妨)。

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索oracle
， 脚本
监控索引使用率
oracle 导出索引脚本、linux cpu使用率脚本、oracle 表空间使用率、oracle查看cpu使用率、oracle查看内存使用率，以便于您获取更多的相关知识。

时间： 2024-09-20 12:38:05

Oracle 监控索引使用率脚本分享_oracle的相关文章

Oracle监控索引怎么使用

Oracle监控索引使用   Oracle提供一个监控索引的方法,来确定索引是否被使用.如果索引没有被使用,就可以删除它们以减少不必要的语句的开销.因为表上的大量不必要的索引可能会降低DML语句的性能,给数据库性能产生压力.所以生产环境上,以根据业务增长情况定期监控.分析数据库索引的使用,特别是一些大表上的索引,提升数据库事务提交的性能.   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 --查看表上的索引 SQL> Selectindex

Oracle重建索引Shell脚本、SQL脚本分享_oracle

索引是提高数据库查询性能的有力武器.没有索引,就好比图书馆没有图书标签一样,找一本书自己想要的书比登天还难.然而索引在使用的过程中,尤其是在批量的DML的情形下会产生相应的碎片,以及B树高度会发生相应变化,因此可以对这些变化较大的索引进行重构以提高性能.N久以前Oracle建议我们定期重建那些高度为4,已删除的索引条目至少占有现有索引条目总数的20%的这些表上的索引.但Oracle现在强烈建议不要定期重建索引.具体可以参考文章:Oracle 重建索引的必要性.尽管如此重建索引还是有必要的,只是不

Oracle索引质量介绍和分析脚本分享_oracle

索引质量的高低对数据库整体性能有着直接的影响.良好高质量的索引使得数据库性能得以数量级别的提升,而低效冗余的索引则使得数据库性能缓慢如牛,即便是使用高档的硬件配置.因此对于索引在设计之初需要经过反复的测试与考量.那对于已经置于生产环境中的数据库,我们也可以通过查询相关数据字典得到索引的质量的高低,通过这个分析来指导如何改善索引的性能.下面给出了演示以及索引创建的基本指导原则,最后给出了索引质量分析脚本. 1.查看索引质量 --获取指定schema或表上的索引质量信息报告 gx_adm@CABO3

Oracle 监控索引的使用率

    Oracle提供了索引监控特性来判断索引是否被使用.在Oracle 10g中,收集统计信息会使得索引被监控,在Oracle 11g中该现象不复存在.尽管如此,该方式仅提供的是索引是否被使用.索引被使用的频率未能得以体现.下面的脚本将得到索引的使用率,可以很好的度量索引的使用情况以及根据这个值来判断当前的这些索引是否可以被移除或改进.   1.索引使用频率报告 --运行环境 SQL> select * from v$version where rownum<2; BANNER -----

Oracle中查看表空间使用率的SQL脚本分享_oracle

复制代码 代码如下: /* Formatted on 2012/5/31 14:51:13 (QP5 v5.185.11230.41888) */ SELECT D.TABLESPACE_NAME,        SPACE || 'M' "SUM_SPACE(M)",        BLOCKS "SUM_BLOCKS",        SPACE - NVL (FREE_SPACE, 0) || 'M' "USED_SPACE(M)",   

Oracle中检查外键是否有索引的SQL脚本分享_oracle

复制代码 代码如下: COLUMN COLUMNS format a30 word_wrapped COLUMN tablename format a15 word_wrapped COLUMN constraint_name format a15 word_wrapped SELECT TABLE_NAME,        CONSTRAINT_NAME,        CNAME1 || NVL2(CNAME2, ',' || CNAME2, NULL) ||        NVL2(CNA

Oracle中实现MySQL show index from table命令SQL脚本分享_oracle

实验数据初始化: 复制代码 代码如下: create table t as select * from hr.employees; create index inx_t1 on t(employee_id,first_name desc,last_name); create index inx_t2 on t(job_id,hire_date); 显示该表所有索引的信息. 以dba登录 复制代码 代码如下: set linesize 300; set pagesize 100; col c1 f

Oracle中查看慢查询进度的脚本分享_oracle

Oracle一个大事务的SQL往往不知道运行到了哪里,可以使用如下SQL查看执行进度. 复制代码 代码如下: set linesize 400; set pagesize 400; col sql_text format a100; col opname format a15; SELECT se.sid,          opname,          TRUNC (sofar / totalwork * 100, 2) pct_work,          elapsed_seconds

Oracle中查看引起Session阻塞的2个脚本分享_oracle

用户A执行删除,但是没有提交. 复制代码 代码如下: SQL> delete from test where object_id<10; 已删除8行. 用户B执行删除或者更新id<10的记录,则被阻塞. 复制代码 代码如下: SQL> update test set flag='N' where object_id<10; 遇到这种阻塞,首先需要确定问题.可以使用以下脚本. 复制代码 代码如下: select t2.username,t2.sid,t2.serial#,t2.