Performance Tuning

Statspack with non default attributes

User Rating:

/ 0

Details: Parent Category: Articles; Created on Thursday, 18 March 2010 21:55; Last Updated on Monday, 12 March 2012 13:54; Published on Thursday, 18 March 2010 21:55; Written by Guy Lambregts; Hits: 3795

Statspack with non default attributes

Statspack - available since 8.1.5 - offers you more then just the simple execution of an "exec perfstat.statspack.snap".

The default "exec perfstat.statspack.snap" takes a snapshot at the instance level (see Statspack at the instance level ), gather statistics at the default collection level ( level 5 ), with the default segment thresholds. We can see what these defaults are with a select from perfstat.stats$statspack_parameter. We can however take snapshots at non default levels, with non default segment thresholds and this for both instance as session level ( see Statspack at the session level ). That ' s what will be shown here.

SQL> desc stats$statspack_parameter;

DBID
INSTANCE_NUMBER
SESSION_ID
SNAP_LEVEL
NUM_SQL
EXECUTIONS_TH
PARSE_CALLS_TH
DISK_READS_TH
BUFFER_GETS_TH
SHARABLE_MEM_TH
VERSION_COUNT_TH
PIN_STATSPACK
ALL_INIT NOT NULL
LAST_MODIFIED
UCOMMENT
JOB NUMBER
SEG_PHY_READS_TH
SEG_LOG_READS_TH
SEG_BUFF_BUSY_TH
SEG_ROWLOCK_W_TH
SEG_ITL_WAITS_TH
SEG_CR_BKS_SD_TH
SEG_CU_BKS_SD_TH

Here we notice the default snapshot level is 5.

SQL> select snap_level from stats$statspack_parameter;

SNAP_LEVEL

Here we list the default segment threshold values

SQL> select SEG_PHY_READS_TH "1",SEG_LOG_READS_TH "2",SEG_BUFF_BUSY_TH "3",SEG_ROWLOCK_W_TH "4",SEG_ITL_WAITS_TH "5",SEG_CR_BKS_SD_TH "6",SEG_CU_BKS_SD_TH "7" from stats$statspack_parameter;

1 2 3 4 5 6 7

1000 10000 100 100 100 1000 1000

Here is an example how one can take a snapshot at level 7 for the session id 9 ( 9 is here the session's SID as found back in v$session )

SQL> exec perfstat.statspack.snap(i_snap_level=>7,i_session_id=>9);

We can modify the segment defaults with the perfstat.statspack.modify_statspack_parameter procedure.
Let' s suppose we have a lot of buffer busy waits, enqueue waits and a lot of physical reads and let' s suppose we want to modify permanently the statspacks persistent settings regarding the segment information.

SQL> exec perfstat.statspack.modify_statspack_parameter (i_seg_rowlock_w_th=>0,i_seg_buff_busy_th=>0,i_seg_itl_waits_th=>0,i_seg_log_reads_th=>0, i_seg_phy_reads_th=>0);

If we take from now on snapshots in this way ( session level info )

SQL> exec perfstat.statspack.snap(i_snap_level=>7,i_session_id=>9);

or in this way ( instance level info )

SQL> exec perfstat.statspack.snap(i_snap_level=>7);

and take reports between those snapshots, we have near the bottom end of the report information about segment statistics for either the session level either the instance level

Top 5 Physical Reads per Segment for DB
Top 5 Buf. Busy Waits per Segment for DB
Top 5 Row Lock Waits per Segment for DB
...

This information can be interesting in order to know - and this in a user friendly manner - which are the hot segments, the segments suffering from the most row lock waits, ... and this for 0 cent.