STATS PACK Configuring And Report Generating

SQL> connect sys/manager as SYSDBA

SQL>CREATE TABLESPACE TBS_STSPCK DATAFILE '/opt/app/oracle/oradata/orcl/stats01.dbf' size 500m autoextend on
maxsize unlimited;

SQL> @spcreate

here it will ask for username and defalut tablespace for this username

SELECT Snap_Level FROM STATS$STATSPACK_PARAMETER

EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER –
(i_snap_level=>5, i_buffer_gets_th=>100000, -
i_modify_parameter=>'true');

Level    Description

0–4    General performance statistics on all memory areas, latches, pools, and
          events, and segment statistics, such as rollback and undo segments.

5       Same statistics from the lower levels, plus the most resource-intensive
         SQL statements.

6       Introduced in Oracle 9.0.1, level 6 includes the level 5 results plus
         SQL plans.

7–9    Introduced in Oracle 10g, level 7 includes level 6 results plus additional
          Segment Level statistics, including logical reads, physical reads/writes,
         global cache cr/current served and buffer busy, ITL, and row lock waits.

10      and greater Same statistics from level 6 plus parent/child latch data.




to create snapshot

execute STATSPACK.SNAP;

to create statspack report


@ORACLE_HOME/rdbms/admin/spreport

AWR Reports Generation

Snapshots

By default snapshots of the relevant data are taken every hour and retained for 7 days. The default values for these settings can be altered using the following procedure.

    BEGIN
      DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(
        retention => 43200,        -- Minutes (= 30 Days). Current value retained if NULL.
        interval  => 30);          -- Minutes. Current value retained if NULL.
    END;
    /

Extra snapshots can be taken and existing snapshots can be removed, as shown below.

    EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot;

    BEGIN
      DBMS_WORKLOAD_REPOSITORY.drop_snapshot_range (
        low_snap_id  => 22,
        high_snap_id => 32);
    END;
    /
   
Baselines

A baseline is a pair of snapshots that represents a specific period of usage. Once baselines are defined they can be used to compare current performance against similar periods in the past. You may wish to create baseline to represent a period of batch processing.

    BEGIN
      DBMS_WORKLOAD_REPOSITORY.create_baseline (
        start_snap_id => 210,
        end_snap_id   => 220,
        baseline_name => 'batch baseline');
    END;
    /

The pair of snapshots associated with a baseline are retained until the baseline is explicitly deleted.

    BEGIN
      DBMS_WORKLOAD_REPOSITORY.drop_baseline (
        baseline_name => 'batch baseline',
        cascade       => FALSE); -- Deletes associated snapshots if TRUE.
    END;
    /


Workload Repository Reports

Oracle provide two scripts to produce workload repository reports (awrrpt.sql and awrrpti.sql). They are similar in format to the statspack reports and give the option of HTML or plain text formats. The two reports give essential the same output but the awrrpti.sql allows you to select a single instance. The reports can be generated as follows.

    @$ORACLE_HOME/rdbms/admin/awrrpt.sql
    @$ORACLE_HOME/rdbms/admin/awrrpti.sql


awrblmig.sql
awrddinp.sql
awrddrpi.sql
awrddrpt.sql  *
awrextr.sql 
awrgdinp.sql
awrgdrpi.sql
awrgdrpt.sql
awrginp.sql 
awrgrpti.sql
awrgrpt.sql 
awrinfo.sql 
awrinpnm.sql
awrinput.sql
awrload.sql 
awrrpti.sql 
awrrpt.sql  *
awrsqrpi.sql 
awrsqrpt.sql  *