Statspack Analysis

Sample Queries for analysing Statspack base tables over time.

These Queries can be used to graph details via sql developer or excel.

OS Stats over time

SELECT sn.snap_time time , nm.stat_name , os.value FROM stats$osstat os , stats$osstatname nm , stats$snapshot sn WHERE os.osstat_id = nm.osstat_id AND nm.stat_name ='LOAD' --See stats$osstatname.stat_name for other os stats. AND sn.snap_id = os.snap_id AND sn.instance_number = ( SELECT instance_number FROM v$instance ) AND os.instance_number = sn.instance_number AND sn.snap_time between '06-FEB-17' and '11-FEB-17' order by 1;

Database time (DB Time)

SELECT to_char(sn.snap_time,'YYYY-MM-DD HH24:MI:SS') time , nm.stat_name , ( LEAD( tm.value, 1 ) OVER( ORDER BY tm.snap_id ) - tm.value ) /1000000/60 AS DB_TIME FROM stats$sys_time_model tm , stats$time_model_statname nm , stats$snapshot sn WHERE tm.stat_id = nm.stat_id AND nm.stat_name = 'DB time' AND sn.snap_id = tm.snap_id AND sn.instance_number = ( SELECT instance_number FROM v$instance ) AND tm.instance_number = sn.instance_number AND sn.snap_time > sysdate -10 order by sn.snap_time;

System Event Wait(ms) over time
Edit the query to select a particular event (select distinct (event) from stats$system_event order by 1;)

select to_char(snap_time,'Mon-dd hh24:mi:ss') snap_time, event event_name, round((time_waited_ms - prv_time_waited_ms) / decode((total_waits - prv_waits), 0,null, (total_waits - prv_waits)),2) wait_average_ms from ( select ss.snap_time, se.event, se.total_waits, lag(se.total_waits,1) over (order by se.snap_id) prv_waits, se.time_waited_micro/1000 time_waited_ms, lag(se.time_waited_micro/1000,1) over (order by se.snap_id) prv_time_waited_ms from perfstat.stats$snapshot ss, perfstat.stats$system_event se where ss.snap_time > sysdate -7 and se.snap_id = ss.snap_id and se.instance_number = ss.instance_number and se.dbid = ss.dbid -- select the event you want --and se.event = 'db file sequential read' --and se.event = 'db file scattered read' and se.event = 'log file sync' ) order by snap_time

Wait Class

select sh.snap_time , en.wait_class ,sse.total_waits from stats$system_event sse, STATS$SNAPSHOT SH , v$event_name en where sh.snap_id = sse.snap_id and sh.dbid = sse.dbid and sse.event_id = en.event_id --and en.wait_class = 'System I/O' --AND sh.snap_time between '27-FEB-17' and '5-MAR-17' AND trunc(sh.snap_time) > sysdate -7 order by sh.snap_time, en.wait_class

SQL over snaps

select ss.snap_id, ss.snap_time, sql_id, executions, buffer_gets/decode(nvl(executions,0),0,1,executions) avg_lio, disk_reads/decode(nvl(executions,0),0,1,executions) avg_pio, elapsed_time/decode(nvl(executions,0),0,1,executions)/1000000 avg_elap, cpu_time/decode(nvl(executions,0),0,1,executions)/1000000avg_cpu , rows_processed/decode(nvl(executions,0),0,1,executions) rows_per_exec, application_wait_time/decode(nvl(executions,0),0,1,executions)/1000000 avg_app_wait, concurrency_wait_time/decode(nvl(executions,0),0,1,executions)/1000000 avg_con_wait, user_io_wait_time/decode(nvl(executions,0),0,1,executions)/1000000 avg_io_wait from STATS$SQL_SUMMARY sql , stats$snapshot ss where ss.snap_id = sql.snap_id and sql_id = '8dfk0qpzvdcbc' and ss.snap_time > sysdate -2;

******************************************
 keywords: stats pack statspack
******************************************
 rdbms version:
******************************************