alter session set nls_territory=france;alter session set nls_numeric_characters=", ";WITHp 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 SHAREMEMORYKBGETSPX, 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 GETSPX1 158795534 4400 4401 24/01/07 06:00:07 24/01/07 07:00:03 3 33 751 11 250,32 158795534 4401 4402 24/01/07 07:00:03 24/01/07 08:00:03 3 33 751 11 250,33 1548175165 4401 4402 24/01/07 07:00:03 24/01/07 08:00:03 24 164 935 6 872,34 3377200449 4401 4402 24/01/07 07:00:03 24/01/07 08:00:03 113 6 364 56,35 3250389959 4401 4402 24/01/07 07:00:03 24/01/07 08:00:03 113 4 705 41,66 158795534 4402 4403 24/01/07 08:00:03 24/01/07 09:00:05 6 67 522 11 253,77 2374230868 4402 4403 24/01/07 08:00:03 24/01/07 09:00:05 751 11 093 14,88 3250389959 4402 4403 24/01/07 08:00:03 24/01/07 09:00:05 2 501 110 651 44,29 3377200449 4402 4403 24/01/07 08:00:03 24/01/07 09:00:05 2 501 143 986 57,610 2933510729 4402 4403 24/01/07 08:00:03 24/01/07 09:00:05 28 115 778 4 134,9
