Using STATSPACK or AWR reports, it is easy to document the performance
improvement from a system change. We can also time individual
transactions in SQL*Plus using the set timing on command.
Also see: Measuring Oracle
transactions per second and my book "Oracle
Tuning: The Definitive Reference" for details on documenting
performance changes to an Oracle database.
Step 1 - Collect a representative sample
Start by taking two one-hour elapsed-time
snapshots, make the change, and then take two more snapshots (under
similar load conditions).
The WISE tool is
the easiest way to analyze Oracle performance and WISE
allows you to spot hidden performance trends.
Step 2 -
Create STATSPACK Reports
Here we have two options:
1. Run statspack_alert.sql
(See Oracle Press book “Oracle high performance tuning with
STATSPACK”). By setting the thresholds to low values, you will be able
to see the exact changes in each salient metric.
2. Create two standard
Oracle STATSPACK reports, for the elapsed-time period, one before, one
after.
STEP 3 -
Interpret the STATSPACK Reports
Let’s use the standard spreport as an example.
1 – Load Profile Report
a) Apples to Apples - We start by
verifying that both reports have the same approximate system load. In
the Load Profile below, we see .92 transactions per second.
b) Changes - What we look at next depends
on the type of change. For example, If we made a change to reduce
disk I/O, we compare the ratio of Logical reads to Physical
reads.
Load
Profile
~~~~~~~~~~~~ Per Second Per
Transaction
---------------
---------------
Redo size: 2,189.01
2,376.67
Logical reads: 5,467.24
5,935.95
Block changes: 10.59
11.50
Physical reads: 1,953.94
2,121.45
Physical writes: 20.35
22.10
User calls: 131.08
142.32
Parses: 28.80
31.27
Hard parses: 0.30
0.33
Sorts: 3.98
4.32
Logons: 0.21
0.22
Executes: 28.60
31.05
Transactions:
0.92
Instance efficiency Report
Again, the metric depends upon the type of change.
If we did a change to reduce disk I/O, we expect the buffer Hit % to
rise. In the example below, the hit ratio is quite poor at %64,
indicating an over-stressed data cache.
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 88.10 Redo NoWait %: 100.00
Buffer Hit %: 64.26 In-memory Sort %:
99.61
Library Hit %: 99.42 Soft Parse %: 98.95
Execute to Parse %: -0.72 Latch Hit %: 99.11
Parse CPU to Parse Elapsd %: 64.02 % Non-Parse CPU: 100.00
Wait event report
If we are tuning to remove a system bottleneck,
this report will give us direct data. Again, the metric we choose
depends upon the type of change.
·
I/O tuning - In the
example below, the database is I/O bound and all tuning should be
aimed at reducing the amount of disk I/O (by adjusting init.ora
parameters, improving statistics quality, and tuning SQL with hints).
·
Network Tuning –
This report show very high network latency, with 800 milliseconds wait
time per transaction and 142 waits per transaction. If we were making
a change to tune the network (e.g. bundling I/O with stored
procedures), we would see a huge drop in this metric.
Wait Events for DB:
-> cs - centisecond - 100th of a second
-> ms - millisecond - 1000th of a second
-> ordered by wait time desc, waits desc (idle events
last)
Avg
Total Wait wait Waits
Event Waits Timeouts
Time (cs) (ms) /txn
---------------------------- ------------ ----------
----------- ------ ------
db file scattered read
1,322,855 0 2,405,612 18 396.5
buffer busy waits 2,357,596 0
1,866,742 8 706.7
db file sequential read
1,130,255 0 1,726,200 15 338.8
latch free 51,295
50,357 96,563 19 15.4
direct path read 7,018
0 3,946 6 2.1
direct path write 2,448
0 1,964 8 0.7
log file sync 3,847
2 1,686 4 1.2
SQL*Net more data to client 26,844
0 1,207 0 8.0
log file parallel write 3,581
0 1,104 3 1.1
control file parallel write 1,179
0 979 8 0.4
db file parallel write 521
0 600 12 0.2
file open 281
0 244 9 0.1
SQL*Net break/reset to clien 238
0 102 4 0.1
control file sequential read 45
0 59 13 0.0
refresh controlfile command 6
0 28 47 0.0
file identify 2
0 3 15 0.0
SQL*Net message from client
473,639 0 37,955,899 801 142.0
PX Idle Wait 5,286 5,292
1,086,379 2055 1.6
SQL*Net message to client
473,649 0 60 0 142.0
SQL*Net more data from clien 3,690
0 43 0 1.1
