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 


 

 

 


 

Donald K. Burleson

 

Oracle and Expert Systems Technology


Oracle -
Tuning the Dictionary Cache

The data dictionary cache is used to hold rows from the internal Oracle metadata tables, including SQL stored in packages.  The data dictionary data is maintained in a separate cache called the dictionary cache which is stored in shared SQL area. This is accessed for each SQL statement at parse time and also at runtime for dynamic storage allocation.  Cache hits avoid the necessity for recursive calls and performance on SQL statements improves.

When a package is invoked, Oracle first checks the dictionary cache to see if the package is already in memory. Of course, a package will not be in memory the first time it is requested, and Oracle will register a dictionary cache miss. Consequently, it is virtually impossible to have an instance with no dictionary cache misses, because each item must be loaded once.

The V$ROWCACHE view is used to measure dictionary cache activity. Three columns are of interest: Data Dict., gets, and getmisses. The first column, Data Dict., describes the type of dictionary object that has been requested. The second parameter, gets, provides the total number of requests for objects of that type. The last column, getmisses, counts the number of times Oracle had to perform a disk I/O to retrieve a row from its dictionary tables.

The data dictionary cache hit ratio is used to measure the ratio of dictionary hits to misses. Bear in mind, however, that this ratio is only good for measuring the average hit ratio for the life of the instance.

The data dictionary cache hit ratio can be measured using the script shown below.

dict.sql - displays the dictionary cache hit ratio

PROMPT
PROMPT
PROMPT         =========================
PROMPT         DATA DICT HIT RATIO
PROMPT         =========================
PROMPT (should be higher than 90 else increase shared_pool_size in       
       init.ora)
PROMPT
 

COLUMN "Data Dict. Gets"            FORMAT 999,999,999
COLUMN "Data Dict. cache misses"    FORMAT 999,999,999
SELECT sum(gets) "Data Dict. Gets",
       sum(getmisses) "Data Dict. cache misses",
       trunc((1-(sum(getmisses)/sum(gets)))*100)
       "DATA DICT CACHE HIT RATIO"
FROM V$ROWCACHE;
 

=========================
DATA DICT HIT RATIO
=========================
(should be higher than 90 else increase shared_pool_size in init.ora) 

Fri Feb 23                                                      page    1
                               dbname Database
                          Data Dictionary Hit Ratios 

Data Dict. Gets Data Dict. cache misses DATA DICT CACHE HIT RATIO
-----------------------------------------------------------------
409,288          11,639                  97
 

1 row selected. 

col parameter heading 'Parameter Name'    format a20       justify c trunc
col count     heading 'Entries|Allocated' format 9999990   justify c
col usage     heading 'Entries|Used'      format 9999990   justify c
col gets      heading 'Gets'              format 9999990   justify c
col getmisses heading 'Get|Misses'        format 9999990   justify c
col pctused   heading 'Pct|Used'          format 990.0     justify c
col pctmisses heading 'Pct|Misses'        format 990.0     justify c
col action    heading 'Rec''d|Action'     format a6        justify c 

select
  parameter,
  count,
  usage,
  100*nvl(usage,0)/decode(count,null,1,0,1,count) pctused,
  gets,
  getmisses,
  100*nvl(getmisses,0)/decode(gets,null,1,0,1,gets) pctmisses,
  decode(
    greatest(100*nvl(usage,0)/decode(count,null,1,0,1,count),80),
    80, ' Lower',
    decode(least(100*nvl(getmisses,0)/decode(gets,null,1,0,1,gets),10),
    10, '*Raise', ' Ok')   ) action
from
   v$rowcache
order by   1

prompt
prompt
prompt Rec'd Action is based on maintaining >80% Entries Used and <10% Gets


SQL>@dict
 

Entries  Entries   Pct              Get     Pct   Rec'd
   Parameter Name    Allocated   Used    Used    Gets    Misses  Misses Action
-------------------- --------- -------- ------ -------- -------- ------ ------
dc_column_grants             1        0    0.0        0        0    0.0  Lower
dc_columns               13524    13461   99.5   577710    11741    2.0  Ok
dc_constraint_defs         334      318   95.2      614      318   51.8 *Raise
dc_constraint_defs           1        0    0.0        0        0    0.0  Lower
dc_constraints               8        3   37.5      135       48   35.6  Lower
dc_database_links            7        2   28.6       16        2   12.5  Lower
dc_files                     1        0    0.0        0        0    0.0  Lower
dc_free_extents           2163     1281   59.2   155862     8183    5.3  Lower
dc_histogram_data            1        0    0.0        0        0    0.0  Lower
dc_histogram_defs           25        0    0.0     3699     3699  100.0  Lower
dc_indexes                 179      169   94.4     1810      234   12.9 *Raise
dc_object_ids              155      145   93.5     1405      586   41.7 *Raise
dc_objects                1394     1386   99.4    13688     1784   13.0 *Raise
dc_profiles                  1        0    0.0        0        0    0.0  Lower
dc_rollback_segments        35       33   94.3    11465       32    0.3  Ok
dc_segments                328      313   95.4    14066     1009    7.2  Ok
dc_sequence_grants           1        0    0.0        0        0    0.0  Lower
dc_sequences                14        2   14.3      112        2    1.8  Lower
dc_synonyms                 23       18   78.3       92       18   19.6  Lower
dc_table_grants           4819     4783   99.3    12076     4791   39.7 *Raise
dc_tables                  729      713   97.8    73300     1147    1.6  Ok
dc_tablespace_quotas        23        6   26.1     8957        6    0.1  Lower
dc_tablespaces              13        6   46.2     6853        6    0.1  Lower
dc_tablespaces              19        6   31.6     6053        6    0.1  Lower
dc_truncates                93       86   92.5       86       86  100.0 *Raise
dc_used_extents           4776     4766   99.8    10339     9664   93.5 *Raise
dc_user_grants              53       24   45.3    20321       24    0.1  Lower
dc_usernames                21       20   95.2     5370       20    0.4  Ok
dc_users                    29       27   93.1    22510       27    0.1  Ok
dc_users                     1        0    0.0        0        0    0.0  Lower
 

30 rows selected.

Rec'd Action is based on maintaining >80% Entries Used and <10% Gets


If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.  Click here for more books by Donald K. Burleson.

 

 

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