shared pool latch 等待事件-优快云博客

本文介绍了Oracle数据库中Shared Pool Latch的作用及优化方法，包括减少硬解析、消除字面SQL等，并提供了查询未绑定SQL及整体解析情况的SQL语句。

摘要生成于 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' 
         ); 
       
 
 