Today, 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_id
START 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 p
WHERE p.person_id = w.person_id
START 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 p
WHERE p.person_id = w.person_id
START WITH p.emplid = '000001'
CONNECT BY PRIOR w.person_id = w.supervisor_id ;
EMPLID
-----------
000001
That's fine, a result is returned, but what about the explain plan ?
SQL> explain plan for
SELECT /*+ NO_CONNECT_BY_COST_BASED */ p.emplid
FROM ps_rb_worker w , ps_rd_person p
WHERE p.person_id = w.person_id
START 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_JOB
PS1RD_WRKR_JOB PS_RD_WRKR_JOB
PS3RD_WRKR_JOB PS_RD_WRKR_JOB
PS2RD_WRKR_JOB PS_RD_WRKR_JOB
PS0RD_WRKR_JOB PS_RD_WRKR_JOB
PS4RD_WRKR_JOB PS_RD_WRKR_JOB
Let'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/sysadm
Connected.
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_name
from user_ind_columns
where table_name in ('PS_RD_WRKR_ASGN','PS_RD_WRKR_JOB','PS_RD_PERSON')
and descend = 'DESC';
no rows selected
And now, the explain plan :
SQL> explain plan for
SELECT p.emplid
FROM ps_rb_worker w , ps_rd_person p
WHERE p.person_id = w.person_id
START 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,
3 comments:
Thanks for this very useful tip. I got a question though, after rebuilding the stats don't we need to regather the stats. If it were a small table there may not be much difference but if you are working with PS_NAMES etc it could become quite large depending on the setup!
Nicolas,
Are there any changes to the Integration Broker in PT8.5 from PT8.49
Thanks
I could get away with this problem by setting _optimizer_connect_by_cost_based in the init.ora to false.
alter system set "_optimizer_connect_by_cost_based" = false;
Post a Comment