最近两个星期全都在和bug较劲,前一阵是92上的bug,最近碰到的都是10.2上的。
最先在alert文件中发现这个bug还比较奇特:
Errors in file /data/oracle/admin/kaifa/udump/kaifa_ora_5013.trc:
ORA-00600: 脛脷虏驴麓铆脦贸麓煤脗毛, 虏脦脢媒: [qernsRowP], [1], [], [], [], [], [], []
这里的问题到不是乱码,而是这里给出的详细TRACE文件根本没有生成:
[oracle@yans3 bdump]$ more /data/oracle/admin/kaifa/udump/kaifa_ora_5013.trc
/data/oracle/admin/kaifa/udump/kaifa_ora_5013.trc: No such file or directory
本来这个问题已经无法跟踪了,最多只能在metalink上看看大致问题出现在哪个方面。结果在查询metalink的时候看到了解决方法是ALTER SESSION SET EVENTS 10119,而恰好前两天听同事说碰到了这个问题,于是请同事查找版本库才最终找到这个问题SQL。
由于问题SQL比较复杂,这里给出一个简化后的SQL:
SQL> SELECT A.ID,
2 MIN(A.BUSINESS_CATEGORY) CATEGORY
3 FROM SW_PLAT_CAT_ENTERPRISE@JIANGSU231 A,
4 SW_PLAT_CAT_ORG@JIANGSU231 B
5 WHERE A.PLAT_ID != '30SH10000000000013180006'
6 AND B.ENABLE_FLAG = '1'
7 AND A.ENABLE_FLAG = '1'
8 AND A.ID = B.ID
9 AND A.PLAT_ID = B.PLAT_ID
10 AND EXISTS (SELECT 'X' FROM CAT_ORG C WHERE C.ID = A.ID)
11 AND NOT EXISTS (SELECT 'X' FROM CAT_ENTERPRISE D WHERE D.ID = A.ID)
12 GROUP BY A.ID;
ID CATEGORY
------------------------ ------------------------------
FR20T0000004000000008114
FR20T0000004000000008136
FR20T0000004000000008361
FR20T0000004000000008430
FR20T0000004000000008533
FR20T0000004000000008563
FR20T0000004000000008564
FR20T0000004000000008584
FR20T0000004000000008620
FR20T0000004000000008643
FR20T0000004000000008711
FR20T0000004000000008740
FR20T0000004000000008772
FR20T0000004000000008774
FR20T0000004000000008775
ERROR:
ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [], [], []
15 rows selected.
和前面碰到的几个错误不同,这个错误显然发生在SQL的执行阶段。对于这种情况,可以很方便的检查SQL的执行计划:
SQL> EXPLAIN PLAN FOR
2 SELECT A.ID,
3 MIN(A.BUSINESS_CATEGORY) CATEGORY
4 FROM SW_PLAT_CAT_ENTERPRISE@JIANGSU231 A,
5 SW_PLAT_CAT_ORG@JIANGSU231 B
6 WHERE A.PLAT_ID != '30SH10000000000013180006'
7 AND B.ENABLE_FLAG = '1'
8 AND A.ENABLE_FLAG = '1'
9 AND A.ID = B.ID
10 AND A.PLAT_ID = B.PLAT_ID
11 AND EXISTS (SELECT 'X' FROM CAT_ORG C WHERE C.ID = A.ID)
12 AND NOT EXISTS (SELECT 'X' FROM CAT_ENTERPRISE D WHERE D.ID = A.ID)
13 GROUP BY A.ID;
Explained.
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 519467468
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)|Time |Inst |IN-OUT|
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8368 | 2345K| | 747K (1)|02:54:20| | |
| 1 | SORT GROUP BY NOSORT | | 8368 | 2345K| | 747K (1)|02:54:20| | |
|* 2 | FILTER | | | | | | | | |
| 3 | MERGE JOIN | | 8368 | 2345K| | 1030 (1)|00:00:15| | |
| 4 | REMOTE | | 16665 | 3368K| | 164 (0)|00:00:03|JIANG~| R->S |
|* 5 | SORT JOIN | | 35084 | 856K| 2224K| 362 (1)|00:00:06| | |
| 6 | TABLE ACCESS FULL|CAT_ORG | 35084 | 856K| | 199 (0)|00:00:03| | |
|* 7 | TABLE ACCESS FULL |CAT_ENTERPRISE| 1 | 25 | | 126 (1)|00:00:02| | |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "CAT_ENTERPRISE" "D" WHERE "D"."ID"=:B1))
5 - access("C"."ID"="A"."ID")
filter("C"."ID"="A"."ID")
7 - filter("D"."ID"=:B1)
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - SELECT "A1"."ID","A1"."ID","A1"."ID","A1"."BUSINESS_CATEGORY","A1"."PLAT_ID","A1"."ENABLE_FLAG","
A1"."ID","A1"."PLAT_ID","A1"."ID","A2"."ENABLE_FLAG","A2"."ID","A2"."PLAT_ID" FROM
"SW_PLAT_CAT_ENTERPRISE" "A1","SW_PLAT_CAT_ORG" "A2" WHERE "A2"."ENABLE_FLAG"='1' AND
"A1"."PLAT_ID"="A2"."PLAT_ID" AND "A1"."ID"="A2"."ID" AND "A1"."ENABLE_FLAG"='1' AND
"A1"."PLAT_ID"<>'30SH10000000000013180006' (accessing 'JIANGSU231' )
32 rows selected.
根据metalink文档Doc ID: Note:285913.1的描述和给出的解决方法,可以确定,问题是由于SORT GROUP BY NOSORT操作造成的。
利用Oracle给出的方法可以顺利问题:
SQL> ALTER SESSION SET EVENTS '10119 TRACE NAME CONTEXT FOREVER, LEVEL 12';
Session altered.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> SET AUTOT TRACE
SQL> SELECT A.ID,
2 MIN(A.BUSINESS_CATEGORY) CATEGORY
3 FROM SW_PLAT_CAT_ENTERPRISE@JIANGSU231 A,
4 SW_PLAT_CAT_ORG@JIANGSU231 B
5 WHERE A.PLAT_ID != '30SH10000000000013180006'
6 AND B.ENABLE_FLAG = '1'
7 AND A.ENABLE_FLAG = '1'
8 AND A.ID = B.ID
9 AND A.PLAT_ID = B.PLAT_ID
10 AND EXISTS (SELECT 'X' FROM CAT_ORG C WHERE C.ID = A.ID)
11 AND NOT EXISTS (SELECT 'X' FROM CAT_ENTERPRISE D WHERE D.ID = A.ID)
12 GROUP BY A.ID;
1512 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3196703611
----------------------------------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes |TempSpc| Cost (%CPU)|Time |Inst |IN-OUT|
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8368 | 2345K| | 747K (1)|02:54:20| | |
| 1 | HASH GROUP BY | | 8368 | 2345K| | 747K (1)|02:54:20| | |
|* 2 | FILTER | | | | | | | | |
| 3 | MERGE JOIN | | 8368 | 2345K| | 1030 (1)|00:00:15| | |
| 4 | REMOTE | | 16665 | 3368K| | 164 (0)|00:00:03|JIANG~| R->S |
|* 5 | SORT JOIN | | 35084 | 856K| 2224K| 362 (1)|00:00:06| | |
| 6 | TABLE ACCESS FULL|CAT_ORG | 35084 | 856K| | 199 (0)|00:00:03| | |
|* 7 | TABLE ACCESS FULL |CAT_ENTERPRISE| 1 | 25 | | 126 (1)|00:00:02| | |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "CAT_ENTERPRISE" "D" WHERE "D"."ID"=:B1))
5 - access("C"."ID"="A"."ID")
filter("C"."ID"="A"."ID")
7 - filter("D"."ID"=:B1)
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - SELECT "A1"."ID","A1"."ID","A1"."ID","A1"."BUSINESS_CATEGORY","A1"."PLAT_ID","A1"."ENABLE_FLAG","
A1"."ID","A1"."PLAT_ID","A1"."ID","A2"."ENABLE_FLAG","A2"."ID","A2"."PLAT_ID" FROM
"SW_PLAT_CAT_ENTERPRISE" "A1","SW_PLAT_CAT_ORG" "A2" WHERE "A2"."ENABLE_FLAG"='1' AND
"A1"."PLAT_ID"="A2"."PLAT_ID" AND "A1"."ID"="A2"."ID" AND "A1"."ENABLE_FLAG"='1' AND
"A1"."PLAT_ID"<>'30SH10000000000013180006' (accessing 'JIANGSU231' )
Statistics
----------------------------------------------------------
1015 recursive calls
0 db block gets
2250327 consistent gets
0 physical reads
0 redo size
64449 bytes sent via SQL*Net to client
1591 bytes received via SQL*Net from client
102 SQL*Net roundtrips to/from client
14 sorts (memory)
0 sorts (disk)
1512 rows processed
SQL> ALTER SESSION SET EVENTS '10119 TRACE NAME CONTEXT OFF';
Session altered.
其实了解了原因,要避免这个错误也很容易,不需要一定使用EVENTS,仅通过HINT就可以避免这个错误的发生:
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> SELECT A.ID,
2 MIN(A.BUSINESS_CATEGORY) CATEGORY
3 FROM SW_PLAT_CAT_ENTERPRISE@JIANGSU231 A,
4 SW_PLAT_CAT_ORG@JIANGSU231 B
5 WHERE A.PLAT_ID != '30SH10000000000013180006'
6 AND B.ENABLE_FLAG = '1'
7 AND A.ENABLE_FLAG = '1'
8 AND A.ID = B.ID
9 AND A.PLAT_ID = B.PLAT_ID
10 AND EXISTS (SELECT 'X' FROM CAT_ORG C WHERE C.ID = A.ID)
11 AND NOT EXISTS (SELECT 'X' FROM CAT_ENTERPRISE D WHERE D.ID = A.ID)
12 GROUP BY A.ID;
ERROR:
ORA-00600: internal error code, arguments: [qernsRowP], [1], [], [], [], [], [], []
15 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 519467468
----------------------------------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes |TempSpc| Cost (%CPU)|Time |Inst |IN-OUT|
----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8368 | 2345K| | 747K (1)|02:54:20| | |
| 1 | SORT GROUP BY NOSORT | | 8368 | 2345K| | 747K (1)|02:54:20| | |
|* 2 | FILTER | | | | | | | | |
| 3 | MERGE JOIN | | 8368 | 2345K| | 1030 (1)|00:00:15| | |
| 4 | REMOTE | | 16665 | 3368K| | 164 (0)|00:00:03|JIANG~| R->S |
|* 5 | SORT JOIN | | 35084 | 856K| 2224K| 362 (1)|00:00:06| | |
| 6 | TABLE ACCESS FULL|CAT_ORG | 35084 | 856K| | 199 (0)|00:00:03| | |
|* 7 | TABLE ACCESS FULL |CAT_ENTERPRISE| 1 | 25 | | 126 (1)|00:00:02| | |
----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "CAT_ENTERPRISE" "D" WHERE "D"."ID"=:B1))
5 - access("C"."ID"="A"."ID")
filter("C"."ID"="A"."ID")
7 - filter("D"."ID"=:B1)
Remote SQL Information (identified by operation id):
----------------------------------------------------
4 - SELECT "A1"."ID","A1"."ID","A1"."ID","A1"."BUSINESS_CATEGORY","A1"."PLAT_ID","A1"."ENABLE_FLAG","
A1"."ID","A1"."PLAT_ID","A1"."ID","A2"."ENABLE_FLAG","A2"."ID","A2"."PLAT_ID" FROM
"SW_PLAT_CAT_ENTERPRISE" "A1","SW_PLAT_CAT_ORG" "A2" WHERE "A2"."ENABLE_FLAG"='1' AND
"A1"."PLAT_ID"="A2"."PLAT_ID" AND "A1"."ID"="A2"."ID" AND "A1"."ENABLE_FLAG"='1' AND
"A1"."PLAT_ID"<>'30SH10000000000013180006' (accessing 'JIANGSU231' )
Statistics
----------------------------------------------------------
2073 recursive calls
4 db block gets
10039 consistent gets
0 physical reads
0 redo size
1070 bytes sent via SQL*Net to client
502 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
54 sorts (memory)
0 sorts (disk)
15 rows processed
SQL> SELECT A.ID,
2 MIN(A.BUSINESS_CATEGORY) CATEGORY
3 FROM SW_PLAT_CAT_ENTERPRISE@JIANGSU231 A,
4 SW_PLAT_CAT_ORG@JIANGSU231 B
5 WHERE A.PLAT_ID != '30SH10000000000013180006'
6 AND B.ENABLE_FLAG = '1'
7 AND A.ENABLE_FLAG = '1'
8 AND A.ID = B.ID
9 AND A.PLAT_ID = B.PLAT_ID
10 AND EXISTS (SELECT /*+ NO_USE_MERGE(C) */ 'X' FROM CAT_ORG C WHERE C.ID = A.ID)
11 AND NOT EXISTS (SELECT 'X' FROM CAT_ENTERPRISE D WHERE D.ID = A.ID)
12 GROUP BY A.ID;
1512 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 925946195
---------------------------------------------------------------------------------------------------------
| Id | Operation |Name | Rows | Bytes |TempSpc| Cost (%CPU)|Time |Inst |IN-OUT|
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8368 | 2345K| | 747K (1)|02:54:22| | |
| 1 | HASH GROUP BY | | 8368 | 2345K| 4912K| 747K (1)|02:54:22| | |
|* 2 | FILTER | | | | | | | | |
|* 3 | HASH JOIN | | 8368 | 2345K| | 820 (1)|00:00:12| | |
| 4 | TABLE ACCESS FULL|CAT_ORG | 35084 | 856K| | 199 (0)|00:00:03| | |
| 5 | REMOTE | | 16665 | 3368K| | 222 (1)|00:00:04|JIANG~| R->S |
|* 6 | TABLE ACCESS FULL |CAT_ENTERPRISE| 1 | 25 | | 126 (1)|00:00:02| | |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "CAT_ENTERPRISE" "D" WHERE "D"."ID"=:B1))
3 - access("C"."ID"="A"."ID")
6 - filter("D"."ID"=:B1)
Remote SQL Information (identified by operation id):
----------------------------------------------------
5 - SELECT "A1"."ID","A1"."ID","A1"."ID","A1"."BUSINESS_CATEGORY","A1"."PLAT_ID","A1"."ENABLE_FLAG",
"A1"."ID","A1"."PLAT_ID","A1"."ID","A2"."ENABLE_FLAG","A2"."ID","A2"."PLAT_ID" FROM
"SW_PLAT_CAT_ENTERPRISE" "A1","SW_PLAT_CAT_ORG" "A2" WHERE "A2"."ENABLE_FLAG"='1' AND
"A1"."PLAT_ID"="A2"."PLAT_ID" AND "A1"."ID"="A2"."ID" AND "A1"."ENABLE_FLAG"='1' AND
"A1"."PLAT_ID"<>'30SH10000000000013180006' (accessing 'JIANGSU231' )
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2250147 consistent gets
0 physical reads
0 redo size
64449 bytes sent via SQL*Net to client
1591 bytes received via SQL*Net from client
102 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1512 rows processed
Oracle为了避免SORT的时候的排序,而选择了MERGE JOIN作为执行计划,这里使用NO_USE_MERGE来避免CBO选择MERGE JOIN,由于执行计划中没有了排序操作,因此Oracle在最终SORT的时候也就不会选择SORT GROUP BY NOSORT执行计划了。
这个600错误在10g和9i都是比较常见的,绝大部分在10.2.0.4或者11.1.0.6中被解决。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-234560/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/4227/viewspace-234560/