Rem Rem $Header: rdbms/admin/sprepins.sql /main/59 2009/07/28 09:44:20 yberezin Exp $ Rem Rem sprepins.sql Rem Rem Copyright (c) 2001, 2009, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem sprepins.sql - StatsPack Report Instance Rem Rem DESCRIPTION Rem SQL*Plus command file to report on differences between Rem values recorded in two snapshots. Rem Rem This script requests the user for the dbid and instance number Rem of the instance to report on, before producing the standard Rem Statspack report. Rem Rem NOTES Rem Usually run as the STATSPACK owner, PERFSTAT Rem Rem MODIFIED (MM/DD/YY) Rem yberezin 07/27/09 - bug 8639400 Rem pmurthy 12/10/08 - Fix for Bug - 7149145 Rem bnayak 09/23/08 - To fix Bug 7385751 Rem cdgreen 12/03/07 - Streams - AWR sync Rem cdgreen 08/20/07 - 6317857 Rem cdgreen 08/10/07 - 6335987 Rem cdgreen 03/14/07 - 11 F2 Rem cdgreen 03/02/07 - use _FG for v$system_event Rem cdgreen 05/11/06 - 11 F1 Rem cdgreen 01/30/06 - 4631691 Rem cdgreen 05/05/06 - 5145816 Rem cdgreen 05/10/06 - 5215982 Rem cdgreen 05/23/05 - 4246955 Rem cdgreen 02/28/05 - 10gR2 misc Rem vbarrier 02/18/05 - 4081984/4071648 Rem cdgreen 10/29/04 - 10gR2_sqlstats Rem cdgreen 10/25/04 - 3970898 Rem vbarrier 09/03/04 - Wait Event Histogram Rem cdgreen 07/15/04 - sp_10_r2 Rem cdialeri 03/30/04 - 3356242 Rem vbarrier 03/18/04 - 3517841 Rem vbarrier 02/12/04 - 3412853/3378066 Rem vbarrier 01/30/04 - 3411063/3411129 Rem cdialeri 12/03/03 - 3290482 Rem cdialeri 10/14/03 - 10g - streams - rvenkate Rem cdialeri 08/06/03 - 10g F3 Rem vbarrier 02/25/03 - 10g RAC Rem cdialeri 11/15/02 - 10g R1 Rem cdialeri 10/29/02 - 2648471 Rem cdialeri 09/26/02 - 10.0 Rem vbarrier 07/14/02 - Segment Statistics: outerjoin + order by Rem vbarrier 07/10/02 - Input checking + capt/tot SQL + snapdays Rem vbarrier 03/20/02 - Module in SQL reporting + 2188360 Rem vbarrier 03/05/02 - Segment Statistics Rem spommere 02/14/02 - cleanup RAC stats that are no longer needed Rem spommere 02/08/02 - 2212357 Rem cdialeri 02/07/02 - 2218573 Rem cdialeri 01/30/02 - 2184717 Rem cdialeri 01/09/02 - 9.2 - features 2 Rem ykunitom 12/21/01 - 1396578: fixed '% Non-Parse CPU' Rem cdialeri 12/19/01 - 9.2 - features 1 Rem cdialeri 09/20/01 - 1767338,1910458,1774694 Rem cdialeri 04/26/01 - Renamed from spreport.sql Rem cdialeri 03/02/01 - 9.0 Rem cdialeri 09/12/00 - sp_1404195 Rem cdialeri 07/10/00 - 1349995 Rem cdialeri 06/21/00 - 1336259 Rem cdialeri 04/06/00 - 1261813 Rem cdialeri 03/28/00 - sp_purge Rem cdialeri 02/16/00 - 1191805 Rem cdialeri 11/01/99 - Enhance, 1059172 Rem cgervasi 06/16/98 - Remove references to wrqs Rem cmlim 07/30/97 - Modified system events Rem gwood.uk 02/30/94 - Modified Rem densor.uk 03/31/93 - Modified Rem cellis.uk 11/15/89 - Created Rem -- -- Get the report settings @@sprepcon.sql -- -- 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 newpage 1 recsep off; set trimspool on trimout on define "&" concat "." serveroutput on; set linesize &&linesize_fmt; set escape \ -- -- Must not be modified -- Bytes to gigabytes define btogb = 1073741824; -- Bytes to megabytes define btomb = 1048576; -- Bytes to kilobytes define btokb = 1024; -- Days to seconds define daystosecs = 86400; -- Centiseconds to seconds define cstos = 100; -- Microseconds to milli-seconds define ustoms = 1000; -- Microseconds to centi-seconds define ustocs = 10000; -- Microseconds to seconds define ustos = 1000000; define top_n_events = 5; define total_event_time_s_th = .001; define pct_cpu_diff_th = 5; -- -- Request the DB Id and Instance Number, if they are not specified column instt_num heading "Inst Num" format 99999; column instt_name heading "Instance" format a12; column dbb_name heading "DB Name" format a12; column dbbid heading "DB Id" format 9999999999 just c; column host heading "Host" format a12; prompt prompt prompt Instances in this Statspack schema prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ select distinct dbid dbbid , instance_number instt_num , db_name dbb_name , instance_name instt_name , host_name host from stats$database_instance; prompt prompt Using &&dbid for database Id prompt Using &&inst_num for instance number -- -- Set up the binds for dbid and instance_number variable dbid number; variable inst_num number; begin :dbid := &dbid; :inst_num := &inst_num; end; / -- -- Error reporting whenever sqlerror exit; variable max_snap_time char(10); declare cursor cidnum is select 'X' from stats$database_instance where instance_number = :inst_num and dbid = :dbid; cursor csnapid is select to_char(max(snap_time),'dd/mm/yyyy') from stats$snapshot where instance_number = :inst_num and dbid = :dbid; vx char(1); begin -- Check Database Id/Instance Number is a valid pair open cidnum; fetch cidnum into vx; if cidnum%notfound then raise_application_error(-20200, 'Database/Instance '||:dbid||'/'||:inst_num||' does not exist in STATS$DATABASE_INSTANCE'); end if; close cidnum; -- Check Snapshots exist for Database Id/Instance Number open csnapid; fetch csnapid into :max_snap_time; if csnapid%notfound then raise_application_error(-20200, 'No snapshots exist for Database/Instance '||:dbid||'/'||:inst_num); end if; close csnapid; end; / whenever sqlerror continue; -- -- Ask how many days of snapshots to display set termout on; column instart_fmt noprint; column inst_name format a12 heading 'Instance'; column db_name format a12 heading 'DB Name'; column snap_id format 99999990 heading 'Snap Id'; column snapdat format a17 heading 'Snap Started' just c; column lvl format 99 heading 'Snap|Level'; column commnt format a20 heading 'Comment'; prompt prompt prompt Specify the number of days of snapshots to choose from prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt Entering the number of days (n) will result in the most recent prompt (n) days of snapshots being listed. Pressing without prompt specifying a number lists all completed snapshots. prompt prompt set heading off; column num_days new_value num_days noprint; select 'Listing ' || decode( nvl('&&num_days', to_number('3.14','9D99','nls_numeric_characters=''.,''')) , to_number('3.14','9D99','nls_numeric_characters=''.,'''), 'all Completed Snapshots' , 0 , 'no snapshots' , 1 , 'the last day''s Completed Snapshots' , 'the last &num_days days of Completed Snapshots') , nvl('&&num_days', to_number('3.14','9D99','nls_numeric_characters=''.,''')) num_days from sys.dual; set heading on; -- -- List available snapshots break on inst_name on db_name on host on instart_fmt skip 1; ttitle off; select to_char(s.startup_time,' dd Mon "at" HH24:mi:ss') instart_fmt , di.instance_name inst_name , di.db_name db_name , s.snap_id snap_id , to_char(s.snap_time,'dd Mon YYYY HH24:mi') snapdat , s.snap_level lvl , substr(s.ucomment, 1,60) commnt 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.dbid = s.dbid and di.instance_number = s.instance_number and di.startup_time = s.startup_time and s.snap_time >= decode( to_number('&num_days') , to_number('3.14','9D99','nls_numeric_characters=''.,'''), s.snap_time , 0 , to_date('31-JAN-9999','DD-MON-YYYY') , to_date(:max_snap_time,'dd/mm/yyyy') - (to_number('&num_days') - 1)) order by db_name, instance_name, snap_id; clear break; ttitle off; -- -- Ask for the snapshots Id's which are to be compared prompt prompt prompt Specify the Begin and End Snapshot Ids prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt Begin Snapshot Id specified: &&begin_snap prompt prompt End Snapshot Id specified: &&end_snap prompt -- -- Set up the snapshot-related binds variable bid number; variable eid number; begin :bid := &begin_snap; :eid := &end_snap; end; / prompt -- -- Error reporting whenever sqlerror exit; declare cursor cspid(vspid stats$snapshot.snap_id%type) is select snap_time , startup_time , session_id , serial# from stats$snapshot where snap_id = vspid and instance_number = :inst_num and dbid = :dbid; bsnapt stats$snapshot.startup_time%type; bstart stats$snapshot.startup_time%type; bsesid stats$snapshot.session_id%type; bseria stats$snapshot.serial#%type; esnapt stats$snapshot.startup_time%type; estart stats$snapshot.startup_time%type; esesid stats$snapshot.session_id%type; eseria stats$snapshot.serial#%type; begin -- Check Begin Snapshot id is valid, get corresponding instance startup time open cspid(:bid); fetch cspid into bsnapt, bstart, bsesid, bseria; if cspid%notfound then raise_application_error(-20200, 'Begin Snapshot Id '||:bid||' does not exist for this database/instance'); end if; close cspid; -- Check End Snapshot id is valid and get corresponding instance startup time open cspid(:eid); fetch cspid into esnapt, estart, esesid, eseria; if cspid%notfound then raise_application_error(-20200, 'End Snapshot Id '||:eid||' does not exist for this database/instance'); end if; if esnapt <= bsnapt then raise_application_error(-20200, 'End Snapshot Id '||:eid||' must be greater than Begin Snapshot Id '||:bid); end if; close cspid; -- Check startup time is same for begin and end snapshot ids if ( bstart != estart) then raise_application_error(-20200, 'The instance was shutdown between snapshots '||:bid||' and '||:eid); end if; -- Check sessions are same for begin and end snapshot ids if (bsesid != esesid or bseria != eseria) then dbms_output.put_line('WARNING: SESSION STATISTICS WILL NOT BE PRINTED, as session statistics'); dbms_output.put_line('captured in begin and end snapshots are for different sessions'); dbms_output.put_line('(Begin Snap sid,serial#: '||bsesid||','||bseria||', End Snap sid,serial#: '||esesid||','||eseria||').'); dbms_output.put_line(''); end if; end; / whenever sqlerror continue; -- -- Get the database info to display in the report set termout off; column para new_value para; column versn new_value versn; column host_name new_value host_name; column platform_name new_value platform_name; column db_name new_value db_name; column inst_name new_value inst_name; column btime new_value btime; column etime new_value etime; column sutime new_value sutime; select parallel para , version versn , host_name host_name , platform_name platform_name , db_name db_name , instance_name inst_name , to_char(snap_time, 'YYYYMMDD HH24:MI:SS') btime , to_char(s.startup_time, 'DD-Mon-YY HH24:MI') sutime from stats$database_instance di , stats$snapshot s where s.snap_id = :bid and s.dbid = :dbid and s.instance_number = :inst_num and di.dbid = s.dbid and di.instance_number = s.instance_number and di.startup_time = s.startup_time; select to_char(snap_time, 'YYYYMMDD HH24:MI:SS') etime from stats$snapshot s where s.snap_id = :eid and s.dbid = :dbid and s.instance_number = :inst_num; variable para varchar2(9); variable versn varchar2(10); variable host_name varchar2(64); variable platform_name varchar2(101); variable db_name varchar2(20); variable inst_name varchar2(20); variable btime varchar2(25); variable etime varchar2(25); variable sutime varchar2(19); begin :para := '¶'; :versn := '&versn'; :host_name := '&host_name'; :platform_name := '&platform_name'; :db_name := '&db_name'; :inst_name := '&inst_name'; :btime := '&btime'; :etime := '&etime'; :sutime := '&sutime'; end; / define DBtime=1 define DBtimes=1 column DBtime new_value DBtime noprint column DBtimes new_value DBtimes noprint select (e.value - b.value) DBtime , round((e.value - b.value)/1000000,1) DBtimes from stats$sys_time_model e , stats$sys_time_model b , stats$time_model_statname sn 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 sn.stat_name = 'DB time' and b.stat_id = e.stat_id and e.stat_id = sn.stat_id; set termout on; -- -- 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; prompt prompt Specify the Report Name prompt ~~~~~~~~~~~~~~~~~~~~~~~ prompt The default report file name is &dflt_name.. To use this name, prompt press to continue, otherwise enter an alternative. prompt set heading off; column report_name new_value report_name noprint; select 'Using the report name ' || nvl('&&report_name','&dflt_name') , decode( instr(nvl('&&report_name','&dflt_name'),'.'), 0, nvl('&&report_name','&dflt_name')||'.lst' , nvl('&&report_name','&dflt_name')) report_name from sys.dual; prompt spool &report_name; -- -- Call statspack to calculate certain statistics -- set termout off heading off verify off newpage none; 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 phyrd number; variable phyrdl number; variable phyrc number; variable phyw number; variable prse number; variable hprs number; variable recr number; variable gets number; variable slr 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 bsp varchar2(512); variable esp varchar2(512); variable bbc varchar2(512); variable ebc varchar2(512); variable blb varchar2(512); variable elb varchar2(512); variable bs varchar2(512); variable twt number; variable tct_us 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; variable bocur number; variable eocur number; variable bpgaalloc number; variable epgaalloc number; variable bsgaalloc number; variable esgaalloc number; variable bnprocs number; variable enprocs number; variable timstat varchar2(20); variable statlvl varchar2(40); -- OS Stat variable bncpu number; variable encpu number; variable bncore number; variable encore number; variable bnsock number; variable ensock number; variable bpmem number; variable epmem number; variable blod number; variable elod number; variable itic number; variable btic number; variable iotic number; variable rwtic number; variable utic number; variable stic number; variable vmib number; variable vmob number; variable oscpuw number; -- OS Stat derived variable ttic number; variable ttics number; variable cpubrat number; variable cpuirat number; -- Time Model variable dbtim number; variable dbcpu number; variable bgela number; variable bgcpu number; variable totcpu number; variable totela number; variable prstela number; variable sqleela number; variable conmela number; variable bncpu number; -- RAC variables variable dmsd number; variable dmfc number; variable dmsi number; variable pmrv number; variable pmpt number; variable npmrv number; variable npmpt number; variable dbfr number; variable dpms number; variable dnpms number; variable glsg number; variable glag number; variable glgt number; variable gccrrv number; variable gccrrt number; variable gccrfl number; variable gccurv number; variable gccurt number; variable gccufl number; variable gccrsv number; variable gccrbt number; variable gccrft number; variable gccrst number; variable gccusv number; variable gccupt number; variable gccuft number; variable gccust number; variable msgsq number; variable msgsqt number; variable msgsqk number; variable msgsqtk number; variable msgrq number; variable msgrqt number; -- Or not column bncore new_value bncore noprint column encore new_value encore noprint column bnsock new_value bnsock noprint column ensock new_value ensock noprint select sum(case when snap_id = :bid and stat_name = 'NUM_CPU_CORES' then os.value else 0 end) bncore , sum(case when snap_id = :eid and stat_name = 'NUM_CPU_CORES' then os.value else 0 end) encore , sum(case when snap_id = :bid and stat_name = 'NUM_CPU_SOCKETS' then os.value else 0 end) bnsock , sum(case when snap_id = :eid and stat_name = 'NUM_CPU_SOCKETS' then os.value else 0 end) ensock from stats$osstat os , stats$osstatname osn where os.snap_id in (:bid, :eid) and os.dbid = :dbid and os.instance_number = :inst_num and osn.osstat_id = os.osstat_id; begin STATSPACK.STAT_CHANGES ( :bid, :eid , :dbid, :inst_num , :para -- End of IN arguments , :lhtr, :bfwt , :tran, :chng , :ucal, :urol , :rsiz , :phyr, :phyrd , :phyrdl, :phyrc , :phyw, :ucom , :prse, :hprs , :recr, :gets , :slr , :rlsr, :rent , :srtm, :srtd , :srtr, :strn , :lhr , :bbc, :ebc , :bsp, :esp , :blb , :bs, :twt , :logc, :prscpu , :tcpu, :exe , :prsela , :bspm, :espm , :bfrm, :efrm , :blog, :elog , :bocur, :eocur , :bpgaalloc, :epgaalloc , :bsgaalloc, :esgaalloc , :bnprocs, :enprocs , :timstat, :statlvl , :bncpu, :encpu -- OS Stat , :bpmem, :epmem , :blod, :elod , :itic, :btic , :iotic, :rwtic , :utic, :stic , :vmib, :vmob , :oscpuw , :dbtim, :dbcpu -- Time Model , :bgela, :bgcpu , :prstela,:sqleela , :conmela , :dmsd, :dmfc -- begin RAC , :dmsi , :pmrv, :pmpt , :npmrv, :npmpt , :dbfr , :dpms, :dnpms , :glsg, :glag , :glgt , :gccrrv, :gccrrt, :gccrfl , :gccurv, :gccurt, :gccufl , :gccrsv , :gccrbt, :gccrft , :gccrst, :gccusv , :gccupt, :gccuft , :gccust , :msgsq, :msgsqt , :msgsqk, :msgsqtk , :msgrq, :msgrqt -- end RAC ); :call := :ucal + :recr; -- Osstat -- total ticks (cs) :ttic := :btic + :itic; -- total ticks (s) :ttics := :ttic/100; -- CPU :totcpu := :dbcpu + :bgcpu; :totela := :dbtim + :bgela; -- Busy to total CPU ratio :cpubrat := :btic / :ttic; :cpuirat := :itic / :ttic; -- Cores/CPUs :bncore := &&bncore; :encore := &&encore; :bnsock := &&bnsock; :ensock := &&ensock; -- Total Call Time, microseconds :tct_us := :twt + :tcpu*10000; end; / -- -- Print stat consistency warnings set termout on heading off; begin if :statlvl = 'INCONSISTENT_BASIC' then dbms_output.put_line('WARNING: statistics_level setting changed between begin/end snaps: Time Model'); dbms_output.put_line(' data is INVALID'); end if; if :timstat = 'INCONSISTENT' then dbms_output.put_line('WARNING: timed_statistics setting changed between begin/end snaps: TIMINGS'); dbms_output.put_line(' ARE INVALID'); end if; end; / set heading on; -- -- Standard formatting column ch1 format a1 column ch3 format a3 column chr4n format a4 newline column ch4n format a4 newline column ch5 format a5 column ch5n format a5 newline column ch6 format a6 column ch6n format a6 newline column ch7 format a7 column ch7n format a7 newline column ch8 format a8 column ch8n format a8 newline column ch9 format a9 column ch10r format a10 just r column ch11 format a11 column ch11n format a11 newline column ch12 format a12 column ch12n format a12 newline column ch13 format a13 column ch14n format a14 newline column ch15 format a15 column ch15n format a15 newline column chr16 format a16 column ch16 format a16 column ch16t format a16 trunc column ch17 format a17 column ch17n format a17 newline column ch18n format a18 newline column ch19 format a19 column ch19n format a19 newline column ch20 format a20 column ch20n format a20 newline column ch21 format a21 column ch21n format a21 newline column ch22 format a22 column ch22t format a22 trunc column ch22n format a22 newline column ch23 format a23 column ch23n format a23 newline column ch24 format a24 column ch24n format a24 newline column ch25 format a25 column ch25 format a25 trunc column ch25n format a25 newline column ch26t format a26 trunc column ch27 format a27 column ch28 format a28 column ch28n format a28 newline column ch30 format a30 column ch30n format a30 newline column ch32 format a32 column ch32n format a32 newline column ch40n format a40 newline column ch42n format a42 newline column ch43n format a43 newline column ch45n format a45 newline column ch50 format a50 column ch50n format a50 newline column ch50t format a50 trunc column ch52n format a52 newline just r column ch53n format a53 newline column ch59n format a59 newline just r column ch60n format a60 newline column ch60t format a60 trunc column ch62 format a62 column ch62n format a62 newline column ch78n format a78 newline column ch80n format a80 newline column num3 format 999 just left column num3_1 format 990.9 column num3_2 format 990.99 column num3_2n format 990.99 newline column num4 format 9999 column num4c format 9,999 column num4c_2 format 9,999.99 column num4c_2n format 9,999.99 newline column num5 format 99999 column num5c format 99,999 column num5c_2 format 99,999.99 column num5c0_1 format 99,990.9 column num5c0_2 format 99,990.99 column num6 format 999999 column num6c format 999,999 column num6c_2 format 999,999.99 column num6c_1 format 999,999.9 column num6c_2n format 999,999.99 newline column num6cn format 999,999 newline column num7 format 9999999 column num7c format 9,999,999 column num7c_1 format 9,999,999.9 column num7c_2 format 9,999,999.99 column num8c format 99,999,999 column num8cn format 99,999,999 newline column num8c_1 format 99,999,999.9 column num8c_2 format 99,999,999.99 column num8cn format 99,999,999 newline column num9 format 999999999 column num9c format 999,999,999 column num9cn format 999,999,999 newline column num10c format 9,999,999,999 -- -- Summary Statistics -- -- -- Print database, instance, parallel, release, host and snapshot -- information prompt STATSPACK report for set newpage 1 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 just center; column host_name heading "Host" format a16 print; column para heading "RAC" format a3 print; column versn heading "Release" format a11 print; column sutime heading "Started" format a15 print; column a newline; set heading off select 'Database' ch8, ' DB Id ' ch11, 'Instance ' ch12, 'Inst Num' ch8, ' Startup Time ' ch15, 'Release ' ch11, 'RAC' ch3 , '~~~~~~~~' ch8n, '-----------' ch11, '------------' ch12, '--------' ch8, '---------------' ch15, '-----------' ch11, '---' ch3 , ' ' ch8n , :dbid dbid , :inst_name inst_name , :inst_num inst_num , :sutime sutime , :versn versn , :para para from sys.dual; set heading off select 'Host' ch4 , 'Name' ch16, 'Platform' ch22, ' CPUs' ch5, 'Cores' ch5, 'Sockets' ch7, ' Memory (G)' ch12 , '~~~~' ch4n, '----------------' ch16, '----------------------' ch22, '-----' ch5, '-----' ch5, '-------' ch7, '------------' ch12 , ' ' ch4n , :host_name ch16t , :platform_name ch22t , decode(:bncpu, null, to_number(null), :bncpu) num4 , decode(:bncore, null, to_number(null), :bncore) num4 , decode(:bnsock, null, to_number(null), :bnsock) num6 , decode(:bpmem, null, to_number(null), :bpmem/&&btogb) num7c_1 from sys.dual; set heading on; -- -- 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 just c; column etim new_value etim heading 'End Time' format a19 just c; column dur format 99,990.00; 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 esmtk new_value esmtk noprint; column bvc new_value bvc noprint; column evc new_value evc noprint; column bpc new_value bpc noprint; column epc new_value epc noprint; column bspr new_value bspr noprint; column espr new_value espr noprint; column bslr new_value bslr noprint; column eslr new_value eslr noprint; column bsbb new_value bsbb noprint; column esbb new_value esbb noprint; column bsrl new_value bsrl noprint; column esrl new_value esrl noprint; column bsiw new_value bsiw noprint; column esiw new_value esiw noprint; column bcrb new_value bcrb noprint; column ecrb new_value ecrb noprint; column bcub new_value bcub noprint; column ecub new_value ecub noprint; column blog format 99,999; column elog format 99,999; column ocs format 99,999.0; column comm format a18 trunc; column nl newline; column nl11 format a11 newline; column nl16 format a16 newline; set heading off; select 'Snapshot Snap Id Snap Time Sessions Curs/Sess Comment' nl , '~~~~~~~~ ---------- ------------------ -------- --------- ------------------' nl , 'Begin Snap:' ch11n , b.snap_id num9 , to_char(b.snap_time, 'dd-Mon-yy hh24:mi:ss') btim , :blog blog , :bocur/:blog ocs , b.ucomment comm , ' End Snap:' ch11n , e.snap_id num9 , to_char(e.snap_time, 'dd-Mon-yy hh24:mi:ss') etim , :elog elog , :eocur/:elog ocs , e.ucomment comm , ' Elapsed:' ch11n , round(((e.snap_time - b.snap_time) * 1440 * 60), 0)/60 dur -- mins , '(mins)' ch6 , 'Av Act Sess:' ch12 , :dbtim/&ustos/((e.snap_time - b.snap_time) * &daystosecs) num5c0_1 , ' DB time:' ch11n , :dbtim/&ustos/60 dur -- mins , '(mins)' ch6 , ' DB CPU:' ch12 , :dbcpu/&&ustos/60 num5c0_2 -- mins , '(mins)' ch6 , 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 , e.sharable_mem_th/&&btokb esmtk , b.version_count_th bvc , e.version_count_th evc , b.parse_calls_th bpc , e.parse_calls_th epc , b.seg_phy_reads_th bspr , e.seg_phy_reads_th espr , b.seg_log_reads_th bslr , e.seg_log_reads_th eslr , b.seg_buff_busy_th bsbb , e.seg_buff_busy_th esbb , b.seg_rowlock_w_th bsrl , e.seg_rowlock_w_th esrl , b.seg_itl_waits_th bsiw , e.seg_itl_waits_th esiw , b.seg_cr_bks_rc_th bcrb , e.seg_cr_bks_rc_th ecrb , b.seg_cu_bks_rc_th bcub , e.seg_cu_bks_rc_th ecub 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 esmtk number; variable bvc number; variable evc number; variable bpc number; variable epc number; variable spctim number; variable pct_sp_oss_cpu_diff number; begin :btim := '&btim'; :etim := '&etim'; :ela := to_number('&ela'); :instart := '&instart'; :bbgt := to_number('&bbgt'); :ebgt := to_number('&ebgt'); :bdrt := to_number('&bdrt'); :edrt := to_number('&edrt'); :bet := to_number('&bet'); :eet := to_number('&eet'); :bsmt := to_number('&bsmt'); :esmt := to_number('&esmt'); :esmtk := &esmtk; :bvc := to_number('&bvc'); :evc := to_number('&evc'); :bpc := to_number('&bpc'); :epc := to_number('&epc'); -- Statspack total CPU time (secs) - assumes Begin CPU count and End -- CPU count are identical :spctim := :ela * :encpu; -- Statspack to OS Stat CPU percentage select decode(:ttics, null, 0, 0, 0 ,100*(abs(:spctim-round(:ttics))/:spctim)) into :pct_sp_oss_cpu_diff from sys.dual; end; / -- -- set heading off; -- -- Cache Sizes select 'Cache Sizes Begin End' ch50n , '~~~~~~~~~~~ ---------- ----------' ch50n , ' Buffer Cache:' ch17n , lpad(to_char(round(:bbc/&&btomb),'999,999') || 'M', 10) ch10r , lpad(decode( :ebc, :bbc, null , to_char(round(:ebc/&&btomb), '999,999') || 'M'), 10) ch10r , ' Std Block Size:' ch17 , lpad(to_char((:bs/&&btokb) ,'999') || 'K',10) ch10r , ' Shared Pool:' ch17n , lpad(to_char(round(:bsp/&&btomb),'999,999') || 'M',10) ch10r , lpad(decode( :esp, :bsp, null , to_char(round(:esp/&&btomb), '999,999') || 'M'), 10) ch10r , ' Log Buffer:' ch17 , lpad(to_char(round(:blb/&&btokb),'999,999') || 'K', 10) ch10r from sys.dual; -- -- Load Profile column lpval format 999,999,999,990.9; column lpsval format 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; column bpctval format 9990.99; select 'Load Profile Per Second Per Transaction Per Exec Per Call' ,'~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------' ,' DB time(s):' ch17n, round(:dbtim/&ustos/:ela,2) lpval , round(:dbtim/&ustos/:tran,2) lpval , round(:dbtim/&ustos/:exe,2) lpsval , round(:dbtim/&ustos/:ucal,2) lpsval ,' DB CPU(s):' ch17n, round(:dbcpu/&ustos/:ela,2) lpval , round(:dbcpu/&ustos/:tran,2) lpval , round(:dbcpu/&ustos/:exe,2) lpsval , round(:dbcpu/&ustos/:ucal,2) lpsval ,' Redo size:' ch17n, round(:rsiz/:ela,2) lpval , round(:rsiz/:tran,2) lpval ,' Logical reads:' ch17n, round(:slr/:ela,2) lpval , round(:slr/:tran,2) lpval ,' Block changes:' ch17n, round(:chng/:ela,2) lpval , round(:chng/:tran,2) lpval ,' Physical reads:' ch17n, round(:phyr/:ela,2) lpval , round(:phyr/:tran,2) lpval ,' Physical writes:' ch17n, round(:phyw/:ela,2) lpval , round(:phyw/:tran,2) lpval ,' User calls:' ch17n, round(:ucal/:ela,2) lpval , round(:ucal/:tran,2) lpval ,' Parses:' ch17n, round(:prse/:ela,2) lpval , round(:prse/:tran,2) lpval ,' Hard parses:' ch17n, round(:hprs/:ela,2) lpval , round(:hprs/:tran,2) lpval ,'W/A MB processed:' ch17n, round(:srtr/&btomb/:ela,2) lpval , round(:srtr/&btomb/:tran,2) lpval ,' Logons:' ch17n, round(:logc/:ela,2) lpval , round(:logc/:tran,2) lpval ,' Executes:' ch17n, round(:exe/:ela,2) lpval , round(:exe/:tran,2) lpval ,' Rollbacks:' ch17n, round(:urol/:ela,2) lpval , round(:urol/:tran,2) lpval ,' Transactions:' ch17n, round(:tran/:ela,2) lpval from sys.dual; -- -- Instance Efficiency Percentages select 'Instance Efficiency Indicators' ch30n ,'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' ch30n ,' Buffer Nowait %:' ch28n , round(100*(1-:bfwt/:gets),2) pctval ,' Redo NoWait %:' ch20 , decode(:rent,0,to_number(null), round(100*(1-:rlsr/:rent),2)) pctval ,' Buffer Hit %:' ch28n , round(100*(1 - :phyrc/:gets),2) pctval ,' Optimal W/A Exec %:' ch20 , decode((:srtm+:srtd),0,to_number(null), round(100*:srtm/(:srtd+:srtm),2)) pctval ,' Library Hit %:' ch28n , round(100*:lhtr,2) pctval ,' Soft Parse %:' ch20 , round(100*(1-:hprs/:prse),2) pctval ,' Execute to Parse %:' ch28n , round(100*(1-:prse/:exe),2) pctval ,' Latch Hit %:' ch20 , round(100*(1-:lhr),2) pctval ,'Parse CPU to Parse Elapsd %:' ch28n , decode(:prsela, 0, to_number(null) , round(100*:prscpu/:prsela,2)) pctval ,' % Non-Parse CPU:' ch20 , decode(:tcpu, 0, to_number(null) , round(100*(1-(:prscpu/:tcpu)),2)) pctval from sys.dual; -- Setup vars in case snap < 5 taken define b_total_cursors = 0 define e_total_cursors = 0 define b_total_sql = 0 define e_total_sql = 0 define b_total_sql_mem = 0 define e_total_sql_mem = 0 column b_total_cursors new_value b_total_cursors noprint column e_total_cursors new_value e_total_cursors noprint column b_total_sql new_value b_total_sql noprint column e_total_sql new_value e_total_sql noprint column b_total_sql_mem new_value b_total_sql_mem noprint column e_total_sql_mem new_value e_total_sql_mem noprint select ' Shared Pool Statistics Begin End' ch60n , ' ------ ------' , ' Memory Usage %:' ch28n , 100*(1-:bfrm/:bspm) pctval , 100*(1-:efrm/:espm) pctval , ' % SQL with executions>1:' ch28n , 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:' ch28n , 100*(1-b.single_use_sql_mem/b.total_sql_mem) pctval , 100*(1-e.single_use_sql_mem/e.total_sql_mem) pctval , nvl(b.total_cursors, 0) b_total_cursors , nvl(e.total_cursors, 0) e_total_cursors , nvl(b.total_sql, 0) b_total_sql , nvl(e.total_sql, 0) e_total_sql , nvl(b.total_sql_mem, 0) b_total_sql_mem , nvl(e.total_sql_mem, 0) e_total_sql_mem 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; variable b_total_cursors number; variable e_total_cursors number; variable b_total_sql number; variable e_total_sql number; variable b_total_sql_mem number; variable e_total_sql_mem number; begin :b_total_cursors := to_number('&&b_total_cursors'); :e_total_cursors := to_number('&&e_total_cursors'); :b_total_sql := to_number('&&b_total_sql'); :e_total_sql := to_number('&&e_total_sql'); :b_total_sql_mem := to_number('&&b_total_sql_mem'); :e_total_sql_mem := to_number('&&e_total_sql_mem'); end; / -- -- set heading on; repfooter center - '-------------------------------------------------------------'; -- -- Top N Wait Events col idle noprint; col event format a41 heading 'Top &&top_n_events Timed Events|~~~~~~~~~~~~~~~~~~|Event' trunc; col waits format 999,999,990 heading 'Waits'; col time format 99,999,990 heading 'Time (s)'; col pctwtt format 999.9 heading '%Total|Call|Time'; col avwait format 99990 heading 'Avg|wait|(ms)'; select event , waits , time , avwait , pctwtt from (select event, waits, time, pctwtt, avwait from (select e.event event , e.total_waits - nvl(b.total_waits,0) waits , (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000 time , decode( (e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL) , ( (e.time_waited_micro - nvl(b.time_waited_micro,0)) / &&ustoms ) / (e.total_waits - nvl(b.total_waits,0)) ) avwait , decode(:tct_us, 0, 0, 100 * (e.time_waited_micro - nvl(b.time_waited_micro,0)) / (:tct_us) ) 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) union all select 'CPU time' event , to_number(null) waits , :tcpu/100 time , to_number(null) avwait , decode( :tct_us, 0, 0 , 100 * (:tcpu*10000) / :tct_us ) pctwait from dual where :tcpu > 0 ) order by time desc, waits desc ) where rownum <= &&top_n_events; -- -- set space 1 termout on newpage 1; whenever sqlerror exit; set heading off; repfooter off; -- -- Performance Summary continued set newpage 0; ttitle off; select 'Host CPU ' || '(CPUs: ' || :bncpu || ' Cores: ' || :bncore || ' Sockets: ' || :bnsock || case when (:bncpu != :encpu) or (:bncore != :encore) or (:bnsock != :ensock) then 'End CPUs: ' || :bncpu || ' Cores: ' || :bncore || ' Sockets: ' || :bnsock end || ')' ch78n , '~~~~~~~~ Load Average' ch78n , ' Begin End User System Idle WIO WCPU' ch78n , ' ------- ------- ------- ------- ------- ------- --------' ch78n , ' ' ch19 , round(:blod,2) pctval , round(:elod,2) pctval , ' ' ch1 , 100*(:utic / :ttic) pctval , 100*(:stic / :ttic) pctval , 100*(:itic / :ttic) pctval , 100*(:iotic / :ttic) pctval , 100*(:oscpuw / :ttic) pctval from sys.dual where :ttic > 0; set newpage 1; select 'Note: There is a ' || round(:pct_sp_oss_cpu_diff) || '% discrepancy between the OS Stat total CPU time and' ch78n , ' the total CPU time estimated by Statspack' ch78n , ' OS Stat CPU time: ' || round(:ttics) || '(s) (BUSY_TIME + IDLE_TIME)' ch78n , ' Statspack CPU time: ' || :spctim || '(s) (Elapsed time * num CPUs in end snap)' ch78n from sys.dual where &pct_cpu_diff_th < :pct_sp_oss_cpu_diff and :ttics > 0; select 'Instance CPU' ch40n , '~~~~~~~~~~~~ % Time (seconds)' ch70n , ' -------- --------------' ch70n , ' Host: Total time (s):' ch45n , to_char(:ttics , '99,999,999,999,990.0') ch27 , ' Host: Busy CPU time (s):' ch45n , to_char((:btic)/&&cstos , '99,999,999,999,990.0') ch27 , ' % of time Host is Busy:' ch45n , 100* (:btic)/(:ttic) num3_1 , ' Instance: Total CPU time (s):' ch45n , to_char( (:dbcpu + :bgcpu)/&&ustos, '99,999,999,999,990.0') ch27 , ' % of Busy CPU used for Instance:' ch45n , 100* ((:totcpu)/&ustos) / ((:btic)/&&cstos) num3_1 , ' Instance: Total Database time (s):' ch45n , to_char((:dbtim + :bgela)/&&ustos, '99,999,999,999,990.0') ch27 , ' %DB time waiting for CPU (Resource Mgr):' ch45n , decode(:dbtim, 0, to_number(null), 100*(round(:rwtic/:dbtim)) ) num3_1 from sys.dual where :dbtim > 0 and :btic/100 > 0; column kpersec format 999,999,999.9 select 'Virtual Memory Paging' ch78n , '~~~~~~~~~~~~~~~~~~~~~' ch78n , ' KB paged out per sec: ' ch43n, (:vmob/1024)/:ela kpersec , ' KB paged in per sec: ' ch43n, (:vmib/1024)/:ela kpersec from sys.dual where :vmob + :vmib > 0; col bpctval format 999999999.9 repfooter center - '-------------------------------------------------------------'; col memsz format 9,999,999.9 select 'Memory Statistics Begin End' ch79n , '~~~~~~~~~~~~~~~~~ ------------ ------------' ch79n , ' Host Mem (MB):' ch32n, :bpmem/&&btomb memsz, :epmem/&&btomb memsz , ' SGA use (MB):' ch32n, :bsgaalloc/&&btomb memsz, :esgaalloc/&&btomb memsz , ' PGA use (MB):' ch32n, :bpgaalloc/&&btomb memsz, :epgaalloc/&&btomb memsz , ' % Host Mem used for SGA+PGA:' ch32n, 100*(:bpgaalloc + :bsgaalloc)/:bpmem bpctval , 100*(:epgaalloc + :esgaalloc)/:epmem bpctval from sys.dual where :bpmem !=0; repfooter off -- -- set space 1 termout on newpage 0; whenever sqlerror exit; repfooter center - '-------------------------------------------------------------'; -- -- Time Model Statistics set newpage 1; set heading on; ttitle lef 'Time Model System Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> Ordered by % of DB time desc, Statistic name' - skip 2; column statnam format a35 trunc heading 'Statistic' column tdifs format 9,999,999,999,990.9 heading 'Time (s)' column pctdb format 99999.9 heading '% DB time' column order_col noprint select statnam , tdif/&ustos tdifs , decode(order_col, 0, 100*tdif/&DBtime , to_number(null) ) pctdb , order_col from (select sn.stat_name statnam , (e.value - b.value) tdif , decode( sn.stat_name , 'DB time', 1 , 'background cpu time', 2 , 'background elapsed time', 2 , 0 ) order_col from stats$sys_time_model e , stats$sys_time_model b , stats$time_model_statname sn 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.stat_id = e.stat_id and sn.stat_id = e.stat_id and e.value - b.value > 0 ) order by order_col, decode(pctdb, null, tdifs, pctdb) desc; set heading off; set newpage 0; -- -- Beginning of RAC specific Ratios column hd1 format a54 newline; column hd2 format a31 newline; column avg format 9990.0; column nl format a68 newline; column val format 9,999,999,999,990.99; column vali format 9990; ttitle lef 'RAC Statistics '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 2; select ' ' hd2 , 'Begin' , ' End' , ' ' hd2 , '-----' , '-----' , ' Number of Instances:' hd2 , ( select count(b.thread#) from stats$thread b where b.snap_id = :bid and b.dbid = :dbid and b.instance_number = :inst_num and b.status = 'OPEN' ) vali , ( select count(e.thread#) from stats$thread e where e.snap_id = :eid and e.dbid = :dbid and e.instance_number = :inst_num and e.status = 'OPEN' ) vali , ' ' nl , ' ' nl , 'Global Cache Load Profile' nl , '~~~~~~~~~~~~~~~~~~~~~~~~~ Per Second Per Transaction' , ' --------------- ---------------' , ' Global Cache blocks received:' hd2, round((:gccurv+:gccrrv)/:ela,2) val , round((:gccurv+:gccrrv)/:tran,2) val , ' Global Cache blocks served:' hd2, round((:gccusv+:gccrsv)/:ela,2) val , round((:gccusv+:gccrsv)/:tran,2) val , ' GCS/GES messages received:' hd2, round((:pmrv+:npmrv)/:ela,2) val , round((:pmrv+:npmrv)/:tran,2) val , ' GCS/GES messages sent:' hd2, round((:dpms+:dnpms)/:ela,2) val , round((:dpms+:dnpms)/:tran,2) val , ' DBWR Fusion writes:' hd2, round(:dbfr/:ela,2) val , round(:dbfr/:tran,2) val , 'Estd Interconnect traffic (KB):' hd2, round( ( ((:gccrrv+:gccurv +:gccrsv+:gccusv) * :bs) + ((:dpms+:dnpms+:pmrv+:npmrv) * 200) )/&&btokb/:ela,2 ) val , ' ' nl , ' ' nl , 'Global Cache Efficiency Percentages (Target local+remote 100%)' nl , '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' nl , 'Buffer access - local cache %:' hd2 , round(100*(1- (:phyrc +:gccrrv+:gccurv)/:gets), 2) pctval , 'Buffer access - remote cache %:' hd2 , round(100* (:gccurv+:gccrrv)/:gets, 2) pctval , 'Buffer access - disk %:' hd2 , round(100 * :phyrc/:gets, 2) pctval , ' ' nl , ' ' nl , 'Global Cache and Enqueue Services - Workload Characteristics' nl , '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' nl , ' Avg global enqueue get time (ms):' hd1 , decode(:glag+:glsg, 0, to_number(NULL) , (:glgt / (:glag+:glsg)) * 10) avg , ' ' nl , ' Avg global cache cr block receive time (ms):' hd1 , decode(:gccrrv, 0, to_number(NULL) , 10 * :gccrrt / :gccrrv) avg , ' Avg global cache current block receive time (ms):' hd1 , decode(:gccurv, 0, to_number(NULL) , 10 * :gccurt / :gccurv) avg , ' ' nl , ' Avg global cache cr block build time (ms):' hd1 , decode(:gccrsv, 0, to_number(NULL) , 10 * :gccrbt / :gccrsv) avg , ' Avg global cache cr block send time (ms):' hd1 , decode(:gccrsv, 0, to_number(NULL) , 10 * :gccrst / :gccrsv) avg , ' Avg global cache cr block flush time (ms):' hd1 , decode(:gccrfl, 0, to_number(NULL) , 10 * :gccrft / :gccrfl) avg , ' Global cache log flushes for cr blocks served %:' hd1 , 100*(decode(:gccrsv, 0, to_number(NULL), :gccrfl/:gccrsv)) avg , ' ' nl , ' Avg global cache current block pin time (ms):' hd1 , decode(:gccusv, 0, to_number(NULL) , 10 * :gccupt / :gccusv) avg , ' Avg global cache current block send time (ms):' hd1 , decode(:gccusv, 0, to_number(NULL) , 10 * :gccust / :gccusv) avg , ' Avg global cache current block flush time (ms):' hd1 , decode(:gccufl, 0, to_number(NULL) , 10 * :gccuft / :gccufl) avg , ' Global cache log flushes for current blocks served %:' hd1 , 100*(decode(:gccusv, 0, to_number(NULL), :gccufl/:gccusv)) avg , ' ' nl , ' ' nl , 'Global Cache and Enqueue Services - Messaging Statistics' nl , '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~' nl , ' Avg message sent queue time (ms):' hd1 , decode(:msgsq, 0, to_number(NULL), :msgsqt / :msgsq) avg , ' Avg message sent queue time on ksxp (ms):' hd1 , decode(:msgsqk, 0, to_number(NULL), :msgsqtk / :msgsqk) avg , ' Avg message received queue time (ms):' hd1 , decode(:msgrq, 0, to_number(NULL), :msgrqt / :msgrq) avg , ' Avg GCS message process time (ms):' hd1 , decode(:pmrv, 0, to_number(NULL), :pmpt / :pmrv) avg , ' Avg GES message process time (ms):' hd1 , decode(:npmrv, 0, to_number(NULL), :npmpt / :npmrv) avg , ' ' nl , ' % of direct sent messages:' hd1 , decode((:dmsd + :dmsi + :dmfc), 0 , to_number(NULL) , (100 * :dmsd) / (:dmsd + :dmsi + :dmfc)) pctval , ' % of indirect sent messages:' hd1 , decode((:dmsd + :dmsi + :dmfc), 0, to_number(NULL) , (100 * :dmsi) / (:dmsd + :dmsi + :dmfc)) pctval , ' % of flow controlled messages:' hd1 , decode((:dmsd+:dmsi+:dmfc), 0, to_number(NULL) , 100 * :dmfc / (:dmsd+:dmsi+:dmfc)) pctval from sys.dual where :para = 'YES'; -- -- End of RAC specific Ratios set heading on newpage 0; -- -- Wait Events / System Events -- -- Foreground ONLY wait events ttitle lef 'Foreground Wait Events '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> Only events with Total Wait Time (s) >= &&total_event_time_s_th are shown' - skip 1 - lef '-> ordered by Total 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 pctto format 999 heading '%Tim|out' just r col time format 9,999,990 heading 'Total Wait|Time (s)'; col avwt_fmt format &&avwt_fmt heading 'Avg|wait|(ms)'; col txwaits format 9,990.0 heading 'Waits|/txn'; col pcttct format 999.9 heading '%Total|Call|Time' select e.event , e.total_waits_fg - nvl(b.total_waits_fg,0) waits , decode( (e.total_waits_fg - nvl(b.total_waits_fg,0)), 0, to_number(null) , 100*(e.total_timeouts_fg - nvl(b.total_timeouts_fg,0)) /(e.total_waits_fg - nvl(b.total_waits_fg,0))) pctto , (e.time_waited_micro_fg - nvl(b.time_waited_micro_fg,0))/&&ustos time , decode ((e.total_waits_fg - nvl(b.total_waits_fg, 0)), 0, to_number(NULL), ((e.time_waited_micro_fg - nvl(b.time_waited_micro_fg,0))/&&ustoms) / (e.total_waits_fg - nvl(b.total_waits_fg,0)) ) avwt_fmt , (e.total_waits_fg - nvl(b.total_waits_fg,0))/:tran txwaits , decode( :tct_us, 0, 0 , decode(i.event, null, 100* (e.time_waited_micro_fg - nvl(b.time_waited_micro_fg,0)) / :tct_us, to_number(null)) ) pcttct , 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_fg > nvl(b.total_waits_fg,0) and ( :timstat in ('FALSE', 'INCONSISTENT') -- No valid timings - use # waits to filter or ( :timstat = 'TRUE' -- Valid timings - only show if time > threshold ms and ((e.time_waited_micro_fg - nvl(b.time_waited_micro_fg,0))/&&ustos) >= &&total_event_time_s_th ) ) and i.event(+) = e.event order by idle, time desc, waits desc; -- -- Background ONLY process wait events set newpage 1; ttitle lef 'Background Wait Events '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> Only events with Total Wait Time (s) >= &&total_event_time_s_th are shown' - skip 1 - lef '-> ordered by Total 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 , decode( (e.total_waits - nvl(b.total_waits,0)), 0, to_number(null) , 100*(e.total_timeouts - nvl(b.total_timeouts,0)) /(e.total_waits - nvl(b.total_waits,0))) pctto , (e.time_waited_micro - nvl(b.time_waited_micro,0))/&&ustos time , decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/&&ustoms) / (e.total_waits - nvl(b.total_waits,0)) ) avwt_fmt , (e.total_waits - nvl(b.total_waits,0))/:tran txwaits , decode(:tct_us, 0, 0 , decode(i.event, null, 100* (e.time_waited_micro - nvl(b.time_waited_micro,0)) / :tct_us, to_number(null)) ) pcttct , 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 ( :timstat in ('FALSE', 'INCONSISTENT') -- No valid timings - use # waits to filter or ( :timstat = 'TRUE' -- Valid timings - only show if time > 1ms and ((e.time_waited_micro - nvl(b.time_waited_micro,0))/&&ustos) >= &&total_event_time_s_th ) ) and i.event(+) = e.event order by idle, time desc, waits desc; clear break; set newpage 0; -- -- All (Forground and Background Waits) set newpage 1; ttitle lef 'Wait Events (fg and bg) '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> s - second, cs - centisecond, ms - millisecond, us - microsecond' - skip 1 - '-> %Timeouts: value of 0 indicates value was < .5%. Value of null is truly 0' - skip 1 - '-> Only events with Total Wait Time (s) >= &&total_event_time_s_th are shown' - skip 1 - lef '-> ordered by Total 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 pctto format 999 heading '%Tim|out' just r col time format 9,999,990 heading 'Total Wait|Time (s)'; col avwt_fmt format &&avwt_fmt heading 'Avg|wait|(ms)'; col txwaits format 9,990.0 heading 'Waits|/txn'; col pcttct format 999.9 heading '%Total|Call|Time' select e.event , e.total_waits - nvl(b.total_waits,0) waits , decode( (e.total_waits - nvl(b.total_waits,0)), 0, to_number(null) , 100*(e.total_timeouts - nvl(b.total_timeouts,0)) /(e.total_waits - nvl(b.total_waits,0))) pctto , (e.time_waited_micro - nvl(b.time_waited_micro,0))/&&ustos time , decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/&&ustoms) / (e.total_waits - nvl(b.total_waits,0)) ) avwt_fmt , (e.total_waits - nvl(b.total_waits,0))/:tran txwaits , decode ( :tct_us, 0, 0 , decode(i.event, null, 100* (e.time_waited_micro - nvl(b.time_waited_micro,0)) / :tct_us, to_number(null)) ) pcttct , 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 ( :timstat in ('FALSE', 'INCONSISTENT') -- No valid timings - use # waits to filter or ( :timstat = 'TRUE' -- Valid timings - only show if time > threshold ms and ((e.time_waited_micro - nvl(b.time_waited_micro,0))/&&ustos) >= &&total_event_time_s_th ) ) and i.event(+) = e.event order by idle, time desc, waits desc; set newpage 0; -- -- Event Histogram ttitle lef 'Wait Event Histogram '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> Total Waits - units: K is 1000, M is 1000000, G is 1000000000' - skip 1 - lef '-> % of Waits - column heading: <=1s is truly <1024ms, >1s is truly >=1024ms' - skip 1 - lef '-> % of Waits - value: .0 indicates value was <.05%, null is truly 0' - skip 1 - lef '-> Ordered by Event (idle events last)' - skip 2 - ' Total ----------------- % of Waits ------------------'; col idle noprint; col event format a26 heading 'Event' col total_waits format a5 heading 'Waits' col to1 format a5 heading ' <1ms' col to2 format a5 heading ' <2ms' col to4 format a5 heading ' <4ms' col to8 format a5 heading ' <8ms' col to16 format a5 heading '<16ms' col to32 format a5 heading '<32ms' col to1024 format a5 heading ' <=1s' col over format a5 heading ' >1s' with event_histogram as ( select /*+ inline ordered index(h) index(se) */ h.snap_id , se.event , sum(h.wait_count) total_waits , sum(case when (h.wait_time_milli = 1) then (nvl(h.wait_count,0)) else 0 end) to1 , sum(case when (h.wait_time_milli = 2) then (nvl(h.wait_count,0)) else 0 end) to2 , sum(case when (h.wait_time_milli = 4) then (nvl(h.wait_count,0)) else 0 end) to4 , sum(case when (h.wait_time_milli = 8) then (nvl(h.wait_count,0)) else 0 end) to8 , sum(case when (h.wait_time_milli = 16) then (nvl(h.wait_count,0)) else 0 end) to16 , sum(case when (h.wait_time_milli = 32) then (nvl(h.wait_count,0)) else 0 end) to32 , sum(case when (h.wait_time_milli between 64 and 1024) then (nvl(h.wait_count,0)) else 0 end) to1024 , sum(case when (1024 < h.wait_time_milli) then (nvl(h.wait_count,0)) else 0 end) over , decode(i.event, null, 0, 99) idle from stats$event_histogram h , stats$system_event se , stats$idle_event i where se.event_id = h.event_id and se.snap_id = h.snap_id and i.event(+) = se.event and se.instance_number = :inst_num and se.dbid = :dbid and h.instance_number = :inst_num and h.dbid = :dbid and '&event_histogram' = 'Y' group by h.snap_id , se.event , decode(i.event, null, 0, 99) ) select e.event , lpad(case when e.total_waits - nvl(b.total_waits,0) <= 9999 then to_char(e.total_waits - nvl(b.total_waits,0))||' ' when trunc((e.total_waits - nvl(b.total_waits,0))/1000) <= 9999 then to_char(trunc((e.total_waits - nvl(b.total_waits,0))/1000))||'K' when trunc((e.total_waits - nvl(b.total_waits,0))/1000000) <= 9999 then to_char(trunc((e.total_waits - nvl(b.total_waits,0))/1000000))||'M' when trunc((e.total_waits - nvl(b.total_waits,0))/1000000000) <= 9999 then to_char(trunc((e.total_waits - nvl(b.total_waits,0))/1000000000))||'G' when trunc((e.total_waits - nvl(b.total_waits,0))/1000000000000) <= 9999 then to_char(trunc((e.total_waits - nvl(b.total_waits,0))/1000000000000))||'T' else substr(to_char(trunc((e.total_waits - nvl(b.total_waits,0))/1000000000000000))||'P', 1, 5) end , 5, ' ') total_waits , substr(to_char(decode(e.to1-nvl(b.to1,0),0,to_number(NULL),(e.to1-nvl(b.to1,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) to1 , substr(to_char(decode(e.to2-nvl(b.to2,0),0,to_number(NULL),(e.to2-nvl(b.to2,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) to2 , substr(to_char(decode(e.to4-nvl(b.to4,0),0,to_number(NULL),(e.to4-nvl(b.to4,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) to4 , substr(to_char(decode(e.to8-nvl(b.to8,0),0,to_number(NULL),(e.to8-nvl(b.to8,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) to8 , substr(to_char(decode(e.to16-nvl(b.to16,0),0,to_number(NULL),(e.to16-nvl(b.to16,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) to16 , substr(to_char(decode(e.to32-nvl(b.to32,0),0,to_number(NULL),(e.to32-nvl(b.to32,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) to32 , substr(to_char(decode(e.to1024-nvl(b.to1024,0),0,to_number(NULL),(e.to1024-nvl(b.to1024,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) to1024 , substr(to_char(decode(e.over-nvl(b.over,0),0,to_number(NULL),(e.over-nvl(b.over,0))*100/(e.total_waits-nvl(b.total_waits,0))),'999.9MI'),1,5) over from ( select * from event_histogram where snap_id = :bid) b , ( select * from event_histogram where snap_id = :eid) e where b.event(+) = e.event and (e.total_waits - nvl(b.total_waits,0)) > 0 order by e.idle, e.event; -- -- SQL Reporting -- Get the captured vs total workloads ratios set newpage none; set heading off; set termout off; ttitle off; repfooter off; col bufcappct new_value bufcappct noprint col getsa new_value getsa noprint col phycappct new_value phycappct noprint col phyra new_value phyra noprint col execappct new_value execappct noprint col exea new_value exea noprint col prscappct new_value prscappct noprint col prsea new_value prsea noprint col cpucappct new_value cpucappct noprint col elacappct new_value elacappct noprint col dbcpua new_value dbcpua noprint col dbcpu_s new_value dbcpu_s noprint col dbtima new_value dbtima noprint col dbtim_s new_value dbtim_s noprint select decode( :slr, 0, to_number(null) , 100*sum( case e.command_type when 47 then 0 else e.buffer_gets - nvl(b.buffer_gets,0) end)/:slr ) bufcappct , :slr getsa , decode( :phyr, 0, to_number(null) , 100*sum( case e.command_type when 47 then 0 else e.disk_reads - nvl(b.disk_reads,0) end)/:phyr ) phycappct , :phyr phyra , decode( :exe, 0, to_number(null) , 100*sum(e.executions - nvl(b.executions,0))/:exe ) execappct , :exe exea , decode( :prse, 0, to_number(null) , 100*sum(e.parse_calls - nvl(b.parse_calls,0))/:prse ) prscappct , :prse prsea , decode( :dbcpu, 0, to_number(null) , 100*sum(e.cpu_time - nvl(b.cpu_time,0))/:dbcpu ) cpucappct , decode(:dbcpu, 0, to_number(null), :dbcpu) dbcpua , decode(:dbcpu, 0, to_number(null), :dbcpu/1000000) dbcpu_s , decode( :dbtim, 0, to_number(null) , 100*sum(e.elapsed_time - nvl(b.elapsed_time,0))/:dbtim ) elacappct , decode(:dbtim, 0, to_number(null), :dbtim) dbtima , decode(:dbtim, 0, to_number(null), :dbtim/1000000) dbtim_s from stats$sql_summary e , stats$sql_summary b 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 e.executions > nvl(b.executions,0) and b.old_hash_value(+) = e.old_hash_value and b.address(+) = e.address and b.text_subset(+) = e.text_subset; set termout on; -- -- Create GTT -- -- Cases handled: -- 1. If SQL only appears in the first snapshot, do not add value -- from this snapshot -- 2. If SQL appears in the first snapshot, and has more than one -- occurrence, then do not addthe raw value from first snapshot -- (this is really the same condition as 1.) -- 3. If SQL first appears in a snapshot in the middle of the range, and -- it has more than one occurrence, add the raw value from the -- first snapshot, in addition to the deltas -- 4. If SQL only appears once after the first snapshot, then -- include the raw value. -- 5. All other cases, do the deltas -- -- Cases indeterminate: -- 1. If the SQL appears in a snapshot, ages out, then re-appears -- it is not possible to tell whether -- a) the SQL was aged out -- b) the snapshots between were purged -- c) the snapshots between never occurred -- d) the snapshots were for a different instance (RAC) -- For a) you would want to add the raw value, whereas for the others -- you would want a diff. In some cases, adding the raw value wou;d -- result in an overestimate, and in other cases performing the diff -- rather than adding the raw value would be an underestimate. -- The code performs a diff. set feedback off; insert into stats$temp_sqlstats ( old_hash_value, text_subset, module , delta_buffer_gets, delta_executions, delta_cpu_time , delta_elapsed_time, avg_hard_parse_time, delta_disk_reads, delta_parse_calls , max_sharable_mem, last_sharable_mem , delta_version_count, max_version_count, last_version_count , delta_cluster_wait_time, delta_rows_processed ) select old_hash_value, text_subset, module , delta_buffer_gets, delta_executions, delta_cpu_time , delta_elapsed_time, avg_hard_parse_time, delta_disk_reads, delta_parse_calls , max_sharable_mem, last_sharable_mem , delta_version_count, max_version_count, last_version_count , delta_cluster_wait_time, delta_rows_processed from ( select -- sum deltas old_hash_value , text_subset , module , sum(case when snap_id = &&begin_snap and prev_snap_id = -1 then 0 else case when (address != prev_address) or (buffer_gets < prev_buffer_gets) then buffer_gets else buffer_gets - prev_buffer_gets end end) delta_buffer_gets , sum(case when snap_id = &&begin_snap and prev_snap_id = -1 then 0 else case when (address != prev_address) or (executions < prev_executions) then executions else executions - prev_executions end end) delta_executions , sum(case when snap_id = &&begin_snap and prev_snap_id = -1 then 0 else case when (address != prev_address) or (cpu_time < prev_cpu_time) then cpu_time else cpu_time - prev_cpu_time end end) delta_cpu_time , sum(case when snap_id = &&begin_snap and prev_snap_id = -1 then 0 else case when (address != prev_address) or (elapsed_time < prev_elapsed_time) then elapsed_time else elapsed_time - prev_elapsed_time end end) delta_elapsed_time , avg(case when snap_id = &&begin_snap and prev_snap_id = -1 then 0 else avg_hard_parse_time end) avg_hard_parse_time , sum(case when snap_id = &&begin_snap and prev_snap_id = -1 then 0 else case when (address != prev_address) or (disk_reads < prev_disk_reads) then disk_reads else disk_reads - prev_disk_reads end end) delta_disk_reads , sum(case when snap_id = &&begin_snap and prev_snap_id = -1 then 0 else case when (address != prev_address) or (parse_calls < prev_parse_calls) then parse_calls else parse_calls - prev_parse_calls end end) delta_parse_calls , max(sharable_mem) max_sharable_mem , sum(case when snap_id = &&end_snap then last_sharable_mem else 0 end) last_sharable_mem , sum(case when snap_id = &&begin_snap and prev_snap_id = -1 then 0 else case when (address != prev_address) or (version_count < prev_version_count) then version_count else version_count - prev_version_count end end) delta_version_count , max(version_count) max_version_count , sum(case when snap_id = &&end_snap then last_version_count else 0 end) last_version_count , sum(case when snap_id = &&begin_snap and prev_snap_id = -1 then 0 else case when (address != prev_address) or (cluster_wait_time < prev_cluster_wait_time) then cluster_wait_time else cluster_wait_time - prev_cluster_wait_time end end) delta_cluster_wait_time , sum(case when snap_id = &&begin_snap and prev_snap_id = -1 then 0 else case when (address != prev_address) or (rows_processed < prev_rows_processed) then rows_processed else rows_processed - prev_rows_processed end end) delta_rows_processed from (select /*+ first_rows */ -- windowing function snap_id , old_hash_value , text_subset , module , (lag(snap_id, 1, -1) over (partition by old_hash_value , dbid , instance_number order by snap_id)) prev_snap_id , (lead(snap_id, 1, -1) over (partition by old_hash_value , dbid , instance_number order by snap_id)) next_snap_id , address ,(lag(address, 1, hextoraw(0)) over (partition by old_hash_value , dbid , instance_number order by snap_id)) prev_address , buffer_gets ,(lag(buffer_gets, 1, 0) over (partition by old_hash_value , dbid , instance_number order by snap_id)) prev_buffer_gets , cpu_time ,(lag(cpu_time, 1, 0) over (partition by old_hash_value , dbid , instance_number order by snap_id)) prev_cpu_time , executions ,(lag(executions, 1, 0) over (partition by old_hash_value , dbid , instance_number order by snap_id)) prev_executions , elapsed_time ,(lag(elapsed_time, 1, 0) over (partition by old_hash_value , dbid , instance_number order by snap_id)) prev_elapsed_time , avg_hard_parse_time , disk_reads ,(lag(disk_reads, 1, 0) over (partition by old_hash_value , dbid , instance_number order by snap_id)) prev_disk_reads , parse_calls ,(lag(parse_calls, 1, 0) over (partition by old_hash_value , dbid , instance_number order by snap_id)) prev_parse_calls , sharable_mem ,(last_value(sharable_mem) over (partition by old_hash_value , dbid , instance_number order by snap_id)) last_sharable_mem ,(lag(sharable_mem, 1, 0) over (partition by old_hash_value , dbid , instance_number order by snap_id)) prev_sharable_mem , version_count ,(lag(version_count, 1, 0) over (partition by old_hash_value , dbid , instance_number order by snap_id)) prev_version_count ,(last_value(version_count) over (partition by old_hash_value , dbid , instance_number order by snap_id)) last_version_count , cluster_wait_time ,(lag(cluster_wait_time, 1, 0) over (partition by old_hash_value , dbid , instance_number order by snap_id)) prev_cluster_wait_time , rows_processed ,(lag(rows_processed, 1, 0) over (partition by old_hash_value , dbid , instance_number order by snap_id)) prev_rows_processed from stats$sql_summary s where s.snap_id between &&begin_snap and &&end_snap and s.dbid = &&dbid and s.instance_number = &&inst_num ) group by old_hash_value , text_subset , module ) where delta_buffer_gets > 0 or delta_executions > 0 or delta_cpu_time > 0 or delta_disk_reads > 0 or delta_parse_calls > 0 or max_sharable_mem >= &&esmt or max_version_count >= &&evc or delta_cluster_wait_time > 0; commit; set heading on; set newpage 0; repfooter center - '-------------------------------------------------------------'; col Execs format 999,999,990 heading 'Executes'; col GPX format 999,999,990.0 heading 'Gets|per Exec' just c; col RPX format 999,999,990.0 heading 'Reads|per Exec' just c; col RWPX format 9,999,990.0 heading 'Rows|per Exec' just c; 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 CPU ttitle lef 'SQL ordered by CPU '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> Total DB CPU (s): ' format 99,999,999,999 dbcpu_s - skip 1 - '-> Captured SQL accounts for ' format 990.9 cpucappct '% of Total DB CPU' - skip 1 - '-> SQL reported below exceeded &top_pct_sql.% of Total DB CPU' - skip 2; -- Bug 1313544 requires this rather bizarre SQL statement set underline off; col aa format a80 heading - ' CPU CPU per Elapsd Old| Time (s) Executions Exec (s) %Total Time (s) Buffer Gets Hash Value |---------- ------------ ---------- ------ ---------- --------------- ----------' column hv noprint; break on hv skip 1; select /*+ orderd use_nl (topn st) */ decode(st.piece ,0 ,lpad(to_char(delta_cpu_time/&&ustos,'99990.00') , 10) || ' ' || lpad(to_char(delta_executions,'999,999,999') , 12) || ' ' || lpad(decode(delta_executions ,0 , ' ' ,to_char(delta_cpu_time/&&ustos/delta_executions,'999990.00')) , 10) || ' ' || lpad(decode( :dbcpu, 0, ' ' , to_char(100*delta_cpu_time/:dbcpu,'999.0')) , 6) || ' ' || lpad(to_char(delta_elapsed_time/&&ustos,'99990.00') , 10) || ' ' || lpad(to_char(delta_buffer_gets,'99,999,999,999') , 15) || ' ' || lpad(topn.old_hash_value, 10) || ' ' || decode(topn.module, null, st.sql_text ,rpad('Module: '||topn.module,80)||st.sql_text) , st.sql_text) aa , topn.old_hash_value hv from ( select * from ( select * from perfstat.stats$temp_sqlstats where decode(:dbcpu, 0, 2, null, 2, 100*delta_cpu_time/:dbcpu) > decode(:dbcpu, 0, 1, null, 2, &&top_pct_sql) order by delta_cpu_time desc ) where rownum <= &&top_n_sql ) topn , stats$sqltext st where st.old_hash_value(+) = topn.old_hash_value and st.text_subset(+) = topn.text_subset and st.piece < &&num_rows_per_hash order by topn.delta_cpu_time desc, topn.old_hash_value, st.piece; -- -- SQL statements ordered by Elapsed ttitle lef 'SQL ordered by Elapsed time for ' - 'DB: ' db_name ' Instance: ' inst_name ' '- 'Snaps: ' format 999999 begin_snap ' -' format 999999 end_snap - skip 1 - '-> Total DB Time (s): ' format 99,999,999,999 dbtim_s - skip 1 - '-> Captured SQL accounts for ' format 990.9 elacappct '% of Total DB Time' - skip 1 - '-> SQL reported below exceeded &top_pct_sql.% of Total DB Time' - skip 2; col aa format a80 heading - ' Elapsed Elap per CPU Old| Time (s) Executions Exec (s) %Total Time (s) Physical Reads Hash Value |---------- ------------ ---------- ------ ---------- --------------- ----------' col hv noprint; break on hv skip 1; select /*+ orderd use_nl (topn st) */ decode(st.piece ,0 ,lpad(to_char(delta_elapsed_time/&&ustos,'999990.00') , 10) || ' ' || lpad(to_char(delta_executions,'999,999,999') ,12) || ' ' || lpad(decode(delta_executions ,0 , ' ' ,to_char(delta_elapsed_time/&&ustos/delta_executions,'999990.00')) ,10) || ' ' || lpad(decode(:dbtim, 0, ' ' ,to_char(100*delta_elapsed_time/:dbtim, '990.0') ) , 6) ||' '|| lpad(to_char(delta_cpu_time/&&ustos,'99990.00') , 10) || ' ' || lpad(to_char(delta_disk_reads,'99,999,999,999') ,15) || ' ' || lpad(topn.old_hash_value,10) || ' ' || decode(topn.module, null, st.sql_text ,rpad('Module: '||topn.module,80)||st.sql_text) , st.sql_text) aa , topn.old_hash_value hv from ( select * from ( select * from perfstat.stats$temp_sqlstats where decode(:dbtim, 0, 2 , 100*delta_elapsed_time/:dbtim) > decode(:dbtim, 0, 1, &&top_pct_sql) order by delta_elapsed_time desc ) where rownum <= &&top_n_sql ) topn , stats$sqltext st where st.old_hash_value(+) = topn.old_hash_value and st.text_subset(+) = topn.text_subset and st.piece < &&num_rows_per_hash order by topn.delta_elapsed_time desc, topn.old_hash_value, st.piece; -- -- SQL statements ordered by Gets ttitle lef 'SQL ordered by Gets '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> End Buffer Gets Threshold: ' ebgt ' Total Buffer Gets: ' format 99,999,999,999 getsa - skip 1 - '-> Captured SQL accounts for ' format 990.9 bufcappct '% of Total Buffer Gets' - skip 1 - '-> SQL reported below exceeded &top_pct_sql.% of Total Buffer Gets' - skip 2; -- Bug 1313544 requires this rather bizarre SQL statement set underline off; col aa format a80 heading - ' CPU Elapsd Old| Buffer Gets Executions Gets per Exec %Total Time (s) Time (s) Hash Value |--------------- ------------ -------------- ------ -------- --------- ----------' column hv noprint; break on hv skip 1; select /*+ orderd use_nl (topn st) */ decode(st.piece , 0 , lpad(to_char(delta_buffer_gets,'99,999,999,999') ,15) || ' ' || lpad(to_char(delta_executions,'999,999,999') ,12) || ' ' || lpad(decode(delta_executions ,0 , ' ' ,to_char(delta_buffer_gets/delta_executions,'999,999,990.0')) ,14) || ' ' || lpad(to_char(100*delta_buffer_gets/:gets,'999.0') , 6) || ' ' || lpad(to_char(delta_cpu_time/&&ustos,'9990.00') , 8) || ' ' || lpad(to_char(delta_elapsed_time/&&ustos,'99990.00') , 9) || ' ' || lpad(topn.old_hash_value,10) || '' || decode(topn.module, null, st.sql_text ,rpad('Module: '||topn.module,80)||st.sql_text) , st.sql_text) aa , topn.old_hash_value hv from ( select * from ( select * from perfstat.stats$temp_sqlstats where 100*delta_buffer_gets/:slr > &&top_pct_sql order by delta_buffer_gets desc ) where rownum <= &&top_n_sql ) topn , stats$sqltext st where st.old_hash_value(+) = topn.old_hash_value and st.text_subset(+) = topn.text_subset and st.piece < &&num_rows_per_hash order by topn.delta_buffer_gets desc, topn.old_hash_value, st.piece; -- -- SQL statements ordered by physical reads ttitle lef 'SQL ordered by Reads '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> End Disk Reads Threshold: ' edrt ' Total Disk Reads: ' format 99,999,999,999 phyra - skip 1 - '-> Captured SQL accounts for ' format 990.9 phycappct '% of Total Disk Reads' - skip 1 - '-> SQL reported below exceeded &top_pct_sql.% of Total Disk Reads' - skip 2; col aa format a80 heading - ' CPU Elapsd| Physical Rds Executions Rds per Exec %Total Time (s) Time (s) Hash Value |--------------- ------------ -------------- ------ -------- --------- ----------' col hv noprint; break on hv skip 1; select /*+ orderd use_nl (topn st) */ decode(st.piece ,0 ,lpad(to_char(delta_disk_reads,'99,999,999,999') ,15) || ' ' || lpad(to_char(delta_executions,'999,999,999') ,12) || ' ' || lpad(decode(delta_executions , 0, ' ' ,to_char(delta_disk_reads/delta_executions,'999,999,990.0')) ,14) || ' ' || lpad(to_char(100*delta_disk_reads/:phyr,'999.0') , 6) || ' ' || lpad(to_char(delta_cpu_time/&&ustos,'9990.00') , 8) || ' ' || lpad(to_char(delta_elapsed_time/&&ustos,'99990.00') , 9) || ' ' || lpad(topn.old_hash_value,10) || '' || decode(topn.module, null, st.sql_text ,rpad('Module: '||topn.module,80)||st.sql_text) ,st.sql_text) aa , topn.old_hash_value hv from ( select * from ( select * from perfstat.stats$temp_sqlstats where :phyr > 0 and 100*delta_disk_reads/:phyr > &&top_pct_sql order by delta_disk_reads desc ) where rownum <= &&top_n_sql ) topn , stats$sqltext st where st.old_hash_value(+) = topn.old_hash_value and st.text_subset(+) = topn.text_subset and st.piece < &&num_rows_per_hash order by topn.delta_disk_reads desc, topn.old_hash_value, st.piece; -- -- SQL statements ordered by executions ttitle lef 'SQL ordered by Executions '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> End Executions Threshold: ' eet ' Total Executions: ' format 99,999,999,999 exea- skip 1 - '-> Captured SQL accounts for ' format 990.9 execappct '% of Total Executions' - skip 1 - '-> SQL reported below exceeded &top_pct_sql.% of Total Executions' - skip 2; col aa format a80 heading - ' CPU per Elap per Old| Executions Rows Processed Rows per Exec Exec (s) Exec (s) Hash Value |------------ --------------- ---------------- ----------- ---------- ----------' col hv noprint; break on hv skip 1; select /*+ orderd use_nl (topn st) */ decode( st.piece , 0 , lpad(to_char(delta_executions, '999,999,999') ,12)||' '|| lpad(to_char(delta_rows_processed, '99,999,999,999') ,15)||' '|| lpad(decode(delta_executions ,0, ' ' ,to_char(delta_rows_processed/delta_executions, '9,999,999,990.0')) ,16) ||' '|| lpad(decode(delta_executions , 0, ' ' , to_char(delta_cpu_time/delta_executions/&&ustos, '999990.00')) ,10) ||' '|| lpad(decode(delta_executions , 0, ' ' , to_char(delta_elapsed_time/delta_executions/&&ustos, '9999990.00')) ,11) ||' '|| lpad(topn.old_hash_value,11) || '' || decode(topn.module, null, st.sql_text ,rpad('Module: '||topn.module,80)||st.sql_text) , st.sql_text) aa , topn.old_hash_value hv from ( select * from ( select * from perfstat.stats$temp_sqlstats where 100*delta_executions/:exe > &&top_pct_sql order by delta_executions desc ) where rownum <= &&top_n_sql ) topn , stats$sqltext st where st.old_hash_value(+) = topn.old_hash_value and st.text_subset(+) = topn.text_subset and st.piece < &&num_rows_per_hash order by topn.delta_executions desc, topn.old_hash_value, st.piece; -- -- SQL statements ordered by Parse Calls ttitle lef 'SQL ordered by Parse Calls '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> End Parse Calls Threshold: ' epc ' Total Parse Calls: ' format 99,999,999,999 prsea - skip 1 - '-> Captured SQL accounts for ' format 990.9 prscappct '% of Total Parse Calls' - skip 1 - '-> SQL reported below exceeded &top_pct_sql.% of Total Parse Calls' - skip 2; col aa format a80 heading - ' % Total Old| Parse Calls Executions Parses Hash Value |------------ ------------ -------- ----------' column hv noprint; break on hv skip 1; select /*+ orderd use_nl (topn st) */ decode( st.piece , 0 , lpad(to_char(delta_parse_calls,'999,999,999') ,12)||' '|| lpad(to_char(delta_executions,'999,999,999') ,12)||' '|| lpad(to_char(100*delta_parse_calls/:prse,'990.09') ,8)||' '|| lpad(topn.old_hash_value,10)||' '|| rpad(' ',34)|| decode(topn.module, null, st.sql_text ,rpad('Module: '||topn.module,80)||st.sql_text) , st.sql_text ) aa , topn.old_hash_value hv from ( select * from ( select * from perfstat.stats$temp_sqlstats where 100*delta_parse_calls/:prse > &&top_pct_sql order by delta_parse_calls desc ) where rownum <= &&top_n_sql ) topn , stats$sqltext st where st.old_hash_value(+) = topn.old_hash_value and st.text_subset(+) = topn.text_subset and st.piece < &&num_rows_per_hash order by topn.delta_parse_calls desc, topn.old_hash_value, st.piece; -- -- SQL statements ordered by Sharable Memory ttitle lef 'SQL ordered by Sharable Memory '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> End Sharable Memory Threshold(KB): ' format 99999999 esmtk - skip 2; col aa format a80 heading - ' Max End| Sharable Sharable Old|Memory (KB) Memory (KB) Parse Calls Executions % Total Hash Value|----------- ----------- ------------ ------------ ------- ----------' select /*+ orderd use_nl (topn st) */ decode( st.piece , 0 , lpad(to_char(max_sharable_mem/&&btokb, '99,999,990') ,11)||' '|| lpad(to_char(last_sharable_mem/&&btokb, '99,999,990') ,11)||' '|| lpad(to_char(delta_parse_calls, '999,999,999') ,12)||' '|| lpad(to_char(delta_executions, '999,999,999') ,12)||' '|| lpad(to_char(100*max_sharable_mem/:espm, '990.0') , 7) ||' '|| lpad(topn.old_hash_value,10) || '' || rpad(' ',12)|| decode(topn.module, null, st.sql_text ,rpad('Module: '||topn.module,80)||st.sql_text) , st.sql_text ) aa , topn.old_hash_value hv from ( select * from ( select * from perfstat.stats$temp_sqlstats where max_sharable_mem > :esmt order by max_sharable_mem desc ) where rownum <= &&top_n_sql ) topn , stats$sqltext st where st.old_hash_value(+) = topn.old_hash_value and st.text_subset(+) = topn.text_subset and st.piece < &&num_rows_per_hash order by topn.max_sharable_mem desc, topn.old_hash_value, st.piece; -- -- SQL statements ordered by Version Count ttitle lef 'SQL ordered by Version Count '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> End Version Count Threshold: ' format 99999999 evc - skip 2; col aa format a80 heading - ' Max End Delta| Version Version Version Old| Count Count Count Executions Hash Value |-------- -------- -------- ------------ ----------' select /*+ orderd use_nl (topn st) */ decode( st.piece , 0 , lpad(to_char(max_version_count, '999,999') ,8)||' '|| lpad(to_char(last_version_count, '999,999') ,8)||' '|| lpad(to_char(delta_version_count,'999,999') ,8)||' '|| lpad(to_char(delta_executions, '999,999,999') ,12)||' '|| lpad(topn.old_hash_value,10) || '' || rpad(' ',30)|| decode(topn.module, null, st.sql_text ,rpad('Module: '||topn.module,80)||st.sql_text) , st.sql_text) aa , topn.old_hash_value hv from ( select * from ( select * from perfstat.stats$temp_sqlstats where max_version_count > :evc order by max_version_count desc ) where rownum <= &&top_n_sql ) topn , stats$sqltext st where st.old_hash_value(+) = topn.old_hash_value and st.text_subset(+) = topn.text_subset and st.piece < &&num_rows_per_hash order by topn.max_version_count desc, topn.old_hash_value, st.piece; -- -- SQL statements ordered by Cluster Wait Time -- ttitle lef 'SQL ordered by Cluster Wait Time '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 2; col aa format a80 heading - ' Cluster CWT % of Elapsd CPU Old|Wait Time (s) Elapsd Time Time (s) Time (s) Executions Hash Value |------------- ----------- ----------- ----------- -------------- ----------' select /*+ orderd use_nl (topn st) */ decode( st.piece , 0 , lpad(nvl(to_char(delta_cluster_wait_time/&&ustos, '9,999,990.00') , ' '),13) || ' ' || lpad(nvl(decode(delta_elapsed_time , 0, ' ' , to_char(100*delta_cluster_wait_time/delta_elapsed_time, '990.0')) , ' '),11) || ' ' || lpad(nvl(to_char(delta_elapsed_time/&&ustos, '999,990.00') , ' '),11) || ' ' || lpad(nvl(to_char(delta_cpu_time/&&ustos, '999,990.00') , ' '),11) || ' ' || lpad(to_char(delta_executions, '9,999,999,999') ,14) || ' ' || lpad(topn.old_hash_value,10)||''|| rpad (' ',5)|| decode(topn.module, null, st.sql_text ,rpad('Module: '||topn.module,80)||st.sql_text) , st.sql_text) aa , topn.old_hash_value hv from ( select * from ( select * from perfstat.stats$temp_sqlstats where delta_cluster_wait_time > 0 and :para = 'YES' order by delta_cluster_wait_time desc ) where rownum <= &&top_n_sql ) topn , stats$sqltext st where st.old_hash_value (+) = topn.old_hash_value and st.text_subset (+) = topn.text_subset and st.piece < &&num_rows_per_hash order by topn.delta_cluster_wait_time desc, topn.old_hash_value, st.piece; set underline '-'; set termout off; set feedback off; whenever sqlerror continue; truncate table STATS$TEMP_SQLSTATS; whenever sqlerror exit sql.sqlcode; set termout on; -- -- Instance Activity Statistics ttitle lef 'Instance Activity Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 2; column st format a33 heading 'Statistic' trunc; column dif format 9,999,999,999,990 heading 'Total'; column ps format 999,999,990.9 heading 'per Second'; column pt format 9,999,990.9 heading 'per Trans'; select b.name st , e.value - b.value dif , round((e.value - b.value)/:ela,2) ps , round((e.value - b.value)/:tran,2) pt 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.name not in ( 'logons current' , 'opened cursors current' , 'workarea memory allocated' , 'session cursor cache count' ) and e.value >= b.value and e.value > 0 order by st; -- -- Instance Activity Statistics - absolute values set newpage 1; ttitle lef 'Instance Activity Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> Statistics with absolute values (should not be diffed)' - skip 2; column begin_value format 99,999,999,999 heading 'Begin Value' column end_value format 99,999,999,999 heading 'End Value' select b.name st , b.value begin_value , e.value end_value 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.name in ( 'logons current' , 'opened cursors current' , 'workarea memory allocated' , 'session cursor cache count' ) and ( b.value > 0 or e.value > 0 ); set newpage 0; -- -- Non-sysstat Instance Activity Statistics set newpage 1; ttitle lef 'Instance Activity Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> Statistics identified by ''(derived)'' come from sources other than SYSSTAT' - skip 2; column ph format 9,999.99 heading 'per Hour'; select 'log switches (derived)' st , e.sequence# - b.sequence# dif , (e.sequence# - b.sequence#)/(:ela/3600) ph from stats$thread e , stats$thread b 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.thread# = e.thread# and b.thread_instance_number = e.thread_instance_number and e.thread_instance_number = :inst_num; set newpage 0; -- -- OS Stat set newpage 1; ttitle lef 'OS Statistics '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> ordered by statistic type (CPU use, Virtual Memory, Hardware Config), Name' - skip 2; column osn format a25 heading 'Statistic' trunc; column dif format 9,999,999,999,999,990 heading 'Total'; column ps format 9,999,999,999,990 heading 'per Second'; column styp noprint select osn.stat_name osn , decode( osn.cumulative, 'NO', e.value, e.value - b.value) dif , ( to_number(decode(sign(instrb(osn.stat_name, 'TIME')), 1, 1, 0)) + to_number(decode(sign(instrb(osn.stat_name, 'LOAD')), 1, 2, 0)) + to_number(decode(sign(instrb(osn.stat_name, 'CPU_WAIT')), 1, 3, 0)) + to_number(decode(sign(instrb(osn.stat_name, 'VM_')), 1, 4, 0)) + to_number(decode(sign(instrb(osn.stat_name, 'PHYSICAL_MEMORY')), 1, 5, 0)) + to_number(decode(sign(instrb(osn.stat_name, 'NUM_CPU')), 1, 6, 0)) + to_number(decode(sign(instrb(osn.stat_name, 'TCP')), 1, 7, 0)) + to_number(decode(sign(instrb(osn.stat_name, 'GLOBAL')), 1, 7, 0)) ) styp from stats$osstat b , stats$osstat e , stats$osstatname osn 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.osstat_id = e.osstat_id and osn.osstat_id = e.osstat_id and (osn.stat_name not like 'AVG_%' and osn.stat_name not like '%LOAD%') and e.value >= b.value and e.value > 0 order by styp, osn; set newpage 1; ttitle lef 'OS Statistics - detail '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 2; column snap_id format 99999 heading 'Snap|Id' just r column snap_time format a15 heading 'Snapshot|Day Time' column load format 999.9 heading 'Load' column pct_busy format 999.9 heading '%Busy' column pct_user format 999.9 heading '%User' column pct_sys format 999.9 heading '%System' column pct_iowt format 990.9 heading '%WIO' column pct_cpuwt format 990.9 heading '%WCPU' select snap_id , snap_time , load , 100*busy_time/(busy_time+idle_time) pct_busy , 100*user_time/(busy_time+idle_time) pct_user , 100*sys_time /(busy_time+idle_time) pct_sys , 100*iowt_time/(busy_time+idle_time) pct_iowt , 100*cpuwt_time/(busy_time+idle_time) pct_cpuwt from ( select snap_id , snap_time , load , decode( prev_busyt, -1, to_number(null), busyt-prev_busyt ) busy_time , decode( prev_idlet, -1, to_number(null), idlet-prev_idlet ) idle_time , decode( prev_usert, -1, to_number(null), usert-prev_usert ) user_time , decode( prev_syst, -1, to_number(null), syst -prev_syst ) sys_time , decode( prev_iowt, -1, to_number(null), iowt -prev_iowt ) iowt_time , decode( prev_cpuwt, -1, to_number(null), cpuwt-prev_cpuwt ) cpuwt_time from ( select -- lag snap_id , load , snap_time , busyt , ( lag (busyt, 1, -1) over (order by snap_id asc) ) prev_busyt , idlet , ( lag (idlet, 1, -1) over (order by snap_id asc) ) prev_idlet , usert , ( lag (usert, 1, -1) over (order by snap_id asc) ) prev_usert , syst , ( lag (syst, 1, -1) over (order by snap_id asc) ) prev_syst , iowt , ( lag (iowt, 1, -1) over (order by snap_id asc) ) prev_iowt , cpuwt , ( lag (cpuwt, 1, -1) over (order by snap_id asc) ) prev_cpuwt from ( (-- select the data select s.snap_id , sn.stat_name , to_char(ss.snap_time, 'Dy DD HH24:MI:SS') snap_time , s.value from stats$osstat s , stats$osstatname sn , stats$snapshot ss where s.snap_id between :bid and :eid and s.dbid = :dbid and s.instance_number = :inst_num and sn.osstat_id = s.osstat_id and sn.stat_name in ('LOAD','IDLE_TIME','BUSY_TIME','USER_TIME' ,'SYS_TIME','IOWAIT_TIME','CPU_WAIT_TIME') and ss.snap_id = s.snap_id and ss.dbid = s.dbid and ss.instance_number = ss.instance_number ) pivot ( sum(value) for stat_name in ( 'LOAD' load ,'IDLE_TIME' idlet ,'BUSY_TIME' busyt ,'USER_TIME' usert ,'SYS_TIME' syst ,'IOWAIT_TIME' iowt ,'CPU_WAIT_TIME' cpuwt ) ) ) ) ) order by snap_id; set newpage 0; -- -- Session Wait Events ttitle lef 'Session Wait Events '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 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; col event format a28 heading 'Event' trunc; select e.event , e.total_waits - nvl(b.total_waits,0) waits , e.total_timeouts - nvl(b.total_timeouts,0) timeouts , (e.time_waited_micro - nvl(b.time_waited_micro,0))/1000000 time , decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), ((e.time_waited_micro - nvl(b.time_waited_micro,0))/1000) / (e.total_waits - nvl(b.total_waits,0)) ) avwt_fmt , (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 = :bid and es.snap_id = :eid and bs.dbid = :dbid and es.dbid = :dbid and bs.instance_number = :inst_num and es.instance_number = :inst_num and bs.session_id = es.session_id and bs.serial# = es.serial# order by idle, time desc, waits desc; -- -- Session Time Model Statistics ttitle lef 'Session Time Model Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef 'Session Id: ' sess_id ' Serial#: ' serial - skip 1 - lef '-> Total Time in Database calls &DBtimes.s (or &DBtime.us)' - skip 1 - lef '-> Ordered by % of DB time desc, Statistic name' - skip 2; column statnam format a35 trunc heading 'Statistic' column tdifs format 9,999,999,999,990.9 heading 'Time (s)' column pctdb format 99999.9 heading '% of DB time' column order_col noprint select statnam , tdif/&ustos tdifs , decode(order_col, 0, 100*tdif/&DBtime , to_number(null) ) pctdb , order_col from (select sn.stat_name statnam , (e.value - b.value) tdif , decode( sn.stat_name , 'DB time', 1 , 'background cpu time', 2 , 'background elapsed time', 2 , 0 ) order_col from stats$sess_time_model e , stats$sess_time_model b , stats$time_model_statname sn 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.stat_id = e.stat_id and sn.stat_id = e.stat_id and e.value - b.value > 0 ) order by order_col, decode(pctdb, null, tdifs, pctdb) desc; -- -- Session Statistics ttitle lef 'Session Statistics '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef 'Session Id: ' sess_id ' Serial#: ' serial - skip 2; column st format a33 heading 'Statistic' trunc; column dif format 9,999,999,999,990 heading 'Total'; column ps format 999,999,990.9 heading 'per Second'; column pt format 9,999,990.9 heading 'per Trans'; 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))) pt 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 = :bid and es.snap_id = :eid and bs.dbid = :dbid and es.dbid = :dbid and bs.instance_number = :inst_num and es.instance_number = :inst_num and bs.session_id = es.session_id and bs.serial# = es.serial# order by st; -- -- IOStat summary statistics by Function ttitle lef 'IO Stat by Function - summary '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '->Data Volume values suffixed with M,G,T,P are in multiples of 1024, ' - skip 1 - lef ' other values suffixed with K,M,G,T,P are in multiples of 1000' - skip 1 - lef '->ordered by Data Volume (Read+Write) desc' - skip 2 - lef ' ---------- Read --------- --------- Write -------- --- Wait ----'; col func format a15 heading 'Function' trunc col data_read format a6 heading 'Data |Volume' just r col data_writ format a6 heading 'Data |Volume' just r col rrps format a8 heading 'Requests|/sec' just r col wrps format a8 heading 'Requests|/sec' just r col mbrps format a8 heading 'Data |Vol/sec' just r col mbwps format a8 heading 'Data |Vol/sec' just r col waits format a6 heading 'Count' just r col atpw format 990.0 heading 'Avg |Tm(ms)' select func , statspack.v1024(read_mb, 'M','M',0,1024) data_read , statspack.v1024(read_reqs_ps, 'B','B',1,1000) rrps , statspack.v1024(read_mb_ps, 'M','M',1,1024) mbrps , statspack.v1024(write_mb, 'M','M',0,1024) data_writ , statspack.v1024(write_reqs_ps,'B','B',1,1000) wrps , statspack.v1024(write_mb_ps, 'M','M',1,1024) mbwps , statspack.v1024(waits, 'B','B',0,1000) waits , avg_wait_time atpw from (select n.function_name func , (e.small_read_megabytes - b.small_read_megabytes) +(e.large_read_megabytes - b.large_read_megabytes) read_mb , ( (e.small_read_reqs - b.small_read_reqs) + (e.large_read_reqs - b.large_read_reqs) )/:ela read_reqs_ps , ( (e.small_read_megabytes - b.small_read_megabytes) + (e.large_read_megabytes - b.large_read_megabytes) )/:ela read_mb_ps , (e.small_write_megabytes - b.small_write_megabytes) +(e.large_write_megabytes - b.large_write_megabytes) write_mb , ( (e.small_write_reqs - b.small_write_reqs) + (e.large_write_reqs - b.large_write_reqs) )/:ela write_reqs_ps , ( (e.small_write_megabytes - b.small_write_megabytes) + (e.large_write_megabytes - b.large_write_megabytes) )/:ela write_mb_ps , e.number_of_waits - b.number_of_waits waits , (e.wait_time - b.wait_time) /( (e.small_read_reqs - b.small_read_reqs) + (e.large_read_reqs - b.large_read_reqs) + (e.small_write_reqs - b.small_write_reqs) + (e.large_write_reqs - b.large_write_reqs) ) /&&ustoms avg_wait_time from stats$iostat_function b , stats$iostat_function e , stats$iostat_function_name n 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.function_id = e.function_id and n.function_id = e.function_id and (e.small_read_reqs + e.large_read_reqs) - (b.small_read_reqs + b.large_read_reqs) > 0 ) order by read_mb + write_mb desc; set newpage 1 -- -- IOStat Detail statistics by Function ttitle lef 'IO Stat by Function - detail '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '->ordered by Data Volume (Read+Write) desc' - skip 2 - lef ' ----------- Read ---------- ----------- Write ---------'; set heading on col func format a18 heading 'Function' trunc col s_rr format a6 heading 'Small|Read|Reqs' just r col s_wr format a6 heading 'Small|Write|Reqs' just r col l_rr format a6 heading 'Large|Read|Reqs' just r col l_wr format a6 heading 'Large|Write|Reqs' just r col s_rm format a6 heading 'Small|Data|Read' just r col s_wm format a6 heading 'Small|Data|Writn' just r col l_rm format a6 heading 'Large|Data|Read' just r col l_wm format a6 heading 'Large|Data|Writn' just r select func , statspack.v1024(small_rr, 'B','B',0,1000) s_rr , statspack.v1024(large_rr, 'B','B',0,1000) l_rr , statspack.v1024(small_rm, 'M','M',0,1024) s_rm , statspack.v1024(large_rm, 'M','M',0,1024) l_rm , statspack.v1024(small_wr, 'B','B',0,1000) s_wr , statspack.v1024(large_wr, 'B','B',0,1000) l_wr , statspack.v1024(small_wm, 'M','M',0,1024) s_wm , statspack.v1024(large_wm, 'M','M',0,1024) l_wm from (select n.function_name func , e.small_read_reqs - b.small_read_reqs small_rr , e.large_read_reqs - b.large_read_reqs large_rr , e.small_read_megabytes - b.small_read_megabytes small_rm , e.large_read_megabytes - b.large_read_megabytes large_rm , e.small_write_reqs - b.small_write_reqs small_wr , e.large_write_reqs - b.large_write_reqs large_wr , e.small_write_megabytes - b.small_write_megabytes small_wm , e.large_write_megabytes - b.large_write_megabytes large_wm from stats$iostat_function b , stats$iostat_function e , stats$iostat_function_name n 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.function_id = e.function_id and n.function_id = e.function_id and (e.small_read_reqs + e.large_read_reqs) - (b.small_read_reqs + b.large_read_reqs) > 0 ) order by small_rm + small_wm + large_rm + large_wm desc; set newpage 0 -- -- Tablespace IO summary statistics ttitle lef 'Tablespace IO Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 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)' just c; 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)' just c; col rps format 99,999 heading 'Av|Reads/s' just c; col wps format 99,999 heading 'Av|Writes/s' just c; col bpr format 999.0 heading 'Av|Blks/Rd' just c; 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 all 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 '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '->Mx Rd Bkt: Max bucket time for single block read' - skip 1 - lef '->ordered by Tablespace, File' - skip 2; col tsname format a24 heading 'Tablespace' trunc; col filename format a52 heading 'Filename' trunc; col reads format 9,999,999,990 heading 'Reads' col atpwt format 990.0 heading 'Av|BufWt|(ms)' just c; col atpr format 90.0 heading 'Av|Rd|(ms)' just c; col mrt format 99 heading 'Mx|Rd|Bkt' just c; 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 , max_read.mrt , 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 , (select max(e.singleblkrdtim_milli) mrt , e.file# fn from stats$file_histogram b , stats$file_histogram 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.file#(+) = e.file# and b.singleblkrdtim_milli(+) = e.singleblkrdtim_milli and (e.singleblkrds - nvl(b.singleblkrds,0)) > 0 and upper('&&display_file_io') = 'Y' group by e.file# ) max_read 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 max_read.fn(+) = e.file# and ( (e.phyrds - nvl(b.phyrds,0) ) + (e.phywrts - nvl(b.phywrts,0) ) ) > 0 union all 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 , to_number(null) mrt , 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; -- -- File IO Histogram statistics ttitle lef 'File Read Histogram Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '->Number of single block reads in each time range' - skip 1 - lef '->Tempfiles are not included' - skip 1 - lef '->ordered by Tablespace, File' - skip 2; col tsname format a24 heading 'Tablespace' trunc; col filename format a52 heading 'Filename' trunc; col reads format 9,999,999,990 heading 'Reads' col to2 format 999,999,999 heading '0 - 2 ms' col to4 format 999,999,999 heading '2 - 4 ms' col to8 format 999,999,999 heading '4 - 8 ms ' col to16 format 999,999,999 heading '8 - 16 ms' col to32 format 999,999,999 heading '16 - 32 ms' col over32 format 999,999,999 heading '32+ ms' break on tsname skip 1; select fse.tsname , fse.filename , sum(case when (0 <= e.singleblkrdtim_milli) and (e.singleblkrdtim_milli <= 2) then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to2 , sum(case when ( e.singleblkrdtim_milli = 4) then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to4 , sum(case when ( e.singleblkrdtim_milli = 8) then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to8 , sum(case when ( e.singleblkrdtim_milli = 16) then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to16 , sum(case when ( e.singleblkrdtim_milli = 32) then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) to32 , sum(case when 32 < e.singleblkrdtim_milli then (e.singleblkrds - nvl(b.singleblkrds,0)) else 0 end) over32 from stats$file_histogram e , stats$file_histogram b , stats$filestatxs fse where b.snap_id(+) = :bid and e.snap_id = :eid and e.dbid = :dbid and b.dbid(+) = e.dbid and e.instance_number = :inst_num and b.instance_number(+) = e.instance_number and b.file#(+) = e.file# and b.singleblkrdtim_milli(+) = e.singleblkrdtim_milli and fse.snap_id = e.snap_id and fse.dbid = e.dbid and fse.instance_number = e.instance_number and fse.file# = e.file# and (e.singleblkrds - nvl(b.singleblkrds,0)) > 0 and '&&file_histogram' = 'Y' group by fse.tsname , fse.filename; -- -- Instance Recovery Stats ttitle lef 'Instance Recovery Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> B: Begin snapshot, E: End snapshot' - skip 2; column tm format 9999 heading 'Targt|MTTR|(s)' just c; column em format 9999 heading 'Estd|MTTR|(s)' just c; column beg format a1 heading ''; column rei format 999999999 heading 'Recovery|Estd IOs' just c; column arb format 99999999 heading 'Actual|Redo Blks' just c; column trb format 99999999 heading 'Target|Redo Blks' just c; column lfrb format 999999999 heading 'Log File|Size|Redo Blks' just c; column lctrb format 99999999 heading 'Log Ckpt|Timeout|Redo Blks' just c; column lcirb format 99999999999 heading 'Log Ckpt|Interval|Redo Blks' just c; column fsirb format 999999999 heading 'Fast|Start IO|Redo Blks'; column cbr format 9999999 heading 'Ckpt|Block|Writes'; column snid noprint; select 'B' beg , target_mttr tm , estimated_mttr em , recovery_estimated_ios rei , actual_redo_blks arb , target_redo_blks trb , log_file_size_redo_blks lfrb , log_chkpt_timeout_redo_blks lctrb , log_chkpt_interval_redo_blks lcirb , snap_id snid from stats$instance_recovery b where b.snap_id = :bid and b.dbid = :dbid and b.instance_number = :inst_num union all select 'E' beg , target_mttr tm , estimated_mttr em , recovery_estimated_ios rei , actual_redo_blks arb , target_redo_blks trb , log_file_size_redo_blks lfrb , log_chkpt_timeout_redo_blks lctrb , log_chkpt_interval_redo_blks lcirb , snap_id snid from stats$instance_recovery e where e.snap_id = :eid and e.dbid = :dbid and e.instance_number = :inst_num order by snid; set newpage 0 -- -- Memory Target Advice ttitle lef 'Memory Target Advice '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> Advice Reset: if this is null, the data shown has been diffed between' - skip 1 - lef ' the Begin and End snapshots. If this is ''Y'', the advisor has been' - skip 1 - lef ' reset during this interval due to memory resize operations, and' - skip 1 - lef ' the data shown is since the reset operation.' - skip 2; column ms format 999,999,999 heading 'Memory Size (M)' column msf format 999.9 heading 'Memory Size|Factor' column edbt format 999,999,999 heading 'Est.|DB time (s)' just c column av format a6 heading 'Advice|Reset' select e.memory_size ms , e.memory_size_factor msf , e.estd_db_time - nvl(b.estd_db_time, 0) edbt , decode(e.version, b.version, null, 'Y') av from stats$memory_target_advice b , stats$memory_target_advice 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.memory_size(+) = e.memory_size and b.version(+) = e.version order by e.memory_size; set newpage 1 ttitle lef 'Memory Dynamic Components '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> Op - memory resize Operation' - skip 1 - lef '-> Cache: D: Default, K: Keep, R: Recycle' - skip 1 - lef '-> Mode: DEF: DEFerred mode, IMM: IMMediate mode' - skip 2; column comp format a22 heading 'Cache' trunc column op_cnt format 99,999 heading 'Op|Count' just c column beg_size format 999,999 heading 'Begin Snap|Size (M)' column end_size format 999,999 heading 'End Snap|Size (M)' column uss format 999,999 heading 'User Specified|Size (M)' column lomod format a10 heading 'Last Op|Type/Mode' trunc column lotim format a15 heading 'Last Op Time' just c select replace(replace(replace(e.component, 'DEFAULT ', 'D:'), 'KEEP ', 'K:'), 'RECYCLE','R:') comp , b.current_size/&&btomb beg_size , decode( e.current_size, b.current_size, to_number(null) , e.current_size/&&btomb) end_size , e.oper_count - b.oper_count op_cnt , substr(e.last_oper_type,1,6) || decode(e.last_oper_type, 'STATIC', ' ', '/') || substr(e.last_oper_mode,1,3) lomod , to_char(e.last_oper_time, 'DD-Mon HH24:MI:SS') lotim from stats$memory_dynamic_comps b , stats$memory_dynamic_comps 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.component = e.component and ( e.current_size + b.current_size > 0 or e.oper_count - b.oper_count > 0 ) order by e.component; ttitle lef 'Memory Resize Operations '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> Status: ERR: ERRor, COM: COMplete, PEN: PENding' - skip 1 - lef '-> Delta - Target change in size (MB)' - skip 1 - lef '-> Num Ops - number of identical Operations initiated concurrently' - skip 2; column op_st_tm format a13 heading 'Start Time' column elapsd_s format 9999 heading 'Elap|(s)' column comp format a14 trunc heading 'Cache' column ini_sz format 999,999 heading 'Init |Size(M)' column op_tm format a9 trunc heading 'Delta(M)|\& Mode' just c column oper_type noprint column end_sz format 999,999 heading 'Final |Size(M)' column stat format a3 heading 'Sta' trunc column num_ops format 9999 heading 'Num|Ops' select to_char(start_time, 'MMDD HH24:MI:SS') op_st_tm , 60*60*24*(e.end_time-e.start_time) elapsd_s , replace(replace(replace(e.component, 'DEFAULT ', 'D:'), 'KEEP ', 'K:'), 'RECYCLE','R:') comp , e.initial_size/&&btomb ini_sz , to_char((e.target_size - e.initial_size)/&&btomb,'S9999') ||' '||substr(e.oper_mode,1,3) op_tm , e.final_size/&&btomb end_sz , e.status stat , e.num_ops num_ops , e.oper_type from stats$memory_resize_ops e where e.dbid = :dbid and e.instance_number = :inst_num and ( e.start_time between to_date(:btime, 'YYYYMMDD HH24:MI:SS') and to_date(:etime, 'YYYYMMDD HH24:MI:SS') or e.end_time between to_date(:btime, 'YYYYMMDD HH24:MI:SS') and to_date(:etime, 'YYYYMMDD HH24:MI:SS') ) order by op_st_tm, oper_type desc; -- -- Buffer Pool Advisory set newpage none; set heading off; set termout off; ttitle off; repfooter off; column k2_cache new_value k2_cache noprint; column k4_cache new_value k4_cache noprint; column k8_cache new_value k8_cache noprint; column k16_cache new_value k16_cache noprint; column k32_cache new_value k32_cache noprint; column def_cache new_value def_cache noprint; column rec_cache new_value rec_cache noprint; column kee_cache new_value kee_cache noprint; select nvl(sum (case when name = 'db_2k_cache_size' then value else '0' end),'0') k2_cache , nvl(sum (case when name = 'db_4k_cache_size' then value else '0' end),'0') k4_cache , nvl(sum (case when name = 'db_8k_cache_size' then value else '0' end),'0') k8_cache , nvl(sum (case when name = 'db_16k_cache_size' then value else '0' end),'0') k16_cache , nvl(sum (case when name = 'db_32k_cache_size' then value else '0' end),'0') k32_cache , decode(nvl(sum (case when name = 'db_keep_cache_size' then value else '0' end),'0') , '0' , nvl(sum (case when name = 'buffer_pool_keep' then to_char(decode( 0 , instrb(value, 'buffers') , value , decode(1, instrb(value, 'lru_latches') , substr(value, instrb(value,',')+10, 9999) , decode(0,instrb(value, ', lru') , substrb(value,9,99999) , substrb(substrb(value,1,instrb(value,', lru_latches:')-1),9,99999) ))) * :bs) else '0' end),'0') , sum (case when name = 'db_keep_cache_size' then value else '0' end)) kee_cache , decode(nvl(sum (case when name = 'db_recycle_cache_size' then value else '0' end),'0') , '0' , nvl(sum (case when name = 'buffer_pool_recycle' then to_char(decode( 0 , instrb(value, 'buffers') , value , decode(1, instrb(value, 'lru_latches') , substr(value, instrb(value,',')+10, 9999) , decode(0,instrb(value, ', lru') , substrb(value,9,99999) , substrb(substrb(value,1,instrb(value,', lru_latches:')-1),9,99999) ))) * :bs) else '0' end),'0') , sum (case when name = 'db_recycle_cache_size' then value else '0' end) ) rec_cache , decode(nvl(sum (case when name = '__db_cache_size' then value else '0' end) , '0') , '0' , nvl(sum (case when name = 'db_block_buffers' then to_char(value * :bs) else '0' end),'0') , sum (case when name = '__db_cache_size' then value else '0' end) ) def_cache from stats$parameter where name in ( 'db_2k_cache_size' ,'db_4k_cache_size' ,'db_8k_cache_size' ,'db_16k_cache_size' ,'db_32k_cache_size' ,'__db_cache_size' ,'db_block_buffers' ,'db_keep_cache_size' ,'buffer_pool_keep' ,'db_recycle_cache_size','buffer_pool_recycle') and snap_id = :eid and dbid = :dbid and instance_number = :inst_num; variable k2_cache number; variable k4_cache number variable k8_cache number; variable k16_cache number; variable k32_cache number; variable def_cache number; variable rec_cache number; variable kee_cache number; begin :k2_cache := to_number('&k2_cache')/1024; :k4_cache := to_number('&k4_cache')/1024; :k8_cache := to_number('&k8_cache')/1024; :k16_cache := to_number('&k16_cache')/1024; :k32_cache := to_number('&k32_cache')/1024; :def_cache := to_number('&def_cache')/1024; :rec_cache := to_number('&rec_cache')/1024; :kee_cache := to_number('&kee_cache')/1024; end; / set termout on; set heading on; repfooter center - '-------------------------------------------------------------'; ttitle lef 'Buffer Pool Advisory '- 'DB/Inst: ' db_name '/' inst_name ' '- 'End Snap: ' format 99999999 end_snap - skip 1 - lef '-> Only rows with estimated physical reads >0 are displayed' - skip 1 - lef '-> ordered by Pool, Block Size, Buffers For Estimate' - skip 2; column id format 999; column bpool format a3 heading 'P' trunc; column order_def_bs noprint column advice_status format a2 trunc heading 'ON'; column block_size format 99999 heading 'Block|Size'; column sfe format 999,999 heading 'Size for|Est (M)'; column bfe format 999,999,999 heading 'Buffers|(thousands)'; column eprf format 990.9 heading 'Est|Phys|Read|Factr'; column epr format 9,999,999,999 heading 'Estimated|Phys Reads|(thousands)'; column bcsf format 99.9 heading 'Size|Factr' column eprt format 999,999,999 heading 'Est Phys|Read Time' column epdbt format 999.9 heading 'Est|% dbtime|for Rds' select replace( block_size/1024||'k', :bs/1024||'k' , substr(name,1,1)) bpool , decode(block_size, :bs, 1, 2) order_def_bs , size_for_estimate sfe , nvl( size_factor , decode( replace(block_size/1024||'k', :bs/1024||'k' , substr(name,1,1)) , '2k' , size_for_estimate*1024/:k2_cache , '4k' , size_for_estimate*1024/:k4_cache , '8k' , size_for_estimate*1024/:k8_cache , '16k', size_for_estimate*1024/:k16_cache , '32k', size_for_estimate*1024/:k32_cache , 'D' , size_for_estimate*1024/:def_cache , 'K' , size_for_estimate*1024/:kee_cache , 'R' , size_for_estimate*1024/:rec_cache ) ) bcsf , buffers_for_estimate/1000 bfe , estd_physical_read_factor eprf , estd_physical_reads/1000 epr , estd_physical_read_time eprt , estd_pct_of_db_time_for_reads epdbt from stats$db_cache_advice where snap_id = :eid and dbid = :dbid and instance_number = :inst_num and estd_physical_reads > 0 order by order_def_bs, block_size, name, buffers_for_estimate; set newpage 1; -- -- Buffer pools ttitle lef 'Buffer Pool Statistics '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> Standard block size Pools D: default, K: keep, R: recycle' - skip 1 - lef '-> Default Pools for other block sizes: 2k, 4k, 8k, 16k, 32k' - skip 1 - lef '-> Buffers: the number of buffers. Units of K, M, G are divided by 1000' - skip 2; col id format 99 heading 'Set|Id'; col name format a3 heading 'P' trunc; col buffs format 9,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 99,999 heading 'Free|Buffer|Waits'; col wcwait format 999 heading 'Writ|Comp|Wait'; col bbwait format 9,999,999 heading 'Buffer|Busy|Waits' col poolhr format 999 heading 'Pool|Hit%' col numbufs format a7 heading 'Buffers' -- col numbufs format 9,999,999 heading 'Number of|Buffers' select replace(e.block_size/1024||'k', :bs/1024||'k', substr(e.name,1,1)) name , lpad(case when e.set_msize <= 9999 then to_char(e.set_msize)||' ' when trunc((e.set_msize)/1000) <= 9999 then to_char(trunc((e.set_msize)/1000))||'K' when trunc((e.set_msize)/1000000) <= 9999 then to_char(trunc((e.set_msize)/1000000))||'M' when trunc((e.set_msize)/1000000000) <= 9999 then to_char(trunc((e.set_msize)/1000000000))||'G' when trunc((e.set_msize)/1000000000000) <= 9999 then to_char(trunc((e.set_msize)/1000000000000))||'T' else substr(to_char(trunc((e.set_msize)/1000000000000000))||'P', 1, 5) end , 7, ' ') numbufs , decode( e.db_block_gets - nvl(b.db_block_gets,0) + e.consistent_gets - nvl(b.consistent_gets,0) , 0, to_number(null) , (100* (1 - ( (e.physical_reads - nvl(b.physical_reads,0)) / ( e.db_block_gets - nvl(b.db_block_gets,0) + e.consistent_gets - nvl(b.consistent_gets,0)) ) ) ) ) poolhr , e.db_block_gets - nvl(b.db_block_gets,0) + e.consistent_gets - nvl(b.consistent_gets,0) buffs , e.physical_reads - nvl(b.physical_reads,0) phread , e.physical_writes - nvl(b.physical_writes,0) phwrite , e.free_buffer_wait - nvl(b.free_buffer_wait,0) fbwait , e.write_complete_wait - nvl(b.write_complete_wait,0) wcwait , e.buffer_busy_wait - nvl(b.buffer_busy_wait,0) 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 newpage 1; set heading off; ttitle off; select 'The following buffer pool no longer exists in the end snapshot: ' || replace(b.block_size/1024||'k', :bs/1024||'k', substr(b.name,1,1)) from stats$buffer_pool_statistics b where b.snap_id = :bid and b.dbid = :dbid and b.instance_number = :inst_num minus select 'The following buffer pool no longer exists in the end snapshot: ' || replace(e.block_size/1024||'k', :bs/1024||'k', substr(e.name,1,1)) from stats$buffer_pool_statistics e where e.snap_id = :eid and e.dbid = :dbid and e.instance_number = :inst_num; set colsep ' '; set underline on; set heading on; -- -- Buffer waits ttitle lef 'Buffer wait Statistics '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 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 'Total Wait Time (s)'; column iavg format 999,990 heading 'Avg Time (ms)' just c; select e.class , e.wait_count - nvl(b.wait_count,0) icnt , (e.time - nvl(b.time,0))/100 itim ,10* (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; set newpage 0; -- -- PGA Memory Statistics ttitle lef 'PGA Aggr Target Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> B: Begin snap E: End snap (rows identified with B or E contain data' - skip 1 - lef ' which is absolute i.e. not diffed over the interval)' - skip 1 - lef '-> PGA cache hit % - percentage of W/A (WorkArea) data processed only in-memory' - skip 1 - lef '-> Auto PGA Target - actual workarea memory target'- skip 1 - lef '-> W/A PGA Used - amount of memory used for all WorkAreas (manual + auto)'- skip 1 - lef '-> %PGA W/A Mem - percentage of PGA memory allocated to WorkAreas'- skip 1 - lef '-> %Auto W/A Mem - percentage of WorkArea memory controlled by Auto Mem Mgmt'- skip 1 - lef '-> %Man W/A Mem - percentage of WorkArea memory under Manual control'- skip 2; repfooter off; -- Show the PGA cache hit percentage for this interval col tbp format 9,999,999,999 heading 'W/A MB Processed' col tbrw format 9,999,999,999 heading 'Extra W/A MB Read/Written' col calc_cache_pct format 990.0 heading 'PGA Cache Hit %' select 100 * (e.bytes - nvl(b.bytes,0)) / (e.bytes - nvl(b.bytes,0) + e.bytesrw - nvl(b.bytesrw,0)) calc_cache_pct , (e.bytes - nvl(b.bytes,0)) /1024/1024 tbp , (e.bytesrw - nvl(b.bytesrw,0))/1024/1024 tbrw from (select sum(case when name = 'bytes processed' then value else 0 end) bytes , sum(case when name = 'extra bytes read/written' then value else 0 end) bytesrw from stats$pgastat e1 where e1.snap_id = :eid and e1.dbid = :dbid and e1.instance_number = :inst_num and e1.name in ('bytes processed','extra bytes read/written') ) e , (select sum(case when name = 'bytes processed' then value else 0 end) bytes , sum(case when name = 'extra bytes read/written' then value else 0 end) bytesrw from stats$pgastat b1 where b1.snap_id = :bid and b1.dbid = :dbid and b1.instance_number = :inst_num and b1.name in ('bytes processed','extra bytes read/written') ) b where e.bytes - nvl(b.bytes,0) > 0; set newpage 1; -- Display overflow warning, if needed ttitle off; set heading off; col nl format a78 newline select 'Warning: pga_aggregate_target was set too low for current workload, as this' nl , ' value was exceeded during this interval. Use the PGA Advisory view' nl , ' to help identify a different value for pga_aggregate_target.' nl from stats$pgastat e , stats$pgastat b , stats$parameter p where e.snap_id = :eid and e.dbid = :dbid and e.instance_number = :inst_num and e.name = 'over allocation count' and b.snap_id(+) = :bid and b.dbid(+) = e.dbid and b.instance_number(+) = e.instance_number and b.name(+) = e.name and e.value > nvl(b.value,0) and p.snap_id = :eid and p.dbid = :dbid and p.instance_number = :inst_num and p.name = 'workarea_size_policy' and p.value = 'AUTO'; set heading on; repfooter center - '-------------------------------------------------------------'; -- Display Begin and End statistics for this interval column snap format a1 heading ''; column pgaat format 999,999 heading 'PGA Aggr|Target(M)' just c; column pat format 999,999 heading 'Auto PGA|Target(M)' just c; column tot_pga_allo format 999,990.9 heading 'PGA Mem|Alloc(M)' just c; column tot_tun_used format 999,990.9 heading 'W/A PGA|Used(M)' just c; column pct_tun format 999.9 heading '%PGA|W/A|Mem' just c; column pct_auto_tun format 999.9 heading '%Auto|W/A|Mem' just c; column pct_man_tun format 999.9 heading '%Man|W/A|Mem' just c; column glo_mem_bnd format 9,999,999 heading 'Global Mem|Bound(K)' just c; select 'B' snap , to_number(p.value)/1024/1024 pgaat , mu.pat/1024/1024 pat , mu.PGA_alloc/1024/1024 tot_pga_allo , (mu.PGA_used_auto + mu.PGA_used_man)/1024/1024 tot_tun_used , 100*(mu.PGA_used_auto + mu.PGA_used_man) / PGA_alloc pct_tun , decode(mu.PGA_used_auto + mu.PGA_used_man, 0, 0 , 100* mu.PGA_used_auto/(mu.PGA_used_auto + mu.PGA_used_man) ) pct_auto_tun , decode(mu.PGA_used_auto + mu.PGA_used_man, 0, 0 , 100* mu.PGA_used_man / (mu.PGA_used_auto + mu.PGA_used_man) ) pct_man_tun , mu.glob_mem_bnd/1024 glo_mem_bnd from (select sum(case when name = 'total PGA allocated' then value else 0 end) PGA_alloc , sum(case when name = 'total PGA used for auto workareas' then value else 0 end) PGA_used_auto , sum(case when name = 'total PGA used for manual workareas' then value else 0 end) PGA_used_man , sum(case when name = 'global memory bound' then value else 0 end) glob_mem_bnd , sum(case when name = 'aggregate PGA auto target' then value else 0 end) pat from stats$pgastat pga where pga.snap_id = :bid and pga.dbid = :dbid and pga.instance_number = :inst_num ) mu , stats$parameter p where p.snap_id = :bid and p.dbid = :dbid and p.instance_number = :inst_num and p.name = 'pga_aggregate_target' and p.value != '0' union all select 'E' snap , to_number(p.value)/1024/1024 pgaat , mu.pat/1024/1024 pat , mu.PGA_alloc/1024/1024 tot_pga_allo , (mu.PGA_used_auto + mu.PGA_used_man)/1024/1024 tot_tun_used , 100*(mu.PGA_used_auto + mu.PGA_used_man) / PGA_alloc pct_tun , decode(mu.PGA_used_auto + mu.PGA_used_man, 0, 0 , 100* mu.PGA_used_auto/(mu.PGA_used_auto + mu.PGA_used_man) ) pct_auto_tun , decode(mu.PGA_used_auto + mu.PGA_used_man, 0, 0 , 100* mu.PGA_used_man / (mu.PGA_used_auto + mu.PGA_used_man) ) pct_man_tun , mu.glob_mem_bnd/1024 glo_mem_bnd from (select sum(case when name = 'total PGA allocated' then value else 0 end) PGA_alloc , sum(case when name = 'total PGA used for auto workareas' then value else 0 end) PGA_used_auto , sum(case when name = 'total PGA used for manual workareas' then value else 0 end) PGA_used_man , sum(case when name = 'global memory bound' then value else 0 end) glob_mem_bnd , sum(case when name = 'aggregate PGA auto target' then value else 0 end) pat from stats$pgastat pga where pga.snap_id = :eid and pga.dbid = :dbid and pga.instance_number = :inst_num ) mu , stats$parameter p where p.snap_id = :eid and p.dbid = :dbid and p.instance_number = :inst_num and p.name = 'pga_aggregate_target' and p.value != '0' order by snap; set heading on; set newpage 1; -- PGA usage Histogram ttitle lef 'PGA Aggr Target Histogram '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> Optimal Executions are purely in-memory operations' - skip 2; col low_o format a7 heading 'Low|Optimal' just r col high_o format a7 heading 'High|Optimal' just r col tot_e format 9,999,999,999 heading 'Total Execs' col opt_e format 999,999,999 heading 'Optimal Execs' col one_e format 999,999,999 heading '1-Pass Execs' col mul_e format 999,999,999 heading 'M-Pass Execs' select case when e.low_optimal_size >= 1024*1024*1024*1024 then lpad(round(e.low_optimal_size/1024/1024/1024/1024) || 'T',7) when e.low_optimal_size >= 1024*1024*1024 then lpad(round(e.low_optimal_size/1024/1024/1024) || 'G' ,7) when e.low_optimal_size >= 1024*1024 then lpad(round(e.low_optimal_size/1024/1024) || 'M',7) when e.low_optimal_size >= 1024 then lpad(round(e.low_optimal_size/1024) || 'K',7) else lpad(e.low_optimal_size || 'B',7) end low_o , case when e.high_optimal_size >= 1024*1024*1024*1024 then lpad(round(e.high_optimal_size/1024/1024/1024/1024) || 'T',7) when e.high_optimal_size >= 1024*1024*1024 then lpad(round(e.high_optimal_size/1024/1024/1024) || 'G',7) when e.high_optimal_size >= 1024*1024 then lpad(round(e.high_optimal_size/1024/1024) || 'M',7) when e.high_optimal_size >= 1024 then lpad(round(e.high_optimal_size/1024) || 'K',7) else e.high_optimal_size || 'B' end high_o , e.total_executions - nvl(b.total_executions,0) tot_e , e.optimal_executions - nvl(b.optimal_executions,0) opt_e , e.onepass_executions - nvl(b.onepass_executions,0) one_e , e.multipasses_executions - nvl(b.multipasses_executions,0) mul_e from stats$sql_workarea_histogram e , stats$sql_workarea_histogram b where e.snap_id = :eid and e.dbid = :dbid and e.instance_number = :inst_num and b.snap_id(+) = :bid and b.dbid(+) = e.dbid and b.instance_number(+) = e.instance_number and b.low_optimal_size(+) = e.low_optimal_size and b.high_optimal_size(+) = e.high_optimal_size and e.total_executions - nvl(b.total_executions,0) > 0 order by e.low_optimal_size; -- PGA Advisory ttitle lef 'PGA Memory Advisory '- 'DB/Inst: ' db_name '/' inst_name ' '- 'End Snap: ' format 99999999 end_snap - skip 1 - lef '-> When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value'- skip 1 - lef ' where Estd PGA Overalloc Count is 0' - skip 2; col pga_t format 9,999,999 heading 'PGA Aggr|Target|Est (MB)' col pga_tf format 990.0 heading 'Size|Factr' col byt_p format 9,999,999,990 heading 'W/A MB|Processed' col byt_rw format 9,999,999,990 heading 'Estd Extra|W/A MB|Read/Written|to Disk' just c col esttim format 9,990.0 heading 'Estd Time|to Process|Bytes (s)' col epchp format 990.0 heading 'Estd|PGA|Cache|Hit %' col eoc format 9,999,999 heading 'Estd PGA|Overalloc|Count' select pga_target_for_estimate/1024/1024 pga_t , pga_target_factor pga_tf , bytes_processed/1024/1024 byt_p , estd_extra_bytes_rw/1024/1024 byt_rw , estd_time/&&ustos esttim , estd_pga_cache_hit_percentage epchp , estd_overalloc_count eoc from stats$pga_target_advice e where snap_id = :eid and dbid = :dbid and instance_number = :inst_num order by pga_target_for_estimate; -- -- PGA Memory Stats set newpage 0; ttitle lef 'Process Memory Summary Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> B: Begin snap E: End snap' - skip 1 - lef '-> All rows below contain absolute values (i.e. not diffed over the interval)' - skip 1 - lef '-> Max Alloc is Maximum PGA Allocation size at snapshot time' - skip 1 - lef ' Hist Max Alloc is the Historical Max Allocation for still-connected processes' - skip 1 - lef '-> Num Procs or Allocs: For Begin/End snapshot lines, it is the number of' - skip 1 - lef ' processes. For Category lines, it is the number of allocations' - skip 1 - lef '-> ordered by Begin/End snapshot, Alloc (MB) desc' - skip 2; col b_or_e format a1 heading ' ' col ord_col noprint col snid noprint col cat format a8 heading 'Category' trunc col tot_alloc_mb format 99,999.9 heading 'Alloc|(MB)' just c col tot_used_mb format 99,999.9 heading 'Used|(MB)' just c col tot_free_pga_mb format 9,999.9 heading 'Freeabl|(MB)' just c col avg_alloc_mb format 9,999.9 heading 'Avg|Alloc|(MB)' just c col cov_alloc_mb format 999.9 heading 'Coeff of|Variance' just c col stddev_alloc_mb format 999.9 heading 'Std Dev|Alloc|(MB)' just c col max_alloc_mb format 99,999 heading 'Max|Alloc|(MB)' just c col max_max_alloc_mb format 9,999 heading 'Hist|Max|Alloc|(MB)' just c col nza format 9,999 heading 'Num|Procs|or|Allocs' just c break on b_or_e select * from (select decode(snap_id, :bid, 'B', :eid, 'E') b_or_e , 1 ord_col , snap_id snid , '---------' cat , pga_alloc_mem/&&btomb tot_alloc_mb , pga_used_mem/&&btomb tot_used_mb , pga_freeable_mem/&&btomb tot_free_pga_mb , avg_pga_alloc_mem/&&btomb avg_alloc_mb , stddev_pga_alloc_mem/&&btomb stddev_alloc_mb , max_pga_alloc_mem/&&btomb max_alloc_mb , max_pga_max_mem/&&btomb max_max_alloc_mb , num_processes nza from stats$process_rollup where snap_id in (:bid, :eid) and dbid = :dbid and instance_number = :inst_num and pid = -9 union all select decode(snap_id, :bid, 'B', :eid, 'E') b_or_e , 2 ord_col , snap_id snid , category cat , allocated/&&btomb tot_alloc_mb , used/&&btomb tot_used_mb , to_number(null) tot_free_pga_mb , avg_allocated /&&btomb avg_alloc_mb , stddev_allocated/&&btomb stddev_alloc_mb , max_allocated/&&btomb max_alloc_mb , max_max_allocated/&&btomb max_max_alloc_mb , non_zero_allocations nza from stats$process_memory_rollup where snap_id in (:bid, :eid) and dbid = :dbid and instance_number = :inst_num and pid = -9 ) order by snid, ord_col, tot_alloc_mb desc; -- -- PGA Allocation by Component summary clear breaks set newpage 1; ttitle lef 'Top Process Memory (by component) '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> ordered by Begin/End snapshot, Alloc (MB) desc' - skip 2; col b_or_e heading '' col snid noprint col ord_col noprint col pid format 99999 heading 'PId' col cat format a13 heading 'Category' trunc col tot_alloc_mb format 9999.9 heading 'Alloc|(MB)' just c col tot_used_mb format 9999.9 heading 'Used|(MB)' just c col max_alloc_mb format 9999.9 heading 'Max|Alloc (MB)' just c col max_max_alloc_mb format 9999.9 heading 'Hist Max|Alloc (MB)' just c col tot_alloc_mb2 noprint clear breaks break on b_or_e on pid select * from (select decode(snap_id, :bid, 'B', :eid, 'E') b_or_e , snap_id snid , 1 ord_col , pid pid , rpad(substr( program, instrb(program,'(') +1 , instrb(program, ')')-1-instrb(program,'(')) || ' ' , 13, '-') cat , pga_alloc_mem/&&btomb tot_alloc_mb , pga_used_mem/&&btomb tot_used_mb , pga_freeable_mem/&&btomb tot_free_pga_mb , max_pga_alloc_mem/&&btomb max_alloc_mb , max_pga_max_mem/&&btomb max_max_alloc_mb , pga_alloc_mem/&&btomb tot_alloc_mb2 from stats$process_rollup where snap_id in (:bid, :eid) and dbid = :dbid and instance_number = :inst_num and pid != -9 union all select decode(pmr.snap_id, :bid, 'B', :eid, 'E') b_or_e , pmr.snap_id snid , 2 ord_col , pmr.pid pid , pmr.category cat , pmr.allocated/&&btomb tot_alloc_mb , pmr.used/&&btomb tot_used_mb , to_number(null) pga_free_mb , pmr.max_allocated/&&btomb max_alloc_mb , pmr.max_max_allocated/&&btomb max_max_alloc_mb , pr.pga_alloc_mem/&&btomb tot_alloc_mb2 from stats$process_memory_rollup pmr , stats$process_rollup pr where pmr.snap_id in (:bid, :eid) and pmr.dbid = :dbid and pmr.instance_number = :inst_num and pmr.pid != -9 and pr.snap_id = pmr.snap_id and pr.dbid = pmr.dbid and pr.instance_number = pmr.instance_number and pr.pid = pmr.pid and pmr.serial# = pmr.serial# ) order by snid, tot_alloc_mb2 desc, pid, ord_col,tot_alloc_mb desc; set newpage 0 -- -- Enqueue activity ttitle lef 'Enqueue activity '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> only enqueues with waits are shown' - skip 1 - lef '-> Enqueue stats gathered prior to 10g should not be compared with 10g data' - skip 1 - lef '-> ordered by Wait Time desc, Waits desc' - skip 2; col ety format a78 heading 'Enqueue Type (Request Reason)' trunc; col reqs format 999,999,990 heading 'Requests' newline; col sreq format 999,999,990 heading 'Succ Gets'; col freq format 99,999,990 heading 'Failed Gets'; col waits format 99,999,990 heading 'Waits'; col wttm format 999,999,999 heading 'Wt Time (s)' just c; col awttm format 9,999,999.99 heading 'Av Wt Time(ms)' just c; select /*+ ordered */ e.eq_type || '-' || to_char(nvl(l.name,' ')) || decode( upper(e.req_reason) , 'CONTENTION', null , '-', null , ' ('||e.req_reason||')') ety , e.total_req# - nvl(b.total_req#,0) reqs , e.succ_req# - nvl(b.succ_req#,0) sreq , e.failed_req# - nvl(b.failed_req#,0) freq , e.total_wait# - nvl(b.total_wait#,0) waits , (e.cum_wait_time - nvl(b.cum_wait_time,0))/1000 wttm , decode( (e.total_wait# - nvl(b.total_wait#,0)) , 0, to_number(NULL) , ( (e.cum_wait_time - nvl(b.cum_wait_time,0)) / (e.total_wait# - nvl(b.total_wait#,0)) ) ) awttm from stats$enqueue_statistics e , stats$enqueue_statistics b , v$lock_type l 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.eq_type(+) = e.eq_type and b.req_reason(+) = e.req_reason and e.total_wait# - nvl(b.total_wait#,0) > 0 and l.type(+) = e.eq_type order by wttm desc, waits desc; -- -- Rollback segment ttitle off; repfooter off; set newpage none; set heading off; set termout off; column auto_undo new_value auto_undo noprint select value auto_undo from stats$parameter where snap_id = :eid and dbid = :dbid and instance_number = :inst_num and name = 'undo_management'; repfooter center - '-------------------------------------------------------------'; set newpage 0; set heading on; set termout on; ttitle lef 'Rollback Segment Stats '- 'DB/Inst: ' db_name '/' inst_name ' ' - 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '->A high value for "Pct Waits" suggests more rollback segments may be required' - skip 1 - lef '->RBS stats may not be accurate between begin and end snaps when using Auto Undo,' - skip 1 - lef ' managment, as RBS may be dynamically created and dropped as needed' - skip 2; column usn format 99990 heading 'RBS No' Just Cen; column gets format 999,999,990.9 heading 'Trans Table|Gets' Just Cen; column waits format 990.99 heading 'Pct|Waits'; column writes format 99,999,999,990 heading 'Undo Bytes|Written' Just Cen; 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 and ( '&&auto_undo' = 'MANUAL' or upper('&&display_rollstat') = 'Y' ) order by e.usn; ttitle lef 'Rollback Segment Storage '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 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 and ( '&&auto_undo' = 'MANUAL' or upper('&&display_rollstat') = 'Y' ) order by e.usn; -- -- Undo Segment ttitle lef 'Undo Segment Summary '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> Min/Max TR (mins) - Min and Max Tuned Retention (minutes)' - skip 1 - lef '-> STO - Snapshot Too Old count, OOS - Out Of Space count' - skip 1 - lef '-> Undo segment block stats:' - skip 1 - lef ' uS - unexpired Stolen, uR - unexpired Released, uU - unexpired reUsed' - skip 1 - lef ' eS - expired Stolen, eR - expired Released, eU - expired reUsed' - skip 2; column undotsn format 999 heading 'Undo|TS#'; column undob format 99,999.0 heading 'Num Undo|Blocks (K)'; column txcnt format 99,999,999,999 heading 'Number of|Transactions'; column maxq format 999,999 heading 'Max Qry|Len (s)'; column maxc format 9,999,999 heading 'Max Tx|Concy'; column mintun format a9 heading 'Min/Max|TR (mins)' wrap; column snolno format a5 heading 'STO/|OOS' wrap; column blkst format a11 heading 'uS/uR/uU/|eS/eR/eU' wrap; column unst format 9,999 heading 'Unexp|Stolen' newline; column unrl format 9,999 heading 'Unexp|Relesd'; column unru format 9,999 heading 'Unexp|Reused'; column exst format 9,999 heading 'Exp|Stolen'; column exrl format 9,999 heading 'Exp|Releas'; column exru format 9,999 heading 'Exp|Reused'; select undotsn , sum(undoblks)/1000 undob , sum(txncount) txcnt , max(maxquerylen) maxq , max(maxconcurrency) maxc , round(min(tuned_undoretention)/60,1) ||'/'|| round(max(tuned_undoretention)/60,1) mintun , sum(ssolderrcnt) ||'/'|| sum(nospaceerrcnt) snolno , sum(unxpstealcnt) ||'/'|| sum(unxpblkrelcnt) ||'/'|| sum(unxpblkreucnt) ||'/'|| sum(expstealcnt) ||'/'|| sum(expblkrelcnt) ||'/'|| sum(expblkreucnt) blkst from stats$undostat where dbid = :dbid and instance_number = :inst_num and end_time > to_date(:btime, 'YYYYMMDD HH24:MI:SS') and begin_time < to_date(:etime, 'YYYYMMDD HH24:MI:SS') and upper('&&display_undostat') = 'Y' group by undotsn; set newpage 2; ttitle lef 'Undo Segment Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> Most recent ' &&top_n_undostat ' Undostat rows, ordered by End Time desc' - skip 2; column undotsn format 999 heading 'Undo|TS#' noprint; column endt format a12 heading 'End Time'; column undob format 99,999,999 heading 'Num Undo|Blocks'; column txcnt format 999,999,999 heading 'Number of|Transactions'; column maxq format 99,999 heading 'Max Qry|Len (s)'; column maxc format 99,999 heading 'Max Tx|Concy'; column mintun format 99,999 heading 'Tun Ret|(mins)'; column snolno format a5 heading 'STO/|OOS' wrap; column blkst format a11 heading 'uS/uR/uU/|eS/eR/eU' wrap; select undotsn , endt , undob , txcnt , maxq , maxc , mintun , snolno , blkst from (select undotsn , to_char(end_time, 'DD-Mon HH24:MI') endt , undoblks undob , txncount txcnt , maxquerylen maxq , maxconcurrency maxc , tuned_undoretention/60 mintun , ssolderrcnt || '/' || nospaceerrcnt snolno , unxpstealcnt ||'/'|| unxpblkrelcnt ||'/'|| unxpblkreucnt ||'/'|| expstealcnt ||'/'|| expblkrelcnt ||'/'|| expblkreucnt blkst from stats$undostat where dbid = :dbid and instance_number = :inst_num and end_time > to_date(:btime, 'YYYYMMDD HH24:MI:SS') and begin_time < to_date(:etime, 'YYYYMMDD HH24:MI:SS') and upper('&&display_undostat') = 'Y' order by begin_time desc ) where rownum < &&top_n_undostat; set newpage 0; -- -- Latch Activity ttitle lef 'Latch Activity '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 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 a24 heading 'Latch' 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'; column wt format 99990 heading 'Wait|Time|(s)'; 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.wait_time - b.wait_time)/1000000 wt , 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 + e.immediate_gets - b.immediate_gets ) > 0 order by b.name; -- -- Latch Sleep breakdown ttitle lef 'Latch Sleep breakdown '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> ordered by misses desc' - skip 2; column gets clear; column name format a26 heading 'Latch Name' trunc; column gets format 99,999,999,990 heading 'Get|Requests'; column sleeps format 99,999,990 heading 'Sleeps'; column spin_gets format 99,999,990 heading 'Spin|Gets'; column misses format 999,999,990 heading 'Misses'; select b.name name , e.gets - b.gets gets , e.misses - b.misses misses , e.sleeps - b.sleeps sleeps , e.spin_gets - b.spin_gets spin_gets 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 '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 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 999,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/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 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.spin_gets 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 , e.spin_gets - b.spin_gets spin_gets 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/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> only latches with sleeps/gets > 1/100000 are shown' - skip 1 - lef '-> ordered by name, gets desc' - skip 2; column name format a22 heading 'Latch Name' trunc; column child format 999999 heading 'Child|Num'; column gets format 999,999,990 heading 'Get|Requests'; column spin_gets format 99,999,990 heading 'Spin|Gets'; select l.name , lc.child , lc.gets , lc.misses , lc.sleeps , lc.spin_gets 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 , e.spin_gets - b.spin_gets spin_gets 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 and (e.sleeps - b.sleeps) / (e.gets - b.gets) > .00001 ) 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; -- -- Mutex Statistics ttitle lef 'Mutex Sleep '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> ordered by Wait Time desc' - skip 2; column mux format a18 heading 'Mutex Type' trunc; column loc format a32 heading 'Location' trunc; column sleeps format 9,999,999,990 heading 'Sleeps'; column wt format 9,999,990.9 heading 'Wait |Time (s)'; select e.mutex_type mux , e.location loc , e.sleeps - nvl(b.sleeps, 0) sleeps , (e.wait_time - nvl(b.wait_time, 0))/&ustos wt from stats$mutex_sleep b , stats$mutex_sleep e where b.snap_id(+) = :bid and e.snap_id = :eid and e.dbid = :dbid and b.dbid(+) = e.dbid and e.instance_number = :inst_num and b.instance_number(+) = e.instance_number and b.mutex_type(+) = e.mutex_type and b.location(+) = e.location and e.sleeps - nvl(b.sleeps, 0) > 0 order by e.wait_time - nvl(b.wait_time, 0) desc; -- -- Segment Statistics -- Logical Reads ttitle lef 'Segments by Logical Reads ' - 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> End Segment Logical Reads Threshold: ' format 99999999 eslr - skip 1 - '-> Pct Total shows % of logical reads for each top segment compared with total' - skip 1 - ' logical reads for all segments captured by the Snapshot' - skip 2; column owner heading "Owner" format a10 trunc column tablespace_name heading "Tablespace" format a10 trunc column object_name heading "Object Name" format a20 trunc column subobject_name heading "Subobject|Name" format a12 trunc column object_type heading "Obj.|Type" format a5 trunc col ratio heading " Pct|Total" format a5 column logical_reads heading "Logical|Reads" format 999,999,999 select n.owner , n.tablespace_name , n.object_name , case when length(n.subobject_name) < 11 then n.subobject_name else substr(n.subobject_name,length(n.subobject_name)-9) end subobject_name , n.object_type , r.logical_reads , substr(to_char(r.ratio * 100,'999.9MI'), 1, 5) ratio from stats$seg_stat_obj n , (select * from (select e.dataobj# , e.obj# , e.ts# , e.dbid , e.logical_reads - nvl(b.logical_reads, 0) logical_reads , ratio_to_report(e.logical_reads - nvl(b.logical_reads, 0)) over () ratio from stats$seg_stat e , stats$seg_stat b 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.ts#(+) = e.ts# and b.obj#(+) = e.obj# and b.dataobj#(+) = e.dataobj# and e.logical_reads - nvl(b.logical_reads, 0) > 0 order by logical_reads desc) d where rownum <= &&top_n_segstat) r where n.dataobj# = r.dataobj# and n.obj# = r.obj# and n.ts# = r.ts# and n.dbid = r.dbid and 7 <= (select snap_level from stats$snapshot where snap_id = :bid) order by logical_reads desc; -- Physical Reads set newpage 2 ttitle lef 'Segments by Physical Reads '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> End Segment Physical Reads Threshold: ' espr - skip 2 column physical_reads heading "Physical|Reads" format 999,999,999 select n.owner , n.tablespace_name , n.object_name , case when length(n.subobject_name) < 11 then n.subobject_name else substr(n.subobject_name,length(n.subobject_name)-9) end subobject_name , n.object_type , r.physical_reads , substr(to_char(r.ratio * 100,'999.9MI'), 1, 5) ratio from stats$seg_stat_obj n , (select * from (select e.dataobj# , e.obj# , e.ts# , e.dbid , e.physical_reads - nvl(b.physical_reads, 0) physical_reads , ratio_to_report(e.physical_reads - nvl(b.physical_reads, 0)) over () ratio from stats$seg_stat e , stats$seg_stat b 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.ts#(+) = e.ts# and b.obj#(+) = e.obj# and b.dataobj#(+) = e.dataobj# and e.physical_reads - nvl(b.physical_reads, 0) > 0 order by physical_reads desc) d where rownum <= &&top_n_segstat) r where n.dataobj# = r.dataobj# and n.obj# = r.obj# and n.ts# = r.ts# and n.dbid = r.dbid and 7 <= (select snap_level from stats$snapshot where snap_id = :bid) order by physical_reads desc; -- Row Lock Waits set newpage 0 ttitle lef 'Segments by Row Lock Waits '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> End Segment Row Lock Waits Threshold: ' esrl - skip 2 column row_lock_waits heading "Row|Lock|Waits" format 999,999,999 select n.owner , n.tablespace_name , n.object_name , case when length(n.subobject_name) < 11 then n.subobject_name else substr(n.subobject_name,length(n.subobject_name)-9) end subobject_name , n.object_type , r.row_lock_waits , substr(to_char(r.ratio * 100,'999.9MI'), 1, 5) ratio from stats$seg_stat_obj n , (select * from (select e.dataobj# , e.obj# , e.ts# , e.dbid , e.row_lock_waits - nvl(b.row_lock_waits, 0) row_lock_waits , ratio_to_report(e.row_lock_waits - nvl(b.row_lock_waits, 0)) over () ratio from stats$seg_stat e , stats$seg_stat b 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.ts#(+) = e.ts# and b.obj#(+) = e.obj# and b.dataobj#(+) = e.dataobj# and e.row_lock_waits - nvl(b.row_lock_waits, 0) > 0 order by row_lock_waits desc) d where rownum <= &&top_n_segstat) r where n.dataobj# = r.dataobj# and n.obj# = r.obj# and n.ts# = r.ts# and n.dbid = r.dbid and 7 <= (select snap_level from stats$snapshot where snap_id = :bid) order by row_lock_waits desc; -- ITL Waits set newpage 2 ttitle lef 'Segments by ITL Waits ' - 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> End Segment ITL Waits Threshold: ' esiw - skip 2 column itl_waits heading "ITL|Waits" format 999,999,999 select n.owner , n.tablespace_name , n.object_name , n.subobject_name , n.object_type , r.itl_waits , substr(to_char(r.ratio * 100,'999.9MI'), 1, 5) ratio from stats$seg_stat_obj n , (select * from (select e.dataobj# , e.obj# , e.ts# , e.dbid , e.itl_waits - nvl(b.itl_waits, 0) itl_waits , ratio_to_report(e.itl_waits - nvl(b.itl_waits, 0)) over () ratio from stats$seg_stat e , stats$seg_stat b 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.ts#(+) = e.ts# and b.obj#(+) = e.obj# and b.dataobj#(+) = e.dataobj# and e.itl_waits - nvl(b.itl_waits, 0) > 0 order by itl_waits desc) d where rownum <= &&top_n_segstat) r where n.dataobj# = r.dataobj# and n.obj# = r.obj# and n.ts# = r.ts# and n.dbid = r.dbid and 7 <= (select snap_level from stats$snapshot where snap_id = :bid) order by itl_waits desc; -- Buffer Busy Waits set newpage 2 ttitle lef 'Segments by Buffer Busy Waits '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> End Segment Buffer Busy Waits Threshold: ' esbb - skip 2 column buffer_busy_waits heading "Buffer|Busy|Waits" format 999,999,999 select n.owner , n.tablespace_name , n.object_name , case when length(n.subobject_name) < 11 then n.subobject_name else substr(n.subobject_name,length(n.subobject_name)-9) end subobject_name , n.object_type , r.buffer_busy_waits , substr(to_char(r.ratio * 100,'999.9MI'), 1, 5) ratio from stats$seg_stat_obj n , (select * from (select e.dataobj# , e.obj# , e.ts# , e.dbid , e.buffer_busy_waits - nvl(b.buffer_busy_waits, 0) buffer_busy_waits , ratio_to_report(e.buffer_busy_waits - nvl(b.buffer_busy_waits, 0)) over () ratio from stats$seg_stat e , stats$seg_stat b 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.ts#(+) = e.ts# and b.obj#(+) = e.obj# and b.dataobj#(+) = e.dataobj# and e.buffer_busy_waits - nvl(b.buffer_busy_waits, 0) > 0 order by buffer_busy_waits desc) d where rownum <= &&top_n_segstat) r where n.dataobj# = r.dataobj# and n.obj# = r.obj# and n.ts# = r.ts# and n.dbid = r.dbid and 7 <= (select snap_level from stats$snapshot where snap_id = :bid) order by buffer_busy_waits desc; -- GC Buffer Busy Waits set newpage 0 ttitle lef 'Segments by Global Cache Buffer Busy Waits '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> There is no specific Threshold for Segment GC Buffer Busy Waits'- skip 2 column gc_buffer_busy heading "GC Buffer|Busy|Waits" format 999,999,999 select n.owner , n.tablespace_name , n.object_name , case when length(n.subobject_name) < 11 then n.subobject_name else substr(n.subobject_name,length(n.subobject_name)-9) end subobject_name , n.object_type , r.gc_buffer_busy , substr(to_char(r.ratio * 100,'999.9MI'), 1, 5) ratio from stats$seg_stat_obj n , (select * from (select e.dataobj# , e.obj# , e.ts# , e.dbid , e.gc_buffer_busy - nvl(b.gc_buffer_busy, 0) gc_buffer_busy , ratio_to_report(e.gc_buffer_busy - nvl(b.gc_buffer_busy, 0)) over () ratio from stats$seg_stat e , stats$seg_stat b 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.ts#(+) = e.ts# and b.obj#(+) = e.obj# and b.dataobj#(+) = e.dataobj# and e.gc_buffer_busy - nvl(b.gc_buffer_busy, 0) > 0 order by gc_buffer_busy desc) d where rownum <= &&top_n_segstat) r where n.dataobj# = r.dataobj# and n.obj# = r.obj# and n.ts# = r.ts# and n.dbid = r.dbid and 7 <= (select snap_level from stats$snapshot where snap_id = :bid) order by gc_buffer_busy desc; -- CR Blocks Received (was Served in versions prior to 10g) set newpage 2 ttitle lef 'Segments by CR Blocks Received ' - 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> End Global Cache CR Blocks Received Threshold: ' ecrb - skip 2 column cr_blocks_received heading "CR|Blocks|Recevd" format 999,999,999 select n.owner , n.tablespace_name , n.object_name , case when length(n.subobject_name) < 11 then n.subobject_name else substr(n.subobject_name,length(n.subobject_name)-9) end subobject_name , n.object_type , r.cr_blocks_received , substr(to_char(r.ratio * 100,'999.9MI'), 1, 5) ratio from stats$seg_stat_obj n , (select * from (select e.dataobj# , e.obj# , e.ts# , e.dbid , e.gc_cr_blocks_received-nvl(b.gc_cr_blocks_received, 0) cr_blocks_received , ratio_to_report(e.gc_cr_blocks_received - nvl(b.gc_cr_blocks_received, 0)) over () ratio from stats$seg_stat e , stats$seg_stat b 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.ts#(+) = e.ts# and b.obj#(+) = e.obj# and b.dataobj#(+) = e.dataobj# and e.gc_cr_blocks_received-nvl(b.gc_cr_blocks_received,0)>0 order by cr_blocks_received desc) d where rownum <= &&top_n_segstat) r where n.dataobj# = r.dataobj# and n.obj# = r.obj# and n.ts# = r.ts# and n.dbid = r.dbid and 7 <= (select snap_level from stats$snapshot where snap_id = :bid) and :para ='YES' order by cr_blocks_received desc; -- Current Blocks Received (was Served in versions prior to 10g) set newpage 2 ttitle lef 'Segments By Current Blocks Received ' - 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> End Global Cache CU Blocks Received Threshold: ' ecub - skip 2 column cu_blocks_received heading "CU|Blocks|Recevd" format 999,999,999 select n.owner , n.tablespace_name , n.object_name , case when length(n.subobject_name) < 11 then n.subobject_name else substr(n.subobject_name,length(n.subobject_name)-9) end subobject_name , n.object_type , r.cu_blocks_received , substr(to_char(r.ratio * 100,'999.9MI'), 1, 5) ratio from stats$seg_stat_obj n , (select * from (select e.dataobj# , e.obj# , e.ts# , e.dbid , e.gc_current_blocks_received-nvl(b.gc_current_blocks_received, 0) cu_blocks_received , ratio_to_report(e.gc_current_blocks_received - nvl(b.gc_current_blocks_received, 0)) over () ratio from stats$seg_stat e , stats$seg_stat b 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.ts#(+) = e.ts# and b.obj#(+) = e.obj# and b.dataobj#(+) = e.dataobj# and e.gc_current_blocks_received-nvl(b.gc_current_blocks_received,0)>0 order by cu_blocks_received desc) d where rownum <= &&top_n_segstat) r where n.dataobj# = r.dataobj# and n.obj# = r.obj# and n.ts# = r.ts# and n.dbid = r.dbid and 7 <= (select snap_level from stats$snapshot where snap_id = :bid) and :para ='YES' order by cu_blocks_received desc; set newpage 0 -- -- Dictionary Cache ttitle lef 'Dictionary Cache Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '->"Pct Misses" should be very low (< 2% in most cases)'- skip 1 - lef '->"Final Usage" is the number of cache entries being used in End Snapshot'- skip 2; column param format a25 heading 'Cache' trunc; column gets format 999,999,990 heading 'Get|Requests'; column getm format 990.9 heading 'Pct|Miss'; column scans format 99,990 heading 'Scan|Reqs'; column scanm format 90.9 heading 'Pct|Miss'; column mods format 999,990 heading 'Mod|Reqs'; column usage format 9,999,990 heading 'Final|Usage'; 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 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 and e.gets - b.gets > 0 order by param; ttitle off; set newpage 2; column dreq format 999,999,999 heading 'GES|Requests' column dcon format 999,999,999 heading 'GES|Conflicts' column drel format 999,999,999 heading 'GES|Releases' select lower(b.parameter) param , e.dlm_requests - b.dlm_requests dreq , e.dlm_conflicts - b.dlm_conflicts dcon , e.dlm_releases - b.dlm_releases drel 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 and e.dlm_requests - b.dlm_requests > 0 and :para = 'YES' order by param; -- -- Library Cache set newpage 2; ttitle lef 'Library Cache Activity '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 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' just c; column getm format 990.9 heading 'Pct|Miss' just c; column pinm format 990.9 heading 'Pct|Miss' just c; column reloads format 9,999,990 heading 'Reloads'; column inv format 999,990 heading 'Invali-|dations'; select e.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 e.dbid = :dbid and b.dbid = e.dbid and e.instance_number = :inst_num and b.instance_number = e.instance_number and b.namespace = e.namespace and e.gets - b.gets > 0; ttitle off; set newpage 2; column dlreq format 999,999,999 heading 'GES Lock|Requests'; column dpreq format 999,999,999 heading 'GES Pin|Requests'; column dprel format 999,999,999 heading 'GES Pin|Releases'; column direq format 99,999,999 heading 'GES Inval|Requests' column dinv format 99,999,999 heading 'GES Invali-|dations'; select e.namespace , e.dlm_lock_requests - b.dlm_lock_requests dlreq , e.dlm_pin_requests - b.dlm_pin_requests dpreq , e.dlm_pin_releases - b.dlm_pin_releases dprel , e.dlm_invalidation_requests - b.dlm_invalidation_requests direq , e.dlm_invalidations - b.dlm_invalidations dinv from stats$librarycache b , stats$librarycache e where b.snap_id = :bid and e.snap_id = :eid and e.dbid = :dbid and b.dbid = e.dbid and e.instance_number = :inst_num and b.instance_number = e.instance_number and b.namespace = e.namespace and e.gets - b.gets > 0 and :para = 'YES'; set newpage 0; -- -- Miscellaneous GES RAC Statistics ttitle lef 'Global Enqueue Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 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 , e.value - b.value dif , round(e.value - b.value)/:ela ps , round(e.value - b.value)/:tran pt from stats$dlm_misc b , stats$dlm_misc 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 and e.statistic# = b.statistic# and :para = 'YES' order by b.name; -- -- CR Blocks Served Statistics (RAC) ttitle lef 'Global CR Served Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 2; set heading off; column nl format a30 newline column val format 9,999,999,999,990 select 'Statistic Total' , '------------------------------' nl , '------------------' , 'CR Block Requests ' nl , e.cr_requests - b.cr_requests val , 'CURRENT Block Requests ' nl , e.current_requests - b.current_requests val , 'Data Block Requests ' nl , e.data_requests - b.data_requests val , 'Undo Block Requests ' nl , e.undo_requests - b.undo_requests val , 'TX Block Requests ' nl , e.tx_requests - b.tx_requests val , 'Current Results ' nl , e.current_results - b.current_results val , 'Private results ' nl , e.private_results - b.private_results val , 'Zero Results ' nl , e.zero_results - b.zero_results val , 'Disk Read Results ' nl , e.disk_read_results -b.disk_read_results val , 'Fail Results ' nl , e.fail_results - b.fail_results val , 'Fairness Down Converts ' nl , e.fairness_down_converts - b.fairness_down_converts val , 'Fairness Clears ' nl , e.fairness_clears - b.fairness_clears val , 'Free GC Elements ' nl , e.free_gc_elements - b.free_gc_elements val , 'Flushes ' nl , e.flushes - b.flushes val , 'Flushes Queued ' nl , e.flushes_queued - b.flushes_queued val , 'Flush Queue Full ' nl , e.flush_queue_full - b.flush_queue_full val , 'Flush Max Time (us) ' nl , e.flush_max_time - b.flush_max_time val , 'Light Works ' nl , e.light_works - b.light_works val , 'Errors ' nl , e.errors - b.errors val from stats$cr_block_server b , stats$cr_block_server 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 and :para = 'YES'; set newpage 2; -- -- CURRENT Blocks Served Statistics (RAC) ttitle lef 'Global CURRENT Served Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - '-> Pins = CURRENT Block Pin Operations' - skip 1 - '-> Flushes = Redo Flush before CURRENT Block Served Operations' - skip 1 - '-> Writes = CURRENT Block Fusion Write Operations' - skip 2; column tot format 999,999,990 column stat newline select 'Statistic ' , ' Total' , ' % <1ms' , ' % <10ms' , '% <100ms' , ' % <1s' , ' % <10s' , '----------- ----------- -------- -------- -------- -------- --------' , 'Pins ' stat , pins tot , lpad(to_char(decode(pins,0,0,100*pin1/pins),'990.99'),8,' ') , lpad(to_char(decode(pins,0,0,100*pin10/pins),'990.99'),8,' ') , lpad(to_char(decode(pins,0,0,100*pin100/pins),'990.99'),8,' ') , lpad(to_char(decode(pins,0,0,100*pin1000/pins),'990.99'),8,' ') , lpad(to_char(decode(pins,0,0,100*pin10000/pins),'990.99'),8,' ') , 'Flushes ' stat , flushes tot , lpad(to_char(decode(flushes,0,0,100*flush1/flushes),'990.99'),8,' ') , lpad(to_char(decode(flushes,0,0,100*flush10/flushes),'990.99'),8,' ') , lpad(to_char(decode(flushes,0,0,100*flush100/flushes),'990.99'),8,' ') , lpad(to_char(decode(flushes,0,0,100*flush1000/flushes),'990.99'),8,' ') , lpad(to_char(decode(flushes,0,0,100*flush10000/flushes),'990.99'),8,' ') , 'Writes ' stat , writes tot , lpad(to_char(decode(writes,0,0,100*write1/writes),'990.99'),8,' ') , lpad(to_char(decode(writes,0,0,100*write10/writes),'990.99'),8,' ') , lpad(to_char(decode(writes,0,0,100*write100/writes),'990.99'),8,' ') , lpad(to_char(decode(writes,0,0,100*write1000/writes),'990.99'),8,' ') , lpad(to_char(decode(writes,0,0,100*write10000/writes),'990.99'),8,' ') from (select (e.pin1+e.pin10+e.pin100+e.pin1000+e.pin10000 - (b.pin1+b.pin10+b.pin100+b.pin1000+b.pin10000) ) pins , e.pin1 - b.pin1 pin1 , e.pin10 - b.pin10 pin10 , e.pin100 - b.pin100 pin100 , e.pin1000 - b.pin1000 pin1000 , e.pin10000 - b.pin10000 pin10000 , (e.flush1+e.flush10+e.flush100+e.flush1000+e.flush10000 - (b.flush1+b.flush10+b.flush100+b.flush1000+b.flush10000) ) flushes , e.flush1 - b.flush1 flush1 , e.flush10 - b.flush10 flush10 , e.flush100 - b.flush100 flush100 , e.flush1000 - b.flush1000 flush1000 , e.flush10000 - b.flush10000 flush10000 , (e.write1+e.write10+e.write100+e.write1000+e.write10000 - (b.write1+b.write10+b.write100+b.write1000+b.write10000) ) writes , e.write1 - b.write1 write1 , e.write10 - b.write10 write10 , e.write100 - b.write100 write100 , e.write1000 - b.write1000 write1000 , e.write10000 - b.write10000 write10000 from stats$current_block_server b , stats$current_block_server 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 and :para = 'YES'); -- -- Cache Transfer Statistics (RAC) ttitle lef 'Global Cache Transfer Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> Immediate (Immed) - Block Transfer NOT impacted by Remote Processing Delays' - skip 1 - lef ' Busy (Busy) - Block Transfer impacted by Remote Contention' - skip 1 - lef ' Congested (Congst) - Block Transfer impacted by Remote System Load' - skip 1 - '-> ordered by CR + Current Blocks Received desc' - skip 2 - ' -------------- CR ------------- ----------- Current -----------'; set heading on; column inst format 990 heading 'Inst|No' column class format a8 heading 'Block|Class' trunc column totcr format 99,999,990 heading 'Blocks|Received' column totcu format 99,999,990 heading 'Blocks|Received' column blkimm format 999.9 heading '%|Immed' column blkbus format 999.9 heading '%|Busy' column blkcgt format 999.9 heading '%|Congst' -- -- Transfer Cache Statistics detailed per instance -- Report only if define variable cache_xfer_per_instance = 'Y' with instance_cache_transfer as ( select snap_id , instance , case when class in ('data block', 'undo header', 'undo block') then class else 'others' end as class , sum(cr_block) cr_block , sum(cr_busy) cr_busy , sum(cr_congested) cr_congested , sum(current_block) current_block , sum(current_busy) current_busy , sum(current_congested) current_congested , sum(cr_block) + sum(cr_busy) + sum(cr_congested) totcr , sum(current_block) + sum(current_busy) + sum(current_congested) totcu from stats$instance_cache_transfer where instance_number = :inst_num and dbid = :dbid and '&&cache_xfer_per_instance' = 'Y' and :para = 'YES' group by snap_id , instance , case when class in ('data block', 'undo header', 'undo block') then class else 'others' end) select e.instance inst , e.class class , e.totcr - nvl(b.totcr , 0) totcr , decode(e.totcr-nvl(b.totcr, 0), 0, to_number(NULL), (e.cr_block-nvl(b.cr_block, 0))*100/(e.totcr-nvl(b.totcr, 0))) blkimm , decode(e.totcr-nvl(b.totcr, 0), 0, to_number(NULL), (e.cr_busy -nvl(b.cr_busy, 0))*100/(e.totcr-nvl(b.totcr, 0))) blkbus , decode(e.totcr-nvl(b.totcr,0),0,to_number(NULL),(e.cr_congested-nvl(b.cr_congested, 0))*100/(e.totcr-nvl(b.totcr,0))) blkcgt , e.totcu - nvl(b.totcu , 0) totcu , decode(e.totcu-nvl(b.totcu, 0), 0, to_number(NULL), (e.current_block-nvl(b.current_block, 0))*100/(e.totcu-nvl(b.totcu, 0))) blkimm , decode(e.totcu-nvl(b.totcu, 0), 0, to_number(NULL), (e.current_busy -nvl(b.current_busy, 0))*100/(e.totcu-nvl(b.totcu, 0))) blkbus , decode(e.totcu-nvl(b.totcu,0),0,to_number(NULL),(e.current_congested-nvl(b.current_congested, 0))*100/(e.totcu-nvl(b.totcu,0))) blkcgt from (select * from instance_cache_transfer where snap_id = :bid) b , (select * from instance_cache_transfer where snap_id = :eid) e where b.class(+) = e.class and b.instance(+) = e.instance and e.totcr + e.totcu - nvl(b.totcr, 0) - nvl(b.totcu, 0) > 0 order by totcr + totcu desc; -- -- Transfer Cache Statistics aggregated per class -- Report only if define variable cache_xfer_per_instance = 'N' column class format a12 with class_cache_transfer as ( select snap_id , case when class in ('data block', 'undo header', 'undo block') then class else 'others' end as class , sum(cr_block) cr_block , sum(cr_busy) cr_busy , sum(cr_congested) cr_congested , sum(current_block) current_block , sum(current_busy) current_busy , sum(current_congested) current_congested , sum(cr_block) + sum(cr_busy) + sum(cr_congested) totcr , sum(current_block) + sum(current_busy) + sum(current_congested) totcu from stats$instance_cache_transfer where instance_number = :inst_num and dbid = :dbid and '&&cache_xfer_per_instance' = 'N' and :para = 'YES' group by snap_id , case when class in ('data block', 'undo header', 'undo block') then class else 'others' end) select e.class , e.totcr - nvl(b.totcr , 0) totcr , decode(e.totcr-nvl(b.totcr, 0), 0, to_number(NULL), (e.cr_block-nvl(b.cr_block, 0))*100/(e.totcr-nvl(b.totcr, 0))) blkimm , decode(e.totcr-nvl(b.totcr, 0), 0, to_number(NULL), (e.cr_busy-nvl(b.cr_busy, 0))*100/(e.totcr-nvl(b.totcr, 0))) blkbus , decode(e.totcr-nvl(b.totcr,0),0,to_number(NULL),(e.cr_congested-nvl(b.cr_congested, 0))*100/(e.totcr-nvl(b.totcr,0))) blkcgt , e.totcu - nvl(b.totcu , 0) totcu , decode(e.totcu-nvl(b.totcu, 0), 0, to_number(NULL), (e.current_block-nvl(b.current_block, 0))*100/(e.totcu-nvl(b.totcu, 0))) blkimm , decode(e.totcu-nvl(b.totcu, 0), 0, to_number(NULL), (e.current_busy-nvl(b.current_busy, 0))*100/(e.totcu-nvl(b.totcu, 0))) blkbus , decode(e.totcu-nvl(b.totcu,0),0,to_number(NULL),(e.current_congested-nvl(b.current_congested, 0))*100/(e.totcu-nvl(b.totcu,0))) blkcgt from (select * from class_cache_transfer where snap_id = :bid) b , (select * from class_cache_transfer where snap_id = :eid) e where b.class(+) = e.class and (e.totcr + e.totcu - nvl(b.totcr, 0) - nvl(b.totcu, 0)) > 0 order by totcr + totcu desc; set newpage 0; -- -- Interconnect Ping Statistics ttitle lef 'Interconnect Ping Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> Latency of roundtrip of a message from this instance to other instances.' - skip 1 - lef '-> Average and standard deviation are in miliseconds' - skip 1 - lef '-> The latency of messages from the instance to itself is used as ' - skip 1 - lef ' control, since message latency can include wait for CPU' - skip 2 - ' ---------- 500 bytes --------------- ------------- 8 Kbytes --------------'; column target_instance format 9999 heading 'Target|Inst' column iter_500b format 9,999,999 heading 'Ping Count' column wait_500b format 999,999 heading 'Time (s)' column av500b format 9999.9 heading 'Avg|Time(ms)' column sd500b format 999.9 heading 'Std|Dev' column iter_8k format 9,999,999 heading 'Ping Count' column wait_8k format 999,999 heading 'Time (s)' column av8k format 9999.9 heading 'Avg|Time(ms)' column sd8k format 999.9 heading 'Std|Dev' select e.target_instance , e.iter_500b - nvl(b.iter_500b,0) cnt500b , (e.wait_500b - nvl(b.wait_500b,0))/&ustos wait500b , decode( (e.iter_500b - nvl(b.iter_500b,0)), 0, to_number(null) , (e.wait_500b - nvl(b.wait_500b,0)) / (e.iter_500b - nvl(b.iter_500b,0))/&ustoms ) av500b , decode( e.iter_500b - nvl(b.iter_500b,0), 0, to_number(null) , SQRT( greatest( ( 1000*(e.waitsq_500b - nvl(b.waitsq_500b,0)) / greatest(e.iter_500b - nvl(b.iter_500b,0),1) ) - ( ((e.wait_500b - nvl(b.wait_500b,0))/(e.iter_500b - nvl(b.iter_500b,0))) *((e.wait_500b - nvl(b.wait_500b,0))/(e.iter_500b - nvl(b.iter_500b,0))) ) , 0 ))/1000 ) sd500b , e.iter_8k - nvl(b.iter_8k,0) cnt8k , (e.wait_8k - nvl(b.wait_8k,0))/&ustos wait8k , decode( (e.iter_8k - nvl(b.iter_8k,0)), 0, to_number(null) , (e.wait_8k - nvl(b.wait_8k,0)) / (e.iter_8k - nvl(b.iter_8k,0))/&ustoms ) av8k , decode( e.iter_8k - nvl(b.iter_8k,0), 0, to_number(null) , SQRT( greatest( ( 1000*(e.waitsq_8k - nvl(b.waitsq_8k,0)) / greatest(e.iter_8k - nvl(b.iter_8k,0),1) ) - ( ((e.wait_8k - nvl(b.wait_8k,0))/(e.iter_8k - nvl(b.iter_8k,0))) *((e.wait_8k - nvl(b.wait_8k,0))/(e.iter_8k - nvl(b.iter_8k,0))) ) ,0))/1000 ) sd8k from stats$interconnect_pings e , stats$interconnect_pings b 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 and b.instance_number(+) = e.instance_number and b.dbid(+) = e.dbid and b.target_instance(+) = e.target_instance and :para = 'YES' order by target_instance; -- -- Remastering Stats set heading off; ttitle lef 'Dynamic Remastering Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 2; column numX format 9,999,999,990 column numXX format 99,999,990.9 select ' Remaster Operations:' ch25n , e.remaster_ops - nvl(b.remaster_ops, 0) numX , ' Remaster Time(s):' ch20 , (e.remaster_time - nvl(b.remaster_time, 0))/&cstos numXX , ' Remastered Objects:' ch25n , e.remastered_objects - nvl(b.remastered_objects, 0) numX , ' Quiesce Time(s):' ch20 , (e.quiesce_time - nvl(b.quiesce_time, 0))/&cstos numXX , ' Affinity Objects (Beg):' ch25n , b.current_objects numX , ' Freeze Time(s):' ch20 , (e.freeze_time - nvl(b.freeze_time, 0))/&cstos numXX , ' Affinity Objects (End):' ch25n , e.current_objects numX , ' Cleanup Time(s):' ch20 , (e.cleanup_time - nvl(b.cleanup_time, 0))/&cstos numXX , ' Replayed Locks Sent:' ch25n , e.replayed_locks_sent - nvl(b.replayed_locks_sent, 0) numX , ' Replay Time(s):' ch20 , (e.replay_time - nvl(b.replay_time, 0))/&cstos numXX , ' Replayed Locks Recvd:' ch25n , e.replayed_locks_received - nvl(b.replayed_locks_received, 0) numX , ' Fixwrite Time(s):' ch20 , (e.fixwrite_time - nvl(b.fixwrite_time, 0))/&cstos numXX , ' Resources Cleaned:' ch25n , e.resources_cleaned - nvl(b.resources_cleaned, 0) numX , ' Sync Time(s):' ch20 , (e.sync_time - nvl(b.sync_time, 0))/&cstos numXX from stats$dynamic_remaster_stats b , stats$dynamic_remaster_stats e where b.snap_id(+) = :bid and e.snap_id = :eid and e.dbid = :dbid and b.dbid(+) = e.dbid and e.instance_number = :inst_num and b.instance_number(+) = e.instance_number and e.remaster_ops - nvl(b.remaster_ops, 0) > 0; set heading on; -- -- Streams ttitle lef 'Streams Capture '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> * indicates Capture process (re)started between Begin/End snaps' - skip 1 - lef '-> Top &&streams_top_n ordered by Messages Queued desc' - skip 2; col capture_name format a20 trunc heading 'Capture Name' col a heading '*' col capture_rate format 9,999 heading 'Captured|Msg/s' just c col enqueue_rate format 9,999 heading 'Enq|Msg/s' just c col pct_capture format 999 heading 'Capt|Time %' just c col pct_lcr format 999 heading 'Msg|Creatn|Time %' just c col pct_rule format 999 heading 'Rule|Time %' just c col pct_enqueue format 999 heading 'Enq|Time %' just c col pct_redo_wait format 999 heading 'Redo|Wait|Time %' just c col pct_pause format 999 heading 'Pause|Time %' just c select capture_name , a , total_messages_captured/:ela capture_rate , total_messages_enqueued/:ela enqueue_rate , elapsed_capture_time / ( elapsed_capture_time + elapsed_lcr_time + elapsed_rule_time + elapsed_enqueue_time + elapsed_redo_wait_time + elapsed_pause_time + .0000001 ) * 100 pct_capture , elapsed_lcr_time / (elapsed_capture_time + elapsed_lcr_time + elapsed_rule_time + elapsed_enqueue_time + elapsed_redo_wait_time + elapsed_pause_time + .0000001 ) * 100 pct_lcr , elapsed_rule_time / (elapsed_capture_time + elapsed_lcr_time + elapsed_rule_time + elapsed_enqueue_time + elapsed_redo_wait_time + elapsed_pause_time + .0000001) * 100 pct_rule , elapsed_enqueue_time / (elapsed_capture_time + elapsed_lcr_time + elapsed_rule_time + elapsed_enqueue_time + elapsed_redo_wait_time + elapsed_pause_time + .0000001) * 100 pct_enqueue , elapsed_redo_wait_time / (elapsed_capture_time + elapsed_lcr_time + elapsed_rule_time + elapsed_enqueue_time + elapsed_redo_wait_time + elapsed_pause_time + .0000001) * 100 pct_redo_wait , elapsed_pause_time / (elapsed_capture_time + elapsed_lcr_time + elapsed_rule_time + elapsed_enqueue_time + elapsed_redo_wait_time + elapsed_pause_time + .0000001) * 100 pct_pause from (select e.capture_name , decode( e.startup_time, b.startup_time, null, '*') a , e.total_messages_captured - nvl(b.total_messages_captured, 0) total_messages_captured , e.total_messages_enqueued - nvl(b.total_messages_enqueued, 0) total_messages_enqueued , e.elapsed_capture_time - nvl(b.elapsed_capture_time ,0) elapsed_capture_time , e.elapsed_lcr_time - nvl(b.elapsed_lcr_time,0) elapsed_lcr_time , e.elapsed_rule_time - nvl(b.elapsed_rule_time, 0) elapsed_rule_time , e.elapsed_enqueue_time - nvl(b.elapsed_enqueue_time,0) elapsed_enqueue_time , e.elapsed_redo_wait_time - nvl(b.elapsed_redo_wait_time,0) elapsed_redo_wait_time , e.elapsed_pause_time - nvl(b.elapsed_pause_time,0) elapsed_pause_time from stats$streams_capture b , stats$streams_capture e where b.snap_id (+)= :bid and e.snap_id = :eid and e.dbid = :dbid and b.dbid (+)= e.dbid and e.instance_number = :inst_num and b.instance_number (+)= e.instance_number and b.capture_name (+)= e.capture_name and b.startup_time (+)= e.startup_time order by e.total_messages_enqueued desc ) where rownum <= &&streams_top_n; set newpage 2; ttitle lef 'Propagation Sender '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> * indicates Sender process (re)started between Begin/End snaps' - skip 1 - lef '-> Top &&streams_top_n ordered by Messages desc' - skip 2; col qname format a22 trunc heading 'Queue' col a heading '*' col destination format a22 trunc heading 'Destination' col msgs_per_sec format 99,999 heading 'Msg/s' just c col kbyte_per_sec format 99,999 heading 'KB/s' just c col pct_dequeue format 999 heading 'Deq|Time|%' just c col pct_pickle format 999 heading 'Pickle|Time|%' just c col pct_propagation format 999 heading 'Prop|Time|%' just c select qname , dblink destination , a , total_msgs/:ela msgs_per_sec , total_bytes/1024/:ela kbyte_per_sec , elapsed_dequeue_time / (elapsed_dequeue_time + elapsed_pickle_time + elapsed_propagation_time + .0000001 ) * 100 pct_dequeue , elapsed_pickle_time / (elapsed_dequeue_time + elapsed_pickle_time + elapsed_propagation_time + .0000001 ) * 100 pct_pickle , elapsed_propagation_time / (elapsed_dequeue_time + elapsed_pickle_time + elapsed_propagation_time + .0000001 ) * 100 pct_propagation from (select e.queue_schema||'.'||e.queue_name qname , e.dblink dblink , decode(e.startup_time, b.startup_time, null, '*') a , e.total_msgs - nvl(b.total_msgs, 0) total_msgs , e.total_bytes - nvl(b.total_bytes, 0) total_bytes , e.elapsed_dequeue_time - nvl(b.elapsed_dequeue_time ,0) elapsed_dequeue_time , e.elapsed_pickle_time - nvl(b.elapsed_pickle_time,0) elapsed_pickle_time , e.elapsed_propagation_time - nvl(b.elapsed_propagation_time, 0) elapsed_propagation_time from stats$propagation_sender b , stats$propagation_sender e where b.snap_id (+) = :bid and e.snap_id = :eid and e.dbid = :dbid and b.dbid (+) = e.dbid and e.instance_number = :inst_num and b.instance_number (+) = e.instance_number and b.queue_schema (+) = e.queue_schema and b.queue_name (+) = e.queue_name and b.dblink (+) = e.dblink and b.dst_queue_schema(+) = e.dst_queue_schema and b.dst_queue_name (+) = e.dst_queue_name and b.startup_time (+) = e.startup_time order by e.total_msgs desc ) where rownum <= &&streams_top_n; set newpage 0; ttitle lef 'Propagation Receiver '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> * indicates Receiver process (re)started between Begin/End snaps' - skip 1 - lef '-> Top &&streams_top_n ordered by Source Queue Name, Source DB' - skip 2; col src_queue_name format a25 heading 'Source|Queue' col src_dbname format a35 heading 'Source|DB Name' col a heading '*' col pct_unpickle format 99 heading 'Un-|pickle|Time %' just c col pct_rule format 99 heading 'Rule|Time|%' just c col pct_enqueue format 99 heading 'Enq|Time|%' just c select src_queue_name , src_dbname , a , elapsed_unpickle_time / (elapsed_unpickle_time + elapsed_rule_time + elapsed_enqueue_time + + .0000001 ) * 100 pct_unpickle , elapsed_rule_time / (elapsed_unpickle_time + elapsed_rule_time + elapsed_enqueue_time + + .0000001 ) * 100 pct_rule , elapsed_enqueue_time / (elapsed_unpickle_time + elapsed_rule_time + elapsed_enqueue_time + + .0000001 ) * 100 pct_enqueue from (select e.src_queue_schema || '.'|| e.src_queue_name src_queue_name , decode(e.src_dbname, '-', null, e.src_dbname) src_dbname , decode(e.startup_time, b.startup_time, null, '*') a , e.elapsed_unpickle_time - nvl(b.elapsed_unpickle_time,0) elapsed_unpickle_time , e.elapsed_rule_time - nvl(b.elapsed_rule_time, 0) elapsed_rule_time , e.elapsed_enqueue_time - nvl(b.elapsed_enqueue_time, 0) elapsed_enqueue_time from stats$propagation_receiver b , stats$propagation_receiver e where b.snap_id (+) = :bid and e.snap_id = :eid and e.dbid = :dbid and b.dbid (+) = e.dbid and e.instance_number = :inst_num and b.instance_number (+) = e.instance_number and b.src_queue_schema(+) = e.src_queue_schema and b.src_queue_name (+) = e.src_queue_name and b.src_dbname (+) = e.src_dbname and b.dst_queue_schema(+) = e.dst_queue_schema and b.dst_queue_name (+) = e.dst_queue_name and b.startup_time (+) = e.startup_time order by e.src_queue_name, e.src_dbname ) where rownum <= &&streams_top_n; set newpage 2; ttitle lef 'Streams Apply '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> * indicates Apply process (re)started between Begin/End snaps' - skip 1 - lef '-> Top &&streams_top_n ordered by Apply Transactions desc' - skip 2; col apply_name format a19 trunc heading 'Apply Name' col a heading '*' col rate_received_txn format 9999 heading 'Rcv|Tx/s' just c col rate_applied_txn format 9999 heading 'Apply|Tx/s' just c col pct_wait_deps format 999 heading 'Wait|Deps|%' just c col pct_wait_commits format 999 heading 'Wait|Comit|%' just c col schedule_rate format 9999 heading 'Sched|Tx/s' just c col reader_rate_deq format 9999 heading 'Read|Deq|Msg/s' just c col reader_rate_sched format 99999 heading 'Read|Sched|Msg/s' just c col server_apply_rate format 9999 heading 'Apply|Rate|Msg/s' just c col server_pct_apply format 999 heading 'Apply|Time|%' just c col server_pct_dequeue format 999 heading 'Deq|Time|%' just c select apply_name , a , coord_total_received/:ela rate_received_txn , coord_total_applied/:ela rate_applied_txn , decode( coord_total_applied, 0, 0 , coord_total_wait_deps/coord_total_applied) pct_wait_deps , decode( coord_total_applied, 0, 0 , coord_total_wait_commits/coord_total_applied) pct_wait_commits , decode( coord_elapsed_schedule_time, 0, 0 , coord_total_applied/coord_elapsed_schedule_time) * 100 schedule_rate , reader_total_messages_dequeued/:ela reader_rate_deq , decode( reader_elapsed_schedule_time, 0, 0 , reader_total_messages_dequeued/reader_elapsed_schedule_time) reader_rate_sched , server_total_messages_applied/:ela server_apply_rate , server_elapsed_apply_time / (server_elapsed_dequeue_time+server_elapsed_apply_time+1) * 100 server_pct_apply , server_elapsed_dequeue_time / (server_elapsed_dequeue_time+server_elapsed_apply_time+1) * 100 server_pct_dequeue from (select e.apply_name , decode( e.startup_time, b.startup_time, null, '*') a , e.reader_total_messages_dequeued - nvl(b.reader_total_messages_dequeued, 0) reader_total_messages_dequeued , e.reader_elapsed_dequeue_time - nvl(b.reader_elapsed_dequeue_time, 0) reader_elapsed_dequeue_time , e.reader_elapsed_schedule_time - nvl(b.reader_elapsed_schedule_time, 0) reader_elapsed_schedule_time , e.coord_total_received - nvl(b.coord_total_received, 0) coord_total_received , e.coord_total_applied - nvl(b.coord_total_applied, 0) coord_total_applied , e.coord_total_wait_deps - nvl(b.coord_total_wait_deps, 0) coord_total_wait_deps , e.coord_total_wait_commits - nvl(b.coord_total_wait_commits, 0) coord_total_wait_commits , e.coord_elapsed_schedule_time - nvl(b.coord_elapsed_schedule_time, 0) coord_elapsed_schedule_time , e.server_total_messages_applied - nvl(b.server_total_messages_applied, 0) server_total_messages_applied , e.server_elapsed_dequeue_time - nvl(b.server_elapsed_dequeue_time, 0) server_elapsed_dequeue_time , e.server_elapsed_apply_time - nvl(b.server_elapsed_apply_time, 0) server_elapsed_apply_time from stats$streams_apply_sum b , stats$streams_apply_sum e where b.snap_id (+)= :bid and e.snap_id = :eid and e.dbid = :dbid and b.dbid (+)= e.dbid and e.instance_number = :inst_num and b.instance_number (+)= e.instance_number and b.apply_name (+)= e.apply_name and b.startup_time (+)= e.startup_time order by e.coord_total_applied desc ) where rownum <= &&streams_top_n; set newpage 0; ttitle lef 'Buffered Queues '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> * indicates Buffered Queue activity (re)started between Begin/End snaps' - skip 1 - lef '-> Top &&streams_top_n ordered by Queued Messages desc' - skip 2; col qname format a54 trunc heading 'Queue' col a heading '*' col enq_rate format 99,999 heading 'Enq|Msg/s' just c col deq_rate format 99,999 heading 'Deq|Msg/s' just c col spill_rate format 99,999 heading 'Spill|Msg/s' just c select queue_schema||'.'||queue_name qname , a , cnum_msgs/:ela enq_rate , (cnum_msgs-num_msgs)/:ela deq_rate , cspill_msgs/:ela spill_rate from (select e.queue_schema , e.queue_name , decode( e.startup_time, b.startup_time, null, '*') a , e.num_msgs - nvl(b.num_msgs,0) num_msgs , e.cnum_msgs - nvl(b.cnum_msgs,0) cnum_msgs , e.cspill_msgs - nvl(b.cspill_msgs,0) cspill_msgs from stats$buffered_queues b , stats$buffered_queues e where b.snap_id (+)= :bid and e.snap_id = :eid and e.dbid = :dbid and b.dbid (+)= e.dbid and e.instance_number = :inst_num and b.instance_number (+)= e.instance_number and b.queue_schema (+)= e.queue_schema and b.queue_name (+)= e.queue_name and b.startup_time (+)= e.startup_time order by e.cnum_msgs desc ) where rownum <= &&streams_top_n; ttitle lef 'Buffered Queue Subscribers '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> * indicates Buffered Subscriber activity (re)started between Begin/End snaps' - skip 1 - lef '-> Top &&streams_top_n ordered by Queued Messages desc' - skip 2; col subscriber format a34 trunc heading 'Subscriber' col qname format a25 trunc heading 'Queue' col a heading '*' col enq_rate format 99 heading 'Enq|Msg/s' just c col deq_rate format 99 heading 'Deq|Msg/s' just c col spill_rate format 99 heading 'Spill|Msg/s' just c select decode( subscriber_type, 'PROXY', 'PROXY: '||subscriber_address , subscriber_name) subscriber , queue_schema||'.'||queue_name qname , a , cnum_msgs/:ela enq_rate , (cnum_msgs - num_msgs)/:ela deq_rate , total_spilled_msg/:ela spill_rate from (select e.subscriber_name , e.queue_schema , e.queue_name , decode( e.startup_time, b.startup_time, null, '*') a , e.subscriber_type , e.subscriber_address , e.num_msgs - nvl(b.num_msgs,0) num_msgs , e.cnum_msgs - nvl(b.cnum_msgs,0) cnum_msgs , e.total_spilled_msg - nvl(b.total_spilled_msg,0) total_spilled_msg from stats$buffered_subscribers b , stats$buffered_subscribers e where b.snap_id (+)= :bid and e.snap_id = :eid and e.dbid = :dbid and b.dbid (+)= e.dbid and e.instance_number = :inst_num and b.instance_number (+)= e.instance_number and b.queue_schema (+)= e.queue_schema and b.queue_name (+)= e.queue_name and b.subscriber_id (+)= e.subscriber_id and b.startup_time (+)= e.startup_time order by e.cnum_msgs desc ) where rownum <= &&streams_top_n; ttitle lef 'Rule Sets '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> * indicates Rule Set activity (re)started between Begin/End snaps' - skip 1 - lef '-> Top &&streams_top_n ordered by Evaluations desc ' - skip 2; col rule_name format a35 trunc heading 'Rule' col restart format a1 heading '*' col eval_rate format 999,999,999 heading 'Eval/sec' col reload_rate format 9,999 heading 'Reloads/sec' col pct_sql_free format 999 heading 'No-SQL|Eval %' just c col pct_sql format 999 heading 'SQL|Eval %' just c select owner||'.'||name rule_name , restart , decode( elapsed_time, 0, 0 , evaluations/elapsed_time * 100) eval_rate , reloads/:ela reload_rate , sql_free_evaluations / (sql_free_evaluations + sql_executions + .0000001) * 100 pct_sql_free , sql_executions / (sql_free_evaluations + sql_executions + .0000001 ) * 100 pct_sql from (select e.owner , e.name , decode( e.startup_time, b.startup_time, null, '*') restart , e.cpu_time - nvl(b.cpu_time,0) cpu_time , e.elapsed_time - nvl(b.elapsed_time,0) elapsed_time , e.evaluations - nvl(b.evaluations,0) evaluations , e.sql_free_evaluations - nvl(b.sql_free_evaluations,0) sql_free_evaluations , e.sql_executions - nvl(b.sql_executions,0) sql_executions , e.reloads - nvl(b.reloads,0) reloads from stats$rule_set b , stats$rule_set e where b.snap_id (+)= :bid and e.snap_id = :eid and e.dbid = :dbid and b.dbid (+)= e.dbid and e.instance_number = :inst_num and b.instance_number (+)= e.instance_number and b.owner (+)= e.owner and b.name (+)= e.name and b.startup_time (+)= e.startup_time order by e.evaluations desc ) where rownum <= &&streams_top_n; -- -- Streams Pool Advisory ttitle lef 'Streams Pool Advisory '- 'DB/Inst: ' db_name '/' inst_name ' '- 'End Snap: ' format 99999999 end_snap - skip 2; column spsfe format 9,999,990.9 heading 'Streams Pool|Size (M)'; column spsf format 99.0 heading 'Streams Pool|Size Factor'; column esc format 999,990 heading 'Est Spill|Count'; column est format 999,990 heading 'Est Spill|Time (s)'; column eusc format 999,990 heading 'Est Unspill|Count'; column eust format 999,990 heading 'Est Unspill|Time (s)'; select streams_pool_size_for_estimate spsfe , streams_pool_size_factor spsf , estd_spill_count esc , estd_spill_time est , estd_unspill_count eusc , estd_unspill_time eust from stats$streams_pool_advice where snap_id = :eid and dbid = :dbid and instance_number = :inst_num and streams_pool_size_for_estimate > 0 order by streams_pool_size_for_estimate; -- Shared Pool Advisory ttitle lef 'Shared Pool Advisory '- 'DB/Inst: ' db_name '/' inst_name ' '- 'End Snap: ' format 99999999 end_snap - skip 1 - lef '-> SP: Shared Pool Est LC: Estimated Library Cache Factr: Factor' - skip 1 - lef '-> Note there is often a 1:Many correlation between a single logical object' - skip 1 - lef ' in the Library Cache, and the physical number of memory objects associated' - skip 1 - lef ' with it. Therefore comparing the number of Lib Cache objects (e.g. in ' - skip 1 - lef ' v$librarycache), with the number of Lib Cache Memory Objects is invalid' - skip 2; column spsfe format 9,999,999 heading 'Shared|Pool|Size (M)'; column spsf format 99.0 heading 'SP|Size|Factr'; column elcs format 999,990 heading 'Est LC|Size|(M)'; column elcmo format 999,999,999 heading 'Est LC|Mem Obj'; column elcts format 99,999 heading 'Est LC|Time|Saved|(s)'; column elctsf format 99.0 heading 'Est LC|Time|Saved|Factr'; column elclt format 99,999 heading 'Est LC|Load|Time|(s)'; column elcltf format 99.0 heading 'Est LC|Load|Time|Factr'; column elcmoh format 99,999,999 heading 'Est LC|Mem|Obj Hits'; select shared_pool_size_for_estimate spsfe , shared_pool_size_factor spsf , estd_lc_size elcs , estd_lc_memory_objects elcmo , estd_lc_time_saved elcts , estd_lc_time_saved_factor elctsf , estd_lc_load_time elclt , estd_lc_load_time_factor elcltf , estd_lc_memory_object_hits elcmoh from stats$shared_pool_advice where snap_id = :eid and dbid = :dbid and instance_number = :inst_num order by shared_pool_size_for_estimate; -- -- Java Pool Advisory set newpage 2; ttitle lef 'Java Pool Advisory '- 'DB/Inst: ' db_name '/' inst_name ' '- 'End Snap: ' format 99999999 end_snap - skip 2; column jpsfe format 9,999,999 heading 'Java|Pool|Size(M)'; column jpsf format 99.0 heading 'JP|Size|Factr'; column elcs format 999,990 heading 'Est LC|Size|(M)'; column elcmo format 999,999,999 heading 'Est LC|Mem Obj'; column elcts format 99,999 heading 'Est LC|Time|Saved|(s)'; column elctsf format 99.0 heading 'Est LC|Time|Saved|Factr'; column elclt format 99,999 heading 'Est LC|Load|Time|(s)'; column elcltf format 99.0 heading 'Est LC|Load|Time|Factr'; column elcmoh format 99,999,999 heading 'Est LC|Mem|Obj Hits'; select java_pool_size_for_estimate jpsfe , java_pool_size_factor jpsf , estd_lc_size elcs , estd_lc_memory_objects elcmo , estd_lc_time_saved elcts , estd_lc_time_saved_factor elctsf , estd_lc_load_time elclt , estd_lc_load_time_factor elcltf , estd_lc_memory_object_hits elcmoh from stats$java_pool_advice where snap_id = :eid and dbid = :dbid and instance_number = :inst_num and estd_lc_memory_objects > 0 order by java_pool_size_for_estimate; -- -- SGA cache size changes ttitle lef 'Cache Size Changes '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> Not all cache size changes may be captured. Only cache changes which are' - skip 1 - lef ' evident at snapshot time are shown' - skip 2; column cache format a12 heading 'Cache' column prev_value format 999,999 heading 'Prior|Size (MB)' just c column value format 999,999 heading 'New|Size (MB)' just c colum diff format 999,999 heading 'Difference|(MB)' just c break on snap_id select snap_id , decode(name, '__db_cache_size', 'Buffer Cache' , '__shared_pool_size', 'Shared Pool' , '__large_pool_size', 'Large Pool' , '__java_pool_size', 'Java Pool' , '__streams_pool_size', 'Streams Pool') cache , prev_value , value , (value - prev_value) diff from (select snap_id, name , to_number(value)/&btomb value , to_number((lag(value, 1, null) over (order by name, snap_id)))/&btomb prev_value , (lag(name, 1, null) over (order by name, snap_id)) prev_name from stats$parameter where snap_id between :bid and :eid and dbid = :dbid and instance_number = :inst_num and name in ('__shared_pool_size', '__db_cache_size' ,'__large_pool_size' , '__java_pool_size' ,'__streams_pool_size') ) where value != prev_value and name = prev_name order by snap_id, diff; clear breaks set newpage 0; ttitle lef 'SGA Target Advisory '- 'DB/Inst: ' db_name '/' inst_name ' '- 'End Snap: ' format 99999999 end_snap - skip 2; column sgatsfe format 9,999,999 heading 'SGA Target|Size (M)'; column sgasf format 99.0 heading 'SGA Size|Factor'; column edbts format 999,990 heading 'Est DB|Time (s)'; column edbtf format 99.0 heading 'Est DB|Time Factor'; column epr format 9,999,999,999 heading 'Est Physical|Reads'; select sga_size sgatsfe , sga_size_factor sgasf , estd_db_time edbts , estd_db_time_factor edbtf , estd_physical_reads epr from stats$sga_target_advice where snap_id = :eid and dbid = :dbid and instance_number = :inst_num order by sga_size; -- -- SGA column name format a30 heading 'SGA regions'; column bval format 999,999,999,999,990 heading 'Begin Size (Bytes)'; column eval format 999,999,999,999,990 heading 'End Size (Bytes)|(if different)'; break on report; compute sum of bval on report; compute sum of eval on report; ttitle lef 'SGA Memory Summary '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 2; select e.name , b.value bval , decode(b.value, e.value, to_number(null), e.value) eval from stats$sga b , stats$sga e where e.snap_id = :eid and e.dbid = :dbid and e.instance_number = :inst_num and b.snap_id = :bid and b.dbid = :dbid and b.instance_number = :inst_num and b.name = e.name order by name; clear break compute; set newpage 2; ttitle lef 'SGA breakdown difference '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 1 - lef '-> Top &sgastat_top_n rows by size, ordered by Pool, Name (note rows with null values for' - skip 1 - lef ' Pool column, or Names showing free memory are always shown)' - skip 1 - lef '-> Null value for Begin MB or End MB indicates the size of that Pool/Name was' - skip 1 - lef ' insignificant, or zero in that snapshot' - skip 2; column pool format a6 heading 'Pool' trunc ; column name format a30 heading 'Name'; column snap1 format 999,999,999.9 heading 'Begin MB'; column snap2 format 999,999,999.9 heading 'End MB'; column diff format 9990.90 heading '% Diff'; -- inline views in from clause required to prevent full outer join -- from applying where clause after outer join. ANSI SQL standard -- described in (bug 3805503) select * from (select nvl(e.pool, b.pool) pool , nvl(e.name, b.name) name , b.bytes/1024/1024 snap1 , e.bytes/1024/1024 snap2 , 100*(nvl(e.bytes,0) - nvl(b.bytes,0))/nvl(b.bytes,1) diff from (select * from stats$sgastat where snap_id = :bid and dbid = :dbid and instance_number = :inst_num ) b full outer join (select * from stats$sgastat where snap_id = :eid and dbid = :dbid and instance_number = :inst_num ) e on b.name = e.name and nvl(b.pool, 'a') = nvl(e.pool, 'a') order by nvl(e.bytes, b.bytes) ) where pool is null or name = 'free memory' or rownum <= &&sgastat_top_n order by pool, name; set newpage 0; -- -- SQL Memory stats set heading off; ttitle lef 'SQL Memory Statistics '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 end_snap - skip 2; select ' Begin End % Diff' ch78n , ' -------------- -------------- --------------' ch78n , ' Avg Cursor Size (KB): ' ch25n, :b_total_sql_mem/&&btokb/:b_total_cursors num8c_2 , :e_total_sql_mem/&&btokb/:e_total_cursors num8c_2 , 100*( (:e_total_sql_mem/&&btokb/:e_total_cursors) - (:b_total_sql_mem/&&btokb/:b_total_cursors) ) /(:e_total_sql_mem/&&btokb/:e_total_cursors) num8c_2 , ' Cursor to Parent ratio: ' ch25n, :b_total_cursors/:b_total_sql num8c_2 , :e_total_cursors/:e_total_sql num8c_2 , 100*( (:e_total_cursors/:e_total_sql) -(:b_total_cursors/:b_total_sql) ) /(:e_total_cursors/:e_total_sql) num8c_2 , ' Total Cursors: ' ch25n, :b_total_cursors num10c , :e_total_cursors num10c , 100*( (:e_total_cursors) -(:b_total_cursors) ) /(:e_total_cursors) num8c_2 , ' Total Parents: ' ch25n, :b_total_sql num10c , :e_total_sql num10c , 100*( (:e_total_sql) -(:b_total_sql) ) /(:e_total_sql) num8c_2 from sys.dual where :b_total_cursors > 0 and :e_total_cursors > 0; set heading on; -- -- Resource Limit set newpage 2; ttitle lef 'Resource Limit Stats '- 'DB/Inst: ' db_name '/' inst_name ' '- 'End Snap: ' format 99999999 end_snap - skip 1 - lef '-> only rows with Current or Maximum Utilization > 80% of Limit are shown' - skip 1 - lef '-> ordered by resource name' - skip 2; column rname format a30 heading 'Resource Name'; column curu format 999,999,990 heading 'Current|Utilization' just c; column maxu format 999,999,990 heading 'Maximum|Utilization' just c; column inita format a10 heading 'Initial|Allocation' just c; column lim format a10 heading 'Limit' just r; select resource_name rname , current_utilization curu , max_utilization maxu , initial_allocation inita , limit_value lim from stats$resource_limit where snap_id = :eid and dbid = :dbid and instance_number = :inst_num and ( nvl(current_utilization,0)/limit_value > .8 or nvl(max_utilization,0)/limit_value > .8 ) order by rname; set newpage 0; -- -- Initialization Parameters column name format a29 heading 'Parameter Name' trunc; column bval format a33 heading 'Begin value' ; column eval format a14 heading 'End value|(if different)' just c; ttitle lef 'init.ora Parameters '- 'DB/Inst: ' db_name '/' inst_name ' '- 'Snaps: ' format 99999999 begin_snap '-' format 99999999 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 translate(e.name, '_', '#') not like '##%' 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) ) order by e.name; prompt prompt End of Report ( &report_name ) 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 dbid undefine inst_num undefine num_days undefine report_name undefine top_n_sql undefine top_pct_sql undefine top_n_events undefine top_n_segstat undefine btime undefine etime undefine num_rows_per_hash whenever sqlerror continue; -- -- End of script file;