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.