Reduce the Number of SQL Statements
原创maclean_007 ©著作权
©著作权归作者所有:来自51CTO博客作者maclean_007的原创作品,请联系作者获取转载授权,否则将追究法律责任
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.
下一篇:Round-Trip Time
提问和评论都可以,用心的回复会被更多人看到
评论
发布评论
相关文章
-
psql窗口函数 ROW_NUMBER的应用
psql窗口函数的使用
窗口函数 一对多 表结构 -
14.2.2.6 Locks Set by Different SQL Statements in InnoDB
14.2.2.6 Locks Set by ...
排它锁 共享锁 搜索 结果集 事务隔离级别