select name, parameter1, parameter2, parameter3 from v$event_name where name like &event ; Enter value for event: latch: shared pool old &n..."/>
溫馨提示×

您好，登錄后才能下訂單哦！

密碼登錄×
登錄注冊(cè)×
其他方式登錄
點(diǎn)擊 登錄注冊(cè) 即表示同意《億速云用戶服務(wù)條款》

latch: shared pool

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

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

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:對(duì)于行緩存等待，請(qǐng)查看以下注意事項(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。 行高速緩存鎖主要用于序列化對(duì)數(shù)據(jù)字典的更改，并在需要數(shù)據(jù)字典高速緩存上的鎖時(shí)等待。 等待這個(gè)事件通常會(huì)指示某種形式的DDL發(fā)生，或者可能是遞歸操作，如存儲(chǔ)管理和遞增序列號(hào)。

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.
對(duì)于DC_SEQUENCES，請(qǐng)考慮使用緩存選項(xiàng)緩存序列。

DC_OBJECTS
Look for any object compilation activity which might require an exclusive lock, blocking other activities
查找任何可能需要獨(dú)占鎖定的對(duì)象編譯活動(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.
這里的爭(zhēng)議很可能是由于分部分配。 調(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ì)不斷請(qǐng)求新的擴(kuò)展區(qū)并導(dǎo)致爭(zhēng)用。 你有小尺寸的物體在快速增長嗎？  （您可以通過查找具有大量范圍的對(duì)象來查看這些對(duì)象）。 檢查插入/更新活動(dòng)的軌跡，檢查插入到的范圍數(shù)量的對(duì)象

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




向AI問一下細(xì)節(jié)

免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱：is@yisu.com進(jìn)行舉報(bào)，并提供相關(guān)證據(jù)，一經(jīng)查實(shí)，將立刻刪除涉嫌侵權(quán)內(nèi)容。

AI
助
手