Few weeks ago, I wrote about an ORA-600 on HRMS9.0 hereToday, it is a little bit different, it is also an ORA-600, but on a CRM database and not in a transactional mode.The query is coming from development team, when doing customized code, create the following hierarchical query :SELECT p.emplid
FROM ps_rb_worker w , ps_rd_person p WHERE p.person_id = w.person_idSTART WITH p.emplid = '000001' CONNECT BY PRIOR w.person_id = w.supervisor_id ;The database is 10.2.0.4, OS does not matter :SQL> SELECT p.emplid
FROM ps_rb_worker w , ps_rd_person pWHERE p.person_id = w.person_idSTART WITH p.emplid = '000001'CONNECT BY PRIOR w.person_id = w.supervisor_id ;CONNECT BY PRIOR w.person_id = w.supervisor_id *ERROR at line 5:ORA-00600: internal error code, arguments: [qkacon:FJswrwo], [10], [], [], [],[], [], []Well, after a quick look on Metalink, found a workaround with a HINT :SQL> SELECT /*+ NO_CONNECT_BY_COST_BASED */ p.emplid
FROM ps_rb_worker w , ps_rd_person pWHERE p.person_id = w.person_idSTART WITH p.emplid = '000001'CONNECT BY PRIOR w.person_id = w.supervisor_id ;EMPLID-----------000001That's fine, a result is returned, but what about the explain plan ?SQL> explain plan for
SELECT /*+ NO_CONNECT_BY_COST_BASED */ p.emplidFROM ps_rb_worker w , ps_rd_person pWHERE p.person_id = w.person_idSTART WITH p.emplid = '000001'CONNECT BY PRIOR w.person_id = w.supervisor_id ;Explained.SQL> select * from table(dbms_xplan.display);Plan hash value: 136118842---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 66 | 4092 | 37 (6)| 00:00:01 ||* 1 | CONNECT BY WITH FILTERING| | | | | ||* 2 | FILTER | | | | | || 3 | COUNT | | | | | ||* 4 | HASH JOIN | | 66 | 4092 | 37 (6)| 00:00:01 || 5 | NESTED LOOPS | | 66 | 3234 | 31 (4)| 00:00:01 ||* 6 | HASH JOIN | | 66 | 2772 | 31 (4)| 00:00:01 ||* 7 | TABLE ACCESS FULL | PS_RD_WRKR_ASGN | 329 | 5922 | 15 (0)| 00:00:01 ||* 8 | TABLE ACCESS FULL | PS_RD_WRKR_JOB | 1193 | 28632 | 15 (0)| 00:00:01 ||* 9 | INDEX UNIQUE SCAN | PS_RD_PERSON | 1 | 7 | 0 (0)| 00:00:01 || 10 | INDEX FAST FULL SCAN | PSBRD_PERSON | 1807 | 23491 | 5 (0)| 00:00:01 ||* 11 | HASH JOIN | | | | | || 12 | CONNECT BY PUMP | | | | | || 13 | COUNT | | | | | ||* 14 | HASH JOIN | | 66 | 4092 | 37 (6)| 00:00:01 || 15 | NESTED LOOPS | | 66 | 3234 | 31 (4)| 00:00:01 ||* 16 | HASH JOIN | | 66 | 2772 | 31 (4)| 00:00:01 ||* 17 | TABLE ACCESS FULL | PS_RD_WRKR_ASGN | 329 | 5922 | 15 (0)| 00:00:01 ||* 18 | TABLE ACCESS FULL | PS_RD_WRKR_JOB | 1193 | 28632 | 15 (0)| 00:00:01 ||* 19 | INDEX UNIQUE SCAN | PS_RD_PERSON | 1 | 7 | 0 (0)| 00:00:01 || 20 | INDEX FAST FULL SCAN | PSBRD_PERSON | 1807 | 23491 | 5 (0)| 00:00:01 |---------------------------------------------------------------------------------------------If we got a resultm, performance are really bad. The explain plan looks not good at all, especially the index fast full scan on PSBRD_PERSON (table PS_RD_PERSON) , and it is doing this twice !I started the current article with a link to an other ORA-600 reported on HRMS9.0 within Peopletools 8.49. My CRM9.0 is also on Peopletools 8.49, and one common point is the (in)famous DESCending indexes from Peopletools 8.48....So, let's have a look on that side, is there DESC indexes on the involved tables ?SQL> select distinct index_name,table_name
2 from user_ind_columns 3 where table_name in ('PS_RD_WRKR_ASGN','PS_RD_WRKR_JOB','PS_RD_PERSON') 4* and descend = 'DESC'PS_RD_WRKR_JOB PS_RD_WRKR_JOBPS1RD_WRKR_JOB PS_RD_WRKR_JOBPS3RD_WRKR_JOB PS_RD_WRKR_JOBPS2RD_WRKR_JOB PS_RD_WRKR_JOBPS0RD_WRKR_JOB PS_RD_WRKR_JOBPS4RD_WRKR_JOB PS_RD_WRKR_JOBLet's rebuild them by ignoring them :SQL> conn / as sysdba
Connected.SQL> alter system set "_ignore_desc_in_index" = true scope=memory;System altered.SQL> conn sysadm/sysadmConnected.SQL> declare v_stmt long; begin for i in (select distinct index_name,table_name from user_ind_columns where table_name in ('PS_RD_WRKR_ASGN','PS_RD_WRKR_JOB','PS_RD_PERSON') and descend = 'DESC') loop select dbms_metadata.get_ddl('INDEX',i.index_name) into v_stmt from dual; execute immediate 'drop index '||i.index_name; execute immediate v_stmt; end loop;end;/PL/SQL procedure successfully completed.SQL> select distinct index_name,table_namefrom user_ind_columnswhere table_name in ('PS_RD_WRKR_ASGN','PS_RD_WRKR_JOB','PS_RD_PERSON')and descend = 'DESC';no rows selectedAnd now, the explain plan :SQL> explain plan for
SELECT p.emplidFROM ps_rb_worker w , ps_rd_person pWHERE p.person_id = w.person_idSTART WITH p.emplid = '000001'CONNECT BY PRIOR w.person_id = w.supervisor_id ;Explained.SQL> select * from table(dbms_xplan.display); Plan hash value: 2569705422--------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 62 | 32 (4)| 00:00:01 ||* 1 | CONNECT BY WITH FILTERING | | | | | || 2 | NESTED LOOPS | | 1 | 92 | 20 (5)| 00:00:01 || 3 | NESTED LOOPS | | 1 | 85 | 20 (5)| 00:00:01 ||* 4 | HASH JOIN | | 2 | 114 | 18 (6)| 00:00:01 ||* 5 | INDEX RANGE SCAN | PSBRD_PERSON | 2 | 38 | 2 (0)| 00:00:01 ||* 6 | TABLE ACCESS FULL | PS_RD_WRKR_JOB | 1193 | 45334 | 15 (0)| 00:00:01 ||* 7 | TABLE ACCESS BY INDEX ROWID| PS_RD_WRKR_ASGN | 1 | 28 | 1 (0)| 00:00:01 ||* 8 | INDEX UNIQUE SCAN | PS_RD_WRKR_ASGN | 1 | | 0 (0)| 00:00:01 ||* 9 | INDEX UNIQUE SCAN | PS_RD_PERSON | 1 | 7 | 0 (0)| 00:00:01 || 10 | NESTED LOOPS | | 1 | 62 | 32 (4)| 00:00:01 || 11 | NESTED LOOPS | | 1 | 49 | 31 (4)| 00:00:01 ||* 12 | HASH JOIN | | 1 | 42 | 31 (4)| 00:00:01 ||* 13 | HASH JOIN | | | | | || 14 | CONNECT BY PUMP | | | | | ||* 15 | TABLE ACCESS FULL | PS_RD_WRKR_JOB | 23 | 552 | 15 (0)| 00:00:01 ||* 16 | TABLE ACCESS FULL | PS_RD_WRKR_ASGN | 329 | 5922 | 15 (0)| 00:00:01 ||* 17 | INDEX UNIQUE SCAN | PS_RD_PERSON | 1 | 7 | 0 (0)| 00:00:01 || 18 | TABLE ACCESS BY INDEX ROWID | PS_RD_PERSON | 1 | 13 | 1 (0)| 00:00:01 ||* 19 | INDEX UNIQUE SCAN | PS_RD_PERSON | 1 | | 0 (0)| 00:00:01 |--------------------------------------------------------------------------------------------------Now, with unique scan index are doing against PS_RD_PERSON the query is running much much faster.If a conclusion was needed, once more, on Peopletools 8.48 and above, rebuild the indexes with these DESC indexes is definately a good idea.Enjoy,