oracle解析相关的等待事件（Shared Pool latch/library cache: mutex X/cursor: pin S）

落日映苍穹つ 2022-05-28 05:55 460阅读 0赞

Shared Pool/Library Cache Latch Contention  
可能的原因  
sql语句不能被重用，语句没有使用绑定变量，不合适的应用游标缓存大小，频繁的登入或登出，数据库对象上做过ddl操作，共享池太小等  
在会话级别  
V$SESSTAT  
parse time CPU  
parse time elapsed  
Ratio of parse count (hard) / execute count  
Ratio of parse count (total) / execute count  
在游标缓存（V$SQLAREA/V$SQLSTATS）  
高的PARSE\_CALLS / EXECUTIONS  
EXECUTIONS = 1 differing only in literals in the WHERE clause (that is, no bind variables used)  
High RELOADS（高的重载次数）  
High INVALIDATIONS（高的失效次数，ddl等操作造成对象失败需要重新解析）  
Large (> 1mb) SHARABLE\_MEM

共享池或库缓存latch 争用的主要原因是由于解析，有很多技术可以避免没必要的解析。  
1.手工检查只执行次数很少的sql语句是不是相似的。

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>SELECT</code> 
      <code>SQL_TEXT </code> 
      <code>FROM</code> 
      <code>V$SQLSTATS </code> 
      <code>WHERE</code> 
      <code>EXECUTIONS &lt; 4 </code> 
      <code>ORDER</code> 
      <code>BY</code> 
      <code>SQL_TEXT;</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

2.由于很多语句在select部分是一样的，而在where之后条件不一致，根据这种情况，可以截取语句的前半部分区汇总，得到相似的语句。

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>SELECT</code> 
      <code>SUBSTR(SQL_TEXT, 1, 60), </code> 
      <code>COUNT</code> 
      <code>(*)</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>FROM</code> 
      <code>V$SQLSTATS</code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>WHERE</code> 
      <code>EXECUTIONS &lt; 4</code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>GROUP</code> 
      <code>BY</code> 
      <code>SUBSTR(SQL_TEXT, 1, 60)</code> 
     </div> 
     <div> 
      <code>HAVING</code> 
      <code>COUNT</code> 
      <code>(*) &gt; 1;</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

3.查询不同的语句但使用了相同的执行计划，这些语句使用的是字面值而不是绑定变量。

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>SELECT</code> 
      <code>SQL_TEXT</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>FROM</code> 
      <code>V$SQLSTATS</code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>WHERE</code> 
      <code>PLAN_HASH_VALUE </code> 
      <code>IN</code> 
      <code>(</code> 
      <code>SELECT</code> 
      <code>PLAN_HASH_VALUE</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>FROM</code> 
      <code>V$SQLSTATS</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>GROUP</code> 
      <code>BY</code> 
      <code>PLAN_HASH_VALUE</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>HAVING</code> 
      <code>COUNT</code> 
      <code>(*) &gt; 4)</code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>ORDER</code> 
      <code>BY</code> 
      <code>PLAN_HASH_VALUE;</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

检查V$SQLSTATS视图

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>SELECT</code> 
      <code>SQL_TEXT, PARSE_CALLS, EXECUTIONS</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>FROM</code> 
      <code>V$SQLSTATS</code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>ORDER</code> 
      <code>BY</code> 
      <code>PARSE_CALLS;</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

当执行次数和解析次数相近时，这证明这些语句解析频率很高，这些语句应该是调优的对象。  
识别没必要的解析调用，这可能是特定的批处理程序或特定类型的应用做了对多的解析，根据如下查询识别

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>SELECT</code> 
      <code>pa.SID, pa.VALUE </code> 
      <code>"Hard Parses"</code> 
      <code>, ex.VALUE </code> 
      <code>"Execute Count"</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>FROM</code> 
      <code>V$SESSTAT pa, V$SESSTAT ex</code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>WHERE</code> 
      <code>pa.SID = ex.SID</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>AND</code> 
      <code>pa.STATISTIC# =</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>(</code> 
      <code>SELECT</code> 
      <code>STATISTIC# </code> 
      <code>FROM</code> 
      <code>V$STATNAME </code> 
      <code>WHERE</code> 
      <code>NAME</code> 
      <code>= </code> 
      <code>'parse count (hard)'</code> 
      <code>)</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>AND</code> 
      <code>ex.STATISTIC# =</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>(</code> 
      <code>SELECT</code> 
      <code>STATISTIC# </code> 
      <code>FROM</code> 
      <code>V$STATNAME </code> 
      <code>WHERE</code> 
      <code>NAME</code> 
      <code>= </code> 
      <code>'execute count'</code> 
      <code>)</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;</code> 
      <code>AND</code> 
      <code>pa.VALUE &gt; 0;　　</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

硬解析的测试

代码如下：

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> 
    <div>
      14 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>create</code> 
      <code>or</code> 
      <code>replace</code> 
      <code>procedure</code> 
      <code>p1 </code> 
      <code>as</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>v_cursor number;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>v_sql&nbsp;&nbsp;&nbsp; varchar2(100);</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>v_stat&nbsp;&nbsp; number;</code> 
     </div> 
     <div> 
      <code>begin</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>for</code> 
      <code>x </code> 
      <code>in</code> 
      <code>1 .. 10000 loop</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>v_sql&nbsp;&nbsp;&nbsp; := </code> 
      <code>'insert into t9 values ('</code> 
      <code>|| x || </code> 
      <code>')'</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>v_cursor := dbms_sql.open_cursor; </code> 
      <code>--????????</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>v_stat := dbms_sql.</code> 
      <code>execute</code> 
      <code>(v_cursor);</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>dbms_sql.close_cursor(v_cursor);</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>end</code> 
      <code>loop;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>commit</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>end</code> 
      <code>;</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

开两个会话同时执行调用存储过程p1：（预先查询sid为12,70)　;

查询结果

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>select</code> 
      <code>* </code> 
      <code>from</code> 
      <code>v$session_wait_history&nbsp; </code> 
      <code>where</code> 
      <code>sid </code> 
      <code>in</code> 
      <code>(</code> 
      <code>'12'</code> 
      <code>,</code> 
      <code>'70'</code> 
      <code>)</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

![891887-20161220222000620-530041710.png][]

可见很多都是 shared pool latch的争用 。

与解析相关的其它锁，不是latch  
library cache pin  
该事件管理库缓存争用，pin住一个对象导致heaps 被laods进入内存，如果client想要修改或检查object，client必须获取一个pin在库缓存对象上。  
library cache lock  
这个事件控制clients 的争用，它会获取一个锁在对象句柄上。  
一个clients能阻止其它clients 访问相同的对象。  
客户端能够维护一个长时间的依赖关系不允许其它clients 修改对象。  
这个锁也用来搜索对象在库缓存中。

对软解析的测试

代码如下

<table> 
 <tbody> 
  <tr> 
   <td> 
    <div>
      1 
    </div> 
    <div>
      2 
    </div> 
    <div>
      3 
    </div> 
    <div>
      4 
    </div> 
    <div>
      5 
    </div> 
    <div>
      6 
    </div> 
    <div>
      7 
    </div> 
    <div>
      8 
    </div> 
    <div>
      9 
    </div> 
    <div>
      10 
    </div> 
    <div>
      11 
    </div> 
    <div>
      12 
    </div> 
    <div>
      13 
    </div> 
    <div>
      14 
    </div> 
    <div>
      15 
    </div> 
    <div>
      16 
    </div></td> 
   <td> 
    <div> 
     <div> 
      <code>create</code> 
      <code>or</code> 
      <code>replace</code> 
      <code>procedure</code> 
      <code>p3 </code> 
      <code>as</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>v_cursor number;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>v_sql&nbsp;&nbsp;&nbsp; varchar2(100);</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>v_stat&nbsp;&nbsp; number;</code> 
     </div> 
     <div> 
      <code>begin</code> 
     </div> 
     <div> 
      <code>&nbsp;</code> 
      <code>-- execute immediate 'alter session set session_cached_cursors=1000';</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>v_sql := </code> 
      <code>'insert into t9 values (:a)'</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>for</code> 
      <code>x </code> 
      <code>in</code> 
      <code>1 .. 1000000 loop</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>v_cursor := dbms_sql.open_cursor; </code> 
      <code>--????????</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>dbms_sql.bind_variable(v_cursor, </code> 
      <code>':a'</code> 
      <code>, x);</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>v_stat := dbms_sql.</code> 
      <code>execute</code> 
      <code>(v_cursor);</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;&nbsp;&nbsp;</code> 
      <code>dbms_sql.close_cursor(v_cursor);</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>end</code> 
      <code>loop;</code> 
     </div> 
     <div> 
      <code>&nbsp;&nbsp;</code> 
      <code>commit</code> 
      <code>;</code> 
     </div> 
     <div> 
      <code>end</code> 
      <code>;</code> 
     </div> 
    </div></td> 
  </tr> 
 </tbody> 
</table>

开两个会话同时执行调用存储过程p3：（预先查询sid为12,70)　;

查询结果

![891887-20161220225551448-356589440.png][]

可见软解析几乎都是ibrary cache: mutex X/cursor: pin S的等待。

[891887-20161220222000620-530041710.png]: /images/20220528/3483d00065c748df8a21b4756da7d627.png
[891887-20161220225551448-356589440.png]: /images/20220528/aea068bca9af4f308e707cb0c63ffaef.png