ITPub博客

首页 > 数据库 > Oracle > 索引的监控记录

索引的监控记录

原创 Oracle 作者:静以致远√团团 时间:2015-08-14 11:34:43 0 删除 编辑
标识索引的使用情况  
1.启用索引监控  
  alter index emp_ename_idx monitoring usage;  
2.执行相关查询  
 select ename,job ,sal from scott.emp  
   where ename like 'C%';  
3.查看索引是否使用  
select * from v$object_usage;  
4.禁用索引监控  
 alter index emp_ename_idx nomonitoring usage;  


SQL查看索引
当前session使用索引的记录
select 'select '''||index_name||'''as index_name,count(1) as session_count 
from v$sql_plan where object_name like ''%'||index_name||'%''
union all'
from dba_indexes
where table_name='T_RESV_BASE';

历史中使用索引的记录
select 'select '''||index_name||''' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
''%'||index_name||'%''
union all'
from dba_indexes
where table_name='T_RESV_BASE';

整理结果执行查询
with
his_count as (
select 'RESV_IDX_BOOKUSER' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_IDX_BOOKUSER%'
union all
select 'RESV_IDX_PID' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_IDX_PID%'
union all
select 'RESV_IDX_GUASTATUS' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_IDX_GUASTATUS%'
union all
select 'RESV_IDX_PRESTATUS' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_IDX_PRESTATUS%'
union all
select 'RESV_IDX_PMSNO' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_IDX_PMSNO%'
union all
select 'RESV_STATUS_TIME_INDEX' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_STATUS_TIME_INDEX%'
union all
select 'PK_T_R3' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%PK_T_R3%'
union all
select 'RESV_CNF_INDEX' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_CNF_INDEX%'
union all
select 'RESV_OUTCNF_INDEX' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_OUTCNF_INDEX%'
union all
select 'RESV_SEND_INDEX' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_SEND_INDEX%'
union all
select 'RESV_RP_INDEX' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_RP_INDEX%'
union all
select 'RESV_UPDATETIMESTAMP' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_UPDATETIMESTAMP%'
union all
select 'IDX_RESV_FLAG' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%IDX_RESV_FLAG%'
union all
select 'RESV_FREEEZE_IDX' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_FREEEZE_IDX%'
union all
select 'RESV_IDX_FIRSTCONFIRMTIME' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_IDX_FIRSTCONFIRMTIME%'
union all
select 'RESV_IDX_BOOKDATE' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_IDX_BOOKDATE%'
union all
select 'RESV_IDX_INDATE' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_IDX_INDATE%'
union all
select 'RESV_IDX_OUTDATE' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_IDX_OUTDATE%'
union all
select 'RESV_IDX_SLUICE' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_IDX_SLUICE%'
union all
select 'RESV_CHECK_OUTCNFNUM' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_CHECK_OUTCNFNUM%'
union all
select 'RESV_IDX_BOOKDAY' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_IDX_BOOKDAY%'
union all
select 'RESV_IDX_INDAY' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_IDX_INDAY%'
union all
select 'RESV_IDX_RATECODE' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_IDX_RATECODE%'
union all
select 'RESV_HOLDTIME_IDX' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_HOLDTIME_IDX%'
union all
select 'RESV_ACPAYMENT_IDX' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_ACPAYMENT_IDX%'
union all
select 'RESV_IDX_PROP_IATA' as index_name,count(1) as his_count
  FROM dba_hist_active_sess_history a
  JOIN dba_hist_sqltext b
    ON a.sql_id = b.sql_id
  JOIN DBA_HIST_SQL_PLAN c
    ON a.sql_id = c.sql_id
    where c.object_name like
'%RESV_IDX_PROP_IATA%'
),
session_count as (
select 'RESV_IDX_BOOKUSER'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_IDX_BOOKUSER%'
union all
select 'RESV_IDX_PID'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_IDX_PID%'
union all
select 'RESV_IDX_GUASTATUS'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_IDX_GUASTATUS%'
union all
select 'RESV_IDX_PRESTATUS'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_IDX_PRESTATUS%'
union all
select 'RESV_IDX_PMSNO'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_IDX_PMSNO%'
union all
select 'RESV_STATUS_TIME_INDEX'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_STATUS_TIME_INDEX%'
union all
select 'PK_T_R3'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%PK_T_R3%'
union all
select 'RESV_CNF_INDEX'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_CNF_INDEX%'
union all
select 'RESV_OUTCNF_INDEX'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_OUTCNF_INDEX%'
union all
select 'RESV_SEND_INDEX'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_SEND_INDEX%'
union all
select 'RESV_RP_INDEX'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_RP_INDEX%'
union all
select 'RESV_UPDATETIMESTAMP'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_UPDATETIMESTAMP%'
union all
select 'IDX_RESV_FLAG'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%IDX_RESV_FLAG%'
union all
select 'RESV_FREEEZE_IDX'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_FREEEZE_IDX%'
union all
select 'RESV_IDX_FIRSTCONFIRMTIME'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_IDX_FIRSTCONFIRMTIME%'
union all
select 'RESV_IDX_BOOKDATE'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_IDX_BOOKDATE%'
union all
select 'RESV_IDX_INDATE'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_IDX_INDATE%'
union all
select 'RESV_IDX_OUTDATE'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_IDX_OUTDATE%'
union all
select 'RESV_IDX_SLUICE'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_IDX_SLUICE%'
union all
select 'RESV_CHECK_OUTCNFNUM'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_CHECK_OUTCNFNUM%'
union all
select 'RESV_IDX_BOOKDAY'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_IDX_BOOKDAY%'
union all
select 'RESV_IDX_INDAY'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_IDX_INDAY%'
union all
select 'RESV_IDX_RATECODE'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_IDX_RATECODE%'
union all
select 'RESV_HOLDTIME_IDX'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_HOLDTIME_IDX%'
union all
select 'RESV_ACPAYMENT_IDX'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_ACPAYMENT_IDX%'
union all
select 'RESV_IDX_PROP_IATA'as index_name,count(1) as session_count
from v$sql_plan where object_name like '%RESV_IDX_PROP_IATA%'
)
select a.index_name,a.his_count his_count,b.session_count session_count
from his_count a,session_count b
where a.index_name=b.index_name
order by his_count,session_count

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29320885/viewspace-1771335/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
每个人都有梦想,去实现吧!

注册时间:2013-11-14

  • 博文量
    164
  • 访问量
    2103578