 |
|
Display rows processed and disk reads for an index
Oracle Tips by Burleson Consulting |
Oracle 11g and Expert Systems Technology
Earl Shaffer of OracleMan
Consulting offers this handy script to display the disk reads and rows
processed for a specific index.
clear break
col begintvtm heading 'Interval|Time|Begin' format a8
col objnm heading 'Index Name'
format a30
col dskrdstot heading 'Disk|Reads' format 99,999,999
col rwsprctot heading 'Proc-ed|Rows' format 99,999,999
break on begintvtm
spool indexsqlsnap.lis
select
to_char(s.begin_interval_time,'mm-dd
hh24') begintvtm ,
p.object_name objnm ,
sum(t.disk_reads_total) dskrdstot,
sum(t.rows_processed_total)
rwsprctot
from
dba_hist_sql_plan p,
dba_hist_sqlstat t,
dba_hist_snapshot s
where
p.sql_id = t.sql_id and
t.snap_id = s.snap_id and
p.object_type like '%INDEX%'
group by
to_char(s.begin_interval_time,'mm-dd
hh24'),
p.object_name
order by
begintvtm ,
dskrdstot desc,
objnm
/
spool off
ttitle off
clear col
clear break
set echo on feedback on lines 80 pages 60
Interval
Time
Disk
Proc-ed
Begin
Index Name
Reads Rows
--------
------------------------------
------
-----------
02-12 14
I_IDL_UB11
3,930 6,808
I_TYPE2
2,481 0
I_USER2
2,344 2,093
I_IDL_UB21
1,362 3,828
I_OBJ4
1,100 948
I_IND1
886
948
I_LINK1
876
946
I_SUM$_1
876
946
I_OBJ#_INTCOL#
566
25,263
I_OBJ1
554
10,366
MGMT_TARGETS_PK
451
0
 |
If you like Oracle tuning, see the book "Oracle
Tuning: The Definitive Reference", with 950 pages of tuning tips and
scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot of Oracle tuning scripts. |
