2013-1-23 - 1-24 11gR2 "Performance Tuning Guide" page 142 - 151

 

 

Shared Pool Concepts
A cache miss on the data dictionary cache or library cache is more expensive than a
miss on the buffer cache. For this reason, the shared pool should be sized to ensure
that frequently used data is cached.
-- 在data dictionary或者library cache的丢失比在buffer cache的丢失还要严重。

 

Dictionary Cache Concepts
Information stored in the data dictionary cache includes usernames, segment
information, profile data, tablespace information, and sequence numbers. The
dictionary cache also stores descriptive information, or metadata, about schema
objects. Oracle Database uses this metadata when parsing SQL cursors or during the
compilation of PL/SQL programs.
-- data dictionary中主要有usernames, segment
information, profile data, tablespace information, and sequence numbers

 

Library Cache Concepts
When application code is run, Oracle Database attempts to reuse existing code if it has
been executed previously and can be shared. If the parsed representation of the
statement does exist in the library cache and it can be shared, then the database reuses
the existing code. This is known as a soft parse, or a library cache hit. If Oracle
Database cannot use existing code, then the database must build a new executable
version of the application code. This is known as a hard parse, or a library cache miss.
-- soft parse与library cache hit,hard parse与library cache miss

 

In order to perform a hard parse, Oracle Database uses more resources than during a
soft parse. Resources used for a soft parse include CPU and library cache latch gets.
Resources required for a hard parse include additional CPU, library cache latch gets,
and shared pool latch gets.
-- soft parse需要消耗的资源包括CPU和library cache latch gets,hard parse需要消耗的资源包括
额外的CPU,library cache latch gets和shared pool latch gets

 

Use of PL/SQL
Using stored PL/SQL packages can overcome many of the scalability issues for
systems with thousands of users, each with individual user sign-on and public
synonyms. This is because a package is executed as the owner, rather than the caller,
which reduces the dictionary cache load considerably.
-- package由拥有者去执行而不是调用者去执行

 

Cursor Access and Management
An application that closes cursors or reuses cursors (for a different SQL statement),
does not need as much session memory as an application that keeps cursors open.
Conversely, that same application may need to perform more parse calls, using extra
CPU and Oracle Database resources.
-- 游标不用的话建议关掉，可以节约SESSION MEMORY。反过来说，就需要额外的其他资源去执行调用。

Shared Pool: Library Cache Statistics
The statistic that shows the amount of reloading (that is, reparsing) of a previously
cached SQL statement that was aged out of the cache is the RELOADS column in the
V$LIBRARYCACHE view. In an application that reuses SQL effectively, on a system
with an optimal shared pool size, the RELOADS statistic will have a value near zero.
-- RELOADS参数越低越好，甚至接近于0

 

The INVALIDATIONS column in V$LIBRARYCACHE view shows the number of times
library cache data was invalidated and had to be reparsed. INVALIDATIONS should
be near zero. This means SQL statements that could have been shared were
invalidated by some operation (for example, a DDL). This statistic should be near zero
on OLTP systems during peak loads.
-- INVALIDATIONS参数越低越好，甚至接近于0

 

V$LIBRARYCACHE
SELECT NAMESPACE, PINS, PINHITS, RELOADS, INVALIDATIONS
FROM V$LIBRARYCACHE
ORDER BY NAMESPACE;

 

To calculate the library cache hit ratio, use the following formula:
Library Cache Hit Ratio = sum(pinhits) / sum(pins)

The amount of free memory in the shared pool is reported in V$SGASTAT. Report the
current value from this view using the following query:
SELECT * FROM V$SGASTAT
WHERE NAME = 'free memory'
AND POOL = 'shared pool';

Shared Pool: Dictionary Cache Statistics

Use the following query to monitor the statistics in the V$ROWCACHE view over a
period while your application is running. The derived column PCT_SUCC_GETS can
be considered the item-specific hit ratio:
column parameter format a21
column pct_succ_gets format 999.9
column updates format 999,999,999
SELECT parameter
, sum(gets)
, sum(getmisses)
, 100*sum(gets - getmisses) / sum(gets) pct_succ_gets
, sum(modifications) updates
FROM V$ROWCACHE
WHERE gets > 0
GROUP BY parameter;

It is also possible to calculate an overall dictionary cache hit ratio using the following
formula; however, summing up the data over all the caches will lose the finer
granularity of data:
SELECT (SUM(GETS - GETMISSES - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;