由latch导致的ORA-3136

Jialin.Lee

ML：
情况描述如下，昨晚凌晨一点和今早六点和八点一套生产库出现ORA-3136，导致数据hang住，早上过来抓了个AWR看了下，其中TOP 5全是latch的锁，确定是因为latch导致了ORA-3136的问题，但是无法解决解决latch问题，可以肯定该问题还会再次出现，所以请你帮忙看看，能否给予解决思路，谢谢！
注：附件里为出现问题时的AWR、alter.log，平台为HP-UNIX 11.31。

Maclean Liu(刘相兵

Statistic Name        Time (s)        % of DB Time
sql execute elapsed time        43,662.18        67.13
parse time elapsed        28,597.05        43.97
failed parse elapsed time        15,955.45        24.53
hard parse elapsed time        14,933.65        22.96
failed parse (out of shared memory) elapsed time        8,190.01        12.59
DB CPU        6,263.76        9.63
PL/SQL compilation elapsed time        4,380.07        6.73
connection management call elapsed time        2,723.87        4.19


解析始终占了大头的db time，而且是 失败的解析 failed parses ,failed parse (out of shared memory) elapsed time

虽然 每秒硬解析次数并不多

shared pool        kghdmp        0        203        0
shared pool        kghalo        0        101        251
shared pool        kghalp        0        34        65
shared pool        kghfre        0        17        16
shared pool        kghupr1        0        12        35
shared pool        kghasp        0        1        1


shared pool的miss source 主要是 kghdmp kghalo


关键词 hpux_sched_noage        178 ==> 这是HPUX平台

HPUX+10.2.0.2 +  cursor pin s on X 很有可能是 HPux 上mutex的BUG ==>  _kks_use_mutex_Pin

Maclean Liu(刘相兵

action plan:

opatch lsinventory

下面的脚本查一下

1. 
   
2. set pages 1000 lines 120
   
3. col name for a60
   
4. col value for a30
   
5. 
   
6. select * from v$sgastat where pool like 'shared%' and name='free memory';
   
7. 
   
8. select '0 (<140)' BUCKET, KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10) "From",
   
9. count(*) "Count" , max(KSMCHSIZ) "Biggest",
   
10. trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
    
11. from x$ksmsp
    
12. where KSMCHSIZ<140
    
13. and KSMCHCLS='free'
    
14. group by KSMCHCLS, KSMCHIDX, 10*trunc(KSMCHSIZ/10)
    
15. UNION ALL
    
16. select '1 (140-267)' BUCKET, KSMCHCLS, KSMCHIDX,20*trunc(KSMCHSIZ/20) ,
    
17. count(*) , max(KSMCHSIZ) ,
    
18. trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
    
19. from x$ksmsp
    
20. where KSMCHSIZ between 140 and 267
    
21. and KSMCHCLS='free'
    
22. group by KSMCHCLS, KSMCHIDX, 20*trunc(KSMCHSIZ/20)
    
23. UNION ALL
    
24. select '2 (268-523)' BUCKET, KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50) ,
    
25. count(*) , max(KSMCHSIZ) ,
    
26. trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
    
27. from x$ksmsp
    
28. where KSMCHSIZ between 268 and 523
    
29. and KSMCHCLS='free'
    
30. group by KSMCHCLS, KSMCHIDX, 50*trunc(KSMCHSIZ/50)
    
31. UNION ALL
    
32. select '3-5 (524-4107)' BUCKET, KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500) ,
    
33. count(*) , max(KSMCHSIZ) ,
    
34. trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
    
35. from x$ksmsp
    
36. where KSMCHSIZ between 524 and 4107
    
37. and KSMCHCLS='free'
    
38. group by KSMCHCLS, KSMCHIDX, 500*trunc(KSMCHSIZ/500)
    
39. UNION ALL
    
40. select '6+ (4108+)' BUCKET, KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000) ,
    
41. count(*) , max(KSMCHSIZ) ,
    
42. trunc(avg(KSMCHSIZ)) "AvgSize", trunc(sum(KSMCHSIZ)) "Total"
    
43. from x$ksmsp
    
44. where KSMCHSIZ >= 4108
    
45. and KSMCHCLS='free'
    
46. group by KSMCHCLS, KSMCHIDX, 1000*trunc(KSMCHSIZ/1000);
    
47. 
    
48. SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
    
49. To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
    
50. FROM X$KSMSP GROUP BY KSMCHCLS;
    
51. 
    
52. SELECT alloc_type, alloc_size, num_objs_flushed, object_loaded
    
53.   FROM (SELECT ksmlrcom alloc_type,
    
54.                ksmlrsiz alloc_size,
    
55.                ksmlrnum num_objs_flushed,
    
56.                ksmlrhon object_loaded,
    
57.                RANK() OVER(ORDER BY ksmlrsiz DESC) AS order_ranking
    
58.           FROM x$ksmlru
    
59.          WHERE inst_id = USERENV('INSTANCE')
    
60.            AND ksmlrsiz > 0)
    
61. WHERE order_ranking  400)
    
62. WHERE order_ranking  0
    
63.            AND o.type LIKE 'JAVA%')
    
64. WHERE order_ranking  0
    
65.            AND o.type in
    
66.                ('PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'SEQUENCE'))
    
67. WHERE order_ranking  0
    
68.             AND o.type = 'CURSOR'
    
69. )
    
70. WHERE order_ranking

复制代码

Jialin.Lee

我按照你提供的SQL脚本去跑，导致其中一核CPU 100%，第一个SQL还未返回结果，等待中！

xteitxu

Maclean Liu(刘相兵 发表于 2013-3-15 09:55
action plan:

opatch lsinventory

刘大  最后一段脚本没贴全  缺少运算符
WHERE order_ranking  400)
                      *
第 10 行出现错误:
ORA-00920: 无效的关系运算符

Jialin.Lee

按照你提供的脚本运行，结果见附件！

qhd2004

受益了，但是“HPUX+10.2.0.2 +  cursor pin s on X 很有可能是 HPux 上mutex的BUG ==>  _kks_use_mutex_Pin”
这个是怎么看出来的呢？望指点下。

Maclean Liu(刘相兵

BUCKET         KSMCHCLS   KSMCHIDX       From      Count    Biggest    AvgSize
-------------- -------- ---------- ---------- ---------- ---------- ----------
     Total
----------
6+ (4108+)     free              2       7000          2       7616       7552
     15104

6+ (4108+)     free              2       4000        539       4784       4127
   2224928

6+ (4108+)     free              1       8000          2       8064       8032
     16064


BUCKET         KSMCHCLS   KSMCHIDX       From      Count    Biggest    AvgSize
-------------- -------- ---------- ---------- ---------- ---------- ----------
     Total
----------
6+ (4108+)     free              1       7000          1       7552       7552
      7552

6+ (4108+)     free              2       8000          1       8264       8264
      8264

6+ (4108+)     free              1       6000          1       6320       6320
      6320


BUCKET         KSMCHCLS   KSMCHIDX       From      Count    Biggest    AvgSize
-------------- -------- ---------- ---------- ---------- ---------- ----------
     Total
----------
6+ (4108+)     free              2       6000          6       6376       6336
     38016

6+ (4108+)     free              2       5000          2       5336       5272
     10544

6+ (4108+)     free              1       5000          2       5768       5500
     11000


可以看到shared pool里大于4k的 chunk 很少了 仅为 3~4MB ， 这说明存在较多碎片， shared pool使用不健康

Jialin.Lee

按照你所说,需要优化shared pool的碎片,还有别的需要解决的为难题吗?

Jialin.Lee

Maclean Liu(刘相兵 发表于 2013-3-15 10:57
BUCKET         KSMCHCLS   KSMCHIDX       From      Count    Biggest    AvgSize
-------------- ------ ...

ML:
按照你所说,需要优化shared pool的碎片,还有别的需要解决的问题吗?

Maclean Liu(刘相兵

需要关注的

1. 版本 10.2.0.2 ====================》
2. HPUX+ 使用mutex cursor pin
3. 估计有部分SQL没有绑定变量
4.  shared pool碎片多

Jialin.Lee

Maclean Liu(刘相兵 发表于 2013-3-15 11:14
需要关注的

1. 版本 10.2.0.2 ====================》

ML：
1.对于10.2.0.2 HPUX+ 使用mutex cursor pin这个bug问题之前遇见过；
2.对于部分的SQL会去查，让开发；
3.shared pool碎片我会优化；

最后谢谢ML！

sunshine

AWR中SQL ordered by Parse Calls的统计怎么会有这么多这种语句？
这段时间是不是在批量新建/删除对象？
比如说 拆表的分区和删除分区，个人感觉，希望对你有帮助

16,814        16,816        7.67        bsa0wjtftg3uw                 select file# from file$ where ...
14,286        14,286        6.52        aq4js2gkfjru8                 update tsq$ set blocks=:3, max...
14,255        14,255        6.50        59vjj34vugaav                 delete from obj$ where obj# = ...
14,226        14,226        6.49        1mvbtvbqt4z4p                 delete from sys.cache_stats_1$...
14,226        14,226        6.49        as3uq6ggb3gx6                 delete from hist_head$ where o...
14,226        14,226        6.49        gc7b0drtzbyc6                 select max(intcol#) from hist_...
6,280        6,280        2.87        64wq3fa5a77sm                 insert into seg$ (file#, block...

yanshoupeng

MUTEX的串行机制是通过CAS(compare-and-swap)实现的。

某些平台不支持CAS ，有些HP-UX貌似就不支持。

关掉MUTEX看看 _kks_use_mutex_Pin =false