程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle 監控索引使用率腳本分享，oracle索引

Oracle 監控索引使用率腳本分享，oracle索引

編輯：Oracle教程

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, [email protected]
 
 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索引的使用完全解析

通過監控數據庫索引的使用，釋放那些未被使用的索引，從而節省維護索引的開銷，優化性能。 1、在oracle8i中，確定使用了那個索引的方法意味著要對存在語共享SQL區中的所有語句運行EXPLIAN PALN，然後查詢計劃表中的OPERATION列，從而識別有OBJECT_OWNER和OBJECT_NAME列所確定的那個索引上的索引訪問。下面是一個監控索引使用的腳本，這個腳本僅僅是一個樣品，在某種條件下成立：條件：運行這個腳本的用戶擁有權限解釋所有的v$sqlarea中的sql，除了不是被SYS裝載的。plan_table.remarks能夠別用來決定與特權習慣的錯誤。對所有的共享池中SQL，參數OPTIMIZER_GOAL是一個常量，無視v$sqlarea.optimizer_mode。兩次快照之間，統計資料被再次分析過。沒有語句別截斷。所有的對象都是局部的。所有被引用的表或視圖或者是被運行腳本的用戶所擁有，或者完全有資格的名字或同義詞被使用。自從上次快照以來，沒有不受"歡迎"的語句被沖洗出共享池(例如，在裝載)。腳本: Code: [Copy to clipboard] set echo off Rem Drop and recreate PLAN_TABLE for EXPLAIN PLAN drop table plan_table; create table PLAN_TABLE ( statement_id varchar2(30), timestamp date, remarks varchar2(80), operation varchar2(30), options varchar2(255), object_node varchar2(128), object_owner varchar2(30), object_name varchar2(30), object_instance numeric, object_type varchar2(30), optimizer varchar2(255), search_columns number, id numeric, parent_id numeric, position numeric, cost numeric, cardinality numeric, bytes numeric, other_tag varchar2(255), partition_start varchar2(255), partition_stop varchar2(255), partition_id numeric, other long, distribution varchar2(30), cpu_cost numeric, io_cost numeric, temp_space numeric, access_predicates varchar2(4000), filter_predicates varchar2(4000)); Rem Drop and recreate SQLTEMP for taking a snapshot of the SQLAREA drop table sqltemp; create table sqltemp ( ADDR VARCHAR2 (16), SQL_TEXT VARCHAR2 (2000), DISK_READS NUMBER, EXECUTIONS NUMBER, PARSE_CALLS NUMBER); set ......余下全文>>

oracle 怎監控一個索引被用了多少次，如可以給出方法或sql不勝感激

監控索引被用了多少次，這個我不清楚怎麼實現，但你要監控索引是否被使用你可以按一下方法進行監控。

你要監控索引是否被使用，你需要提前開啟監控索引
alter index *** monitoring usage; -- ***是你的索引名
然後你可以可以對這條索引進行監控了
select * from v$object_usage 這個語句就能進行監控了。
如果在你開啟監控後使用了索引這個視圖會有記錄，否則無記錄