導讀:1建站知識這篇文章主要介紹了Oracle 監(jiān)控索引使用率腳本分享,本文給出的腳本將得到索引的使用率，可以很好的度量索引的使用情況以及根據(jù)這個值來判斷當前的這些索引是否可以營銷型網(wǎng)站建設網(wǎng)站推廣優(yōu)化seo。

這篇文章主要介紹了Oracle 監(jiān)控索引使用率腳本分享,本文給出的腳本將得到索引的使用率，可以很好的度量索引的使用情況以及根據(jù)這個值來判斷當前的這些索引是否可以被移除或改進,需要的朋友可以參考下 Oracle提供了索引監(jiān)控特性來判斷索引是否被使用。在Oracle 10g中，收集統(tǒng)計信息會使得索引被監(jiān)控，在Oracle 11g中該現(xiàn)象不復存在。盡管如此，該方式僅提供的是索引是否被使用。索引被使用的頻率未能得以體現(xiàn)。下面的腳本將得到索引的使用率，可以很好的度量索引的使用情況以及根據(jù)這個值來判斷當前的這些索引是否可以被移除或改進。 1、索引使用頻率報告 --運行環(huán)境 SQL> select * from v$version where rownum<2; BANNER ---------------------------------------------------------------- Oracle Database 10g Release 10.2.0.3.0 - 64bit Production --獲得當前數(shù)據(jù)庫索引的使用頻率 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  &nbs網(wǎng)站seo優(yōu)化培訓p;  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        網(wǎng)站優(yōu)化seo培訓;  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_Iseo網(wǎng)站優(yōu)化D    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、結(jié)果分析與建議 a、上面的結(jié)果列出了當前數(shù)據(jù)庫中schema為GOEX_ADMIN且索引大小大于100MB的索引的使用頻率。 b、由于當前的數(shù)據(jù)庫為標準版，沒有分區(qū)表功能，所以可以看到很多arc結(jié)尾的表，且索引很大，如ACC_POS_STOCK_TBL_ARC上索引達到19G。 c、表ACC_POS_CASH_PL_TBL_ARC上的主鍵PK_ACC_POS_CASH_PL_ARCH_TBL上范圍掃描最多，總計被使用次數(shù)為112次。 d、對于上述列出的被使用的次數(shù)為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語句的歷史執(zhí)行計劃不會被收集到DBA_HIST_SQL_PLAN。因此執(zhí)行腳本時索引大小輸入的建議值是100。如果需要收集所有的歷史sql執(zhí)行計劃來判斷索引是否被使用，需要修改statistics_level為all或者修改snapshot的收集策略。收集策略對系統(tǒng)性能有一定的影響，以及耗用大量磁盤空間，因此Prod環(huán)境應慎用(UAT和DEV則無妨)。相關營銷型網(wǎng)站建設網(wǎng)站推廣優(yōu)化seo。

關鍵詞標簽: 腳本 使用率