索引的监控记录

标识索引的使用情况  

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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29320885/viewspace-1771335/