标题: ORA-445 and Heavy 'latch: shared pool' and 'row cache *' Waits; RAC may Hang [打印本页] 作者: 刘泽宇 时间: 2023-10-29 11:28 标题: ORA-445 and Heavy 'latch: shared pool' and 'row cache *' Waits; RAC may Hang 现象：
The first symptoms of the problem may be seeing the following in the alert log, although there may also be messages about not being able to attach to an incoming connection, etc. Eventually ORA-445 will be seen.
CLMN failed to acquire latch
ORA-00445: background process "<process name>" did not start after 120 seconds <--- this might be any process
You may see high 'latch: shared pool' and/or 'row cache mutex' waits, and a RAC instance may hang. If you have dynamic connection pooling configured and it's configured for more than 10 * <number of physical CPUs> on the node, you may also have a connection storm.
变动：
Upgrade to 19c, and often 19.17 or higher.
原因：
Per unpublished Bug "35346203 - ORA-00445 AND LIBRARY CACHE CAUSING INSTANCE AND RAC DATABASE HANG, which was opened explicitly for this issue, the causes are several:
- Unpublished Bug 34304965 - TRACKING BUG FOR KQR FIXES FOR 19.18 RU. <--- this is the main one
- Unpublished Bug 34106320 - SHARED POOL RESERVED MEMORY USED BY SMALL PERMANENT ALLOCATIONS
- Unpublished Bug 34832725 - ORA-4031 "KSU:STATS_FREELIST" AND "KGLSESHTTABLE" ERRORS IN 19C.
处理方法：
Apply patches for the following bugs:
- Patch 34832725 - ORA-4031 "KSU:STATS_FREELIST" AND "KGLSESHTTABLE" ERRORS IN 19C (fix)
- Patch 34304965 - TRACKING BUG FOR KQR FIXES FOR 19.18 RU (fix)
- Patch 34106320 - SHARED POOL RESERVED MEMORY USED BY SMALL PERMANENT ALLOCATIONS (fix)
- Patch 29768682 - AUTOMATICALLY TRIGGER 4031 SUMMARY REPORT ON LATCH CONTENTION (proactive diagnostic)
- Patch 33780182 - IMPLEMENT MECHANISM TO BETTER TRACK USE OF RESERVED SHARED POOL (proactive diagnostic)
- Patch 34958073 - IMPLEMENT A MECHANISM TO PROVIDE DETAILS ABOUT LRU SCAN EFFORT (proactive diagnostic)
Make sure your shared_pool_reserved_size is at least 10% of your shared_pool_size; adjust shared_pool_size if necessary to accommodate the increase (requires a bounce of the database).
Other actions recommended by Support:
- Change connection pooling to a static pool of no more than 10 * <number of physical CPUs> on each node to avoid connection storms:
- Configure connection pooling (configure for PRCP)
- Connection pooling -- programming requirements