SSD.sql

-- 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 
;