ORA-00600: internal error code, arguments: [qkaffsindex3], [], [], [], [], [], [], []

SQL> select * from V$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bi
PL/SQL Release 10.2.0.2.0 - Production
CORE 10.2.0.2.0 Production
TNS for Solaris: Version 10.2.0.2.0 - Production
NLSRTL Version 10.2.0.2.0 - Production

SQL> ANALYZE TABLE "SAPKBW"."/BI0/F0TCT_C21" PARTITION ("/BI0/F0TCT_C210000000052") ESTIMATE STATISTICS SAMPLE 60 PERCENT FOR TABLE FOR ALL LOCAL INDEXES FOR COLUMNS SIZE 75 "KEY_0TCT_C21P","KEY_0TCT_C212","KEY_0TCT_C213","KEY_0TCT_C21T" 2 3 4 / ANALYZE TABLE "SAPKBW"."/BI0/F0TCT_C21" PARTITION ("/BI0/F0TCT_C210000000052") ESTIMATE * ERROR at line 1: ORA-00600: internal error code, arguments: [qkaffsindex3], [], [], [], [], [], [], []

[@more@]

SQL> alter session set "_fast_full_scan_enabled" =false;

Session altered.

SQL> ANALYZE TABLE "SAPKBW"."/BI0/F0TCT_C21" PARTITION ("/BI0/F0TCT_C210000000052") ESTIMATE
STATISTICS SAMPLE 60 PERCENT FOR TABLE FOR ALL LOCAL INDEXES
FOR COLUMNS SIZE 75 "KEY_0TCT_C21P","KEY_0TCT_C212","KEY_0TCT_C213","KEY_0TCT_C21T" 2 3
4
SQL> ANALYZE TABLE "SAPKBW"."/BI0/F0TCT_C21" PARTITION ("/BI0/F0TCT_C210000000052") ESTIMATE
STATISTICS SAMPLE 60 PERCENT FOR TABLE FOR ALL LOCAL INDEXES
FOR COLUMNS SIZE 75 "KEY_0TCT_C21P","KEY_0TCT_C212","KEY_0TCT_C213","KEY_0TCT_C21T" 2 3
4 /

Table analyzed.

it can be done as:

SQL> alter system set "_fast_full_scan_enabled" =false;

System altered.

SQL> show parameter _fast

NAME TYPE VALUE
------------------------------------ ------ ------------------------------
_fast_full_scan_enabled boolea FALSE

But why?

SQL> desc "SAPKBW"."/BI0/F0TCT_C21"
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
KEY_0TCT_C21P NOT NULL NUMBER(10)
KEY_0TCT_C21T NOT NULL NUMBER(10)
KEY_0TCT_C21U NOT NULL NUMBER(10)
KEY_0TCT_C212 NOT NULL NUMBER(10) KEY_0TCT_C213 NOT NULL NUMBER(10)
TCTDURTION NOT NULL FLOAT(126)
TCTSTRTDAT NOT NULL VARCHAR2(24)
TCTSTRTTIM NOT NULL VARCHAR2(18)
TCTSTRTTST NOT NULL NUMBER(17,3)
TCTENDDAT NOT NULL VARCHAR2(24)
TCTSTAUIK NOT NULL NUMBER(17,3)
TCTENDTIM NOT NULL VARCHAR2(18)
TCTENDTST NOT NULL NUMBER(17,3)
TCTSTIMEK

Index Fast Full Scan: goes to the first block of the segment, and does multi-block reads through the segment, picking up branch and leaf blocks, discarding the branches and using the data in the leaf blocks as if they were skinny tables. Does not return the data in order.It typically uses db file scattered reads to get data from disk

If fast full scans are troubling you in general, there is a hidden init.ora parameter to disable them "_fast_full_scan_enabled"=false.

However, I'd advise that you consult oracle support before setting this. Why is oracle thinking that FFS would be better over a min/max optimization:

One reason would probably be that the lastcolumn has a low cardinality. Thus oracle determines that a FFS on the index will be better since it needs to read a high % of the index anyway....

but remember that column (not null) can not use FFS

This is from doc. of oracle
"

Fast full index scans are an alternative to a full table scan when the index contains all the columns that are needed for the query, and at least one column in the index key has the NOT NULL constraint. A fast full scan accesses the data in the index itself, without accessing the table. It cannot be used to eliminate a sort operation, because the data is not ordered by the index key. It reads the entire index using multiblock reads, unlike a full index scan, and can be parallelized.

You can specify fast full index scans with the initialization parameter OPTIMIZER_FEATURES_ENABLE or the INDEX_FFS hint. Fast full index scans cannot be performed against bitmap indexes.

A fast full scan is faster than a normal full index scan in that it can use multiblock I/O and can be parallelized just like a table scan.

"

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/67/viewspace-983028/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/67/viewspace-983028/