Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

   
 

Oracle v$librarycache script

Chapter 2 Inside the Oracle Shared Pool

By monitoring not only disk reads, but other performance characteristics such as CPU and total elapsed time, the DBA gets a full picture of SQL code usage and problem areas. Notice how the script calculates the average value per execution for the aggregated values stored in v$sqlarea.

Monitoring Library and Data Dictionary Caches

Much of this chapter has discussed 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 areas for all data dictionary related definitions.

The script below generates a report on the library caches. The items of particular interest in the report are the various ratios, which are shown further down.

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

Look at the example output below. All Get Hit% (gethitratio in the view) are greater than 80-90 percent.

This is the desired range. Notice that the Pin Hit% is also greater than 90%. This is also to be desired.

Library Object       Gets Get Hit%         Pins Pin Hit%  Reloads Invalid
--------------- --------- -------- ------------ -------- -------- -------
SQL AREA        5,628,059    98.31   28,080,373    99.26   20,566  20,322
TABLE/PROCEDURE 7,140,597    99.15   16,876,602    97.79   74,252       0
BODY              263,096    99.75      259,497    93.32   16,645       0
TRIGGER             5,288    96.41        5,342    92.25      147       0
INDEX             530,725    97.12      530,685    94.40        0       0
CLUSTER             3,967    98.89        4,266    98.05        0       0
OBJECT                  0   100.00            0   100.00        0       0
PIPE              792,503   100.00      792,578   100.00        0       0
 
8 rows selected.

The other tuning goals 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, 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

If no flushing is used, increase the shared pool size to reduce reloads and invalidations and increase hit ratios.

The data dictionary caches used to be tuned individually through several initialization parameters. Now they are internally controlled. The script below can be used to monitor the overall hit ratio for the data dictionary caches.

ddcache.sql

ttitle "DD Cache Hit Ratio"
spool ddcache
SELECT
   (SUM(getmisses)/SUM(gets))*100 RATIO
FROM
   v$rowcache;
spool off
pause Press enter to continue
ttitle off

Here is the output from the script above

    RATIO
---------
 1.273172

The reported ratio should always be less than 1. The ratio corresponds to the number of times out of 100 that the database engine searched the cache without finding anything. A dictionary cache miss is more expensive than a data block buffer miss, so if the ratio approaches 1, increase the size of the shared pool. If the ratio is close to 1, the internal algorithm isn't allocating enough memory to the data dictionary caches.

 

 



 

The Oracle script collection contains more than 600 working Oracle scripts.  You can download them immediately at this link:
 


 

Linux Oracle commands syntax poster

ION Oracle tuning software

Oracle data dictionary reference poster



Oracle Forum

BC Oracle consulting support training

BC remote Oracle DBA   

 

   

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


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks