How to Resolve ORA-00600: [sorsikbeg_1]

Now we got another ORA-00600 Error in our production database and this time error was ORA-00600: internal error code, arguments: [sorsikbeg_1], [5], [0], [], [], [], []

This error is generally caused by "CONNECT BY PRIOR" in SQL statement. In our trace file we could see the following connect by query:

SELECT COUNT(DISTINCT PAAF.PERSON_ID)
FROM PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERSON_TYPES PPT,
PER_PERSON_TYPE_USAGES_F PPTU,
PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE 1 = 1 AND PAPF.PERSON_ID = PPTU.PERSON_ID
AND PPTU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE = 'EMP'
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID AND
PAST.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
AND PAAF.ASSIGNMENT_TYPE = 'E'
CONNECT BY PRIOR PAAF.PERSON_ID = PAAF.SUPERVISOR_ID
AND TRUNC(SYSDATE)
BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE AND
TRUNC(SYSDATE) BETWEEN PPTU.EFFECTIVE_START_DATE AND PPTU.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND
PAAF.EFFECTIVE_END_DATE START WITH 1 = 1 AND PAAF.PERSON_ID = :b1;

In order to resolve this error we temporary used following work around (Oracle Hint) but be careful as this hint can have serious performance issue.

SELECT /*+ NO_CONNECT_BY_FILTERING*/ COUNT(DISTINCT PAAF.PERSON_ID) FROM
PER_ALL_PEOPLE_F PAPF,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERSON_TYPES PPT, PER_PERSON_TYPE_USAGES_F PPTU, PER_ASSIGNMENT_STATUS_TYPES PAST
WHERE 1 = 1
AND PAPF.PERSON_ID =PPTU.PERSON_ID
AND PPTU.PERSON_TYPE_ID = PPT.PERSON_TYPE_ID
AND PPT.SYSTEM_PERSON_TYPE = 'EMP'
AND PAPF.PERSON_ID = PAAF.PERSON_ID
AND PAAF.ASSIGNMENT_STATUS_TYPE_ID = PAST.ASSIGNMENT_STATUS_TYPE_ID
AND PAST.PER_SYSTEM_STATUS = 'ACTIVE_ASSIGN'
AND PAAF.ASSIGNMENT_TYPE = 'E'
CONNECT BY PRIOR
PAAF.PERSON_ID = PAAF.SUPERVISOR_ID
AND TRUNC(SYSDATE)BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE ANDTRUNC(SYSDATE) BETWEEN PPTU.EFFECTIVE_START_DATE AND PPTU.EFFECTIVE_END_DATEAND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE ANDPAAF.EFFECTIVE_END_DATE START WITH 1 = 1
AND PAAF.PERSON_ID = :b1;

another way to resolve this is to set the parameter "_optimizer_connect_by_cost_based" to false in the session itself using
alter session set "_optimizer_connect_by_cost_based"=FALSE by default value of this hidden parameter is TRUE, but one should be careful with hidden parameters and should always get blessing from oracle before using them otherwise you might get unpredictable results.