【每日一摩斯】-Shared Pool优化和Library Cache Latch冲突优化 (1523934.1)-系列3-阿里云开发者社区

开发者社区> 数据库> 正文
登录阅读全文

【每日一摩斯】-Shared Pool优化和Library Cache Latch冲突优化 (1523934.1)-系列3

简介: 减轻Shared Pool负载 Parse一次并执行多次        在OLTP类型的应用中,最好的方法是只让一个语句被解析一次,然后保持这个cursor的打开状态,在需要的时候重复执行它。

减轻Shared Pool负载

Parse一次并执行多次
       在OLTP类型的应用中,最好的方法是只让一个语句被解析一次,然后保持这个cursor的打开状态,在需要的时候重复执行它。这样做的结果是每个语句只被Parse了一次(不管是soft parse还是hard parse)。显然,总会有些语句很少被执行,所以作为一个打开的cursor维护它们是一种浪费。
       请注意一个session最多只能使用参数:open_cursors定义的cursor数,保持cursor打开会增加总体open cursors的数量。
       OCI中开发者能直接控制cursor,在预编译器中,HOLD_CURSOR参数控制cursor是否被保持打开。 

消除 Literal SQL
       如果你有一个现有的应用程序,你可能没法消除所有的literal SQL,但是你还是得设法消除其中一部分会产生问题的语句。从V$SQLAREA视图可能找到适合转为使用绑定变量的语句。下面的查询列出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;

注意:如果系统中有library cache latch争用的问题,上面的语句会导致争用加剧。

避免Invalidations
       有些命令会将cursor的状态变成成INVALIDATE。这些命令直接修改cursor相关对象的上下文环境。它包括TRUNCATE, 表或索引上的ANALYZE或DBMS_STATS.GATHER_XXX,关联对象的权限变更。相对应的cursor会留在SQLAREA中,但是下次被引用时会被完全reload并重新parse,所以会对数据库的整体性能造成影响。

下面的查询可以帮我们找到Invalidation较多的cursor:

SELECT SUBSTR(sql_text, 1, 40) "SQL",
invalidations
FROM v$sqlarea
ORDER BY invalidations DESC;

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章