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的等待。