参考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博客 ” ,链接:http://blog.itpub.net/9225895/viewspace-1030718/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9225895/viewspace-1030718/