Lab: Using Statspack Under Oracle XE

1. Install - Run the create scripts (under Oracle 10g XE version)

SQL> connect / as sysdba
SQL> @C:\oraclexe\app\oracle\product\10.2.0\server\RDBMS\ADMIN>spcreate.sql

Notes: prompted for enter password (oracle), the default tablespace SYSAUX
       was chosed, the temporary tablespace was also chosen as TEMP

2. Adjusting the STATSPACK Collection Level

   STATSPACK has two types of collection options: level and threshold.
     level     - controls the type of data collected from Oracle
     threshold - as a filter for the collection of SQL statements into
                 the stats$sql_summary table

   To show the description of different level, issue the following statement:
   SQL> select * from stats$level_description order by snap_level;

   To change the level of a snapshot, you can use statspack.snap function.
   The i_modify_parameter=> 'true' changes the level permanent for all
   snapshots in the future. e.g.
   SQL> exec statspace.snap(i_snap_level => 6, i_modify_parameter => 'true');

3. Create, View and Delete Snapshots


SQL> connect perfstat/oracle
Connected.
SQL> exec statspack.snap
PL/SQL procedure successfully completed.


SQL> select name, snap_id, to_char(snap_time, 'MM-DD-YYYY:HH24:MI:SS')
  2  "Date/Time" from stats$snapshot, v$database;

NAME         SNAP_ID Date/Time
--------- ---------- -------------------
XE                 1 09-29-2006:15:03:40
XE                 2 09-29-2006:15:04:31
XE                11 09-29-2006:15:06:09

   
  
To delete
SQL> @?\rdbms\admin\sppurge;

Notes: prompted to enter low and high snapshot ID


4. Create the Report


SQL> @?\rdbms\admin\spreport.sql

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

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

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_11_21.  To use this name,
press  to continue, otherwise enter an alternative.

Enter value for report_name:


5. Statspack at a Glance  - understading the report



5.1. Statspack Report Header

STATSPACK report for

Database    DB Id    Instance     Inst Num Startup Time    Release     RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
          2481462586 xe                  1 29-Sep-06 14:10 10.2.0.1.0  NO

Host  Name:   NTBK             Num CPUs:    1        Phys Memory (MB):      494
~~~~

Snapshot       Snap Id     Snap Time      Sessions Curs/Sess Comment
~~~~~~~~    ---------- ------------------ -------- --------- -------------------
Begin Snap:         11 29-Sep-06 15:06:09       16       5.4
  End Snap:         21 29-Sep-06 15:14:59       16       3.8
   Elapsed:                8.83 (mins)

Cache Sizes                       Begin        End
~~~~~~~~~~~                  ---------- ----------
               Buffer Cache:        72M             Std Block Size:         8K
           Shared Pool Size:        52M                 Log Buffer:     2,792K


Notes:
Curs/Sess column - shows the number of open cursors per session.
the item we are most interested in is the elapsed time. We want that to be
large enough to be meaningful, but small enough to be relevant (15 to 30
minutes is OK).

5.2 Statspack Load Profile

Load Profile                            Per Second       Per Transaction
~~~~~~~~~~~~                       ---------------       ---------------
                  Redo size:              5,410.54            716,896.00
              Logical reads:                 71.40              9,460.25
              Block changes:                 33.27              4,408.75
             Physical reads:                  0.03                  4.50
            Physical writes:                  4.69                621.25
                 User calls:                  0.21                 28.25
                     Parses:                  6.08                805.50
                Hard parses:                  0.86                114.50
                      Sorts:                  3.84                509.25
                     Logons:                  0.01                  0.75
                   Executes:                 13.62              1,804.00
               Transactions:                  0.01

  % Blocks changed per Read:   46.60    Recursive Call %:    99.88
 Rollback per transaction %:    0.00       Rows per Sort:     6.65

Notes:
 Three items are important:
   - The Hard parses (we want very few of them)
   - Executes (how many statements we are executing per second/transaction
   - Transactions (how many transactions per second we process).

 This gives an overall view of the load on the server.
 a fairly light system load (1-4 transactions per second is low).

5.3 Statspack Instance Efficiency Percentage

Instance Efficiency Percentages
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
            Buffer Nowait %:  100.00       Redo NoWait %:  100.00
            Buffer  Hit   %:   99.95    In-memory Sort %:  100.00
            Library Hit   %:   86.15        Soft Parse %:   85.79
         Execute to Parse %:   55.35         Latch Hit %:  100.00
Parse CPU to Parse Elapsd %:   77.17     % Non-Parse CPU:   68.03

 Shared Pool Statistics        Begin   End
                               ------  ------
             Memory Usage %:   77.74   79.31
    % SQL with executions>1:   91.71   70.29
  % Memory for SQL w/exec>1:   98.44   90.15


Notes:

Most important: Library Hit, Soft Parse and Execute to Parse. All of these
have to do with how well the shared pool is being utilized.

If the library Hit ration was low, it could be indicative of a shared pool
that is too small, or just as likely, that the system did not make correct
use of bind variables in the applicaiton

OLTP System
   The Soft Parase % value is one of the most important ratio in the database.
   For a typical OLTP system, it should be as near to 100% as possible. You
   quite simply do not hard parse after the database has been up for a while
   in your typical transactional / general-purpose database. The way you
   achieve that is with bind variables.

Data Warehouse
   In a data warehouse, we would like to generally see the Soft Parse ratio
   lower. We don't necessarily want to use bind variables in a data warehouse.
   This is because they typically use materialized views, hustograms, and
   other things that are easily thwarted by bind variables. In a data warehouse,
   we may have many seconds between executions, so hard parsing is not evil;
   in fact, it is good in those environments


5.4 Statspack Top 5 Timed Events

Top 5 Timed Events                                                    Avg %Total
~~~~~~~~~~~~~~~~~~                                                   wait   Call
Event                                            Waits    Time (s)   (ms)   Time
----------------------------------------- ------------ ----------- ------ ------
db file parallel write                             487           7     15   45.7
CPU time                                                         6          37.4
log file parallel write                            116           1      9    6.5
control file parallel write                        177           1      4    4.8
control file sequential read                       247           1      3    3.8
          -------------------------------------------------------------


Notes:
  CPU time - the sum of the CPU used by this session, or the amount of CPU
             time used during the snapshot window. In a heavily loaded system,
             if the CPU time event is the biggest event, that could point to
             some CPU-intensive processing

5.5 SQL ordered by Gets

Here you will find the most CPU-Time consuming SQL statements