Shareable SQL uses bind variables rather than literal values. If an application makes use of literal (unshared) SQL then this can severely limit scalability and throughput. The cost of parsing a new SQL statement is expensive both in terms of CPU and the number of times the library cache and shared pool latches may need to be acquired and released. Even parsing a simple SQL statement may need to acquire a library cache latch twenty or thirty times.
By looking at the V$SQLAREA view it is possible to see which literal statements are good candidates for converting to use bind variables. The following query shows SQL in the SGA where there are a large number of similar statements:
SELECT substr(sql_text,1,50) “SQL”,
count(*),
sum(executions) “TotExecs”
FROM v$sqlarea
WHERE executions < 5
GROUP BY substr(sql_text,1,50)
HAVING count(*) > 30
ORDER BY 2
This query finds statements whose first 50 characters are the same and which have only been executed a few times each and have at least 30 different copies of this SQL in the shared pool. The query may need to be modified if the literals are in the first 50 characters.
There are numerous parameters in the INIT.ORA that can directly impact the efficiency of shared pool usage. For a full accounting of these, refer to MetaLink Note: 62143.1.
Comment