当前位置:Oracle研究中心 > 运维DBA > Oracle等待事件 >
时间:2016-06-26 22:16 来源:Oracle研究中心 作者:惜分飞 点击: 次
The shared pool latch is used to protect critical operations when allocating and freeing memory in the shared pool. 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 requirements and the number of times the library cache and shared pool latches may need to be acquired and released. Before Oracle9, there was just one such latch for the entire database to protect the allocation of memory in the library cache. In Oracle9, multiple children were introduced to relieve contention on this resource.
Avoid hard parses when possible, parse once, execute many. Eliminate literal SQL so that same sql is shared by many sessions. Size the shared_pool adequately to avoid reloads Use of MTS (shared server option) also greatly influences the shared pool latch.
查询未绑定sql
--9i 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
select to_char(100 * sess / calls, '999999999990.00') || '%' cursor_cache_hits, to_char(100 * (calls - sess - hard) / calls, '999990.00') || '%' soft_parses, to_char(100 * hard / calls, '999990.00') || '%' hard_parses from ( select value calls from v$sysstat where name = 'parse count (total)' ), ( select value hard from v$sysstat where name = 'parse count (hard)' ), ( select value sess from v$sysstat where name = 'session cursor cache hits' );
参考:Note 62143.1 Troubleshooting: Tuning the Shared Pool and Tuning Library Cache Latch
--------------------------------------ORACLE-DBA----------------------------------------
最权威、专业的Oracle案例资源汇总之学习笔记:Oracle等待事件 分析shared pool latch事件产生原因和解决办法案例
本文由大师惜分飞原创分享,网址:http://www.oracleplus.net/arch/565.html
Powered by Oracle研究中心 OraclePlus Inc.
BY DedeCMS 京ICP备14022373号-5
声明:Oracle研究中心部分文章源自互联网,转载均按原文作者要求转载,如有疑问联系QQ:1026002015,协商未果24小时删除。