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则无妨)。
Forward from http://blog.csdn.net/leshami/article/details/8823133
Author: Leshami