查询11G的数据字典试图遇到BUG
2010-5-4
在Oracle新版本上比较容易遇到BUG，最近还是真让我偶遇到了。而且还是个将为生产环境的数据库。汗啊。
在一个windows server 2008的11.1.0.6的环境上，查询all_objects 试图时，发现每次都会产生大量的递归调用，而且执行时间很长。

SQL>select count(*) from all_objects;

Elapsed: 00:00:08.99
Statistics
----------------------------------------------------------
     94579 recursive calls
         0 db block gets
    198754 consistent gets
      1657 physical reads
         0 redo size
       420 bytes sent via SQL*Net to client
       419 bytes received via SQL*Net from client
         2 SQL*Net roundtrips to/from client
      1063 sorts (memory)
         0 sorts (disk)
         1 rows processed

在10G和9I上都没有同样的问题。

On 9.2.0.8:

SQL> set timing on
SQL> set autotr on
SQL> select count(*) from all_objects;

COUNT(*)
----------
30368

Elapsed: 00:00:00.12

On 10.2.0.4:
SQL> select count(*) from all_objects;
COUNT(*)
----------
50538

Elapsed: 00:00:00.22

Statistics
----------------------------------------------------------
        680 recursive calls
          0 db block gets
      14130 consistent gets
         14 physical reads
          0 redo size
        517 bytes sent via SQL*Net to client
        487 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
         11 sorts (memory)
          0 sorts (disk)
          1 rows processed

在11G的recursive call比9i，10G高出很多，而且执行的时间要8秒多才执行完毕。
做了10046的trace，发现下面的两个SQL总耗时为7.32秒。

select sys_nc_oid$
from
xdb.xdb$resource where rowid = :1

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse    10633      2.29       2.48          0          0          0           0
Execute 10633      3.23       3.42          0        156          0           0
Fetch    10633      2.92       3.14          0      10633          0       10633
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total    31899      8.45       9.05          0      10789          0       10633

select value(p$)
from
"XDB"."XDB$RESOURCE" as of snapshot(:2) p$ where SYS_NC_OID$
= :1

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        0      0.00       0.00          0          0          0           0
Execute 10633      3.76       3.90          0          0          0           0
Fetch    10633      1.17       1.53          0      31899          0       10633
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total    21266      4.93       5.44          0      31899          0       10633

深入问题去分析，结果是该版本上的一个Bug 7295298，而且在11.1.0.6的windows版本上没有相关的独立补丁可打。升级到11.1.0.7可解决这个问题。
有个规避的方式就是设置EVENTS：
alter session set events '10158 trace name context forever';
我是没有去设置，因为只有DBA才会查询这个试图。
不要Oracle新数据库引擎的第一版来作为生产环境，还真是百验不爽的真理。
这个ALL_OBJECTS的试图，还好没有什么用户使用，对环境影响不大。在下次有机会升级时，一并把这个BUG解决了。
-THE END-

查询11G的数据字典试图ALL_OBJECTS遇到BUG