参考ID 62143.1

1：理解Literal SQL

2：Literal SQL versus Shared SQL

当条件谓词是数字时，使用Literal 可以根据histogram statistics产生更精确的执行计划，在DDS中 parse time is small percentage of the exection time.

3:Eliminating Literal 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；
4：SESSION_CACHED_CURSORS parameter

客户发出SQL请求，会首先在SESSION_CACHED(private session cache )查找是否有相同SQL ，省去了SHARED_POOL 的LATCH争用。（建议设置50 再根据应用调优 , The 'session cursor cache hits' in the SESSION_CACHED_CURSORS indiate whether there is a SESSION_CACHED_CURSORS problem）

5 :Finding the Library Cache hit ratio

SELECT SUM(PINS) "EXECUTIONS",

SUM(RELOADS) "CACHE MISSES WHILE EXECUTING",SUM(RELOADS)/SUM(PINS)

FROM V$LIBRARYCACHE;

[@more@]

ITPub博客

shared pool 相关知识点

最新文章