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

Complete Database Performance Management