|
|
SQL ScriptStatspack Historical Analysis Script |
|
Displays all Wait statistics for many statspack snapshots in single report. This makes for easy trend analysis of statspack reports longer time periods which can then be imported into Spreadsheet for graphing
REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved.
column inst_num heading "Inst Num" new_value inst_num format 99999; column inst_name heading "Instance" new_value inst_name format a10; column db_name heading "DB Name" new_value db_name format a10; column dbid heading "DB Id" new_value dbid format 9999999999 just c; variable dbid number; variable inst_num number; variable inst_name varchar2(20); variable db_name varchar2(20); variable l_instance_num_def number; begin /* change the instance_num if you have many instances in the same perfstat schema (e.g. RAC/OPS) */ :l_instance_num_def := 1; select dbid, instance_number, instance_name,db_name INTO :dbid, :inst_num, :inst_name, :db_name FROM STATS$DATABASE_INSTANCE WHERE instance_number = :l_instance_num_def ; end; / print dbid print db_name -- -- Top Wait Events set pages 20000 set lines 200 set trimspool on set term off set echo off set feed off create or replace function TOTAL_EVENT_TIME (db_id NUMBER, inst_num NUMBER, bid NUMBER, eid NUMBER) RETURN number is /* Returns the total amount of time waited for events for the interval specified by the begin and end snapshot id's (bid, eid) by foreground processes. This excludes idle wait events. */ cursor WAITS (i_snap_id number) is select sum(time_waited) from stats$system_event where snap_id = i_snap_id and dbid = dbid and instance_number = inst_num and event not in (select event from stats$idle_event); bwaittime number; ewaittime number; begin if not WAITS%ISOPEN then open WAITS (bid); end if; fetch WAITS into bwaittime; if WAITS%NOTFOUND then raise_application_error (-20100,'Missing start value for stats$system_event'); end if; close WAITS; if not WAITS%ISOPEN then open WAITS (eid); end if; fetch WAITS into ewaittime; if WAITS%NOTFOUND then raise_application_error (-20100,'Missing end value for stats$system_event'); end if; close WAITS; return ewaittime - bwaittime; end TOTAL_EVENT_TIME; / show errors column snap_id heading 'SnapId' format 9990; column snapt format a12 heading 'Snap Time' col idle noprint; col event format a28 heading 'Event' trunc; col waits format 999,999,990 heading 'Waits'; col time format 99,999,990 heading 'Evnt Wt Tm (cs)'; col pctwtt format 999.99 heading '% Total Wt Tm'; col wt format 9990 heading 'Avg Wt (ms)'; col stime format 999,999,990 heading 'Service Tm (cs)'; col rtime format 999,999,999,990 heading 'Tot Resp Tm Evts (cs)'; col wtime format 999,999,999,990 heading 'Tot Wt Tm Evts (cs)'; col pctov format 999.99 heading '% Resp Tm'; col pctcpu format 999.99 heading '% CPU Tm'; col pctwt format 999.99 heading '% Wait Tm'; spool sprepwaitsum.txt PROMPT NOTES PROMPT ===== PROMPT PROMPT Display the overall summary for CPU vs Wait Time. select b.snap_id ,e.snap_id , to_char(es.snap_time,'dd Mon HH24:mi') snapt , eservtime.value-bservtime.value stime , SUM(e.time_waited - b.time_waited) wtime , (eservtime.value-bservtime.value) + SUM(e.time_waited - b.time_waited) rtime , decode (eservtime.value-bservtime.value ,0,0 , 100* (eservtime.value-bservtime.value)/ ((eservtime.value-bservtime.value) + SUM(e.time_waited - b.time_waited)) ) pctcpu , decode (eservtime.value-bservtime.value ,0,0 , 100* SUM(e.time_waited - b.time_waited)/ ((eservtime.value-bservtime.value) + SUM(e.time_waited - b.time_waited)) ) pctwt from stats$system_event b , stats$system_event e , stats$idle_event i , (select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'CPU used by this session') bservtime , (select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'CPU used by this session') eservtime , stats$snapshot es where b.dbid(+) = :dbid and e.dbid = :dbid and b.instance_number (+) = :inst_num and e.instance_number = :inst_num and b.snap_id = bservtime.snap_id and e.snap_id = eservtime.snap_id and b.event(+) = e.event and e.total_waits - nvl(b.total_waits,0) > 0 and e.event not in (select event from stats$idle_event) and i.event(+) = e.event and es.dbid = e.dbid and es.instance_number = e.instance_number and es.snap_id = e.snap_id and e.snap_id = b.snap_id + 1 and to_char(es.snap_time,'DY') NOT IN ('SAT','SUN') group by b.snap_id ,e.snap_id , to_char(es.snap_time,'dd Mon HH24:mi') , eservtime.value-bservtime.value order by b.snap_id ; col cput format 999,999,990 heading 'CPU Time (s)' col pcput format 999,999,990 heading 'Parse Time (s)' col rcput format 999,999,990 heading 'Recursive Time (s)' col pctpcpu format 999.99 heading '% Par Tm'; col pctrcpu format 999.99 heading '% Rec Tm'; col pctocpu format 999.99 heading '% Oth Tm'; spool sprepwaitcpu.txt PROMPT NOTES PROMPT ===== PROMPT PROMPT Display the CPU breakdown or parse CPU, recursive CPU, other CPU PROMPT PROMPT recursive CPU - PLSQL PROMPT parse CPU - parsing SQL PROMPT other CPU - the time spent handling buffers (look for Top SQL order by buffer gets) select bs.snap_id , es.snap_id , to_char(es.snap_time,'dd Mon HH24:mi') snapt , (eservtime.value-bservtime.value)/100 cput , (epcpu.value-bpcpu.value)/100 pcput , (ercpu.value-brcpu.value)/100 rcput , decode (epcpu.value-bpcpu.value ,0,0 , 100* (epcpu.value-bpcpu.value) / (eservtime.value-bservtime.value) ) pctpcpu , decode (ercpu.value-brcpu.value ,0,0 , 100* (ercpu.value-brcpu.value) / (eservtime.value-bservtime.value) ) pctrcpu , decode ( (eservtime.value-bservtime.value)-(ercpu.value-brcpu.value)-(ercpu.value-brcpu.value) ,0,0 , 100* ((eservtime.value-bservtime.value)-(ercpu.value-brcpu.value)-(ercpu.value-brcpu.value)) / (eservtime.value-bservtime.value) ) pctocpu from (select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'CPU used by this session') bservtime , (select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'CPU used by this session') eservtime , (select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'parse time cpu') bpcpu , (select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'parse time cpu') epcpu , (select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'recursive cpu usage') brcpu , (select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'recursive cpu usage') ercpu , stats$snapshot bs , stats$snapshot es where bs.dbid(+) = :dbid and es.dbid = :dbid and bs.instance_number (+) = :inst_num and es.instance_number = :inst_num and bs.snap_id = bservtime.snap_id and es.snap_id = eservtime.snap_id and bs.snap_id = bpcpu.snap_id and es.snap_id = epcpu.snap_id and bs.snap_id = brcpu.snap_id and es.snap_id = ercpu.snap_id and es.snap_id = bs.snap_id + 1 and to_char(es.snap_time,'DY') NOT IN ('SAT','SUN') order by bs.snap_id ; spool off spool sprepwait.txt PROMPT PROMPT Notes PROMPT ===== PROMPT PROMPT The Service Time is the amount of CPU used in this snapshot PROMPT The % Total Event Wt Time = Event Wait Time / Total Wait Time Events PROMPT Tot Resp Time All Events = Service Time + Tot Wait Time Evts PROMPT The % Resp Time = Event Wait Time / Total Resp Time Events PROMPT select b.snap_id ,e.snap_id , to_char(es.snap_time,'dd Mon HH24:mi') snapt ,e.event , e.total_waits - nvl(b.total_waits,0) waits , e.time_waited - nvl(b.time_waited,0) time , decode ((e.total_waits - nvl(b.total_waits, 0)), 0, to_number(NULL), (e.time_waited - nvl(b.time_waited,0)) / (e.total_waits - nvl(b.total_waits,0))*10) wt , decode(TOTAL_EVENT_TIME(:dbid,:inst_num,b.snap_id, e.snap_id), 0, 0, 100*((e.time_waited - nvl(b.time_waited,0))/TOTAL_EVENT_TIME(:dbid,:inst_num,b.snap_id, e.snap_id))) pctwtt , decode ( (eservtime.value-bservtime.value) + TOTAL_EVENT_TIME(:dbid,:inst_num,b.snap_id, e.snap_id),0,0, 100*(e.time_waited - nvl(b.time_waited,0)) / ((eservtime.value-bservtime.value) + TOTAL_EVENT_TIME(:dbid,:inst_num,b.snap_id, e.snap_id)) ) pctov , decode(i.event, null, 0, 99) idle -- , TOTAL_EVENT_TIME(:dbid,:inst_num,b.snap_id, e.snap_id) wtime -- , (eservtime.value-bservtime.value) + TOTAL_EVENT_TIME(:dbid,:inst_num,b.snap_id, e.snap_id) rtime -- , decode (eservtime.value-bservtime.value ,0,0 -- , 100* (eservtime.value-bservtime.value)/ ((eservtime.value-bservtime.value) + TOTAL_EVENT_TIME(:dbid,:inst_num,b.snap_id, e.snap_id)) -- ) pctcpu -- , decode (eservtime.value-bservtime.value ,0,0 -- , 100* TOTAL_EVENT_TIME(:dbid,:inst_num,b.snap_id, e.snap_id)/ ((eservtime.value-bservtime.value) + TOTAL_EVENT_TIME(:dbid,:inst_num,b.snap_id, e.snap_id)) -- ) pctwt from stats$system_event b , stats$system_event e , stats$idle_event i , (select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'CPU used by this session') bservtime , (select sys.snap_id, sys.value from stats$sysstat sys where sys.dbid = :dbid and sys.instance_number = :inst_num and sys.name = 'CPU used by this session') eservtime , stats$snapshot es where b.dbid(+) = :dbid and e.dbid = :dbid and b.instance_number (+) = :inst_num and e.instance_number = :inst_num and b.snap_id = bservtime.snap_id and e.snap_id = eservtime.snap_id and b.event(+) = e.event and e.total_waits - nvl(b.total_waits,0) > 1000 and e.event not like '%timer%' and e.event not like 'rdbms ipc%' and e.event not like 'SQL*Net%' and i.event(+) = e.event and es.dbid = e.dbid and es.instance_number = e.instance_number and es.snap_id = e.snap_id and e.snap_id = b.snap_id + 1 and to_char(es.snap_time,'DY') NOT IN ('SAT','SUN') order by b.snap_id, e.time_waited - nvl(b.time_waited,0) desc ; spool off
Return to Index of SQL Scripts
General Information :
Home | Company Profile
| Contact Us | Legal
Database Support :
Proactive Support
|
Pay Per Incident Support
Expert Services :
High Availability
|
Creative Solutions
|
Training
Build Services :
Oracle Installations
|
Revive your Oracle Setup
|
Low Cost Development
Free Oracle Resources:
DBA Scripts
|
Oracle Links