shared pool latch相关描述

最新推荐文章于 2024-09-04 09:34:26 发布

转载最新推荐文章于 2024-09-04 09:34:26 发布 · 240 阅读

本文介绍Shared PoolLatch在Oracle数据库中的作用及如何减少其竞争。通过避免硬解析、使用Force Matching Signature等方法，可以提高数据库的可伸缩性和吞吐量。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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'
);