查询USER_SEGMENTS 遭受ORA-00600 [kkoitbp-corruption]

在本机的WINDOWS测试环境中遇到了下面这个错误：

 

> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

 

> select sum(bytes)/1024/1024 from user_segments where segment_name='T1';
select sum(bytes)/1024/1024 from user_segments where segment_name='T1'
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kkoitbp-corruption], [], [], [], [], [], [], []

 

查询了一下MOS，与这个错误相关的信息并不多。
如果WHERE条件的谓词超过了6W个，可能会遭受这个BUG，另一个就是启用了星形查询也可能会遇到这个BUG。

• Bug 10348427  ORA-600 [kkoitbp-corruption] from query with many WHERE clause predicates (>60000)

A select query with a lot of conditions in the WHERE clause may fail with ORA-600 [kkoitbp-corruption] or other errors.
 
Rediscovery Notes:
  The failing query has a large number of conditions in the   WHERE clause (in the order of 65530 or more).
 
Workaround
  Recode the SQL not to use such long predicate lists in  a single statement.

• Bug 10038373 - Query fails with ORA-600 [kkoitbp-corruption] when star transformation is enabled [ID 10038373.8]

When star transformation is enabled (star_transformation_enabled parameter is TRUE), and in some other limited cases, a query may fail with ORA-600 [kkoitbp-corruption]
 

当前的查询谓词数很显然没有超过60000W，另一个就是启用了星形查询，不过即时将star_transformation_enabled设置为FALSE，问题还是存在：

 

> show parameter star

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
dg_broker_start                      boolean     FALSE
drs_start                            boolean     FALSE
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
fast_start_parallel_rollback         string      LOW
log_archive_start                    boolean     FALSE
star_transformation_enabled          string      TRUE

> alter system set star_transformation_enabled=false;

系统已更改。

> select sum(bytes)/1024/1024 from user_segments where segment_name='T1';
select sum(bytes)/1024/1024 from user_segments where segment_name='T1'
*
第 1 行出现错误:
ORA-00600: 内部错误代码, 参数: [kkoitbp-corruption], [], [], [], [], [], [], []

如果使用RULE提示或者将优化器设置为RULE模式即可避免这个BUG。

> select /*+rule*/sum(bytes)/1024/1024 from user_segments where segment_name='T1';

SUM(BYTES)/1024/1024
--------------------
                   6

> alter session set optimizer_mode=rule;

会话已更改。

> select sum(bytes)/1024/1024 from user_segments where segment_name='T1';

SUM(BYTES)/1024/1024
--------------------
                   6

 

又是一个和CBO优化器相关的BUG。