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