ORA-00600:[qctcte1]内部错误一例

一套AIX上的4节点10.2.0.4 RAC系统在1月份出现实例hang住的现象,并伴随有ORA-00600:[qctcte1]内部错误,trace文件内容如下:

siposrc1_ora_102944.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
ORACLE_HOME = /oracle/product/10.2.0/db_1
System name: AIX
Node name: jszydb1
Release: 3
Version: 5
Machine: 00CE31834C00
Instance name: siposrc1
Redo thread mounted by this instance: 1
Oracle process number: 34
Unix process pid: 102944, image: oracle@jszydb1

*** ACTION NAME:() 2010-01-18 15:53:11.530
*** MODULE NAME:(JDBC Thin Client) 2010-01-18 15:53:11.530
*** SERVICE NAME:(siposrc) 2010-01-18 15:53:11.530
*** SESSION ID:(2175.6953) 2010-01-18 15:53:11.530
*** 2010-01-18 15:53:11.530
ksedmp: internal or fatal error
ORA-00600: 内部错误代码, 参数: [qctcte1], [0], [], [], [], [], [], []
Current SQL statement for this session:
SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_00000"), NVL(SUM(C2),:"SYS_B_00001") FROM (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("SIPO_ECLA$_TEMP") FULL("SIPO_ECLA$_TEMP") NO_PARALLEL_INDEX("SIPO_ECLA$_TEMP") */ :"SYS_B_00002" AS C1, CASE WHEN "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00003" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00004" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00005" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00006" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00007" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00008" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00009" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00010" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00011" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00012" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00013" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00014" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00015" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00016" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00017" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_00018" OR "SIPO_ECLA
.

.
$_TEMP"."SEQ_ID"=:"SYS_B_40000" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_40001" OR "SIPO_ECLA$_TEMP"."SEQ_ID"=:"SYS_B_40002" THEN :"SYS_B_40003" ELSE :"SYS_B_40004" END AS C2 FROM "SIPO_ECLA$_TEMP" SAMPLE BLOCK (:"SYS_B_40005" , :"SYS_B_40006") SEED (:"SYS_B_40007") "SIPO_ECLA$_TEM

STACK
qctcte qctocssm qctcopn qctcopn xtyxcssr xtyopncb qctcopn qctcpqb <- qctcpqbl <- xtydrv <- opitca <- kksFullTypeCheck <- rpiswu2 <- kksSetBindType <- kksfbc <- opiexe <- opiall0 <- opikpr <- opiodr <- rpidrus <- skgmstack <- rpidru <- rpiswu2 <- kprball <- IPRA <- IPRA <- kkedsSel <- kkecdn <- kkotap <- kkoiqb <- kkooqb <- kkoqbc <- apakkoqb <- apaqbdDescendents <- 3d4 <- apaqbdListReverse <- 06c <- apaqbd <- apadrv <- opitca <- kksLoadChild <- kxsGetRuntimeLock <- 810 <- kksfbc <- kkspsc0 <- kksParseCursor <- opiosq0 <- opiall0 <- opial7 <- opiodr <- ttcpip <- opitsk <- opiino <- opiodr <- opidrv

提交SR,MOS认为可能是Bug 6666870,给出以下方案:
There are a large number of possible bugs but Bug 6666870 is the most likely culprit. There is no one off patch available. 10205 is not yet available.

ACTION PLAN:
=============
1.Please disable Cost Based Transformation as a workaround, this can be done in the init.ora/spfile or at the session level, for example:
SQL> alter session set "_optimizer_cost_based_transformation"=off ;

2.Apply the 10205 patch set when it becomes available.

3.If the optimiser change fails to resolve your issue, please advise us of any recent changes to your DB or server?

4.In particular, did you recently apply the CPUJAN2008 Patch?
If so, please see Note.558901.1 Ext/Mod ORA-00600 internal error code, arguments [qctcte1] After Applying CPUJAN2008 Patch

5.If there is a function based index involved, please see;
Note.788124.1 Ext/Pub ORA-00600 [qctcte1] With Function Based Index Access

6.Changing your code to eliminate the parallel clauses may also act as a workaround.

Comments

  1. admin says

    Hdr: 6666870 10.2.0.3.0 RDBMS 10.2.0.3.0 QRY OPTIMIZER PRODID-5 PORTID-212 ORA-600
    Abstract: COMPLEX SELECT AGAINST THE TABLE FAILS WITH ORA-600 [QCTCTE1]
    PROBLEM:
    ——–
    ORA-600 [QCTCTE1] occurs while selecting against the tables.
    Query failed in SYNTAX state

    DIAGNOSTIC ANALYSIS:
    ——————–
    Applied the following patches, but the issue still persist.

    5395270 -> OERI[qctcte1] / dump / wrong results from view merging
    6075238 -> ORA-600 [QCTCTE1] REPORTED ON A QUERY
    6167999
    # 5382842:SELECT FAILED WITH ORA-600 [QCTCTE1]
    # 5371149:ORA-600 [QCTCTE1] [0] FROM QUERY WITH CONNECT BY AND LEFT JOIN
    6012053 -> OERI [qctcte1] from GROUPING SETs query

    WORKAROUND:
    ———–
    alter session set “_optimizer_cost_based_transformation”=off ;

    RELATED BUGS:
    ————-

    REPRODUCIBILITY:
    —————-
    Error reproducible with the table definition and statistics

    TEST CASE:
    ———-
    Test case available in house

    STACK TRACE:
    ————
    ksedst ksedmp ksfdmp kgerinv kgeasnmierr qctcte
    qctoreo qctcopn qctclog qctclog qctcpqb qctcpqbl
    xtydrv kkqcttcalo kkqctdrvSU nsotruns nsotruns nsotruns
    nsotruns nsotruns nsotruns nsoqbc kkqctdrvTD kkqdrv

    RELEASE NOTES:
    ]]ORA-600[qctcte1] when cost based transformation is enabled and
    ]]and coorelated column is located in set query block view.

    REDISCOVERY INFORMATION:
    ORA-600[qctcte1] if cost based transformation is enabled,
    correlated operand is located in set query block
    WORKAROUND:
    disable cost based transformation

  2. admin says

    Init.ora Parameter “_OPTIMIZER_COST_BASED_TRANSFORMATION” [Hidden] Reference Note
    This parameter is introduced in 10g.
    The default value is determined by the setting of OPTIMIZER_FEATURES_ENABLE.
    A setting of OPTIMIZER_FEATURES_ENABLE >= 10.1.0 allows cost based
    transformations to occur. See Note:62337.1 .

    _OPTIMIZER_COST_BASED_TRANSFORMATION controls whether or not the
    optimizer tries different transformations against a query
    using the cost with and without the transformations in order
    to determine if a transformation is useful or not.
    The parameter can be set to any of:
    “exhaustive”, “iterative”, “linear”, “on”, “off”
    giving some control over how much effort is given to costing
    various transformations.

    Cost based transformation can add a high overhead at parse time
    but can yeild considerable benefits by way of a better plan
    for the statement.

Comment

*

沪ICP备14014813号-2

沪公网安备 31010802001379号