Thursday, September 17, 2009

Statspack is configurable for data capturing and reporting

Capturing

SQL statements and segment statistics are captured based on threshold values, by default, stats$statspack_parameters is empty, and all values taken from their default values.


5.6. Input Parameters for the SNAP and MODIFY_STATSPACK_PARAMETER procedures

Parameters able to be passed in to the statspack.snap and
statspack.modify_statspack_parameter procedures are as follows:

Range of Default
Parameter Name Valid Values Value Meaning
------------------ ------------ ------- -----------------------------------
i_snap_level 0,5,6,7,10 5 Snapshot Level
i_ucomment Text Comment to be stored with Snapshot
i_executions_th Integer >=0 100 SQL Threshold: number of times
the statement was executed
i_disk_reads_th Integer >=0 1,000 SQL Threshold: number of disk reads
the statement made
i_parse_calls_th Integer >=0 1,000 SQL Threshold: number of parse
calls the statement made
i_buffer_gets_th Integer >=0 10,000 SQL Threshold: number of buffer
gets the statement made
i_sharable_mem_th Integer >=0 1048576 SQL Threshold: amount of sharable
memory
i_version_count_th Integer >=0 20 SQL Threshold: number of versions
of a SQL statement
i_seg_phy_reads_th Integer >=0 1,000 Segment statistic Threshold: number
of physical reads on a segment.
i_seg_log_reads_th Integer >=0 1,0000 Segment statistic Threshold: number
of logical reads on a segment.
i_seg_buff_busy_th Integer >=0 100 Segment statistic Threshold: number
of buffer busy waits for a segment.
i_seg_rowlock_w_th Integer >=0 100 Segment statistic Threshold: number
of row lock waits for a segment.
i_seg_itl_waits_th Integer >=0 100 Segment statistic Threshold: number
of ITL waits for a segment.
i_seg_cr_bks_sd_th Integer >=0 1000 Segment statistic Threshold: number
of Consistent Reads blocks served by
the instance for the segment (RAC).
i_seg_cu_bks_sd_th Integer >=0 1000 Segment statistic Threshold: number
of CUrrent blocks served by the
instance for the segment (RAC).
i_session_id Valid sid 0 (no Session Id of the Oracle Session
from session) to capture session granular
v$session statistics for
i_modify_parameter True,False False Save the parameters specified for
future snapshots?


Reporting

Statspack report is using "spreport.sql" from $ORACLE_HOME/rdbms/admin. and it calls "sprepins.sql" for the actual reporting work.

Certain parameters can be tuned to change how many events/top sqls/top segments inside the spreport output.


Rem $Header: sprepins.sql 17-may-2004.14:15:50 cdialeri Exp $

clear break compute;
repfooter off;
ttitle off;
btitle off;
set timing off veri off space 1 flush on pause off termout on numwidth 10;
set echo off feedback off pagesize 60 linesize 80 newpage 1 recsep off;
set trimspool on trimout on;
define top_n_events = 5;
define top_n_sql = 65;
define top_n_segstat = 5;
define num_rows_per_hash=5;