利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL

做为一个DBA,你大概习惯了定期要抓取数据库中的非绑定变量SQL,这些SQL经常扮演着一箱苹果中蛀虫的角色。
看到下列SQL你必定觉得眼熟:

SELECT substr(sql_text, 1, 80), count(1)
  FROM v$sql
 GROUP BY substr(sql_text, 1, 80)
HAVING count(1) > 10
 ORDER BY 2

是的,以上这段抓取literal sql的脚本大约从8i时代就开始流行了,在那时它很popular也很休闲,使用它或许还会给你的雇主留下一丝神秘感。不过今天我要告诉你的是,它彻底过时了,落伍了,已经不是fashion master了。
10g以后v$SQL动态性能视图增加了FORCE_MATCHING_SIGNATURE列,其官方定义为”The signature used when the CURSOR_SHARING parameter is set to FORCE”,也就是Oracle通过将原SQL_TEXT转换为可能的FORCE模式后计算得到的一个SIGNATURE值。这么说可能不太形象,我们来具体看一下:

SQL> create table YOUYUS (t1 int);
Table created.

SQL> alter system flush shared_pool;
System altered.

SQL>select /*test_matching_a*/ * from YOUYUS where t1=1;
no rows selected

SQL>select /*test_matching_a*/ * from YOUYUS where t1=2;
no rows selected

SQL>select /*test_matching_a*/ * from YOUYUS where t1=3;
no rows selected

SQL> col sql_text format a55;
SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
  2    FROM V$SQL
  3   WHERE sql_text like '%test_matching_a%'
  4     and sql_text not like '%like%';

SQL_TEXT                                                FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
------------------------------------------------------- ------------------------ ------------------------
select /*test_matching_a*/ * from YOUYUS where t1=2          4.59124694481197E18      1.00267830752731E19
select /*test_matching_a*/ * from YOUYUS where t1=3          4.59124694481197E18      1.61270448861426E19
select /*test_matching_a*/ * from YOUYUS where t1=1          4.59124694481197E18      1.36782048270058E18

/*以上将变量硬编码至SQL中的游标,FORCE_MATCHING_SIGNATURE值完全相同,而EXACT_MATCHING_SIGNATURE值各有不同。FORCE_MATCHING_SIGNATURE值相同说明在游标共享FORCE模式下,这些游标满足CURSOR SHARING的条件 */

SQL> alter system flush shared_pool;
System altered.
SQL> alter session set cursor_sharing=FORCE;
Session altered.

SQL>select /*test_matching_a*/ * from YOUYUS where t1=1;
no rows selected

SQL>select /*test_matching_a*/ * from YOUYUS where t1=2;
no rows selected

SQL>select /*test_matching_a*/ * from YOUYUS where t1=3;
no rows selected

SQL> col sql_text for a70
SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
  2    FROM V$SQL
  3   WHERE sql_text like '%test_matching_a%'
  4     and sql_text not like '%like%';

SQL_TEXT                                                               FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
---------------------------------------------------------------------- ------------------------ ------------------------
select /*test_matching_a*/ * from YOUYUS where t1=:"SYS_B_0"                4.59124694481197E18      4.59124694481197E18

/*FORCE模式下将SQL文本中的变量值转换成了:SYS_B形式,EXACT_MATCHING_SIGNATURE也随之等同于FORCE_MATCHING_SIGNATURE了*/

以上演示说明了FORCE_MATCHING_SIGNATURE列可以帮助我们找出那些潜在可以共享的游标(也包括了因非绑定问题造成的游标无法共享),现在我们利用它来完善捕获非绑定变量SQL的脚本:

SQL> alter system flush shared_pool;
System altered.

SQL> select  /*test_matching_b*/ * from YOUYUS where t1=1;
no rows selected

SQL> select  /*test_matching_b*/ * from YOUYUS where t1='1';            //我有引号,我与众不同!
no rows selected

SQL> col sql_text for a70

SQL> select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_SIGNATURE
  2    FROM V$SQL
  3   WHERE sql_text like '%test_matching_b%'
  4     and sql_text not like '%like%';

SQL_TEXT                                                               FORCE_MATCHING_SIGNATURE EXACT_MATCHING_SIGNATURE
---------------------------------------------------------------------- ------------------------ ------------------------
select  /*test_matching_b*/ * from YOUYUS where t1='1'                      1.43666633406896E19      1.83327833675856E19
select  /*test_matching_b*/ * from YOUYUS where t1=1                       1.43666633406896E19      8.05526057286178E18

/*多余的引号也会导致游标无法共享,此时的FORCE_MATCHING_SIGNATURE 也会是一致的*/

select FORCE_MATCHING_SIGNATURE, count(1)
  from v$sql
 where FORCE_MATCHING_SIGNATURE > 0
   and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
 group by FORCE_MATCHING_SIGNATURE
having count(1) > &a
 order by 2;

Enter value for a: 10
old   6: having count(1) > &a
new   6: having count(1) > 10

FORCE_MATCHING_SIGNATURE   COUNT(1)
------------------------ ----------
     8.81463386552502E18         12

So We find it!

在这里再推荐一种来自MOS,find Literal SQL的方法:

How to Find Literal SQL in Shared Pool


Applies to:

PL/SQL – Version: 8.1.7 to 10.2
Information in this document applies to any platform.

Goal

There is no direct way to query the dictionary for literal SQL only.

However the following example will try to exclude all SQL statements in the
shared pool that do use bind variables.

There still might be situations, with statements using subqueries, where the
example still will show SQL statements using bind variables.

Solution

Create the following PL/SQL block:

[maclean@rh2 bin]$ cat  find_literal.sql
set serveroutput on
set linesize 120
--
-- This anonymous PL/SQL block must be executed as INTERNAL or SYS
-- Execute from : SQL*PLUS
-- CAUTION:
-- This sample program has been tested on Oracle Server - Enterprise Edition
-- However, there is no guarantee of effectiveness because of the possibility
-- of error in transmitting or implementing it. It is meant to be used as a
-- template, and it may require modification.
--
declare
b_myadr VARCHAR2(20);
b_myadr1 VARCHAR2(20);
qstring VARCHAR2(100);
b_anybind NUMBER;
cursor my_statement is
select address from v$sql
group by address;
cursor getsqlcode is
select substr(sql_text,1,60)
from v$sql
where address = b_myadr;
cursor kglcur is
select kglhdadr from x$kglcursor
where kglhdpar = b_myadr
and kglhdpar != kglhdadr
and kglobt09 = 0;
cursor isthisliteral is
select kkscbndt
from x$kksbv
where kglhdadr = b_myadr1;
begin
dbms_output.enable(10000000);
open my_statement;
loop
Fetch my_statement into b_myadr;
open kglcur;
fetch kglcur into b_myadr1;
if kglcur%FOUND Then
open isthisliteral;
fetch isthisliteral into b_anybind;
if isthisliteral%NOTFOUND Then
open getsqlcode;
fetch getsqlcode into qstring;
dbms_output.put_line('Literal:'||qstring||' address: '||b_myadr);
close getsqlcode;
end if;
close isthisliteral;
end if;
close kglcur;
Exit When my_statement%NOTFOUND;
End loop;
close my_statement;
end;
/
/*尝试执行*/
SQL> @find_literal
Literal:select inst_id, java_size, round(java_size / basejava_size,  address: 00000000BC6E94E8
Literal:select reason_id, object_id, subobject_id, internal_instance address: 00000000BC5F1D60
Literal:select  DBID, NAME, CREATED, RESETLOGS_CHANGE#, RESETLOGS_TI address: 00000000BC6000B0
Literal:select di.inst_id,di.didbi,di.didbn,to_date(di.dicts,'MM/DD/ address: 00000000BC530DA8
Literal:      declare          vsn  varchar2(20);             begin  address: 00000000BC85A9F8
Literal:SELECT INCARNATION#, RESETLOGS_CHANGE#, RESETLOGS_TIME, PRIO address: 00000000BC829978
Literal:select pos#,intcol#,col#,spare1,bo#,spare2 from icol$ where  address: 00000000BCA84D00
Literal:select SYS_CONTEXT('USERENV', 'SERVER_HOST'), SYS_CONTEXT('U address: 00000000BC771BF0
Literal: select sql_text, FORCE_MATCHING_SIGNATURE, EXACT_MATCHING_S address: 00000000BC4673A8
Literal:select streams_pool_size_for_estimate s,           streams_p address: 00000000BCA58848
Literal:         select open_mode from v$database address: 00000000BC5DF2D0
Literal:select FORCE_MATCHING_SIGNATURE, count(1)   from v$sql  wher address: 00000000BCA91628
Literal:select inst_id, tablespace_name, segment_file, segment_block address: 00000000BC66EF38
Literal:select sum(used_blocks), ts.ts#   from GV$SORT_SEGMENT gv, t address: 00000000BCAA01B0
Literal:BEGIN DBMS_OUTPUT.ENABLE(NULL); END; address: 00000000BC61D2D8
Literal:select value$ from props$ where name = 'GLOBAL_DB_NAME' address: 00000000BC570500
Literal:select count(*) from sys.job$ where (next_date > sysdate) an address: 00000000BC6C53F8
Literal:select java_pool_size_for_estimate s,           java_pool_si address: 00000000BCA65070
Literal:select local_tran_id, global_tran_fmt, global_oracle_id, glo address: 00000000BC5900B8
Literal:select inst_id,kglnaobj,kglfnobj,kglobt03, kglobhs0+kglobhs1 address: 00000000BC921538
Literal:select o.owner#,o.name,o.namespace,o.remoteowner,o.linkname, address: 00000000BCA83E90
Literal:SELECT * FROM V$SQL address: 00000000BCA58BC0
Literal:SELECT ADDRESS FROM V$SQL GROUP BY ADDRESS address: 00000000BC565BE8
Literal:      begin          dbms_rcvman.resetAll;       end; address: 00000000BC759858
Literal:declare b_myadr VARCHAR2(20); b_myadr1 VARCHAR2(20); qstring address: 00000000BC928FF8
Literal:select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, address: 00000000BC898BF8
Literal:select CONF#, NAME, VALUE from GV$RMAN_CONFIGURATION where i address: 00000000BC8CB7F8
Literal:select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t address: 00000000BC8CDFE8
Literal:select u.name, o.name, trigger$.sys_evts, trigger$.type#  fr address: 00000000BCA877B8
Literal:select id, name, block_size, advice_status,                  address: 00000000BC636B38
Literal:select incarnation#, resetlogs_change#, resetlogs_time,      address: 00000000BCA94250
Literal:select  INSTANCE_NUMBER , INSTANCE_NAME , HOST_NAME , VERSIO address: 00000000BC62A678
Literal:select ks.inst_id,ksuxsins,ksuxssid,ksuxshst,ksuxsver,ksuxst address: 00000000BC8E5440
Literal:select timestamp, flags from fixed_obj$ where obj#=:1 address: 00000000BC916C78
Literal:select size_for_estimate,                      size_factor * address: 00000000BCA5F830
Literal:select shared_pool_size_for_estimate s,          shared_pool address: 00000000BCA5A350
Literal:select  SQL_TEXT , SQL_FULLTEXT , SQL_ID,  SHARABLE_MEM , PE address: 00000000BC76B3A0
Literal:lock table sys.col_usage$ in exclusive mode nowait address: 00000000BCA05978
Literal:select 'x' from dual  address: 00000000BC583818
Literal:      select name, resetlogs_time,              resetlogs_ch address: 00000000BCA9D430
Literal:select inst_id, sp_size, round(sp_size / basesp_size, 4),  k address: 00000000BC65A9F0
Literal:select userenv('Instance'),  icrid, to_number(icrls),        address: 00000000BC692260
Literal:select shared_pool_size_for_estimate, shared_pool_size_facto address: 00000000BCAE0750
Literal:select INST_ID, RMRNO, RMNAM, RMVAL from X$KCCRM where RMNAM address: 00000000BC8CD778
Literal:select metadata from kopm$  where name='DB_FDO' address: 00000000BC9EBB98
Literal:select java_pool_size_for_estimate, java_pool_size_factor,   address: 00000000BC5B27D0
Literal:SELECT INCARNATION#, INCARNATION#, RESETLOGS_CHANGE#, RESETL address: 00000000BC829C48
Literal:select file# from file$ where ts#=:1 address: 00000000BC87CF18
Literal:select A.inst_id, A.bpid, B.bp_name, A.blksz,                address: 00000000BC802248
Literal:lock table sys.mon_mods$ in exclusive mode nowait address: 00000000BC5CBE68
Literal:lock table sys.mon_mods$ in exclusive mode nowait address: 00000000BC5CBE68

7月最新发布11.2.0.1.2 Patch set update

7月13日,11g release 2 的第二个补丁集更新发布了;9i的最终版本为9.2.0.8,10g上10.2.0.5很有可能成为最终版本,我们预期今后(11g,12g)中Patch set数量会有效减少,而patch set update数量可能大幅增加;这样的更新形式可以为Oracle Database提升一定的软件形象。可以猜想11gr2的最终版本号可能是11.2.0.2/3.x。

附该psu的readme note:

Released: July 13, 2010

This document is accurate at the time of release. For any changes and additional information regarding PSU 11.2.0.1.2, see these related documents that are available at My Oracle Support (http://support.oracle.com/):

  • Note 854428.1 Patch Set Updates for Oracle Products
  • Note 1089071.1 Oracle Database Patch Set Update 11.2.0.1.2 Known Issues

This document includes the following sections:

1 Patch Information

Patch Set Update (PSU) patches are cumulative. That is, the content of all previous PSUs is included in the latest PSU patch.

PSU 11.2.0.1.2 includes the fixes listed in Section 5, “Bugs Fixed by This Patch”.

Table 1 describes installation types and security content. For each installation type, it indicates the most recent PSU patch to include new security fixes that are pertinent to that installation type. If there are no security fixes to be applied to an installation type, then “None” is indicated. If a specific PSU is listed, then apply that or any later PSU patch to be current with security fixes.

Table 1 Installation Types and Security Content

Installation Type Latest PSU with Security Fixes
Server homes PSU 11.2.0.1.2


Client-Only Installations None
Instant Client Installations None

(The Instant Client installation is not the same as the client-only Installation. For additional information about Instant Client installations, see Oracle Database Concepts.)

2 Patch Installation and Deinstallation

This section includes the following sections:

2.1 Platforms for PSU 11.2.0.1.2

For a list of platforms that are supported in this Patch Set Update, see My Oracle Support Note 1060989.1 Critical Patch Update July 2010 Patch Availability Document for Oracle Products.

2.2 OPatch Utility Information

You must use the OPatch utility version 11.2.0.1.0 or later to apply this patch. Oracle recommends that you use the latest released OPatch 11.2, which is available for download from My Oracle Support patch 6880880 by selecting the 11.2.0.0.0 release.

For information about OPatch documentation, including any known issues, see My Oracle Support Note 293369.1 OPatch documentation list.

2.3 Patch Installation

These instructions are for all Oracle Database installations.

2.3.1 Patch Pre-Installation Instructions

Before you install PSU 11.2.0.1.2, perform the following actions to check the environment and to detect and resolve any one-off patch conflicts.

2.3.1.1 Environments with ASM

If you are installing the PSU to an environment that has Automatic Storage Management (ASM), note the following:

  • For Linux x86 and Linux x86-64 platforms, install either (A) the bug fix for 8898852 and the Database PSU patch 9654983, or (B) the Grid Infrastructure PSU patch 9343627.
  • For all other platforms, no action is required. The fix for 8898852 was included in the base 11.2.0.1.0 release.

2.3.1.2 Environment Checks
  1. Ensure that the $PATH definition has the following executables: make, ar, ld, and nm.The location of these executables depends on your operating system. On many operating systems, they are located in /usr/ccs/bin, in which case you can set your PATH definition as follows:
    export PATH=$PATH:/usr/ccs/bin
    

2.3.1.3 One-off Patch Conflict Detection and Resolution

For an introduction to the PSU one-off patch concepts, see “Patch Set Updates Patch Conflict Resolution” in My Oracle Support Note 854428.1 Patch Set Updates for Oracle Products.

The fastest and easiest way to determine whether you have one-off patches in the Oracle home that conflict with the PSU, and to get the necessary conflict resolution patches, is to use the Patch Recommendations and Patch Plans features on the Patches & Updates tab in My Oracle Support. These features work in conjunction with the My Oracle Support Configuration Manager. Recorded training sessions on these features can be found in Note 603505.1.

However, if you are not using My Oracle Support Patch Plans, follow these steps:

  1. Determine whether any currently installed one-off patches conflict with the PSU patch as follows:
    unzip p9654983_11201_<platform>.zip
    opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./9654983
    
  2. The report will indicate the patches that conflict with PSU 9654983 and the patches for which PSU 9654983 is a superset.Note that Oracle proactively provides PSU 11.2.0.1.2 one-off patches for common conflicts.
  3. Use My Oracle Support Note 1061295.1 Patch Set Updates – One-off Patch Conflict Resolution to determine, for each conflicting patch, whether a conflict resolution patch is already available, and if you need to request a new conflict resolution patch or if the conflict may be ignored.
  4. When all the one-off patches that you have requested are available at My Oracle Support, proceed with Section 2.3.2, “Patch Installation Instructions”.

2.3.2 Patch Installation Instructions

Follow these steps:

  1. If you are using a Data Guard Physical Standby database, you must first install this patch on the primary database before installing the patch on the physical standby database. It is not supported to install this patch on the physical standby database before installing the patch on the primary database. For more information, see My Oracle Support Note 278641.1.
  2. Do one of the following, depending on whether this is a RAC environment:
    • If this is a RAC environment, choose one of the patch installation methods provided by OPatch (rolling, all node, or minimum downtime), and shutdown instances and listeners as appropriate for the installation method selected.This PSU patch is rolling RAC installable. Refer to My Oracle Support Note 244241.1 Rolling Patch – OPatch Support for RAC.
    • If this is not a RAC environment, shut down all instances and listeners associated with the Oracle home that you are updating. For more information, see Oracle Database Administrator’s Guide.
  3. Set your current directory to the directory where the patch is located and then run the OPatch utility by entering the following commands:
    unzip p9654983_11201_<platform>.zip
    cd 9654983
    opatch apply
    
  4. If there are errors, refer to Section 3, “Known Issues”.

2.3.3 Patch Post-Installation Instructions

After installing the patch, perform the following actions:

  1. Apply conflict resolution patches as explained in Section 2.3.3.1.
  2. Load modified SQL files into the database, as explained in Section 2.3.3.2.

2.3.3.1 Applying Conflict Resolution Patches

Apply the patch conflict resolution one-off patches that were determined to be needed when you performed the steps in Section 2.3.1.3, “One-off Patch Conflict Detection and Resolution”.

2.3.3.2 Loading Modified SQL Files into the Database

The following steps load modified SQL files into the database. For a RAC environment, perform these steps on only one node.

  1. For each database instance running on the Oracle home being patched, connect to the database using SQL*Plus. Connect as SYSDBA and run the catbundle.sql script as follows:
    cd $ORACLE_HOME/rdbms/admin
    sqlplus /nolog
    SQL> CONNECT / AS SYSDBA
    SQL> STARTUP
    SQL> @catbundle.sql psu apply
    SQL> QUIT
    

    The catbundle.sql execution is reflected in the dba_registry_history view by a row associated with bundle series PSU.

    For information about the catbundle.sql script, see My Oracle Support Note 605795.1 Introduction to Oracle Database catbundle.sql.

  2. Check the following log files in $ORACLE_HOME/cfgtoollogs/catbundle for any errors:
    catbundle_PSU_<database SID>_APPLY_<TIMESTAMP>.log
    catbundle_PSU_<database SID>_GENERATE_<TIMESTAMP>.log
    

    where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS. If there are errors, refer to Section 3, “Known Issues”.

2.3.4 Patch Post-Installation Instructions for Databases Created or Upgraded after Installation of PSU 11.2.0.1.2 in the Oracle Home

These instructions are for a database that is created or upgraded after the installation of PSU 11.2.0.1.2.

You must execute the steps in Section 2.3.3.2, “Loading Modified SQL Files into the Database” for any new database only if it was created by any of the following methods:

  • Using DBCA (Database Configuration Assistant) to select a sample database (General, Data Warehouse, Transaction Processing)
  • Using a script that was created by DBCA that creates a database from a sample database

2.4 Patch Deinstallation

These instructions apply if you need to deinstall the patch.

2.4.1 Patch Deinstallation Instructions for a Non-RAC Environment

Follow these steps:

  1. Verify that an $ORACLE_HOME/rdbms/admin/catbundle_PSU_<database SID>_ROLLBACK.sql file exists for each database associated with this ORACLE_HOME. If this is not the case, you must execute the steps in Section 2.3.3.2, “Loading Modified SQL Files into the Database” against the database before deinstalling the PSU.
  2. Shut down all instances and listeners associated with the Oracle home that you are updating. For more information, see Oracle Database Administrator’s Guide.
  3. Run the OPatch utility specifying the rollback argument as follows.
    opatch rollback -id 9654983
    
  4. If there are errors, refer to Section 3, “Known Issues”.

2.4.2 Patch Post-Deinstallation Instructions for a Non-RAC Environment

Follow these steps:

  1. Start all database instances running from the Oracle home. (For more information, see Oracle Database Administrator’s Guide.)
  2. For each database instance running out of the ORACLE_HOME, connect to the database using SQL*Plus as SYSDBA and run the rollback script as follows:
    cd $ORACLE_HOME/rdbms/admin
    sqlplus /nolog
    SQL> CONNECT / AS SYSDBA
    SQL> STARTUP
    SQL> @catbundle_PSU_<database SID>_ROLLBACK.sql
    SQL> QUIT
    

    In a RAC environment, the name of the rollback script will have the format catbundle_PSU_<database SID PREFIX>_ROLLBACK.sql.

  3. Check the log file for any errors. The log file is found in $ORACLE_HOME/cfgtoollogs/catbundle and is named catbundle_PSU_<database SID>_ROLLBACK_<TIMESTAMP>.log where TIMESTAMP is of the form YYYYMMMDD_HH_MM_SS. If there are errors, refer to Section 3, “Known Issues”.

2.4.3 Patch Deinstallation Instructions for a RAC Environment

Follow these steps for each node in the cluster, one node at a time:

  1. Shut down the instance on the node.
  2. Run the OPatch utility specifying the rollback argument as follows.
    opatch rollback -id 9654983
    

    If there are errors, refer to Section 3, “Known Issues”.

  3. Start the instance on the node as follows:
    srvctl start instance
    

2.4.4 Patch Post-Deinstallation Instructions for a RAC Environment

Follow the instructions listed in Section Section 2.4.2, “Patch Post-Deinstallation Instructions for a Non-RAC Environment” only on the node for which the steps in Section 2.3.3.2, “Loading Modified SQL Files into the Database” were executed during the patch application.

All other instances can be started and accessed as usual while you are executing the deinstallation steps.

3 Known Issues

For information about OPatch issues, see My Oracle Support Note 293369.1 OPatch documentation list.

For issues documented after the release of this PSU, see My Oracle Support Note 1089071.1 Oracle Database Patch Set Update 11.2.0.1.2 Known Issues.

Other known issues are as follows.

Issue 1
The following ignorable errors may be encountered while running the catbundle.sql script or its rollback script:

ORA-29809: cannot drop an operator with dependent objects
ORA-29931: specified association does not exist
ORA-29830: operator does not exist
ORA-00942: table or view does not exist
ORA-00955: name is already used by an existing object
ORA-01430: column being added already exists in table
ORA-01432: public synonym to be dropped does not exist
ORA-01434: private synonym to be dropped does not exist
ORA-01435: user does not exist
ORA-01917: user or role 'XDB' does not exist
ORA-01920: user name '<user-name>' conflicts with another user or role name
ORA-01921: role name '<role name>' conflicts with another user or role name
ORA-01952: system privileges not granted to 'WKSYS'
ORA-02303: cannot drop or replace a type with type or table dependents
ORA-02443: Cannot drop constraint - nonexistent constraint
ORA-04043: object <object-name> does not exist
ORA-29832: cannot drop or replace an indextype with dependent indexes
ORA-29844: duplicate operator name specified
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
ORA-06512: at line <line number>. If this error follow any of above errors, then can be safely ignored.
ORA-01927: cannot REVOKE privileges you did not grant

4 References

The following documents are references for this patch.

Note 293369.1 OPatch documentation list

Note 360870.1 Impact of Java Security Vulnerabilities on Oracle Products

Note 468959.1 Enterprise Manager Grid Control Known Issues

Note 9352237.8 Bug 9352237 – 11.2.0.1.1 Patch Set Update (PSU)

5 Bugs Fixed by This Patch

This patch includes the following bug fixes.

5.1 CPU Molecules

CPU molecules in PSU 11.2.0.1.2:

PSU 11.2.0.1.2 contains the following new CPU molecules:

9676419 – DB-11.2.0.1-MOLECULE-004-CPUJUL2010

9676420 – DB-11.2.0.1-MOLECULE-005-CPUJUL2010

5.2 Bug Fixes

PSU 11.2.0.1.2 contains the following new fixes:

Automatic Storage Management

8755082 – ORA-00600: [KCFIS_TRANSLATE4:VOLUME LOOKUP], [2], [WRONG DEVICE NAME], [], [], [

8890026 – ASM PARTNERING CREATES IMBALANCED PARTNERSHIPS

9170608 – STBH:DD BLOCKS PINNED FOR QUERIES THAT DO NOT REQUEST USED SPACE

9363145 – STBH:DB INSTANCES TERMINATED BY ASMB DUE TO ORA-00600 [KFDSKALLOC0]

Buffer Cache

8330783 – HANGING DB WITH “CACHE BUFFER CHAINS” AND “BUFFER DEADLOCK” WAITS DURING INSERT

8822531 – TAKING AWR SNAP HANGS

Data Guard Broker

8918433 – UNPERSISTED FSFO STATE BITS CAN GET PERSISTED

9363384 – PHYSICAL STANDBY SERVICES NOT STARTED AFTER CONVERT FROM SNAPSHOT

9467635 – BROKER’S METADATA FILE UPGRADE TO 11.2 IS BROKEN

9467727 – GETSTATUS DOC YIELDS INCORRECT RESULT IF DBRESOURCE_ID PROP VALUE IS USED

Data Guard Logical

8774868 – LGSBFSFO: ORA-600 [3020], [3], [138] RAISED IN RECOVERY SLAVE

8822832 – V$ARCHIVE_DEST_STATUS HAS INCORRECT VALUE FOR APPLIED_SEQ#

DataGuard Redo Transport

8872096 – ARCHIVING FORCED DURING CLOSE WHEN NO STANDBY IS PRESENT

9399090 – STBH: CONSTANT/HIGH FREQUENT LOG SWITCHES ON BEEHIVE DATABASE IN THE LAST 3 DAYS

Shared Cursors

8865718 – RECURSIVE CURSORS CONTAINING “AS OF SNAPSHOT” CLAUSE ARE NOT SHARED

8981059 – HIGH VERSION COUNT:BIND_MISMATCH,USER_BIND_PEEK_MISMATCH,OPTIMIZER_MODE_MISMATCH

9010222 – APPS ST 11G ORA-00600 [KKSFBC-REPARSE-INFINITE-LOOP]

9067282 – TB:SH:ORA-00600:[KKSFBC-WRONG-KKSCSFLGS] WHILE RUNNING TPC-H

DML Drivers

9255542 – ARRAY INSERT TO PARTITIONED TABLE LOOSES ROWS DUE TO CONCURRENT DDL (ORA-14403)

9488887 – FORIEGN KEY VIOLATION WITH ARRAY-INSERT AND ONLINE IDX REBUILD AFTER BUG-9255542

Flashback Database

8834425 – ORA-240 IN RVWR PROCESS CAUSING 5MIN TRANSACTIONAL HANG

PLSQL

9210925 – AFTER MANUAL UPGRADE TO 11.1.0.7 PL/SQL CALLS INCORRECT FUNCTION

Automatic Memory Management

8505803 – PRE_PAGE_SGA RESULTS IN EXCESSIVE PAGE TABLE SIZE WHEN USING MEMORY_TARGET [AMM]

Partitioning

9165206 – PARTITIONING ORA-600 [KKPOLLS1] / [KKDOILSF1] – DURING PARTITION MAINTANANCE

Real Application Cluster

8875671 – LX64: ORA-600 ARGS [KJPNP_CHK:!MASTER_READY],

9093300 – LOTS OF REPEATED KJXOCDR: DROP DUPLICATE OPEN MESSAGE IN LMD TRACE

Row Access Method

8544696 – TABLE GROWTH – BLOCKS ARE NOT REUSED

Streams

8650719 – DOWNSTREAM CAPTURE ABORTS WITH ORA-26766

Secure Files

8856478 – RAM SECUREFILE PERF DEGRADATION WITH SF COMPRESSION ON SMALL LOBS DURING ATB MOVE

9272086 – STBH: DATA PUMP WRITER SEEMS TO BE WAITING ON WAIT FOR UNREAD MESSAGE ON BROADCA

DB Recovery

8909984 – APPSST GSI 11G: GAPS IN AWR SNAPSHOTS

9068088 – MEDIA RECOVERY WAS HUNG ON STANDBY

9145541 – ORA-600 [25027] / ORA-600 [4097] FOR ACTIVE TX IN A PLUGGED TABLESPACE

9167285 – PKT-BUGOLTP: ORA-07445: [KCRALC()+87]

Space Management

7519406 – ‘J000’ TRACE FILE REGARDING GATHER_STATS_JOB INTERMITTENTLY SINCE 10.2.0.4

8815639 – [11GR2-LNX-090813] MULTIPLE INSERT CAUSE DATA ALLOCATION ABOVE HHWM

9216806 – HIGH “ENQ: TS – CONTENTION” FOR TEMPORARY SEGMENT WHILE SQLLDR DIRECT PATH LOAD

9242411 – STRESS-BIGBH: LOTS OF OR-3113S IN BIGBH STRESS TEST

9461782 – ORA-7445 [KTSLF_SUMFSG()+54] [SIGSEGV] AND KTSLFSUM_CFS ON CALL STACK

Compression

9011088 – [11GR2]ADDING COLUMN TO COMPRESSED TABLE, DATA LOSS OCCURED.

9275072 – APPSST GSI 11G : BUFFER BUSY WAITS INSERTING INTO TABLES

9341448 – APPSST GSI 11G : BUFFER BUSY WAITS AND LATCH: CACHE BUFFERS WAITS WHEN INSERTING

9637033 – ORA-07445[KDR9IR2RST0] INSERT AS SELECT IN A COMPRESSED TABLE WITH > 255 COLUMNS

SQL Execution

8664189 – ORA-00600 [KDISS_UNCOMPRESS: BUFFER LENGTH]

9119194 – PSRC: DISTRIBUTED QUERY SLOWER IN 10.2.0.4 COMPARED TO 10.2.0.3

Transaction Management

8268775 – PERF: HIGH US ENQUEUE CONTENTION DURING A LOGIN STORM OR SESSION FAILOVER

8803762 – ORA-00600 [KDSGRP1] BLOCK CORRUPTION ON 11G DATABASE UPGRADE

Memory Management

8431487 – INSTANCE CRASH ORA-07445 [KGGHSTFEL()+192] ORA-07445[KGGHSTMAP()+241]

Message

9713537 – ENHANCE CAUSE/ACTION FIELDS OF THE INTERNAL ERROR ORA-00600

9714832 – ENHANCE CAUSE/ACTION FIELDS OF THE INTERNAL ERROR ORA-07445

Script To Monitor RDBMS Session UGA and PGA Current And Maximum Usage Over Time

Script

set echo off;
set feedback off;
set heading off;
set linesize 128;
set show off;
set pagesize 55;
set trimspool on;
set verify off;
column "SID AND SERIAL#" FORMAT A19
col SNAP_COLUMN new_value SNAP_TIME
col SNAP_EOF_NAME new_value EOF_NAME
col SNAP_HOST_NAME new_value THE_HOST_NAME
col SNAP_INSTANCE_NAME new_value THE_NAME_OF_THE_INSTANCE
col SNAP_RDBMS_VERSION new_value THE_RDBMS_VERSION
set term off;
select to_char(sysdate,'YYYYMMDD_HH24MISS') "SNAP_COLUMN" from dual;
select trim(host_name) "SNAP_HOST_NAME" from v$instance;
select trim(instance_name) "SNAP_INSTANCE_NAME" from v$instance;
select trim(version) "SNAP_RDBMS_VERSION" from v$instance;
select '&THE_NAME_OF_THE_INSTANCE'||'_'||'&SNAP_TIME'||'.LST' "SNAP_EOF_NAME" from dual;
drop table maxpgauga;
create table maxpgauga as select s.sid,
s2.serial#,
n.name,
s.value,
decode(s2.username,null,s2.program,s2.username) "USERNAME",
s2.logon_time
from   v$statname n,
v$sesstat s,
v$session s2
where  n.statistic# = s.statistic# and
(s.sid = s2.sid) and
name like 'session%memory max%';
drop table curpgauga;
create table curpgauga as select s.sid,
s2.serial#,
n.name,
s.value,
decode(s2.username,null,s2.program,s2.username) "USERNAME",
s2.logon_time
from   v$statname n,
v$sesstat s,
v$session s2
where  n.statistic# = s.statistic# and
(s.sid = s2.sid) and
name like 'session%memory' and
name not like 'session%memory max%';
set term on;

spool ORACLE_MEMORY_USAGE_SNAPSHOT_&EOF_NAME

select ‘Oracle Memory Usage Report: PGA And UGA Memory Usage Per Session’ from dual;
select ‘Host……..: ‘||’&THE_HOST_NAME’ from dual;
select ‘Name……..: ‘||’&THE_NAME_OF_THE_INSTANCE’ from dual;
select ‘Version…..: ‘||’&THE_RDBMS_VERSION’ from dual;
select ‘Startup Time: ‘||to_char(min(logon_time),’YYYY-MM-DD HH24:MI:SS’) from curpgauga;
select ‘Current Time: ‘||to_char(sysdate,’YYYY.MM.DD-HH24:MI:SS’) from dual;
select ‘Worst possible value of concurrent PGA + UGA memory usage per session:’ from dual;
set heading on
select   trim(to_char(sid))||’,’||trim(to_char(serial#)) “SID AND SERIAL#”,
username “USERNAME OR PROGRAM”,
sum(value),
to_char(logon_time,’YYYY-MM-DD HH24:MI:SS’) “SESSION START TIME”
from     maxpgauga
group by sid,
serial#,
username,
logon_time
order by sum(value) desc;
set heading off
select ‘Worst possible total and average values of concurrent PGA + UGA memory usage:’ from dual;
select sum(value)||’ bytes (total) and ~’||trunc(avg(value))||’ bytes (average), for ~’||trunc(count(*)/2)||’ sessions.’ from maxpgauga;
select ‘Approximate value of current PGA + UGA memory usage per session:’ from dual;
set heading on
select   trim(to_char(sid))||’,’||trim(to_char(serial#)) “SID AND SERIAL#”,
username “USERNAME OR PROGRAM”,
sum(value),
to_char(logon_time,’YYYY-MM-DD HH24:MI:SS’) “SESSION START TIME”
from     curpgauga
group by sid,
serial#,
username,
logon_time
order by sum(value) desc;
set heading off
select ‘Current total and average values of concurrent PGA + UGA memory usage:’ from dual;
select sum(value)||’ bytes (total) and ~’||trunc(avg(value))||’ bytes (average), for ~’||trunc(count(*)/2)||’ sessions.’ from curpgauga;
select ‘Maximum value of PGA memory usage per session:’ from dual;
set heading on
select   trim(to_char(sid))||’,’||trim(to_char(serial#)) “SID AND SERIAL#”,
username “USERNAME OR PROGRAM”,
value,
to_char(logon_time,’YYYY-MM-DD HH24:MI:SS’) “SESSION START TIME”
from     maxpgauga
where    name like ‘session pga memory max%’
order by value desc, sid desc;
set heading off
select ‘Worst possible total and average values of concurrent PGA memory usage:’ from dual;
select sum(value)||’ bytes (total) and ~’||trunc(avg(value))||’ bytes (average), for ~’||count(*)||’ sessions.’ from maxpgauga where name like ‘session pga memory max%’;
select ‘Maximum value of UGA memory usage per session:’ from dual;
set heading on
select   trim(to_char(sid))||’,’||trim(to_char(serial#)) “SID AND SERIAL#”,
username “USERNAME OR PROGRAM”,
value,
to_char(logon_time,’YYYY-MM-DD HH24:MI:SS’) “SESSION START TIME”
from     maxpgauga
where    name like ‘session uga memory max%’
order by value desc, sid desc;
set heading off
select ‘Worst possible total and average values of concurrent UGA memory usage:’  from dual;
select sum(value)||’ bytes (total) and ~’||trunc(avg(value))||’ bytes (average), for ~’||count(*)||’ sessions.’ from maxpgauga where name like ‘session uga memory max%’;
select ‘Current value of PGA memory usage per session:’ from dual;
set heading on
select   trim(to_char(sid))||’,’||trim(to_char(serial#)) “SID AND SERIAL#”,
username “USERNAME OR PROGRAM”,
value,
to_char(logon_time,’YYYY-MM-DD HH24:MI:SS’) “SESSION START TIME”
from     curpgauga
where    name like ‘session pga memory%’
order by value desc, sid desc;
set heading off
select ‘Current total and average values of concurrent PGA memory usage:’ from dual;
select sum(value)||’ bytes (total) and ~’||trunc(avg(value))||’ bytes (average), for ~’||count(*)||’ sessions.’ from curpgauga where name like ‘session pga memory%’;
select ‘Current value of UGA memory usage per session:’ from dual;
set heading on
select   trim(to_char(sid))||’,’||trim(to_char(serial#)) “SID AND SERIAL#”,
username “USERNAME OR PROGRAM”,
value,
to_char(logon_time,’YYYY-MM-DD HH24:MI:SS’) “SESSION START TIME”
from     curpgauga
where    name like ‘session uga memory%’
order by value desc, sid desc;
set heading off
select ‘Current total and average values of concurrent UGA memory usage:’ from dual;
select sum(value)||’ bytes (total) and ~’||trunc(avg(value))||’ bytes (average), for ~’||count(*)||’ sessions.’ from curpgauga where name like ‘session uga memory%’;
select ‘Current SGA structure sizings:’ from dual;
show sga
select ‘Some initialization parameter values at instance startup:’ from dual;
select trim(name)||’=’||value
from v$parameter
where name in (‘__shared_pool_size’,
‘large_pool_size’,
‘pga_aggregate_target’,
‘sga_target’,
‘shared_pool_size’,
‘sort_area_size’,
‘streams_pool_size’) order by name;
select ‘Current Time: ‘||TO_CHAR(sysdate,’YYYY.MM.DD-HH24:MI:SS’) from dual;
spool off
set feedback on;
set heading on;
set linesize 80;
set pagesize 14;
set verify on;
set echo on;

Oracle Memory Usage Report: PGA And UGA Memory Usage Per Session

 

ora-600[qesmmCValStat4]一例

在之前的文章中提到过一个有趣的绘制五角星的SQL,具体SQL语句如下:
with a as
(select distinct round(sum(x) over(order by n)) x,
round(sum(y) over(order by n)) y
from (select n,
cos(trunc(n / 20) * (1 – 1 / 5) * 3.1415926) * 2 x,
sin(trunc(n / 20) * (1 – 1 / 5) * 3.1415926) y
from (select rownum – 1 n from all_objects where rownum <= 20 * 5)))
select replace (sys_connect_by_path(point, ‘/’), ‘/’, null) star
from (select b.y, b.x, decode(a.x, null, ‘ ‘, ‘*’) point
from a,
(select *
from (select rownum – 1 + (select min(x) from a) x
from all_objects
where rownum <= (select max(x) – min(x) + 1 from a)),
(select rownum – 1 + (select min(y) from a) y
from all_objects
where rownum <= (select max(y) – min(y) + 1 from a))) b
where a.x(+) = b.x
and a.y(+) = b.y)
where x = (select max(x) from a)
start with x = (select min(x) from a)
connect by y = prior y and x = prior x + 1;

在10.2.0.1版本输入以上SQL可能会出现ORA-00600: internal error code, arguments: [qesmmCValStat4], [3], [1], [], [],[], [], []错误,g10r21_ora_15473.trc

其调用栈为:qercoFetch->qerhjFetch->qerhjInitializeManagementComponents->

qesmmCStartWorkArea->qesmmCValidateStatus->kgeasnmierr(报错)

metalink文档ID 360811.1对该 Bug 4926357进行了描述,该bug可能在9.2.07~10.2.0.1版本中出现,一般由带start with … … connect by prior ..子句的查询语句引起;典型的调用栈为:qesmmCValidateStatus<- qesmmCStartWorkArea <-qerhjInitializeManagementComponents <-qerhjFetch …

未公布的Bug 4401437是Bug 4926357的一个复制品avatar,该Bug已在10.1.0.5, 10.2.0.2, 11.1等版本中修复了。

Oracle support建议的四种解决方式:

1. 打上该Bug的one-off补丁;

2.升级到该Bug已修复的大版本中,例如从10.2.0.1升级到10.2.0.2;

3.设置参数hash_join_enabled(9i中),_hash_join_enabled(10g中),实例级别的或者会话级别的均可,如:

SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
SQL> alter session set "_hash_join_enabled"=false;
Session altered.
SQL> set pagesize 1400;
SQL> with a as
2   (select distinct round(sum(x) over(order by n)) x,
3                    round(sum(y) over(order by n)) y
4      from (select n,
5                   cos(trunc(n / 20) * (1 - 1 / 5) * 3.1415926) * 2 x,
6                   sin(trunc(n / 20) * (1 - 1 / 5) * 3.1415926) y
7              from (select rownum - 1 n from all_objects where rownum <= 20 * 5)))
8  select replace (sys_connect_by_path(point, '/'), '/', null) star
9    from (select b.y, b.x, decode(a.x, null, ' ', '*') point
10            from a,
11                 (select *
12                    from (select rownum - 1 + (select min(x) from a) x
13                            from all_objects
14                           where rownum <= (select max(x) - min(x) + 1 from a)),
15                         (select rownum - 1 + (select min(y) from a) y
16                            from all_objects
17                           where rownum <= (select max(y) - min(y) + 1 from a))) b
18           where a.x(+) = b.x
19             and a.y(+) = b.y)
20   where x = (select max(x) from a)
21   start with x = (select min(x) from a)
22  connect by y = prior y and x = prior x + 1;
STAR
--------------------------------------------------------------------------------
.................
20 rows selected.

4.设置’no_filtering’ 提示,如:
with a as
(select distinct round(sum(x) over(order by n)) x,
round(sum(y) over(order by n)) y
from (select n,
cos(trunc(n / 20) * (1 – 1 / 5) * 3.1415926) * 2 x,
sin(trunc(n / 20) * (1 – 1 / 5) * 3.1415926) y
from (select rownum – 1 n from all_objects where rownum <= 20 * 5)))
select  /*+ no_filtering */ replace (sys_connect_by_path(point, ‘/’), ‘/’, null) star
from (select b.y, b.x, decode(a.x, null, ‘ ‘, ‘*’) point
from a,
(select *
from (select rownum – 1 + (select min(x) from a) x
from all_objects
where rownum <= (select max(x) – min(x) + 1 from a)),
(select rownum – 1 + (select min(y) from a) y
from all_objects
where rownum <= (select max(y) – min(y) + 1 from a))) b
where a.x(+) = b.x
and a.y(+) = b.y)
where x = (select max(x) from a)
start with x = (select min(x) from a)
connect by y = prior y and x = prior x + 1;

但是该hint在9.2.0.7版本中可能因为Bug 4752555 ‘Wrong results from CONNECT BY query’而导致返回错误行数;并且这是一个undocumented hint,所以强烈不推荐使用!

famous summary stack trace from Oracle Version 8.1.7.4.0 Bug Note

as this bug note claimed that:

PROBLEM:
——–
Customer frequently receives the following errors while rollback of a
transcation using Portal application:

ORA-603: ORACLE server session terminated by fatal error
ORA-600: internal error code, arguments: [6856], [0], [0], [], [], [], [],
[]

ORA-600: internal error code, arguments: [25012], [3], [15], [], [], [], [],
[]

DIAGNOSTIC ANALYSIS:
——————–
Alert.log:
~~~~~~~~~~
Wed May 19 12:47:28 2004
Errors in file /opt/oracle/admin/ORTPTP/udump/ortptp_ora_6363.trc:
ORA-603: ORACLE server session terminated by fatal error
ORA-600: internal error code, arguments: [6856], [0], [0], [], [], [], [],
[]
Wed May 19 14:38:39 2004
Errors in file /opt/oracle/admin/ORTPTP/udump/ortptp_ora_782.trc:
ORA-600: internal error code, arguments: [25012], [3], [15], [], [], [], [],
[]

Tablespace 3 = TEMP tablespace.

Block dump in tracefile ortptp_ora_21207.trc points to TEMP tablespace and
TEMP segment:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Block header dump:  0x00c0b917
Object id on Block? Y
seg/obj: 0xc0b916  csc: 0x00.18f4bc  itc: 1  flg: O  typ: 1 – DATA
fsl: 0  fnx: 0x0 ver: 0x01
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

WORKAROUND:
———–

RELATED BUGS:
————-
3562030

REPRODUCIBILITY:
—————-
Frequently

TEST CASE:
———-

STACK TRACE:
————
Summary Stack   (to Full stack)   (to Function List)
ksedmp             # KSE: dump the process state
kgeriv             # KGE Record Internal error code (with Va_list) (IGNORE)
kgeasi             # Raise an error on an ASSERTION failure (IGNORE)
kdbmrd             ? Module Notes: kdb.c – Kernel Data Block structure and
internal manipulation
kdoqmd             ? Module Notes: kdo.c – Kernel Data Operations
kcoapl             NAME: kcoapl – Kernel Cache Op APpLy
kcbchg1
kcbchg
ktuapundo          ktuapundo – Kernel Transaction Undo APply UNdo
ktbapundo          ktbapundo – Kernel Transaction Block APply UNdo
kdoiur             declare local objects */
kcoubk             kcoubk – Kernel Cache Op Undo callBacK — invoke undo
callback routine    */
ktundo             ktundo – Kernel Transaction UNDO
ktubko             Get undo record to rollback transaction, non-CR only */
ktuabt             ktuabt – Kernel Transaction Undo ABorT
*/
ktcrab             KTC: Kernel Transaction Control Real ABort – Abort a
transaction.
ktdabt
k2labo             abort session: first abort aborts tx
k2send             TESTING SUPPORT:
xctrol             XaCTion ROLlback: Rollback the current transaction of the
current session.
opiodr             OPIODR: ORACLE code request driver – route the current
request
ttcpip             TTCPIP: Two Task Common PIPe read/write
opitsk             opitsk – Two Task Oracle Side Function Dispatcher
opiino             opiino – ORACLE Program Interface INitialize Opi
opiodr             OPIODR: ORACLE code request driver – route the current
request
opidrv             # opidrv – ORACLE Program Interface DRiVer (IGNORE)
sou2o              # Main Oracle executable entry point
main               # Standard executable entry point
start              # C program entry point (IGNORE)
**********************************************************************************************

another summary:

drepprep     perform the document indexing
evapls    EVAluate any PLSql function
kcmclscn    check Lamport SCN
kcsadj1    adjust SCN
kgesinv    KGE Signal Internal (Named) error (with VA_list)
kghalo    KGH: main allocation entry point
kghalp    KGH: Allocate permanent memory
kghfnd    KGH: Find a chunk of at least the minimum size
kghfrunp    KGH: Ask client to free unpinned space
kghfrx    Free extent. This is called when a heap is unpinned to request that it
kghgex    KGH: Get a new extent
kghnospc    KGH: There is no space available in the heap
kghpmalo    KGH: Find and return a permanent chunk of space
kghxal    Allocate a fixed size piece of shared memory.
kglhpd    KGL HeaP Deallocate
kglobcl    KGL OBject CLear all tables
kglpnal    KGL PiN ALlOcate
kglpnc    KGL: PiN heaps and load data pieces of a Cursor object
kglpndl    KGL PiN DeLete
kglrfcl    KGL ReFerence CLear
kgmexec    KGM EXECute
kkmpost    POST PROCESSING
kksalx    ALlocate ‘size’ bytes from the eXecution-time heap
kkscls    KKS: Close the cursor, user is done with it
kkspfda    Multiple context area management
kkssbt    KKS: set bind types
kksscl    KKS: scan child list?
koklcopy    KOK Lob COPY.
koklcpb2c    KOK Lob CoPy Binary data (BFILE/BLOB) into Clob
kolfgdir    KOL File Get DIRectory object, path and FileNames.
kpuexec    KPU: Execute
kpuexecv8    KPU: Execute V8
kpurcsc    KPU Remote Call with ServiceContext, Callbacks
kqdgtc    return an open and parsed cursor for the given statement
kqldprr    KQLD Parent Referential constraint Read
kqllod    KQL: database object load
kqlsadd    kqlsadd – KQLS ADD a new element to a subordinate set
kqlslod    KQLS: Load all subordinate set elements for a given heap
kslcll    KSL: Clean up after a given latch
kslcllt    Clean up after a given latch
kslilcr    invoke latch cleanup routine:
ksmapg    KSM: Callback function for allocating a PGA extent, calls OSD to alloc
ksmasg    Callback function for allocating an SGA extent.
kssxdl    KSS: delete SO ignoring all except severe errors. cleans latches
ksucln    KSUCLN: Cleanup detached process
ksudlc    delete call
ksudlp    KSU: delete process.called when user detaches or during cleanup by PMON
ksuxda    KSUCLN: Attempt to delete all processes that are marked dead.
ksuxdl    KSUCLN: Delete state object for PMON
ksuxfl    KSU: Find dead processes and cleanup their latches. Called by PMON
kxfpbgpc    Get Permanent Chunks
kxfpbgtc    Buffer Allocation Get Chunk
kxfpnfy    KXFP: NotiFY (component notifier)
kxfxse    KXFX: execute
kxstcls    Trace cursor closing
opicca    ORACLE Program Interface: Clear Context Area
opiclo    ORACLE Program Interface: CLOse cursor
opiprs    ORACLE Program Interface: PaRSe
opitca    OPITCA: sets up the context area
pextproc    Pefm call EXTernal PROCedure
qerocStart    This function creates a collection iterator row-source to iterate
qkadrv    QKADRV: allocate query structures
qkajoi    QKAJOI: Query Kernel Allocation: JOIn processing
qximeop    QXIM Evaluate OPerand
rpicls    RPI: Recursive Program Interface CLoSe
selexe    SELEXE: prepare context area for fetch
xtyinpr    XTY Insert Numeric PRecision operator

 

ORA-600 Lookup Error Categories

Applies to:

Oracle Server – Enterprise Edition – Version:
Oracle Server – Personal Edition – Version:
Oracle Server – Standard Edition – Version:
Information in this document applies to any platform.
Checked for relevance 04-Jun-2009

Purpose

This note aims to provide a high level overview of the internal errors which may be encountered on the Oracle Server (sometimes referred to as the Oracle kernel). It is written to provide a guide to where a particular error may live and give some indication as to what the impact of the problem may be. Where a problem is reproducible and connected with a specific feature, you might obviously try not using the feature. If there is a consistent nature to the problem, it is good practice to ensure that the latest patchsets are in place and that you have taken reasonable measures to avoid known issues.

For repeatable issues which the ora-600 tool has not listed a likely cause , it is worth constructing a test case. Where this is possible, it greatly assists in the resolution time of any issue. It is important to remember that, in a many instances , the Server is very flexible and a workaround can very often be achieved.

Scope and Application

This bulletin provides Oracle DBAs with an overview of internal database errors.

Disclaimer: Every effort has been made to provide a reasonable degree of accuracy in what has been stated. Please consider that the details provided only serve to provide an indication of functionality and, in some cases, may not be wholly correct.

ORA-600 Lookup Error Categories

In the Oracle Server source, there are two types of ora-600 error :

  • the first parameter is a number which reflects the source component or layer the error is connected with; or
  • the first parameter is a mnemonic which indicates the source module where the error originated. This type of internal error is now used in preference to an internal error number.

Both types of error may be possible in the Oracle server.

Internal Errors Categorised by number range

The following table provides an indication of internal error codes used in the Oracle server. Thus, if ora-600[X] is encountered, it is possible to glean some high level background information : the error in generated in the Y layer which indicates that there may be a problem with Z.

Ora-600 Base Functionality Description
1 Service Layer The service layer has within it a variety of service related components which are associated with in memory related activities in the SGA such as, for example : the management of Enqueues, System Parameters, System state objects (these objects track the use of structures in the SGA by Oracle server processes), etc.. In the main, this layer provides support to allow process communication and provides support for locking and the management of structures to support multiple user processes connecting and interacting within the SGA. Note : vos  – Virtual Operating System provides features to support the functionality above.  As the name suggests it provides base functionality in much the same way as is provided by an Operating System.

Ora-600 Base Functionality Description
1 vos Component notifier
100 vos Debug
300 vos Error
500 vos Lock
700 vos Memory
900 vos System Parameters
1100 vos System State object
1110 vos Generic Linked List management
1140 vos Enqueue
1180 vos Instance Locks
1200 vos User State object
1400 vos Async Msgs
1700 vos license Key
1800 vos Instance Registration
1850 vos I/O Services components
2000 Cache Layer Where errors are generated in this area, it is advisable to check whether the error is repeatable and whether the error is perhaps associated with recovery or undo type operations; where this is the case and the error is repeatable, this may suggest some kind of hardware or physical issue with a data file, control file or log file. The Cache layer is responsible for making the changes to the underlying files and well as managing the related memory structures in the SGA. Note : rcv indicates recovery. It is important to remember that the Oracle cache layer is effectively going through the same code paths as used by the recovery mechanism.

Ora-600 Base Functionality Description
2000 server/rcv Cache Op
2100 server/rcv Control File mgmt
2200 server/rcv Misc (SCN etc.)
2400 server/rcv Buffer Instance Hash Table
2600 server/rcv Redo file component
2800 server/rcv Db file
3000 server/rcv Redo Application
3200 server/cache Buffer manager
3400 server/rcv Archival & media recovery component
3600 server/rcv recovery component
3700 server/rcv Thread component
3800 server/rcv Compatibility segment

It is important  to consider when the error occurred and the context in which the error was generated. If the error does not reproduce, it may be an in memory issue.

4000 Transaction Layer Primarily the transaction layer is involved with maintaining structures associated with the management of transactions.  As with the cache layer , problems encountered in this layer may indicate some kind of issue at a physical level. Thus it is important to try and repeat the same steps to see if the problem recurs.

Ora-600 Base Functionality Description
4000 server/txn Transaction Undo
4100 server/txn Transaction Undo
4210 server/txn Transaction Parallel
4250 server/txn Transaction List
4300 space/spcmgmt Transaction Segment
4400 txn/lcltx Transaction Control
4450 txn/lcltx distributed transaction control
4500 txn/lcltx Transaction Block
4600 space/spcmgmt Transaction Table
4800 dict/rowcache Query Row Cache
4900 space/spcmgmt Transaction Monitor
5000 space/spcmgmt Transaction Extent

It is important to try and determine what the object involved in any reproducible problem is. Then use the analyze command. For more information, please refer to the analyze command as detailed in the context of  Note:28814.1; in addition, it may be worth using the dbverify as discussed in Note:35512.1.

6000 Data Layer The data layer is responsible for maintaining and managing the data in the database tables and indexes. Issues in this area may indicate some kind of physical issue at the object level and therefore, it is important to try and isolate the object and then perform an anlayze on the object to validate its structure.

Ora-600 Base Functionality Description
6000 ram/data
ram/analyze
ram/index
data, analyze command and index related activity
7000 ram/object lob related errors
8000 ram/data general data access
8110 ram/index index related
8150 ram/object general data access

Again, it is important to try and determine what the object involved in any reproducible problem is. Then use the analyze command. For more information, please refer to the analyze command as detailed in the context of  Note:28814.1; in addition, it may be worth using the dbverify as discussed in Note:35512.1.

12000 User/Oracle Interface & SQL Layer Components This layer governs the user interface with the Oracle server. Problems generated by this layer usually indicate : some kind of presentation or format error in the data received by the server, i.e. the client may have sent incomplete information; or there is some kind of issue which indicates that the data is received out of sequence

Ora-600 Base Functionality Description
12200 progint/kpo
progint/opi
lob related
errors at interface level on server side, xa , etc.
12300 progint/if OCI interface to coordinating global transactions
12400 sqlexec/rowsrc table row source access
12600 space/spcmgmt operations associated with tablespace : alter / create / drop operations ; operations associated with create table / cluster
12700 sqlexec/rowsrc bad rowid
13000 dict/if dictionary access routines associated with kernel compilation
13080 ram/index kernel Index creation
13080 sqllang/integ constraint mechanism
13100 progint/opi archival and Media Recovery component
13200 dict/sqlddl alter table mechanism
13250 security/audit audit statement processing
13300 objsupp/objdata support for handling of object generation and object access
14000 dict/sqlddl sequence generation
15000 progint/kpo logon to Oracle
16000 tools/sqlldr sql loader related

You should try and repeat the issue and with the use of sql trace , try and isolate where exactly the issue may be occurring within the application.

14000 System Dependent Component internal error values This layer manages interaction with the OS. Effectively it acts as the glue which allows the Oracle server to interact with the OS. The types of operation which this layer manages are indicated as follows.

Ora-600 Base Functionality Description
14000 osds File access
14100 osds Concurrency management;
14200 osds Process management;
14300 osds Exception-handler or signal handler management
14500 osds Memory allocation
15000 security/dac,
security/logon
security/ldap
local user access validation; challenge / response activity for remote access validation; auditing operation; any activities associated with granting and revoking of privileges; validation of password with external password file
15100 dict/sqlddl this component manages operations associated with creating, compiling (altering), renaming, invalidating, and dropping  procedures, functions, and packages.
15160 optim/cbo cost based optimizer layer is used to determine optimal path to the data based on statistical information available on the relevant tables and indexes.
15190 optim/cbo cost based optimizer layer. Used in the generation of a new index to determine how the index should be created. Should it be constructed from the table data or from another index.
15200 dict/shrdcurs used to in creating sharable context area associated with shared cursors
15230 dict/sqlddl manages the compilation of triggers
15260 dict/dictlkup
dict/libcache
dictionary lookup and library cache access
15400 server/drv manages alter system and alter session operations
15410 progint/if manages compilation of pl/sql packages and procedures
15500 dict/dictlkup performs dictionary lookup to ensure semantics are correct
15550 sqlexec/execsvc
sqlexec/rowsrc
hash join execution management;
parallel row source management
15600 sqlexec/pq component provides support for Parallel Query operation
15620 repl/snapshots manages the creation of snapshot or materialized views as well as related snapshot / MV operations
15640 repl/defrdrpc layer containing various functions for examining the deferred transaction queue and retrieving information
15660 jobqs/jobq manages the operation of the Job queue background processes
15670 sqlexec/pq component provides support for Parallel Query operation
15700 sqlexec/pq component provides support for Parallel Query operation; specifically mechanism for starting up and shutting down query slaves
15800 sqlexec/pq component provides support for Parallel Query operation
15810 sqlexec/pq component provides support for Parallel Query operation; specifically functions for creating mechanisms through which Query co-ordinator can communicate with PQ slaves;
15820 sqlexec/pq component provides support for Parallel Query operation
15850 sqlexec/execsvc component provides support for the execution of SQL statements
15860 sqlexec/pq component provides support for Parallel Query operation
16000 loader sql Loader direct load operation;
16150 loader this layer is used for ‘C’ level call outs to direct loader operation;
16200 dict/libcache this is part of library Cache operation. Amongst other things it manages the dependency of SQL objects and tracks who is permitted to access these objects;
16230 dict/libcache this component is responsible for managing access to remote objects as part of library Cache operation;
16300 mts/mts this component relates to MTS (Multi Threaded Server) operation
16400 dict/sqlddl this layer contains functionality which allows tables to be loaded / truncated and their definitions to be modified. This is part of dictionary operation;
16450 dict/libcache this layer layer provides support for multi-instance access to the library cache; this functionality is applicable therefore to OPS environments;
16500 dict/rowcache this layer provides support to load / cache Oracle’s dictionary in memory in the library cache;
16550 sqlexec/fixedtab this component maps data structures maintained in the Oracle code to fixed tables such that they can be queried using the SQL layer;
16600 dict/libcache this layer performs management of data structures within the library cache;
16651 dict/libcache this layer performs management of dictionary related information within library Cache;
16701 dict/libcache this layer provides library Cache support to support database creation and forms part of the bootstrap process;
17000 dict/libcache this is the main library Cache manager. This Layer maintains the in memory representation of cached sql statements together will all the necessary support that this demands;
17090 generic/vos this layer implementations error management operations: signalling errors, catching  errors, recovering from errors, setting error frames, etc.;
17100 generic/vos Heap manager. The Heap manager manages the storage of internal data in an orderly and consistent manner. There can be many heaps serving various purposes; and heaps within heaps. Common examples are the SGA heap, UGA heap and the PGA heap. Within a Heap there are consistency markers which aim to ensure that the Heap is always in a consistent state. Heaps are use extensively and are in memory structures – not on disk.
17200 dict/libcache this component deals with loading remote library objects into the local library cache with information from the remote database.
17250 dict/libcache more library cache errors ; functionality for handling pipe operation associated with dbms_pipe
17270 dict/instmgmt this component manages instantiations of procedures, functions, packages, and cursors in a session. This provides a means to keep track of what has been loaded in the event of process death;
17300 generic/vos manages certain types of memory allocation structure.  This functionality is an extension of the Heap manager.
17500 generic/vos relates to various I/O operations. These relate to async i/o operation,  direct i/o operation and the management of writing buffers from the buffer cache by potentially a number of database writer processes;
17625 dict/libcache additional library Cache supporting functions
17990 plsql plsql ‘standard’ package related issues
18000 txn/lcltx transaction and savepoint management operations
19000 optim/cbo cost based optimizer related operations
20000 ram/index bitmap index and index related errors.
20400 ram/partnmap operations on partition related objects
20500 server/rcv server recovery related operation
21000 repl/defrdrpc,
repl/snapshot,
repl/trigger
replication related features
23000 oltp/qs AQ related errors.
24000 dict/libcache operations associated with managing stored outlines
25000 server/rcv tablespace management operations

Internal Errors Categorised by mnemonic

The following table details mnemonics error stems which are possible. If you have encountered : ora-600[kkjsrj:1] for example, you should look down the Error Mnemonic column (errors in alphabetical order) until you find the matching stem. In this case, kkj indicates that something unexpected has occurred in job queue operation.

Error Mnemonic(s) Functionality Description
ain ainp ram/index ain – alter index; ainp –  alter index partition management operation
apacb optim/rbo used by optimizer in connect by processing
atb atbi atbo ctc ctci cvw dict/sqlddl alter table , create table (IOT) or cluster operations as well as create view related operations (with constraint handling functionality)
dbsdrv sqllang/parse alter / create database operation
ddfnet progint/distrib various distributed operations on remote dictionary
delexe sqlexec/dmldrv manages the delete statement operation
dix ram/index manages drop index or validate index operation
dtb dict/sqlddl manages drop table operation
evaa2g evah2p evaa2g dbproc/sqlfunc various functions involves in evaluating operand outcomes such as : addition , average, OR operator, bites AND , bites OR, concatenation, as well as Oracle related functions : count(), dump() , etc. The list is extensive.
expcmo expgon dbproc/expreval handles expression evaluation with respect to two operands being equivalent
gra security/dac manages the granting and revoking of privilege rights to a user
gslcsq plsldap support for operations with an LDAP server
insexe sqlexec/dmldrv handles the insert statement operation
jox progint/opi functionality associated with the Java compiler and with the Java runtime environment within the Server
k2c k2d progint/distrib support for database to database operation in distributed environements as well as providing, with respect to the 2-phase commit protocol, a globally unique Database id
k2g k2l txn/disttx support for the 2 phase commit protocol protocol and the coordination of the various states in managing the distributed transaction
k2r k2s k2sp progint/distrib k2r – user interface for managing distributed transactions and combining distributed results ; k2s – handles logging on, starting a transaction, ending a transaction and recovering a transaction; k2sp – management of savepoints in a distributed environment.
k2v txn/disttx handles distributed recovery operation
kad cartserv/picklercs handles OCIAnyData implementation
kau ram/data manages the modification of indexes for inserts, updates and delete operations for IOTs as well as modification of indexes for IOTs
kcb kcbb kcbk kcbl kcbs kcbt kcbw kcbz cache manages Oracle’s buffer cache operation as well as operations used by capabilities such as direct load, has clusters , etc.
kcc kcf rcv manages and coordinates operations on the control file(s)
kcit context/trigger internal trigger functionality
kck rcv compatibility related checks associated with the compatible parameter
kcl cache background lck process which manages locking in a RAC or parallel server multiple instance environment
kco kcq kcra kcrf kcrfr kcrfw kcrp kcrr kcs kct kcv rcv various buffer cache operation such as quiesce operation , managing fast start IO target, parallel recovery operation , etc.
kd ram/data support for row level dependency checking and some log miner operations
kda ram/analyze manages the analyze command and collection of statistics
kdbl kdc kdd ram/data support for direct load operation, cluster space management and deleting rows
kdg ram/analyze gathers information about the underlying data and is used by the analyze command
kdi kdibc3 kdibco kdibh kdibl kdibo kdibq kdibr kdic kdici kdii kdil kdir kdis kdiss kdit kdk ram/index support of the creation of indexes on tables an IOTs and index look up
kdl kdlt ram/object lob and temporary lob management
kdo ram/data operations on data such as inserting a row piece or deleting a row piece
kdrp ram/analyze underlying support for operations provided by the dbms_repair package
kds kdt kdu ram/data operations on data such as retrieving a row and updating existing row data
kdv kdx ram/index functionality for dumping index and managing index blocks
kfc kfd kfg asm support for ASM file and disk operations
kfh kfp kft rcv support for writing to file header and transportable tablespace operations
kgaj kgam kgan kgas kgat kgav kgaz argusdbg/argusdbg support for Java Debug Wire Protocol (JDWP) and debugging facilites
kgbt kgg kgh kghs kghx kgkp vos kgbt – support for BTree operations; kgg – generic lists processing; kgh – Heap Manager : managing the internal structures withing the SGA / UGA / PGA and ensures their integrity; kghs – Heap manager with Stream support; kghx – fixed sized shared memory manager; kgkp – generic services scheduling policies
kgl kgl2 kgl3 kgla kglp kglr kgls dict/libcache generic library cache operation
kgm kgmt ilms support for inter language method services – or calling one language from another
kgrq kgsk kgski kgsn kgss vos support for priority queue and scheduling; capabilities for Numa support;  Service State object manager
kgupa kgupb kgupd0 kgupf kgupg kgupi kgupl kgupm kgupp kgupt kgupx kguq2 kguu vos Service related activities activities associated with for Process monitor (PMON); spawning or creating of background processes; debugging; managing process address space;  managing the background processes; etc.
kgxp vos inter process communication related functions
kjak kjat kjb kjbl kjbm kjbr kjcc kjcs kjctc kjcts kjcv kjdd kjdm kjdr kjdx kjfc kjfm kjfs kjfz kjg kji kjl kjm kjp kjr kjs kjt kju kjx ccl/dlm dlm related functionality ; associated with RAC or parallel server operation
kjxgf kjxgg kjxgm kjxgn kjxgna kjxgr ccl/cgs provides communication & synchronisation associated with GMS or OPS related functionality as well as name service and OPS Instance Membership Recovery Facility
kjxt ccl/dlm DLM request message management
kjzc kjzd kjzf kjzg kjzm ccl/diag support for diagnosibility amongst OPS related services
kkb dict/sqlddl support for operatoins which load/change table definitions
kkbl kkbn kkbo objsupp/objddl support for tables with lobs , nested tables and varrays as well as columns with objects
kkdc kkdl kkdo dict/dictlkup support for constraints, dictionary lookup and dictionary support for objects
kke optim/cbo query engine cost engine; provides support functions that provide cost estimates for queries under a number of different circumstances
kkfd sqlexec/pq support for performing parallel query operation
kkfi optim/cbo optimizer support for matching of expressions against functional ndexes
kkfr kkfs sqlexec/pq support for rowid range handling as well as for building parallel query query operations
kkj jobqs/jobq job queue operation
kkkd kkki dict/dbsched resource manager related support. Additionally, provides underlying functions provided by dbms_resource_manager and dbms_resource_manager_privs packages
kklr dict/sqlddl provides functions used to manipulate LOGGING and/or RECOVERABLE attributes of an object (non-partitioned table or index or  partitions of a partitioned table or index)
kkm kkmi dict/dictlkup provides various semantic checking functions
kkn ram/analyze support for the analyze command
kko kkocri optim/cbo Cost based Optimizer operation : generates alternative execution plans in order to find the optimal / quickest access to the data.  Also , support to determine cost and applicability of  scanning a given index in trying to create or rebuild an index or a partition thereof
kkpam kkpap ram/partnmap support for mapping predicate keys expressions to equivalent partitions
kkpo kkpoc kkpod dict/partn support for creation and modification of partitioned objects
kkqg kkqs kkqs1 kkqs2 kkqs3 kkqu kkqv kkqw optim/vwsubq query rewrite operation
kks kksa kksh kksl kksm dict/shrdcurs support for managing shared cursors/ shared sql
kkt dict/sqlddl support for creating, altering and dropping trigger definitions as well as handling the trigger operation
kkxa repl/defrdrpc underlying support for dbms_defer_query package operations
kkxb dict/sqlddl library cache interface for external tables
kkxl dict/plsicds underlying support for the dbms_lob package
kkxm progint/opi support for inter language method services
kkxs dict/plsicds underlying support for the dbms_sys_sql package
kkxt repl/trigger support for replication internal trigger operation
kkxwtp progint/opi entry point into the plsql compiler
kky drv support for alter system/session commands
kkz kkzd kkzf kkzg kkzi kkzj kkzl kkzo kkzp kkzq kkzr kkzu kkzv repl/snapshot support for snapshots or Materialized View validation and operation
kla klc klcli klx tools/sqlldr support for direct path sql loader operation
kmc kmcp kmd kmm kmr mts/mts support for Multi Threaded server operation (MTS) : manange and operate the virtual circuit mechanism, handle the dispatching of massages, administer shared servers and for collecting and maintaining statistics associated with MTS
knac knafh knaha knahc knahf knahs repl/apply replication apply operation associated with Oracle streams
kncc repl/repcache support for replication related information stored and maintained in library cache
kncd knce repl/defrdrpc replication related enqueue and dequeue of transction data as well as other queue related operations
kncog repl/repcache support for loading replicaiton object group information into library cache
kni repl/trigger support for replication internal trigger operation
knip knip2 knipi knipl knipr knipu knipu2 knipx repl/intpkg support for replication internal package operation.
kno repl/repobj support for replication objects
knp knpc knpcb knpcd knpqc knps repl/defrdrpc operations assocaied with propagating transactions to a remote node and coordination of this activity.
knst repl/stats replication statistics collection
knt kntg kntx repl/trigger support for replication internal trigger operation
koc objmgmt/objcache support for managing ADTs objects in the OOCI heap
kod objmgmt/datamgr support for persistent storage for objects : for read/write objects, to manage object IDs, and to manage object concurrency and recovery.
koh objmgmt/objcache object heap manager provides memory allocation services for objects
koi objmgmt/objmgr support for object types
koka objsupp/objdata support for reading images, inserting images, updating images, and deleting images based on object references (REFs).
kokb kokb2 objsupp/objsql support for nested table objects
kokc objmgmt/objcache support for pinning , unpinning and freeing objects
kokd objsupp/datadrv driver on the server side for managing objects
koke koke2 koki objsupp/objsql support for managing objects
kokl objsupp/objdata lob access
kokl2 objsupp/objsql lob DML and programmatic interface support
kokl3 objsupp/objdata object temporary LOB support
kokle kokm objsupp/objsql object SQL evaluation functions
kokn objsupp/objname naming support for objects
koko objsupp/objsup support functions to allow oci/rpi to communicate with Object Management Subsystem (OMS).
kokq koks koks2 koks3 koksr objsupp/objsql query optimisation for objects , semantic checking and semantic rewrite operations
kokt kokt2 kokt3 objsupp/objddl object compilation type manager
koku kokv objsupp/objsql support for unparse object operators and object view support
kol kolb kole kolf kolo objmgmt/objmgr support for object Lob buffering , object lob evaluation and object Language/runtime functions for Opaque types
kope2 kopi2 kopo kopp2 kopu koputil kopz objmgmt/pickler 8.1 engine implementation,  implementation of image ops for 8.1+ image format together with various pickler related support functions
kos objsupp/objsup object Stream interfaces for images/objects
kot kot2 kotg objmgmt/typemgr support for dynamic type operations to create, delete, and  update types.
koxs koxx objmgmt/objmgt object generic image Stream routines and miscellaneous generic object functions
kpcp kpcxlt progint/kpc Kernel programmatic connection pooling and kernel programmatic common type XLT translation routines
kpki progint/kpki kernel programatic interface support
kpls cartserv/corecs support for string formatting operations
kpn progint/kpn support for server to server communication
kpoal8 kpoaq kpob kpodny kpodp kpods kpokgt kpolob kpolon kpon progint/kpo support for programmatic operations
kpor progint/opi support for streaming protocol used by replication
kposc progint/kpo support for scrollable cursors
kpotc progint/opi oracle side support functions for setting up trusted external procedure callbacks
kpotx kpov progint/kpo support for managing local and distributed transaction coordination.
kpp2 kpp3 sqllang/parse kpp2 – parse routines for dimensions;
kpp3 – parse support for create/alter/drop summary  statements
kprb kprc progint/rpi support for executing sql efficiently on the Oracle server side as well as for copying data types during rpi operations
kptsc progint/twotask callback functions provided to all streaming operation as part of replication functionality
kpu kpuc kpucp progint/kpu Oracle kernel side programmatic user interface,  cursor management functions and client side connection pooling support
kqan kqap kqas argusdbg/argusdbg server-side notifiers and callbacks for debug operations.
kql kqld kqlp dict/libcache SQL Library Cache manager – manages the sharing of sql statements in the shared pool
kqr dict/rowcache row cache management. The row cache consists of a set of facilities to provide fast access to table definitions and locking capabilities.
krbi krbx krby krcr krd krpi rcv Backup and recovery related operations :
krbi – dbms_backup_restore package underlying support.; krbx –  proxy copy controller; krby – image copy; krcr – Recovery Controlfile Redo; krd – Recover Datafiles (Media & Standby Recovery);  krpi – support for the package : dbms_pitr
krvg krvt rcv/vwr krvg – support for generation of redo associated with DDL; krvt – support for redo log miner viewer (also known as log miner)
ksa ksdp ksdx kse ksfd ksfh ksfq ksfv ksi ksim ksk ksl ksm ksmd ksmg ksn ksp kspt ksq ksr kss ksst ksu ksut vos support for various kernel associated capabilities
ksx sqlexec/execsvc support for query execution associated with temporary tables
ksxa ksxp ksxr vos support for various kernel associated capabilities in relation to OPS or RAC operation
kta space/spcmgmt support for DML locks and temporary tables associated with table access
ktb ktbt ktc txn/lcltx transaction control operations at the block level : locking block, allocating space within the block , freeing up space, etc.
ktec ktef ktehw ktein ktel kteop kteu space/spcmgmt support for extent management operations :
ktec – extent concurrency operations; ktef – extent format; ktehw – extent high water mark operations; ktein – extent  information operations; ktel – extent support for sql loader; kteop – extent operations : add extent to segment, delete extent, resize extent, etc. kteu – redo support for operations changing segment header / extent map
ktf txn/lcltx flashback support
ktfb ktfd ktft ktm space/spcmgmt ktfb – support for bitmapped space manipulation of files/tablespaces;  ktfd – dictionary-based extent management; ktft – support for temporary file manipulation; ktm – SMON operation
ktp ktpr ktr ktri txn/lcltx ktp – support for parallel transaction operation; ktpr – support for parallel transaction recovery; ktr – kernel transaction read consistency;
ktri – support for dbms_resumable package
ktsa ktsap ktsau ktsb ktscbr ktsf ktsfx ktsi ktsm ktsp ktss ktst ktsx ktt kttm space/spcmgmt support for checking and verifying space usage
ktu ktuc ktur ktusm txn/lcltx internal management of undo and rollback segments
kwqa kwqi kwqic kwqid kwqie kwqit kwqj kwqm kwqn kwqo kwqp kwqs kwqu kwqx oltp/qs support for advanced queuing :
kwqa – advanced queue administration; kwqi – support for AQ PL/SQL trusted callouts; kwqic – common AQ support functions; kwqid – AQ dequeue support; kwqie – AQ enqueu support ; kwqit – time management operation ; kwqj – job queue scheduler for propagation; kwqm – Multiconsumer queue IOT support; kwqn – queue notifier; kwqo – AQ support for checking instType checking options; kwqp – queueing propagation; kwqs – statistics handling; kwqu – handles lob data. ; kwqx – support for handling transformations
kwrc kwre oltp/re rules engine evaluation
kxcc kxcd kxcs sqllang/integ constraint processing
kxdr sqlexec/dmldrv DML driver entrypoint
kxfp kxfpb kxfq kxfr kxfx sqlexec/pq parallel query support
kxhf kxib sqlexec/execsvc khhf- support for hash join file and memory management; kxib – index buffering operations
kxs dict/instmgmt support for executing shared cursors
kxti kxto kxtr dbproc/trigger support for trigger operation
kxtt ram/partnmap support for temporary table operations
kxwph ram/data support for managing attributes of the segment of a table / cluster / table-partition
kza security/audit support for auditing operations
kzar security/dac support for application auditing
kzck security/crypto encryption support
kzd security/dac support for dictionary access by security related functions
kzec security/dbencryption support inserting and retrieving encrypted objects into and out of the database
kzfa kzft security/audit support for fine grained auditing
kzia security/logon identification and authentication operations
kzp kzra kzrt kzs kzu kzup security/dac security related operations associated with privileges
msqima msqimb sqlexec/sqlgen support for generating sql statments
ncodef npi npil npixfr progint/npi support for managing remote network connection from  within the server itself
oba sqllang/outbufal operator buffer allocate for various types of operators : concatenate, decode, NVL, etc.  the list is extensive.
ocik progint/oci OCI oracle server functions
opiaba opidrv opidsa opidsc opidsi opiexe opifch opiino opilng opipar opipls opirip opitsk opix progint/opi OPI Oracle server functions – these are at the top of the server stack and are called indirectly by ythe client in order to server the client request.
orlr objmgmt/objmgr support for  C langauge interfaces to user-defined types (UDTs)
orp objmgmt/pickler oracle’s external pickler / opaque type interfaces
pesblt pfri pfrsqc plsql/cox pesblt – pl/sql built in interpreter; pfri – pl/sql runtime; pfrsqc – pl/sql callbacks for array sql and dml with returning
piht plsql/gen/utl support for pl/sql implementation of utl_http package
pirg plsql/cli/utl_raw support for pl/sql implementation of utl_raw package
pism plsql/cli/utl_smtp support for pl/sql implementation of utl_smtp package
pitcb plsql/cli/utl_tcp support for pl/sql implementation of utl_tcp package
piur plsql/gen/utl_url support for pl/sql implementation of utl_url package
plio plsql/pkg pl/sql object instantiation
plslm plsql/cox support for NCOMP processing
plsm pmuc pmuo pmux objmgmt/pol support for pl/sql handling of collections
prifold priold plsql/cox support to allow rpc forwarding to an older release
prm sqllang/param parameter handling associated with sql layer
prsa prsc prssz sqllang/parse prsa – parser for alter cluster command; prsc – parser for create database command; prssz – support for parse context to be saved
psdbnd psdevn progint/dbpsd psdbnd – support for managing bind variables; psdevn – support for pl/sql debugger
psdicd progint/plsicds small number of ICD to allow pl/sql to call into ‘C’ source
psdmsc psdpgi progint/dbpsd psdmsc – pl/sql system dependent miscellaneous functions ; psdpgi – support for opening and closing cursors in pl/sql
psf plsql/pls pl/sql service related functions for instantiating called pl/sql unit in library cache
qbadrv qbaopn sqllang/qrybufal provides allocation of buffer and control structures in query execution
qcdl qcdo dict/dictlkup qcdl – query compile semantic analysis; qcdo – query compile dictionary support for objects
qci dict/shrdcurs support for SQL language parser and semantic analyser
qcop qcpi qcpi3 qcpi4 qcpi5 sqllang/parse support for query compilation parse phase
qcs qcs2 qcs3 qcsji qcso dict/dictlkup support for semantic analysis by SQL compiler
qct qcto sqllang/typeconv qct – query compile type check operations; qcto –  query compile type check operators
qcu sqllang/parse various utilities provided for sql compilation
qecdrv sqllang/qryedchk driver performing high level checks on sql language query capabilities
qerae qerba qerbc qerbi qerbm qerbo qerbt qerbu qerbx qercb qercbi qerco qerdl qerep qerff qerfi qerfl qerfu qerfx qergi qergr qergs qerhc qerhj qeril qerim qerix qerjm qerjo qerle qerli qerlt qerns qeroc qeroi qerpa qerpf qerpx qerrm qerse qerso qersq qerst qertb qertq qerua qerup qerus qervw qerwn qerxt sqlexec/rowsrc row source operators :
qerae – row source (And-Equal) implementation; qerba – Bitmap Index AND row source; qerbc – bitmap index compaction row source; qerbi – bitmap index creation row source; qerbm – QERB Minus row source; qerbo  – Bitmap Index OR row source; qerbt – bitmap convert row source; qerbu – Bitmap Index Unlimited-OR row source; qerbx – bitmap index access row source; qercb – row source: connect by; qercbi – support for connect by; qerco – count row source; qerdl – row source delete; qerep – explosion row source; qerff – row source fifo buffer; qerfi  – first row row source; qerfl  – filter row source definition; qerfu – row source: for update; qerfx – fixed table row source; qergi – granule iterator row source; qergr – group by rollup row source; qergs – group by sort row source; qerhc – row sources hash clusters; qerhj – row source Hash Join;  qeril  – In-list row source; qerim – Index Maintenance row source; qerix – Index row source; qerjo – row source: join; qerle – linear execution row source implementation; qerli – parallel create index; qerlt – row source populate Table;  qerns  – group by No Sort row source; qeroc – object collection iterator row source; qeroi – extensible indexing query component; qerpa – partition row sources; qerpf – query execution row source: prefetch; qerpx – row source: parallelizer; qerrm – remote row source; qerse – row source: set implementation; qerso – sort row source; qersq – row source for sequence number; qerst  – query execution row sources: statistics; qertb – table row source; qertq  – table queue row source; qerua – row source : union-All;
qerup – update row source; qerus – upsert row source ; qervw – view row source; qerwn – WINDOW row source; qerxt – external table fetch row source
qes3t qesa qesji qesl qesmm qesmmc sqlexec/execsvc run time support for sql execution
qkacon qkadrv qkajoi qkatab qke qkk qkn qkna qkne sqlexec/rwsalloc SQL query dynamic structure allocation routines
qks3t sqlexec/execsvc query execution service associated with temp table transformation
qksmm qksmms qksop sqllang/compsvc qksmm –  memory management services for the SQL compiler; qksmms – memory management simulation services for the SQL compiler; qksop – query compilation service for operand processing
qkswc sqlexec/execsvc support for temp table transformation associated for with clause.
qmf xmlsupp/util support for ftp server; implements processing of ftp commands
qmr qmrb qmrs xmlsupp/resolver support hierarchical resolver
qms xmlsupp/data support for storage and retrieval of XOBs
qmurs xmlsupp/uri support for handling URIs
qmx qmxsax xmlsupp/data qmx – xml support; qmxsax – support for handling sax processing
qmxtc xmlsupp/sqlsupp support for ddl  and other operators related to the sql XML support
qmxtgx xmlsupp support for transformation : ADT -> XML
qmxtsk xmlsupp/sqlsupp XMLType support functions
qsme summgmt/dict summary management expression processing
qsmka qsmkz dict/dictlkup qsmka – support to analyze request in order to determine whether a summary could be created that would be useful; qsmkz – support for create/alter summary semantic analysis
qsmp qsmq qsmqcsm qsmqutl summgmt/dict qsmp – summary management partition processing; qsmq – summary management dictionary access; qsmqcsm – support for create / drop / alter summary and related dimension operations; qsmqutl – support for summaries
qsms summgmt/advsvr summary management advisor
qxdid objsupp/objddl support for domain index ddl operations
qxidm objsupp/objsql support for extensible index dml operations
qxidp objsupp/objddl support for domain index ddl partition operations
qxim objsupp/objsql extensible indexing support for objects
qxitex qxopc qxope objsupp/objddl qxitex – support for create / drop indextype; qxope – execution time support for operator  callbacks; qxope – execution time support for operator DDL
qxopq qxuag qxxm objsupp/objsql qxopq – support for queries with user-defined operators; qxuag – support for user defined aggregate processing; qxxm – queries involving external tables
rfmon rfra rfrdb rfrla rfrm rfrxpt drs implements 9i data guard broker monitor
rnm dict/sqlddl manages rename statement operation
rpi progint/rpi recursive procedure interface which handles the the environment setup where multiple recursize statements are executed from one top level statement
rwoima sqlexec/rwoprnds row operand operations
rwsima sqlexec/rowsrc row source implementation/retrieval according to the defining query
sdbima sqlexec/sort manages and performs sort operation
selexe sqlexec/dmldrv handles the operation of select statement execution
skgm osds platform specific memory management rountines interfacing with O.S. allocation functions
smbima sor sqlexec/sort manages and performs sort operation
sqn dict/sqlddl support for parsing references to sequences
srdima srsima stsima sqlexec/sort manages and performs sort operation
tbsdrv space/spcmgmt operations for executing create / alter / drop tablespace and related supporting functions
ttcclr ttcdrv ttcdty ttcrxh ttcx2y progint/twotask two task common layer which provides high level interaction and negotiation functions for Oracle client when communicating with the server.  It also provides important function of converting client side data / data types into equivalent on the server and vice versa
uixexe ujiexe updexe upsexe sqlexec/dmldrv support for : index maintenance operations, the execution of the update statement and associated actions connected with update as well as the upsert command which combines the operations of update and insert
vop optim/vwsubq view optimisation related functionality
xct txn/lcltx support for the management of transactions and savepoint operations
xpl sqlexec/expplan support for the explain plan command
xty sqllang/typeconv type checking functions
zlke security/ols/intext label security error handling component

ORA-600 [kddummy_blkchk] [18038] 一例

一位客户的Oracle告警日志中出现了ORA-600 [kddummy_blkchk] [18038]故障,alert中的具体信息:

Errors in file /u01/app/oracle/admin/prdw014a/udump/prdw014a_ora_4377.trc:
ORA-00600: internal error code, arguments: [kddummy_blkchk], [222], [5792], [18038], [], [], [], []
Mon May 17 15:27:53 2010
Trace dumping is performing id=[cdmp_20100517152753]
Mon May 17 15:27:53 2010
Doing block recovery for file 2 block 504365
Block recovery from logseq 159276, block 166357 to scn 10934615778284
Mon May 17 15:27:53 2010
Recovery of Online Redo Log: Thread 1 Group 4 Seq 159276 Reading mem 0
Mem# 0: /u01/app/oracle/dataPRDW014/redo04a_1.log
Mem# 1: /u01/app/oracle/dataPRDW014/redo04a_2.log
Block recovery completed at rba 159276.167277.16, scn 2545.3924010007
Doing block recovery for file 222 block 5792
Block recovery from logseq 159276, block 84741 to scn 10934615778283
Mon May 17 15:27:53 2010
Recovery of Online Redo Log: Thread 1 Group 4 Seq 159276 Reading mem 0
Mem# 0: /u01/app/oracle/dataPRDW014/redo04a_1.log
Mem# 1: /u01/app/oracle/dataPRDW014/redo04a_2.log
Block recovery completed at rba 159276.167277.16, scn 2545.3924009964
Mon May 17 15:27:55 2010
Block recovery completed at rba 159276.167277.16, scn 2545.3924009964
Mon May 17 15:27:55 2010
Corrupt Block Found
TSN = 67, TSNAME = OBA_DATA
RFN = 222, BLK = 5792, RDBA = 931141280
OBJN = 1657288, OBJD = 1699775, OBJECT = W_ORG_DS, SUBOBJECT =
SEGMENT OWNER = BMS_OBA_DW, SEGMENT TYPE = Table Segment
Mon May 17 15:32:56 2010
Trace dumping is performing id=[cdmp_20100517153255]

附600错误产生的trace信息:
prdw014a_ora_4377.trc

/u01/app/oracle/admin/prdw014a/udump/prdw014a_ora_4377.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining Scoring Engine
and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/102prdw014
System name: SunOS
Node name: v08k405
Release: 5.9
Version: Generic_122300-29
Machine: sun4u
Instance name: prdw014a
Redo thread mounted by this instance: 1
Oracle process number: 109
Unix process pid: 4377, image: oracle@v08k405
*** 2010-05-17 15:23:15.391
*** ACTION NAME:() 2010-05-17 15:23:15.389
*** MODULE NAME:(pmdtm@v04k413 (TNS V1-V3)) 2010-05-17 15:23:15.389
*** SERVICE NAME:(prdw014_taf) 2010-05-17 15:23:15.389
*** SESSION ID:(789.48811) 2010-05-17 15:23:15.389
TYP:0 CLS: 4 AFN:222 DBA:0x378016a0 OBJ:1699775 SCN:0x09f1.e9e3a3eb SEQ: 2 OP:14.4
kteop redo - redo operation on extent map
RESIZE: entry:0 delta:
...
..
..
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kddummy_blkchk], [222], [5792], [18038], [], [], [], []
Current SQL statement for this session:
INSERT /*+ SYS_DL_CURSOR */ INTO bms_oba_dw.W_ORG_DS ("W_CUSTOMER_CLASS","NAME","ST_ADDRESS","CITY","STATE","ZIPCODE","COUNTRY","CUST_TYPE_CODE","CUST_TYPE_NAME","ACTIVE_FLG","DOM_ULT_DUNS_NUM","DUNS_NUM","EMP_COUNT","FORMED_DT","GLBLULT_DUNS_NUM","ANNUAL_REVENUE","BRANCH_FLG","BIRTH_DT","NO_OF_CHILDREN","LEGAL_NAME","FAMILY_NAME","OTHER_NAME","PREFERRED_NAME","INDV_ADDNL_TITLE","INDV_TITLE","INDV_MARITAL_STATE","INDV_GENDER","EMAIL_ADDRESS","RELATIONSHIP_STATE","INDV_EMP_STATUS","FAX_NUM","PAGER_NUM","MOBILE_NUM","LIFE_CYCLE_STATE","CUST_CAT_CODE","CUST_CAT_NAME","SIC_CODE","SIC_NAME","GOVT_ID_TYPE","GOVT_ID_VALUE","DUNNS_SITE_NAME","DUNNS_GLOBAL_NAME","DUNNS_LEGAL_NAME","CUSTOMER_NUM","ALT_CUSTOMER_NUM","ALT_PHONE_NUM","INTERNET_HOME_PAGE","LEGAL_STRUCT_CODE","LEGAL_STRUCT_NAME","DIRECT_MKTG_FLG","SOLICITATION_FLG","CUSTOMER_HIER1_CODE","CUSTOMER_HIER1_NAME","CUSTOMER_HIER2_CODE","CUSTOMER_HIER2_NAME","CUSTOMER_HIER3_CODE","CUSTOMER_HIER3_NAME","CUSTOMER_HIER4_CODE","CUSTOMER_HIER4_NAME","CUSTOMER_HIER5_CODE","CUSTOMER_HIER5_NAME","CUSTOMER_HIER6_CODE","CREATED_BY_ID","CHANGED_BY_ID","CREATED_ON_DT","CHANGED_ON_DT","AUX1_CHANGED_ON_DT","AUX2_CHANGED_ON_DT","AUX3_CHANGED_ON_DT","AUX4_CHANGED_ON_DT","SRC_EFF_FROM_DT","SRC_EFF_TO_DT","DELETE_FLG","DATASOURCE_NUM_ID","INTEGRATION_ID","TENANT_ID","X_CUSTOM","MOT_ATTRIBUTE1","MOT_ATTRIBUTE2","MOT_ATTRIBUTE3","MOT_ATTRIBUTE4","MOT_ATTRIBUTE5","MOT_ATTRIBUTE6","MOT_ATTRIBUTE7","MOT_ATTRIBUTE8","MOT_ATTRIBUTE9","MOT_ATTRIBUTE10","MOT_ATTRIBUTE11","MOT_ATTRIBUTE12","MOT_ATTRIBUTE13","MOT_ATTRIBUTE14","MOT_ATTRIBUTE15","MOT_ATTRIBUTE16","MOT_ATTRIBUTE17","MOT_ATTRIBUTE18","MOT_ATTRIBUTE19","MOT_ATTRIBUTE20","MOT_PARTY_TYPE","MOT_PHONE_AREA_CODE","MOT_ORIG_SYSTEM_REFERENCE","MOT_PER_EMAIL_ADDR","MOT_PERSON_FIRST_NAME","MOT_PHONE_EXTENSION","MOT_ALTERNATE_NAME","MOT_TELEPHONE_TYPE","MOT_SALES_CHANNEL_CODE","MOT_ACCOUNT_NAME","MOT_ATTRIBUTE_CATEGORY","MOT_INTERCOMPANY_FLAG","MOT_PARTY_NUMBER","MOT_PARTY_ID","MOT_LAST_UPDATE_LOGIN","MOT_CUST_CLASS_DESC","MOT_RECEIPT_METHOD_NAME","MOT_PHONE_NUMBER","MOT_CONTACT_POINT_PURPOSE","MOT_SALESREP_NAME","MOT_PAY_TERMS_CODE","MOT_PAY_TERMS_NAME") VALUES (NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL)
----- Call Stack Trace -----
calling              call     entry                argument values in hex
location             type     point                (? means dubious value)
-------------------- -------- -------------------- ----------------------------
ksedmp()+744         CALL     ksedst()             000000840 ?
FFFFFFFF7FFF620C ?
000000000 ?
FFFFFFFF7FFF2D00 ?
FFFFFFFF7FFF1A68 ?
FFFFFFFF7FFF2468 ?
kgerinv()+200        PTR_CALL 0000000000000000     000106800 ? 10681C1C4 ?
10681C000 ? 00010681C ?
000106800 ? 10681C1C4 ?
kseinpre()+96        CALL     kgerinv()            106816B18 ? 000000000 ?
1064564C0 ? 000000003 ?
FFFFFFFF7FFF6750 ?
000001430 ?
ksesin()+52          CALL     kseinpre()           000106800 ? 000000003 ?
00000025F ? 10681C1B8 ?
FFFFFFFF7FFF6750 ?
1068167D8 ?
kco_blkchk()+2568    CALL     ksesin()             1064564C0 ? 000000003 ?
000106800 ? 0000000DE ?
000000000 ? 000106800 ?
kcoapl()+1284        CALL     kco_blkchk()         0001900DE ? 0378016A0 ?
0000016A0 ? 00000FC00 ?
000000000 ?
FFFFFFFF7FFF89F8 ?
kcbapl()+412         CALL     kcoapl()             000000002 ? 000002300 ?
000105800 ? 583DBC000 ?
106816C98 ? 00010598F ?
kcrfw_redo_gen()+16  CALL     kcbapl()             FFFFFFFF7FFF89B8 ?
376                                                583FB7870 ?
FFFFFFFF7AF3AA3C ?
B6E9FABD0 ? 000000000 ?
583DBC000 ?
kcbchg1_main()+1363  CALL     kcrfw_redo_gen()     000000000 ?
2                                                  FFFFFFFF7FFF76C8 ?
B693A9998 ? 000000000 ?
3800135A0 ?
FFFFFFFF7FFF7700 ?
kcbchg1()+1324       CALL     kcbchg1_main()       000100C00 ?
FFFFFFFF7FFF7850 ?
000000000 ? 583FB7870 ?
000000000 ? 00000FFFF ?
ktuchg()+968         CALL     kcbchg1()            000106819 ? 1068195B8 ?
1068195C8 ? 106819000 ?
000000000 ? 106819000 ?
ktbchg2nt()+104      CALL     ktuchg()             000000002 ? 000000001 ?
FFFFFFFF7FFF8928 ?
B67A76DD8 ? 000000000 ?
000000000 ?
kteopgen()+728       CALL     ktbchg2nt()          FFFFFFFF7FFF89B8 ?
FFFFFFFF7FFF87C4 ?
000000000 ? 000000000 ?
FFFFFFFF7FFF8928 ?
FFFFFFFF7FFF9D98 ?
kteopresize()+2276   CALL     kteopgen()           FFFFFFFF7FFF89B8 ?
000000006 ? 000106800 ?
000000002 ? 10682247C ?
106816B18 ?
ktsxbmdelext1()+968  CALL     kteopresize()        FFFFFFFF7FFF9D98 ?
8                                                  FFFFFFFF7FFF9E88 ?
000000004 ? 000000002 ?
000000000 ? 000000000 ?
ktsstrm_segment()+6  CALL     ktsxbmdelext1()      FFFFFFFF7AD33A78 ?
308                                                0000016A0 ? 0003FFFFF ?
FFFFFFFF7AD33A78 ?
106822000 ? 000000043 ?
ktsmg_trimf()+1208   CALL     ktsstrm_segment()    000000000 ? 000000003 ?
000000001 ? 000100C00 ?
106819000 ? 000000000 ?
kdbltrmt()+1916      CALL     ktsmg_trimf()        00010598F ? 0000010E2 ?
106822478 ? 000000005 ?
10682247C ? 106816B18 ?
kdblfpl()+96         CALL     kdbltrmt()           000000006 ? 000000000 ?
FFFFFFFF7AD33918 ?
000000180 ? 0000010E4 ?
000000008 ?
kdblfl()+1948        CALL     kdblfpl()            FFFFFFFF7FFFB0AC ?
FFFFFFFF7AD33918 ?
000000000 ?
FFFFFFFF7AD33AE0 ?
FFFFFFFF7AD33A68 ?
000000000 ?
klafin()+160         CALL     kdblfl()             FFFFFFFF7FFFB0AC ?
FFFFFFFF7AD33918 ?
000000000 ? 000000001 ?
000000008 ? 000106800 ?
kpodpfin()+76        CALL     klafin()             FFFFFFFF7AF35C40 ?
1059BF2B8 ? 000000321 ?
FFFFFFFF7AD33918 ?
000000000 ? 000400000 ?
kpodpmop()+320       CALL     kpodpfin()           FFFFFFFF7AF35C40 ?
000106816 ? 000106800 ?
000000321 ? 000000001 ?
FFFFFFFF7AF35BC8 ?
opiodr()+1496        PTR_CALL 0000000000000000     000000301 ? 000000321 ?

进过与Oracle support确认,定位为Bug 5386204 – Block corruption / OERI[kddummy_blkchk] after direct load of ASSM segment [ID 5386204.8].
“kteop redo – redo operation on extent map” 记录是确定该Bug的一个重要依据。

该Bug的Oracle note:

Bug 5386204  Block corruption / OERI[kddummy_blkchk] after direct load of ASSM segment
This note gives a brief overview of bug 5386204.
The content was last updated on: 08-FEB-2010
Click here for details of each of the sections below.
This bug is alerted in Note:580561.1
Affects:

Product (Component)    Oracle Server (Rdbms)
Range of versions believed to be affected    Versions < 11
Versions confirmed as being affected

* 9.2.0.8
* 10.2.0.1
* 10.2.0.2
* 10.2.0.3
* 10.2.0.4

Platforms affected    Generic (all / most platforms affected)

Fixed:

This issue is fixed in

* 9.2.0.8 Patch 15 on Windows Platforms
* 10.2.0.2 Patch 15 on Windows Platforms
* 10.2.0.3 Patch 5 on Windows Platforms
* 10.2.0.4.1 (Patch Set Update)
* 10.2.0.4 Patch 2 on Windows Platforms
* 10.2.0.5 (Server Patch Set)
* 11.1.0.6 (Base Release)

Symptoms:

Related To:

* Internal Error May Occur (ORA-600)
* Corruption (Logical)
* ORA-600 [kddummy_blkchk]

* Direct Path Operations
* ASSM Space Management (Bitmap Managed Segments)

Description

Block corruption / ORA-600 [kddummy_blkchk][file#] [block#] [18038]
can occur on a segment which has been direct loaded.

(The corruption shows as a PAGETABLE SEGMENT HEADER
having blocks in the “Auxillary Map” outside of the “Extent Map”
range)

Note:
This bug was previously incorrectly listed as fixed in 10.2.0.4

Further details on this issue can be found in Note:580561.1
ORA-600 [kddummy_blkchk][][][18038] during extent operations like TRUNCATE on ASSM tablespaces [ID 580561.1]

Applies to:
Oracle Server – Enterprise Edition – Version: 9.2.0.8 to 10.2.0.4
Information in this document applies to any platform.
Description

This alert describes the problem in Bug 5386204 / Note 5386204.8.

Block corruption with error ORA-600 [kddummy_blkchk] [file#] [block#] [18038]
may be reported during a DROP/TRUNCATE

The corruption shows as a PAGETABLE SEGMENT HEADER having blocks in the
“Auxillary Map” outside of the “Extent Map” range.

The same operation terminated without any error in previous RDBMS versions
like Oracle9i.

Likelihood of Occurrence

The object is populated by direct path operations such as SQL*Loader using DIRECT=Y for example.
The object is stored in a Locally Managed Tablespace (LMT) that is using ASSM (dba_tablespaces.segment_space_management=’AUTO’).
Bug 5386204 is mostly hit when db_block_size=16384.

Possible Symptoms

One evidence of hitting this bug might be the value 18038 in the third argument of
ORA-600 [kddummy_blkchk] where [18038] is a check error code.

@Error check code 18038 means that the “Data dba” stored in “Auxiliary Map” is out of range

@TYP:0 CLS: 4 AFN:234 DBA:0x3a801554 OBJ:0 SCN:0x000b.290f5e0d SEQ:  1 OP:14.2

@In this case “Data dba: 0x3a801555” stored in the “Auxiliary Map” is equal to 0x3a801551 + 4 which is out of the extent 0, hence the error.
@Note that extent 0 is 4 blocks, so extent 0 starts from 0x3a801551 to 0x3a801554.

Workaround or Resolution

In order to identify objects that are affected by the corruption, use the procedure
DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY

@DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY is also an option but it requires patch for Bug 6760697 is needed)

How to execute DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY:

alter system set DB_BLOCK_CHECKSUM = OFF;
— open a new session and run :
exec DBMS_SPACE_ADMIN.assm_tablespace_verify(‘<Tablespace Name>’, DBMS_SPACE_ADMIN.TS_VERIFY_DEEP, DBMS_SPACE_ADMIN.SEGMENT_VERIFY_DEEP);

See if any trace file is generated in the directory defined by user_dump_dest.
The absence of a trace file means that no corrupt segments were found.

Note: DB_BLOCK_CHECKSUM has to be disabled; otherwise the same ORA-600 error may be produced

@Oracle check block type 0x23=PAGETABLE SEGMENT HEADER even if DB_BLOCK_CHECKING is not set.

Example of output from DBMS_SPACE_ADMIN.ASSM_TABLESPACE_VERIFY

Segment header [dba: 0x003a801554, (file 234,block 5460)]
Segment object id: 7825838; inc. no.: 0
*********

verifying extent map and tablespace bitmap consistency
———
Verifying extent map and  auxilliary extent map   consistency in   the segment
Block Corruption in seg hdr / ext map block:                rdba: 0x3a801554, err code: 18038

Identifying the object using the segment header information.

Segment header [dba: 0x003a801554, (file 234,block 5460)]

select *
from DBA_EXTENTS
where FILE_ID = 234
and 5460 between block_id and block_id + blocks – 1;

Identifying the object using the Segment object id information.

Segment object id: 7825838; inc. no.: 0

select *
from DBA_OBJECTS
where DATA_OBJECT_ID = 7825838;

@How to execute DBMS_SPACE_ADMIN.ASSM_SEGMENT_VERIFY

WORKAROUNDs:

Disable DB_BLOCK_CHECKSUM for any action taken.

Note: DB_BLOCK_CHECKSUM has to be disabled; otherwise the same ORA-600 error may be produced

alter system set DB_BLOCK_CHECKSUM = OFF;
— open a new session

DROP TABLE .. PURGE;
ALTER TABLE .. MOVE ..;
Create table as select (CTAS)
export/import, etc

Patches

The patch prevents the corruption from taking place. Affected objects will have to be recreated.

This bug was previously incorrectly listed as fixed in 10.2.0.4.

@This problem is fixed in the 10.2.0.5 Patch Set (not available yet and still without a due date).
This problem is fixed in the 11.1.0.6 rdbms release.

One off patches for this issue are available for some platforms / versions.

See Patch 5386204 for patch availability.

Modification History

03-JUN-2008 – Initial Alert version
04-JUN-2008 – Implemented correction
11-JUN-2008 – Added info about DB_BLOCK_CHECKSUM
13-JUN-2008 – Published

References

BUG:5386204 – ORA-600 [KDDUMMY_BLKCHK] ERRORS WITH CODE 18038

NOTE:5386204.8 – Bug 5386204 – Block corruption / OERI[kddummy_blkchk] after direct load of ASSM segment

Bug 5386204: ORA-600 [KDDUMMY_BLKCHK] ERRORS WITH CODE 18038

Show Bug Attributes Bug Attributes
Type     B – Defect     Fixed in Product Version     11.1
Severity     1 – Complete Loss of Service     Product Version     10.2.0.2
Status     80 – Development to Q/A     Platform     226 – Linux x86-64
Created     12-Jul-2006     Platform Version     2.6.5-7.191-SMP
Updated     20-May-2010     Base Bug     –
Database Version     10.2.0.2
Affects Platforms     Generic
Product Source     Oracle

Show Related Products Related Products
Line     Oracle Database Products     Family     Oracle Database
Area     Oracle Database     Product     5 – Oracle Server – Enterprise Edition

Hdr: 5386204 10.2.0.2 RDBMS 10.2.0.2 SPACE PRODID-5 PORTID-226 ORA-600
Abstract: ORA-600 [KDDUMMY_BLKCHK] ERRORS WITH CODE 18038

*** 07/12/06 12:59 am ***
TAR:
—-

PROBLEM:
——–
1. Clear description of the problem encountered
Customer is getting repeated ORA-600 [kddummy_blkchk] errors reported with
internal check code 18038 on tables which have had bulk deletions made.  This
has occurred on both production and test instances.

2. Pertinent configuration information (MTS/OPS/distributed/etc)
RAC, ASM

3. Indication of the frequency and predictability of the problem
Problem is intermittent but occurs several times a day impacting the
customers ability to work.

4. Sequence of events leading to the problem
Error is typically signalled on a COMMIT most likely following a deletion
from the tables.

5. Technical impact on the customer. Include persistent after effects.
Severe, as it occurs multiple times per day, and corrupt the underlying
tables preventing further data loads.

DIAGNOSTIC ANALYSIS:
——————–
The trace files show that the problem occurs following a bulk deletion from
the underlying tables, which appear to corrupt the extent map, as the segment
header dump shows 1 extent of 4 blks, but the deleteion entry in the redo
stream shows one extent of 8 blks, e.g.:

REDO RECORD – Thread:1 RBA: 0x0005da.000e5e34.01c0 LEN: 0x00fc VLD: 0x01
SCN: 0x000d.37eacce9 SUBSCN:  5 07/11/2006 10:29:53
CHANGE #1 TYP:0 CLS:60 AFN:39 DBA:0x09c322e0 OBJ:4294967295
SCN:0x000d.37eacce9 SEQ:  2 OP:5.1
ktudb redo: siz: 112 spc: 15940 flg: 0x0022 seq: 0x011d rec: 0x06
xid:  0x0016.020.000005b6
ktubu redo: slt: 32 rci: 5 opc: 14.5 objn: 2 objd: 93662 tsn: 12
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  Yes
0x00000000
kteopu undo – undo operation on extent map
segdba: 0x87e3cc  class: 4  mapdba:0x87e3cc  offset: 3
rbr extent – dba: 0x0  nbk: 0x0
kteop redo – redo operation on extent map
ADD: dba:0x803673d len:8 at offset:1
DEFAULT: ???
SETSTAT: exts:2 blks:16 lastmap:0x0 mapcnt:0
CHANGE #2 TYP:0 CLS: 4 AFN:2 DBA:0x0087e3cc OBJ:93662 SCN:0x000d.37eacce9
SEQ:  1 OP:14.4
kteop redo – redo operation on extent map
DELETE: entry:1
shift back: dba:0x0 len:0
SETSTAT: exts:1 blks:8 lastmap:0x0 mapcnt:0

WORKAROUND:
———–
None

RELATED BUGS:
————-
Bug 4949123 – ORA-600: [KDDUMMY_BLKCHK], [541], [147050], [18038]

REPRODUCIBILITY:
—————-
Consistently occurring at customers site.

TEST CASE:
———-
n/a

STACK TRACE:
————
ksedst ksedmp ksfdmp kgerinv kseinpre ksesin kco_blkchk kcoapl kcbapl
kcrfw_redo_gen kcbchg1_main kcbchg1 ktuchg ktbchg2nt kteopgen kteopresize
ktsxbmdelext1 ktsstrm_segment ktsmg_icmt_prepare ktcifc ktucmt ktpcmt ktcrcm
ktdcmt k2lcom k2send xctctl xctcom_with_options kksExecuteCommand opiexe
opipls opiodr rpidrus skgmstack rpidru rpiswu2 rpidrv psddr0 psdnal
pevm_EXECC pfrinstr_EXECC pfrrun_no_tool pfrrun plsql_run peicnt kkxexe
opiexe kpoal8 opiodr ttcpip opitsk opiino opiodr opidrv sou2o opimai_real
main __libc_start_main _start

SUPPORTING INFORMATION:
———————–
alertlogs and trace files

24 HOUR CONTACT INFORMATION FOR P1 BUGS:
—————————————-
n/a

DIAL-IN INFORMATION:
——————–
n/a

IMPACT DATE:
————
21-JUL-2006

*** 07/12/06 02:34 am *** (CHG: Asg->NEW OWNER OWNER)

A redo dump of the segment header during the entire procedure execution was
requested on 06 Aug and supplied on 09 Aug so why are you asking for this
information again when you already have it?  Please check that file
(redo_1.trc in bug5386204_07Aug.zip), and let me know if you need anything
else.
*** 09/19/06 02:39 am *** (CHG: Sta->30)
Uploaded the requested information in file bug5386204_Oct02.zip.

*** 11/27/06 11:13 am ***
Here is one theory we (space group) have on this bug so far:
During direct load one of the segments does not get loaded with any data. The
segment is empty and the first extent has 8 blocks (this is 16k block size).
However it goes through the usual high water mark movement phase (even though
the hwm does not move). During the hwm movement phase, the segment is trimmed
close to 64k boundary. For ASSM segment with 16k block size, this means the
segment will be left with no data blocks after the trim- 4 blocks after the
trim would represent bitmaps and segment header only.

There are two issues here:
(1) Why was ktsstrm_segment called on an empty (or unloaded) segment at first
place?
(2) Even if it was called, why is segment trimmed to 64k boundary?

I’m working on the 2nd issue and will give an update soon.
*** 11/29/06 03:18 am *** (CHG: Pri->1)
*** 11/29/06 03:18 am ***
*** 11/29/06 03:25 am *** -> CLOSED
*** 11/29/06 05:31 pm ***
*** 11/30/06 10:36 pm ***
We ran into some issues (bugs) while testing the code for the diagnostic
patch. I was hoping to have it finished by today but it seems it’ll take some
more time and I’m pretty hopeful of having it ready to go by tomorrow evening
(PST). I’m really sorry for the delay.
*** 12/01/06 07:16 pm ***
*** 12/02/06 05:05 pm ***
Sorry for the delay in replying. I would expect the long regressions to be
complete by sunday afternoon PST. I should be ready to release the patch by
sunday evening if things go fine. Will keep this page updated on my progress.
*** 12/03/06 05:30 pm ***
*** 12/04/06 05:35 pm ***
It seems most of major issues with the long regressions have been taken care
of and I hope to get a clean run on the farm soon, by tomorrow end of day and
the patch should be on its way soonafter.

I had a question though, that will help me in getting the patch out faster. I
wanted to know if the customer has had any diagnosibility patches installed
on their 10.2.0.2.0 release version.

Another thing which I would like to mention here is that my patch modifies
only one file (ktss.c) in the RDBMS code.
*** 12/05/06 02:19 am ***
*** 12/05/06 04:42 pm ***
*** 12/05/06 05:06 pm ***
I was hoping to have all the farm regressions (and the patch) done by today
evening but it seems farm is taking a bit long to finish the regressions.
I’ll work on the patch as soon as I have the regressions done. Sorry for the
delay. I’ll provide an update on that in the next few hours.
*** 12/05/06 09:07 pm ***
My regressions are still moving very slowly through the queue on the farm.
The farm seems to be busy with 11g Beta 4 deadline round the corner. My
regressions have been on the farm for more than a day now. I’ll work on the
patch as soon as I have a clean farm run.
*** 12/06/06 06:15 pm ***
Still waiting on the clean farm runs. Fortunately, I’ve been able to get a
high priority on the farm jobs. So, I expect things to run clean soon. Will
keep things updated here.
*** 12/07/06 05:54 pm ***
Got my farm runs completed last night but got a small number of diffs. Have
been trying to isolate them and hopefully soon, everything should be clean.
Farm has been giving those diffs over and over again though those look
unrelated to my change. Currently, verifying them on my linux workstation.
*** 12/08/06 06:01 pm ***
*** 12/08/06 06:23 pm ***
Have been able to run almost all the long regressions locally and things look
clean. There’s just a couple of long regressions which I’m still running and
I should expect to be ready to go as soon as they are completed. Should be
able to start the patch building soon.
*** 12/11/06 01:07 am ***
There’s one long regression which seems to be broken. I’m currently working
on that to have it run clean. Will update as soon as I have it running clean.
*** 12/11/06 01:23 pm ***
Everything is clean now. Working on starting the patch building process.
*** 12/11/06 02:46 pm ***

The customer has confirmed that following application of the suplied patch
the error no longer occurred when running the testcase, which ran through to
completion after about 8 hours.  They are resetting the testcase, and will
run it again to verify this, but the initial response is that this looks to
have resolved the problem.

Can you confirm if the patch would need to be rebuilt as a permananent fix,
ie. any diagnostics to be removed etc. or is it actually the full fix anyway?
*** 12/13/06 07:19 am ***
The customer has confirmed the following:

1. Rerun the test for the 2nd time with patched rdbms: completed quickly and
without any problems.
2. Rollbacked the patch: the test failed as expected within 30 minutes.
3. Re-applied the patch and ran the test once again: completed ok.

This appears to confirm that the patch resolves the problem so could we have
an answer to the previous update?
*** 12/13/06 07:47 pm ***
That is good news.
No additional diagnostics have been added to the patch. So, it’s not needed
to be rebuilt. I guess the supplied patch should be complete in itself.
*** 12/14/06 12:40 am ***
Thanks for the update.

该文档描述当使用直接路径方式导入数据时一定概率导致该Bug产生,譬如使用Sql loader且DIRECT=Y;
该Bug只会由存贮在本地管理方式(LTM)并自动段管理(ASSM)的对象引发, 并且当标准块大小为16k时出现概率较高(Bug 5386204 is mostly hit when db_block_size=16384.)
一般数据库都会启用db_block_checksum,该参数控制Oracle在读入块时做检验操作,[18038]是kddummy_blkchk的一种错误代码,出现该错误代码说明存储在段头中的辅助区间图中的Data dba越界, 我们举一个段头来看:

Start dump data blocks tsn: 4 file#: 4 minblk 139 maxblk 139
buffer tsn: 4 rdba: 0x0100008b (4/139)
scn: 0x0000.000f327e seq: 0x01 flg: 0x04 tail: 0x327e2301
frmt: 0x02 chkval: 0x619e type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
.......
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0      spare2: 0      #extents: 9      #blocks: 72
last map  0x00000000  #maps: 0      offset: 2716
Highwater::  0x0101e1f1  ext#: 8      blk#: 8      ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 65
mapblk  0x00000000  offset: 8
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater::  0x0101e1f1  ext#: 8      blk#: 8      ext size: 8
#blocks in seg. hdr's freelists: 0
#blocks below: 65
mapblk  0x00000000  offset: 8
Level 1 BMB for High HWM block: 0x0101e1e9
Level 1 BMB for Low HWM block: 0x0101e1e9
--------------------------------------------------------
Segment Type: 1 nl2: 1      blksz: 8192   fbsz: 0
L2 Array start offset:  0x00001434
First Level 3 BMB:  0x00000000
L2 Hint for inserts:  0x0100008a
Last Level 1 BMB:  0x0101e1e9
Last Level II BMB:  0x0100008a
Last Level III BMB:  0x00000000
Map Header:: next  0x00000000  #extents: 9    obj#: 51806  flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x01000089  length: 8
0x0101e1a1  length: 8
0x0101e1a9  length: 8
0x0101e1b9  length: 8
0x0101e1c1  length: 8
0x0101e1c9  length: 8
0x0101e1d9  length: 8
0x0101e1e1  length: 8
0x0101e1e9  length: 8
Auxillary Map
--------------------------------------------------------
Extent 0     :  L1 dba:  0x01000089 Data dba:  0x0100008c
Extent 1     :  L1 dba:  0x01000089 Data dba:  0x0101e1a1
Extent 2     :  L1 dba:  0x0101e1a9 Data dba:  0x0101e1aa
Extent 3     :  L1 dba:  0x0101e1a9 Data dba:  0x0101e1b9
Extent 4     :  L1 dba:  0x0101e1c1 Data dba:  0x0101e1c2
Extent 5     :  L1 dba:  0x0101e1c1 Data dba:  0x0101e1c9
Extent 6     :  L1 dba:  0x0101e1d9 Data dba:  0x0101e1da
Extent 7     :  L1 dba:  0x0101e1d9 Data dba:  0x0101e1e1
Extent 8     :  L1 dba:  0x0101e1e9 Data dba:  0x0101e1ea
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1:   0x0100008a

其中辅助区间图( Auxillary Map)列出了该段每个区间(Extent)的一级位图块以及区间中实际数据开始的data block address (Data dba).譬如Extent 0 中的Data dba应在
(0x0100008A ~0x01000090)之间,否则即越界。
DROP或TRUNCATE是触发该Bug的主要操作,原因是这2个操作都需要使用到Pagetable segment header中的Auxiliary Map。
Oracle建议的WorkAround方式主要是通过MOVE TABLESPACE 来”REBUILD”这个PAGETABLE SEGMENT HEADER。
这个Case中Oracle support给出Workaround建议:

1-. Make sure the below query will return the table mentioned above:

SQL> select owner, object_name, object_type, SUBOBJECT_NAME, OBJECT_ID,
DATA_OBJECT_ID, CREATED,LAST_DDL_TIME,TIMESTAMP
from DBA_OBJECTS
where DATA_OBJECT_ID =1699775;

If so continue:

SQL>alter system set DB_BLOCK_CHECKSUM = OFF;

Find all indexes for W_ORG_DS table.

SQL> select owner, index_name, index_type, table_name , table_owner from dba_indexes
Where table_owner = ‘BMS_OBA_DW’ and
Table_name = ‘W_ORG_DS’;

connect as BMS_OBA_DW

SQL> desc W_ORG_DS

if this table does not have LONG column, then Alter table table_name move is like a CTAS but better since is using the same name of the object plus keeping any related object like index, etc. If it has Long column then export/truncate/import need to be use;

SQL>Alter table W_ORG_DS Move;

Then rebuild all indexes for W_ORG_DS table as per above query: .i.e.

SQL>Alter index rebuild

To avoid problem, please apply patch for bug 5386204, see note 580561.1 for further information.

Oracle文档宣称其已在10.2.0.4的第一个patch set update及10.2.0.5中修复了该Bug.

注:最早认为该Bug在10.2.0.4中就已经修复了,但后来确认“This bug was previously incorrectly listed as fixed in 10.2.0.4”。

windows平台上的11g release 2终于发布了

下午无意中打开了oracle主页上11g下载的页面,赫然发现windows平台的安装介质已经发布了。

介质分成2个zip包,1.5g和600m; 11g的安装介质较10g大了许多,因为默认附加了apex与sql developer.

下载地址:

Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (32-bit)
Download win32_11gR2_database_1of2.zip (1,625,721,289 bytes)
Download win32_11gR2_database_2of2.zip (631,934,821 bytes)
Oracle Database 11g Release 2 (11.2.0.1.0) for Microsoft Windows (x64)
Download win64_11gR2_database_1of2.zip (1,213,501,989 bytes) (cksum – 3906682109)
Download win64_11gR2_database_2of2.zip (1,007,988,954 bytes) (cksum – 1232608515)

目前32位未提供grid infrastructure 介质,想要体验windows上的11g rac只能使用64bit .

Oracle Database 11g Release 2 Grid Infrastructure (11.2.0.1.0) for Microsoft Windows (x64)

Download win64_11gR2_grid.zip (715,166,425 bytes) (cksum – 3127109177)

根据metalink文档867040.1所述,Oracle database 11g release 2 将默认支持windows 7 以及 windows 2008 r2 .

单机的安装过程十分简单:

一般来说windows 发行版在正式生产环境中不多见,不过安装到笔记本上方便了今后对11gr2新特性的测试。

附release note:

Oracle Database 11g Release 2 Now Available on Windows 32 and 64-bit [ID 1081390.1]

Modified 07-APR-2010     Type ANNOUNCEMENT     Status PUBLISHED

In this Document
What is being announced?
References


Applies to:

Oracle Server – Standard Edition – Version: 11.2.0.1 – Release: 11.2
Oracle Server – Personal Edition – Version: 11.2.0.1 – Release: 11.2
Oracle Server – Enterprise Edition – Version: 11.2.0.1 – Release: 11.2
Microsoft Windows (32-bit)
Microsoft Windows x64 (64-bit)
Microsoft Windows x64 (64-bit) – OS Version: 7
Microsoft Windows (32-bit) – OS Version: 7
Microsoft Windows x64 (64-bit) – Version: 2008 R2

What is being announced?

Oracle Database 11g Release 2 for Windows x86 (32-bit) and x86-64 is now available for download from OTN or the Oracle Store.

New with 11.2 is certification with Windows 2008 R2 and Windows 7.   You can find out more in Note  1065024.1 “Oracle Database 11g Release 2 Certification Highlights”.

explore my oracle support using firefox 3.6

升级到FF3.6的朋友肯定发现了现在使用火狐浏览器访问my oracle support 时许多页面打开为空白页,譬如这篇介绍FF3.6与目前oracle support site兼容性的文章:

Firefox 3.6 Not Officially Supported by My Oracle Support
Although much of the functionality of My Oracle Support is accessible via Firefox 3.6, please be aware that Firefox 3.6 is not officially supported by My Oracle Support at this time. Please review the My Oracle Support FAQ (Knowledge Document #747252.5) for details on the current browser requirements for accessing My Oracle Support.
We are aware of a current issue when viewing Knowledge documents using Firefox 3.6. Using Firefox 3.6, when clicking on a link for a Knowledge document the document appears blank.  The workaround is to instead open the documents in a new tab or new window.
We are working on a fix for this issue and hope to have this resolved in the near future. We apologize for any inconvenience.

FF升级到3.6的时间已经超过一个月了;oracle support 之前曾发表将解决该兼容问题的声明,原本以为这只是一个小case,因该在数周内彻底解决。但目前又发布了暂时不官方支持的声明(大部分文档可以通过在新窗口内打开方式阅读)。

看起来大型网站的建设维护确实不是Oracle的特长,apex或者说htmldb又真的适合超大型web site吗?

参数cluster_interconnect详细介绍

以下文本摘自metalink doc:

This note attempts to clarify the cluster_interconnects parameter and the
platforms on which the implementation has been made. A brief explanation on
the workings of the parameter has also been presented in this note.
This is also one of the most frequently questions related to cluster and RAC
installations on most sites and forms a part of the prerequisite as well.

ORACLE 9I RAC – Parameter CLUSTER_INTERCONNECTS
———————————————–

FREQUENTLY ASKED QUESTIONS
————————–
November 2002

CONTENTS
——–
1.  What is the parameter CLUSTER_INTERCONNECTS for ?
2.  Is the parameter CLUSTER_INTERCONNECTS available for all platforms ?
3.  How is the Interconnect recognized on Linux ?
4.  Where could I find more information on this parameter ?
5.  How to detect which interconnect is used ?
6.  Cluster_Interconnects is mentioned in the 9i RAC administration
    guide as a Solaris specific parameter, is this the only platform
    where this parameter is available ?
7.  Are there any side effects for this parameter, namely affecting normal
    operations ?
8.  Is the parameter OPS_INTERCONNECTS which was available in 8i similar
    to this parameter ?
9.  Does Cluster_interconnect allow failover from one Interconnect to another
    Interconnect ?
10. Is the size of messages limited on the Interconnect ?
11. How can you see which protocoll is being used by the instances ?
12. Can the parameter CLUSTER_INTERCONNECTS be changed dynamically during runtime ?

 
QUESTIONS & ANSWERS
——————-
1. What is the parameter CLUSTER_INTERCONNECTS for ?

Answer
——
This parameter is used to influence the selection of the network interface
for Global Cache Service (GCS) and Global Enqueue Service (GES) processing.

This note does not compare the other elements of 8i OPS with 9i RAC
because of substantial differences in the behaviour of both architectures.
Oracle 9i RAC has certain optimizations which attempt to transfer most of
the information required via the interconnects so that the number of disk
reads are minimized. This behaviour known as Cache fusion phase 2 is summarised
in Note 139436.1
The definition of the interconnnect is a private network which
will be used to transfer the cluster traffic and Oracle Resource directory
information and blocks to satisfy queries. The technical term for that is
cache fusion.

The CLUSTER_INTERCONNECTS should be used when
– you want to override the default network selection
– bandwith of a single interconnect does not meet the bandwith requirements of
  a Real Application Cluster database

The syntax of the parameter is:

CLUSTER_INTERCONNECTS = if1:if2:…:ifn
Where if<n> is an IP address in standard dotted-decimal format, for example,
144.25.16.214. Subsequent platform implementations may specify interconnects
with different syntaxes.
2. Is the parameter CLUSTER_INTERCONNECTS available for all platforms ?

Answer
——

This parameter is configurable on most platforms.
This parameter can not be used on Linux.

The following Matrix shows when the parameter was introduced on which platform:

Operating System    Available since
AIX                   9.2.0
HP/UX                 9.0.1
HP Tru64              9.0.1
HP OPenVMS            9.0.1
Sun Solaris           9.0.1

References
———-
Bug <2119403> ORACLE9I RAC ADMINISTRATION SAYS CLUSTER_INTERCONNECTS IS SOLARIS ONLY.
Bug <2359300> ENHANCE CLUSTER_INTERCONNECTS TO WORK WITH 9I RAC ON IBM
3.  How is the Interconnect recognized on Linux ?

Answer
——
Since Oracle9i 9.2.0.8 CLUSTER_INTECONNETCS can be used to change the interconnect.
A patch is also available for 9.2.0.7 under Patch 4751660.
Before 9.2.0.8 the Oracle implementation for the interface selection reads the ‘private hostname’
in the cmcfg.ora file and uses the corresponding ip-address for the interconnect.
If no private hostname is available the public hostname will be used.
4.  Where could I find information on this parameter ?

Answer
——

The parameter is documented in the following books:
Oracle9i Database Reference Release 2 (9.2)
Oracle9i Release 1 (9.0.1) New Features in Oracle9i Database Reference –
                   What’s New in Oracle9i Database Reference?
Oracle9i Real Application Clusters Administration Release 2 (9.2)
Oracle9i Real Application Clusters Deployment and Performance Release 2 (9.2)

Also port specific documentation may contain information about the usage of
the cluster_interconnects parameter.

Documentation can be viewed on
    http://tahiti.oracle.com
    http://otn.oracle.com/documentation/content.html
References:
———–
Note 162725.1: OPS/RAC VMS: Using alternate TCP Interconnects on 8i OPS
               and 9i RAC on OpenVMS

Note 151051.1: Init.ora Parameter “CLUSTER_INTERCONNECTS” Reference Note

5. How to detect which interconnect is used ?
    The following commands show which interconnect is used for UDP or TCP:
    sqlplus> connect / as sysdba
             oradebug setmypid
             oradebug ipc
             exit

    The corresponding trace can be found in the user_dump_dest directory and for
    example contains the following information in the last couple of lines:

           SKGXPCTX: 0x32911a8 ctx
           admno 0x12f7150d admport:
           SSKGXPT 0x3291db8 flags SSKGXPT_READPENDING     info for network 0
                 socket no 9     IP 172.16.193.1         UDP 43307
                 sflags SSKGXPT_WRITESSKGXPT_UP
                 info for network 1
                 socket no 0     IP 0.0.0.0      UDP 0
                 sflags SSKGXPT_DOWN
           context timestamp 0x1ca5
                 no ports
   Please note that on some platforms and versions (Oracle9i 9.2.0.1 on Windows)
   you might see an ORA-70 when the command oradebug ipc has not been
   implemented.

   When  other protocols such as LLT, HMP or RDG are used, then the trace file will not
   reveal an IP address.
6.  Cluster_Interconnects is mentioned in the 9i RAC administration
    guide as a Solaris specific parameter, is this the only platform
    where this parameter is available ?

Answer
—– 

This information that this parameter works on Solaris only is incorrect. Please
check the answer for question number 2 for the complete list of platforms for the same.

References:
———–
bug <2119403> ORACLE9I RAC ADMINISTRATION SAYS CLUSTER_INTERCONNECTS IS SOLARIS ONLY.
7.  Are there any side effects for this parameter, namely affecting normal
    operations ?

Answer
—–
When you set CLUSTER_INTERCONNECTS in cluster configurations, the
interconnect high availability features are not available. In other words,
an interconnect failure that is normally unnoticeable would instead cause
an Oracle cluster failure as Oracle still attempts to access the network
interface which has gone down. Using this parameter you are explicitly
specifying the interface or list of interfaces to be used.
 

8.  Is the parameter OPS_INTERCONNECTS which was available in 8i similar
    to this parameter ?

Answer
——
Yes, the parameter OPS_INTERCONNECTS was used to influence the network selection
for the Oracle 8i Parallel Server.

Reference
———
Note <120650.1> Init.ora Parameter “OPS_INTERCONNECTS” Reference Note
9.  Does Cluster_interconnect allow failover from one Interconnect to another
    Interconnect ?

Answer
——
Failover capability is not implemented at the Oracle level. In general this
functionality is delivered by hardware and/or Software of the operating system.
For platform details please see Oracle platform specific documentation
and the operating system documentation.
10. Is the size of messages limited on the Interconnect ?

Answer
——
The message size depends on the protocoll and platform.
UDP: In Oracle9i Release 2 (9.2.0.1) message size for UDP was limited to 32K.
     Oracle9i 9.2.0.2 allows to use bigger UDP message sizes depending on the
     platform. To increase throughput on an interconnect you have to adjust
     udp kernel parameters.
TCP: There is no need to set the message size for TCP.
RDG: The recommendations for RDG are documented in
        Oracle9i Administrator’s Reference – Part No. A97297-01
References
———-
Bug <2475236> RAC multiblock read performance issue using UDP IPC
11. How can you see which protocoll is being used by the instances ?

Answer
——
Please see the alert-file(s) of your RAC instances. During startup you’ll
   find a message in the alert-file that shows the protocoll being used.

      Wed Oct 30 05:28:55 2002
      cluster interconnect IPC version:Oracle UDP/IP with Sun RSM disabled
      IPC Vendor 1 proto 2 Version 1.0
12. Can the parameter CLUSTER_INTERCONNECT be changed dynamically during runtime ?

Answer
——
    No. Cluster_interconnects is a static parameter and can only be set in the
    spfile or pfile (init.ora)

约束条件对于查询优化的作用

约束条件对于查询优化至关重要。 许多人仅仅认识到约束是为了保证数据的完整性,当然这也是对的。
但约束同事也会被优化器利用以便决定最优执行计划。
优化器使用以下数据作为输入变量:
1. 查询语句
2. 所有可用的数据库对象统计值
3. 系统统计值,可能存在的如CPU速度,单块物理读的速度,以及一系列硬件指标
4. 数据库初始化参数 (parameters)
优化器使用所有这些信息以便决定最好的查询方式。我常常遇到人们在数据仓库或报表系统中避免使用约束。
“或许他们不需要约束以保持数据完整性,但他们确实需要约束以获取最优执行计划。数据仓库中糟糕的执行计划
可能执行数个小时乃至于数天。由于性能考量,数据仓库同样需要约束!
让我来看一些例子(使用11gr1,11.1.0.7)。第一个例子是分区排除,该特性自版本7.3时引入。
在代码演示1中,我们建立2个表包括互斥的数据以及一个合并(UNION ALL)它们的视图。
代码演示1: 建立表以及互斥数据以及试图
SQL> create table t1
2  as
3  select * from all_objects
4  where object_type in (‘TABLE’,’VIEW’);

SQL> alter table t1 modify object_type not null;

表已更改。
SQL> alter table t1 add constraint t1_check_otype
2  check (object_type in (‘TABLE’,’VIEW’));

表已更改。

SQL> create table t2
2  as
3  select * from all_objects
4  where object_type in (‘SYNONYM’,’PROCEDURE’);

SQL> alter table t2 modify object_type not null;

表已更改。

表已创建。

SQL> alter table t2 add constraint t2_check_obype
2  check (object_type in (‘SYNONYM’,’PROCEDURE’));

表已更改。
SQL> create or replace view v
2  as
3  select * from t1
4  union all
5  select * from t2;

视图已创建。

代码演示2中我们使用object_type列查询视图并观察其执行计划。

代码演示2:
SQL> select * from v where object_type = ‘TABLE’;

Execution Plan
—————————————————————————-
Plan hash value: 3982894595

—————————————————————————–
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
—————————————————————————–
|   0 | SELECT STATEMENT     |      |    40 |  6320 |   151   (1)| 00:00:02 |
|   1 |  VIEW                | V    |    40 |  6320 |         (1)| 00:00:02 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    TABLE ACCESS FULL | T1   |  3083 |   475K|    31   (0)| 00:00:01 |
|*  4 |    FILTER            |      |       |       |            |          |
|*  5 |     TABLE ACCESS FULL| T2   |     5 |   790 |   12    (1)| 00:00:02 |
—————————————————————————–

Predicate Information (identified by operation id):
——————————-

3 – filter(“OBJECT_TYPE”=’TABLE’)
4 – filter(NULL IS NOT NULL)
5 – filter(“OBJECT_TYPE”=’TABLE’)

在代码演示2中的执行计划似乎没有避免读取T2表,请注意T2表上的object_type仅包括SYNONYMS和PROCEDURES类型

。 但我们可以看到该读表操作的上层检查,即第四步是过滤操作,该过滤的方式是
NULL is NOT NULL
这十分有趣,我们并没有写过这样的句子,但优化器为我们添加了他。由于 NULL IS NOT NULL是恒假的,所以实际

上这系列操作(步骤4和5)其实永远不会发生。(你可以使用tkprof工具来确认这一点)

在下一个例子中,我们来体验一下为什么NOT NULL约束在涉及索引使用时特别重要。

SQL> create table t
2    as
3    select * from all_objects;
Table created.

SQL> create index t_idx on t(object_type);
Index created.

SQL> exec
dbms_stats.gather_table_stats( user, ‘T’ );
PL/SQL procedure successfully completed.

现在,我们尝试计算表中的行数,优化器仅有一种方案,如代码演示3
代码演示3:
SQL> set autotrace traceonly explain
SQL> select count(*) from t;

Execution Plan
—————————————-
Plan hash value: 2966233522

——————————————————————-
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
——————————————————————-
|   0 | SELECT STATEMENT   |      |     1 |   283   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T    | 68437 |   283   (1)| 00:00:04 |
——————————————————————-
因为object_type列是可为空的且索引不包含空键值(指索引相关的所有列皆为空的情况),我们无法利用索引计算表

上的行数,故不得不全表扫描。若我们告知数据库,OBJECT_TYPE列是非空的,执行计划将立即改变,如代码演示4


代码演示4:
SQL> alter table t modify object_type NOT NULL;
Table altered.

SQL> set autotrace traceonly explain
SQL> select count(*) from t;

Execution Plan
——————————————
Plan hash value: 1058879072

————————————————————————
| Id  | Operation             | Name  | Rows   | Cost (%CPU)| Time     |
————————————————————————
|   0 | SELECT STATEMENT      |       |     1  |    54   (2)| 00:00:01 |
|   1 |  SORT AGGREGATE       |       |     1  |            |          |
|   2 |   INDEX FAST FULL SCAN| T_IDX | 68437  |    54   (2)| 00:00:01 |
————————————————————————
在object_type实际允许为空的情况呢?我们能做些什么呢?若我们可以在索引中加上非空的键值呢?显然计划将改

变。 在这里我把常数0加入索引。
SQL> drop index t_idx;
Index dropped.

SQL> create index t_idx
on t (object_type, 0);
Index created.

现在代码演示6中的执行计划趋向于使用索引了

代码演示6:
SQL> select * from t where object_type is null;

Execution Plan
—————————–
Plan hash value: 470836197

————————————————————————————–
| Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————————–
|   0 | SELECT STATEMENT             |       |     1 |   101 |  1      (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T     |     1 |   101 |  1      (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN           | T_IDX |     1 |       |  1      (0)| 00:00:01 |
————————————————————————————–

Predicate Information (identified by operation id):
—————————————————
2 – access(“OBJECT_TYPE” IS NULL)

约束,主键,以及外键

现在我们来测试主键,外键是如何影响优化器的。我们复制scott.emp和scott.dept表使用

DBMS_STATS.SET_TABLE_STATS改变他们的统计量使得它们看起来“十分庞大”,让优化器产生这种假象。

代码演示7:

SQL> create table emp
2    as
3    select *
4    from scott.emp;
Table created.

SQL> create table dept
2    as
3    select *
4    from scott.dept;
Table created.

SQL> create or replace view emp_dept
2    as
3    select emp.ename, dept.dname
4      from emp, dept
5     where emp.deptno = dept.deptno;
View created.

SQL> begin
2       dbms_stats.set_table_stats
3           ( user, ‘EMP’, numrows=>1000000, numblks=>100000 );
4       dbms_stats.set_table_stats
5           ( user, ‘DEPT’, numrows=>100000, numblks=>10000 );
6    end;
7    /
PL/SQL procedure successfully completed.

我们同样使用一个view:emp_dept来返回以上2个表的连接查询结果,在该view仅返回emp表上数据时(ename列),我们

发现执行计划需要读取emp与dept两个表,如代码演示8。

代码演示8:
SQL> select ename from emp_dept;

Execution Plan
—————————–
Plan hash value: 615168685

—————————————————————————————-
| Id   | Operation          | Name  | Rows  |  Bytes |TempSpc | Cost (%CPU) | Time     |
—————————————————————————————-
|    0 | SELECT STATEMENT   |       |  1000K|     31M|        | 31515    (1)| 00:06:19 |
|*   1 |  HASH JOIN         |       |  1000K|     31M|   2448K| 31515    (1)| 00:06:19 |
|    2 |   TABLE ACCESS FULL| DEPT  |   100K|   1269K|        |  2716    (1)| 00:00:33 |
|    3 |   TABLE ACCESS FULL| EMP   |  1000K|     19M|        | 27151    (1)| 00:05:26 |
—————————————————————————————-

Predicate Information (identified by operation id):
—————————————————
1 – access(“EMP”.”DEPTNO”=”DEPT”.”DEPTNO”)

我们知道实际无需访问dept表,由于deptno是dept表的主键,而emp表中的dept实际是外键。现在我们加上这层约束

关系。
SQL> alter table dept add constraint
dept_pk primary key(deptno);
Table altered.

SQL> alter table emp add constraint
emp_fk_dept foreign key(deptno)
2    references dept(deptno);
Table altered.

试看代码演示9中的执行计划

代码演示9:
SQL> select ename from emp_dept;

Execution Plan
——————————
Plan hash value: 3956160932

————————————————————————–
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
————————————————————————–
|   0 | SELECT STATEMENT  |      | 50000 |   976K| 27152   (1)| 00:05:26 |
|*  1 |  TABLE ACCESS FULL| EMP  | 50000 |   976K| 27152   (1)| 00:05:26 |
————————————————————————–

Predicate Information (identified by operation id):
—————————————————
1 – filter(“EMP”.”DEPTNO” IS NOT NULL)

如上dept表在以上查询中无需再在考虑之内了,故也谈不上哈希连接了,多出了一个断言:deptno是非空的。
优化器意识到外键和逐渐的存在,以上查询实际等效于 SELECT ENAME FROM EMP WHERE DEPTNO IS NOT NULL。
优化器舍弃了不必要的表,获得了响应时间上的进步。
同时也证明了实际应用中不因该总是使用SELECT *以简化应用的实施。

NULL对于索引的影响:

Indexes and NULLs

Applies to:

Oracle Server – Enterprise Edition – Version: 9.2.0.8 to 10.2.0.4 – Release: 9.2 to 10.2
Information in this document applies to any platform.

Purpose

This article illustrates some common reasons why indexes are not selected when NULLs are present.

Scope and Application

This is a basic level overview with examples of index usage.

Indexes and NULLs

Indexes and NULLs

When dealing with indexes, a common mistake is to forget about NULLs. Indexes do not store NULL values and so indexes on NULLable columns can’t be used to drive queries unless there is something that eliminates the NULL values from the query.

To illustrate this are a number of examples based upon the following table/indexes:

drop table nulltest; create table nulltest ( col1 number, col2 number, col3 number not null, col4 number not null); create index nullind1 on nulltest (col1); create index notnullind3 on nulltest (col3); begin for i in 1..10000 loop insert into nulltest values (i,i,i,i); if i mod 1000 = 0 then commit; end if; end loop; end; / analyze table nulltest compute statistics;

Illustrative Queries:

select col1 from nulltest t; select /*+ index(t nullind1) */ col1 from nulltest t; select /*+ index(t) */ col1 from nulltest t; select /*+ index(t notnullind3) */ col1 from nulltest t; select /*+ index(t notnullind3) */ col3 from nulltest t; select /*+ index(t nullind1) */ col1 from nulltest t where col1 between 0 and 20000; select col1 from nulltest t where col1 is not null;

Queries and Explanations:

SQL> select col1 from nulltest t; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=10000 Bytes=30000) 1 0 TABLE ACCESS (FULL) OF 'NULLTEST' (Cost=6 Card=10000 Bytes=30000)

col1 is NULLable so the index cannot be used with no predicate
SQL> select /*+ index(t nullind1) */ col1 from nulltest t; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=10000 Bytes=30000) 1 0 TABLE ACCESS (FULL) OF 'NULLTEST' (Cost=6 Card=10000 Bytes=30000)
hinting the index on col1 (nullind1) makes no difference since col1 is NULLable
SQL> select /*+ index(t) */ col1 from nulltest t; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=10000 Bytes=30000) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'NULLTEST' (Cost=49 Card=10000 Bytes=30000) 2 1 INDEX (FULL SCAN) OF 'NOTNULLIND3' (NON-UNIQUE) (Cost=20 Card=10000)

An open index hint on the table allows the selection of the index on the NOT NULL column (col3). Notice that the col3 predicate is not included anywhere in the query. In order for col1 to be retrieved, the table has to be accessed.

SQL> select /*+ index(t notnullind3) */ col1 from nulltest t; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=49 Card=10000 Bytes=30000) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'NULLTEST' (Cost=49 Card=10000 Bytes=30000) 2 1 INDEX (FULL SCAN) OF 'NOTNULLIND3' (NON-UNIQUE) (Cost=20 Card=10000)
hinting notnullind3 directly works as well
SQL> select /*+ index(t notnullind3) */ col3 from nulltest t; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=10000 Bytes=30000) 1 0 INDEX (FULL SCAN) OF 'NOTNULLIND3' (NON-UNIQUE) (Cost=20 Card=10000 Bytes=30000)
Selecting the NOT NULL column (col3) works fine and uses the index with no table access.
SQL> select /*+ index(t nullind1) */ col1 from nulltest t where col1 between 0 and 20000; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=20 Card=10000 Bytes=30000) 1 0 INDEX (RANGE SCAN) OF 'NULLIND1' (NON-UNIQUE) (Cost=20 Card=10000 Bytes=30000)
The effect of the predicate against col1 is to eliminate nulls from the data returned from the column. This allows the index to be used.
SQL> select col1 from nulltest t 2 where col1 is not null; Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=10000 Bytes=30000) 1 0 INDEX (FAST FULL SCAN) OF 'NULLIND1' (NON-UNIQUE) (Cost=5 Card=10000 Bytes=30000)
This example illustrates that forcing the column to return only NOT NULL values allows the index to be used.

Note that in the previous example, the Index hint prevents an index fast full scan operation from being selected. An INDEX_FFS hint must be supplied to force an index fast full scan.

沪ICP备14014813号

沪公网安备 31010802001379号