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

Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance

Free Oracle Tips



BC Oracle tuning

Oracle training

Oracle support

Remote Oracle



  Oracle Tips by Burleson

Segment Information for an object 

Notice that this table is about 1 MB in size.  Theorize that a KEEP pool of at least 1 MB in size is created, and mark this table to be kept in that pool, the physical I/Os should drop and performance should improve.  The fact that the hit ratio for the KEEP pool would be high is of no importance to the user, so it is regarded as a collateral benefit to keep monitoring tools happy.

Reconfigure the database so that it has a KEEP pool of 2 MB.  This will require a database restart unless the application is at least version 9.  Configure table “foo” to put it into the KEEP pool that was just created.  The following command will accomplish this goal:

alter table foo storage (buffer_pool KEEP); 

In order to examine the results, query the user and run another STATSPACK report for a similar period of time to look for the code seen earlier while accessing this table.  Reviewing the new report, it is evident that while this SQL statement is still there, it no longer impacts the user as much since it can almost always get the data from the buffer cache.  As a side benefit, notice that the Buffer Hit ratio is significantly higher now as shown in Figure 7.14.  This does not necessarily mean that the performance is better, but it is back to what is considered “normal” for this database.

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 Elapsed%:  119.05      % Non-Parse CPU:  98.32

Figure 7.14 – SPREPORT with higher buffer hit ratio 

Another area to consider is whether the SQL statement in question is really needed.  If this SQL statement is a scheduled job that runs on a regular basis, it may be possible that there is no longer a business need to continue running this job.  For example, a daily report could have been rendered obsolete by a new screen that provides up-to-the-minute data. 

If the SQL statement does pass that test, then another question to ask is whether or not it can be optimized.  Optimizing could reduce the load on the database and make overall performance better.

The above book excerpt is from:

Oracle Wait Event Tuning

High Performance with Wait Event Iinterface Analysis 

ISBN 0-9745993-7-9  

Stephen Andert


Oracle performance tuning software 

Oracle performance tuning book


Oracle performance Tuning 10g reference poster
Oracle training in Linux commands
Oracle training Excel
Oracle training & performance tuning books



Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.