PROMPT [STEP 11] I/O Latency during periods this SQL appeared


COLUMN filetype_id   FORMAT 999 HEADING 'TYPE_ID'

COLUMN ms_per_read   FORMAT 999.999

COLUMN ms_per_write  FORMAT 999.999


WITH r AS (

  SELECT MIN(snap_id) b, MAX(snap_id) e

  FROM   dba_hist_sqlstat

  WHERE  sql_id='&sql_id'

)

SELECT f.filetype_id,

       ROUND(f.readtim  / DECODE(NULLIF(f.phyrds ,0),0,1,f.phyrds ),3) AS ms_per_read,

       ROUND(f.writetim / DECODE(NULLIF(f.phywrts,0),0,1,f.phywrts),3) AS ms_per_write

FROM   dba_hist_filestatxs f, r

WHERE  f.snap_id BETWEEN r.b AND r.e

GROUP  BY f.filetype_id, f.readtim, f.phyrds, f.writetim, f.phywrts

ORDER  BY ms_per_read DESC NULLS LAST;

Popular posts from this blog