Oracle SQL Tuning and CBO Internal Using STATSPACK Utility

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

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:
$ORACLE_HOME/rdbms/admin/spcreate.sql:

cd $ORACLE_HOME/rdbms/admin

sqlplus / as sysdba

@spcreate

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

@report

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

@report

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 9.2.0.3.0 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)

Avg

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)

Avg

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.