通过微软的报表服务器访问Oracle,做好一张报表,为了偷懒,直接在数据库建立了一个视图,然后显示就行了。结果测试时报ORA-600 [qks3tinit]。登陆到本机一运行,没问题啊。怀疑是微软报表服务器连接Oracle的接口有问题。但是其他几个类似视图又都没问题。于是检测SQL:
SELECT (CASE
WHEN fjname IS NULL
THEN '全局'
ELSE fjname
END) fjname,
(CASE
WHEN stat_type IS NULL
THEN '总计'
ELSE stat_type
END) stat_type,
(CASE
WHEN cust_type_old IS NULL
THEN '总计'
ELSE cust_type_old
END
) cust_type_old,
cnt, curr_user
FROM (SELECT fjname, stat_type, cust_type_old, COUNT (*) cnt, SUM(CASE state WHEN '正常' THEN 1 ELSE 0 END) curr_user
FROM tmp_tj0507001
WHERE fjname IS NOT NULL
GROUP BY grouping sets(rollup(fjname, stat_type, cust_type_old), rollup(stat_type, cust_type_old)))
ORDER BY fjname, stat_type, cust_type_old;
执行计划:
SELECT STATEMENT, GOAL = HINT: ALL_ROWS Cost=10 Cardinality=15 Bytes=780
SORT ORDER BY Cost=10 Cardinality=15 Bytes=780
VIEW Object owner=YAOYP Cost=8 Cardinality=15 Bytes=780
TEMP TABLE TRANSFORMATION
VIEW Object owner=YAOYP Cost=8 Cardinality=4 Bytes=208
VIEW Object owner=YAOYP Cost=8 Cardinality=4 Bytes=208
UNION-ALL
TABLE ACCESS FULL Object owner=SYS Object name=SYS_TEMP_0FD9D668F_9A864B59 Cost=2 Cardinality=1 Bytes=52
TABLE ACCESS FULL Object owner=SYS Object name=SYS_TEMP_0FD9D6690_9A864B59 Cost=2 Cardinality=1 Bytes=52
TABLE ACCESS FULL Object owner=SYS Object name=SYS_TEMP_0FD9D6691_9A864B59 Cost=2 Cardinality=1 Bytes=52
TABLE ACCESS FULL Object owner=SYS Object name=SYS_TEMP_0FD9D6691_9A864B59 Cost=2 Cardinality=1 Bytes=52
RECURSIVE EXECUTION Object name=SYS_LE_7_0
RECURSIVE EXECUTION Object name=SYS_LE_7_1
RECURSIVE EXECUTION Object name=SYS_LE_7_2
RECURSIVE EXECUTION Object name=SYS_LE_7_3
就看到一个 TEMP TABLE TRANSFORMATION ,这个比较少见哦。另外一个类似SQL:
SELECT (CASE
WHEN fjname IS NULL
THEN '全局'
ELSE fjname
END) fjname,
(CASE
WHEN cust_type IS NULL
THEN '总计'
ELSE cust_type
END
) cust_type,
curr_user
FROM (SELECT fjname, (CASE WHEN cust_type IS NULL THEN '公免等' ELSE cust_type END) cust_type,
SUM(CASE state WHEN '正常' THEN 1 ELSE 0 END) curr_user
FROM tmp_tj0507001
WHERE fjname IS NOT NULL AND state = '正常'
GROUP BY ROLLUP (fjname, (CASE WHEN cust_type IS NULL THEN '公免等' ELSE cust_type END)))
ORDER BY fjname, cust_type;
执行计划:
SELECT STATEMENT, GOAL = CHOOSE Cost=3021 Cardinality=43 Bytes=1376
SORT ORDER BY Cost=3021 Cardinality=43 Bytes=1376
VIEW Object owner=YAOYP Cost=3020 Cardinality=43 Bytes=1376
SORT GROUP BY ROLLUP Cost=3020 Cardinality=43 Bytes=989
INDEX FAST FULL SCAN Object owner=YAOYP Object name=SYS_IOT_TOP_62350 Cost=367 Cardinality=1132418 Bytes=26045614
没有 TEMP TABLE TRANSFORMATION 了。看来要避免这个执行路径,想了半天也没办法,开始还怀疑是参数STAR_TRANSFORMATION_ENABLED问题,后来一看是FALSE的。只能到Metalink上找了,结果又是一个Bug,最近简直是一出问题就是Bug,以前怎么就不觉得呢?
文档 ID: 注释:2836690.8
主题: Support Description of Bug 2836690
类型: PATCH
状态: PUBLISHED
内容类型: TEXT/X-HTML
创建日期: 08-AUG-2003
上次修订日期: 14-AUG-2003
Click here for details of sections in this note.
Bug 2836690 ROLLUP over DBLINK may fail with OERI:qks3tinit
This note gives a brief overview of bug 2836690.
Affects:
Product (Component) Oracle Server (RDBMS)
Range of versions believed to be affected Versions >= 9 but < 10G
Versions confirmed as being affected
- 9.2.0.3
Platforms affected Generic (all / most platforms affected)
Fixed:
This issue is fixed in
- 9.2.0.4 (Server Patch Set)
- 10G Production Base Release
Symptoms:
- Internal Error may occur (ORA-600)
- ORA-600 [qks3tinit]
Related To:
- DB-Link / Distributed
- Analytic SQL (Windowing etc..)
Description
ORA-600 [qks3tinit] possible from queries using ROLLUP which reference
remote objects.
The full bug text (if published) can be seen at
1<bug:2836690>
2This link will not work for UNPUBLISHED bugs.
3
4类似还有一个Bug:
5
6文档 ID: 注释:2419335.8
7主题: Support Description of Bug 2419335
8类型: PATCH
9状态: PUBLISHED
10
11内容类型: TEXT/X-HTML
12创建日期: 08-AUG-2003
13上次修订日期: 14-AUG-2003
14Click here for details of sections in this note.
15Bug 2419335 OERI:[qks3tInit] possible from STAR transformation
16This note gives a brief overview of bug 2419335.
17Affects:
18
19Product (Component) Oracle Server (RDBMS)
20Range of versions believed to be affected Versions >= 9.2 but < 10G
21Versions confirmed as being affected
22
23* 9.2.0.3
24
25Platforms affected Generic (all / most platforms affected)
26
27Fixed:
28
29This issue is fixed in
30
31* 9.2.0.4 (Server Patch Set)
32* 10G Production Base Release
33
34Symptoms:
35
36* Internal Error may occur (ORA-600)
37* ORA-600 [qks3tInit]
38
39Related To:
40
41* DB-Link / Distributed
42* Star Transformation
43
44Description
45
46ORA-600 [qks3tInit] possible from star transformation query
47where all the tables are remote (ie: over a dblink)
48
49Workaround:
50Set star_transformation_enabled = false at the remote DB.
51
52The full bug text (if published) can be seen at <bug:2419335>
53This link will not work for UNPUBLISHED bugs.
54
55不过我还是觉得是 TEMP TABLE TRANSFORMATION 问题,没理由第2个SQL里的ROLLUP就没问题哦。唉,放弃ROLLUP咯。关于上面这个执行路径资料好少,有办法避免么?</bug:2419335></bug:2836690>