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 









Oracle Concepts - Disk IO and the Shared Pool

Oracle Tips by Burleson Consulting

Disk IO and the Shared Pool

The shared SQL area contains the Pcode versions of all of the current SQL commands that haven?t been aged out of the shared pool. There are numerous statistics available via the v$sqlarea DPT. The text of SQL statements in the shared pool can be retrieved (at least the first tens of bytes) from the v$sqltext DPT. Lets look at a report that displays the SQL statements in the SQL area with the greatest amount of disk reads (these will probably be the ones you will want to review and tune). Look at the report in Source 26.

REM Name: sqldrd.sql
REM Function: return the sql statements from the shared area with
REM Function: highest disk reads
REM History: Presented in paper 35 at IOUG-A 1997, converted for
REM use 6/24/97 MRA
DEFINE access_level = 1000 (NUMBER)
COLUMN parsing_user_id FORMAT 9999999 HEADING ?User Id?
COLUMN executions       FORMAT 9999        HEADING ?Exec?
COLUMN sorts            FORMAT 99999       HEADING ?Sorts?
COLUMN command_type     FORMAT 99999       HEADING ?CmdT?
COLUMN disk_reads       FORMAT 999,999,999 HEADING ?Block Reads?
START title132 ?SQL Statements With High Reads?
SPOOL rep_out/&db/sqldrd.lis
      parsing_user_id, executions,
      disk_reads > &&access_level

Source 26: Script to Monitor SQL Area Disk Reads By Script

Date: 06/24/97                                                 Page:   1
Time: 11:35 PM           SQL Statements With High Reads         SYSTEM

                               ORTEST1 database User

Id   Exec Sorts CmdT Block Reads Statement
---- ---- ----- ---- ----------- ---------------------------------------
   0  403     0    3          11 select f.file#, f.block#, f.ts#,
                                 f.length from fet$ f, ts$ t where
                                 t.ts#=f.ts# and t.dflextpct!=0

   0   11     0    3          11 select order#,columns,types from  
                                 access$ where d_obj#=:1

   0   12     0    3          12 select /*+ index(idl_ub1$ i_idl_ub11)
                                 +*/ piece#,length,piece from idl_ub1$
                                 where obj#=:1 and part=:2 and
                                 version=:3 order by piece#

   5   34     0    3          13 SELECT NAME,VALUE   FROM V$SYSSTAT
                                          WHERE NAME = 'db block gets'

   0   12     0    3          14 select /*+ index(idl_ub2$ i_idl_ub21)
                                 +*/ piece#,length,piece from idl_ub2$
                                 where obj#=:1 and part=:2 and
                                 version=:3 order by piece#

   0   17     0    3          27 select file#, block#, ts# from seg$
                                 where type# = 3

   0    1     1    3          79 select distinct d.p_obj#,d.p_timestamp
                                 from sys.dependency$ d, obj$ o where
                                 d.p_obj#>=:1 and d.d_obj#=o.obj# and

   5   34     0   47          90 DECLARE job BINARY_INTEGER := :job;
                                 next_date DATE := :mydate;  broken
                                 BOOLEAN := FALSE; BEGIN hitratio;
                                 :mydate := next_date; IF broken THEN :b
                                 := 1; ELSE :b := 0; END IF; END;

Listing 26:  Example Output From SQL Disk Read Script

The example report in Listing 26 was generated forcing a read count of 10. Usually disk reads will be in the range specified by the define statement. By tuning those statements which show large amounts of disk reads the overall performance of the application is increased.

Monitoring Library and Data Dictionary Caches

I've spent most of this lesson looking at the shared SQL area of the shared pool. Let's wrap up with a high level look at the library and data dictionary caches. The library cache area is monitored via the V$LIBRARYCACHE view and contains the SQL area, PL/SQL area, table, index and cluster cache areas. The data dictionary caches contain cache area for all data dictionary related definitions.

The script in Source 27 creates a report on the library caches. The items of particular interest in the report generated by the script in Source 27 (shown in Lisitng 27) are the various ratios. This report should look familiar, it is similar to the library caches report in the utlestat.sql reports.

REM Title: libcache.sql
REM FUNCTION: Generate a library cache report
COLUMN namespace                         HEADING "Library Object"
COLUMN gets             FORMAT 9,999,999 HEADING "Gets"
COLUMN gethitratio      FORMAT 999.99    HEADING "Get Hit%"
COLUMN pins             FORMAT 9,999,999 HEADING "Pins"
COLUMN pinhitratio      FORMAT 999.99    HEADING "Pin Hit%"
COLUMN reloads          FORMAT 99,999    HEADING "Reloads"
COLUMN invalidations    FORMAT 99,999    HEADING  "Invalid"
COLUMN db               FORMAT a10
START title80 "Library Caches Report"
DEFINE output = rep_out\&db\lib_cache
SPOOL &output
      gethitratio*100 gethitratio,
      pinhitratio*100 pinhitratio,
PAUSE Press enter to continue
UNDEF output

Listing 27: Example Script To Monitor The Library Caches

Look at the example output from the script in Source 27 in Listing 27. In Listing 27 we see that all Get Hit% (gethitratio in the view) except for indexes are greater than 80-90 percent. This is the desired state, the value for indexes is low because of the few accesses of that type of object. Notice that the Pin Hit% is also greater than 90% (except for indexes) this is also to be desired. The other goals of tuning this area are to reduce reloads too as small a value as possible (this is done by proper sizing and pinning) and to reduce invalidations. Invalidations happen when for one reason or another an object becomes unusable. However, if you must use flushing of the shared pool reloads and invalidations may occur as objects are swapped in and out of the shared pool. Proper pinning can reduce the number of objects reloaded and invalidated.

Guideline 7: In a system where there is no flushing increase the shared pool size in 20% increments to reduce reloads and invalidations and increase hit ratios.

Date: 11/21/98                                              Page:   1
Time: 02:51 PM             Library Caches Report            SYSTEM
                              ORTEST1 database

Library Object        Gets Get Hit%       Pins Pin Hit% Reloads Invalid
--------------- ---------- -------- ---------- -------- ------- -------
SQL AREA            46,044    99.17     99,139    99.36      24      16
TABLE/PROCEDURE      1,824    84.59      6,935    93.21       3       0
BODY                   166    93.98        171    91.23       0       0
INDEX                   27      .00         27      .00       0       0
CLUSTER                373    98.12        373    97.59       0       0

Listing 27: Example Of The Output From Library Caches Report

The data dictionary caches used to be individually tunable through several initialization parameters, now they are internally controlled. The script in Source 28 should be used to monitor the overall hit ratio for the data dictionary caches.

REM title:    ddcache.sql
REM FUNCTION: report on the v$rowcache table
REM HISTORY:  created sept 1995 MRA
START title80 "DD Cache Hit Ratio"
SPOOL rep_out\&db\ddcache
SELECT (SUM(getmisses)/SUM(gets)) ratio
FROM v$rowcache
PAUSE Press enter to continue

Source 28: Script to Monitor the Data Dictionary Caches

The output from the script in Source 28 is shown in Listing 28.

Date: 11/21/98                                              Page:   1
Time: 02:59 PM               DD Cache Hit Ratio             SYSTEM
                              ORTEST1 database


Listing 28: Example Output From Data Dictionary Script

The ratio reported from the script in Source 28 should always be less than 1. The ratio corresponds to the number of times out of 100 that the database engine sought something from the cache and missed. A dictionary cache miss is more expensive than a data block buffer miss so if your ratio gets near 1 increase the size of the shared pool since the internal algorithm isn't allocating enough memory to the data dictionary caches.

Guideline 8: In any shared pool, if the overall data dictionary cache miss ratio exceeds 1 percent, increase the size of the shared pool.


This is an excerpt from the eBook "Oracle DBA made Simple".

For more details on Oracle database administration, see the "Easy Oracle Jumpstart" by Robert Freeman and Steve Karam.  It?s only $19.95 when you buy it directly from the publisher here.



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