10g临时表空间组导致递归SQL高度解析案例

前几天在为客户提供服务时，发现客户的系统中，存在一条高解析执行的SQL：

这条SQL占据了78.62%的解析比重，在10小时的AWR报告采样中，共执行了2千多万次，平均每秒解析执行约600次，这个高解析执行的SQL引起了我的注意，其SQL的完整文本为：

select min(bitmapped) from ts$ where dflmaxext =:1 and bitand(flags, 1024) =1024

这条SQL直观判断是和系统递归调用相关的，查询了底层的ts$视图，如果跟踪一下普通查询，我们发现这个SQL都有很高的解析度。比如跟踪如下SQL查询：

SQL> alter session set events '10046 trace name context forever,level 12';
Session altered.

SQL> select count(*) from dba_indexes;
  COUNT(*)
----------
      5890

tkprof格式化后台跟踪文件可以发现，在这个查询中，后台ts$递归查询高达3305次：

select min(bitmapped)
from
 ts$ where dflmaxext =:1 and bitand(flags,1024) =1024


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse     3305      0.02       0.04          0          0          0           0
Execute   3305      0.24       0.30          0          0          0           0
Fetch     6610      0.90       1.05          0     317280          0        3305
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    13220      1.16       1.39          0     317280          0        3305

而且逻辑读很高。

这使得我怀疑可能是某个Bug在作祟，检索Metalink，马上发现了相关Bug，bug号为：5455880。

该Bug的影响版本为：

客户的数据库版本为10.2.0.2，正好在受影响之列，这个Bug是说，当使用了Oracle 10g的临时表空间组特性时，后台的递归SQL可能会发生高昂的解析及执行：

When using a tablespace group as the temporary tablespace
excessive recursive queried against TS$ can impact performance.
The offending SQL is of the form:
  "select min(bitmapped) from ts$  where dflmaxext =:1 and bitand(flags,1024)=1024"

这个Bug在10.2.0.4之后修正，暂时的解决方案是停用临时表空间组。

用户调整了临时表空间组的使用之后，这个SQL立即消失了，系统的解析等负载概要信息也发生了较大变化：

我们在使用Oracle的一些新特性时，一定要注意观察，看是否会引发一些新的问题，最近发现的几个Bug都和Oracle的一些新特性相关。

记录一下，供大家参考！

-The End-