shared pool latch相关描述

shared pool latch相关描述

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.

减少shared pool latch方法

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' );
  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值