Call (800) 766-1884 for Oracle support & training
Free Oracle Tips

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle


Oracle Tips by Burleson

Oracle SQL Tuning and CBO Internal

Using STATSPACK Utility

Now, how do you know if need to use a hint, or if Oracle is in fact using the best access path it can?  One of the easiest ways to figure out what is going on is to use the STATSPACK utility that Oracle comes with, to see what is going on in your system.  The setup of STATSPACK is quite easy:


The installation script for STATSPACK is located in:

cd $ORACLE_HOME/rdbms/admin

sqlplus / as sysdba



You will be prompted for a default tablespace and a temporary tablespace for the id that this creates – perfstat.  Note that the default password for the perfstat is perfstat (same id and password), and obviously should be changed.  But, for purposes of the examples below, we’ll keep it the same.


There is an instruction document typically located in this same directory, and it is called either spdoc.txt or statspack.doc


Once you have created the STATSPACK structures, grabbing a snapshot is relatively simple:


sqlplus perfstat/perfstat

exec statspack.snap


The code above will grab a snapshot right now.  Then, wait 5 or 10 or 15 minutes, and run the same thing again.  Now you have two snapshots, with which to compare.


Now, you need to get a report of what was happening during your timeslot:


sqlplus perfstat/perfstat



You will be prompted for the ids of the beginning time and end time, and the output file name.  Then your report will be generated.


For example:


sqlplus perfstat/perfstat



Sample output:


    DB Id DB     Name     Instance#    Instance

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

     123456789   ORCL            1     ORCL

 Completed Snapshots


Instance    DB Name   SnapId      Snap Started      Snap Level

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

ORCL       ORCL        1        1 Jan 2004 13:00:02        5

                       2        1 Jan 2004 13:05:54        5


    Enter beginning Snap Id: 1

    Enter ending    Snap Id: 2

Enter name of output file [sp_1_2] : <press return or enter a new name>


Then your report is generated, into whatever output file you designated  (note – be sure that your init.ora or spfile parameter timed_statistics is set to true or you won’t get all the information that you need).


There are lots of options that you can tweak in STATSPACK, but what is covered above is just the basic, default installation and report.


It is possible to change the options that STATSPACK is using in which to generate the report.  I typically go with the defaults, to start with, but for the sake of completeness, they are listed below:

n         Snapshot level – values 0 – 10, defaults to 5, which is gather general performance statistics on things like waits, system and session events, SGA, background events, locks, latches, buffer pool statistics, rollback segment information, row cache statistics, and high resource SQL statements.  Using a level of 0 would do all of the above except the high resource SQL statements.

n         Using a Level 6 plan usage data to the report (assuming the statement is in the shared pool when the snapshot is taken, and it exceeds one of the thresholds defined below).

n         Using a Level 10 would add child latch statistics to the report.  This can have a performance impact on your system, and isn’t recommended unless Oracle Technical Support tells you to turn it on.

High resource SQL statements – what is high?  The answer is, it is all relative.  So, there are thresholds that you can set, so you can use the definition of high that is appropriate for your system.


There are 4 main thresholds that you can change:

n         Number of executions of the SQL statement (default value = 100)

n         Number of parse calls executed by a given SQL statement (default value = 1000)

n         Number of disk reads executed by a given SQL statement (default value = 1000)

n         Number of buffer gets executed by a statement (default value = 1000)

So, for example, if you run with the defaults, and an SQL statement gets more than 1000 buffers, that SQL statement will appear on the report.

n         If a given SQL statement performs more than 1000 disk reads, it will appear on the report.

n         If a SQL statement is executed more than 100 times, it will appear on the report.

When following a tuning methodology, this reports makes it easy to zero in on the statements that are using all the resources of your system.  If you see a handful of SQL statements running over and over again, and doing lots of I/O, you know just where to start!


You can either change these defaults permanently, or just for the current execution of a snap.


To change the default permanently:


execute statspack.snap (i_snap_level => 6, i_modify_parameter=>’true’);


execute statspack.snap (i_buffer_gets_th => 5000, i_modify_parameter=>’true’);


To change the default just for the current run:


execute statspack.snap (i_snap_level => 6);


execute statspack.snap (i_buffer_gets_th => 5000, i_modify_parameter=>’false’);


(either setting the i_modify_parameterto false, or not specifying it at all, will just change defaults for the current run).


Or, you can change defaults permanently, in preparation for your next snapshot, without actually getting a snapshot, using the following:


execute statspack.modify_statspack_parameter                (i_snap_level=>6, i_executions_th => 200, i_buffer_gets_th=>5000, i_disk_reads_th=>5000,

i_parse_calls_th =>5000);


A partial sample output from STATSPACK:


STATSPACK report for


DB Name         DB Id    Instance     Inst Num Release     Cluster Host

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

TOY           2410677970 TOY                 1   NO      matthew


            Snap Id     Snap Time      Sessions Curs/Sess Comment

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

Begin Snap:     835 19-Jan-04 22:16:20       13       2.6

  End Snap:     836 19-Jan-04 22:30:13       12       4.1

   Elapsed:               13.88 (mins)


Cache Sizes (end)


               Buffer Cache:         8M      Std Block Size:        16K

           Shared Pool Size:         8M          Log Buffer:        32K


Load Profile

~~~~~~~~~~~~                            Per Second       Per Transaction

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

                  Redo size:                397.05            330,740.00

              Logical reads:                 68.15             56,769.00

              Block changes:                  0.52                431.00

             Physical reads:                  0.33                272.00

            Physical writes:                  0.15                123.00

                 User calls:                 14.66             12,212.00

                     Parses:                  0.68                569.00

                Hard parses:                  0.00                  0.00

                      Sorts:                  0.27                221.00

                     Logons:                  0.00                  0.00

                   Executes:                  1.10                913.00

               Transactions:                  0.00


  % Blocks changed per Read:    0.76    Recursive Call %:    14.04

 Rollback per transaction %:    0.00       Rows per Sort:    24.33


Instance Efficiency Percentages (Target 100%)


            Buffer Nowait %:  100.00       Redo NoWait %:  100.00

            Buffer  Hit   %:   99.58    In-memory Sort %:   98.64

            Library Hit   %:  100.00        Soft Parse %:  100.00

         Execute to Parse %:   37.68         Latch Hit %:  100.00

Parse CPU to Parse Elapsd %:  119.05     % Non-Parse CPU:   98.32


 Shared Pool Statistics        Begin   End

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

             Memory Usage %:   86.17   86.09

    % SQL with executions>1:   78.14   78.14

  % Memory for SQL w/exec>1:   79.65   79.65


Top 5 Timed Events

~~~~~~~~~~~~~~~~~~                                                     % Total

Event                                               Waits    Time (s) Ela Time

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

CPU time                                                           15    79.57

control file parallel write                           270           3    15.11

db file sequential read                               174           1     4.12

db file scattered read                                 15           0      .86

control file sequential read                          137           0      .27




Wait Events for DB: TOY  Instance: TOY  Snaps: 835 -836

-> s  - second

-> cs - centisecond -     100th of a second

-> ms - millisecond -    1000th of a second

-> us - microsecond - 1000000th of a second

-> ordered by wait time desc, waits desc (idle events last)



                                                     Total Wait   wait    Waits

Event                               Waits   Timeouts   Time (s)   (ms)     /txn

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

control file parallel write           270          0          3     10    270.0

db file sequential read               174          0          1      4    174.0

db file scattered read                 15          0          0     11     15.0

control file sequential read          137          0          0      0    137.0

log file sync                           1          0          0     13      1.0

log file parallel write                24         24          0      0     24.0

direct path read                       15          0          0      0     15.0

db file parallel write                  8          0          0      0      8.0

direct path write                       6          0          0      0      6.0

SQL*Net message from client        11,769          0      4,199    357 ########

SQL*Net message to client          11,768          0          0      0 ########




Background Wait Events for DB: TOY  Instance: TOY  Snaps: 835 -836

-> ordered by wait time desc, waits desc (idle events last)



                                                     Total Wait   wait    Waits

Event                               Waits   Timeouts   Time (s)   (ms)     /txn

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

control file parallel write           270          0          3     10    270.0

db file scattered read                 11          0          0     14     11.0

db file sequential read                15          0          0      6     15.0

control file sequential read          108          0          0      0    108.0

log file parallel write                24         24          0      0     24.0

db file parallel write                  8          0          0      0      8.0

rdbms ipc message                     850        833      3,922   4614    850.0

smon timer                              3          3        900 ######      3.0

pmon timer                            280        280        814   2907    280.0



Now that we’ve looked at STATSPACK, and we’ve looked at our top 5 waits, the next step is to take a look at our highest resource-utilizing SQL and see if we can tweak it. 


Some of the things to zero in on are correlated subqueries, which can sometimes be poor performers, as well as the join type that Oracle is using.






Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.