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;