2013-1-30 - 1-31 11gR2 "Performance Tuning Guide" page 152 - 162

 

 

 

Interpreting Shared Pool Statistics
Allocating Additional Memory to the Data Dictionary Cache Examine cache activity by
monitoring the GETS and GETMISSES columns. For frequently accessed dictionary
caches, the ratio of total GETMISSES to total GETS should be less than 10% or 15%,
depending on the application.
-- 对于dictionary cache 正常的比值应该是GETMISSES/GETS 小于 10%-15%.

Using the Large Pool
Unlike the shared pool, the large pool does not have an LRU list. Oracle Database does
not attempt to age objects out of the large pool.
-- large pool 没有LRU链表.

Enabling the Session Cursor Cache
The following initialization parameters are relevant to the cursor cache:
 -- SESSION_CACHED_CURSORS
This parameter sets the maximum number of cached closed cursors for each
session. The default setting is 50. You can use this parameter to prevent a session
from opening an excessive number of cursors, thereby filling the library cache or
forcing excessive hard parses.
 -- OPEN_CURSORS
This parameter specifies the maximum number of cursors a session can have open
simultaneously. For example, if OPEN_CURSORS is set to 1000, then each session
can have up to 1000 cursors open at one time.

 

Tuning the Session Cursor Cache -- 调整SESSION CURSOR CAHCE参数
To tune the session cursor cache:
1. Determine how many cursors are currently cached in a particular session.
For example, enter the following query for session 35:
-- 查看SESSION35当前的值与最大值

sys@DBS1> SELECT a.value curr_cached, p.value max_cached,
2 s.username, s.sid, s.serial#
3 FROM v$sesstat a, v$statname b, v$session s, v$parameter2 p
4 WHERE a.statistic# = b.statistic# and s.sid=a.sid and a.sid=&sid
5 AND p.name='session_cached_cursors'
6 AND b.name = 'session cursor cache count';
Enter value for sid: 35
old 4: WHERE a.statistic# = b.statistic# and s.sid=a.sid and a.sid=&sid
new 4: WHERE a.statistic# = b.statistic# and s.sid=a.sid and a.sid=35
CURR_CACHED MAX_CACHED USERNAME SID SERIAL#
----------- ---------- -------- ----- ----------
49 50 APP 35 263
The preceding result shows that the number of cursors currently cached for
session 35 is close to the maximum.

 

2. Find the percentage of parse calls that found a cursor in the session cursor cache.
For example, enter the following query for session 35:
SQL> SELECT cach.value cache_hits, prs.value all_parses,
2 round((cach.value/prs.value)*100,2) as "% found in cache"
3 FROM v$sesstat cach, v$sesstat prs, v$statname nm1, v$statname nm2
4 WHERE cach.statistic# = nm1.statistic#
5 AND nm1.name = 'session cursor cache hits'
6 AND prs.statistic#=nm2.statistic#
7 AND nm2.name= 'parse count (total)'
8 AND cach.sid= &sid and prs.sid= cach.sid;
Enter value for sid: 35
old 8: AND cach.sid= &sid and prs.sid= cach.sid
new 8: AND cach.sid= 35 and prs.sid= cach.sid
CACHE_HITS ALL_PARSES % found in cache
---------- ---------- ----------------
34 700 4.57
The preceding result shows that the number of hits in the session cursor cache for
session 35 is low compared to the total number of parses.

 

3. Consider increasing SESSION_CURSOR_CACHE when the following statements are
true: -- 当满足以下条件时，建议增大SESSION_CURSOR_CACHE参数
 -- The session cursor cache count is close to the maximum. -- 参数当前值接近最大值
 -- The percentage of session cursor cache hits is low relative to the total parses. -- 参数HITS与总体解析相比偏低
 -- The application repeatedly makes parse calls for the same queries. -- 对同一条查询语句总是重复解析
In this example, setting SESSION_CURSOR_CACHE to 100 may help boost
performance.

How Similar Statements Can Share SQL Areas -- 类似的语句如何共享SQL AREAS
When CURSOR_SHARING is set to a nondefault value, the database performs the
following steps during the parse:
1. Searches for an identical statement in the shared pool
If an identical statement is found, then the database skips to Step 3. Otherwise, the
database proceeds to the next step.
2. Searches for a similar statement in the shared pool
If a similar statement is not found, then the database performs a hard parse. If a
similar statement is found, then the database proceeds to the next step.
3. Proceeds through the remaining steps of the parse phase to ensure that the
execution plan of the existing statement is applicable to the new statement
If the plan is not applicable, then the database performs a hard parse. If the plan is
applicable, then the database proceeds to the next step.
4. Shares the SQL area of the statement