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则无妨)。

(0)
上一篇 2022年3月22日
下一篇 2022年3月22日

相关推荐