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 


 

 

 


 

 

 

 
 

Oracle STATSPACK & AWR reports showing scattered read timings

Oracle Database Tips by Donald BurlesonOctober 28, 2015

 

 

Samples from the real world

 

Work in progress - Not complete yet! 

 

Here is an Oracle8i system running Oracle Application manufacturing module.  Please note that multi-block reads show as 2x slower than single block reads:

 

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:             65,998.37             15,749.46
              Logical reads:             50,859.74             12,136.87
              Block changes:                450.87                107.59
             Physical reads:                 68.71                 16.40
            Physical writes:                 33.13                  7.91
 . . .
Top 5 Wait Events
~~~~~~~~~~~~~~~~~                                             Wait     % Total
Event                                               Waits  Time (cs)   Wt Time
-------------------------------------------- ------------ ------------ -------
SQL*Net message from client                     1,342,946   47,258,362   79.20
pipe get                                           19,517    7,616,463   12.76
rdbms ipc message                                  18,109    3,585,905    6.01
smon timer                                             12      368,652     .62
pmon timer                                          1,180      362,842     .61
 . . .
 
                                                                    Avg
                                                     Total Wait    wait  Waits
Event                               Waits   Timeouts  Time (cs)    (ms)   /txn
---------------------------- ------------ ---------- ----------- ------ ------
SQL*Net message from client     1,342,946          0  47,258,362    352   88.4
pipe get                           19,517     16,976   7,616,463   3902    1.3
wakeup time manager                   114        114     350,322  30730    0.0
SQL*Net more data from clien        7,188          0      31,539     44    0.5
log file parallel write            15,492          0      27,934     18    1.0
db file sequential read           102,300          0      26,537      3    6.7
log file sync                      14,485          3      23,103     16    1.0
db file scattered read              7,191          0       5,087      7    0.5

 

 

Here is an Oracle eBusiness suite database where we see a small number of scattered reads, which are 2x faster than single block reads::

 

                                                                  

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:              8,592.35             30,970.33
              Logical reads:            164,756.72            593,850.28
              Block changes:                 31.95                115.16
             Physical reads:              1,762.80              6,353.85
            Physical writes:                 30.81                111.04
 . . .

Top 5 Timed Events

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

Event                                               Waits    Time (s) Ela Time

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

db file sequential read                         3,489,208       6,486    49.21

CPU time                                                        6,336    48.07

db file scattered read                            232,831         211     1.60


 . . .
 
Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read         3,489,208          0      6,486      2  3,661.3
db file scattered read            232,831          0        211      1    244.3

 

 

Here is a sample Oracle 9i database where we see slower multi-block reads:

 

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:              1,378.67              5,856.08
              Logical reads:                348.58              1,480.64
              Block changes:                  9.42                 40.01
             Physical reads:                 62.25                264.42
 . . .
Top 5 Wait Events
~~~~~~~~~~~~~~~~~                                            Wait     % Total
Event                                               Waits  Time (s)   Wt Time
-------------------------------------------- ------------ ----------- -------
db file sequential read                           211,516          66   72.71
control file parallel write                         1,178          13   14.83
db file scattered read                                939           4    4.46
log file sync                                         868           3    3.23
db file parallel write                              1,100           2    2.61

 . . .
                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts  Time (s)    (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file sequential read           211,516          0         66         249.4
control file parallel write         1,178          0         13     11      1.4
db file scattered read                939          0          4      4      1.1
 

Here is another 9i database with high read activity where see see that multi-block reads register as faster than sequential reads:

 

Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:             24,569.28              4,834.38
              Logical reads:              3,751.76                738.22
              Block changes:                136.22                 26.80
             Physical reads:              1,023.42                201.37
            Physical writes:                  3.67                  0.72
 . . .
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file scattered read                            145,571      10,098    80.00
CPU time                                                        1,313    10.40
db file sequential read                            45,408         573     4.54
log file sync                                      18,456         362     2.87
log file parallel write                            19,007         121      .95
 . . .
 
                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
db file scattered read            145,571          0     10,098     69      8.0
db file sequential read            45,408          0        573     13      2.5
 

Here is a 10g database where we see scattered read as being 3x slower than single block access:
 
Load Profile
~~~~~~~~~~~~                            Per Second       Per Transaction
                                   ---------------       ---------------
                  Redo size:             15,291.71              8,386.60
              Logical reads:             11,389.83              6,246.65
              Block changes:                 28.79                 15.79
             Physical reads:                 23.31                 12.78
            Physical writes:                  2.36                  1.29
 . . .
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
CPU time                                                        1,899    85.42
latch free                                         28,426          93     4.18
db file scattered read                             11,872          68     3.07
log file sync                                      19,706          27     1.20
db file sequential read                            15,920          24     1.10
 . . .
                                                                   Avg
                                                     Total Wait   wait    Waits
Event                               Waits   Timeouts   Time (s)   (ms)     /txn
---------------------------- ------------ ---------- ---------- ------ --------
latch free                         28,426     26,676         93      3      1.4
db file scattered read             11,872          0         68      6      0.6
log file sync                      19,706          0         27      1      1.0
db file sequential read            15,920          0         24      2      0.8
 
 

 


 

 

��  
 
 
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