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

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语句是不是相似的。

1	SELECT SQL_TEXT FROM V$SQLSTATS WHERE EXECUTIONS < 4 ORDER BY SQL_TEXT;

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

1 2 3 4 5

SELECT SUBSTR(SQL_TEXT, 1, 60), COUNT (*)    FROM V$SQLSTATS   WHERE EXECUTIONS < 4   GROUP BY SUBSTR(SQL_TEXT, 1, 60) HAVING COUNT (*) > 1;

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

1 2 3 4 5 6 7

SELECT SQL_TEXT    FROM V$SQLSTATS   WHERE PLAN_HASH_VALUE IN ( SELECT PLAN_HASH_VALUE                               FROM V$SQLSTATS                              GROUP BY PLAN_HASH_VALUE                             HAVING COUNT (*) > 4)   ORDER BY PLAN_HASH_VALUE;

检查V$SQLSTATS视图

1 2 3

SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS    FROM V$SQLSTATS   ORDER BY PARSE_CALLS;

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

1 2 3 4 5 6 7 8

SELECT pa.SID, pa.VALUE "Hard Parses" , ex.VALUE "Execute Count"    FROM V$SESSTAT pa, V$SESSTAT ex   WHERE pa.SID = ex.SID     AND pa.STATISTIC# =         ( SELECT STATISTIC# FROM V$STATNAME WHERE NAME = 'parse count (hard)' )     AND ex.STATISTIC# =         ( SELECT STATISTIC# FROM V$STATNAME WHERE NAME = 'execute count' )     AND pa.VALUE > 0;

硬解析的测试 

代码如下：

1 2 3 4 5 6 7 8 9 10 11 12 13 14

create or replace procedure p1 as    v_cursor number;    v_sql    varchar2(100);    v_stat   number; begin    for x in 1 .. 10000 loop      v_sql    := 'insert into t9 values (' || x || ')' ;      v_cursor := dbms_sql.open_cursor; --????????      dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);      v_stat := dbms_sql. execute (v_cursor);      dbms_sql.close_cursor(v_cursor);    end loop;    commit ; end ;

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

查询结果 

1	select * from v$session_wait_history  where sid in ( '12' , '70' )

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

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

对软解析的测试

代码如下

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

create or replace procedure p3 as    v_cursor number;    v_sql    varchar2(100);    v_stat   number; begin   -- execute immediate 'alter session set session_cached_cursors=1000';    v_sql := 'insert into t9 values (:a)' ;    for x in 1 .. 1000000 loop      v_cursor := dbms_sql.open_cursor; --????????      dbms_sql.parse(v_cursor, v_sql, dbms_sql.native);      dbms_sql.bind_variable(v_cursor, ':a' , x);      v_stat := dbms_sql. execute (v_cursor);      dbms_sql.close_cursor(v_cursor);    end loop;    commit ; end ;

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

查询结果 

1	select * from v$session_wait_history  where sid in ( '12' , '70' )

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