WITH
p AS (
SELECT
sysdate-5 /* start date */ bsnap ,
sysdate /* end date */ esnap,
2383739701 /* hash value */ hashv
FROM dual
)
select
bs.snap_id beg_snap_id,
es.snap_id end_snap_id,
to_char(b.snap_time, 'DD/MM/YY HH24:MI:SS') beg_snap_time,
to_char(e.snap_time, 'DD/MM/YY HH24:MI:SS') end_snap_time,
es.executions - nvl(bs.executions,0) execs ,
es.buffer_gets - nvl(bs.buffer_gets,0) gets
, decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.buffer_gets - nvl(bs.buffer_gets,0))/ (es.executions - nvl(bs.executions,0))) getspx
, es.disk_reads - nvl(bs.disk_reads,0) reads
, decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.disk_reads - nvl(bs.disk_reads,0)) / (es.executions - nvl(bs.executions,0))) ReadsPx
, es.rows_processed - nvl(bs.rows_processed,0) "rows"
, decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.rows_processed - nvl(bs.rows_processed,0))/ (es.executions - nvl(bs.executions,0))) RowsPx
, (es.cpu_time - nvl(bs.cpu_time,0))/1000000 cput
, decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.cpu_time - nvl(bs.cpu_time,0))/1000)/ (es.executions - nvl(bs.executions,0)) cpupx
, (es.elapsed_time - nvl(bs.elapsed_time,0))/1000000 elat
, decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.elapsed_time - nvl(bs.elapsed_time,0))/1000)/(es.executions - nvl(bs.executions,0)) elapx
, es.sorts - nvl(bs.sorts,0) sorts
, decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.sorts - nvl(bs.sorts,0))/ (es.executions - nvl(bs.executions,0))) sortpx
, es.parse_calls - nvl(bs.parse_calls,0) parsec
, decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.parse_calls - nvl(bs.parse_calls,0))/ (es.executions - nvl(bs.executions,0))) parsepx
, es.invalidations - nvl(bs.invalidations,0) inval
, es.version_count vcount
, es.sharable_mem/1024 sharemeory
from
v$database d
, stats$sql_summary es
, stats$sql_summary bs
, p
, stats$snapshot b
, stats$snapshot e
where
(e.snap_time BETWEEN p.bsnap AND p.esnap AND MOD(b.snap_id,1)=0 AND d.dbid=b.dbid )
AND (b.snap_id=e.snap_id-1 AND b.dbid=e.dbid AND b.instance_number=e.instance_number)
AND (b.snap_id=bs.snap_id AND b.dbid=bs.dbid AND b.instance_number=bs.instance_number)
AND (e.snap_id=es.snap_id AND e.dbid=es.dbid AND e.instance_number=es.instance_number)
and bs.dbid(+) = es.dbid
and bs.instance_number(+) = es.instance_number
and bs.hash_value(+) = es.hash_value
and bs.address(+) = es.address
and bs.text_subset(+) = es.text_subset
and es.hash_value = p.hashv
p AS (
SELECT
sysdate-5 /* start date */ bsnap ,
sysdate /* end date */ esnap,
2383739701 /* hash value */ hashv
FROM dual
)
select
bs.snap_id beg_snap_id,
es.snap_id end_snap_id,
to_char(b.snap_time, 'DD/MM/YY HH24:MI:SS') beg_snap_time,
to_char(e.snap_time, 'DD/MM/YY HH24:MI:SS') end_snap_time,
es.executions - nvl(bs.executions,0) execs ,
es.buffer_gets - nvl(bs.buffer_gets,0) gets
, decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.buffer_gets - nvl(bs.buffer_gets,0))/ (es.executions - nvl(bs.executions,0))) getspx
, es.disk_reads - nvl(bs.disk_reads,0) reads
, decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.disk_reads - nvl(bs.disk_reads,0)) / (es.executions - nvl(bs.executions,0))) ReadsPx
, es.rows_processed - nvl(bs.rows_processed,0) "rows"
, decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.rows_processed - nvl(bs.rows_processed,0))/ (es.executions - nvl(bs.executions,0))) RowsPx
, (es.cpu_time - nvl(bs.cpu_time,0))/1000000 cput
, decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.cpu_time - nvl(bs.cpu_time,0))/1000)/ (es.executions - nvl(bs.executions,0)) cpupx
, (es.elapsed_time - nvl(bs.elapsed_time,0))/1000000 elat
, decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.elapsed_time - nvl(bs.elapsed_time,0))/1000)/(es.executions - nvl(bs.executions,0)) elapx
, es.sorts - nvl(bs.sorts,0) sorts
, decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.sorts - nvl(bs.sorts,0))/ (es.executions - nvl(bs.executions,0))) sortpx
, es.parse_calls - nvl(bs.parse_calls,0) parsec
, decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.parse_calls - nvl(bs.parse_calls,0))/ (es.executions - nvl(bs.executions,0))) parsepx
, es.invalidations - nvl(bs.invalidations,0) inval
, es.version_count vcount
, es.sharable_mem/1024 sharemeory
from
v$database d
, stats$sql_summary es
, stats$sql_summary bs
, p
, stats$snapshot b
, stats$snapshot e
where
(e.snap_time BETWEEN p.bsnap AND p.esnap AND MOD(b.snap_id,1)=0 AND d.dbid=b.dbid )
AND (b.snap_id=e.snap_id-1 AND b.dbid=e.dbid AND b.instance_number=e.instance_number)
AND (b.snap_id=bs.snap_id AND b.dbid=bs.dbid AND b.instance_number=bs.instance_number)
AND (e.snap_id=es.snap_id AND e.dbid=es.dbid AND e.instance_number=es.instance_number)
and bs.dbid(+) = es.dbid
and bs.instance_number(+) = es.instance_number
and bs.hash_value(+) = es.hash_value
and bs.address(+) = es.address
and bs.text_subset(+) = es.text_subset
and es.hash_value = p.hashv