减轻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;