www.eygle.com

---

June 12, 2009

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

作者:eygle

出处:http://blog.eygle.com

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

Parse Calls	Executions	% Total Parses	SQL Id	SQL Module	SQL Text
21,220,546	21,298,871	78.62	8sph6b5p41afr		select min(bitmapped) from ts$...
531,686	531,682	1.97	8q7uku4ra1w29	JDBC Thin Client	SELECT * FROM te WHERE tei.........

这条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的影响版本为：

Product (Component)	Oracle Server (Rdbms)
Range of versions believed to be affected	Versions < 11
Versions confirmed as being affected	10.1.0.5 10.2.0.3
Platforms affected	Generic (all / most platforms affected)

客户的数据库版本为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立即消失了，系统的解析等负载概要信息也发生了较大变化：

	1st Per Sec	2nd Per Sec	%Diff	1st Per Txn	2nd Per Txn	%Diff
Parses:	743.69	145.64	-80.42	22.40	5.78	-74.20
Sorts:	4,534.49	2,670.65	-41.10	136.60	106.02	-22.39
Executes:	1,032.02	425.26	-58.79	31.09	16.88	-45.71

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

记录一下，供大家参考！

-The End-

Posted by eygle at 10:21 AM | Comments (8)

---