Difference between values recorded in two oracle snapshots

REM

REM NAME

REM afamsprep.sql

REM

REM DESCRIPTION

REM SQL*Plus command file to report on differences between

REM values recorded in two snapshots.

REM

REM NOTES

REM Usually run as the STATSPACK owner, PERFSTAT

REM

REM dbdrv: none

SET VERIFY OFF

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

CLEAR BREAK COMPUTE;

REPFOOTER OFF;

TTITLE OFF;

BTITLE OFF;

SET TIMING OFF VERI OFF SPACE 1 FLUSH ON PAUSE OFF TERMOUT ON NUMWIDTH 10;

SET ECHO OFF FEEDBACK OFF PAGESIZE 60 LINESIZE 80 NEWPAGE 2 RECSEP OFF;

SET TRIMSPOOL ON TRIMOUT ON;

DEFINE top_n_events = 5;

DEFINE top_n_sql = 65;

DEFINE num_rows_per_hash=5;

-- Get the current database/instance information - this will be used

-- later in the report along with bid, eid to lookup snapshots

COLUMN inst_num HEADING "Inst Num" NEW_VALUE inst_num FORMAT 99999;

COLUMN inst_name HEADING "Instance" NEW_VALUE inst_name FORMAT a12;

COLUMN db_name HEADING "DB Name" NEW_VALUE db_name FORMAT a12;

COLUMN dbid HEADING "DB Id" NEW_VALUE dbid FORMAT 9999999999;

SELECT d.dbid dbid,

d.name db_name,

i.instance_number inst_num,

i.instance_name inst_name

FROM v$database d, v$instance i;

VARIABLE dbid NUMBER;

VARIABLE inst_num NUMBER;

VARIABLE inst_name VARCHAR2(20);

VARIABLE db_name VARCHAR2(20);

BEGIN

:dbid := &dbid;

:inst_num := &inst_num;

:inst_name := '&inst_name';

:db_name := '&db_name';

END;

-- Ask for the snapshots Id's which are to be compared

SET TERMOUT ON;

COLUMN instart_fmt NOPRINT;

COLUMN versn NOPRINT HEADING 'Release' NEW_VALUE versn;

COLUMN host_name NOPRINT HEADING 'Host' NEW_VALUE host_name;

COLUMN para NOPRINT HEADING 'OPS' NEW_VALUE para;

COLUMN level FORMAT 99 HEADING 'Snap|Level';

COLUMN snap_id HEADING 'Snap|Id' FORMAT 9990;

COLUMN snapdat HEADING 'Snap Started' FORMAT a17;

COLUMN comment HEADING 'Comment' FORMAT a22;

BREAK ON inst_name ON db_name ON instart_fmt;

TTITLE LEF 'Completed Snapshots' SKIP 2;

SELECT di.instance_name inst_name,

di.host_name host_name,

di.db_name db_name,

di.version versn,

di.parallel para,

TO_CHAR (s.startup_time, ' dd Mon "at" HH24:mi:ss') instart_fmt,

s.snap_id,

TO_CHAR (s.snap_time, 'dd Mon YYYY HH24:mi') snapdat,

s.snap_level "level",

SUBSTR (s.ucomment, 1, 60) "comment"

FROM stats$snapshot s, stats$database_instance di

WHERE s.dbid = :dbid

AND di.dbid = :dbid

AND s.instance_number = :inst_num

AND di.instance_number = :inst_num

AND di.startup_time = s.startup_time

ORDER BY db_name, instance_name, snap_id;

CLEAR BREAK;

TTITLE OFF;

SET TERMOUT ON;

VARIABLE bid NUMBER;

VARIABLE eid NUMBER;

VARIABLE versn VARCHAR2(10);

VARIABLE para VARCHAR2(9);

VARIABLE host_name VARCHAR2(64);

BEGIN

:bid := &1;

:eid := &2;

:versn := '&versn';

:para := '&para';

:host_name := '&host_name';

END;

-- Use report name if specified, otherwise prompt user for output file

-- name (specify default), then begin spooling

SET TERMOUT OFF;

COLUMN dflt_name NEW_VALUE dflt_name NOPRINT;

SELECT 'sp_' || :bid || '_' || :eid dflt_name FROM DUAL;

SET TERMOUT ON;

-- set heading off;

-- column report_name new_value report_name noprint;

-- select 'Using the report name ' || nvl('&&report_name','&dflt_name')

-- , nvl('&&report_name','&dflt_name') report_name

-- from sys.dual;

-- spool &report_name;

-- set heading on;

-- prompt

-- Verify begin and end snapshot Ids exist for the database, and that

-- there wasn't an instance shutdown in between the two snapshots

-- being taken.

SET HEADING OFF;

SELECT 'ERROR: Database/Instance does not exist in STATS$DATABASE_INSTANCE'

FROM DUAL

WHERE NOT EXISTS

(SELECT NULL

FROM stats$database_instance

WHERE instance_number = :inst_num AND dbid = :dbid);

SELECT 'ERROR: Begin Snapshot Id specified does not exist for this database/instance'

FROM DUAL

WHERE NOT EXISTS

(SELECT NULL

FROM stats$snapshot b

WHERE b.snap_id = :bid

AND b.dbid = :dbid

AND b.instance_number = :inst_num);

SELECT 'ERROR: End Snapshot Id specified does not exist for this database/instance'

FROM DUAL

WHERE NOT EXISTS

(SELECT NULL

FROM stats$snapshot e

WHERE e.snap_id = :eid

AND e.dbid = :dbid

AND e.instance_number = :inst_num);

SELECT 'WARNING: timed_statitics setting changed between begin/end snaps: TIMINGS ARE INVALID'

FROM DUAL

WHERE NOT EXISTS

(SELECT NULL

FROM stats$parameter b, stats$parameter e

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.name = e.name

AND b.name = 'timed_statistics'

AND b.VALUE = e.VALUE);

SELECT 'ERROR: Snapshots chosen span an instance shutdown: RESULTS ARE INVALID'

FROM DUAL

WHERE NOT EXISTS

(SELECT NULL

FROM stats$snapshot b, stats$snapshot e

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.startup_time = e.startup_time);

SELECT 'ERROR: Session statistics are for different sessions: RESULTS NOT PRINTED'

FROM DUAL

WHERE NOT EXISTS

(SELECT NULL

FROM stats$snapshot b, stats$snapshot e

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.session_id = e.session_id

AND b.serial# = e.serial#);

SET HEADING ON;

SET NEWPAGE 1 HEADING ON;

-- Call statspack to calculate certain statistics

SET HEADING OFF;

VARIABLE lhtr NUMBER;

VARIABLE bfwt NUMBER;

VARIABLE tran NUMBER;

VARIABLE chng NUMBER;

VARIABLE ucal NUMBER;

VARIABLE urol NUMBER;

VARIABLE ucom NUMBER;

VARIABLE rsiz NUMBER;

VARIABLE phyr NUMBER;

VARIABLE phyw NUMBER;

VARIABLE prse NUMBER;

VARIABLE hprs NUMBER;

VARIABLE recr NUMBER;

VARIABLE gets NUMBER;

VARIABLE rlsr NUMBER;

VARIABLE rent NUMBER;

VARIABLE srtm NUMBER;

VARIABLE srtd NUMBER;

VARIABLE srtr NUMBER;

VARIABLE strn NUMBER;

VARIABLE call NUMBER;

VARIABLE lhr NUMBER;

VARIABLE sp VARCHAR2(512);

VARIABLE bc VARCHAR2(512);

VARIABLE lb VARCHAR2(512);

VARIABLE bs VARCHAR2(512);

VARIABLE twt NUMBER;

VARIABLE logc NUMBER;

VARIABLE prscpu NUMBER;

VARIABLE prsela NUMBER;

VARIABLE tcpu NUMBER;

VARIABLE exe NUMBER;

VARIABLE bspm NUMBER;

VARIABLE espm NUMBER;

VARIABLE bfrm NUMBER;

VARIABLE efrm NUMBER;

VARIABLE blog NUMBER;

VARIABLE elog NUMBER;

BEGIN

STATSPACK.STAT_CHANGES (:bid,

:eid,

:dbid,

:inst_num -- End of IN arguments

:lhtr,

:bfwt,

:tran,

:chng,

:ucal,

:urol,

:rsiz,

:phyr,

:phyw,

:ucom,

:prse,

:hprs,

:recr,

:gets,

:rlsr,

:rent,

:srtm,

:srtd,

:srtr,

:strn,

:lhr,

:bc,

:sp,

:lb,

:bs,

:twt,

:logc,

:prscpu,

:tcpu,

:exe,

:prsela,

:bspm,

:espm,

:bfrm,

:efrm,

:blog,

:elog);

:call := :ucal + :recr;

END;

-- Summary Statistics

-- Print database, instance, parallel, release, host and snapshot

-- information

PROMPT STATSPACK report for

SET HEADING ON;

COLUMN host_name HEADING "Host" FORMAT a12 PRINT;

COLUMN para HEADING "OPS" FORMAT a3 PRINT;

COLUMN versn HEADING "Release" FORMAT a11 PRINT;

SELECT :db_name db_name,

:dbid dbid,

:inst_name inst_name,

:inst_num inst_num,

:versn versn,

:para para,

:host_name host_name

FROM sys.DUAL;

-- Print snapshot information

COLUMN inst_num NOPRINT

COLUMN instart_fmt NEW_VALUE INSTART_FMT NOPRINT;

COLUMN instart NEW_VALUE instart NOPRINT;

COLUMN session_id NEW_VALUE SESSION NOPRINT;

COLUMN ela NEW_VALUE ELA NOPRINT;

COLUMN btim NEW_VALUE btim HEADING 'Start Time' FORMAT a19;

COLUMN etim NEW_VALUE etim HEADING 'End Time' FORMAT a19;

COLUMN bid HEADING 'Start Id' FORMAT 99999990;

COLUMN eid HEADING ' End Id' FORMAT 99999990;

COLUMN dur HEADING 'Duration(mins)' FORMAT 999,990.00 JUST R;

COLUMN sess_id NEW_VALUE sess_id NOPRINT;

COLUMN serial NEW_VALUE serial NOPRINT;

COLUMN bbgt NEW_VALUE bbgt NOPRINT;

COLUMN ebgt NEW_VALUE ebgt NOPRINT;

COLUMN bdrt NEW_VALUE bdrt NOPRINT;

COLUMN edrt NEW_VALUE edrt NOPRINT;

COLUMN bet NEW_VALUE bet NOPRINT;

COLUMN eet NEW_VALUE eet NOPRINT;

COLUMN bsmt NEW_VALUE bsmt NOPRINT;

COLUMN esmt NEW_VALUE esmt NOPRINT;

COLUMN bvc NEW_VALUE bvc NOPRINT;

COLUMN evc NEW_VALUE evc NOPRINT;

COLUMN blog FORMAT 99,999;

COLUMN elog FORMAT 99,999;

COLUMN nl NEWLINE;

SET HEADING OFF;

SELECT ' Snap Id Snap Time Sessions',

' ------- ------------------ --------' nl,

' Begin Snap: ' nl,

b.snap_id bid,

TO_CHAR (b.snap_time, 'dd-Mon-yy hh24:mi:ss') btim,

:blog blog,

' End Snap: ' nl,

e.snap_id eid,

TO_CHAR (e.snap_time, 'dd-Mon-yy hh24:mi:ss') etim,

:elog elog,

' Elapsed: ' nl,

ROUND ( ( (e.snap_time - b.snap_time) * 1440 * 60), 0) / 60 dur -- mins

'(mins)',

b.instance_number inst_num,

TO_CHAR (b.startup_time, 'dd-Mon-yy hh24:mi:ss') instart_fmt,

b.session_id,

ROUND ( ( (e.snap_time - b.snap_time) * 1440 * 60), 0) ela -- secs

TO_CHAR (b.startup_time, 'YYYYMMDD HH24:MI:SS') instart,

e.session_id sess_id,

e.serial# serial,

b.buffer_gets_th bbgt,

e.buffer_gets_th ebgt,

b.disk_reads_th bdrt,

e.disk_reads_th edrt,

b.executions_th bet,

e.executions_th eet,

b.sharable_mem_th bsmt,

e.sharable_mem_th esmt,

b.version_count_th bvc,

e.version_count_th evc

FROM stats$snapshot b, stats$snapshot e

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.startup_time = e.startup_time

AND b.snap_time < e.snap_time;

SET HEADING ON;

VARIABLE btim VARCHAR2 (20);

VARIABLE etim VARCHAR2 (20);

VARIABLE ela NUMBER;

VARIABLE instart VARCHAR2 (18);

VARIABLE bbgt NUMBER;

VARIABLE ebgt NUMBER;

VARIABLE bdrt NUMBER;

VARIABLE edrt NUMBER;

VARIABLE bet NUMBER;

VARIABLE eet NUMBER;

VARIABLE bsmt NUMBER;

VARIABLE esmt NUMBER;

VARIABLE bvc NUMBER;

VARIABLE evc NUMBER;

BEGIN

:btim := '&btim';

:etim := '&etim';

:ela := &ela;

:instart := '&instart';

:bbgt := &bbgt;

:ebgt := &ebgt;

:bdrt := &bdrt;

:edrt := &edrt;

:bet := &bet;

:eet := &eet;

:bsmt := &bsmt;

:esmt := &esmt;

:bvc := &bvc;

:evc := &evc;

END;

SET HEADING OFF;

-- Cache Sizes

COLUMN dscr FORMAT a28 NEWLINE;

COLUMN val FORMAT a10 JUST R;

SELECT 'Cache Sizes' dscr,

'~~~~~~~~~~~' dscr,

' db_block_buffers:' dscr,

LPAD (:bc, 10) val,

' log_buffer:',

LPAD (:lb, 10) val,

' db_block_size:' dscr,

LPAD (:bs, 10) val,

' shared_pool_size:',

LPAD (:sp, 10) val

FROM sys.DUAL;

-- Load Profile

COLUMN dscr FORMAT a28 NEWLINE;

COLUMN val FORMAT 9,999,999,999,990.99;

COLUMN sval FORMAT 99,990.99;

COLUMN svaln FORMAT 99,990.99 NEWLINE;

COLUMN totcalls NEW_VALUE totcalls NOPRINT

COLUMN pctval FORMAT 990.99;

SELECT 'Load Profile',

'~~~~~~~~~~~~ Per Second Per Transaction',

' --------------- ---------------',

' Redo size:' dscr,

ROUND (:rsiz / :ela, 2) val,

ROUND (:rsiz / :tran, 2) val,

' Logical reads:' dscr,

ROUND (:gets / :ela, 2) val,

ROUND (:gets / :tran, 2) val,

' Block changes:' dscr,

ROUND (:chng / :ela, 2) val,

ROUND (:chng / :tran, 2) val,

' Physical reads:' dscr,

ROUND (:phyr / :ela, 2) val,

ROUND (:phyr / :tran, 2) val,

' Physical writes:' dscr,

ROUND (:phyw / :ela, 2) val,

ROUND (:phyw / :tran, 2) val,

' User calls:' dscr,

ROUND (:ucal / :ela, 2) val,

ROUND (:ucal / :tran, 2) val,

' Parses:' dscr,

ROUND (:prse / :ela, 2) val,

ROUND (:prse / :tran, 2) val,

' Hard parses:' dscr,

ROUND (:hprs / :ela, 2) val,

ROUND (:hprs / :tran, 2) val,

' Sorts:' dscr,

ROUND ( (:srtm + :srtd) / :ela, 2) val,

ROUND ( (:srtm + :srtd) / :tran, 2) val,

' Logons:' dscr,

ROUND (:logc / :ela, 2) val,

ROUND (:logc / :tran, 2) val,

' Executes:' dscr,

ROUND (:exe / :ela, 2) val,

ROUND (:exe / :tran, 2) val,

' Transactions:' dscr,

ROUND (:tran / :ela, 2) val,

' ' dscr,

' % Blocks changed per Read:' dscr,

ROUND (100 * :chng / :gets, 2) pctval,

' Recursive Call %:',

ROUND (100 * :recr / :call, 2) pctval,

' Rollback per transaction %:' dscr,

ROUND (100 * :urol / :tran, 2) pctval,

' Rows per Sort:',

DECODE ( (:srtm + :srtd),

0, TO_NUMBER (NULL),

ROUND (:srtr / (:srtm + :srtd), 2))

pctval

FROM sys.DUAL;

-- Instance Efficiency Percentages

COLUMN ldscr FORMAT a50

COLUMN nl FORMAT a60 NEWLINE;

SELECT 'Instance Efficiency Percentages (Target 100%)' ldscr,

'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' ldscr,

' Buffer Nowait %:' dscr,

ROUND (100 * (1 - :bfwt / :gets), 2) pctval,

' Redo NoWait %:',

DECODE (:rent,

0, TO_NUMBER (NULL),

ROUND (100 * (1 - :rlsr / :rent), 2))

pctval,

' Buffer Hit %:' dscr,

ROUND (100 * (1 - :phyr / :gets), 2) pctval,

' In-memory Sort %:',

DECODE ( (:srtm + :srtd),

0, TO_NUMBER (NULL),

ROUND (100 * :srtm / (:srtd + :srtm), 2))

pctval,

' Library Hit %:' dscr,

ROUND (100 * :lhtr, 2) pctval,

' Soft Parse %:',

ROUND (100 * (1 - :hprs / :prse), 2) pctval,

' Execute to Parse %:' dscr,

ROUND (100 * (1 - :prse / :exe), 2) pctval,

' Latch Hit %:',

ROUND (100 * (1 - :lhr), 2) pctval,

'Parse CPU to Parse Elapsd %:' dscr,

DECODE (:prsela,

0, TO_NUMBER (NULL),

ROUND (100 * :prscpu / :prsela, 2))

pctval,

' % Non-Parse CPU:',

DECODE (:tcpu,

0, TO_NUMBER (NULL),

ROUND (100 * 1 - (:prscpu / :tcpu), 2))

pctval

FROM sys.DUAL;

SELECT ' Shared Pool Statistics Begin End' nl,

' ------ ------',

' Memory Usage %:' dscr,

100 * (1 - :bfrm / :bspm) pctval,

100 * (1 - :efrm / :espm) pctval,

' % SQL with executions>1:' dscr,

100 * (1 - b.single_use_sql / b.total_sql) pctval,

100 * (1 - e.single_use_sql / e.total_sql) pctval,

' % Memory for SQL w/exec>1:' dscr,

100 * (1 - b.single_use_sql_mem / b.total_sql_mem) pctval,

100 * (1 - e.single_use_sql_mem / e.total_sql_mem) pctval

FROM stats$sql_statistics b, stats$sql_statistics e

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.dbid = :dbid

AND e.dbid = :dbid;

SET HEADING ON;

REPFOOTER CENTER -

'-------------------------------------------------------------';

-- Top Wait Events

COL idle NOPRINT;

COL event FORMAT a44 HEADING 'Top 5 Wait Events|~~~~~~~~~~~~~~~~~|Event';

COL waits FORMAT 999,999,990 HEADING 'Waits';

COL time FORMAT 999,999,990 HEADING 'Wait|Time (cs)';

COL pctwtt FORMAT 999.99 HEADING '% Total|Wt Time';

SELECT event,

waits,

time,

pctwtt

FROM ( SELECT e.event event,

e.total_waits - NVL (b.total_waits, 0) waits,

e.time_waited - NVL (b.time_waited, 0) time,

DECODE (

:twt,

0, 0,

100 * ( (e.time_waited - NVL (b.time_waited, 0)) / :twt))

pctwtt

FROM stats$system_event b, stats$system_event e

WHERE b.snap_id(+) = :bid

AND e.snap_id = :eid

AND b.dbid(+) = :dbid

AND e.dbid = :dbid

AND b.instance_number(+) = :inst_num

AND e.instance_number = :inst_num

AND b.event(+) = e.event

AND e.total_waits > NVL (b.total_waits, 0)

AND e.event NOT IN (SELECT event FROM stats$idle_event)

ORDER BY time DESC, waits DESC)

WHERE ROWNUM <= &&top_n_events;

SET SPACE 1 TERMOUT ON NEWPAGE 0;

WHENEVER SQLERROR EXIT;

-- System Events

TTITLE LEF 'Wait Events for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

'-> cs - centisecond - 100th of a second' -

SKIP 1 -

'-> ms - millisecond - 1000th of a second' -

SKIP 1 -

LEF '-> ordered by wait time desc, waits desc (idle events last)' -

SKIP 2;

COL idle NOPRINT;

COL event FORMAT a28 HEADING 'Event' TRUNC;

COL waits FORMAT 999,999,990 HEADING 'Waits';

COL timeouts FORMAT 9,999,990 HEADING 'Timeouts';

COL time FORMAT 99,999,990 HEADING 'Total Wait|Time (cs)';

COL wt FORMAT 99990 HEADING 'Avg|wait|(ms)';

COL txwaits FORMAT 990.0 HEADING 'Waits|/txn';

SELECT e.event,

e.total_waits - NVL (b.total_waits, 0) waits,

e.total_timeouts - NVL (b.total_timeouts, 0) timeouts,

e.time_waited - NVL (b.time_waited, 0) time,

DECODE (

(e.total_waits - NVL (b.total_waits, 0)),

0, TO_NUMBER (NULL),

(e.time_waited - NVL (b.time_waited, 0))

/ (e.total_waits - NVL (b.total_waits, 0))

* 10)

wt,

(e.total_waits - NVL (b.total_waits, 0)) / :tran txwaits,

DECODE (i.event, NULL, 0, 99) idle

FROM stats$system_event b, stats$system_event e, stats$idle_event i

WHERE b.snap_id(+) = :bid

AND e.snap_id = :eid

AND b.dbid(+) = :dbid

AND e.dbid = :dbid

AND b.instance_number(+) = :inst_num

AND e.instance_number = :inst_num

AND b.event(+) = e.event

AND e.total_waits > NVL (b.total_waits, 0)

AND e.event NOT LIKE '%timer%'

AND e.event NOT LIKE 'rdbms ipc%'

AND i.event(+) = e.event

ORDER BY idle, time DESC, waits DESC;

-- Background process wait events

TTITLE LEF 'Background Wait Events for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF '-> ordered by wait time desc, waits desc (idle events last)' -

SKIP 2;

BREAK ON idle;

SELECT e.event,

e.total_waits - NVL (b.total_waits, 0) waits,

e.total_timeouts - NVL (b.total_timeouts, 0) timeouts,

e.time_waited - NVL (b.time_waited, 0) time,

DECODE (

(e.total_waits - NVL (b.total_waits, 0)),

0, TO_NUMBER (NULL),

(e.time_waited - NVL (b.time_waited, 0))

/ (e.total_waits - NVL (b.total_waits, 0))

* 10)

wt,

(e.total_waits - NVL (b.total_waits, 0)) / :tran txwaits,

DECODE (i.event, NULL, 0, 99) idle

FROM stats$bg_event_summary b, stats$bg_event_summary e, stats$idle_event i

WHERE b.snap_id(+) = :bid

AND e.snap_id = :eid

AND b.dbid(+) = :dbid

AND e.dbid = :dbid

AND b.instance_number(+) = :inst_num

AND e.instance_number = :inst_num

AND b.event(+) = e.event

AND e.total_waits > NVL (b.total_waits, 0)

AND i.event(+) = e.event

ORDER BY idle, time DESC, waits DESC;

CLEAR BREAK;

-- SQL Reporting

COL Execs FORMAT 999,999,990 HEADING 'Executes';

COL GPX FORMAT 999,999,990.0 HEADING 'Gets|per Exec';

COL RPX FORMAT 999,999,990.0 HEADING 'Reads|per Exec';

COL RWPX FORMAT 9,999,990.0 HEADING 'Rows|per Exec';

COL Gets FORMAT 9,999,999,990 HEADING 'Buffer Gets';

COL Reads FORMAT 9,999,999,990 HEADING 'Physical|Reads';

COL Rw FORMAT 9,999,999,990 HEADING 'Rows | Processed';

COL hashval FORMAT 99999999999 HEADING 'Hash Value';

COL sql_text FORMAT a500 HEADING 'SQL statement' WRAP;

COL rel_pct FORMAT 999.9 HEADING '% of|Total';

COL shm FORMAT 999,999,999 HEADING 'Sharable |Memory (bytes)';

COL vcount FORMAT 9,999 HEADING 'Version|Count';

-- SQL statements ordered by buffer gets

TTITLE LEF 'SQL ordered by Gets for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

'-> End Buffer Gets Threshold: ' ebgt -

SKIP 1 -

'-> Note that resources reported for PL/SQL includes the ' -

'resources used by' SKIP 1 -

' all SQL statements called within the PL/SQL code. As ' -

'individual SQL' SKIP 1 -

' statements are also reported, it is possible and valid ' -

'for the summed' SKIP 1 -

' total % to exceed 100' -

SKIP 2;

-- Bug 1313544 requires this rather bizarre SQL statement

SET UNDERLINE OFF;

COL aa FORMAT a80 HEADING -

' Buffer Gets Executions Gets per Exec % Total Hash Value |--------------- ------------ -------------- ------- ------------'

COLUMN hv NOPRINT;

BREAK ON hv SKIP 1;

SELECT aa, hv

FROM ( SELECT /*+ ordered */

DECODE (

st.piece,

0, LPAD (

TO_CHAR ( (e.buffer_gets - NVL (b.buffer_gets, 0)),

'99,999,999,999'),

15)

|| ' '

|| LPAD (

TO_CHAR ( (e.executions - NVL (b.executions, 0)),

'999,999,999'),

12)

|| ' '

|| LPAD (

(TO_CHAR (

DECODE (

e.executions - NVL (b.executions, 0),

0, TO_NUMBER (NULL),

(e.buffer_gets - NVL (b.buffer_gets, 0))

/ (e.executions - NVL (b.executions, 0))),

'999,999,990.0')),

14)

|| ' '

|| LPAD (

(TO_CHAR (

100

* (e.buffer_gets - NVL (b.buffer_gets, 0))

/ :gets,

'990.0')),

|| ' '

|| LPAD (e.hash_value, 12)

|| ' '

|| RPAD (' ', 15)

|| st.sql_text,

st.sql_text)

aa,

e.hash_value hv

FROM stats$sql_summary e, stats$sql_summary b, stats$sqltext st

WHERE b.snap_id(+) = :bid

AND b.dbid(+) = e.dbid

AND b.instance_number(+) = e.instance_number

AND b.hash_value(+) = e.hash_value

AND b.address(+) = e.address

AND b.text_subset(+) = e.text_subset

AND e.snap_id = :eid

AND e.dbid = :dbid

AND e.instance_number = :inst_num

AND e.hash_value = st.hash_value

AND e.text_subset = st.text_subset

AND st.piece < &&num_rows_per_hash

AND e.executions > NVL (b.executions, 0)

ORDER BY (e.buffer_gets - NVL (b.buffer_gets, 0)) DESC,

e.hash_value,

st.piece)

WHERE ROWNUM < &&top_n_sql;

-- SQL statements ordered by physical reads

TTITLE LEF 'SQL ordered by Reads for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

'-> End Disk Reads Threshold: ' edrt -

SKIP 2;

COL aa FORMAT a80 HEADING -

' Physical Reads Executions Reads per Exec % Total Hash Value |--------------- ------------ -------------- ------- ------------'

SELECT aa, hv

FROM ( SELECT /*+ ordered */

DECODE (

st.piece,

0, LPAD (

TO_CHAR ( (e.disk_reads - NVL (b.disk_reads, 0)),

'99,999,999,999'),

15)

|| ' '

|| LPAD (

TO_CHAR ( (e.executions - NVL (b.executions, 0)),

'999,999,999'),

12)

|| ' '

|| LPAD (

(TO_CHAR (

DECODE (

e.executions - NVL (b.executions, 0),

0, TO_NUMBER (NULL),

(e.disk_reads - NVL (b.disk_reads, 0))

/ (e.executions - NVL (b.executions, 0))),

'999,999,990.0')),

14)

|| ' '

|| LPAD (

(TO_CHAR (

100

* (e.disk_reads - NVL (b.disk_reads, 0))

/ :phyr,

'990.0')),

|| ' '

|| LPAD (e.hash_value, 12)

|| ' '

|| RPAD (' ', 15)

|| st.sql_text,

st.sql_text)

aa,

e.hash_value hv

FROM stats$sql_summary e, stats$sql_summary b, stats$sqltext st

WHERE b.snap_id(+) = :bid

AND b.dbid(+) = e.dbid

AND b.instance_number(+) = e.instance_number

AND b.hash_value(+) = e.hash_value

AND b.address(+) = e.address

AND b.text_subset(+) = e.text_subset

AND e.snap_id = :eid

AND e.dbid = :dbid

AND e.instance_number = :inst_num

AND e.hash_value = st.hash_value

AND e.text_subset = st.text_subset

AND st.piece < &&num_rows_per_hash

AND e.executions > NVL (b.executions, 0)

AND :phyr > 0

ORDER BY (e.disk_reads - NVL (b.disk_reads, 0)) DESC,

e.hash_value,

st.piece)

WHERE ROWNUM < &&top_n_sql;

-- SQL statements ordered by executions

TTITLE LEF 'SQL ordered by Executions for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

'-> End Executions Threshold: ' eet -

SKIP 2;

COL aa FORMAT a80 HEADING -

' Executions Rows Processed Rows per Exec Hash Value |------------ ---------------- ---------------- ------------'

SELECT aa, hv

FROM ( SELECT /*+ ordered */

DECODE (

st.piece,

0, LPAD (

TO_CHAR ( (e.executions - NVL (b.executions, 0)),

'999,999,999'),

12)

|| ' '

|| LPAD (

TO_CHAR (

(NVL (e.rows_processed, 0)

- NVL (b.rows_processed, 0)),

'999,999,999,999'),

16)

|| ' '

|| LPAD (

(TO_CHAR (

DECODE (

NVL (e.rows_processed, 0)

- NVL (b.rows_processed, 0),

0, 0,

(e.rows_processed

- NVL (b.rows_processed, 0))

/ (e.executions - NVL (b.executions, 0))),

'9,999,999,990.0')),

16)

|| ' '

|| LPAD (e.hash_value, 12)

|| ' '

|| RPAD (' ', 20)

|| st.sql_text,

st.sql_text)

aa,

e.hash_value hv

FROM stats$sql_summary e, stats$sql_summary b, stats$sqltext st

WHERE b.snap_id(+) = :bid

AND b.dbid(+) = e.dbid

AND b.instance_number(+) = e.instance_number

AND b.hash_value(+) = e.hash_value

AND b.address(+) = e.address

AND b.text_subset(+) = e.text_subset

AND e.snap_id = :eid

AND e.dbid = :dbid

AND e.instance_number = :inst_num

AND e.hash_value = st.hash_value

AND e.text_subset = st.text_subset

AND st.piece < &&num_rows_per_hash

AND e.executions > NVL (b.executions, 0)

ORDER BY (e.executions - NVL (b.executions, 0)) DESC,

e.hash_value,

st.piece)

WHERE ROWNUM < &&top_n_sql;

-- SQL statements ordered by Sharable Memory

TTITLE LEF 'SQL ordered by Sharable Memory for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

'-> End Sharable Memory Threshold: ' FORMAT 99999999 bsmt -

SKIP 2;

COL aa FORMAT a80 HEADING -

'Sharable Mem (b) Executions % Total Hash Value |---------------- ------------ ------- ------------'

SELECT aa, hv

FROM ( SELECT /*+ ordered */

DECODE (

st.piece,

0, LPAD (TO_CHAR (e.sharable_mem, '999,999,999,999'), 16)

|| ' '

|| LPAD (

TO_CHAR ( (e.executions - NVL (b.executions, 0)),

'999,999,999'),

12)

|| ' '

|| LPAD (

(TO_CHAR (100 * e.sharable_mem / :espm, '990.0')),

|| ' '

|| LPAD (e.hash_value, 12)

|| ' '

|| RPAD (' ', 29)

|| st.sql_text,

st.sql_text)

aa,

e.hash_value hv

FROM stats$sql_summary e, stats$sql_summary b, stats$sqltext st

WHERE b.snap_id(+) = :bid

AND b.dbid(+) = e.dbid

AND b.instance_number(+) = e.instance_number

AND b.hash_value(+) = e.hash_value

AND b.address(+) = e.address

AND b.text_subset(+) = e.text_subset

AND e.snap_id = :eid

AND e.dbid = :dbid

AND e.instance_number = :inst_num

AND e.hash_value = st.hash_value

AND e.text_subset = st.text_subset

AND st.piece < &&num_rows_per_hash

AND e.executions > NVL (b.executions, 0)

AND e.sharable_mem > :esmt

ORDER BY e.sharable_mem DESC, e.hash_value, st.piece)

WHERE ROWNUM < &&top_n_sql;

-- SQL statements ordered by Version Count

TTITLE LEF 'SQL ordered by Version Count for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

'-> End Version Count Threshold: ' FORMAT 99999999 bvc -

SKIP 2;

COL aa FORMAT a80 HEADING -

' Version| Count Executions Hash Value |-------- ------------ ------------'

SELECT aa, hv

FROM ( SELECT /*+ ordered */

DECODE (

st.piece,

0, LPAD (TO_CHAR (e.version_count, '999,999'), 8) || ' '

|| LPAD (

TO_CHAR ( (e.executions - NVL (b.executions, 0)),

'999,999,999'),

12)

|| ' '

|| LPAD (e.hash_value, 12)

|| ' '

|| RPAD (' ', 45)

|| st.sql_text,

st.sql_text)

aa,

e.hash_value hv

FROM stats$sql_summary e, stats$sql_summary b, stats$sqltext st

WHERE b.snap_id(+) = :bid

AND b.dbid(+) = e.dbid

AND b.instance_number(+) = e.instance_number

AND b.hash_value(+) = e.hash_value

AND b.address(+) = e.address

AND b.text_subset(+) = e.text_subset

AND e.snap_id = :eid

AND e.dbid = :dbid

AND e.instance_number = :inst_num

AND e.hash_value = st.hash_value

AND e.text_subset = st.text_subset

AND st.piece < &&num_rows_per_hash

AND e.executions > NVL (b.executions, 0)

AND e.version_count > :evc

ORDER BY e.version_count DESC, e.hash_value, st.piece)

WHERE ROWNUM < &&top_n_sql;

SET UNDERLINE '-';

-- Instance Activity Statistics

TTITLE LEF 'Instance Activity Stats for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 2;

COLUMN st FORMAT a33 HEADING 'Statistic' TRUNC;

COLUMN dif FORMAT 999,999,999,990 HEADING 'Total';

COLUMN ps FORMAT 9,999,990.9 HEADING 'per Second';

COLUMN pt FORMAT 9,999,990.9 HEADING 'per Trans';

SELECT b.name st,

TO_NUMBER (

DECODE (INSTR (b.name, 'current'), 0, e.VALUE - b.VALUE, NULL))

dif,

TO_NUMBER (

DECODE (INSTR (b.name, 'current'),

0, ROUND ( (e.VALUE - b.VALUE) / :ela, 2),

NULL))

ps,

TO_NUMBER (

DECODE (INSTR (b.name, 'current'),

0, ROUND ( (e.VALUE - b.VALUE) / :tran, 2),

NULL))

FROM stats$sysstat b, stats$sysstat e

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.name = e.name

AND e.VALUE >= b.VALUE

AND e.VALUE > 0

ORDER BY st;

-- Session Wait Events

TTITLE LEF 'Session Wait Events for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF 'Session Id: ' sess_id ' Serial#: ' serial -

SKIP 1 -

LEF '-> ordered by wait time desc, waits desc (idle events last)' -

SKIP 2;

SELECT e.event,

e.total_waits - NVL (b.total_waits, 0) waits,

e.total_timeouts - NVL (b.total_timeouts, 0) timeouts,

e.time_waited - NVL (b.time_waited, 0) time,

DECODE (

(e.total_waits - NVL (b.total_waits, 0)),

0, TO_NUMBER (NULL),

(e.time_waited - NVL (b.time_waited, 0))

/ (e.total_waits - NVL (b.total_waits, 0))

* 10)

wt,

(e.total_waits - NVL (b.total_waits, 0)) / :tran txwaits,

DECODE (i.event, NULL, 0, 99) idle

FROM stats$session_event b,

stats$session_event e,

stats$idle_event i,

stats$snapshot bs,

stats$snapshot es

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.event = e.event

AND e.total_waits > NVL (b.total_waits, 0)

AND i.event(+) = e.event

AND bs.snap_id = b.snap_id

AND es.snap_id = e.snap_id

AND bs.dbid = b.dbid

AND es.dbid = b.dbid

AND bs.dbid = e.dbid

AND es.dbid = e.dbid

AND bs.instance_number = b.instance_number

AND es.instance_number = b.instance_number

AND bs.instance_number = e.instance_number

AND es.instance_number = e.instance_number

AND bs.session_id = es.session_id

AND bs.serial# = es.serial#

ORDER BY idle, time DESC, waits DESC;

-- Session Statistics

TTITLE LEF 'Session Statistics for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF 'Session Id: ' sess_id ' Serial#: ' serial -

SKIP 2;

SELECT LOWER (SUBSTR (ss.name, 1, 38)) st,

TO_NUMBER (

DECODE (INSTR (ss.name, 'current'), 0, e.VALUE - b.VALUE, NULL))

dif,

TO_NUMBER (

DECODE (INSTR (ss.name, 'current'),

0, ROUND ( (e.VALUE - b.VALUE) / :ela, 2),

NULL))

ps,

TO_NUMBER (

DECODE (

INSTR (ss.name, 'current'),

0, DECODE (:strn,

0, ROUND (e.VALUE - b.VALUE),

ROUND ( (e.VALUE - b.VALUE) / :strn, 2), NULL)))

FROM stats$sesstat b,

stats$sesstat e,

stats$sysstat ss,

stats$snapshot bs,

stats$snapshot es

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND ss.snap_id = :eid

AND ss.dbid = :dbid

AND ss.instance_number = :inst_num

AND b.statistic# = e.statistic#

AND ss.statistic# = e.statistic#

AND e.VALUE > b.VALUE

AND bs.snap_id = b.snap_id

AND es.snap_id = e.snap_id

AND bs.dbid = b.dbid

AND es.dbid = b.dbid

AND bs.dbid = e.dbid

AND es.dbid = e.dbid

AND bs.dbid = ss.dbid

AND es.dbid = ss.dbid

AND bs.instance_number = b.instance_number

AND es.instance_number = b.instance_number

AND bs.instance_number = ss.instance_number

AND es.instance_number = ss.instance_number

AND bs.instance_number = e.instance_number

AND es.instance_number = e.instance_number

AND bs.session_id = es.session_id

AND bs.serial# = es.serial#

ORDER BY st;

-- Tablespace IO summary statistics

TTITLE LEF 'Tablespace IO Stats for '

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF '->ordered by IOs (Reads + Writes) desc' -

SKIP 2;

COL tsname FORMAT a30 HEADING 'Tablespace';

COL reads FORMAT 9,999,999,990 HEADING 'Reads' NEWLINE;

COL atpr FORMAT 990.0 HEADING 'Av|Rd(ms)';

COL writes FORMAT 999,999,990 HEADING 'Writes';

COL waits FORMAT 9,999,990 HEADING 'Buffer|Waits'

COL atpwt FORMAT 990.0 HEADING 'Av Buf|Wt(ms)';

COL rps FORMAT 99,999 HEADING 'Av|Reads/s';

COL wps FORMAT 99,999 HEADING 'Av|Writes/s';

COL bpr FORMAT 99.0 HEADING 'Av|Blks/Rd';

COL ios NOPRINT

SELECT e.tsname,

SUM (e.phyrds - NVL (b.phyrds, 0)) reads,

SUM (e.phyrds - NVL (b.phyrds, 0)) / :ela rps,

DECODE (

SUM (e.phyrds - NVL (b.phyrds, 0)),

0, 0,

(SUM (e.readtim - NVL (b.readtim, 0))

/ SUM (e.phyrds - NVL (b.phyrds, 0)))

* 10)

atpr,

DECODE (

SUM (e.phyrds - NVL (b.phyrds, 0)),

0, TO_NUMBER (NULL),

SUM (e.phyblkrd - NVL (b.phyblkrd, 0))

/ SUM (e.phyrds - NVL (b.phyrds, 0)))

bpr,

SUM (e.phywrts - NVL (b.phywrts, 0)) writes,

SUM (e.phywrts - NVL (b.phywrts, 0)) / :ela wps,

SUM (e.wait_count - NVL (b.wait_count, 0)) waits,

DECODE (

SUM (e.wait_count - NVL (b.wait_count, 0)),

0, 0,

(SUM (e.time - NVL (b.time, 0))

/ SUM (e.wait_count - NVL (b.wait_count, 0)))

* 10)

atpwt,

SUM (e.phyrds - NVL (b.phyrds, 0))

+ SUM (e.phywrts - NVL (b.phywrts, 0))

ios

FROM stats$filestatxs e, stats$filestatxs b

WHERE b.snap_id(+) = :bid

AND e.snap_id = :eid

AND b.dbid(+) = :dbid

AND e.dbid = :dbid

AND b.dbid(+) = e.dbid

AND b.instance_number(+) = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number(+) = e.instance_number

AND b.tsname(+) = e.tsname

AND b.filename(+) = e.filename

AND ( (e.phyrds - NVL (b.phyrds, 0))

+ (e.phywrts - NVL (b.phywrts, 0))) > 0

GROUP BY e.tsname

UNION

SELECT e.tsname tbsp,

SUM (e.phyrds - NVL (b.phyrds, 0)) reads,

SUM (e.phyrds - NVL (b.phyrds, 0)) / :ela rps,

DECODE (

SUM (e.phyrds - NVL (b.phyrds, 0)),

0, 0,

(SUM (e.readtim - NVL (b.readtim, 0))

/ SUM (e.phyrds - NVL (b.phyrds, 0)))

* 10)

atpr,

DECODE (

SUM (e.phyrds - NVL (b.phyrds, 0)),

0, TO_NUMBER (NULL),

SUM (e.phyblkrd - NVL (b.phyblkrd, 0))

/ SUM (e.phyrds - NVL (b.phyrds, 0)))

bpr,

SUM (e.phywrts - NVL (b.phywrts, 0)) writes,

SUM (e.phywrts - NVL (b.phywrts, 0)) / :ela wps,

SUM (e.wait_count - NVL (b.wait_count, 0)) waits,

DECODE (

SUM (e.wait_count - NVL (b.wait_count, 0)),

0, 0,

(SUM (e.time - NVL (b.time, 0))

/ SUM (e.wait_count - NVL (b.wait_count, 0)))

* 10)

atpwt,

SUM (e.phyrds - NVL (b.phyrds, 0))

+ SUM (e.phywrts - NVL (b.phywrts, 0))

ios

FROM stats$tempstatxs e, stats$tempstatxs b

WHERE b.snap_id(+) = :bid

AND e.snap_id = :eid

AND b.dbid(+) = :dbid

AND e.dbid = :dbid

AND b.dbid(+) = e.dbid

AND b.instance_number(+) = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number(+) = e.instance_number

AND b.tsname(+) = e.tsname

AND b.filename(+) = e.filename

AND ( (e.phyrds - NVL (b.phyrds, 0))

+ (e.phywrts - NVL (b.phywrts, 0))) > 0

GROUP BY e.tsname

ORDER BY ios DESC;

-- File IO statistics

TTITLE LEF 'File IO Stats for '

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF '->ordered by Tablespace, File' -

SKIP 2;

COL tsname FORMAT a24 HEADING 'Tablespace';

COL filename FORMAT a52 HEADING 'Filename';

COL reads FORMAT 9,999,999,990 HEADING 'Reads'

BREAK ON tsname SKIP 1;

SELECT e.tsname,

e.filename,

e.phyrds - NVL (b.phyrds, 0) reads,

(e.phyrds - NVL (b.phyrds, 0)) / :ela rps,

DECODE (

(e.phyrds - NVL (b.phyrds, 0)),

0, TO_NUMBER (NULL),

( (e.readtim - NVL (b.readtim, 0)) / (e.phyrds - NVL (b.phyrds, 0)))

* 10)

atpr,

DECODE (

(e.phyrds - NVL (b.phyrds, 0)),

0, TO_NUMBER (NULL),

(e.phyblkrd - NVL (b.phyblkrd, 0)) / (e.phyrds - NVL (b.phyrds, 0)))

bpr,

e.phywrts - NVL (b.phywrts, 0) writes,

(e.phywrts - NVL (b.phywrts, 0)) / :ela wps,

e.wait_count - NVL (b.wait_count, 0) waits,

DECODE (

(e.wait_count - NVL (b.wait_count, 0)),

0, TO_NUMBER (NULL),

( (e.time - NVL (b.time, 0))

/ (e.wait_count - NVL (b.wait_count, 0)))

* 10)

atpwt

FROM stats$filestatxs e, stats$filestatxs b

WHERE b.snap_id(+) = :bid

AND e.snap_id = :eid

AND b.dbid(+) = :dbid

AND e.dbid = :dbid

AND b.dbid(+) = e.dbid

AND b.instance_number(+) = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number(+) = e.instance_number

AND b.tsname(+) = e.tsname

AND b.filename(+) = e.filename

AND ( (e.phyrds - NVL (b.phyrds, 0))

+ (e.phywrts - NVL (b.phywrts, 0))) > 0

UNION

SELECT e.tsname,

e.filename,

e.phyrds - NVL (b.phyrds, 0) reads,

(e.phyrds - NVL (b.phyrds, 0)) / :ela rps,

DECODE (

(e.phyrds - NVL (b.phyrds, 0)),

0, TO_NUMBER (NULL),

( (e.readtim - NVL (b.readtim, 0)) / (e.phyrds - NVL (b.phyrds, 0)))

* 10)

atpr,

DECODE (

(e.phyrds - NVL (b.phyrds, 0)),

0, TO_NUMBER (NULL),

(e.phyblkrd - NVL (b.phyblkrd, 0)) / (e.phyrds - NVL (b.phyrds, 0)))

bpr,

e.phywrts - NVL (b.phywrts, 0) writes,

(e.phywrts - NVL (b.phywrts, 0)) / :ela wps,

e.wait_count - NVL (b.wait_count, 0) waits,

DECODE (

(e.wait_count - NVL (b.wait_count, 0)),

0, TO_NUMBER (NULL),

( (e.time - NVL (b.time, 0))

/ (e.wait_count - NVL (b.wait_count, 0)))

* 10)

atpwt

FROM stats$tempstatxs e, stats$tempstatxs b

WHERE b.snap_id(+) = :bid

AND e.snap_id = :eid

AND b.dbid(+) = :dbid

AND e.dbid = :dbid

AND b.dbid(+) = e.dbid

AND b.instance_number(+) = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number(+) = e.instance_number

AND b.tsname(+) = e.tsname

AND b.filename(+) = e.filename

AND ( (e.phyrds - NVL (b.phyrds, 0))

+ (e.phywrts - NVL (b.phywrts, 0))) > 0

ORDER BY tsname, filename;

-- Buffer pools

TTITLE LEF 'Buffer Pool Statistics for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF '-> Pools D: default pool, K: keep pool, R: recycle pool' -

SKIP 2;

COL id FORMAT 99 HEADING 'Set|Id';

COL name FORMAT a1 HEADING 'P|-' TRUNC;

COL buffs FORMAT 999,999,999 HEADING 'Buffer|Gets|-----------';

COL conget FORMAT 9,999,999,999 HEADING 'Consistent|Gets|-------------';

COL phread FORMAT 999,999,999 HEADING 'Physical|Reads|-----------';

COL phwrite FORMAT 99,999,999 HEADING 'Physical|Writes|----------';

COL fbwait FORMAT 999,999 HEADING 'Free|Buffer|Waits|-------';

COL wcwait FORMAT 999,999 HEADING 'Write| Complete|Waits|--------';

COL bbwait FORMAT 99,999,999 HEADING 'Buffer|Busy|Waits|----------'

SET COLSEP '' UNDERLINE OFF;

SELECT e.name name,

e.buf_got - b.buf_got buffs,

e.consistent_gets - b.consistent_gets conget,

e.physical_reads - b.physical_reads phread,

e.physical_writes - b.physical_writes phwrite,

e.free_buffer_wait - b.free_buffer_wait fbwait,

e.write_complete_wait - b.write_complete_wait wcwait,

e.buffer_busy_wait - b.buffer_busy_wait bbwait

FROM stats$buffer_pool_statistics b, stats$buffer_pool_statistics e

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.dbid = e.dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number = e.instance_number

AND b.id = e.id

ORDER BY e.name;

SET COLSEP ' ' UNDERLINE ON;

-- Buffer waits summary

SET NEWPAGE 5;

TTITLE LEF 'Buffer wait Statistics for '

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF '-> ordered by wait time desc, waits desc' -

SKIP 2;

COLUMN class HEADING 'Class';

COLUMN icnt FORMAT 99,999,990 HEADING 'Waits';

COLUMN itim FORMAT 9,999,990 HEADING 'Tot Wait|Time (cs)';

COLUMN iavg FORMAT 999,990 HEADING 'Avg|Time (cs)';

SELECT e.class,

e.wait_count - NVL (b.wait_count, 0) icnt,

e.time - NVL (b.time, 0) itim,

(e.time - NVL (b.time, 0)) / (e.wait_count - NVL (b.wait_count, 0))

iavg

FROM stats$waitstat b, stats$waitstat e

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.dbid = e.dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number = e.instance_number

AND b.class = e.class

AND b.wait_count < e.wait_count

ORDER BY itim DESC, icnt DESC;

-- Enqueue activity

TTITLE LEF 'Enqueue activity for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF '-> ordered by waits desc, gets desc' -

SKIP 2;

COL gets FORMAT 999,999,990 HEADING 'Gets';

COL ename FORMAT a10 HEADING 'Enqueue'

COL waits FORMAT 9,999,990 HEADING 'Waits'

SELECT e.name ename,

e.gets - NVL (b.gets, 0) gets,

e.waits - NVL (b.waits, 0) waits

FROM stats$enqueuestat b, stats$enqueuestat e

WHERE b.snap_id(+) = :bid

AND e.snap_id = :eid

AND b.dbid(+) = :dbid

AND e.dbid = :dbid

AND b.dbid(+) = e.dbid

AND b.instance_number(+) = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number(+) = e.instance_number

AND b.name(+) = e.name

AND e.waits - NVL (b.waits, 0) > 0

ORDER BY waits DESC, gets DESC;

SET NEWPAGE 0;

-- Rollback segment

TTITLE LEF 'Rollback Segment Stats for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF '->A high value for "Pct Waits" suggests more rollback segments may be required'

SKIP 2;

COLUMN usn FORMAT 990 HEADING 'RBS No' EN;

COLUMN gets FORMAT 9,999,990.9 HEADING 'Trans Table|Gets' EN;

COLUMN waits FORMAT 990.99 HEADING 'Pct|Waits';

COLUMN writes FORMAT 99,999,999,990 HEADING 'Undo Bytes|Written' EN;

COLUMN wraps FORMAT 999,990 HEADING 'Wraps';

COLUMN shrinks FORMAT 999,990 HEADING 'Shrinks';

COLUMN extends FORMAT 999,990 HEADING 'Extends';

COLUMN rssize FORMAT 99,999,999,990 HEADING 'Segment Size';

COLUMN active FORMAT 99,999,999,990 HEADING 'Avg Active';

COLUMN optsize FORMAT 99,999,999,990 HEADING 'Optimal Size';

COLUMN hwmsize FORMAT 99,999,999,990 HEADING 'Maximum Size';

SELECT b.usn,

e.gets - b.gets gets,

TO_NUMBER (

DECODE (e.gets,

b.gets, NULL,

(e.waits - b.waits) * 100 / (e.gets - b.gets)))

waits,

e.writes - b.writes writes,

e.wraps - b.wraps wraps,

e.shrinks - b.shrinks shrinks,

e.extends - b.extends extends

FROM stats$rollstat b, stats$rollstat e

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.dbid = e.dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number = e.instance_number

AND e.usn = b.usn

ORDER BY e.usn;

TTITLE LEF 'Rollback Segment Storage for '

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF '->Optimal Size should be larger than Avg Active'

SKIP 2;

SELECT b.usn,

e.rssize,

e.aveactive active,

TO_NUMBER (DECODE (e.optsize, -4096, NULL, e.optsize)) optsize,

e.hwmsize

FROM stats$rollstat b, stats$rollstat e

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.dbid = e.dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number = e.instance_number

AND e.usn = b.usn

ORDER BY e.usn;

-- Latch Activity

TTITLE LEF 'Latch Activity for '

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF '->"Get Requests", "Pct Get Miss" and "Avg Slps/Miss" are ' -

'statistics for ' SKIP 1 -

' willing-to-wait latch get requests' -

SKIP 1 -

LEF '->"NoWait Requests", "Pct NoWait Miss" are for ' -

'no-wait latch get requests' -

SKIP 1 -

LEF '->"Pct Misses" for both should be very close to 0.0'

SKIP 2;

COLUMN name FORMAT a29 HEADING 'Latch Name' TRUNC;

COLUMN gets FORMAT 9,999,999,990 HEADING 'Get|Requests';

COLUMN missed FORMAT 990.9 HEADING 'Pct|Get|Miss';

COLUMN sleeps FORMAT 990.9 HEADING 'Avg|Slps|/Miss';

COLUMN nowai FORMAT 999,999,990 HEADING 'NoWait|Requests';

COLUMN imiss FORMAT 990.9 HEADING 'Pct|NoWait|Miss';

SELECT b.name name,

e.gets - b.gets gets,

TO_NUMBER (

DECODE (e.gets,

b.gets, NULL,

(e.misses - b.misses) * 100 / (e.gets - b.gets)))

missed,

TO_NUMBER (

DECODE (e.misses,

b.misses, NULL,

(e.sleeps - b.sleeps) / (e.misses - b.misses)))

sleeps,

e.immediate_gets - b.immediate_gets nowai,

TO_NUMBER (

DECODE (

e.immediate_gets,

b.immediate_gets, NULL,

(e.immediate_misses - b.immediate_misses)

* 100

/ (e.immediate_gets - b.immediate_gets)))

imiss

FROM stats$latch b, stats$latch e

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.dbid = e.dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number = e.instance_number

AND b.name = e.name

AND e.gets - b.gets > 0

ORDER BY name, sleeps;

-- Latch Sleep breakdown

TTITLE LEF 'Latch Sleep breakdown for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF '-> ordered by misses desc'

SKIP 2;

COLUMN name FORMAT a26 HEADING 'Latch Name' TRUNC;

COLUMN sleeps FORMAT 99,999,990 HEADING 'Sleeps';

COLUMN spin_gets FORMAT 99,999,990 HEADING 'Spin|Gets';

COLUMN misses FORMAT 99,999,990 HEADING 'Misses';

COLUMN sleep4 FORMAT a12 HEADING 'Spin &|Sleeps 1->4';

SELECT b.name name,

e.gets - b.gets gets,

e.misses - b.misses misses,

e.sleeps - b.sleeps sleeps,

TO_CHAR (e.spin_gets - b.spin_gets)

|| '/'

|| TO_CHAR (e.sleep1 - b.sleep1)

|| '/'

|| TO_CHAR (e.sleep2 - b.sleep2)

|| '/'

|| TO_CHAR (e.sleep3 - b.sleep3)

|| '/'

|| TO_CHAR (e.sleep4 - b.sleep4)

sleep4

FROM stats$latch b, stats$latch e

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.dbid = e.dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number = e.instance_number

AND b.name = e.name

AND e.sleeps - b.sleeps > 0

ORDER BY misses DESC;

-- Latch Miss sources

TTITLE LEF 'Latch Miss Sources for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF '-> only latches with sleeps are shown' -

SKIP 1 -

LEF '-> ordered by name, sleeps desc' -

SKIP 2;

COLUMN parent FORMAT a24 HEADING 'Latch Name' TRUNC;

COLUMN where_from FORMAT a26 HEADING 'Where' TRUNC;

COLUMN nwmisses FORMAT 99,990 HEADING 'NoWait|Misses';

COLUMN sleeps FORMAT 9,999,990 HEADING ' Sleeps';

COLUMN waiter_sleeps FORMAT 99,999 HEADING 'Waiter|Sleeps';

SELECT e.parent_name parent,

e.where_in_code where_from,

e.nwfail_count - NVL (b.nwfail_count, 0) nwmisses,

e.sleep_count - NVL (b.sleep_count, 0) sleeps,

e.wtr_slp_count - NVL (b.wtr_slp_count, 0) waiter_sleeps

FROM stats$latch_misses_summary b, stats$latch_misses_summary e

WHERE b.snap_id(+) = :bid

AND e.snap_id = :eid

AND b.dbid(+) = :dbid

AND e.dbid = :dbid

AND b.dbid(+) = e.dbid

AND b.instance_number(+) = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number(+) = e.instance_number

AND b.parent_name(+) = e.parent_name

AND b.where_in_code(+) = e.where_in_code

AND e.sleep_count > NVL (b.sleep_count, 0)

ORDER BY e.parent_name, sleeps DESC;

-- Parent Latch

TTITLE LEF 'Parent Latch Statistics ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF '-> only latches with sleeps are shown' -

SKIP 1 -

LEF '-> ordered by name' -

SKIP 2;

COLUMN name FORMAT a29 HEADING 'Latch Name' TRUNC;

SELECT l.name parent,

lp.gets,

lp.misses,

lp.sleeps,

lp.sleep4

FROM (SELECT e.instance_number,

e.dbid,

e.snap_id,

e.latch#,

e.gets - b.gets gets,

e.misses - b.misses misses,

e.sleeps - b.sleeps sleeps,

TO_CHAR (e.spin_gets - b.spin_gets)

|| '/'

|| TO_CHAR (e.sleep1 - b.sleep1)

|| '/'

|| TO_CHAR (e.sleep2 - b.sleep2)

|| '/'

|| TO_CHAR (e.sleep3 - b.sleep3)

|| '/'

|| TO_CHAR (e.sleep4 - b.sleep4)

sleep4

FROM stats$latch_parent b, stats$latch_parent e

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.dbid = e.dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number = e.instance_number

AND b.latch# = e.latch#

AND e.sleeps - b.sleeps > 0) lp,

stats$latch l

WHERE l.snap_id = lp.snap_id

AND l.dbid = lp.dbid

AND l.instance_number = lp.instance_number

AND l.latch# = lp.latch#

ORDER BY name;

-- Latch Children

TTITLE LEF 'Child Latch Statistics ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF '-> only latches with sleeps are shown' -

SKIP 1 -

LEF '-> ordered by name, gets desc' -

SKIP 2;

COLUMN name FORMAT a22 HEADING 'Latch Name' TRUNC;

COLUMN child FORMAT 99999 HEADING 'Child|Num';

COLUMN sleep4 FORMAT a13 HEADING 'Spin &|Sleeps 1->4';

SELECT l.name,

lc.child,

lc.gets,

lc.misses,

lc.sleeps,

lc.sleep4

FROM (SELECT /*+ ordered use_hash(b) */

e.instance_number,

e.dbid,

e.snap_id,

e.latch#,

e.child# child,

e.gets - b.gets gets,

e.misses - b.misses misses,

e.sleeps - b.sleeps sleeps,

TO_CHAR (e.spin_gets - b.spin_gets)

|| '/'

|| TO_CHAR (e.sleep1 - b.sleep1)

|| '/'

|| TO_CHAR (e.sleep2 - b.sleep2)

|| '/'

|| TO_CHAR (e.sleep3 - b.sleep3)

|| '/'

|| TO_CHAR (e.sleep4 - b.sleep4)

sleep4

FROM stats$latch_children e, stats$latch_children b

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.dbid = e.dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number = e.instance_number

AND b.latch# = e.latch#

AND b.child# = e.child#

AND e.sleeps - b.sleeps > 0) lc,

stats$latch l

WHERE l.snap_id = lc.snap_id

AND l.dbid = lc.dbid

AND l.instance_number = lc.instance_number

AND l.latch# = lc.latch#

ORDER BY name, gets DESC;

-- Dictionary Cache

TTITLE LEF 'Dictionary Cache Stats for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

LEF '->"Pct Misses" should be very low (< 2% in most cases)'

SKIP 1 -

LEF '->"Cache Usage" is the number of cache entries being used'

SKIP 1 -

LEF '->"Pct SGA" is the ratio of usage to allocated size for that cache'

SKIP 2;

COLUMN param FORMAT a22 HEADING 'Cache' TRUNC;

COLUMN gets FORMAT 999,999,990 HEADING 'Get|Requests';

COLUMN getm FORMAT 990.9 HEADING 'Pct|Miss';

COLUMN scans FORMAT 999,990 HEADING 'Scan|Requests';

COLUMN scanm FORMAT 90.9 HEADING 'Pct|Miss';

COLUMN mods FORMAT 999,990 HEADING 'Mod|Req';

COLUMN usage FORMAT 9,990 HEADING 'Final|Usage';

COLUMN sgapct FORMAT 990 HEADING 'Pct|SGA';

SELECT LOWER (b.parameter) param,

e.gets - b.gets gets,

TO_NUMBER (

DECODE (e.gets,

b.gets, NULL,

(e.getmisses - b.getmisses) * 100 / (e.gets - b.gets)))

getm,

e.scans - b.scans scans,

TO_NUMBER (

DECODE (e.scans,

b.scans, NULL,

(e.scanmisses - b.scanmisses) * 100 / (e.scans - b.scans)))

scanm,

e.modifications - b.modifications mods,

e.usage usage,

e.usage * 100 / e.total_usage sgapct

FROM stats$rowcache_summary b, stats$rowcache_summary e

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.dbid = e.dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number = e.instance_number

AND b.parameter = e.parameter

ORDER BY param;

-- Library Cache

SET NEWPAGE 2;

TTITLE LEF 'Library Cache Activity for '

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 1 -

'->"Pct Misses" should be very low ' SKIP 2;

COLUMN namespace HEADING 'Namespace';

COLUMN gets FORMAT 999,999,990 HEADING 'Get|Requests';

COLUMN pins FORMAT 9,999,999,990 HEADING 'Pin|Requests';

COLUMN getm FORMAT 990.9 HEADING 'Pct|Miss';

COLUMN pinm FORMAT 990.9 HEADING 'Pct|Miss';

COLUMN reloads FORMAT 9,999,990 HEADING 'Reloads';

COLUMN inv FORMAT 999,990 HEADING 'Invali-|dations';

SELECT b.namespace,

e.gets - b.gets gets,

TO_NUMBER (

DECODE (e.gets,

b.gets, NULL,

100 - (e.gethits - b.gethits) * 100 / (e.gets - b.gets)))

getm,

e.pins - b.pins pins,

TO_NUMBER (

DECODE (e.pins,

b.pins, NULL,

100 - (e.pinhits - b.pinhits) * 100 / (e.pins - b.pins)))

pinm,

e.reloads - b.reloads reloads,

e.invalidations - b.invalidations inv

FROM stats$librarycache b, stats$librarycache e

WHERE b.snap_id = :bid

AND e.snap_id = :eid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.dbid = e.dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number = e.instance_number

AND b.namespace = e.namespace;

-- SGA

SET NEWPAGE 0;

COLUMN name FORMAT a30 HEADING 'SGA regions';

COLUMN value FORMAT 999,999,999,990 HEADING 'Size in Bytes';

BREAK ON REPORT;

COMPUTE SUM OF VALUE ON REPORT;

TTITLE LEF 'SGA Memory Summary for ' -

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 2;

SELECT name, VALUE

FROM stats$sga

WHERE snap_id = :eid AND dbid = :dbid AND instance_number = :inst_num

ORDER BY name;

CLEAR BREAK COMPUTE;

SET NEWPAGE 2;

COLUMN name FORMAT a30 HEADING 'SGA Component';

COLUMN b_value FORMAT 99,999,999,990 HEADING 'Start snap';

COLUMN e_value FORMAT 99,999,999,990 HEADING 'End snap';

COLUMN change FORMAT 99,999,990 HEADING 'Change |End - Start' EN;

TTITLE LEF 'SGA breakdown difference for '

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 2;

COLUMN pool HEADING 'Pool' FORMAT a11;

COLUMN name HEADING "Name" FORMAT a24;

COLUMN snap1 FORMAT 9,999,999,999 HEADING 'Begin value';

COLUMN snap2 FORMAT 9,999,999,999 HEADING 'End value';

COLUMN diff FORMAT 99,999,999 HEADING 'Difference';

SELECT b.pool pool,

b.name name,

b.bytes snap1,

e.bytes snap2,

e.bytes - b.bytes diff

FROM stats$sgastat b, stats$sgastat e

WHERE e.snap_id = :eid

AND b.snap_id = :bid

AND b.dbid = :dbid

AND e.dbid = :dbid

AND b.dbid = e.dbid

AND b.instance_number = :inst_num

AND e.instance_number = :inst_num

AND b.instance_number = e.instance_number

AND b.name = e.name

AND NVL (b.pool, 'a') = NVL (e.pool, 'a')

ORDER BY b.pool, b.name;

-- Initialization Parameters

SET NEWPAGE 0;

COLUMN name FORMAT a29 HEADING 'Parameter Name' TRUNC;

COLUMN bval FORMAT a33 HEADING 'Begin value' TRUNC;

COLUMN eval FORMAT a14 HEADING 'End value|(if different)' TRUNC;

TTITLE LEF 'init.ora Parameters for '

'DB: ' db_name ' Instance: ' inst_name ' '

'Snaps: ' FORMAT 999999 begin_snap ' -' FORMAT 999999 end_snap -

SKIP 2;

SELECT e.name, b.VALUE bval, DECODE (b.VALUE, e.VALUE, ' ', e.VALUE) eval

FROM stats$parameter b, stats$parameter e

WHERE b.snap_id(+) = :bid

AND e.snap_id = :eid

AND b.dbid(+) = :dbid

AND e.dbid = :dbid

AND b.instance_number(+) = :inst_num

AND e.instance_number = :inst_num

AND b.name(+) = e.name

AND ( NVL (b.isdefault, 'X') = 'FALSE'

OR NVL (b.ismodified, 'X') <> 'FALSE'

OR e.ismodified <> 'FALSE'

OR NVL (e.VALUE, 0) <> NVL (b.VALUE, 0));

PROMPT

PROMPT End of Report

PROMPT

SPOOL OFF;

SET TERMOUT OFF;

CLEAR COLUMNS SQL;

TTITLE OFF;

BTITLE OFF;

REPFOOTER OFF;

SET LINESIZE 78 TERMOUT ON FEEDBACK 6;

UNDEFINE begin_snap

UNDEFINE end_snap

UNDEFINE report_name

UNDEFINE top_n_sql

UNDEFINE top_n_events

WHENEVER SQLERROR CONTINUE;

-- End of script file;

Oracle Apps Scripts & Tips

Pages

Thursday, April 4, 2013

Difference between values recorded in two oracle snapshots