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

 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
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

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

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Sample STATSPACK report (AWR) Analysis

Oracle Database Tips by Donald Burleson

In the area of expert systems we strive to codify human decision rules into software that mimics a human expert.  My latest effort in STATSPACK report analysis (in conjunction with other Oracle tuning experts) is called , a new tool to help automate the analysis of Oracle STATSPACK and AWR reports.

I've written both Oracle Press books on STATSPACK analysis, and my latest book "Oracle Tuning: The Definitive Reference", has over 900 pages of my best STATSPACK and AWR tuning tips and scripts. 


Ion Oracle tuning tool software Another superb tool for STATSPACK report analysis is the Workload Interface Statistics Engine (Ion), written by Oracle guru Alexey Danchenkov.
For professional STATSPACK analysis, out BC Oracle health check can certify that all of your global settings are fully optimized.

Here is a sample output from statspackanalyzer for a busy Oracle MRP database with over 165,000 logical reads per second.

Statspack Analyzer Recommends:

The database has a potentially serious IO bottleneck. Your storage subsystem is holding up processing, and you could accelerate the database by moving the main data tables and indexes to higher performance storage, such as solid state disk.

This Oracle system has a serious read load, performing 1,762 reads/second. This database is spending 50% of its available processing time waiting on reads from the storage system. Look at the table space IO statistics and pay attention to the heavily loaded tables that have a high average response time. Reduce the response time for these tables to less than 1 millisecond by moving them to solid state disks.

Top Timed Events

Event Percentage of Total Timed Events
db file sequential read 49%
The sequential read event occurs when Oracle reads single blocks of a table or index. Index reads typically cause this event. Moving your indexes to solid state disks can reduce the amount of time spent waiting for this event.
CPU time 48%
CPU time is the amount of time that the Oracle database spent processing SQL statements, parsing statements, or managing the buffer cache. Solid state disks help to increase the CPU time by reducing IO related wait events. If this is the main wait event, tuning SQL statements and/or increasing server CPU resources will provide the greatest performance improvement.

Load Profile

Logical reads: 164,756/s   Parses: 1098/s  
Physical reads: 1,762/s   Hard parses: 0/s  
Physical writes: 30/s   Transactions: 0/s  
Rollback per transaction %: 13.01%        
Custom Recommendations:
Your database has relatively high logical I/O at 164,756 reads per second. Logical Reads includes data block reads from both memory and disk. High LIO is sometimes associated with high CPU activity. CPU bottlenecks occur when the CPU run queue exceeds the number of CPUs on the database server, and this can be seen by looking at the "r" column in the vmstat UNIX/Linux utility or within the Windows performance manager. Consider tuning your application to reduce unnecessary data buffer touches (SQL Tuning or PL/SQL bulking), using faster CPU's or adding more CPU's to your system.

You are performing more than 1,762 disk reads per second. High disk latency can be caused by too-few physical disk spindles. Compare your read times across multiple datafiles to see which datafiles are slower than others. Disk read times may be improved if contention is reduced on the datafile, even though read times may be high due to the file residing on a slow disk. You should identify whether the SQL accessing the file can be tuned, as well as the underlying characteristics of the hardware devices. Check you average disk read speed later in this report and ensure that it is under 7ms. Assuming that the SQL is optimized, the only remaining solutions are the addition of RAM for the data buffers or a switch to solid-state disks.

You are performing more than 1,098 SQL parses per second. A parse is the process of executing your SQL, checking for proper security authorization, checks for the existence of tables, columns, and other referenced objects, and generating an execution plan. Your high parses suggest that your system has many incoming unique SQL statements or that your SQL is not reentrant (i.e. literal values in the WHERE clause, not using bind variables). Confirm that the 1,098 parses per second is reasonable and consider setting cursor_sharing=force if warranted. Setting cursor_sharing=force can cause dramatic performance improvements for systems with ad-hoc query tools such as Crystal Reports or Business Objects.

You may have an application issue causing excessive rollbacks with 13.01% rollbacks per transaction. Due to Oracles assumption of a commit, the Rollback process is very expensive and should only be used when necessary. You can identify the specific SQL and user session that is executing the rollbacks by querying the v$sesstat view.

Instance Efficiency

Buffer Hit: 98%   In-memory Sort: 100%  
Library Hit: 99%   Latch Hit: 99%  
Memory Usage: 81%   Memory for SQL: 58%  

Wait Events

Event Avg Wait (ms) Waits / txn
latch free 3 20.4
log file sync 6 0.8
SQL*Net message from client 11 ?
db file sequential read 2 3661.3
db file scattered read 1 244.3
buffer busy waits 5 13.9
direct path read 0 13.8
log file parallel write 6 1.7
control file parallel write 2 1.3
Custom Recommendations:
You have high latch free waits of 20.4 per transaction. The latch free wait occurs when the process is waiting for a latch held by another process. Check the later section for the specific latch waits. Latch free waits are usually due to SQL without bind variables, but buffer chains and redo generation can also cause them.

You have excessive buffer busy waits with 13.9 per transaction. Buffer busy waits are most commonly caused by segment header contention and can be remedied by increasing the value of the tables & index freelists or freelist_groups parameters, tuning your database writer (DBWR process, or by using Automatic Segment Storage Management (ASSM) in the tablespace definition. Using super-fast SSD will also reduce buffer busy waits because transactions are completed many times faster.

Instance Activity Stats

Statistic Total per Second per Trans
SQL*Net roundtrips to/from client 10,770,583 3,135 11,301.8
consistent gets - examination 336,550,178 97,976 353,148.1
db block changes 109,743 32 115.2
physical reads 6,055,219 1,762 6,353.9
physical reads direct 91,306 26 95.8
physical writes 105,818 30 111
physical writes direct 91,246 26 95.8
redo writes 1,575 0.5 1.7
table fetch continued row 11,362,529 3,307 11,922.9
table scans (long tables) 432 0 0.5
table scans (short tables) 58,636 17 61.5
Custom Recommendations:
You have high network activity with 3,135 SQL*Net roundtrips to/from client per second, which is a high amount of traffic. Review your application to reduce the number of calls to Oracle by encapsulating data requests into larger pieces (i.e. make a single SQL request to populate all online screen items). In addition, check your application to see if it might benefit from bulk collection by using PL/SQL "forall" or "bulk collect" operators.

You have 97,976 consistent gets examination per second. "Consistent gets - examination" is different than regular consistent gets. It is used to read undo blocks for consistent read purposes, but also for the first part of an index read and hash cluster I/O. To reduce disk reads, you may consider moving your indexes to a large blocksize tablespace. Because index splitting and spawning are controlled at the block level, a larger blocksize will result in a flatter index tree structure.

You have high disk reads with 1,762 per second. Reduce disk reads by increasing your data buffer size or speed up your disk read speed by moving to SSD storage. You can monitor your physical disk reads by hour of the day using AWR to see when the database has the highest disk activity.

You have 11,362,529 table fetch continued row actions during this period. Migrated/chained rows always cause double the I/O for a row fetch and "table fetch continued row" (chained row fetch) happens when we fetch BLOB/CLOB columns (if the avg_row_len > db_block_size), when we have tables with > 255 columns, and when PCTFREE is too small. You may need to reorganize the affected tables with the dbms_redefintion utility and re-set your PCTFREE parameters to prevent future row chaining.

You have high small table full-table scans, at 17 per second. Verify that your KEEP pool is sized properly to cache frequently referenced tables and indexes. Moving frequently-referenced tables and indexes to SSD will significantly increase the speed of small-table full-table scans.

Tablespace IO Stats

Tablespace Avg Reads (ms) % Reads % Writes % of Total I/O
MDW 2.2 100% 0% 30.8%
MDW_INDEX 1.5 100% 0% 29.69%
MDW_LOB 0.1 100% 0% 2.34%
TEMP02 0.1 50% 50% 0.45%
PERFSTAT 6.5 56% 44% 0.11%

Latch Activity

Latch Get Requests % Get Miss % NoWait Miss
cache buffers chains 803,593,366 0.3% 0%
cache buffers lru chain 30,611 0.2% 0.2%
library cache 39,161,832 0.6% 10%
redo allocation 63,537 0% ?%
redo copy 0 0% 0.1%
Custom Recommendations:
You have high cache buffer chain latches with 803,593,366 get requests at 0.3% get miss. See MOSC about increasing the hidden parameter _db_block_hash_buckets.

You have a high value for cache buffer LRU chain waits with 30,611 get requests at 0.2% get miss, and you need to reduce the length of the hash chains for popular data blocks in your RAM buffer. Investigate the specific data blocks that are experiencing the latches and reduce the popularity of the data block by spreading the rows across more data blocks by reorganizing with a higher value for PCTFREE.

You have high library cache waits with 39,161,832 get requests at 0.6% get miss. Consider pinning your frequently-used packages in the library cache with dbms_shared_pool.keep.

Buffer Pool Advisory

Current: 79,007,914 disk reads  
Optimized: 32,503,580 disk reads  
Estimated Improvement: 59% fewer  

Custom Recommendations:
The Oracle buffer cache advisory utility indicates 79,007,914 disk reads during the sample interval. Oracle estimates that doubling the data buffer size (by increasing db_cache_size) will reduce disk reads to 32,503,580, a 59% decrease.

PGA Memory Advisory

Current: 100% cache hit  
Optimized: 100% cache hit  
Estimated Improvement: 0% higher  

Shared Pool Advisory

Current: 13,738 cached objects   1,285,079 seconds saved  
Optimized: 26,108 cached objects   1,285,089 seconds saved  
Estimated Improvement: 90% more   10s (0% of DB Time)  

Custom Recommendations:
The Oracle shared pool advisory utility indicates 13,738 cached memory objects during the sample interval. Oracle estimates that doubling the shared pool size (by increasing shared_pool_size) will allow for 90% more cached objects and save 0% of the DB Time.

Init.ora Parameters

db_block_size 8,192  
db_cache_size 671,088,640  
db_file_multiblock_read_count 16  
hash_join_enabled true  
optimizer_mode choose  
pga_aggregate_target 1,048,576,000  
query_rewrite_enabled true  
shared_pool_size 402,653,184  
sort_area_size 1,024,000  
Custom Recommendations:
You are using the default optimizer mode and you may consider setting it to first_rows or first_rows_n if you have an online transaction processing system.

You have the default value for db_file_multiblock_read_count at 16. The CBO uses this parameter to determine the cost of a full-table scan. The default value is sometimes too large, and you can run scripts to determine the optimal setting. If full-table scans are unavoidable, you may consider placing those tables on SSD.

You are not using large blocksizes for your index tablespaces. Oracle research proves that indexes will build flatter tree structures in larger blocksizes.

Your shared pool is set at 402MB, which is an unusually large value. Allocating excessive shared pool resource can adversely impact Oracle performance. For further details, see the shared pool advisory.

You are not using your KEEP pool to cache frequently referenced tables and indexes. This may cause unnecessary I/O. When configured properly, the KEEP pool guarantees full caching of popular tables and indexes. Remember, an average buffer get is often 100 times faster than a disk read. Any table or index that consumes > 10% of the data buffer, or tables & indexes that have > 50% of their blocks residing in the data buffer should be cached into the KEEP pool. You can fully automate this process using scripts.

Consider setting your optimizer_index_caching parameter to assist the cost-based optimizer. Set the value of optimizer_index_caching to the average percentage of index segments in the data buffer at any time, which you can estimate from the v$bh view.
If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & 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 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.