Few weeks ago, I opened two surveys :
1. What OS your Peoplesoft appl is running on ?
2. What DB your Peoplesoft appl is running on ?
They are just by curiosity, to know a little bit more about the differents configuration of Peoplesoft customer around the world. Only 7 days remain...
Thanks in advance for your collaboration.
Sunday, May 24, 2009
DEMO CRM 9.0 : ORA-00600
Few weeks ago, I wrote about an ORA-600 on HRMS9.0 here
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 :
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 :
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 :
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 ?
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 ?
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 :
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 :
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,
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,
Saturday, May 09, 2009
About "_unnest_subquery"
Peoplesoft give some recommandation regarding the Oracle init parameters. These recommandations include the hidden parameter "_unnest_subquery" to be set to false (true by default), even for the most recent version.
It is surprising me, because this recommandation exists for several years now, and on older database as well.
Ok, let's see what if we don't follow this specific recommandation.
On 10.2.0.4 database, we have the following query running for hours :
UPDATE PS_AB_PDI_CN_TAO
SET country_2char = COALESCE(( SELECT q.country_2char
FROM ( SELECT o.oprid
, j.emplid
, j.empl_rcd
, j.effdt
, j.effseq
, c.country_2char
FROM ps_job j
, psoprdefn o
, ps_opr_def_tbl_Hr h
, ps_location_Tbl l
, ps_country_tbl c
WHERE EXISTS ( SELECT 'x'
FROM ps_job j2
WHERE j.emplid = j2.emplid
AND j2.empl_Rcd = 1)
AND j.emplid = o.emplid
AND o.oprclass = h.oprclass
AND j.effdt = ( SELECT MAX(j3.EFFDT)
FROM PS_JOB j3
WHERE j3.EMPLID = j.EMPLID
AND j3.EMPL_RCD = j.EMPL_RCD
AND j3.EFFDT <= sysdate)
AND j.effseq = ( SELECT MAX(j4.EFFSEQ)
FROM PS_JOB j4
WHERE j4.EMPLID = j.EMPLID
AND j4.EMPL_RCD = j.EMPL_RCD
AND j4.EFFDT = j.effdt)
AND l.setid = j.setid_location
AND l.location = j.location
AND h.country = l.country
AND l.effdt = ( SELECT MAX(l2.effdt)
FROM ps_location_tbl l2
WHERE l.setid = l2.setid
AND l.location = l2.location
AND l2.effdt <= j.effdt)
AND c.country = l.country ) q
WHERE Q.EFFDT = ( SELECT MAX(EFFDT)
FROM PS_JOB Q2
WHERE Q.EMPLID = Q2.EMPLID
AND Q2.EFFDT <= SYSDATE
AND Q.EFFDT <= SYSDATE)
AND Q.EFFSEQ = ( SELECT MAX(J3.EFFSEQ)
FROM PS_JOB J3
WHERE Q.EMPLID = J3.EMPLID
AND Q.EMPL_RCD = J3.EMPL_RCD
AND Q.EFFDT = J3.EFFDT)
AND Q.OPRID = PS_AB_PDI_CN_TAO.OPRID)
,' ')
WHERE COUNTRY_2CHAR = ' ';
The explain plan is the following :
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 41882 | 3599K| 18 (0)| 00:00:01 |
| 1 | UPDATE | PS_AB_PDI_CN_TAO | | | | |
|* 2 | TABLE ACCESS FULL | PS_AB_PDI_CN_TAO | 41882 | 3599K| 18 (0)| 00:00:01 |
| 3 | HASH UNIQUE | | 1 | 137 | 26 (12)| 00:00:01 |
|* 4 | HASH JOIN | | 1 | 137 | 15 (14)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 117 | 8 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 110 | 7 (0)| 00:00:01 |
| 7 | NESTED LOOPS SEMI | | 1 | 85 | 5 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 74 | 4 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | PSOPRDEFN | 1 | 27 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PS_PSOPRDEFN | 1 | | 0 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | PS_OPR_DEF_TBL_HR | 33 | 396 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PS_OPR_DEF_TBL_HR | 1 | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | PS_JOB | 1 | 35 | 2 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | PSAJOB | 1 | | 1 (0)| 00:00:01 |
| 16 | SORT AGGREGATE | | 1 | 16 | | |
|* 17 | FILTER | | | | | |
|* 18 | INDEX RANGE SCAN | PSAJOB | 2 | 32 | 2 (0)| 00:00:01 |
| 19 | SORT AGGREGATE | | 1 | 22 | | |
| 20 | FIRST ROW | | 1 | 22 | 2 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN (MIN/MAX) | PSAJOB | 1 | 22 | 2 (0)| 00:00:01 |
| 22 | SORT AGGREGATE | | 1 | 22 | | |
| 23 | FIRST ROW | | 1 | 22 | 2 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN (MIN/MAX)| PSAJOB | 1 | 22 | 2 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | PSAJOB | 8 | 88 | 1 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID | PS_LOCATION_TBL | 1 | 25 | 2 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | PS_LOCATION_TBL | 1 | | 1 (0)| 00:00:01 |
| 28 | SORT AGGREGATE | | 1 | 21 | | |
|* 29 | INDEX RANGE SCAN | PS_LOCATION_TBL | 1 | 21 | 2 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | PS_COUNTRY_TBL | 1 | 7 | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | PS_COUNTRY_TBL | 1 | | 0 (0)| 00:00:01 |
| 32 | VIEW | VW_SQ_1 | 3743 | 74860 | 6 (17)| 00:00:01 |
| 33 | SORT GROUP BY | | 3743 | 109K| 6 (17)| 00:00:01 |
|* 34 | INDEX FAST FULL SCAN | PS_JOB | 3743 | 109K| 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Well, the last three lines are not as I would expect.
Let's try this hidden parameter :
"_unnest_subquery"=false (true by default)
Finally the explain plan change a lot and looks much better.
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 41882 | 3599K| 18 (0)| 00:00:01 |
| 1 | UPDATE | PS_AB_PDI_CN_TAO | | | | |
|* 2 | TABLE ACCESS FULL | PS_AB_PDI_CN_TAO | 41882 | 3599K| 18 (0)| 00:00:01 |
| 3 | HASH UNIQUE | | 1 | 117 | 21 (5)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 117 | 8 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 110 | 7 (0)| 00:00:01 |
| 6 | NESTED LOOPS SEMI | | 1 | 85 | 5 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 74 | 4 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | PSOPRDEFN | 1 | 27 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PS_PSOPRDEFN | 1 | | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | PS_OPR_DEF_TBL_HR | 33 | 396 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PS_OPR_DEF_TBL_HR | 1 | | 0 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | PS_JOB | 1 | 35 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | PSAJOB | 1 | | 1 (0)| 00:00:01 |
| 15 | SORT AGGREGATE | | 1 | 16 | | |
|* 16 | FILTER | | | | | |
|* 17 | INDEX RANGE SCAN | PSAJOB | 2 | 32 | 2 (0)| 00:00:01 |
| 18 | SORT AGGREGATE | | 1 | 22 | | |
| 19 | FIRST ROW | | 1 | 22 | 2 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN (MIN/MAX) | PSAJOB | 1 | 22 | 2 (0)| 00:00:01 |
| 21 | SORT AGGREGATE | | 1 | 19 | | |
| 22 | FIRST ROW | | 1 | 19 | 2 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN (MIN/MAX) | PSAJOB | 1 | 19 | 2 (0)| 00:00:01 |
| 24 | SORT AGGREGATE | | 1 | 22 | | |
| 25 | FIRST ROW | | 1 | 22 | 2 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN (MIN/MAX)| PSAJOB | 1 | 22 | 2 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | PSAJOB | 8 | 88 | 1 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID | PS_LOCATION_TBL | 1 | 25 | 2 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | PS_LOCATION_TBL | 1 | | 1 (0)| 00:00:01 |
| 30 | SORT AGGREGATE | | 1 | 21 | | |
|* 31 | INDEX RANGE SCAN | PS_LOCATION_TBL | 1 | 21 | 2 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | PS_COUNTRY_TBL | 1 | 7 | 1 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | PS_COUNTRY_TBL | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Finally the query is running in few seconds only.
Ok, the Peoplesoft support continue to recommand this setting "_unnest_subquery"=false, and I understand now it helps.
However, looks interesting, cost is same, so what happens ? What makes Oracle decide to run the first intead of the second ?
The only comment about this parameter on Metalink is : "This parameter controls whether the optimizer attempts to unnest correlated subqueries or not." Fair enough, let's see within 10053 trace file the differences.
The part of the query causing the issue is the subquery with J3 alias and SYSDATE.
AND J.EFFDT = ( SELECT MAX(J3.EFFDT)
FROM PS_JOB J3
WHERE J3.EMPLID = J.EMPLID
AND J3.EMPL_RCD = J.EMPL_RCD
AND J3.EFFDT <= SYSDATE)
Surprisingly, for this subquery, the 10053 trace with "_unnest_subquery"=true shows that it even doesn't consider a RANGE SCAN (Min/Max) against PSAJOB where it is the lowest cost when "_unnest_subquery"=false.
Well, maybe time to raise a SR and start to follow the advices.
Enjoy !
It is surprising me, because this recommandation exists for several years now, and on older database as well.
Ok, let's see what if we don't follow this specific recommandation.
On 10.2.0.4 database, we have the following query running for hours :
UPDATE PS_AB_PDI_CN_TAO
SET country_2char = COALESCE(( SELECT q.country_2char
FROM ( SELECT o.oprid
, j.emplid
, j.empl_rcd
, j.effdt
, j.effseq
, c.country_2char
FROM ps_job j
, psoprdefn o
, ps_opr_def_tbl_Hr h
, ps_location_Tbl l
, ps_country_tbl c
WHERE EXISTS ( SELECT 'x'
FROM ps_job j2
WHERE j.emplid = j2.emplid
AND j2.empl_Rcd = 1)
AND j.emplid = o.emplid
AND o.oprclass = h.oprclass
AND j.effdt = ( SELECT MAX(j3.EFFDT)
FROM PS_JOB j3
WHERE j3.EMPLID = j.EMPLID
AND j3.EMPL_RCD = j.EMPL_RCD
AND j3.EFFDT <= sysdate)
AND j.effseq = ( SELECT MAX(j4.EFFSEQ)
FROM PS_JOB j4
WHERE j4.EMPLID = j.EMPLID
AND j4.EMPL_RCD = j.EMPL_RCD
AND j4.EFFDT = j.effdt)
AND l.setid = j.setid_location
AND l.location = j.location
AND h.country = l.country
AND l.effdt = ( SELECT MAX(l2.effdt)
FROM ps_location_tbl l2
WHERE l.setid = l2.setid
AND l.location = l2.location
AND l2.effdt <= j.effdt)
AND c.country = l.country ) q
WHERE Q.EFFDT = ( SELECT MAX(EFFDT)
FROM PS_JOB Q2
WHERE Q.EMPLID = Q2.EMPLID
AND Q2.EFFDT <= SYSDATE
AND Q.EFFDT <= SYSDATE)
AND Q.EFFSEQ = ( SELECT MAX(J3.EFFSEQ)
FROM PS_JOB J3
WHERE Q.EMPLID = J3.EMPLID
AND Q.EMPL_RCD = J3.EMPL_RCD
AND Q.EFFDT = J3.EFFDT)
AND Q.OPRID = PS_AB_PDI_CN_TAO.OPRID)
,' ')
WHERE COUNTRY_2CHAR = ' ';
The explain plan is the following :
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 41882 | 3599K| 18 (0)| 00:00:01 |
| 1 | UPDATE | PS_AB_PDI_CN_TAO | | | | |
|* 2 | TABLE ACCESS FULL | PS_AB_PDI_CN_TAO | 41882 | 3599K| 18 (0)| 00:00:01 |
| 3 | HASH UNIQUE | | 1 | 137 | 26 (12)| 00:00:01 |
|* 4 | HASH JOIN | | 1 | 137 | 15 (14)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 117 | 8 (0)| 00:00:01 |
| 6 | NESTED LOOPS | | 1 | 110 | 7 (0)| 00:00:01 |
| 7 | NESTED LOOPS SEMI | | 1 | 85 | 5 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 74 | 4 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | PSOPRDEFN | 1 | 27 | 1 (0)| 00:00:01 |
|* 11 | INDEX UNIQUE SCAN | PS_PSOPRDEFN | 1 | | 0 (0)| 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID | PS_OPR_DEF_TBL_HR | 33 | 396 | 1 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | PS_OPR_DEF_TBL_HR | 1 | | 0 (0)| 00:00:01 |
| 14 | TABLE ACCESS BY INDEX ROWID | PS_JOB | 1 | 35 | 2 (0)| 00:00:01 |
|* 15 | INDEX RANGE SCAN | PSAJOB | 1 | | 1 (0)| 00:00:01 |
| 16 | SORT AGGREGATE | | 1 | 16 | | |
|* 17 | FILTER | | | | | |
|* 18 | INDEX RANGE SCAN | PSAJOB | 2 | 32 | 2 (0)| 00:00:01 |
| 19 | SORT AGGREGATE | | 1 | 22 | | |
| 20 | FIRST ROW | | 1 | 22 | 2 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN (MIN/MAX) | PSAJOB | 1 | 22 | 2 (0)| 00:00:01 |
| 22 | SORT AGGREGATE | | 1 | 22 | | |
| 23 | FIRST ROW | | 1 | 22 | 2 (0)| 00:00:01 |
|* 24 | INDEX RANGE SCAN (MIN/MAX)| PSAJOB | 1 | 22 | 2 (0)| 00:00:01 |
|* 25 | INDEX RANGE SCAN | PSAJOB | 8 | 88 | 1 (0)| 00:00:01 |
|* 26 | TABLE ACCESS BY INDEX ROWID | PS_LOCATION_TBL | 1 | 25 | 2 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | PS_LOCATION_TBL | 1 | | 1 (0)| 00:00:01 |
| 28 | SORT AGGREGATE | | 1 | 21 | | |
|* 29 | INDEX RANGE SCAN | PS_LOCATION_TBL | 1 | 21 | 2 (0)| 00:00:01 |
| 30 | TABLE ACCESS BY INDEX ROWID | PS_COUNTRY_TBL | 1 | 7 | 1 (0)| 00:00:01 |
|* 31 | INDEX UNIQUE SCAN | PS_COUNTRY_TBL | 1 | | 0 (0)| 00:00:01 |
| 32 | VIEW | VW_SQ_1 | 3743 | 74860 | 6 (17)| 00:00:01 |
| 33 | SORT GROUP BY | | 3743 | 109K| 6 (17)| 00:00:01 |
|* 34 | INDEX FAST FULL SCAN | PS_JOB | 3743 | 109K| 5 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
Well, the last three lines are not as I would expect.
Let's try this hidden parameter :
"_unnest_subquery"=false (true by default)
Finally the explain plan change a lot and looks much better.
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 41882 | 3599K| 18 (0)| 00:00:01 |
| 1 | UPDATE | PS_AB_PDI_CN_TAO | | | | |
|* 2 | TABLE ACCESS FULL | PS_AB_PDI_CN_TAO | 41882 | 3599K| 18 (0)| 00:00:01 |
| 3 | HASH UNIQUE | | 1 | 117 | 21 (5)| 00:00:01 |
| 4 | NESTED LOOPS | | 1 | 117 | 8 (0)| 00:00:01 |
| 5 | NESTED LOOPS | | 1 | 110 | 7 (0)| 00:00:01 |
| 6 | NESTED LOOPS SEMI | | 1 | 85 | 5 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 74 | 4 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 39 | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | PSOPRDEFN | 1 | 27 | 1 (0)| 00:00:01 |
|* 10 | INDEX UNIQUE SCAN | PS_PSOPRDEFN | 1 | | 0 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | PS_OPR_DEF_TBL_HR | 33 | 396 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | PS_OPR_DEF_TBL_HR | 1 | | 0 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | PS_JOB | 1 | 35 | 2 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | PSAJOB | 1 | | 1 (0)| 00:00:01 |
| 15 | SORT AGGREGATE | | 1 | 16 | | |
|* 16 | FILTER | | | | | |
|* 17 | INDEX RANGE SCAN | PSAJOB | 2 | 32 | 2 (0)| 00:00:01 |
| 18 | SORT AGGREGATE | | 1 | 22 | | |
| 19 | FIRST ROW | | 1 | 22 | 2 (0)| 00:00:01 |
|* 20 | INDEX RANGE SCAN (MIN/MAX) | PSAJOB | 1 | 22 | 2 (0)| 00:00:01 |
| 21 | SORT AGGREGATE | | 1 | 19 | | |
| 22 | FIRST ROW | | 1 | 19 | 2 (0)| 00:00:01 |
|* 23 | INDEX RANGE SCAN (MIN/MAX) | PSAJOB | 1 | 19 | 2 (0)| 00:00:01 |
| 24 | SORT AGGREGATE | | 1 | 22 | | |
| 25 | FIRST ROW | | 1 | 22 | 2 (0)| 00:00:01 |
|* 26 | INDEX RANGE SCAN (MIN/MAX)| PSAJOB | 1 | 22 | 2 (0)| 00:00:01 |
|* 27 | INDEX RANGE SCAN | PSAJOB | 8 | 88 | 1 (0)| 00:00:01 |
|* 28 | TABLE ACCESS BY INDEX ROWID | PS_LOCATION_TBL | 1 | 25 | 2 (0)| 00:00:01 |
|* 29 | INDEX RANGE SCAN | PS_LOCATION_TBL | 1 | | 1 (0)| 00:00:01 |
| 30 | SORT AGGREGATE | | 1 | 21 | | |
|* 31 | INDEX RANGE SCAN | PS_LOCATION_TBL | 1 | 21 | 2 (0)| 00:00:01 |
| 32 | TABLE ACCESS BY INDEX ROWID | PS_COUNTRY_TBL | 1 | 7 | 1 (0)| 00:00:01 |
|* 33 | INDEX UNIQUE SCAN | PS_COUNTRY_TBL | 1 | | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Finally the query is running in few seconds only.
Ok, the Peoplesoft support continue to recommand this setting "_unnest_subquery"=false, and I understand now it helps.
However, looks interesting, cost is same, so what happens ? What makes Oracle decide to run the first intead of the second ?
The only comment about this parameter on Metalink is : "This parameter controls whether the optimizer attempts to unnest correlated subqueries or not." Fair enough, let's see within 10053 trace file the differences.
The part of the query causing the issue is the subquery with J3 alias and SYSDATE.
AND J.EFFDT = ( SELECT MAX(J3.EFFDT)
FROM PS_JOB J3
WHERE J3.EMPLID = J.EMPLID
AND J3.EMPL_RCD = J.EMPL_RCD
AND J3.EFFDT <= SYSDATE)
Surprisingly, for this subquery, the 10053 trace with "_unnest_subquery"=true shows that it even doesn't consider a RANGE SCAN (Min/Max) against PSAJOB where it is the lowest cost when "_unnest_subquery"=false.
Well, maybe time to raise a SR and start to follow the advices.
Enjoy !
Subscribe to:
Posts (Atom)
