alter session set nls_territory=france;
alter session set nls_numeric_characters=", ";
WITH
p AS (
SELECT
to_date('24/01/07 06:00','DD/MM/YY HH24:MI') /* First start date to compare */ bnewsnap,
to_date('26/01/07 06:05','DD/MM/YY HH24:MI') /* first end date to compare */ enewsnap,
to_date('17/01/07 06:00','DD/MM/YY HH24:MI') /* second start date to compare */ bexistsnap,
to_date('19/01/07 06:05','DD/MM/YY HH24:MI') /* second end date to compare */ eexistsnap
FROM dual
)
select
es.hash_value hash_value,
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,
to_char(es.executions - nvl(bs.executions,0),'999G999G999') execs ,
to_char(es.buffer_gets - nvl(bs.buffer_gets,0),'999G999G999') gets
, to_char(decode(es.executions - nvl(bs.executions,0),0, 0
, (es.buffer_gets - nvl(bs.buffer_gets,0))/ (es.executions - nvl(bs.executions,0))),'999G999G999D9') getspx
, to_char(es.disk_reads - nvl(bs.disk_reads,0),'999G999G999') reads
, to_char(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))),'999G999G999D9') ReadsPx
, to_char(es.rows_processed - nvl(bs.rows_processed,0),'999G999G990') "rows"
, to_char(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))),'999G999G999D9') RowsPx
, to_char((es.cpu_time - nvl(bs.cpu_time,0))/1000000,'999G999G999') cputsec
, to_char(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)),'999G999G999D9') cputpxmsec
, to_char((es.elapsed_time - nvl(bs.elapsed_time,0))/1000000,'999G999G999') elatsec
, to_char(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)),'999G999G990D9') elapxmsec
, to_char(es.sorts - nvl(bs.sorts,0),'999G999G990') sorts
, to_char(decode(es.executions - nvl(bs.executions,0),0, to_number(null)
, (es.sorts - nvl(bs.sorts,0))/ (es.executions - nvl(bs.executions,0))),'999G999G999') sortpx
, to_char(es.parse_calls - nvl(bs.parse_calls,0),'999G999G990') parcall
, to_char(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))),'999G999G999D9') parsepx
, es.invalidations - nvl(bs.invalidations,0) inval
, es.version_count vcount
, to_char(es.sharable_mem/1024,'999G999G999') sharememoryKB
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.bnewsnap AND p.enewsnap 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 not in (SELECT hash_value
FROM p,
stats$snapshot sns
INNER JOIN stats$sql_summary sqs ON sns.snap_id = sqs.snap_id
WHERE (sns.snap_time BETWEEN p.bexistsnap AND p.eexistsnap)
)
and es.executions - nvl(bs.executions,0)>0
order by e.snap_time;
---------------------------------------------------------------------------------------------------------------------------------------------
The indicators listed are :
HASH_VALUE ROWSPX
BEG_SNAP_ID CPUTSEC
END_SNAP_ID CPUTPXMSEC
BEG_SNAP_TIME ELATSEC
END_SNAP_TIME ELAPXMSEC
EXECS SORTS
GETS SORTPX
GETSPX PARCALL
READS PARSEPX
READSPX INVAL
rows VCOUNT
SHAREMEMORYKB
GETSPX, READSPX, ROWSPX, CPUPXMSEC, ELAPXMSEC, SORTPX and PARSEPX are indators by execution.
Exemple of output (on tladbx30 for the last upgrade, not all lines and not all columns)
BEG_ END_
SNAP SNAP
HASH_VALUE _ID _ID BEG_SNAP_TIME END_SNAP_TIME EXECS GETS GETSPX
1 158795534 4400 4401 24/01/07 06:00:07 24/01/07 07:00:03 3 33 751 11 250,3
2 158795534 4401 4402 24/01/07 07:00:03 24/01/07 08:00:03 3 33 751 11 250,3
3 1548175165 4401 4402 24/01/07 07:00:03 24/01/07 08:00:03 24 164 935 6 872,3
4 3377200449 4401 4402 24/01/07 07:00:03 24/01/07 08:00:03 113 6 364 56,3
5 3250389959 4401 4402 24/01/07 07:00:03 24/01/07 08:00:03 113 4 705 41,6
6 158795534 4402 4403 24/01/07 08:00:03 24/01/07 09:00:05 6 67 522 11 253,7
7 2374230868 4402 4403 24/01/07 08:00:03 24/01/07 09:00:05 751 11 093 14,8
8 3250389959 4402 4403 24/01/07 08:00:03 24/01/07 09:00:05 2 501 110 651 44,2
9 3377200449 4402 4403 24/01/07 08:00:03 24/01/07 09:00:05 2 501 143 986 57,6
10 2933510729 4402 4403 24/01/07 08:00:03 24/01/07 09:00:05 28 115 778 4 134,9
|