How to Resolve ORA-00600: [kkoarl1]

Here you go again with ORA-00600: internal error code, arguments: [kkoarl1], [90139], [46226], [], [], [], [], [] error in our production database followed by "Memory Notification: Library Cache Object loaded into SGA" warning message. Luckily this error is not serious and we have a workaround also for ORA-00600 error and warning message.

As per Oracle one can see this error with very large and complex query. In our case we got this error from very complex and very large Discoverer report.

ORA-00600: internal error code, arguments: [kkoarl1], [90139], [46226], [], [], [], [], []Current SQL statement for this session:SELECT CASE WHEN ( SUBSTR(o102468.CHARGE_ACCOUNT_NUM,10,6) ) = '500000' THEN '220270' WHEN ( SUBSTR(o102468.CHARGE_ACCOUNT_NUM,10,6) ) = '500001' THEN '220270' WHEN ( SUBSTR(o102468.CHARGE_ACCOUNT_NUM,10,6) ) = '505000' THEN '220250' WHEN ( SUBSTR(o102468.CHARGE_ACCOUNT_NUM,10,6) ) = '505001' THEN '220250' WHEN ( SUBSTR ----------------
----------------
----------------
FROM POFG_PURCHASE_ORDERS o102467,en') as C_2,NVL(o102467.AUTHORIZATION_STATUS,'Incomplete') as C_1,ROUND(o102468.ORDERED_Q POFG_PO_DISTRIBUTIONS o102468,_5,ROUND(o102468.DELIVERED_QUANTITY,2)*o102470.ACTUAL_PRICE as C_6,ROUND(o102468.BILLED_ POFG_PO_LINES o102469,RICE as C_7
-------------------
-------------------
-------------------

This ORA-00600 error can be resolved by using "_optimizer_cost_based_transformation" parameter and set it false.

"_optimizer_cost_based_transformation"=false

During 11i upgrade also we had many standard forms failing with different errors and we end up setting this parameter to false in 11i e-Business suite. Although some experts says not to change this parameter because of performance issue and work on the the sql statment but we didn't have any performance issue after making changes to this parameter.

Memory Notification: Library Cache Object loaded into SGA

Memory Notification: Library Cache Object loaded into SGA
Heap size 109275K exceeds notification threshold (51200K)Details in trace file ORCLTEST_ora_19128.trc
KGL object name :EXPLAIN PLAN SET STATEMENT_ID = '117984602' INTO EUL4_US.EUL5_PLAN_TABLE FORSELECT CASE WHEN ( SUBSTR(o102468.CHARGE_ACCOUNT_NUM,10,6) ) IN ('785500','803500','810000','810100','810500','810510','810520','810530',
'810540','810550','810570','811010','823250','823500','823501','830500',
'831000','831100','831300','831400','840000','840001','840002','840003',
'840050','840070','840100','840120','840150','840200','840250','840300',
'840350','840400','840450','840500','840550','840600','840650','840700',
-----------
-----------

This is a simple warning message for KGL heap size warning threshold as some process is waiting for a long time in finding free memory extents. This warning message can be supress by using _kgl_large_heap_warning_threshold (hidden parameter) and set it to relatively high value in bytes, default value of this parameter is 50M in 10.2.0.4 .0. You may also try setting this parameter to zero to supress this warning.

How to resolve ORA-00600: [qertbFetchByRowID]

In this post i am going to discuss how to resolve ORA-00600: internal error code, arguments: [qertbFetchByRowID]
ORA-00600: internal error code, arguments: [qertbFetchByRowID] error represents that an index and a table entries are out of sync or underlying index is corrupt.

In this case just check the trace file for current SQL statement for that session. Once we get the SQL statement and table name from the session trace file we can easily find out the index name as shown in this example.

Try to validate the table structure using command.

SQL> analyze table gl.gl_interface validate structure cascade;
analyze table gl.gl_interface validate structure cascade
*
ERROR at line 1:
ORA-01499: table/index cross reference failure - see trace file

Check the trace file create by above statement in udump folder and search for following text

Table/Index row count mismatch
table 48068 : index 239744, 191029
Index root = tsn: 4 rdba: 0x0d00438b

In above example rdba value is in HEXADECIMAL, Convert rdba: 0x0d00438b to decimal value like 0x0d00438b is equivalent to 218121099.

Now use statement given below to find out Header_file and Header_block.

SQL> select dbms_utility.data_block_address_file(218121099) "Rfile#"
,dbms_utility.data_block_address_block(218121099) "Block#"
from dual;

Rfile# Block#
---------- ----------
52 17291

Once you get the Header_file and Header_Block use the SQL statement to find out the segment name and segment type.

SQL> select owner, segment_name, segment_type
from dba_segments
where header_file = 52
and header_block = 17291;

OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------------------------------------------
GL GL_INTERFACE_N1 INDEX

Once you get the index name, you can rebuild index or drop it and recreate it.

SQL> drop index GL.GL_INTERFACE_N1;

CREATE INDEX GL.GL_INTERFACE_N1 ON GL.GL_INTERFACE
(USER_JE_SOURCE_NAME, SET_OF_BOOKS_ID, GROUP_ID)
NOLOGGING
TABLESPACE APPS_TS_INTERFACE
PCTFREE 0
INITRANS 11
MAXTRANS 255
STORAGE (
INITIAL 128K
NEXT 128K
MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0
BUFFER_POOL DEFAULT
)
NOPARALLEL;

SQL> analyze table gl.gl_interface validate structure cascade;

Table analyzed.

Now you wouldn't get the ORA-00600 error anymore.