诊断案例二：临时表引发的竞争

这是帮助一个网友解决的一个问题，通过MSN交流，以下是问题的解决过程及思路，供大家参考。

问：如果一个DB里面的几个存储过程总是跑不完，同样的存储过程在其他的6个省都很正常，数据库里没有锁，数据库和Server上面的空间足够。正常的情况几分钟就能运行完，现在都n多小时了还没有运行完，会是什么原因呢?

答：检查v$session_wait，看系统在等什么？

提示：如果你的系统慢，通常是存在等待，v$session_wait是你应该优先检查的视图。

下面是这位网友发过来的查询结果，这里截取了主要的部分：

SID SEQ#  EVENT   P1TEXT  P1  P1RAW  P2TEXT  P2  
13 7210  library cache pin handle address 3172526924 BD18EB4C pin address 3205742908
33 16179 library cache pin handle address 3172526924 BD18EB4C pin address 3206485860
32 14721 library cache pin handle address 3172526924 BD18EB4C pin address 3206555324
27 54913 library cache pin handle address 3172526924 BD18EB4C pin address 3205741540
30 16169 library cache lock handle address 3174604528 BD389EF0 lock address3206478252

通过以上信息注意到，数据库目前正在经历Library Cache Pin和Library Cache Lock的等待和竞争。我要求她执行本章上文中讲到过的SQL，并提供结果：

SQL> select ADDR,KGLHDADR,KGLHDPAR,KGLNAOWN,KGLNAOBJ,KGLNAHSH,KGLHDOBJ
  2  from X$KGLOB
  3  where KGLHDADR ='BD18EB4C'; 
ADDR     KGLHDADR KGLHDPAR  KGLNAOWN  KGLNAOBJ  KGLNAHSH KGLHDOBJ
---------- ------------------ -------------------------- -------------------------- ------
01920880   BD18EB4C  BD18EB4C          truncate table iptt_pm_all   653109544 BD18E8D4
SQL> SELECT a.SID, a.username, a.program, b.addr, b.kglpnadr, b.kglpnuse,
  2         b.kglpnses, b.kglpnhdl, b.kglpnlck, b.kglpnmod, b.kglpnreq
  3 FROM v$session a, x$kglpn b WHERE a.saddr = b.kglpnuse AND b.kglpnmod <> 0
  4 AND b.kglpnhdl IN (SELECT p1raw FROM v$session_wait WHERE event LIKE 'library%') ;
SID USERNAME PROGRAM       ADDR     KGLPNADR KGLPNUSE KGLPNSES KGLPNHDL KGLPNLCK KGLPNMOD
--- -------- ------------- -------- -------- -------- -------- -------- -------- --------
 30 IPNMS  sqlplus@gs-db 0191BEC0 BF2024C4 BE0AE940 BE0AE940 BD18EB4C BF1FA208   3
 54 IPNMS   sqlplus@gs-db 0191BEC0 BF13814C BE0BB360 BE0BB360 BD389EF0 00          3
SQL> SELECT sql_text FROM v$sqlarea
  3 WHERE (v$sqlarea.address, v$sqlarea.hash_value) IN (
  4     SELECT sql_address, sql_hash_value FROM v$session
  5         WHERE SID IN (SELECT SID FROM v$session a, x$kglpn b
  6                   WHERE a.saddr = b.kglpnuse AND b.kglpnmod <> 0
  7               AND b.kglpnhdl IN (SELECT p1raw FROM v$session_wait
  8                                             WHERE event LIKE 'library%')));
SQL_TEXT
--------------------------------------------------------------------------------
truncate table iptt_pm_all

至此，发现了导致问题的关键所在，持有pin的用户在执行truncate table iptt_pm_all的操作。

问：这个truncate是嵌在过程里面的？

答：是的，在一个loop中间的。每半个小时调用一次，类似的怎么也有10个程序吧，公用iptt_pm_all临时表。

我请求查看网友的代码，在一个Procedure中发现了大量如下语句（做了适当简化）：

update iptt_pm_all p
     set n27 = (SELECT count(*)
                  FROM iptca_interface b
                 WHERE p.int_id = b.related_node
                   AND b.iftype = 18
                   AND b.IFOPERSTATUS IN (1,5));
 
 insert into iptt_pm_all (col_time, int_id, ipaddr,
                n1, c1, n2, c2, n3, n4, n5, n6, n7, n8, n9, n10, n11, n12, n13, n14, n15, n16,
                n17, n18, n19, n20, c3, n21, c4, n22, c5, n23, n24, n25, n26, n27)
         select compress_day, int_id, object_ip_addr, ……………..
           from iptaws_gwgj_hour
          where compress_day = v_time.col_time;
 
 SELECT col_time,n21,c4,n22,
         c5,n1,c1,n2,c2,
         sum(n3),sum(n4),sum(n5),sum(n7),sum(n8),sum(n9),
         sum(n10),sum(n11),sum(n12),sum(n13),sum(n14),
         sum(n16),sum(n17),sum(n18),sum(n19),sum(n20)
    FROM iptt_pm_all
   GROUP BY col_time,n21,c4,n22,c5,n1,c1,n2,c2;
 
  v_dsql := 'truncate table iptt_pm_all';
  EXECUTE IMMEDIATE v_dsql;

类似的存储过程还有很多。我请求获取Shared Pool的转储文件用于分析，Level 32级。

ALTER SESSION SET EVENTS 'immediate trace name LIBRARY_CACHE level 32';

限于篇幅，这里不再列举dump文件内容，需要注意的是，在生产环境上使用以上命令应该十分慎重。如果Shared Pool很大，转储文件可能非常巨大，而且可能引发性能问题和Bug。

根据trace文件及MetaLink说明，最终发现问题是由于truncate临时表时不适当地请求了排他锁所致，理论上truncate临时表无需排他锁定，但是Oracle使用了与处理常规表同样的方式处理临时表的锁定，从而导致了Library Cache Pin和Library Cache Lock的竞争，而且该问题并未作为Bug修正。由于该问题主要当多户交叉访问时引起，所以建议对于不同用户改用独立的临时表，此问题就可得以避免。