Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 
 Home
 E-mail Us
 Oracle Articles
New Oracle Articles


 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog


 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Analysis
 Design
 Implementation
 Oracle Support


 SQL Tuning
 Security

 Oracle UNIX
 Oracle Linux
 Monitoring
 Remote s
upport
 Remote plans
 Remote
services
 Application Server

 Applications
 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S
upport  
 Development  

 Implementation


 Consulting Staff
 Consulting Prices
 Help Wanted!

 


 Oracle Posters
 Oracle Books

 Oracle Scripts
 Ion
 Excel-DB  

Don Burleson Blog 


 

 

 


 

 

 

 
 

Document Oracle Performance Changes

Oracle Tips by Burleson Consulting

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 Ion tool is the easiest way to analyze Oracle performance and Ion 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

Lets 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


 
If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.


 

 

��  
 
 
Oracle Training at Sea
 
 
 
 
oracle dba poster
 

 
Follow us on Twitter 
 
Oracle performance tuning software 
 
Oracle Linux poster
 
 
 

 

Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


                    









Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


 

Copyright © 1996 -  2017

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.

Remote Emergency Support provided by Conversational