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，這意味著鎖存器將保持更長的時間。 因此，高度等待"鎖定：共享池"可能是由于過度的硬解析，如果發生這種情況，請查看為什么應用程序正在鼓勵這么多的硬解析。
