STATSPACK vs AWR
STASPACK is the obvious choice for performance tuning when you’re not licensed for AWR. But just how do you get the most out of STATSPACK. In this article well touch on troubleshooting performance problems and conducting system wide health checks using purely STATSPACK.
At this point it’s probably good to point out that this article is just to get you started. The STATSPACK repository itself covers many wonderful areas and we obviously won’t be able to cover them all. The below tips and techniques are enough to get you started and can be easily reused in other areas of STATAPACK not covered in this article.
SQL Performance Statistics:
STATSPACK tables to use : STATS$SQL_SUMMARY, STATS$SNAPSHOT
AWR equivalent tables : DBA_HIST_SQLSTAT, DBA_HIST_SNAPSHOT
Unlike AWR the STATSPACK tables do not contain delta values. We need to use the LAG analytical function to calculate delta values. The below query extracts SQL performance metrics and calculates the delta value based on the STATAPACK snapshot intervals. Note that there are a lot more columns in the STATS$SQL_SUMMARY table other than what the below query is focusing on. The below query is only focusing on execution count and elapsed time.
select nm.snap_id,nm.snap_time,'',sql_id||'::'||hash_value as sqlexec,
nvl(decode(greatest(executions, nvl(lag(executions) over
(partition by stats$sql_summary.dbid, stats$sql_summary.instance_number,
sql_id||'::'||hash_value order by stats$sql_summary.snap_id),0)),
executions, executions - lag(executions) over
(partition by stats$sql_summary.dbid, stats$sql_summary.instance_number,
sql_id||'::'||hash_value order by stats$sql_summary.snap_id),executions), 0) EXECS,
nvl(decode(greatest(elapsed_time, nvl(lag(elapsed_time) over
(partition by stats$sql_summary.dbid, stats$sql_summary.instance_number,
sql_id||'::'||hash_value order by stats$sql_summary.snap_id),0)),
elapsed_time, elapsed_time - lag(elapsed_time) over
(partition by stats$sql_summary.dbid, stats$sql_summary.instance_number,
sql_id||'::'||hash_value order by stats$sql_summary.snap_id),elapsed_time), 0) /1000000 ELAPS
from stats$sql_summary, stats$snapshot nm
where stats$sql_summary.snap_id = nm.snap_id
and nm.snap_time >= sysdate - 360
and sql_id = 'cm5vu20fhtnq1'
Below we can see how the "Speedway" performance tool uses a similar query to the one above and plots SQL performance information to a series of graphs and tables.
Illustration 1: Speedway Showing SQL Performance Summary from STATSACK
Illustration 2: Speedway showing average SQL Performance statistics from STATSACK
Wait Event Statistics:
STATSPACK tables to use : STATS$SYSTEM_EVENT, STATS$SNAPSHOT
AWR equivalent tables : DBA_HIST_SYSTEM_EVENT, DBA_HIST_SNAPSHOT
The below query extracts database wait information and calculates the delta value based on the STATAPACK snapshot intervals.
select totals.instance_number,totals.event,
sum(totals.tot_waits), sum(totals.tot_time) from
(select dhs.instance_number, dhs.snap_time,
dsys.event,
nvl(decode(greatest(TOTAL_WAITS, nvl(lag(TOTAL_WAITS)
over
(partition by dsys.dbid, dsys.instance_number, dsys.event
order by dhs.snap_id),0)),
TOTAL_WAITS, TOTAL_WAITS - lag(TOTAL_WAITS) over
(partition by dsys.dbid, dsys.instance_number, dsys.event
order by dhs.snap_id),TOTAL_WAITS), 0) tot_waits,
nvl(decode(greatest(TIME_WAITED_MICRO, nvl(lag(TIME_WAITED_MICRO)
over
(partition by dsys.dbid, dsys.instance_number, dsys.event
order by dhs.snap_id),0)),
TIME_WAITED_MICRO, TIME_WAITED_MICRO - lag(TIME_WAITED_MICRO)
over
(partition by dsys.dbid, dsys.instance_number, dsys.event
order by dhs.snap_id),TIME_WAITED_MICRO), 0) tot_time
from STATS$SYSTEM_EVENT dsys, stats$snapshot dhs
where
dsys.instancE_number = dhs.instance_number and
dhs.snap_id = dsys.snap_id and
dhs.snap_time >= sysdate - 360) totals
group by totals.instance_number,totals.event
order by sum(totals.tot_waits) desc
Below we can see how the "Speedway" performance tool uses similar STATSPACK queries to display various health check results derived from STATAPACK performance metrics.
Illustration 3: Speedway showing top wait event information from STATSACK
Object Statistics:
STATSPACK tables to use : STATS$SEG_STAT, STATS$SNAPSHOT
AWR equivalent tables : DBA_HIST_SEG_STAT, DBA_HIST_SNAPSHOT
The below query extracts object performance information and calculates the delta value based on the STATAPACK snapshot intervals. Note that there are a lot more columns in the STATS$SEG_STAT table other than what the below query is focusing on. The below query is only focusing on physical read activity.
select nm.snap_id,nm.snap_time,'',o.owner||':'||o.object_name||':'||o.subobject_name as objname,
nvl(decode(greatest(PHYSICAL_READS, nvl(lag(PHYSICAL_READS) over
(partition by ss.dbid, ss.instance_number,
o.owner||':'||o.object_name||':'||o.subobject_name order by ss.snap_id),0)),
PHYSICAL_READS, PHYSICAL_READS - lag(PHYSICAL_READS) over
(partition by ss.dbid, ss.instance_number,
o.owner||':'||o.object_name||':'||o.subobject_name order by ss.snap_id),PHYSICAL_READS), 0) as phyr,
nvl(decode(greatest(DIRECT_PHYSICAL_READS, nvl(lag(DIRECT_PHYSICAL_READS) over
(partition by ss.dbid, ss.instance_number,
o.owner||':'||o.object_name||':'||o.subobject_name order by ss.snap_id),0)),
DIRECT_PHYSICAL_READS, DIRECT_PHYSICAL_READS - lag(DIRECT_PHYSICAL_READS) over
(partition by ss.dbid, ss.instance_number,
o.owner||':'||o.object_name||':'||o.subobject_name order by ss.snap_id),DIRECT_PHYSICAL_READS), 0) as phyrd
from stats$seg_stat ss, stats$snapshot nm, dba_objects o
where ss.snap_id = nm.snap_id
and ss.obj# = o.object_id
and o.owner = 'PERFSTAT' and o.object_name = 'STATS$SQL_SUMMARY'
and o.subobject_name is null
and nm.snap_time >= sysdate - 360
Below we can see how the "Speedway" performance tool uses a similar query to the one above and plots object performance information to a series of graphs.
Illustration 4: Speedway showing object performance information from STATSACK