Within the quartely CPU (Critical Patch Update) of July 2009 available from today, Oracle introduce a new concept, the PSU (or Patch Set Update).
It is a "bundle" of patches including the latest CPU, and the current one includes also the following patches :
*Generic Recommended Bundle #4 (Patch 8362683)
*RAC Recommended Bundle #3 (Patch 8344348)
*Data Guard Broker Recommended Bundle #1 (Patch 7936793)
*Data Guard Physical/Recovery Recommended Bundle #1 (Patch 7936993)
*Data Guard Logical Recommended Bundle #1 (Patch 7937113)
PSU is a cumulative bundle of patches, the concept exists for a while for Windows plateform, but right now, we got it for most of the Unix/Linux plateform.
It is too early to say if that's good or not, I think it is good idea, that could avoid to apply dozen of individual patches one by one. On an other hand, we still should be able to choose between cumulative and individual patches, that'd a pity to have to download and install several Mb file to get a small fix of few Kb.
Find out more in the Metalink notre Intro to Patch Set Updates (PSU) Doc Id #854428.1
Enjoy Oracle database,
Tuesday, July 14, 2009
Sunday, May 24, 2009
Surveys
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.
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.
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 !
Tuesday, April 14, 2009
The PS_HOME subfolders
On PSST0101 blog, an interesting list of the folders existing under PS_HOME with some explanations for each.
Do not hesitate to contribute.
Enjoy,
Do not hesitate to contribute.
Enjoy,
Wednesday, April 01, 2009
CentOS 5.3 is out
I know, it is a little bit out off topic here, and this is not because this is the 1st of April, but because I'm using it as the main host of all my virtual machines, and even if Oracle database software is officially not certified on CentOS, a lot of people are using it as a database server, as a free OS fully RHEL compatible.
Several week after RHEL 5.3, and two-three weeks later OEL 5.3, CentOS is finaly released its own version of 5.3.
If you are on CentOS 5.x (mine is CentOS5.2), it is very easy to upgrade. For some reasons I had to run the yum update a couple of times before taking in account the news packages. But then, everything run fine :
[root@hercules /root]# rpm -q centos-release
centos-release-5-2.el5.centos
[root@hercules /root]# yum list updates
Loading "fastestmirror" plugin
Loading mirror speeds from cached hostfile
* base: mirror.fraunhofer.de
* updates: ftp.belnet.be
* addons: ftp.hosteurope.de
* extras: ftp.hosteurope.de
Updated Packages
NetworkManager.x86_64 1:0.7.0-3.el5 base
NetworkManager-glib.x86_64 1:0.7.0-3.el5 base
[...]the list of update pakages is around 300, I won't post here[...]
[root@hercules /root]# yum update
Loading "fastestmirror" plugin
Loading mirror speeds from cached hostfile
* base: mirror.widexs.nl
* updates: ftp.belnet.be
* addons: ftp.hosteurope.de
* extras: centos.intergenia.de
Setting up Update Process
Resolving Dependencies
--> Running transaction check
---> Package glx-utils.x86_64 0:6.5.1-7.7.el5 set to be updated
---> Package xorg-x11-drv-ati.x86_64 0:6.6.3-3.22.el5 set to be updated
[...]
Transaction Summary
=============================================================================
Install 11 Package(s)
Update 272 Package(s)
Remove 2 Package(s)
Total download size: 363 M
Is this ok [y/N]: y
Downloading Packages:
(1/283): mkinitrd-5.1.19. 100% |=========================| 449 kB 00:01
(2/283): dbus-glib-0.73-8 100% |=========================| 162 kB 00:00
[...]
Complete!
[root@hercules /root]# rpm -q centos-release
centos-release-5-3.el5.centos.1
To check your Linux distribution, you can use :
[root@hercules /root]# more /etc/redhat-release
CentOS release 5.3 (Final)
Don't forget to bounce your server :
[root@hercules /root]# shutdown -r now
Lastly, if you are using VMWare like I do, don't forget to re-configure it :
[root@hercules /]# cd appl/vmware/bin
[root@hercules /appl/vmware/bin]# ./vmware-config.pl
Making sure services for VMware Server are stopped.
Stopping VMware services:
Virtual machine monitor [ OK ]
Bridged networking on /dev/vmnet0 [ OK ]
Bridged networking on /dev/vmnet2 [ OK ]
Virtual ethernet [ OK ]
Configuring fallback GTK+ 2.4 libraries.
[...]
Starting VMware services:
Virtual machine monitor [ OK ]
Virtual ethernet [ OK ]
Bridged networking on /dev/vmnet0 [ OK ]
Bridged networking on /dev/vmnet2 [ OK ]
Starting VMware virtual machines... [ OK ]
The configuration of VMware Server 1.0.8 build-126538 for Linux for this
running kernel completed successfully.
[root@hercules /appl/vmware/bin]#
All is running fine !
Thanks to the CentOS' team, even if this version is coming very late compared to the source.
To go further : http://www.centos.org
Enjoy,
Several week after RHEL 5.3, and two-three weeks later OEL 5.3, CentOS is finaly released its own version of 5.3.
If you are on CentOS 5.x (mine is CentOS5.2), it is very easy to upgrade. For some reasons I had to run the yum update a couple of times before taking in account the news packages. But then, everything run fine :
[root@hercules /root]# rpm -q centos-release
centos-release-5-2.el5.centos
[root@hercules /root]# yum list updates
Loading "fastestmirror" plugin
Loading mirror speeds from cached hostfile
* base: mirror.fraunhofer.de
* updates: ftp.belnet.be
* addons: ftp.hosteurope.de
* extras: ftp.hosteurope.de
Updated Packages
NetworkManager.x86_64 1:0.7.0-3.el5 base
NetworkManager-glib.x86_64 1:0.7.0-3.el5 base
[...]the list of update pakages is around 300, I won't post here[...]
[root@hercules /root]# yum update
Loading "fastestmirror" plugin
Loading mirror speeds from cached hostfile
* base: mirror.widexs.nl
* updates: ftp.belnet.be
* addons: ftp.hosteurope.de
* extras: centos.intergenia.de
Setting up Update Process
Resolving Dependencies
--> Running transaction check
---> Package glx-utils.x86_64 0:6.5.1-7.7.el5 set to be updated
---> Package xorg-x11-drv-ati.x86_64 0:6.6.3-3.22.el5 set to be updated
[...]
Transaction Summary
=============================================================================
Install 11 Package(s)
Update 272 Package(s)
Remove 2 Package(s)
Total download size: 363 M
Is this ok [y/N]: y
Downloading Packages:
(1/283): mkinitrd-5.1.19. 100% |=========================| 449 kB 00:01
(2/283): dbus-glib-0.73-8 100% |=========================| 162 kB 00:00
[...]
Complete!
[root@hercules /root]# rpm -q centos-release
centos-release-5-3.el5.centos.1
To check your Linux distribution, you can use :
[root@hercules /root]# more /etc/redhat-release
CentOS release 5.3 (Final)
Don't forget to bounce your server :
[root@hercules /root]# shutdown -r now
Lastly, if you are using VMWare like I do, don't forget to re-configure it :
[root@hercules /]# cd appl/vmware/bin
[root@hercules /appl/vmware/bin]# ./vmware-config.pl
Making sure services for VMware Server are stopped.
Stopping VMware services:
Virtual machine monitor [ OK ]
Bridged networking on /dev/vmnet0 [ OK ]
Bridged networking on /dev/vmnet2 [ OK ]
Virtual ethernet [ OK ]
Configuring fallback GTK+ 2.4 libraries.
[...]
Starting VMware services:
Virtual machine monitor [ OK ]
Virtual ethernet [ OK ]
Bridged networking on /dev/vmnet0 [ OK ]
Bridged networking on /dev/vmnet2 [ OK ]
Starting VMware virtual machines... [ OK ]
The configuration of VMware Server 1.0.8 build-126538 for Linux for this
running kernel completed successfully.
[root@hercules /appl/vmware/bin]#
All is running fine !
Thanks to the CentOS' team, even if this version is coming very late compared to the source.
To go further : http://www.centos.org
Enjoy,
Monday, March 23, 2009
Modify NLS_LENGTH_SEMANTICS online
From Peopletools 8.48, when working on a UNICODE Oracle database, it is recommanded, do not say mandatory, to set the NLS_LENGTH_SEMANTICS to CHAR to be able to enter multi-bytes characters.
By default, NLS_LENGTH_SEMANTICS is set to BYTE.
This is a problem if you didn't modify this parameter when started the database, you cannot change it afterwards online without bouncing the database.
If you can change the value for your own session (alter session) and everything works fine and you can play with the new value, if you do it on system level (alter system), the new value is not taken in account (however, the alter system command does not return any error !):
SQL> select name,value,isdefault from v$parameter where name ='nls_length_semantics';
NAME VALUE ISDEFAULT
----------------------- -------- ---------
nls_length_semantics BYTE TRUE
SQL> alter system set nls_length_semantics=char;
System altered.
SQL> create table sysadm.nicolas(column1 varchar2(10));
Table created.
SQL> select column_name,data_type,data_length,char_length,char_used
from dba_tab_columns where table_name='NICOLAS';
COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_LENGTH C
------------ --------- ----------- ----------- -
COLUMN1 VARCHAR2 10 10 B
As you can see, the character used is BYTE, not CHAR as it is expected after parameter value change.
The only workaround to this, is to change the spfile and bounce the database :
SQL> alter system set nls_length_semantics=char scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2074664 bytes
Variable Size 746588120 bytes
Database Buffers 318767104 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
SQL> drop table sysadm.nicolas;
Table dropped.
SQL> select name,value,isdefault from v$parameter where name ='nls_length_semantics';
NAME VALUE ISDEFAULT
----------------------- -------- ---------
nls_length_semantics CHAR FALSE
SQL> create table sysadm.nicolas(column1 varchar2(10));
Table created.
SQL> select column_name,data_type,data_length,char_length,char_used from dba_tab_columns where table_name='NICOLAS';
COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_LENGTH C
------------ --------- ----------- ----------- -
COLUMN1 VARCHAR2 30 10 C
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
Now it is becoming CHAR as expected from the beginning.
I found it disapointed.
By default, NLS_LENGTH_SEMANTICS is set to BYTE.
This is a problem if you didn't modify this parameter when started the database, you cannot change it afterwards online without bouncing the database.
If you can change the value for your own session (alter session) and everything works fine and you can play with the new value, if you do it on system level (alter system), the new value is not taken in account (however, the alter system command does not return any error !):
SQL> select name,value,isdefault from v$parameter where name ='nls_length_semantics';
NAME VALUE ISDEFAULT
----------------------- -------- ---------
nls_length_semantics BYTE TRUE
SQL> alter system set nls_length_semantics=char;
System altered.
SQL> create table sysadm.nicolas(column1 varchar2(10));
Table created.
SQL> select column_name,data_type,data_length,char_length,char_used
from dba_tab_columns where table_name='NICOLAS';
COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_LENGTH C
------------ --------- ----------- ----------- -
COLUMN1 VARCHAR2 10 10 B
As you can see, the character used is BYTE, not CHAR as it is expected after parameter value change.
The only workaround to this, is to change the spfile and bounce the database :
SQL> alter system set nls_length_semantics=char scope=spfile;
System altered.
SQL> startup force
ORACLE instance started.
Total System Global Area 1073741824 bytes
Fixed Size 2074664 bytes
Variable Size 746588120 bytes
Database Buffers 318767104 bytes
Redo Buffers 6311936 bytes
Database mounted.
Database opened.
SQL> drop table sysadm.nicolas;
Table dropped.
SQL> select name,value,isdefault from v$parameter where name ='nls_length_semantics';
NAME VALUE ISDEFAULT
----------------------- -------- ---------
nls_length_semantics CHAR FALSE
SQL> create table sysadm.nicolas(column1 varchar2(10));
Table created.
SQL> select column_name,data_type,data_length,char_length,char_used from dba_tab_columns where table_name='NICOLAS';
COLUMN_NAME DATA_TYPE DATA_LENGTH CHAR_LENGTH C
------------ --------- ----------- ----------- -
COLUMN1 VARCHAR2 30 10 C
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
Now it is becoming CHAR as expected from the beginning.
I found it disapointed.
Addendum : it is an known bug #1488174 (internal bug, non-public), opened several years ago on database version 8.x, not solved yet. Find out more in the metalink note #144808.1.
Friday, March 20, 2009
Create index with compute statistics
When you are (re)building an index on Peoplesoft with in Application designer, Peoplesoft is writing a sql script to (re)create the index on the database level.
The statement of index creation contains "compute statistics" option.
If in most of cases there is no issue in that, sometimes you could have some strange behaviour behind this simple statement.
Some times ago, on one of our 9.2 database, we had a query which was not fast as expected. Just to try, we decided to create a new index. It was still not fast enough, then drop this new index.
Strangely, this index creation makes Oracle optimizer change his behaviour comparing before the index (creation and drop) and start to use an other explain plan.
How comes ? We created and drop a new index (through Application Designer) and Oracle "decided" to change the execution plan, hmmm, strange.
After looking deeper, it appears the table statistics had been updated by the index creation containing "compute statistics".
Example :
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';
NUM_ROWS LAST_ANA
---------- --------
SQL> create index idx on emp(ename) compute statistics;
Index created.
SQL> drop index idx;
Index dropped.
SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';
NUM_ROWS LAST_ANA
---------- --------
14 20/03/09
But, in some cases, I wouldn't want the table statistics to be updated by my index creation. That's bad, that does not happen without this option "compute statistics" :
SQL> create index idx on emp(ename);
Index created.
SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';
NUM_ROWS LAST_ANA
---------- --------
Finally, found an event to work around and avoid that table statistics update :
SQL> alter session set events '8130 trace name context forever';
Session altered.
SQL> create index idx on emp(ename) compute statistics;
Index created.
SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';
NUM_ROWS LAST_ANA
---------- --------
Ah, now that's better, now if I drop this index, my explain plan won't change as before the index.
This case has been tested on 9.2.0.6 and 9.2.0.8. Seems not to be a problem anymore in the higher version.
Enjoy,
The statement of index creation contains "compute statistics" option.
If in most of cases there is no issue in that, sometimes you could have some strange behaviour behind this simple statement.
Some times ago, on one of our 9.2 database, we had a query which was not fast as expected. Just to try, we decided to create a new index. It was still not fast enough, then drop this new index.
Strangely, this index creation makes Oracle optimizer change his behaviour comparing before the index (creation and drop) and start to use an other explain plan.
How comes ? We created and drop a new index (through Application Designer) and Oracle "decided" to change the execution plan, hmmm, strange.
After looking deeper, it appears the table statistics had been updated by the index creation containing "compute statistics".
Example :
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.8.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';
NUM_ROWS LAST_ANA
---------- --------
SQL> create index idx on emp(ename) compute statistics;
Index created.
SQL> drop index idx;
Index dropped.
SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';
NUM_ROWS LAST_ANA
---------- --------
14 20/03/09
But, in some cases, I wouldn't want the table statistics to be updated by my index creation. That's bad, that does not happen without this option "compute statistics" :
SQL> create index idx on emp(ename);
Index created.
SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';
NUM_ROWS LAST_ANA
---------- --------
Finally, found an event to work around and avoid that table statistics update :
SQL> alter session set events '8130 trace name context forever';
Session altered.
SQL> create index idx on emp(ename) compute statistics;
Index created.
SQL> select num_rows, last_analyzed from user_tables where table_name = 'EMP';
NUM_ROWS LAST_ANA
---------- --------
Ah, now that's better, now if I drop this index, my explain plan won't change as before the index.
This case has been tested on 9.2.0.6 and 9.2.0.8. Seems not to be a problem anymore in the higher version.
Enjoy,
Subscribe to:
Posts (Atom)
