shared pool latch和library cache latch

shared pool latch和library cache latch

  


  1. >        
  2.   
  3.          
  4.   
  5.          
  1. >          
  2.   
  3.                                                                            
  4.        
  5.                                                                              
  6.                                                                                  
  7.                                                                                  
  8.                                                                                  
  9.                                                                                  
  10.                                                                                  
  11.                                                                                  
  12.   
  13. >       
  14.       
  15.        
  1. >        
  1. >     
  2.             
  3. >      
  4.    
  5. >    
  6.   
  1.     
  2.                 
  3.     
  4.            
  5.             
  6.                          
  7.            
  8.                    
  9.                        
  10.                      
  11.                    
  12.                        
  13.          
  14.                   
  15.               
  16.                 
  17.                   
  18.               
  19.            
  20.                      
  21.                 
  22.                 
  23.                 
  24.                 
  25.               
  26.                     
  27.                  
  28.             
  29.                   
  30.                      
  31.               
  32.            

  1.       
  2.          
  3.         
  4.   
  5.         
  6.      
  7.      
  1.     
  2.      
  3.     
  4.      
  5.     
  6.      
  7.     
  8.      
  9.     
  10.      
  11.     
  12.      
  13.    
  14.          
  15.      
  16.     
  17.      
  18.     
  19.      
  20.     
  21.      
  22.     
  23.      
  24.     
  25.      
  26.    
  27.   
  28.    
  29.   
  1. >        
  1.        
  2.     
  3.        
  4.           
  5.        
  6.        
  7.           
  8.     
  9.          
  10.      
  11.                          
  12.                          
  13.              
  1.        
  2.   
  3.   
  4.        
  5.     
  6.        
  7.           
  8.        
  9.        
  10.           
  11.     
  12.          
  13.      
  14.                          
  15.                          
  16.              
  17.    
  18.   
  19.   
  20.        
  21.      
  22.                          
  23.                          
  24.              
  25.    
  26.   
  27.   
  28.        
  29.      
  30.                          
  31.                          
  32.              
  33.    
  34.   
  35.   
  36.        
  37.      
  38.                          
  39.                          
  40.              
  41.    
  42.   
  43.   
  44.        
  45.       
  46.                          
  47.                          
  48.              
  49.    
  50.   
  51.   
  52.        
  53.       
  54.                          
  55.                          
  56.              
  57.    
  58.   
  59.   
  60.        
  61.       
  62.                          
  63.                          
  64.              
  65.    
  66.       
  67.   
  68.                 
  1. >       
  2.        
  3.                       
  4.       
  5.                                 
  6.                                 
  7.                                 
  8.                                 
  9.                                 
  10.                                 
  11.                                 
  12.                                 
  13.                                 
  14.                                 
  1.        
  2.         
  3.        
  4.        
  5.         
  6.         

 
 

 

 

 

  1.   

    >


     

  2.   


     

  3.   


     

  4.   

  5.   

    >>


     

  6.   
  7.   


     

         

>

                                                                             

     

                  

>

                                                   

                                                     

                                                         

                                                         

                                                         

                                                         

                                                         

                                                         

                                                    

                                                    

                                                     

                                                

                                                 

                                                 

                                                 

                                                 

                                                  

                                           

                                            

                                           

                                          

                                           

                                          

                                              

                                              

                                              

>

>

>

       

       

>

>

>

>

  

>

>

>

                 

    

 

     

  

  

  

            

  

              

              

              

              

              

    

        

        

      

        

        

        

          

           

           

           

           

          

          

      

          

        

        

        

    

    

    

    

>

>

>

  

>

  

>

  

>

>

>

                 

    

 

                 

    

 

     

  

  

  

            

  

              

              

              

              

              

    

        

        

      

        

        

        

          

           

           

           

          

          

      

    

    

    

                 

    

 

     

  

  

  

            

  

              

              

              

              

              

    

        

        

      

        

        

        

          

           

           

           

          

          

      

    

    

    

>

>

                                         

                    

>

                               

                                                 

                                 

                        

                         

                                

                      

                                

                                

                               

                                  

                          

                                 

                                  

>

                                                                            

                                                                                 

>

                 

>

       

       

>

>

>

>

  

>

  

>

  

>

>

>

                 

    

  

  

  

               

  

                        

                  

                 

    

 

     

  

  

  

            

  

              

              

              

              

              

    

        

        

      

        

        

        

          

           

           

          

          

      

    

    

>

                               

                                                 

                                 

                        

                         

                                

                      

                                

                                

                               

                                  

                          

                                 

                                  

>

                                                                                 

                                                                                 

                 

    

 

     

  

  

  

            

  

              

              

              

              

              

    

        

        

      

        

        

        

          

           

           

           

           

           

           

           

          

          

      

    

    

 

  5. >

    >

    >

    >

    >

    >

    >

    >

    >

    >

    >

           

 

 

 

  

   

 

  

 

  

           

            

 

  

    

 

>

 

                                                                    

                                                                                        

                                                                                             

                                                                                      

                                                                                               

                                                                                               

                                                                                        

                                                                                             

 

 

        

        

 

           

          

 

>

 

                                                                    

                                                                                        

                                                                                             

                                                                                      

                                                                                               

                                                                                               

                                                                                        

                                                                                             

 

 

>

 

                                 

 

>

 

                                                                    

                                                                                        

                                                                                             

                                                                                      

                                                                                               

                                                                                               

                                                                                        

                                                                                             

 

 

>

 

                                         

                                 

 

>

 

                                                                    

                                                                                               

                                                                                                

                                                                                         

                                                                                              

                                                                                              

                                                                                               

                                                                                               

                                                                                                  

                                                                                                 

                                                                                        

                                                                                         

                                                                                        

                                                                                       

                                                                                       

                                                                                      

                                                                                               

                                                                                               

                                                                                               

 

 

         

 

          

 

    

 

>  

 

                                                                                  

                                                                                                              

 

>

 

                                                                                  

 

  

  

  

  

 

 

    

 

>

 

                                 

                   

 

>

 

                               

                                                 

                                  

                         

 

                                   

 

 

>

 

  

 

>

 

  

 

                         

                           

                           

                            

                    

                    

                     

                            

 

 

  

>

 

                    

 

>

> 

 

>

 

                               

                                                 

                                  

                         

 

                                   

 

 

>

 

        

       

         

        

      

       

 

>

 

 

>

 

 

 

 

>

 

>

 

 

  

                  

             

          

                

 

>

 

 

>

>

>

 

                               

                                                 

                                  

                         

 

                                   

 

 

>

 

       

         

        

       

         

         

      

      

 

>

 

 

 

 

    

 

>

 

                                 

                    

 

>

 

                              

                                            

                                    

                                           

 

 

>

 

  

 

>

>

 

>

 

                              

                                            

                                    

                                           

 

 

>

 

   

    

      

        

        

        

 

        

       

       

      

      

      

     

 

>

 

                  

  

 

 

 

 

 

  

 

 

   

 

>

>

 

  

                  

              

          

                

 

>

>

 

>

 

                              

                                            

                                    

                                           

 

 

>

 

   

    

      

        

        

        

 

>

  

      

       

          

         

      

       

          

         

      

     

      

     

 

>

 

                  

 

 

 

    
时间： 2024-10-25 13:38:40

shared pool latch和library cache latch的相关文章

[转载】——故障排除：Shared Pool优化和Library Cache Latch冲突优化 (文档 ID 1523934.1)

原文链接:https://support.oracle.com/epmos/faces/DocumentDisplay?_adf.ctrlstate=23w4l35u5_4&id=1523934.1用途   提出问题,得到帮助并分享您的心得   排错步骤   什么是shared pool?   专用术语   Literal SQL   Hard Parse(硬解析)   Soft Parse(软解析)   完全相同的语句?   Sharable SQL   语句的版本   Library Cac

【每日一摩斯】-Shared Pool优化和Library Cache Latch冲突优化 (1523934.1)-系列4

CURSOR_SHARING 参数 (8.1.6 以上)        这个参数需要小心使用.如果它被设为FORCE,那么Oracle会尽可能用系统产生的绑定变量来替换原来SQL中的literals部分.对于很多仅仅是literal不一样的相似的语句,这会让它们共享cursor.这个参数可以在系统级别或者session级别动态设置: ALTER SESSION SET cursor_sharing = FORCE; 或者 ALTER SYSTEM SET cursor_sharing = FOR

【每日一摩斯】-Shared Pool优化和Library Cache Latch冲突优化 (1523934.1)-系列5

Flushing(清空) SHARED POOL        在使用大量literal SQL的系统中,shared pool随时间推移会产生大量碎片进而导致并发能力的下降.Flushing shared pool能够使得很多小块碎片合并,所以经常能够在一段时间内恢复系统的性能.清空之后可能也会产生短暂的性能下降(补充:因为需要做第一次的硬解析),因为这个操作同时也会把没造成shared pool碎片的共享SQL也清除了.清空shared pool的命令是: ALTER SYSTEM FLUS

【每日一摩斯】-Shared Pool优化和Library Cache Latch冲突优化 (1523934.1)-系列2

下面来谈一谈系列1中讲到的Literal SQL和Shared SQL的比较. 首先是Literal SQL: 在有完整的统计信息并且SQL语句在predicate(限定条件)中使用具体值时,基于成本的优化器 (CBO)能工作的最好.比较下面 的语句: SELECT distinct cust_ref FROM orders WHERE total_cost < 10000.0; 和 SELECT distinct cust_ref FROM orders WHERE total_cost <

【每日一摩斯】-Shared Pool优化和Library Cache Latch冲突优化 (1523934.1)-系列3

减轻Shared Pool负载 Parse一次并执行多次        在OLTP类型的应用中,最好的方法是只让一个语句被解析一次,然后保持这个cursor的打开状态,在需要的时候重复执行它.这样做的结果是每个语句只被Parse了一次(不管是soft parse还是hard parse).显然,总会有些语句很少被执行,所以作为一个打开的cursor维护它们是一种浪费.        请注意一个session最多只能使用参数:open_cursors定义的cursor数,保持cursor打开会增加

【每日一摩斯】-Shared Pool优化和Library Cache Latch冲突优化 (1523934.1)-系列6

使用SQL 查看Shared Pool问题        这一章节展示了一些可以用来帮助找到shared pool中的潜在问题的SQL语句.这些语句的输出最好spool到一个文件中. 注意:这些语句可能会使latch竞争加剧,我们在上面的"使用 V$ 视图 (V$SQL 和 V$SQLAREA)" above. 查找literal SQL SELECT substr(sql_text,1,40) "SQL",                count(*) ,  

【每日一摩斯】-Shared Pool优化和Library Cache Latch冲突优化 (1523934.1)-系列1

什么是Shared Pool?        Oracle的实例主要包括共享内存(主要是SGA,还有PGA)和Background Processes,其中SGA中又包括了Shared Pool.Buffer Cache.Redo Log Buffer以及其它一些内存区.        Oracle在SGA的一个特定区域中保留SQL语句.Package是.对象信息以及其它一些内容,这就是Shared Pool.这个共享内存区域是由一个复杂的cache和heap manager 构成的.它需要解决

shared pool latch/ library cache latch /lock pin介绍

latch:library cache --desc v$librarycache; latch:library cache用于保护hash bucket. library cache lock保护HANDLE. library cache pin保护library cache object--LCO. 从10G开始,library cache lock和library cache pin被MUTEX部分取代.暂时不讨论MUTEX. latch:library cache的数量: SYS@ by

Oracle Kernel : Function kglic &amp; Library Cache Latch

以下是关于KGLIC函数的功能简要说明:  代码如下 复制代码 1. kglic means Kernel Generic Library cache Iterate Chain (AFAIK), it's the function which is executed when you access most X$KGL tables. 2. kglic is the code which goes through the  library cache and row cache to answ