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: 4063
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.