Skip to content
This repository

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP
branch: master
Fetching contributors…

Octocat-spinner-32-eaf2f5

Cannot retrieve contributors at this time

file 40 lines (38 sloc) 1.748 kb
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
-- Find segments with most read operations, and hopefully relatively few writes
-- These are good candidates for placing on SSD storage
WITH segment_stats
          AS (SELECT ss.owner || '.' || ss.object_name
                    || DECODE (ss.subobject_name,NULL, '',
                    '(' || ss.subobject_name || ')') segment_name,
                    ss.object_type,
                    SUM ( CASE WHEN statistic_name LIKE 'physical reads%'
                            THEN VALUE ELSE 0 END) reads,
                    SUM ( CASE WHEN statistic_name LIKE 'physical writes%'
                            THEN VALUE ELSE 0 END) writes,
                    ROUND (SUM (bytes) / 1048576) mb
              FROM v$segment_statistics ss
              JOIN dba_segments s
               ON (s.owner = ss.owner AND s.segment_name = ss.object_name
                   AND NVL (ss.subobject_name, 'x') =NVL (s.partition_name, 'x'))
             WHERE statistic_name LIKE 'physical reads%'
                OR statistic_name LIKE 'physical writes%'
             GROUP BY ss.owner,ss.object_name,ss.subobject_name,ss.object_type)
 SELECT segment_name, object_type, reads,writes,
       ROUND (reads * 100 / SUM (reads) OVER (), 2) pct_reads,
       ROUND (writes * 100 / SUM (writes) OVER (), 2) pct_writes,
       mb FROM segment_stats
 ORDER BY reads DESC

 -- find segments with full scans
 -- These are not ideal for placing on SSD storage
col segment_name format a60
select
        owner || '.' || object_name || DECODE (subobject_name,NULL, '','(' || subobject_name || ')') segment_name,object_type,
        value scans
from
        V$segment_statistics
where
        statistic_name = 'segment scans'
and value != 0
order by
value desc
;
Something went wrong with that request. Please try again.