Statistics Package (ST…

-----------------------------------------------------------------------

Oracle11g Server

Release 11.1

Production

-------------------------------------------------------------------------

Copyright (c) 1993, 2007, Oracle. All rightsreserved. 

Author:  Connie Dialeris Green
Contributors:  Cecilia Gervasio, Graham Wood,Russell Green, Patrick Tearle,
              Harald Eri, Stefan Pommerenk, Vladimir Barriere

Please refer to the Oracle11g server README file in the rdbmsdoc directory,
for copyright, disclosure, restrictions, warrant, trademark,disclaimer,
and licensing information.  The README file isREADME_RDBMS.HTM.

Oracle Corporation, 500 Oracle Parkway, Redwood City, CA94065.

-------------------------------------------------------------------------

Statistics Package (STATSPACK) README (spdoc.txt)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

TABLE OF CONTENTS
-----------------

0.  Introduction and Terminology
1.  Enterprise Manager (EM), Automatic WorkloadRepository (AWR) and Statspack
2.  Statspack Configuration
    2.1.Database Space Requirements
    2.2.Installing the Tool
    2.3. Errorsduring Installation
3.  Gathering data - taking a snapshot
    3.1.Automating Statspack Statistics Gathering
    3.2. Usingdbms_job
4.  Running the Performance reports
    4.1. Runningthe instance report
    4.2. Runningthe instance report when there are multiple instances
    4.3.Configuring the Instance Report
    4.4. Runningthe SQL report
    4.5. Runningthe SQL report when there are multiple instances
    4.6.Configuring the SQL report
    4.7.Gathering optimizer statistics on the PERFSTAT schema
5.  Configuring the amount of data captured
    5.1.Snapshot Level
    5.2.Snapshot SQL thresholds
    5.3.Changing the default values for Snapshot Level and SQLThresholds
    5.4.Snapshot Levels - details
    5.5.Specifying a Session Id
    5.6. InputParameters for the SNAP and
        MODIFY_STATSPACK_PARAMETERS procedures
6.  DB time,, and Time Units used for PerformanceStatistics
   6.1.  DB time compared to Total Call Time
   6.2.  Time Units used for PerformanceStatistics
7.  Event Timings
8.  Managing and Sharing performance data
    8.1.Baselining performance data
      8.1.1. Input Parameters for the MAKE_BASELINE andCLEAR_BASELINE
             procedure and function which accept Begin and End Snap Ids
      8.1.2. Input Parameters for the MAKE_BASELINE andCLEAR_BASELINE
             procedure and function which accept Begin and End Dates
    8.2.Purging/removing unnecessary data
      8.2.1. Input Parameters for the PURGE procedure and function
             which accept Begin Snap Id and End SnapId      
      8.2.2. Input Parameters for the PURGE procedure and function
             which accept Begin Date and End Date
      8.2.3. Input Parameters for the PURGE procedure and function
             which accept a single Purge Before Date
      8.2.4. Input Parameters for the PURGE procedure and function
             which accept the Number of Days of data to keep
      8.2.5. Using sppurge.sql
    8.3.Removing all data
    8.4. Sharingdata via export
9.  New and Changed Features
    9.1. Changesbetween 10.2  and 11.1
    9.2. Changesbetween 10.1  and 10.2
    9.3. Changesbetween 9.2   and 10.1
    9.4. Changesbetween 9.0   and 9.2
    9.5. Changesbetween 8.1.7 and 9.0
    9.6. Changesbetween 8.1.6 and 8.1.7
10. Compatibility and Upgrading from previous releases
    10.1.Compatibility Matrix
      10.1.2. Using Statspack shipped with 11.1
      10.1.2. Using Statspack shipped with 10.2
      10.1.3. Using Statspack shipped with 10.1
      10.1.4. Using Statspack shipped with 10.0
      10.1.5. Using Statspack shipped with 9.2
      10.1.6. Using Statspack shipped with 9.0
      10.1.7. Using Statspack shipped with 8.1.7 on 9i releases
    10.2.Upgrading an existing Statspack schema to a newer release
      10.2.1. Upgrading the Statspack schema from 10.2 to 11.1
      10.2.2. Upgrading the Statspack schema from 10.1 to 10.2
      10.2.3. Upgrading the Statspack schema from9.2   to 10.1
      10.2.4. Upgrading the Statspack schema from9.0   to 9.2
      10.2.5. Upgrading the Statspack schema from 8.1.7 to 9.0
      10.2.6. Upgrading the Statspack schema from 8.1.6 to 8.1.7
      10.2.7. Upgrading the Statspack schema from 8.1.6 to 9.2
      10.2.8. Upgrading the Statspack schema from 8.1.6 to 9.0
      10.2.9. Upgrading the Statspack schema from 8.1.7 to 9.2
11. Oracle Real Application Clusters specific considerations
    11.1.Changing Instance Numbers
    11.2.Cluster Specific Reports
    11.3.Cluster Specific Data
12. Conflicts and differences compared to UTLBSTAT/UTLESTAT
    12.1.Running BSTAT/ESTAT in conjunction to Statspack
    12.2.Differences between Statspack and BSTAT/ESTAT
13. Removing the package
14. Supplied Scripts Overview
15. Limitations and Modifications
    15.1.Limitations
    15.2.Modifications

0.  Introduction and Terminology
--------------------------------

To effectively perform reactive tuning, it is vital to have anestablished
baseline for later comparison when the system is runningpoorly.  Without
a baseline data point, it becomes very difficult to identify what anew
problem is attributable to:  Has the volume oftransactions on the system
increased?  Has the transaction profile orapplication changed?  Has the
number of users increased?

Statspack fundamentally differs from the well knownUTLBSTAT/UTLESTAT
tuning scripts by collecting more information, and also by storingthe
performance statistics permanently in Oracle tables, which canlater
be used for reporting and analysis.  The datacollected can be analyzed
using the report provided, which includes an 'instance health andload'
summary page, high resource SQL statements, as well as thetraditional
wait events and initialization parameters.

Statspack improves on the existing UTLBSTAT/UTLESTAT performancescripts
in the following ways:

  - Statspack collects more data, includinghigh resource SQL
    (and theoptimizer execution plans for those statements)

  - Statspack pre-calculates many ratios usefulwhen performance
    tuning, suchas cache hit ratios, per transaction and per
    secondstatistics (many of these ratios must be calculated
    manuallywhen using BSTAT/ESTAT)

  - Permanent tables owned by PERFSTAT storeperformance statistics;
    instead ofcreating/dropping tables each time, data is inserted
    into thepre-existing tables.  This makes historicaldata
    comparisonseasier

  - Statspack separates the data collectionfrom the report generation.
    Data iscollected when a 'snapshot' is taken; viewing the data
    collected isin the hands of the performance engineer when he/she
    runs theperformance report

  - Data collection is easy to automate usingeither dbms_job or an
    OSutility

  NOTE:  The term 'snapshot' isused to denote a set of statistics gathered
        at a single time, identified by a unique Id which includesthe
        snapshot number (or snap_id).  This term shouldnot be confused
        with Oracle's Snapshot Replication technology.

How does Statspack work?

Statspack is a set of SQL, PL/SQL and SQL*Plus scripts whichallow the
collection, automation, storage and viewing of performancedata.  A user
is automatically created by the installation script - this user,PERFSTAT,
owns all objects needed by this package.  Thisuser is granted limited
query-only privileges on the V$views required for performancetuning.

Statspack users will become familiar with the concept of a'snapshot'.
'snapshot' is the term used to identify a single collectionof
performance data.  Each snapshot taken isidentified by a 'snapshot id'
which is a unique number generated at the time the snapshot istaken;
each time a new collection is taken, a new snap_id isgenerated. 

The snap_id, along with the database identifier (dbid) andinstance number
(instance_number) comprise the unique key for a snapshot (usingthis
unique combination allows storage of multiple instances of aClustered
database in the same tables).

Once snapshots are taken, it is possible to run the performancereport.
The performance report will prompt for the two snapshot id's thereport
will process.  The report produced calculates theactivity on the instance
between the two snapshot periods specified, in a similar way tothe
BSTAT/ESTAT report; to compare - the first snap_id supplied canbe
considered the equivalent of running BSTAT; the secondsnap_id
specified can be considered the equivalent ofESTAT.  Unlike BSTAT/ESTAT
which can by its nature only compare two static data points, thereport
can compare any two snapshots specified.

 

1.  Enterprise Manager (EM), Automatic WorkloadRepository (AWR) and Statspack
------------------------------------------------------------------------------

Enterprise Manager
------------------
Statspack allows you to capture Oracle instance-related performancedata, and
report on this data in a textual format.

For EM managed databases in 9i, Oracle Enterprise Manager usesStatspack data
and displays it graphically.  Starting with 10g,Enterprise Manager instead
uses data collected by the Automatic Workload Repository(AWR).  AWR
data is internally captured and stored by Oracle 10g databases.

For more information about Oracle Enterprise Manager visit theOracle website
oracle.com --> Database --> Manageability

Automatic Workload Repository and Statspack
-------------------------------------------
The Automatic Workload Repository (AWR) is an integrated part ofthe Oracle
server.  Its purpose is to collect server-relatedperformance data
automatically every 60 minutes (by default) when thestatistics_level
parameter is set to 'typical' (or 'all').  As thedata is collected by the
server itself, the Automated Database Diagnostic Monitor (ADDM)component of
the server uses this data automatically to diagnose performanceissues. 
DBAs and performance engineers can access the performancerecommendations
by using EM, or view the captured data in the AWR report, which issimilar to
the Statspack Instance report.

To compare, Statspack is a manually installed and configured setof SQL and
PL/SQL scripts which gather performancestatistics.  The data gathered
is used by DBAs and performance engineers to manually diagnoseperformance
problems.

The AWR schema was initially based on the Statspack schema, buthas since been
modified.  Because of this shared history, thereare some similarities
(e.g. concept of a snapshot, similar basetables).  However, AWR is
separate from Statspack.

For more information on using AWR, please see the Oracle 10gServer
Performance Tuning Guide.  For license informationregarding AWR, please
see the Oracle database Licensing Information Manual.

If you are going to use AWR instead of Statspack, and you havebeen using
Statspack at your site, it is recommended that you continue tocapture
Statspack data for a short time (e.g. one month) after the upgradeto 10g. 
This is because comparing post-upgrade Statspack data topre-upgrade Statspack
data may make diagnosing initial upgrade problems easier todetect.

  WARNING:  If you choose tocontinue Statspack data collection after
           upgrading to 10g, and statistics_level is set to typical or
           all (which enables AWR collection), it is advised to stagger
           Statspack data collection so it does not coincide with AWR
           data collection (AWR data collection is by default is every
           hour, on the hour).  Staggering data collectionshould be done
           to avoid the potential for any interference (e.g. staggerdata
           collection by 30 minutes).

Long term, typically, there is little reason to collect datathrough both
AWR and Statspack.  If you choose to use AWRinstead of Statspack, you should
ensure you should keep a representative set of baselined Statspackdata for
future reference.

 

2.  Statspack Configuration
---------------------------

2.1.  Database Space Requirements

  The amount of database space required by thepackage will vary considerably
  based on the frequency of snapshots, the size ofthe database and instance,
  and the amount of data collected (which isconfigurable).
 
  It is therefore difficult to provide generalstorage clauses and space
  utilization predictions that will be accurate ateach site.

  Space Requirements
  ------------------
  The default initial and next extent sizes are100k, 1MB, 3MB or 5MB for all
  Statspack tables and indexes. To install Statspack, the minimum
  space requirement is approximately100MB.  However, the amount of space
  actually allocated will depend on the Tablespacestorage characteristics
  of the tablespace Statspack is installed in (forexample, if your minimum
  extent size is 10m, then the storage requirementwill be considerably more
  than 100m).

  Using Locally Managed Tablespaces
  ---------------------------------
  If you install the package in a locally-managedtablespace, such as
  SYSAUX, modifying storage clauses is notrequired, as the storage
  characteristics are automatically managed.

  Using Dictionary Managed Tablespaces
  ------------------------------------
  If you install the package in adictionary-managed tablespace, Oracle
  suggests you monitor the space used by theobjects created, and adjust
  the storage clauses of the segments, ifrequired.

2.2.  Installing the Tool

   Installation scripts create a user called PERFSTAT, which will ownall
    PL/SQL codeand database objects created (including the STATSPACK tables,
    constraintsand the STATSPACK package).

    Duringthe installation you will be prompted for the PERFSTAT
    user'spassword and default and temporary tablespaces.

    Thedefault tablespace will be used to create all Statspack
    objects(such as tables and indexes).  Oracle recommendusing the
    SYSAUXtablespace for the PERFSTAT user's default tablespace; the
    SYSAUXtablespace will be the tablespace defaulted during the
   installation, if no other is specified.

    Atemporary tablespace is used for workarea activities, such
    as sorting(for more information on temporary tablespaces, see
    theOracle10g Concepts Manual).  The Statspack user'stemporary
    tablespacewill be set to the database's default temporary tablespace
    by theinstallation, if no other temporary tablespace is specified.

     NOTE:
     o  A password for PERFSTAT user is mandatory andthere is no default
        password; if a password is not specified, the installationwill
        abort with an error indicating this is the problem.

     o  For security reasons, keep PERFSTAT's passwordconfidential.

     o  Do not specify the SYSTEM tablespace for thePERFSTAT users
        DEFAULT or TEMPORARY tablespaces; if SYSTEM is specified the
        installation will terminate with an error indicating this isthe
        problem.  This is enforced as Oracle does notrecommend using
        the SYSTEM tablespace to store statistics data, nor forworkareas.
        Use the SYSAUX (or a TOOLS) tablespace to store the data, and
        your instance's TEMPORARY tablespace for workareas.

     o  During the installation, the dbms_shared_poolPL/SQL package
        is created.  dbms_shared_pool is used to pin theStatspack
        package in the shared pool
        dbms_job is no longer created as part of the installation, as
        it is already created by catproc.sql (dbms_job can be used by
        the DBA to schedule periodic snapshots automatically).

    To installthe package, either change directory to the ORACLE_HOME
    rdbms/admindirectory, or fully specify the ORACLE_HOME/rdbms/admin
    directorywhen calling the installation script, spcreate.

    To runthe installation script, you must use SQL*Plus and connect as
    a user withSYSDBA privilege.

   e.g.  Start SQL*Plus, then:
     on Unix:
       SQL>  connect / as sysdba
       SQL>  @?/rdbms/admin/spcreate

     on Windows:
       SQL>  connect / as sysdba
       SQL>  @%ORACLE_HOME%\rdbms\admin\spcreate

    The spcreateinstall script runs 3 other scripts - you do not need to
    run these -these scripts are called automatically:
     1.  spcusr ->  creates the user and grantsprivileges
     2.  spctab ->  creates the tables
     3.  spcpkg ->  creates the package

    Checkeach of the three output files produced (spcusr.lis,
    spctab.lis,spcpkg.lis) by the installation to ensure no
    errors wereencountered, before continuing on to the next step.

    Note thatthere are two ways to install Statspack - interactively (as
    shownabove), or in 'batch' mode; batch mode is useful when you do
    not wish tobe prompted for the PERFSTAT user's password, and default
    andtemporary tablespaces.

     Batch mode installation
     ~~~~~~~~~~~~~~~~~~~~~~~
     To install in batch mode, you must assign values to theSQL*Plus
     variables which specify the password and the default andtemporary
     tablespaces before running spcreate.

     The variables are:
       perfstat_password   -> for the password
       default_tablespace   -> forthe default tablespace
       temporary_tablespace -> for the temporary tablespace

     e.g.
       on Unix:
         SQL>  connect / as sysdba
         SQL>  define default_tablespace='tools'
         SQL>  define temporary_tablespace='temp'
         SQL>  define perfstat_password='erg8oiw'
         SQL>  @?/rdbms/admin/spcreate
         SQL>  undefine perfstat_password

     spcreate will no longer prompt for the above information.

2.3.  Errors during installation

  Specifying SYSTEM tablespace
    A possibleerror during installation is to specify the SYSTEM
    tablespacefor the PERFSTAT user's DEFAULT or TEMPORARY tablespace.
    In such asituation, the installation will fail, stating the problem.

  To install Statspack after receiving errorsduring the installation
    To correctlyinstall Statspack after such errors, first run the
    de-installscript, then the install script.  Both scriptsmust be
    run fromSQL*Plus.

   e.g.  Start SQL*Plus, connect as a user withSYSDBA privilege, then:
      SQL> @spdrop
      SQL> @spcreate

 

3.  Gathering data - taking a snapshot
--------------------------------------

The simplest interactive way to take a snapshot is to login toSQL*Plus
as the PERFSTAT user, and execute the procedurestatspack.snap:
    e.g.
     SQL>  connect perfstat/perfstat_password
     SQL>  execute statspack.snap;

  Note:  In a Clustereddatabase environment, you must connect to the
        instance you wish to collect data for.

This will store the current values for the performancestatistics
in the Statspack tables, and can be used as a baselinesnapshot
for comparison with another snapshot taken at a later time.

For better performance analysis, set the initializationparameter
timed_statistics to true;  this way, Statspackdata collected will include
important timing information.  Thetimed_statistics parameter is also
dynamically changeable using the 'alter system'command.  Timing data is
important and is usually required by Oracle support to diagnoseperformance
problems.

The default level of data collection is level5.  It is possible to change
the amount of data captured by changing the snapshot level, and thedefault
thresholds used by Statspack.  For information onhow to do this, please see
the 'Configuring the amount of data captured' section of thisfile.

Typically, in the situation where you would like to automate thegathering
and reporting phases (such as during a benchmark), you may need toknow the
snap_id of the snapshot just taken.  To take asnapshot and display the
snap_id, call the statspack.snap function.  Belowis an example of calling
the snap function using an anonymous PL/SQL block in SQL*Plus:

   e.g.
    SQL> variable snap number;
    SQL> begin   :snap :=statspack.snap;   end;
      2  /
    PL/SQL procedure successfully completed.
    SQL> print snap
          SNAP
    ----------
            12

3.1.  Automating Statspack statisticsgathering

  To be able to make comparisons of performancefrom one day, week or
  year to the next, there must be multiplesnapshots taken over a period
  of time.

  The best method to gather snapshots is toautomate the collection on
  a regular time interval.  It ispossible to do this:

    - withinthe database, using the Oracle dbms_job procedure to
     schedule the snapshots

    - usingOperating System utilities.  On Unix systems, youcould use
     utilities such as 'cron' or 'at'.  On Windows, youcould schedule a
     task (e.g. via Start> Programs> Accessories> SystemTools>
     Scheduled Tasks).

3.2.  Using dbms_job

  To use an Oracle-automated method forcollecting statistics, you can use
  dbms_job.  A sample script onhow to do this is supplied in spauto.sql,
  which schedules a snapshot every hour, on thehour.

  You may wish to schedule snapshots at regulartimes each day to reflect your
  system's OLTP and/or batch peak loads. Forexample take snapshots at 9am,
  10am, 11am, 12 midday and 6pm for the OLTP load,then a snapshot at
  12 midnight and another at 6am for the batchwindow.

  In order to use dbms_job to schedulesnapshots, the job_queue_processes
  initialization parameter must be set to a valuegreater than 0 for the job
  to run automatically.

  Example of setting the job_queue_processesparameter in an init.ora file:
   #  Set to enable the job queue process tostart.  This allows dbms_job
   #  to schedule automatic statistics collectionusing STATSPACK
   job_queue_processes=1

  If using spauto.sql in a Clustered databaseenvironment, the spauto.sql
  script must be run once on each instance in thecluster.  Similarly, the
  job_queue_processes parameter must also be setfor each instance.

  Changing the interval of statisticscollection
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  To change the interval of statistics collectionuse the dbms_job.interval
  procedure
  e.g.
    executedbms_job.interval(1,'SYSDATE+(1/48)');

  Where 'SYSDATE+(1/48)' will result in thestatistics being gathered each
  1/48th of a day (i.e. every 30 minutes).

  To force the job to run immediately,
    executedbms_job.run();

  To remove the auto collect job,
    executedbms_job.remove();

  For more information on dbms_job, see theSupplied Packages Reference
  Manual.

 

4.  Running the Performance reports
-----------------------------------

Once snapshots are taken, it is possible to generate aperformance report.
There are two reports available - an Instance report, and a SQLreport:

 - The Instance Report (spreport.sql andsprepins.sql)
   is a general instance healthreport, covering all aspects of instance
   performance. The instance report calculates and prints ratios,
   increases etc. for allstatistics between the two snapshot periods,
   in a similar way to theBSTAT/ESTAT report.

   Note: spreport.sql callssprepins.sql, first defaulting the dbid and
        instance number of the instance you are connectedto.  For more
        information on the difference between sprepins and spreport,
        see the 'Running the instance report when there are multiple
        instances' section of this document.

 - The SQL report (sprepsql.sql andsprsqins.sql)
   is a report for a specific SQLstatement.  The SQL report is usually
   run after examining thehigh-load SQL sections of the instance health
   report.  TheSQL report provides detailed statistics and data for a
   single SQL statement (asidentified by the Hash Value).

   Note: sprepsql.sql callssprsqins.sql, first defaulting the dbid and
        instance number of the instance you are connectedto.  For more
        information on the difference between sprsqins and sprepsql,
        see the 'Running the SQL report when there are multipleinstances'
        section of this document.

Both reports prompt for the beginning snapshot id, the endingsnapshot id,
and the report name.  The SQL report additionallyrequests the Hash Value
for the SQL statement to be reported on.

  Note:  It is not correct tospecify begin and end snapshots where the
        begin snapshot and end snapshot were taken from different
        instance startups.  In other words, the instancemust not have
        been shutdown between the times that the begin and endsnapshots
        were taken.
        The reason for this requirement is the database's dynamic
        performance tables which Statspack queries to gather the data
        are memory resident, hence shutting down the database will
        reset the values in the performance tables to 0. As Statspack
        subtracts the begin-snapshot statistics from the end-snapshot
        statistics, the resulting output will be invalid.
        If begin and end snapshots which were taken between shutdowns
        are specified in the report, an appropriate error is signaled
        and the report exits.

Separating the phase of data gathering from producing a report,allows the
flexibility of basing a report on any data pointsselected.  For example
it may be reasonable for the DBA to use the supplied automationscript to
automate data collection every hour on the hour; If at some laterpoint
a performance issue arose which may be better investigated bylooking
at a three hour data window rather than an hour's worth of data,the
only thing the DBA need do, is specify the required start point andend
point when running the report.

The majority of cases, you will only need to read the followingsections
of this document, to run the reports:
  Running the instance report
  Running the SQL report
  Gathering optimizer statistics on the PERFSTATschema

If your database is a Real Application Clusters database, youmay also benefit
from reading:
  Running the instance report when there aremultiple instances
  Running the SQL report when there are multipleinstances

If you would like a greater degree of configuration in thereport output, you
may also be interested in reading:
  Configuring the Instance Report
  Configuring the SQL report

4.1.  Running the instance report

  To examine the change in instance-widestatistics between two time periods,
  the spreport.sql file is executed while beingconnected to the PERFSTAT
  user.  The spreport.sql commandfile is located in the rdbms/admin
  directory of the Oracle Home.

  This report assumes you are connected to thedatabase you wish to report
  on.  In a clustered databaseenvironment (RAC), you must connect to the
  instance you wish to report on when runningspreport.sql.  To avoid
  this, see the 'Running the instance report whenthere are multiple
  instances' section of this document.

  When running spreport, you will be promptedfor:
    1. Thebeginning snapshot Id
    2. Theending   snapshot Id
    3. The nameof the report text file to be created

  Note:  Blank lines betweenlines of snapshot Id's means the instance
        has been restarted (shutdown/startup) between those times -
        this helps identify which begin and end snapshots can be used
        together when running a Statspack report (ones separated by
        a blank line can not).

        By default, the report shows all completed snapshots for thisinstance
        when choosing the begin and end snapshot Id's. However, the number
        of days worth of snapshots to list is now configurable: tochange
        this, please see 'Snapshot related report settings - num_days' inthe
        'Configuring the Instance Report' section of this document.

    e.g. onUnix
     SQL>  connect perfstat/perfstat_password
     SQL>  @?/rdbms/admin/spreport

    e.g. onWindows
     SQL>  connect perfstat/perfstat_password
     SQL>  @%ORACLE_HOME%\rdbms\admin\spreport
  

    Exampleoutput:
   SQL>  connect perfstat/perfstat_password
   Connected.
   SQL>  @spreport
 

Current Instance
~~~~~~~~~~~~~~~~

   DBId    DBName     Inst Num Instance
----------- ------------ -------- ------------
 2618106428PRD1               1 prd1

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DBId    Inst NumDBName     Instance    Host
----------- -------- ------------ ------------ ------------
 2618106428       1PRD10       prd1        dlsun525

Using  261810642 for database Id
Using         1 for instance number

 

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the mostrecent
(n) days of snapshots being listed.  Pressingwithout
specifying a number lists all completed snapshots.

Listing all Completed Snapshots

                          Snap                   Snap
Instance    DBName        Id   SnapStarted    LevelComment
------------ ------------ ----- ----------------- ---------------------------
prd1        PRD1            1 11 May 200012:07    5
                             2 11 May 200012:08    5

                             3 12 May 200007:07    5
                             4 12 May 200008:08    5

 

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End   Snapshot Id specified:2

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2  To usethis name,
press to continue, otherwise enter an alternative.
Enter value for report_name:

Using the report name sp_1_2

  The report will now scroll past, and also bewritten to the file
  specified (e.g. sp_1_2.lis).

     Batch mode report generation
     ----------------------------
     To run a report without being prompted, assign values to the
     SQL*Plus variables which specify the begin snap id, the end snapid
     and the report name before running spreport.

     The variables are:
       begin_snap   -> specifies thebegin Snapshot Id
       end_snap    -> specifies the end  Snapshot Id
       report_name  -> specifies the Report outputname

     e.g.
       on Unix:
         SQL>  connect perfstat/perfstat_password
         SQL>  define begin_snap=1
         SQL>  define end_snap=2
         SQL>  define report_name=batch_run
         SQL>  @?/rdbms/admin/spreport

     spreport will no longer prompt for the above information.

4.2. Running the instance report when there are multipleinstances

  spreport.sql assumes you are connected to thedatabase you wish to report
  on.  There are certainsituations where this assumption may not be
  valid:

   - In a clustered databaseenvironment (RAC), you may be connected to
    an instance which is not the instance you wish to report on

   - If you are archivingbaseline Statspack data in a separate database
    from your production database, or when importing Statspackdata
    (e.g. in the case of Oracle support)

  In these situations, you would not be able toproduce the Statspack
  instance report using spreport.sql, as theinstance assumed may be
  unavailable, possibly on a totally differenthost.

  To circumvent this problem, you should runthe sprepins.sql report
  instead.  The sprepins.sqlreport output is identical to the
  spreport.sql output, as spreport.sql simplycalls sprepins.sql, first
  defaulting the Instance Number and DBId of thedatabase you are
  currently connected to.

  If you run sprepins.sql directly, you areprompted for the DBId and
  Instance Number for the instance you wish toreport on, in addition
  to the begin_snap and end_snap Ids and reportoutput name (i.e. the
  current DBId and Instance Number are notdefaulted).

  Note:  By default, thereport shows all completed snapshots for this instance
        when choosing the begin and end snapshot Id's. However, the number
        of days worth of snapshots to list is now configurable: tochange
        this, please see 'Snapshot related report settings - num_days' inthe
        'Configuring the Instance Report' section of this document.

  You will be prompted for:

    1. TheDBId
    2. TheInstance Number
    3. Thebeginning snapshot Id
    4. Theending   snapshot Id
    5. The nameof the report text file to be created

    Exampleoutput:
   SQL>  connect perfstat/perfstat_password
   Connected.
   SQL>  @sprepins
 

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DBId    Inst NumDBName     Instance    Host
----------- -------- ------------ ------------ ------------
 590400074       1CON90       con90       dlsun525
 4290976145       1MAIL        MAIL        mailhost

Enter value for dbid: 4290976145
Using 4290976145 for database Id
Enter value for inst_num: 1
....

  Then similarly to spreport, the availablesnapshots are displayed,
  and the begin and end snaps and report name areprompted for.

     Batch mode report generation
     ----------------------------
     To run the sprepins.sql report without being prompted, assignvalues
     to the SQL*Plus variables which specify the dbid, instancenumber,
     begin snap id, the end snap id, and the report name, beforerunning
     spreport.

     The variables are:
       dbid        -> specifies the dbid
       inst_num    -> specifies the instance number
       begin_snap   -> specifies thebegin Snapshot Id
       end_snap    -> specifies the end  Snapshot Id
       report_name  -> specifies the Report outputname

     e.g.
         SQL>  connect perfstat/perfstat_password
         SQL>  define dbid=4290976145
         SQL>  define inst_num=1
         SQL>  define begin_snap=1
         SQL>  define end_snap=2
         SQL>  define report_name=batch_run
         SQL>  @?/rdbms/admin/sprepins

     sprepins will no longer prompt for the above information.

4.3. Configuring the Instance Report

  It is now possible to configure some aspectsof the Statspack Instance
  report output.  These arediscussed below.

  For the majority of sites, the only variablewhich should be modified (if
  required) is num_days.  Theremaining variables have been documented solely
  for benchmarks, or for sites who have veryspecific application requirements.

  Note:  Modifying the defaultvalue of any variable other than num_days
        may result in useful data being excluded from thereport.  Please
        be very cautious when choosing values for the remainingvariables.

        Backup the original Statspack report (sprepins.sql) to adifferent
        file name before making changes to the file.  Oncethe changes
        have been made, backup the newly modified report. As this file
        will be replaced when the server is upgraded to a new release,you
        will need to make the same changes to this file each time the
        server is upgraded.

  The configuration is performed by modifyingthe 'Customer Configurable
  Report Settings' section of the filesprepins.sql for the instance report
  (and for num_days, sprsqins.sql for the SQLreport).

  Snapshot related report settings -num_days
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  The number of days of snapshots to list whendisplaying the snapshots
  to choose the begin and end snapshot Idsfrom.  The default is to list
  all snapshots.  However it isnow possible to configure the number of
  days worth of snapshots to list.

  This facility has been added for sites thathave a large number of snapshots
  stored in the Statspack schema, and whotypically only look at the last
  days worth of data.

  For example, setting the number of days ofsnapshots to list (num_days) to
  31 would result in the most recent 31 days worthof snapshots being listed
  when choosing the begin and end snapshotIds.

  Note:  This variable is theonly variable modifiable in both the instance
        report (sprepins.sql) and the SQL report (sprsqins.sql).

  The value of this variable is configured bychanging the value assigned to
  the variable num_days.
  e.g.
       define num_days = 60

  The variable has the following validvalues:

        - When a null string is used, all snapshots will bedisplayed.
                 This is the default setting.
                 e.g. 
                       define num_days = ''

           - Where n is the number of days of snapshots to list.
                 e.g. to set the number of days of snapshots to list to 31,
                 set num_days to 31:
                       define num_days = 31

   0          - A value of 0 means do not print out any snapshots.
                 This value would be of most use for batch execution of
                 the instance report, where the values for begin snap id and
                 end snap id are already known, thus printing out a list to
                 choose from is unneeded.
                 e.g.
                       define num_days = 0

    - Thismeans the parameter is commented out in (or totally
                 removed from) the file sprepins.sql
                 An undefined value for num_days will result in the report
                 prompting you for the number of days to enter, interactively.
                 Using '--' before the 'define' comments out the definition
                 of the variable, thus leaving it undefined.
                 e.g.
                      -- define num_days=31
                 Choosing this setting as your site's default means the
                 instance report cannot be run in batch mode.

  If num_days is set to any value other than ,you will not be
  prompted to enter a value. However, if the variable is set to
  running the instance report (or the SQL report)will result in you
  being prompted for the value, as follows:

Current Instance
~~~~~~~~~~~~~~~~

   DBId    DBName     Inst Num Instance
----------- ------------ -------- ------------
 1296193444MAINDB             1 maindb

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DBId    Inst NumDBName     Instance    Host
----------- -------- ------------ ------------ ------------
 1296193444       1MAINDB      maindb      main1

Using 1296193444 for database Id
Using         1 for instance number

Specify the number of days of snapshots to choose from
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Entering the number of days (n) will result in the mostrecent
(n) days of snapshots being listed.  Pressingwithout
specifying a number lists all completed snapshots.

Enter value for num_days: 5

Listing the last 5 days of Completed Snapshots

                          Snap                   Snap
Instance    DBName        Id   SnapStarted    LevelComment
------------ ------------ ----- ----------------- ---------------------------
                            13 26 Sep 200217:01    5
                            14 27 Sep 200213:28    5
                            15 27 Sep 200213:29    5
                            16 30 Sep 200214:40    5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap:
....

  SQL section report settings - top_n_sql
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Total number of rows of SQL output to display ineach SQL section of the
  report.  Note this is notrelated to a specific SQL statement, nor to the
  total number of SQL statements, merely to themaximum number of lines
  outputted for each separate SQL section of thereport.
  The default value is 65.  Tochange the value, change the value of the
  variable top_n_sql.
  e.g.
       define top_n_sql = 65;

  SQL section report settings -num_rows_per_hash
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  This is the upper limit of the number of rows ofSQL Text to print for
  each SQL statement appearing in the SQL sectionsof the report.  This
  variable applies to each SQL statement (i.e.hash_value).  The default value
  is 4, which means at most 4lines  of the SQL text will be printed for
  each SQL statement.  To changethis value, change the value of the variable
  num_rows_per_hash.
  e.g.
       define num_rows_per_hash = 10;

  SQL section report settings -top_pct_sql
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  This is a number which restricts the rows of SQLshown in the SQL sections 
  of the report.  Only SQLstatements which exceeded top_pct_sql percentage
  of resources used, are candidates for listing inthe report.
  The default value is 1.0%  Tochange the default, change the value of the
  variable top_pct_sql.
  e.g.
       define top_pct_sql = 0.5;

  In the SQL ordered by gets section of thereport, a top_pct_sql of 0.5% would
  only include SQL statements which had exceeded0.5% of the total buffer gets
  in the interval.

  Segment related report settings -top_n_segstat
 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  The number of top segments to display in each ofthe Segment sections of
  the instance report.
  The default value is 5, which means only the top5 segments in each category
  (e.g. top 5 logical reads) will bedisplayed.  To change the default,
  change the value of the variabletop_n_segstat.
  e.g.
        define top_n_segstat = 5;

4.4. Running the SQL report

  Once the instance report has been analyzed,often there are high-load SQL
  statements which should be examined to determineif they are causing
  unnecessary resource usage, and hence avoidableload.

  The SQL report sprepsql.sql, displaysSQL-specific statistics, the
  complete SQL text and (if level 6 snapshot hasbeen taken), information
  on any SQL Plan(s) associated with thatstatement.

  The SQL statement to be reported on isidentified by the statement's Hash
  Value (which is a numerical representation ofthe statement's SQL text).
  The Hash Value for each statement is displayedin the high-load SQL
  sections of the instance report.

  The sprepsql.sql file is executed while beingconnected to the PERFSTAT
  user, and is located in the rdbms/admindirectory of the Oracle Home.

  Note:  To run sprepsql.sqlin a Cluster environment, you must connect
        to the instance you wish to report on.

  You will be prompted for:
    1. Thebeginning snapshot Id
    2. Theending   snapshot Id
    3. The HashValue for the SQL statement
    4. The nameof the report text file to be created

    Exampleoutput:
   SQL>  connect perfstat/perfstat_password
   Connected.
   SQL>  @sprepsql
 

   DBId    DBName     Inst Num Instance
----------- ------------ -------- ------------
 2618106428PRD1               1 prd1

Completed Snapshots
                          Snap                   Snap
Instance    DBName        Id   SnapStarted    LevelComment
------------ ------------ ----- ----------------- ---------------------------
prd1        PRD1           37 02 Mar 200111:01    6
                            38 02 Mar 200112:01    6

                            39 08 Mar 200109:01    5
                            40 08 Mar 200110:02    5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 39
Begin Snapshot Id specified: 39

Enter value for end_snap: 40
End   Snapshot Id specified:40

Specify the Hash Value
~~~~~~~~~~~~~~~~~~~~~~
Enter value for hash_value: 1988538571
Hash Value specified is: 1988538571

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name issp_39_40_1988538571.  To use this name,
press to continue, otherwise enter an alternative.
Enter value for report_name:

Using the report name sp_39_40_1988538571
....

  The report will scroll past, and also bewritten to the file
  specified (e.g. sp_39_40_1988538571.lis).

     Batch mode report generation
     ----------------------------
     Similarly to spreport.sql, the SQL report can be run in batchmode.
     To run a report without being prompted, assign values to the
     SQL*Plus variables which specify the begin snap id, the end snapid,
     the SQL hash value, and the report name before runningspreport.

     The variables are:
       begin_snap   -> specifies thebegin Snapshot Id
       end_snap    -> specifies the end  Snapshot Id
       hash_value   -> specifies theHash Value
       report_name  -> specifies the Report outputname

     e.g.
         SQL>  connect perfstat/perfstat_password
         SQL>  define begin_snap=39
         SQL>  define end_snap=40
         SQL>  define hash_value=1988538571
         SQL>  define report_name=batch_sql_run
         SQL>  @sprepsql

     sprepsql will no longer prompt for the above information.

4.5.  Running the SQL report when there aremultiple instances

  sprepsql.sql assumes you are connected to thedatabase you wish to report
  on.  There are certainsituations where this assumption may not be
  valid:

   - In a clustered databaseenvironment (RAC), you may be connected to
    an instance which is not the instance you wish to report on

   - If you are archivingbaseline Statspack data in a separate database
    from your production database, or when importing Statspackdata
    (e.g. in the case of Oracle support)

  In these situations, you would not be able toproduce the Statspack
  SQL report using sprepsql.sql, as the instanceassumed may be
  unavailable, possibly on a totally differenthost.

  To circumvent this problem, you should runthe sprsqins.sql report
  instead.  The sprsqins.sqlreport output is identical to the
  sprepsql.sql output, as sprepsql.sql simplycalls sprsqins.sql, first
  defaulting the Instance Number and DBId of thedatabase you are
  currently connected to.

  If you run sprsqins.sql directly, you areprompted for the DBId and
  Instance Number for the instance you wish toreport on, in addition
  to the begin_snap and end_snap Ids, hash valueand report output name
  (i.e. the current DBId and Instance Number arenot defaulted).

  Note:  By default, thereport shows all completed snapshots for this instance
        when choosing the begin and end snapshot Id's. However, the number
        of days worth of snapshots to list is now configurable: tochange
        this, please see 'Snapshot related report settings - num_days' inthe
        'Configuring the Instance Report' section of this document.

  You will be prompted for:

    1. TheDBId
    2. TheInstance Number
    3. Thebeginning snapshot Id
    4. Theending   snapshot Id
    5. The HashValue for the SQL statement
    6. The nameof the report text file to be created

    Exampleoutput:
   SQL>  connect perfstat/perfstat_password
   Connected.
   SQL>  @sprsqins
 

Current Instance
~~~~~~~~~~~~~~~~

   DBId    DBName     Inst Num Instance
----------- ------------ -------- ------------
 1296193444MAINDB             1 maindb

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

   DBId    Inst NumDBName     Instance    Host
----------- -------- ------------ ------------ ------------
 1296193444       1MAINDB      maindb      main1
 4290976145       1MAIL        MAIL        mailhost

Enter value for dbid: 4290976145
Using 4290976145 for database Id
Enter value for inst_num: 1
....

  Then similarly to sprepsql, the availablesnapshots are displayed,
  and the begin and end snap Ids, the hash valueand report name are
  prompted for.

     Batch mode report generation
     ----------------------------
     To run the sprsqins.sql report without being prompted, assignvalues
     to the SQL*Plus variables which specify the dbid, instancenumber,
     begin snap id, the end snap id, hash value and the reportname,
     before running sprsqins.

     The variables are:
       dbid        -> specifies the dbid
       inst_num    -> specifies the instance number
       begin_snap   -> specifies thebegin Snapshot Id
       end_snap    -> specifies the end  Snapshot Id
       hash_value   -> specifies theHashValue       
       report_name  -> specifies the Report outputname

     e.g.
         SQL>  connect perfstat/perfstat_password
         SQL>  define dbid=4290976145
         SQL>  define inst_num=1
         SQL>  define begin_snap=1
         SQL>  define end_snap=2
         SQL>  define hash_value=1988538571
         SQL>  define report_name=batch_run
         SQL>  @?/rdbms/admin/sprsqins

     sprsqins will no longer prompt for the above information.

4.6. Configuring the SQL report

  It is now possible to configure the number ofdays of snapshots to view
  when choosing the begin and end snapshotIds.

  Note:  Backup the originalStatspack SQL report (sprsqins.sql) to a
        different file name before making changes to thefile.  Once the
        changes have been made, backup the newly modifiedreport.  As this
        file will be replaced when the server is upgraded to a newrelease,
        you will need to make the same changes to this file each timethe
        server is upgraded.

  The configuration is performed by modifyingthe 'Customer Configurable
  Report Settings' section of the filesprsqins.sql.  For details on how
  to modify the number of days of snapshots toview, please see
  'Snapshot related report settings - num_days' inthe 'Configuring the
  Instance Report' section of this document.

4.7. Gathering Optimizer statistics on the PERFSTAT schema

  For best performance when running theperformance reports, Optimizer
  statistics should be gathered on the Statspackschema.

  In 10g, the Oracle server automaticallygathers optimizer statistics
  on database segments when the segments becomestale.

  If you have disabled this on your site, youshould manually collect
  optimizer statistics for tables and indexesowned by PERFSTAT.  This
  should be performed whenever significant changein data volumes in
  PERFSTAT's tables.  To do this,use dbms_stats and specify the PERFSTAT
  user:

    executedbms_stats.gather_schema_stats(ownname=>'PERFSTAT',cascade=>true);

 

5.  Configuring the amount of datacaptured
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Both the snapshot level, and the thresholds specified willaffect the amount
of data Statspack captures.

5.1. Snapshot Level

  It is possible to change the amount ofinformation gathered by the package,
  by specifying a different snapshot'level'.  In other words, the level
  chosen (or defaulted) will decide the amount ofdata collected.
  The higher the snapshot level, the more data isgathered.  The default
  level set by the installation is level 5.

  For typical usage, level 5 snapshot iseffective on most sites.  There
  are certain situations when using a level 6snapshot is beneficial, such
  as when taking a baseline.

  The events listed below are a subset ofevents which should prompt
  taking a new baseline, using level 6:
  - when taking the first snapshots
  - when a new application is installed, or anapplication is modified/upgraded
  - after gathering optimizer statistics
  - before and after upgrading

  The various levels are explained in detail'Snapshot Levels - details'
  section of this document.

5.2. Snapshot SQL thresholds

  There are other parameters which can beconfigured in addition to the
  snapshot level.

  These parameters are used as thresholds whencollecting data on SQL
  statements; data will be captured on any SQLstatements that breach
  the specified thresholds.

  Snapshot level and threshold information usedby the package is stored
  in the stats$statspack_parameter table.

5.3. Changing the default values for Snapshot Level and SQLThresholds

  If you wish to, you can change the defaultparameters used for taking
  snapshots, so that they are tailored to theinstance's workload.

  The full list of parameters which can bepassed into the
  modify_statspack_parameter procedure are thesame as those for the
  snap procedure.  These arelisted in the 'Input Parameters for the SNAP and
  MODIFY_STATSPACK_PARAMETERS procedures' sectionof this document.

  Temporarily using new values
  ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  To temporarily use a snapshot level or thresholdwhich is different to
  the instance's default snapshot values, simplyspecify the required
  threshold or snapshot level when taking thesnapshot.  This value will
  only be used for immediate snapshot taken - thenew value will
  not be saved as the default.

  e.g. Take a single level 6 snapshot (do notsave level 6 as the default):
   SQL>  executestatspack.snap(i_snap_level=>6);

  Saving new defaults
  ~~~~~~~~~~~~~~~~~~~~
  If you wish to save the new value as theinstance's default, you can do
  this either by:

  o  Taking a snapshot, andspecifying the new defaults to be saved to the
    database (using statspack.snap, and using thei_modify_parameter
    input variable).

    SQL>  execute statspack.snap -
          (i_snap_level=>10, i_modify_parameter=>'true');

    Setting the i_modify_parameter value to true will save thenew
    thresholds in the stats$statspack_parameter table; thesethresholds
    will be used for all subsequent snapshots.

    If the i_modify_parameter was set to false or if it were omitted,the
    new parameter values would not be saved.  Only thesnapshot taken at
    that point will use the specified values, any subsequent snapshotswill
    use the preexisting values in the stats$statspack_parametertable.

  o  Changing the defaultsimmediately without taking a snapshot, using the
    statspack.modify_statspack_parameter procedure. For example to change
    the snapshot level to 10, and the SQL thresholds for buffer_getsand
    disk_reads, the following statement can be issued:

    SQL>  executestatspack.modify_statspack_parameter -
           (i_snap_level=>10, i_buffer_gets_th=>10000,i_disk_reads_th=>1000);

    This procedure changes the values permanently, but does not
    take a snapshot.

5.4 Snapshot Levels - details

 Levels  >=0   General performancestatistics
    Statisticsgathered:
    This leveland any level greater than 0 collects general
    performancestatistics, such as: wait statistics, system events,
    systemstatistics, rollback segment data, row cache, SGA, background
    events,session events, lock statistics, buffer pool statistics,
    latchstatistics, resource limit, enqueue statistics, andstatistics
    for each ofthe following, if enabled: automatic undo management,
    buffer cacheadvisory data, auto PGA memory management, Cluster DB
   statistics.

 Levels  >=5  Additional data:  SQLStatements
    This levelincludes all statistics gathered in the lower level(s),
    andadditionally gathers the performance data on high resource
    usage SQLstatements.

    In alevel 5 snapshot (or above), note that the time required forthe
    snapshot tocomplete is dependent on the shared_pool_size and on the
    number ofSQL statements in the shared pool at the time the snapshot
    is taken:the larger the shared pool, the longer the time taken to
    complete thesnapshot.

    SQL'Thresholds'
      The SQL statements gathered by Statspack are those which exceed oneof
      six predefined threshold parameters:
       - number of executions of the SQLstatement           (default 100)
       - number of disk reads performed by the SQLstatement  (default 1,000)
       - number of parse calls performed by the SQL statement (default1,000)
       - number of buffer gets performed by the SQL statement (default10,000)
       - size of sharable memory used by the SQLstatement   (default 1m)
       - version count for the SQLstatement                 (default 20)

      The values of each of these threshold parameters are usedwhen
      deciding which SQL statements to collect - if a SQLstatement's
      resource usage exceeds any one of the above threshold values,it
      is captured during the snapshot.
 
      The SQL threshold levels used are either those stored in thetable
      stats$statspack_parameter, or by the thresholds specifiedwhen
      the snapshot is taken.

 Levels  >=6  Additional data:  SQL Plansand SQL Plan usage
    This levelincludes all statistics gathered in the lower level(s),
    andadditionally gathers optimizer execution plans, and planusage
    data foreach of the high resource usage SQL statements captured.

    A level 6snapshot gathers information which is invaluable when
    determiningwhether the execution plan used for a SQL statement
    haschanged.  Therefore level 6 snapshots should beused
    wheneverthere is the possibility a plan may change, such as
    after largedata loads, or after gathering new optimizer
   statistics.

    Tocapture the plan for a SQL statement, the statement must be inthe
    shared poolat the time the snapshot is taken, and must exceed one of
    the SQLthresholds.  To gather plans for all statements inthe
    shared pool,you can temporarily specify the executions threshold
   (i_executions_th) to be zero (0) for thosesnapshots.  For information
    on how to dothis, see the 'Changing the default values for Snapshot
    Level andSQL Thresholds' section of this document.

 Levels  >=7  Additional data:  Segmentlevel statistics
    This levelincludes all statistics gathered in the lower level(s),
    andadditionally gathers the performance data on highly usedsegments.

    A level 7snapshot captures Segment-level statistics for segments which
    are heavilyaccessed or heavily contended for.

   Segment-level statistics captured are:
      - logical reads
      - db block changes
      - physical reads
      - physical writes
      - physical reads direct
      - physical writes direct
      - global cache cr blocks served *
      - global cache current blocks served *
      - buffer busy waits
      - ITL waits
      - row lock waits

      *  Denotes the Statistic is Real ApplicationClusters specific.

    There aremany uses for segment-specific statistics.  Beloware three
   examples:
    - Thestatistics relating to physical reads and writes can help you
     decide to modify the physical layout of some segments (or ofthe
     tablespaces they reside in).  For example, tobetter spread the
     segment IO load, you can add files residing on different disks toa
     tablespace storing a heavily accessed segment, or you can(re)partition
     a segment.
    - Highnumbers of ITL waits for a specific segment may indicate a needto
     change segment storage attributes such as PCTFREE and/orINITRANS.
    - In a RealApplication Clusters database, global cache statistics
     make it easy to spot the segments responsible for much of the
     cross-instance traffic.
 
    AlthoughStatspack captures all segment statistics, it only displaysthe
    followingstatistics in the Instance report:
      - logical reads
      - physical reads
      - buffer busy waits
      - ITL waits
      - row lock waits
      - global cache cr blocks served *
      - global cache current blocks served *

    Segmentstatistics 'Thresholds'
      The segments for which statistics are gathered are thosewhose
      statistics exceed one of the following seven thresholdparameters:
       - number of logical reads on thesegment               (default 10000)
       - number of physical reads on thesegment              (default 1000)
       - number of buffer busy waits on thesegment           (default 100)
       - number of row lock waits on thesegment              (default 100)
       - number of ITL waits on thesegment                   (default 100)
       - number of global cache Consistent Read blocks served* (default1000)
       - number of global cache CUrrent blocksserved*        (default 1000)

      The values of each of these thresholds are used when decidingwhich
      segments to collect statistics for.  If anysegment's statistic value
      exceeds its corresponding threshold value, all statistics forthis
      segment are captured.

      The threshold levels used are either those stored in thetable
      stats$statspack_parameter, or by the thresholds specifiedwhen
      the snapshot is taken.
 
 Levels  >= 10 Additionalstatistics:  Parent and Child latches
    This levelincludes all statistics gathered in the lower levels, and
    additionallygathers Parent and Child Latch information. Data
    gathered atthis level can sometimes cause the snapshot to take longer
    to completei.e. this level can be resource intensive, and should
    only be usedwhen advised by Oracle personnel.

5.5.  Specifying a Session Id

  If you would like to gather sessionstatistics and wait events for a
  particular session (in addition to the instancestatistics and wait events),
  it is possible to specify the session id in thecall to Statspack.  The
  statistics gathered for the session will includesession statistics,
  session events and lockactivity.  The default behaviour is to notto
  gather session level statistics.

   SQL>  executestatspack.snap(i_session_id=>3);

  Note that in order for session statistics tobe included in the report
  output, the session's serial number (serial#)must be the same in the
  begin and end snapshot.  If theserial numbers differ, it means the
  session is not the same session, so it is notvalid to generate session
  statistics.  If the serialnumbers differ, the following warning will
  appear (after the begin/end snapshot has beenentered by the user) to
  signal the session statistics cannot beprinted:

    WARNING:SESSION STATISTICS WILL NOT BE PRINTED, as session statistics
    captured inbegin and end snapshots are for different sessions
    (Begin Snapsid,serial#: 10,752,  End Snap sid,serial#:10,754).

5.6.  Input Parameters for the SNAP andMODIFY_STATSPACK_PARAMETERS procedures

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

                   Rangeof     Default
ParameterName     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    SQLThreshold: number of disk reads
                                          the statement made
i_parse_calls_th   Integer >=0  1,000    SQLThreshold: number of parse
                                          calls the statement made
i_buffer_gets_th   Integer >=0  10,000   SQL Threshold: number ofbuffer
                                          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    Segmentstatistic Threshold: number
                                          of physical reads on a segment.
i_seg_log_reads_th  Integer>=0  1,0000   Segment statisticThreshold: 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*.
i_seg_cu_bks_sd_th  Integer>=0  1000    Segment statistic Threshold: number
                                          of CUrrent blocks served by the
                                          instance for the segment*.
i_session_id       Validsid    0 (no    SessionId of the Oracle Session
                   from         session) to capture session granular
                   v$session             statistics for
i_modify_parameter True,False   False    Savethe parameters specified for
                                          future snapshots?

 

6.  DB time, and Time Units used forPerformance Statistics

----------------------------------------------

6.1.  DB time compared to Total Call Time

  DB time is a statistic manintained by thedatabase, and was introduced
  in Oracle 10g Release 10.1.  Itcan be queried in the view v$sys_time_model,
  along with other time model statistcs.

  DB time measures the amount of time spent inthe database by foreground
  processes in non-Idle state (e.g. the process iseither on the CPU, or
  actively waiting for a resource oraction).  There is also a foreground
  CPU time statistic called 'DB CPU'.

  Non-idle time spent in the database is alsoaccumulated for background
  processes separately, in the time modelstatistic 'background
  elapsed time' (there is also a 'background cputime').

  Call Time, or Total Call Time is a proxycomputed by Statspack for
  'DB Time + bacground elapsed'. It is based on the v$sysstat
  statistic 'CPU used by this session' + 'timespent in non-Idle waits'
  (where Idle events are in stats$idle_event).

  These numbers are usually very close, but maydiverge.  Either can be used
  to perform accurate problemdiagnosis.  Statspack uses both Call Time,and
  DB time, and AWR exclusively uses DB time.

6.2.  Time Units used for PerformanceStatistics
  Oracle now supports capturing certainperformance data with millisecond and
  microsecond granularity.

  Views which include microsecond timinginclude:
    -v$session_wait, v$system_event, v$session_event (time_waited_microcolumn)
    - v$sql,v$sqlarea (cpu_time, elapsed_time columns)
    - v$latch,v$latch_parent, v$latch_children (wait_time column)
    -v$sql_workarea, v$sql_workarea_active (active_time column)

  Views which include millisecond timingsinclude:
    -v$enqueue_stat (cum_wait_time)

  Note that existing columns in other viewscontinue to capture centi-second
  times.

  As centi-second and microsecond timing maynot be appropriate for rolled
  up data such as that displayed by Statspack,Statspack displays most
  cumulative times in seconds, and average timesin milliseconds (for easier
  comparison with Operating System monitoringutilities which often report
  timings in milliseconds).

  For clarity, the time units used arespecified in the column headings of
  each timed column in the Statspackreport.  The convention used is:
       (s)  - a second
       (cs) - a centisecond - whichis      100th of a second
       (ms) - a millisecond - whichis    1,000th of a second
       (us) - a microsecond - which is 1,000,000th of a second