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(调整共享池锁存)
争用"锁定:共享池"通常归因于以下一个或多个:
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(调整共享池锁存)
争用"锁定:共享池"通常归因于以下一个或多个: