Disclaimer:
This script is provided for educational and learning purposes. Review it carefully,
test in a non-production environment, confirm privileges, and validate impact before
using it in production.
SET PAGESIZE 200
SET LINESIZE 200
SET TRIMSPOOL ON
SET TAB OFF
SET VERIFY OFF
SET FEEDBACK ON
COLUMN object_name FORMAT A40 HEADING 'OBJECT_NAME'
COLUMN physical_reads FORMAT 999,999,999,999 HEADING 'PHYSICAL_READS'
COLUMN logical_reads FORMAT 999,999,999,999 HEADING 'LOGICAL_READS'
SELECT
o.object_name,
SUM(s.physical_reads_delta) physical_reads,
SUM(s.logical_reads_delta) logical_reads
FROM
dba_hist_seg_stat s,
dba_hist_seg_stat_obj o,
dba_hist_snapshot sn
WHERE
o.obj# = s.obj#
AND o.dataobj# = s.dataobj#
AND s.snap_id = sn.snap_id
AND sn.begin_interval_time > sysdate - 1 -- yesterday
AND EXTRACT(HOUR FROM sn.end_interval_time) BETWEEN 0 AND 24
AND o.object_type = 'TABLE'
AND o.owner = 'SYS'
GROUP BY
o.object_name
ORDER BY
logical_reads DESC;