12 | 12 | 2017
Latest Articles
Popular Articles

Performance Tuning

Statspack with non default attributes

User Rating:  / 0
PoorBest 

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


5

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.