学习笔记:Oracle等待事件 分析shared pool latch事件产生原因和解决办法案例

天萃荷净 运维DBA在巡检时发现shared pool latch等待事件，Oracle等待事件产生原因和解决办法

1.等待事件shared pool latch相关描述

The shared pool latch is used to protect critical operations when allocating 
and freeing memory in the shared pool. 
If an application makes use of literal (unshared) SQL then this can severely 
limit scalability and throughput. The cost of parsing a new SQL statement is 
expensive both in terms of CPU requirements and the number of times the library 
cache and shared pool latches may need to be acquired and released. Before Oracle9, 
there was just one such latch for the entire database to protect the allocation of 
memory in the library cache. In Oracle9, multiple children were introduced to relieve
contention on this resource.

2.等待事件shared pool latch的解决方法

Avoid hard parses when possible, parse once, execute many. 
Eliminate  literal SQL so that same sql is shared by many sessions.
Size the shared_pool adequately to avoid reloads
Use of MTS (shared server option) also greatly influences the shared pool latch. 

查询未绑定sql

--9i
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

3.查询数据库整体解析情况

select to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits, 
to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses, 
to_char(100 * hard / calls, '999990.00') || '%' hard_parses 
from ( select value calls from v$sysstat where name = 'parse count (total)' ), 
( select value hard from v$sysstat where name = 'parse count (hard)' ), 
( select value sess from v$sysstat where name = 'session cursor cache hits' );

参考:Note 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之学习笔记:Oracle等待事件 分析shared pool latch事件产生原因和解决办法案例

本文由大师惜分飞原创分享，网址：http://www.oracleplus.net/arch/565.html

Oracle研究中心

关键词：

Oracle等待事件

分析shared pool latch事件产生原因和解决办法案例