SQL> select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event';
Enter value for event: latch: shared pool
old 1: select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event'
new 1: select name, parameter1, parameter2, parameter3 from v$event_name where name like 'latch: shared pool'
NAME PARAMETER1 PARAMETER2 PARAMETER3
------------------------------ --------------- --------------- ---------------
latch: shared pool address number tries
SQL>
Problem Confirmation:问题定位
Significant waits on "latch: shared pool"
Other waits related to shared pool such as library cache waits may also be seen
Overall database performance may be significant
There may be high number of hard parsing
"latch: shared pool"("latch：共享池"):
The shared pool is part of the SGA and it's main components are the library cache and the dictionary cache. The shared pool holds objects such as optimized queries, parsed sqls, security checks, and packages in memory to allow sessions fast access. When the shared pool space is exhausted old entries are aged out to allow new entries. Shared pool space may become exhausted due to small shared pool, or sqls that are not shared, or high impact on the data dictionary. Activity in the shared pool is protected by the shared pool latch which, when held by a session, prevents changes being made to it.
共享池是SGA的一部分，它的主要组件是库缓存和字典缓存。 共享池包含对象，如优化查询，解析的sqls，安全检查和内存中的包，以允许会话快速访问。 当共享池空间耗尽时，旧的条目会超时以允许新的条目。 共享池空间可能由于共享池很小或者不共享的sqls而枯竭，或者对数据字典影响很大。 共享池中的活动受共享池锁存器的保护，共享池锁定器在会话期间阻止对其进行更改。
解决：
1.Tuning the Shared Pool Latch(调整共享池锁存)
争用"锁定：共享池"通常归因于以下一个或多个：
共享池太小了
SQL不被共享（通常是硬解析）
大量使用数据字典（行缓存争用）
1.1 To reduce waits, shared pool activity needs to be tuned as outlined in the following article:为了减少等待，共享池活动需要调整，如下文所述
Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention
消除 Literal SQL
如果你有一个现有的应用程序，你可能没法消除所有的literal SQL，但是你还是得设法消除其中一部分会产生问题的语句。从V$SQLAREA视图可能找到适合转为使用绑定变量的语句。下面的查询列出SGA中有大量相似语句的SQL：
SELECT substr(sql_text, 1, 40) "SQL", count(*), sum(executions) "TotExecs"
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text, 1, 40)
HAVING count(*) > 30
ORDER BY 2;
在10g以上的版本可以用下面的语句:
SET pages 10000
SET linesize 250
column FORCE_MATCHING_SIGNATURE format 99999999999999999999999
WITH c AS
(SELECT FORCE_MATCHING_SIGNATURE, COUNT(*) cnt
FROM v$sqlarea
WHERE FORCE_MATCHING_SIGNATURE != 0
GROUP BY FORCE_MATCHING_SIGNATURE
HAVING COUNT(*) > 20),
sq AS
(SELECT sql_text,
FORCE_MATCHING_SIGNATURE,
row_number() over(partition BY FORCE_MATCHING_SIGNATURE ORDER BY sql_id DESC) p
FROM v$sqlarea s
WHERE FORCE_MATCHING_SIGNATURE IN
(SELECT FORCE_MATCHING_SIGNATURE FROM c))
SELECT sq.sql_text, sq.FORCE_MATCHING_SIGNATURE, c.cnt "unshared count"
FROM c, sq
WHERE sq.FORCE_MATCHING_SIGNATURE = c.FORCE_MATCHING_SIGNATURE
AND sq.p = 1
ORDER BY c.cnt DESC
检查高版本:
SELECT address,
hash_value,
version_count,
users_opening,
users_executing,
substr(sql_text, 1, 40) "SQL"
FROM v$sqlarea
WHERE version_count > 10;
找到占用shared pool 内存多的语句:
SELECT substr(sql_text, 1, 40) "Stmt",
count(*),
sum(sharable_mem) "Mem",
sum(users_opening) "Open",
sum(executions) "Exec"
FROM v$sql
GROUP BY substr(sql_text, 1, 40)
HAVING sum(sharable_mem) > &MEMSIZE;
1.2 For row cache wait, review following note:对于行缓存等待，请查看以下注意事项：
Document 1476670.1 Resolving Issues With Latch Row Cache Object
The shared pool contains a cache of rows from the data dictionary that helps reduce physical I/O on the data dictionary tables. The row cache lock is used primarily to serialize changes to the data dictionary and is waited on when a lock on a data dictionary cache is required. Waits on this event usually indicate some form of DDL occuring, or possibly recursive operations such as storage management and incrementing sequence numbers.
共享池包含数据字典中的行缓存，有助于减少数据字典表上的物理I / O。 行高速缓存锁主要用于序列化对数据字典的更改，并在需要数据字典高速缓存上的锁时等待。 等待这个事件通常会指示某种形式的DDL发生，或者可能是递归操作，如存储管理和递增序列号。
Row Cache Lock
When DDLs execute, they must acquire locks on the row cache in order to access and change the Data Dictionary information. Once the locks are taken then they can be allowed to modify individual rows in the data dictionary. 当DDL执行时，它们必须获取行缓存上的锁才能访问和更改数据字典信息。 一旦获得锁定，就可以允许修改数据字典中的单个行。
Reducing Waits减少等待:
a.The data dictionary resides in the shared pool. If the shared pool is not sized correctly then the data dictionary might not be fully cached. This should be handled automatically with the automatic shared memory tuning feature. The following documents provide more details:数据字典驻留在共享池中。 如果共享池的大小不正确，则数据字典可能没有完全缓存.这应该通过自动共享内存调整功能自动处理.
b.查找正在等待的缓存
SQL> select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event';
Enter value for event: row cache lock
old 1: select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event'
new 1: select name, parameter1, parameter2, parameter3 from v$event_name where name like 'row cache lock'
NAME PARAMETER1 PARAMETER2 PARAMETER3
------------------------------ --------------- --------------- ---------------
row cache lock cache id mode request
select parameter,count,gets,getmisses,modifications from v$rowcache where cache#=&cache_id;
c.Take cache dependent actions:采取缓存相关的行动
DC_SEQUENCES
For DC_SEQUENCES, consider caching sequences using the cache option.
对于DC_SEQUENCES，请考虑使用缓存选项缓存序列。
DC_OBJECTS
Look for any object compilation activity which might require an exclusive lock, blocking other activities
查找任何可能需要独占锁定的对象编译活动，阻止其他活动
DC_SEGMENTS
Contention here is most likely to be due to segment allocation. Investigate what segments are being created at the time.
这里的争议很可能是由于分部分配。 调查当时正在创建哪些segment。
DC_USERS
This may occur if a session issues a GRANT to a user and that user is in the process of logging on to the database. Investigate why grants are being made while the users are active.如果会话向用户发出GRANT并且该用户正在登录到数据库，则可能会发生这种情况。 调查为什么拨款正在进行，而用户是积极的
DC_TABLESPACES
The most likely cause is the allocation of new extents. If extent sizes are set low then the application may constantly be requesting new extents and causing contention. Do you have objects with small extent sizes that are rapidly growing? (You may be able to spot these by looking for objects with large numbers of extents). Check the trace for insert/update activity, check the objects inserted into for number of extents.最可能的原因是分配新的范围。 如果扩展区大小设置得较低，则应用程序可能会不断请求新的扩展区并导致争用。 你有小尺寸的物体在快速增长吗？ （您可以通过查找具有大量范围的对象来查看这些对象）。 检查插入/更新活动的轨迹，检查插入到的范围数量的对象
2.Application Logic应用逻辑
Typically, contention for the shared pool arises when many sessions are hard parsing and looking for space in the library cache (held in the shared pool since Oracle attempts to reuse application code if it has been executed before). The library cache holds the executables of SQL cursors, PL/SQL programs, and Java classes - essentially the application code. If a parsed representation is in the library cache and it can be shared, Oracle will reuse the code (soft parsing). If there is no parsed representation of the sql in the library cache, then Oracle will need to hard parse the sql which means that latches will be held for longer. Thus high waits for "latch: shared pool" can be due to excessive hard parsing and if that is occurring, review why the application is encouraging so many hard parses.
通常，当许多会话难以解析并在库高速缓存中寻找空间时（由于Oracle在尝试重用应用程序代码（如果之前已经执行过），所以共享池的争用会发生）。 库缓存包含SQL游标，PL / SQL程序和Java类的可执行文件 - 实质上是应用程序代码。 如果解析的表示在库缓存中并且可以共享，则Oracle将重新使用该代码（软解析）。 如果在库缓存中没有解析的sql表示，那么Oracle将需要硬解析sql，这意味着锁存器将保持更长的时间。 因此，高度等待"锁定：共享池"可能是由于过度的硬解析，如果发生这种情况，请查看为什么应用程序正在鼓励这么多的硬解析。
文章名称：latch:sharedpool
地址分享：
http://shouzuofang.com/article/poscec.html