DEMO HRMS 9.0 : ORA-00600

If you are installing, working, with Peoplesoft HRMS9.0, Peopletools 8.49 on Oracle database 10gR2 (currently on 10.2.0.4), you could easily hit an ORA-600 error.

Go to the front end application, menu :

"Manager Self Service / Job and Personal Information / Request Job Change"

You'll receive the following pop-up error message :

And the alert.log of the database :

[ora102@orion:/apps/oracle/admin/DMOHRMS9/bdump]DMOHRMS9$ tail -f alert_DMOHRMS9.log

Starting background process QMNC

QMNC started with pid=13, OS id=6767

Sun Feb 8 17:38:24 2009

Completed: ALTER DATABASE OPEN

Sun Feb 8 17:38:32 2009

Starting background process CJQ0

CJQ0 started with pid=19, OS id=6830

Sun Feb 8 17:45:08 2009

Errors in file /apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc:ORA-00600: internal error code, arguments: [kkqtcpcky:ficand], [], [], [], [], [], [], []

The content of the file /apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc :
[ora102@orion:/apps/oracle/admin/DMOHRMS9/bdump]DMOHRMS9$ more /apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc
/apps/oracle/admin/DMOHRMS9/udump/dmohrms9_ora_6865.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /apps/oracle/10.2.0
System name: Linux
Node name: orion.phoenix-nga
Release: 2.6.9-67.0.0.0.1.ELsmp
Version: #1 SMP Sun Nov 18 00:23:42 EST 2007
Machine: i686
Instance name: DMOHRMS9
Redo thread mounted by this instance: 1
Oracle process number: 22
Unix process pid: 6865, image: oracle@orion.phoenix-nga
*** ACTION NAME:() 2009-02-08 17:45:08.957
*** MODULE NAME:(PSAPPSRV@orion.phoenix-nga (TNS V1-V3)) 2009-02-08 17:45:08.957 *** SERVICE NAME:(DMOHRMS9) 2009-02-08 17:45:08.957
*** SESSION ID:(145.39) 2009-02-08 17:45:08.957
*** 2009-02-08 17:45:08.956
ksedmp: internal or fatal error
ORA-00600: internal error code, arguments: [kkqtcpcky:ficand], [], [], [], [], [], [], []
Current SQL statement for this session:
SELECT ' ' ,C.MANAGER_ID ,COUNT(*) FROM PS_JOB A , PS_DEPT_TBL C WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W') AND A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2
.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:1,'YYYY-MM-DD')) AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_R CD AND A.EFFDT=A3.EFFDT ) AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 WHERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTI D AND C2.EFFDT<=TO_DATE(:2,'YYYY-MM-DD')) AND C.MANAGER_ID IN(SELECT A.EMPLID FROM PS_JOB A , PS_DEPT_TBL C WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W') AND A.EFFDT= ( SELECT MAX (A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:3,'YYYY-MM-DD')) AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 W HERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTID AND C2.EFFDT<=TO_DATE(:4,'YYYY-MM-DD')) AND C.MANAGER_ID=:5) GROUP BY C.MANAGER_ID

----- Call Stack Trace -----

[...]

If we are running the same query onto the database within SQL*Plus, same error, even to get the explain plan :
SQL> explain plan for
2 SELECT ' ' ,C.MANAGER_ID ,COUNT(*)
3 FROM PS_JOB A , PS_DEPT_TBL C
4 WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W')
5 AND A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:1,'YYYY-MM-DD')) 6 AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) 7 AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 WHERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTID AND C2.EFFDT<=TO_DATE(:2,'YYYY-MM-DD')) AND C.MANAGER_ID IN(SELECT A.EMPLID FROM PS_JOB A , PS_DEPT_TBL C WHERE A.EMPL_STATUS IN ('A', 'L', 'P', 'W') AND A.EFFDT= ( SELECT MAX(A2.EFFDT) FROM PS_JOB A2 WHERE A.EMPLID=A2.EMPLID AND A.EMPL_RCD=A2.EMPL_RCD AND A2.EFFDT<=TO_DATE(:3,'YYYY-MM-DD')) 8 9 10 11 12 AND A.EFFSEQ= ( SELECT MAX(A3.EFFSEQ) FROM PS_JOB A3 WHERE A.EMPLID=A3.EMPLID AND A.EMPL_RCD=A3.EMPL_RCD AND A.EFFDT=A3.EFFDT ) AND C.SETID = A.SETID_DEPT AND C.DEPTID = A.DEPTID AND C.EFFDT= ( SELECT MAX(C2.EFFDT) FROM PS_DEPT_TBL C2 WHERE C.SETID=C2.SETID AND C.DEPTID=C2.DEPTID AND C2.EFFDT<=TO_DATE(:4,'YYYY-MM-DD')) AND C.MANAGER_ID=:5) GROUP BY C.MANAGER_ID 13 14 15 16 17 18 ;

FROM PS_JOB A , PS_DEPT_TBL C

* ERROR at line 3: ORA-00600: internal error code, arguments: [kkqtcpcky:ficand], [], [], [], [], [], [], []

Some of the indexes of the two tables involved in this query are created in descending order.

The descending indexes have been introduced with the Peopletools 8.48 onwards for some (for me, mysterious) reasons, let's say surely to improve the performances.

But, I remember some months ago a benchmark I did on CRM9 (Peopletools 8.48 - Oracle database 10.2.0.3), the indexes in descending order cause a lot of performances issues. I had to use an hidden parameter to deactivate the descending indexes to have better performance.

/* For reminder, it is not only more than a huge work to recreate all the indexes without that DESCending clause, but that make the Peoplesoft application customized, which is not so good for the support, that's why the hidden parameter was a good workaround to keep the Peoplesoft objects closer to the standard. */

Here again, the descending indexes causes the trouble.

As you could have seen above, it is not performance issue, but transactional error and a database ORA-600.

It seems to hit an "old" Oracle bug (from 8.1.7.x to 9.2.x, bug ref #869177) for which the workaround was the setting of "_IGNORE_DESC_IN_INDEX" to TRUE (FALSE by default).

So, it seems we are still under the same effect, my database is 10.2.0.4. So, let's change the setting :

SQL> conn / as sysdba
Connected.
SQL> alter system set "_ignore_desc_in_index" = true scope=both;

System altered.

SQL>

Now, it is not enough, the indexes have to be rebuilt. Rebuild in the Peoplesoft meaning, DROP + CREATE.

So, open the Application Designer, connect within PS user.

Insert into the project the two records JOB and DEPT_TBL.

Then build the project, check only "Create Index", go to the settings, and be sure you are checking the "Recreate index if it already exists", then build the project, as show below :

Then, run the generated SQL script, that won't modify the record (the tables), just drop and recreate the indexes. Oracle will ignore the DESC option embedded in the commands lines of the indexes creation because of the setting of that hidden parameter "_IGNORE_DESC_IN_INDEX".

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @rebuild_indexes

Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index dropped.


Index created.


Index altered.
[...]

Finally, go back to the front end application, and retry the previous failing transaction, from now that'll work fine :

Problem solved.

However, here above was only an example on two records (tables), but you could have to rebuild all the indexes for all the records. A lot of time is required for that.

Conclusion : before creating, especially before loading the Peoplesoft objects into the database (before the first datamover load of the database setup), it could be a good idea to set this hidden parameter "_IGNORE_DESC_IN_INDEX" to TRUE.

Caution : from now, you have a difference between the Peoplesoft datamodel (with the DESCending order in the indexes description) and the Oracle dictionary (without the DESCending order), that means every time you'll rebuild the records, the Application Designer will try to rebuild the indexes, that is not the case when objects are same on both sides. This is one of the biggest problem of this solution.

Caution 2 : playing around hidden parameter is supposed to be validated by your Oracle support.

Going further : after raising a SR to the Oracle support, it seems this bug is solved on Oracle 11.1, and a fix will be included in the coming 10.2.0.5 patchset.

Wait and see, or manage an upgrade to 11.1 (after testing, I confirm, it is working fine on 11.1.0.7).

Enjoy,