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




Disk I/O and the Shared Pool

Oracle Tips by Burleson

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. Numerous statistics are 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 and v$sqlarea DPTs. Let’s look at a report that displays the SQL statements in the SQL area with the greatest number of disk reads (these will probably be the ones you will want to review and tune). Look at the report in Source 13.10. Output from this report is shown in Listing 13.11.

SOURCE 13.10 SQL versus disk reads report.

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’
COLUMN sql_text        FORMAT a40         HEADING ‘Statement’ WORD_WRAPPED
START title132 'SQL Statements With High Reads'
SPOOL rep_out/&db/sqldrd.lis
     parsing_user_id, executions,
     disk_reads > &&access_level

LISTING 13.11 Example SQLDRD.SQL output.

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

Id   Exec Sorts CmdT Block Reads Statement
---- ---- ----- ---- ----------- --------------------------------------
0  403     0    3        1111 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        1104 select order#,columns,types from
                                 access$ where d_obj#=:1         

0   12     0    3         912 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;               

By tuning those statements that show large numbers of disk reads, the overall performance of the application is increased.

Monitoring Library and Data Dictionary Caches

I've spent most of this section discussing 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, which 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.

Source 13.11 creates a report on the library caches. The items of particular interest in the report generated by Source 13.11 (shown in Listing 13.12) are the various ratios.

SOURCE 13.11 Library caches report.

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
set pages 58 lines 80
start title80 "Library Caches Report"
define output = rep_out\&db\lib_cache
spool &output
select  namespace, gets, gethitratio*100 gethitratio,
       pins, pinhitratio*100 pinhitratio, RELOADS,
from v$librarycache;
spool off
pause Press enter to continue
set pages 22 lines 80
ttitle off
undef output

In Listing 13.12 we see that all Get Hit% (gethitratio in the view), except for indexes, are greater than 80 to 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 percent (except for indexes); this is also desirable. The other goals of tuning this area are to reduce reloads to 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 6: In a system where there is no flushing, increase the shared-pool size to reduce reloads and invalidations and to increase hit ratios.

LISTING 13.12 Example library caches report.

Date: 11/08/01                                              Page:   1
Time: 04:57 PM             Library Caches Report            DBAUTIL
                              aultdb1 database 

Library Object     Gets Get Hit%         Pins Pin Hit%  Reloads Invalid
--------------- ------- -------- ------------ -------- -------- -------
SQL AREA          4,523    94.10       19,395    97.91       13       0
TABLE/PROCEDURE   5,835    80.19        3,423    50.16        0       0
BODY                 22    63.64           21    57.14        0       0
TRIGGER               1      .00            1      .00        0       0
INDEX                29      .00           29      .00        0       0
CLUSTER             174    96.55          234    97.01        0       0
OBJECT                0   100.00            0   100.00        0       0
PIPE                  0   100.00            0   100.00        0       0
JAVA SOURCE           0   100.00            0   100.00        0       0
JAVA RESOURCE         2    50.00            2    50.00        0       0
JAVA DATA             1      .00            6    66.67        0       0

Formerly, the data dictionary caches were individually tunable through several initialization parameters; now they are internally controlled. The report in Source 13.12 can be used to monitor the overall hit ratio for the data dictionary caches. The output from Source 13.12 is shown in Listing 13.13.

SOURCE 13.12 Monitoring Data Dictionary hit ratio.

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))*100 RATIO
FROM   v$rowcache;
spool off
pause Press enter to continue
ttitle off

LISTING 13.13 Sample  Data Dictionary hit ratio report.

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


The ratio reported from the script in Source 13.12 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 7: In any shared pool in the data dictionary cache ratio is greater than 1.0, increase the size of the shared pool.

Using these guidelines and the scripts and techniques covered in this section, you should be well on the way toward achieving a well-tuned and well-performing shared pool.


See Code Depot for Full Scripts

This is an excerpt from Mike Ault, bestselling author of "Oracle 10g Grid and Real Application Clusters".

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

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. 

Hit Counter