一条查询语句导致Oracle latch free session allocation wait 等待

问题描述：

出现大量的latch free等待，系统严重阻塞。
该latch为p2=4号session allocation等待。应用报错ORA-00018,无 法建立新的连接。

问题分析：
 

1) 查看OS相关资源指标，未发现异常。

2)  比较14:51 以及14:52 Listener .log ，主要是以下机器连接进来， 也可以看出14:52连接数明显明大，可否找应用看看这些个连接是不是都是正常的？

点击(此处)折叠或打开

1.   (个数      时间)
   
2.   82    29-AUG-2013 14:49
   
3.   72    29-AUG-2013 14:50
   
4.   50    29-AUG-2013 14:51
   
5. 622     29-AUG-2013 14:52               <<<<<<<<<<<<<<
   
6. 492     29-AUG-2013 14:53               <<<<<<<<<<<<<<<
   
7. 266     29-AUG-2013 14:54
   
8.   29    29-AUG-2013 14:55
   
9.   46    29-AUG-2013 14:56
   
10.   37    29-AUG-2013 14:57
    
11.   34    29-AUG-2013 14:58
    
12.   29    29-AUG-2013 14:59

3) 数据库当时抓取到大量latch#=4 即session allocation 的latch free等待，

大量新建的连接都等待在这个event上， 顾名思义，当时认为这个latch只是session在新建连接的时候需要申请的。

Session allocation Latch 每天都 有，检查其历史记录，之前也发生过，只是之前不太严重而已，我们也想知道每天产生的时间有没有规律，

点击(此处)折叠或打开

1. TRUNC(SNAP_TIME)   COUNT(*)
   
2. ---------------- ----------
   
3. 2013/8/1                 39
   
4. 2013/8/2                 24
   
5. 2013/8/3                 27
   
6. 2013/8/4                 16
   
7. 2013/8/5                 25
   
8. 2013/8/6                 49
   
9. 2013/8/7                 30
   
10. 2013/8/8                 33
    
11. 2013/8/9                 23
    
12. 2013/8/10                24
    
13. 2013/8/11                13
    
14. 2013/8/12                28
    
15. 2013/8/13                39
    
16. 2013/8/14                29
    
17. 2013/8/15                32
    
18. 2013/8/16                54
    
19. 2013/8/17                23
    
20. 2013/8/18                16
    
21. 2013/8/19                17
    
22. 2013/8/20                52
    
23. 2013/8/21                35
    
24. 2013/8/22                57
    
25. 2013/8/23                62
    
26. 2013/8/24                22
    
27. 2013/8/25                10
    
28. 2013/8/26                19
    
29. 2013/8/27                28
    
30. 2013/8/28                24
    
31. 2013/8/29              7361

4) 经过对v$session, v$session_wait的采集，发现有个异常的会话，持有了control file sequential read。 随之伴随应用用户产生了大量session allocation的等待，进而导致session数冲满。

后续dba的查询sql退出后，异常等待消失。当再次执行这个语句时，问题就重现了。

点击(此处)折叠或打开

1. select d.name||'-----',sum(s.bytes)/1024/1024/1024 from v$database d, dba_segments s group by d.name||'-----';

5） v$database的视图访问基表x$kccdi，这个视图是从control file中读取，从而导致control file sequential read的次数。由于该语句执行计划异常，导致先访问dba_segments表，然后再进行nested loop访问v$database。

6）修改了该语句访问方式。

点击(此处)折叠或打开

1. select d.name||'-----',sum(s.bytes)/1024/1024/1024 from (select /*+ materialize */  name from v$database ) d, dba_segments s group by d.name||'-----';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25105315/viewspace-2133566/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25105315/viewspace-2133566/