Oracle数据库数据恢复、性能优化»论坛 › Oracle › Oracle数据库管理 › ORA-00600: [qctcte1], [0], [], [], [], [], [], []

13 积分	0 好友	4 主题

发消息

ORA-00600: [qctcte1], [0], [], [], [], [], [], []

1^#

发表于 2012-8-21 14:10:38 | 查看: 9374| 回复: 20

环境：
AIX  :
# oslevel
5.3.0.0

System Model: IBM,9117-MMA
Machine Serial Number: 06C66E2
Processor Type: PowerPC_POWER6
Processor Implementation Mode: POWER 6
Processor Version: PV_6_Compat
Number Of Processors: 9
Processor Clock Speed: 3504 MHz
CPU Type: 64-bit
Kernel Type: 64-bit
LPAR Info: 1 lpar1
Memory Size: 32768 MB
Good Memory Size: 32768 MB
Platform Firmware level: EM350_071
Firmware Version: IBM,EM350_071
Console Login: enable
Auto Restart: true
Full Core: false

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0    Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production

之前并没有重启或者调整过参数

昨天突然日志显示如下：

Mon Aug 20 15:40:51 2012
Thread 1 advanced to log sequence 280528 (LGWR switch)
  Current log# 1 seq# 280528 mem# 0: /oraredo1/GHSPRD/redo01_a.log
Mon Aug 20 16:01:09 2012
Thread 1 advanced to log sequence 280529 (LGWR switch)
  Current log# 3 seq# 280529 mem# 0: /oraredo3/GHSPRD/redo03_a.log
Mon Aug 20 16:02:00 2012
Errors in file /app/oracle/admin/GHSPRD/udump/ghsprd_ora_3670748.trc:
ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], []
Mon Aug 20 16:02:37 2012
Errors in file /app/oracle/admin/GHSPRD/udump/ghsprd_ora_562546.trc:
ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], []
Mon Aug 20 16:05:08 2012
Errors in file /app/oracle/admin/GHSPRD/udump/ghsprd_ora_1357122.trc:
ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], []
Mon Aug 20 16:05:23 2012
Errors in file /app/oracle/admin/GHSPRD/udump/ghsprd_ora_663790.trc:
ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], []
Mon Aug 20 16:15:36 2012
Thread 1 advanced to log sequence 280530 (LGWR switch)
  Current log# 2 seq# 280530 mem# 0: /oraredo2/GHSPRD/redo02_a.log
。。。。。
。。。。。
。。。。。

附件为： alert_log trace file

经测试发现 1.txt中sql
只有选择rule 的时候不报错。

单独去掉条件  and I.VEN_CODE = '100978' 则不需要选择优化器。

SQL> show parameter optimizer_mode
NAME                               TYPE       VALUE
------------------------------------ ----------- ------------------------------
optimizer_mode                      string    FIRST_ROWS

3.25 MB, 下载次数: 742

1.22 KB, 下载次数: 1229

收藏0 回复只看该作者道具举报

Maclean Liu(刘相兵

2^#

发表于 2012-8-21 14:12:27

action plan:

conn

alter session set "_optimizer_cost_based_transformation"=off ;

RUN YOUR STATEMENT 运行报错的语句

回复只看该作者道具举报

3^#

发表于 2012-8-21 14:13:42

GRUNDF@ erp  >alter session set "_optimizer_cost_based_transformation"=off ;
会话已更改。
GRUNDF@ erp  >
GRUNDF@ erp  >select
  2  sitemCode,
  3       itemName,
  4       orderQty,
  5       orderQty * (P.BUY_COST + P.BUY_VAT) AS orderAmt,
  6       returnQty,
  7       returnQty * (P.BUY_COST + P.BUY_VAT) AS returnAmt,
  8       exchOutQty,
  9       exchOutQty * (P. BUY_COST + P.BUY_VAT) AS exchOutAmt,
10       exchInQty,
11       exchInQty * (P.BUY_COST + P.BUY_VAT) AS exchInAmt
12 from (SELECT I.SITEM_CODE AS sitemCode,
13                I.ITEM_CODE AS itemCode,
14                I.ITEM_NAME AS itemName,
15                NVL(SUM(ORDER_OUT_QTY), 0) AS orderQty,
16                NVL(SUM(RETURN_QTY), 0) AS returnQty,
17                NVL(SUM(EXCH_OUT_QTY), 0) AS exchOutQty,
18                NVL(SUM(EXCH_IN_QTY), 0) AS exchInQty
19          FROM V_DAILYITEMSALE D, VITEM I
20          where  1 = 1
21             and I.VEN_CODE = '100978'
22          and D.GATHER_DATE between to_date('2012-08-01', 'yyyy-Mm-dd') AND
23                to_date('2012-08-01', 'yyyy-Mm-dd') + 0.99999
24          AND D.ITEM_CODE = I.ITEM_CODE
25          AND D.C_CODE in
26                ('2000', '6010', '6020', '6030', '6040', '6050', '6060')
27          GROUP BY I.SITEM_CODE, I.ITEM_CODE, I.ITEM_NAME) A,
28       TITEMPRICE P
29 where A.itemCode = P.ITEM_CODE
30 ORDER BY sitemCode desc
31  ;
*
第 31 行出现错误:
ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [],
[]

回复只看该作者道具举报

Maclean Liu(刘相兵

4^#

发表于 2012-8-21 14:14:48

explain plan for 你的语句

@?/rdbms/admin/utlxplp

贴出以上显示的执行计划

回复只看该作者道具举报

5^#

发表于 2012-8-21 14:16:18

GRUNDF@ erp  >explain plan for
  2  select
  3  sitemCode,
  4       itemName,
  5       orderQty,
  6       orderQty * (P.BUY_COST + P.BUY_VAT) AS orderAmt,
  7       returnQty,
  8       returnQty * (P.BUY_COST + P.BUY_VAT) AS returnAmt,
  9       exchOutQty,
10       exchOutQty * (P. BUY_COST + P.BUY_VAT) AS exchOutAmt,
11       exchInQty,
12       exchInQty * (P.BUY_COST + P.BUY_VAT) AS exchInAmt
13 from (SELECT I.SITEM_CODE AS sitemCode,
14                I.ITEM_CODE AS itemCode,
15                I.ITEM_NAME AS itemName,
16                NVL(SUM(ORDER_OUT_QTY), 0) AS orderQty,
17                NVL(SUM(RETURN_QTY), 0) AS returnQty,
18                NVL(SUM(EXCH_OUT_QTY), 0) AS exchOutQty,
19                NVL(SUM(EXCH_IN_QTY), 0) AS exchInQty
20          FROM V_DAILYITEMSALE D, VITEM I
21          where  1 = 1
22             and I.VEN_CODE = '100978'
23          and D.GATHER_DATE between to_date('2012-08-01', 'yyyy-Mm-dd') AND
24                to_date('2012-08-01', 'yyyy-Mm-dd') + 0.99999
25          AND D.ITEM_CODE = I.ITEM_CODE
26          AND D.C_CODE in
27                ('2000', '6010', '6020', '6030', '6040', '6050', '6060')
28          GROUP BY I.SITEM_CODE, I.ITEM_CODE, I.ITEM_NAME) A,
29       TITEMPRICE P
30 where A.itemCode = P.ITEM_CODE
31 ORDER BY sitemCode desc;
ORDER BY sitemCode desc
                     *
第 31 行出现错误:
ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [],
[]

回复只看该作者道具举报

Maclean Liu(刘相兵

6^#

发表于 2012-8-21 14:20:30

10.2.0.4 + AIX

ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [], []
Current SQL statement for this session:
select sitemCode, itemName, orderQty, orderQty * (P.BUY_COST + P.BUY_VAT) AS orderAmt, returnQty, returnQty * (P.BUY_COST + P.BUY_VAT) AS returnAmt, exchOutQty, exchOutQty * (P.BUY_COST + P.BUY_VAT) AS exchOutAmt, exchInQty, exchInQty * (P.BUY_COST + P.BUY_VAT) AS exchInAmt from (  SELECT I.SITEM_CODE AS sitemCode, I.ITEM_CODE AS itemCode, I.ITEM_NAME AS itemName, NVL(SUM(ORDER_OUT_QTY), 0) AS orderQty, NVL(SUM(RETURN_QTY), 0) AS returnQty, NVL(SUM(EXCH_OUT_QTY), 0) AS exchOutQty, NVL(SUM(EXCH_IN_QTY), 0) AS exchInQty FROM V_DAILYITEMSALE D,  VITEM I  where 1=1  and I.VEN_CODE = :1 and D.GATHER_DATE  between to_date( :2 ,'yyyy-Mm-dd')  AND to_date( :3 ,'yyyy-Mm-dd')+0.99999 AND D.ITEM_CODE = I.ITEM_CODE  AND D.C_CODE in ('2000', '6010','6020','6030','6040','6050','6060')  GROUP BY I.SITEM_CODE, I.ITEM_CODE, I.ITEM_NAME) A, TITEMPRICE P where A.itemCode = P.ITEM_CODE ORDER BY sitemCode desc
----- Call Stack Trace -----
calling             call    entry             argument values in hex
location          type    point             (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedst+001c       bl    ksedst1             000000000 ? FFFFFFFFFFF3994 ?
ksedmp+0290       bl    ksedst             104522410 ?
ksfdmp+0018       bl    03F2BA04
kgerinv+00dc       bl    _ptrgl
kgeasnmierr+004c    bl    kgerinv             FFFFFFFFFFF3D30 ? 000000000 ?
                                                1104FC1C0 ? 000000000 ?
                                                000000000 ?
qctcte+0070       bl    kgeasnmierr       110173528 ? 110484180 ?
                                                104664BE0 ? 100000001 ?
                                                000000000 ? 000000000 ?
                                                110410E38 ? 105107270 ?
qctodsc+0018       bl    qctcte             7000001DFD7B788 ? 000000000 ?
                                                000000000 ?
qctcopn+034c       bl    _ptrgl
qctcopn+0260       bl    qctcopn             7000001DFD7B788 ?
                                                7000001DFD7B740 ?
                                                FFFFFFFFFFF3F00 ?
kkfiflip+02d8       bl    qctcopn             700000010008000 ? 1104FC1C0 ?
                                                000000001 ?
kkfilsPred+0118    bl    kkfiflip          FFFFFFFFFFF40A0 ? 110D50730 ?
                                                102040A94 ?
kkfils+0098       bl    kkfilsPred          1104FC358 ? 1104D5970 ?
                                                FFFFFFFFFFF4140 ?
                                                2224202800000000 ?
                                                102042090 ? 110462040 ?
kkfiqs+0130       bl    kkfils             04424C140 ? 02424C140 ?
                                                110D739B8 ? 1104D5970 ?
                                                FFFFFFFFFFF4210 ?
                                                2224C2280089A260 ?
kkfiis1+0060       bl    kkfiqs             0124C0410 ? 02224C040 ?
                                                000000000 ? 02224C040 ?
kkfiis+0120       bl    kkfiis1             000000001 ? 000000000 ?
                                                000000101 ?
opixmf+004c       bl    01F925A4
kkfiGetCand+0040    bl    opixmf             104DDFD08 ? 104DDFC08 ?
                                                700000010027FE8 ?
vopmpf+0a7c       bl    kkfiGetCand       110D50A30 ?
apakkoqb+0328       bl    vopmpf             11050E130 ? 11055DD88 ?
                                                FFFFFFFFFFF4730 ? 10493984C ?
                                                10349B39C ?
apaqbdDescendents+0  bl    apakkoqb          11041FF48 ? 000000010 ?
3d4                                              FFFFFFFFFFF4F18 ?
apaqbdListReverse+0  bl    apaqbdDescendents 10179A300 ? 000000001 ?
06c                                              000000000 ?
apaqbd+000c       bl    apaqbdListReverse FFFFFFFFFFF4DE0 ? 110415458 ?
                                                FFFFFFFFFFF4D00 ?
apaqbdDescendents+0  bl    apaqbd             1025D70B0 ? 110415458 ?
140                                              000000001 ?
apaqbdListReverse+0  bl    apaqbdDescendents FFFFFFFFFFF6118 ? 000000000 ?
06c                                              110415458 ?
apaqbd+000c       bl    apaqbdListReverse FFFFFFFFFFF6118 ? 000000000 ?
                                                110415458 ?
apadrv+045c       bl    apaqbd             7000001D9FD0CF0 ? 400000004 ?
                                                1050A327C ?
opitca+13fc       bl    apadrv             000000000 ?
kksFullTypeCheck+00  bl    03F2B074
1c
rpiswu2+034c       bl    _ptrgl
kksSetBindType+0d28  bl    rpiswu2             70000020F77E868 ?
                                                2B0000002B ?
                                                FFFFFFFFFFF6520 ?
                                                FFFFFFFFFFF6528 ? 000000000 ?
                                                2B00000000 ? 00000000E ?
                                                000000000 ?
kksfbc+1054       bl    kksSetBindType    000000000 ? 110449208 ?
                                                000000000 ? 102FFFF79B8 ?
                                                000000000 ? 7000001C3E7EB48 ?
                                                1046AE5A0 ? 70000020F4A9F18 ?
opiexe+098c       bl    01F94550
kpoal8+0edc       bl    opiexe             FFFFFFFFFFFB6E4 ? 1104252D0 ?
                                                FFFFFFFFFFF98B8 ?
opiodr+0ae0       bl    _ptrgl
ttcpip+1020       bl    _ptrgl
opitsk+1124       bl    01F94840
opiino+0990       bl    opitsk             0FFFFD720 ? 000000000 ?
opiodr+0ae0       bl    _ptrgl
opidrv+0484       bl    01F93DA8
sou2o+0090          bl    opidrv             3C02D97C3C ? 4A0146928 ?
                                                FFFFFFFFFFFF620 ?
opimai_real+01bc    bl    01F917F4
main+0098          bl    opimai_real       000000000 ? 000000000 ?
__start+0098       bl    main                000000000 ? 000000000 ?

   SO: 7000001fdb80a80, type: 3, owner: 7000001fec3aeb8, flag: INIT/-/-/0x00
   (call) sess: cur 70000020f77e868, rec 700000209758970, usr 70000020f77e868; depth: 1
      ----------------------------------------
      SO: 7000001c7347128, type: 54, owner: 7000001fdb80a80, flag: INIT/-/-/0x00
      LIBRARY OBJECT PIN: pin=7000001c7347128 handle=70000020d284c20 mode=S lock=7000001e87a5b90
      user=70000020f77e868 session=70000020f77e868 count=2 mask=0701 savepoint=0x51 flags=[00]
      ----------------------------------------
      SO: 7000001e87a5b90, type: 53, owner: 7000001fdb80a80, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=7000001e87a5b90 handle=70000020d284c20 mode=S
      call pin=7000001c7347128 session pin=0 hpc=0000 hlc=0000
      htl=7000001e87a5c10[7000001e84dc028,7000001fdb95938] htb=7000001fdb95938 ssga=7000001fdb94ea0
      user=70000020f77e868 session=70000020f77e868 count=2 flags=PNC/[0400] savepoint=0x51
      LIBRARY OBJECT HANDLE: handle=70000020d284c20 mtx=70000020d284d50(0) cdp=0
      name=GRUNDF.TSDORDER
      hash=52d98381737022ad761ad8a2fcb81d98 timestamp=04-03-2010 02:29:31
      namespace=TABL flags=KGHP/TIM/SML/[02000000]
      kkkk-dddd-llll=0000-0709-0709 lock=S pin=S latch#=6 hpc=301c hlc=301c
      lwt=70000020d284cc8[70000020d284cc8,70000020d284cc8] ltm=70000020d284cd8[70000020d284cd8,70000020d284cd8]
      pwt=70000020d284c90[70000020d284c90,70000020d284c90] ptm=70000020d284ca0[70000020d284ca0,70000020d284ca0]
      ref=70000020d284cf8[70000020d284cf8,70000020d284cf8] lnd=70000020d284d10[70000020de5b148,70000020d402d38]
      LIBRARY OBJECT: object=7000001e1c0db30
      type=TABL flags=EXS/LOC[0005] pflags=[0000] status=VALD load=0
      DATA BLOCKS:
      data#    heap  pointer status pins change whr
      ----- -------- -------- --------- ---- ------ ---
            0 70000020dd59ef0 7000001e1c0dc48 I/P/A/-/- 0 NONE 00
            3 7000001f8b4eba8 7000001dc8f3c78 I/-/A/-/- 0 NONE 00
            8 7000001e1c0de28 7000001f23728b8 I/P/A/-/- 3 NONE 00
            9 7000001f8b4ea58 7000001d7ab3fc0 I/P/A/-/- 3 NONE 00
         10 7000001f8b4eae0 7000001ec6354c0 I/P/A/-/- 3 NONE 00

回复只看该作者道具举报

7^#

发表于 2012-8-21 14:20:31

使用 /*+ rule */  可以做explian

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 417920075
----------------------------------------------------------------------------
| Id  | Operation                            | Name                   |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT                      |                         |
| 1 |  SORT ORDER BY                      |                         |
| 2 | NESTED LOOPS                      |                         |
| 3 | VIEW                               |                         |
| 4 |    SORT GROUP BY                   |                         |
| 5 |    VIEW                            |                         |
| 6 |    SORT GROUP BY                   |                         |
| 7 |       NESTED LOOPS                   |                         |
| 8 |       VIEW                         |                         |
| 9 |       UNION-ALL                   |                         |
|* 10 |          TABLE ACCESS BY INDEX ROWID  | TDAILYSUMITEMUNITM       |
|* 11 |          INDEX RANGE SCAN          | IDX_TDAILYSUMITEMUNITM_01 |
|  12 |          CONCATENATION             |                         |
|  13 |          NESTED LOOPS             |                         |
|  14 |          TABLE ACCESS BY INDEX ROWID| TSDORDER                |
|* 15 |             INDEX RANGE SCAN       | PK_TSDORDER             |
|  16 |          TABLE ACCESS BY INDEX ROWID| VITEM                   |
|* 17 |             INDEX UNIQUE SCAN       | PK_VITEM                |
|  18 |          NESTED LOOPS             |                         |
|  19 |          TABLE ACCESS BY INDEX ROWID| TSDORDER                |
|* 20 |             INDEX RANGE SCAN       | PK_TSDORDER             |
|  21 |          TABLE ACCESS BY INDEX ROWID| VITEM                   |
|* 22 |             INDEX UNIQUE SCAN       | PK_VITEM                |
|  23 |          NESTED LOOPS             |                         |
|  24 |          TABLE ACCESS BY INDEX ROWID| TSDORDER                |
|* 25 |             INDEX RANGE SCAN       | PK_TSDORDER             |
|  26 |          TABLE ACCESS BY INDEX ROWID| VITEM                   |
|* 27 |             INDEX UNIQUE SCAN       | PK_VITEM                |
|  28 |          NESTED LOOPS             |                         |
|  29 |          TABLE ACCESS BY INDEX ROWID| TSDORDER                |
|* 30 |             INDEX RANGE SCAN       | PK_TSDORDER             |
|  31 |          TABLE ACCESS BY INDEX ROWID| VITEM                   |
|* 32 |             INDEX UNIQUE SCAN       | PK_VITEM                |
|  33 |          NESTED LOOPS             |                         |
|  34 |          TABLE ACCESS BY INDEX ROWID| TSDORDER                |
|* 35 |             INDEX RANGE SCAN       | PK_TSDORDER             |
|  36 |          TABLE ACCESS BY INDEX ROWID| VITEM                   |
|* 37 |             INDEX UNIQUE SCAN       | PK_VITEM                |
|  38 |          NESTED LOOPS             |                         |
|  39 |          TABLE ACCESS BY INDEX ROWID| TSDORDER                |
|* 40 |             INDEX RANGE SCAN       | PK_TSDORDER             |
|  41 |          TABLE ACCESS BY INDEX ROWID| VITEM                   |
|* 42 |             INDEX UNIQUE SCAN       | PK_VITEM                |
|  43 |          NESTED LOOPS             |                         |
|  44 |          TABLE ACCESS BY INDEX ROWID| TSDORDER                |
|* 45 |             INDEX RANGE SCAN       | PK_TSDORDER             |
|  46 |          TABLE ACCESS BY INDEX ROWID| VITEM                   |
|* 47 |             INDEX UNIQUE SCAN       | PK_VITEM                |
|  48 |          CONCATENATION             |                         |
|* 49 |          TABLE ACCESS BY INDEX ROWID | TDAILYSUMITEMUNITM       |
|* 50 |          INDEX RANGE SCAN          | IDX_TDAILYSUMITEMUNITM_01 |
|* 51 |          TABLE ACCESS BY INDEX ROWID | TDAILYSUMITEMUNITM       |
|* 52 |          INDEX RANGE SCAN          | IDX_TDAILYSUMITEMUNITM_01 |
|* 53 |          TABLE ACCESS BY INDEX ROWID | TDAILYSUMITEMUNITM       |
|* 54 |          INDEX RANGE SCAN          | IDX_TDAILYSUMITEMUNITM_01 |
|* 55 |          TABLE ACCESS BY INDEX ROWID | TDAILYSUMITEMUNITM       |
|* 56 |          INDEX RANGE SCAN          | IDX_TDAILYSUMITEMUNITM_01 |
|* 57 |          TABLE ACCESS BY INDEX ROWID | TDAILYSUMITEMUNITM       |
|* 58 |          INDEX RANGE SCAN          | IDX_TDAILYSUMITEMUNITM_01 |
|* 59 |          TABLE ACCESS BY INDEX ROWID | TDAILYSUMITEMUNITM       |
|* 60 |          INDEX RANGE SCAN          | IDX_TDAILYSUMITEMUNITM_01 |
|* 61 |          TABLE ACCESS BY INDEX ROWID | TDAILYSUMITEMUNITM       |
|* 62 |          INDEX RANGE SCAN          | IDX_TDAILYSUMITEMUNITM_01 |
|* 63 |       TABLE ACCESS BY INDEX ROWID | VITEM                   |
|* 64 |       INDEX UNIQUE SCAN          | PK_VITEM                |
|  65 | TABLE ACCESS BY INDEX ROWID       | TITEMPRICE             |
|* 66 |    INDEX RANGE SCAN                | IDX_TITEMPRICE_01       |

回复只看该作者道具举报

8^#

发表于 2012-8-21 14:20:58

----------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------
  10 - filter(("B"."C_CODE"='2000' OR "B"."C_CODE"='6010' OR
            "B"."C_CODE"='6020' OR "B"."C_CODE"='6030' OR "B"."C_CODE"='6040' OR
            "B"."C_CODE"='6050' OR "B"."C_CODE"='6060') AND "GIFT_YN"='0')
  11 - access("B"."GATHER_DATE">=TO_DATE(' 2012-08-01 00:00:00',
            'syyyy-mm-dd hh24:mi:ss') AND "GATHER_DATE"<TO_DATE(TO_CHAR(SYSDATE@!)))
   filter("B"."GATHER_DATE"<=TO_DATE(' 2012-08-01 23:59:59',
            'syyyy-mm-dd hh24:mi:ss'))
  15 - access("GATHER_DATE"=TO_DATE(TO_CHAR(SYSDATE@!)) AND
            "A"."C_CODE"='6060' AND "A"."GIFT_YN"='0')
   filter("A"."GIFT_YN"='0' AND "A"."GATHER_DATE"<=TO_DATE('
            2012-08-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND
            "A"."GATHER_DATE">=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd
            hh24:mi:ss'))
  17 - access("A"."ITEM_CODE"="I"."ITEM_CODE")
  20 - access("GATHER_DATE"=TO_DATE(TO_CHAR(SYSDATE@!)) AND
            "A"."C_CODE"='6050' AND "A"."GIFT_YN"='0')
   filter("A"."GIFT_YN"='0' AND "A"."GATHER_DATE"<=TO_DATE('
            2012-08-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND
            "A"."GATHER_DATE">=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd
            hh24:mi:ss'))
  22 - access("A"."ITEM_CODE"="I"."ITEM_CODE")
  25 - access("GATHER_DATE"=TO_DATE(TO_CHAR(SYSDATE@!)) AND
            "A"."C_CODE"='6040' AND "A"."GIFT_YN"='0')
   filter("A"."GIFT_YN"='0' AND "A"."GATHER_DATE"<=TO_DATE('
            2012-08-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND
            "A"."GATHER_DATE">=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd
            hh24:mi:ss'))
  27 - access("A"."ITEM_CODE"="I"."ITEM_CODE")
  30 - access("GATHER_DATE"=TO_DATE(TO_CHAR(SYSDATE@!)) AND
            "A"."C_CODE"='6030' AND "A"."GIFT_YN"='0')
   filter("A"."GIFT_YN"='0' AND "A"."GATHER_DATE"<=TO_DATE('
            2012-08-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND
            "A"."GATHER_DATE">=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd
            hh24:mi:ss'))
  32 - access("A"."ITEM_CODE"="I"."ITEM_CODE")
  35 - access("GATHER_DATE"=TO_DATE(TO_CHAR(SYSDATE@!)) AND
            "A"."C_CODE"='6020' AND "A"."GIFT_YN"='0')
   filter("A"."GIFT_YN"='0' AND "A"."GATHER_DATE"<=TO_DATE('
            2012-08-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND
            "A"."GATHER_DATE">=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd
            hh24:mi:ss'))
  37 - access("A"."ITEM_CODE"="I"."ITEM_CODE")
  40 - access("GATHER_DATE"=TO_DATE(TO_CHAR(SYSDATE@!)) AND
            "A"."C_CODE"='6010' AND "A"."GIFT_YN"='0')
   filter("A"."GIFT_YN"='0' AND "A"."GATHER_DATE"<=TO_DATE('
            2012-08-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND
            "A"."GATHER_DATE">=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd
            hh24:mi:ss'))
  42 - access("A"."ITEM_CODE"="I"."ITEM_CODE")
  45 - access("GATHER_DATE"=TO_DATE(TO_CHAR(SYSDATE@!)) AND
            "A"."C_CODE"='2000' AND "A"."GIFT_YN"='0')
   filter("A"."GIFT_YN"='0' AND "A"."GATHER_DATE"<=TO_DATE('
            2012-08-01 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND
            "A"."GATHER_DATE">=TO_DATE(' 2012-08-01 00:00:00', 'syyyy-mm-dd
            hh24:mi:ss'))
  47 - access("A"."ITEM_CODE"="I"."ITEM_CODE")
  49 - filter("B"."GIFT_YN"='0')
  50 - access("GATHER_DATE"=TO_DATE(TO_CHAR(SYSDATE@!)) AND
            "B"."C_CODE"='6060')
   filter("B"."GATHER_DATE"<=TO_DATE(' 2012-08-01 23:59:59',
            'syyyy-mm-dd hh24:mi:ss') AND "B"."GATHER_DATE">=TO_DATE(' 2012-08-01
            00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  51 - filter("B"."GIFT_YN"='0')
  52 - access("GATHER_DATE"=TO_DATE(TO_CHAR(SYSDATE@!)) AND
            "B"."C_CODE"='6050')
   filter("B"."GATHER_DATE"<=TO_DATE(' 2012-08-01 23:59:59',
            'syyyy-mm-dd hh24:mi:ss') AND "B"."GATHER_DATE">=TO_DATE(' 2012-08-01
            00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  53 - filter("B"."GIFT_YN"='0')
  54 - access("GATHER_DATE"=TO_DATE(TO_CHAR(SYSDATE@!)) AND
            "B"."C_CODE"='6040')
   filter("B"."GATHER_DATE"<=TO_DATE(' 2012-08-01 23:59:59',
            'syyyy-mm-dd hh24:mi:ss') AND "B"."GATHER_DATE">=TO_DATE(' 2012-08-01
            00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  55 - filter("B"."GIFT_YN"='0')
  56 - access("GATHER_DATE"=TO_DATE(TO_CHAR(SYSDATE@!)) AND
            "B"."C_CODE"='6030')
   filter("B"."GATHER_DATE"<=TO_DATE(' 2012-08-01 23:59:59',
            'syyyy-mm-dd hh24:mi:ss') AND "B"."GATHER_DATE">=TO_DATE(' 2012-08-01
            00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  57 - filter("B"."GIFT_YN"='0')
  58 - access("GATHER_DATE"=TO_DATE(TO_CHAR(SYSDATE@!)) AND
            "B"."C_CODE"='6020')
   filter("B"."GATHER_DATE"<=TO_DATE(' 2012-08-01 23:59:59',
            'syyyy-mm-dd hh24:mi:ss') AND "B"."GATHER_DATE">=TO_DATE(' 2012-08-01
            00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  59 - filter("B"."GIFT_YN"='0')
  60 - access("GATHER_DATE"=TO_DATE(TO_CHAR(SYSDATE@!)) AND
            "B"."C_CODE"='6010')
   filter("B"."GATHER_DATE"<=TO_DATE(' 2012-08-01 23:59:59',
            'syyyy-mm-dd hh24:mi:ss') AND "B"."GATHER_DATE">=TO_DATE(' 2012-08-01
            00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  61 - filter("B"."GIFT_YN"='0')
  62 - access("GATHER_DATE"=TO_DATE(TO_CHAR(SYSDATE@!)) AND
            "B"."C_CODE"='2000')
   filter("B"."GATHER_DATE"<=TO_DATE(' 2012-08-01 23:59:59',
            'syyyy-mm-dd hh24:mi:ss') AND "B"."GATHER_DATE">=TO_DATE(' 2012-08-01
            00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
  63 - filter("I"."VEN_CODE"='100978')
  64 - access("ITEM_CODE"="I"."ITEM_CODE")
  66 - access("A"."ITEMCODE"="P"."ITEM_CODE")
Note
-----
- rule based optimizer used (consider using cbo)
已选择182行。

回复只看该作者道具举报

Maclean Liu(刘相兵

9^#

发表于 2012-8-21 14:26:06

action plan:

alter system flush shared_pool;
alter system flush shared_pool;

alter session set "_optimizer_cost_based_transformation"=off ;

RUN YOUR STATMENT

不行的话删除 GRUNDF.TSDORDER 表的统计信息，或者尝试重建该表

回复只看该作者道具举报

10^#

发表于 2012-8-21 14:57:32

name=GRUNDF.TSDORDER 不能收集统计信息需要先删除吗？

回复只看该作者道具举报

Maclean Liu(刘相兵

11^#

发表于 2012-8-21 14:58:31

不能收集统计信息具体什么意思？

回复只看该作者道具举报

12^#

发表于 2012-8-21 15:03:45

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'GRUNDF',
                              tabname => upper('TSDORDER'),
                              estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                              method_opt => 'for all columns size AUTO',
                              degree => DBMS_STATS.AUTO_DEGREE,
                              cascade=>TRUE
                              );
END;
/

刚是格式错了，我又重新收集了下，收集可以成功
但是执行sql语句还是报600的错误

GRUNDF@ erp  >select --/*+ all_rows */
  2  sitemCode,
  3       itemName,
  4       orderQty,
  5       orderQty * (P.BUY_COST + P.BUY_VAT) AS orderAmt,
  6       returnQty,
  7       returnQty * (P.BUY_COST + P.BUY_VAT) AS returnAmt,
  8       exchOutQty,
  9       exchOutQty * (P. BUY_COST + P.BUY_VAT) AS exchOutAmt,
10       exchInQty,
11       exchInQty * (P.BUY_COST + P.BUY_VAT) AS exchInAmt
12 from (SELECT I.SITEM_CODE AS sitemCode,
13                I.ITEM_CODE AS itemCode,
14                I.ITEM_NAME AS itemName,
15                NVL(SUM(ORDER_OUT_QTY), 0) AS orderQty,
16                NVL(SUM(RETURN_QTY), 0) AS returnQty,
17                NVL(SUM(EXCH_OUT_QTY), 0) AS exchOutQty,
18                NVL(SUM(EXCH_IN_QTY), 0) AS exchInQty
19          FROM V_DAILYITEMSALE D, VITEM I
20          where  1 = 1
21             and I.VEN_CODE = '100978'
22          and D.GATHER_DATE between to_date('2012-08-01', 'yyyy-Mm-dd') AND
23                to_date('2012-08-01', 'yyyy-Mm-dd') + 0.99999
24          AND D.ITEM_CODE = I.ITEM_CODE
25          AND D.C_CODE in
26                ('2000', '6010', '6020', '6030', '6040', '6050', '6060')
27          GROUP BY I.SITEM_CODE, I.ITEM_CODE, I.ITEM_NAME) A,
28       TITEMPRICE P
29 where A.itemCode = P.ITEM_CODE
30 ORDER BY sitemCode desc
31  ;
*
第 31 行出现错误:
ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [],
[]

[ 本帖最后由 bal 于 2012-8-21 15:10 编辑 ]

回复只看该作者道具举报

Maclean Liu(刘相兵

13^#

发表于 2012-8-21 15:12:04

9楼的action plan是否实施了？

回复只看该作者道具举报

14^#

发表于 2012-8-21 15:15:30

还没有，这个需要凌晨处理~ 到时做个systemdump再

回复只看该作者道具举报

15^#

发表于 2012-8-21 16:28:17

Connected.
SQL> alter system flush shared_pool;
alter system flush shared_pool;
System altered.
SQL>
System altered.
SQL>
SQL>
SQL> conn grundf/****Connected.
SQL> alter session set "_optimizer_cost_based_transformation"=off ;
Session altered.
SQL> select --/*+ all_rows */
  2  sitemCode,
  3       itemName,
  4       orderQty,
  5       orderQty * (P.BUY_COST + P.BUY_VAT) AS orderAmt,
  6       returnQty,
  7       returnQty * (P.BUY_COST + P.BUY_VAT) AS returnAmt,
  8       exchOutQty,
  9       exchOutQty * (P. BUY_COST + P.BUY_VAT) AS exchOutAmt,
10       exchInQty,
11       exchInQty * (P.BUY_COST + P.BUY_VAT) AS exchInAmt
12 from (SELECT I.SITEM_CODE AS sitemCode,
13                I.ITEM_CODE AS itemCode,
14                I.ITEM_NAME AS itemName,
15                NVL(SUM(ORDER_OUT_QTY), 0) AS orderQty,
16                NVL(SUM(RETURN_QTY), 0) AS returnQty,
17                NVL(SUM(EXCH_OUT_QTY), 0) AS exchOutQty,
18                NVL(SUM(EXCH_IN_QTY), 0) AS exchInQty
19          FROM V_DAILYITEMSALE D, VITEM I
20          where  1 = 1
21             and I.VEN_CODE = '100978'
22          and D.GATHER_DATE between to_date('2012-08-01', 'yyyy-Mm-dd') AND
23                to_date('2012-08-01', 'yyyy-Mm-dd') + 0.99999
24          AND D.ITEM_CODE = I.ITEM_CODE
25          AND D.C_CODE in
26                ('2000', '6010', '6020', '6030', '6040', '6050', '6060')
27          GROUP BY I.SITEM_CODE, I.ITEM_CODE, I.ITEM_NAME) A,
28       TITEMPRICE P
29 where A.itemCode = P.ITEM_CODE
30 ORDER BY sitemCode desc
31  ;
*
ERROR at line 31:
ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [],
[]

SQL>

回复只看该作者道具举报

Maclean Liu(刘相兵

16^#

发表于 2012-8-21 16:29:33

Workaround method:

1. 使用RULE HINT 绕过该问题

2. 升级到版本10.2.0.5.8

回复只看该作者道具举报

17^#

发表于 2012-8-21 17:49:41

刘大，问题解决了~
是由于那天下午3点钟在 grundf.TSDORDER 上面新建了一个索引 GRUNDF.TSDORDER("ITEM_CODE","GATHER_DATE");
导致的，在删除了这个索引后，症状就解除了。

请问应该怎么去判断这个索引的影响？

回复只看该作者道具举报

18^#

发表于 2012-8-21 19:46:46

有没有索引定义和表GRUNDF.TSDORDER结构
很难想象一个正常的索引会导致解析阶段出现问题：
This error(ora-600[qctcte1]) is reported when an inconsistent datatype is reported during typechecking or when parsing a query.

在oracle forum里有以下例子，挺有意思：
SQL> CREATE TABLE INT_TEST AS
  2 SELECT 1 ID FROM DUAL;

Table created.

SQL> CREATE INDEX IDX_TEST1 ON INT_TEST(ID,(SELECT 1 FROM DUAL));

Index created.

SQL> INSERT INTO  INT_TEST VALUES(1);
INSERT INTO  INT_TEST VALUES(1)
         *
ERROR at line 1:
ORA-00600: internal error code, arguments: [qctcte1], [0], [], [], [], [], [],
[]

回复只看该作者道具举报

19^#

发表于 2012-8-21 21:49:28

是普通的index 考虑如果重建是否还是会如此

回复只看该作者道具举报

20^#

发表于 2012-8-24 16:54:49

这个后，创建索引语句
create index IDX_TSDORDER_01 on TSDORDER(ITEM_CODE,GATHER_DATE) tablespace base_idx;

--根据oracle的建议 sql>alter session set "_disable_function_based_index" = true;

这样就不会出现ora错误，可是这个索引不是function的。。

回复只看该作者道具举报

21^#

发表于 2012-8-24 16:57:29

建议如下：
Thanks for the update.
This error is quite common, with numerous bugs filed, and numerous
workarounds.
(see Note 248095.1 - ORA-600 [qctcte1] for a full listing).
Please try the following parameters (do these 1 at a time, so we can find
out which one, resolves the error):
- alter session set "_optimizer_connect_by_cost_based" = false;
- alter session set "_disable_function_based_index" = true; --这个起作用
- alter session set "_unnest_subquery"=false;
- alter session set "_connect_by_use_union_all"=false;

回复只看该作者道具举报

|手机版|Archiver|Oracle数据库数据恢复、性能优化

GMT+8, 2024-12-25 16:17 , Processed in 0.061003 second(s), 24 queries .

Powered by Discuz! X2.5

© 2001-2012 Comsenz Inc.

回顶部

TEL/電話+86 13764045638

Email service@parnassusdata.com

QQ 47079569