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的一部分，它的主要組件是庫緩存和字典緩存。 共享池包含對象，如優(yōu)化查詢，解析的sqls，安全檢查和內(nèi)存中的包，以允許會(huì)話快速訪問。 當(dāng)共享池空間耗盡時(shí)，舊的條目會(huì)超時(shí)以允許新的條目。 共享池空間可能由于共享池很小或者不共享的sqls而枯竭，或者對數(shù)據(jù)字典影響很大。 共享池中的活動(dòng)受共享池鎖存器的保護(hù)，共享池鎖定器在會(huì)話期間阻止對其進(jìn)行更改。

解決：
1.Tuning the Shared Pool Latch(調(diào)整共享池鎖存)
爭用"鎖定：共享池"通常歸因于以下一個(gè)或多個(gè)：
     共享池太小了
     SQL不被共享（通常是硬解析）
     大量使用數(shù)據(jù)字典（行緩存爭用）

1.1 To reduce waits, shared pool activity needs to be tuned as outlined in the following article:為了減少等待，共享池活動(dòng)需要調(diào)整，如下文所述
Document 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch Contention
消除 Literal SQL

如果你有一個(gè)現(xiàn)有的應(yīng)用程序，你可能沒法消除所有的literal SQL，但是你還是得設(shè)法消除其中一部分會(huì)產(chǎn)生問題的語句。從V$SQLAREA視圖可能找到適合轉(zhuǎn)為使用綁定變量的語句。下面的查詢列出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 內(nèi)存多的語句:
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:對于行緩存等待，請查看以下注意事項(xiàng)：
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.
共享池包含數(shù)據(jù)字典中的行緩存，有助于減少數(shù)據(jù)字典表上的物理I / O。 行高速緩存鎖主要用于序列化對數(shù)據(jù)字典的更改，并在需要數(shù)據(jù)字典高速緩存上的鎖時(shí)等待。 等待這個(gè)事件通常會(huì)指示某種形式的DDL發(fā)生，或者可能是遞歸操作，如存儲(chǔ)管理和遞增序列號。

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. 當(dāng)DDL執(zhí)行時(shí)，它們必須獲取行緩存上的鎖才能訪問和更改數(shù)據(jù)字典信息。 一旦獲得鎖定，就可以允許修改數(shù)據(jù)字典中的單個(gè)行。

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:數(shù)據(jù)字典駐留在共享池中。 如果共享池的大小不正確，則數(shù)據(jù)字典可能沒有完全緩存.這應(yīng)該通過自動(dòng)共享內(nèi)存調(diào)整功能自動(dòng)處理.

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:采取緩存相關(guān)的行動(dòng)

DC_SEQUENCES
For DC_SEQUENCES, consider caching sequences using the cache option.
對于DC_SEQUENCES，請考慮使用緩存選項(xiàng)緩存序列。

DC_OBJECTS
Look for any object compilation activity which might require an exclusive lock, blocking other activities
查找任何可能需要獨(dú)占鎖定的對象編譯活動(dòng)，阻止其他活動(dòng)

DC_SEGMENTS
Contention here is most likely to be due to segment allocation.  Investigate what segments are being created at the time.
這里的爭議很可能是由于分部分配。 調(diào)查當(dāng)時(shí)正在創(chuàng)建哪些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.如果會(huì)話向用戶發(fā)出GRANT并且該用戶正在登錄到數(shù)據(jù)庫，則可能會(huì)發(fā)生這種情況。 調(diào)查為什么撥款正在進(jìn)行，而用戶是積極的

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.最可能的原因是分配新的范圍。 如果擴(kuò)展區(qū)大小設(shè)置得較低，則應(yīng)用程序可能會(huì)不斷請求新的擴(kuò)展區(qū)并導(dǎo)致爭用。 你有小尺寸的物體在快速增長嗎？  （您可以通過查找具有大量范圍的對象來查看這些對象）。 檢查插入/更新活動(dòng)的軌跡，檢查插入到的范圍數(shù)量的對象

2.Application Logic應(yīng)用邏輯
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.
通常，當(dāng)許多會(huì)話難以解析并在庫高速緩存中尋找空間時(shí)（由于Oracle在嘗試重用應(yīng)用程序代碼（如果之前已經(jīng)執(zhí)行過），所以共享池的爭用會(huì)發(fā)生）。 庫緩存包含SQL游標(biāo)，PL / SQL程序和Java類的可執(zhí)行文件 - 實(shí)質(zhì)上是應(yīng)用程序代碼。 如果解析的表示在庫緩存中并且可以共享，則Oracle將重新使用該代碼（軟解析）。 如果在庫緩存中沒有解析的sql表示，那么Oracle將需要硬解析sql，這意味著鎖存器將保持更長的時(shí)間。 因此，高度等待"鎖定：共享池"可能是由于過度的硬解析，如果發(fā)生這種情況，請查看為什么應(yīng)用程序正在鼓勵(lì)這么多的硬解析。

網(wǎng)站欄目：latch:sharedpool
文章起源：http://jinyejixie.com/article42/poscec.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供全網(wǎng)營銷推廣、小程序開發(fā)、搜索引擎優(yōu)化、ChatGPT、面包屑導(dǎo)航、靜態(tài)網(wǎng)站

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)