3 Busiest Objects Since Last Database Restart

3_busiest_objects_since_last_database_restart.sql

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 250
SET TRIMSPOOL ON
SET TAB OFF
SET VERIFY OFF
SET FEEDBACK ON

COLUMN owner FORMAT A20 HEADING 'OWNER'
COLUMN object_name FORMAT A35 HEADING 'OBJECT_NAME'
COLUMN subobject_name FORMAT A30 HEADING 'SUBOBJECT_NAME'
COLUMN object_type FORMAT A12 HEADING 'OBJECT_TYPE'
COLUMN tablespace_name FORMAT A25 HEADING 'TABLESPACE'
COLUMN reads FORMAT 999,999,999,999 HEADING 'READS'
COLUMN logical_reads FORMAT 999,999,999,999 HEADING 'LOGICAL_READS'
COLUMN physical_reads FORMAT 999,999,999,999 HEADING 'PHYSICAL_READS'
COLUMN segment_scans FORMAT 999,999,999,999 HEADING 'SEGMENT_SCANS'
COLUMN writes FORMAT 999,999,999,999 HEADING 'WRITES'

SELECT
    vss.owner,
    vss.object_name,
    vss.subobject_name,
    vss.object_type,
    vss.tablespace_name,
    SUM(CASE statistic_name WHEN 'logical reads' THEN value ELSE 0 END
        + CASE statistic_name WHEN 'physical reads' THEN value ELSE 0 END) AS reads,
    SUM(CASE statistic_name WHEN 'logical reads' THEN value ELSE 0 END) AS logical_reads,
    SUM(CASE statistic_name WHEN 'physical reads' THEN value ELSE 0 END) AS physical_reads,
    SUM(CASE statistic_name WHEN 'segment scans' THEN value ELSE 0 END) AS segment_scans,
    SUM(CASE statistic_name WHEN 'physical writes' THEN value ELSE 0 END) AS writes
FROM
    v$segment_statistics vss
WHERE
    vss.owner NOT IN ('SYS', 'SYSTEM')
    AND vss.object_type IN ('TABLE', 'INDEX')
GROUP BY
    vss.owner,
    vss.object_name,
    vss.object_type,
    vss.subobject_name,
    vss.tablespace_name
ORDER BY
    reads DESC;