Again, we can say September' 2009 is rich in events for Oracle.
After Oracle database 11gR2, OEL5.4, one more proof, Peopletools 8.50, waited for a while is released !
Go through http://edelivery.oracle.com/ to do download
Go through http://download.oracle.com/docs/cd/E15742_01/psft/html/docset.html for the installation doc !
Enjoy !
Nicolas.
Update 19-SEPT-2009 :
The patch 1 is now available on the usual ftp website with the password protection getting from My Oracle Support.
PT8.50 has also been announced on the Peoplesoft Technology blog.
Nicolas.
Friday, September 18, 2009
Wednesday, September 16, 2009
OEL5.4 available
September' 2009 is beeing a very rich month regarding new Oracle software version.
After the Oracle database 11gR2, this is now for Oracle Entreprise Linux 5.4 to be available only few weeks after the linux based version RHEL5.4.
We'll see later if there is added value by Oracle in this release compared to RHEL.
Now, I'm awaiting the CentOS 5.4 which is the OS of my lab.
But since CentOS is a free OS developped on free time, it is always coming much later, one or two months...
Enjoy,
Nicolas.
After the Oracle database 11gR2, this is now for Oracle Entreprise Linux 5.4 to be available only few weeks after the linux based version RHEL5.4.
We'll see later if there is added value by Oracle in this release compared to RHEL.
Now, I'm awaiting the CentOS 5.4 which is the OS of my lab.
But since CentOS is a free OS developped on free time, it is always coming much later, one or two months...
Enjoy,
Nicolas.
Tuesday, September 08, 2009
Get rid off segment of empty table (11gR2)
I'm trying to get rid off segment of empty table, according to the documentation it should be possible to alter the table with the clause "SEGMENT CREATION DEFERRED", but it does not look like so obvious :
Table created.
SQL> create table mytable2(id number);
Table created.
SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';
no rows selected
SQL> insert into mytable1 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';
SEGMENT_NAME BYTES
---------- ------
MYTABLE1 65536
SQL> truncate table mytable1;
Table truncated.
SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';
SEGMENT_NAME BYTES
---------- ------
MYTABLE1 65536
SQL> alter table mytable1 move;
Table altered.
SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';
SEGMENT_NAME BYTES
---------- ------
MYTABLE1 65536
SQL> alter table mytable1 move segment creation deferred;
alter table mytable1 move segment creation deferred
*
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
SQL> alter table mytable1 segment creation deferred;
alter table mytable1 segment creation deferred
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
Ok, then let's try to export and import tables to see if it is easy :
Export: Release 11.2.0.1.0 - Production on Tue Sep 8 21:57:36 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSADM"."SYS_EXPORT_TABLE_01": sysadm/******** dumpfile=mytable.dmp logfile=mytable.log directory=data_pump_dir tables=mytable1,mytable2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SYSADM"."MYTABLE1" 0 KB 0 rows
. . exported "SYSADM"."MYTABLE2" 0 KB 0 rows
Master table "SYSADM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSADM.SYS_EXPORT_TABLE_01 is:
/oradata/DMOFSCM9/dpdump/mytable.dmp
Job "SYSADM"."SYS_EXPORT_TABLE_01" successfully completed at 21:57:46
Then standard import (without particular options) :
Import: Release 11.2.0.1.0 - Production on Tue Sep 8 21:58:04 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSADM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSADM"."SYS_SQL_FILE_FULL_01": sysadm/******** dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir sqlfile=mytable.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSADM"."SYS_SQL_FILE_FULL_01" successfully completed at 21:58:06
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ more mytable.sql
-- CONNECT SYSADM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SYSADM"."MYTABLE1"
( "ID" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PSDEFAULT" ;
CREATE TABLE "SYSADM"."MYTABLE2"
( "ID" NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "PSDEFAULT" ;
The SEGMENT CREATION IMMEDIATE is there for my table mytable1 (eventhough it is empty).
Let's try to remove storage of table during import :
Import: Release 11.2.0.1.0 - Production on Tue Sep 8 21:58:17 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSADM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSADM"."SYS_SQL_FILE_FULL_01": sysadm/******** dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir transform=storage:n sqlfile=mytable.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSADM"."SYS_SQL_FILE_FULL_01" successfully completed at 21:58:19
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ more mytable.sql
-- CONNECT SYSADM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SYSADM"."MYTABLE1"
( "ID" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "PSDEFAULT" ;
CREATE TABLE "SYSADM"."MYTABLE2"
( "ID" NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "PSDEFAULT" ;
No more luck.
Finally, let's remove the segment attribute :
Import: Release 11.2.0.1.0 - Production on Tue Sep 8 21:58:34 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSADM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSADM"."SYS_SQL_FILE_FULL_01": sysadm/******** dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir transform=segment_attributes:n sqlfile=mytable.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSADM"."SYS_SQL_FILE_FULL_01" successfully completed at 21:58:36
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ more mytable.sql
-- CONNECT SYSADM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SYSADM"."MYTABLE1"
( "ID" NUMBER
) ;
CREATE TABLE "SYSADM"."MYTABLE2"
( "ID" NUMBER
) ;
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$
Unfortunately, no more tablespace... that means every single imported table will be (re)created into the user's default tablespace, which is not good enough.
Looks like as soon as we have a segment for a table, it is rather difficult to "remove" it.
To be continued,
Nicolas.
SQL> create table mytable1(id number);
Table created.
SQL> create table mytable2(id number);
Table created.
SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';
no rows selected
SQL> insert into mytable1 values(1);
1 row created.
SQL> commit;
Commit complete.
SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';
SEGMENT_NAME BYTES
---------- ------
MYTABLE1 65536
SQL> truncate table mytable1;
Table truncated.
SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';
SEGMENT_NAME BYTES
---------- ------
MYTABLE1 65536
SQL> alter table mytable1 move;
Table altered.
SQL> select segment_name,bytes from user_segments where segment_name like 'MYTABLE_';
SEGMENT_NAME BYTES
---------- ------
MYTABLE1 65536
SQL> alter table mytable1 move segment creation deferred;
alter table mytable1 move segment creation deferred
*
ERROR at line 1:
ORA-14133: ALTER TABLE MOVE cannot be combined with other operations
SQL> alter table mytable1 segment creation deferred;
alter table mytable1 segment creation deferred
*
ERROR at line 1:
ORA-01735: invalid ALTER TABLE option
Ok, then let's try to export and import tables to see if it is easy :
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ expdp sysadm/sysadm dumpfile=mytable.dmp logfile=mytable.log directory=data_pump_dir tables=mytable1,mytable2
Export: Release 11.2.0.1.0 - Production on Tue Sep 8 21:57:36 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSADM"."SYS_EXPORT_TABLE_01": sysadm/******** dumpfile=mytable.dmp logfile=mytable.log directory=data_pump_dir tables=mytable1,mytable2
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SYSADM"."MYTABLE1" 0 KB 0 rows
. . exported "SYSADM"."MYTABLE2" 0 KB 0 rows
Master table "SYSADM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSADM.SYS_EXPORT_TABLE_01 is:
/oradata/DMOFSCM9/dpdump/mytable.dmp
Job "SYSADM"."SYS_EXPORT_TABLE_01" successfully completed at 21:57:46
Then standard import (without particular options) :
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ impdp sysadm/sysadm dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir sqlfile=mytable.sql
Import: Release 11.2.0.1.0 - Production on Tue Sep 8 21:58:04 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSADM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSADM"."SYS_SQL_FILE_FULL_01": sysadm/******** dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir sqlfile=mytable.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSADM"."SYS_SQL_FILE_FULL_01" successfully completed at 21:58:06
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ more mytable.sql
-- CONNECT SYSADM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SYSADM"."MYTABLE1"
( "ID" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "PSDEFAULT" ;
CREATE TABLE "SYSADM"."MYTABLE2"
( "ID" NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "PSDEFAULT" ;
The SEGMENT CREATION IMMEDIATE is there for my table mytable1 (eventhough it is empty).
Let's try to remove storage of table during import :
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ impdp sysadm/sysadm dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir transform=storage:n sqlfile=mytable.sql
Import: Release 11.2.0.1.0 - Production on Tue Sep 8 21:58:17 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSADM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSADM"."SYS_SQL_FILE_FULL_01": sysadm/******** dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir transform=storage:n sqlfile=mytable.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSADM"."SYS_SQL_FILE_FULL_01" successfully completed at 21:58:19
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ more mytable.sql
-- CONNECT SYSADM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SYSADM"."MYTABLE1"
( "ID" NUMBER
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "PSDEFAULT" ;
CREATE TABLE "SYSADM"."MYTABLE2"
( "ID" NUMBER
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "PSDEFAULT" ;
No more luck.
Finally, let's remove the segment attribute :
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ impdp sysadm/sysadm dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir transform=segment_attributes:n sqlfile=mytable.sql
Import: Release 11.2.0.1.0 - Production on Tue Sep 8 21:58:34 2009
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSADM"."SYS_SQL_FILE_FULL_01" successfully loaded/unloaded
Starting "SYSADM"."SYS_SQL_FILE_FULL_01": sysadm/******** dumpfile=mytable.dmp logfile=mytable_imp.log directory=data_pump_dir transform=segment_attributes:n sqlfile=mytable.sql
Processing object type TABLE_EXPORT/TABLE/TABLE
Job "SYSADM"."SYS_SQL_FILE_FULL_01" successfully completed at 21:58:36
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$ more mytable.sql
-- CONNECT SYSADM
ALTER SESSION SET EVENTS '10150 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10904 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '25475 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10407 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '10851 TRACE NAME CONTEXT FOREVER, LEVEL 1';
ALTER SESSION SET EVENTS '22830 TRACE NAME CONTEXT FOREVER, LEVEL 192 ';
-- new object type path: TABLE_EXPORT/TABLE/TABLE
CREATE TABLE "SYSADM"."MYTABLE1"
( "ID" NUMBER
) ;
CREATE TABLE "SYSADM"."MYTABLE2"
( "ID" NUMBER
) ;
[oracle@orion2:/oradata/DMOFSCM9/dpdump]$
Unfortunately, no more tablespace... that means every single imported table will be (re)created into the user's default tablespace, which is not good enough.
Looks like as soon as we have a segment for a table, it is rather difficult to "remove" it.
To be continued,
Nicolas.
Monday, September 07, 2009
Peoplesoft on 11gR2 64-bits
I should start to say, Peoplesoft (any version) is not supported yet on 11gR2 (64/32 bits).
But I would like to test it how it is working.
1. Install the 11gR2 64-bits, as I explained here
2. Upgrade your database, here below a FSCM9 database (or create from scratch - I did not tested it yet)
The upgrade is quite standard, start the db with old version and run the 11.2 check utility :
Then start your db in upgrade mode, and run the upgrade script (with 11.2 binaries) :
After restarting the db in normal mode, check it afterwards :
There is an additional script to run :
And finally, recompile the objects :
3. Because 11gR2 64-bits is not coming with 32-bits libraries, as I wrote here, you'll need to install a 32-client into a new Oracle home for the application and batch server clients.
That is fine, nothing much to say about the client install, check the runtime option :
But I would like to test it how it is working.
1. Install the 11gR2 64-bits, as I explained here
2. Upgrade your database, here below a FSCM9 database (or create from scratch - I did not tested it yet)
The upgrade is quite standard, start the db with old version and run the 11.2 check utility :
@/apps/oracle/product/11.2.0/rdbms/admin/utlu112i.sql
Then start your db in upgrade mode, and run the upgrade script (with 11.2 binaries) :
@$ORACLE_HOME/rdbms/admin/catupgrd.sql
After restarting the db in normal mode, check it afterwards :
@$ORACLE_HOME/rdbms/admin/utlu112s.sql
There is an additional script to run :
@$ORACLE_HOME/rdbms/admin/catuppst.sql
And finally, recompile the objects :
@$ORACLE_HOME/rdbms/admin/utlrp.sql
3. Because 11gR2 64-bits is not coming with 32-bits libraries, as I wrote here, you'll need to install a 32-client into a new Oracle home for the application and batch server clients.
That is fine, nothing much to say about the client install, check the runtime option :
4. Create the required symbolic link $ORACLE_HOME/lib/libclntsh.so.9.0 pointing to $ORACLE_HOME/lib/libclntsh.so.11.1 (assuming ORACLE_HOME is
your Oracle client home path directory). Do not forget to configure the tnsnames.ora as well.
5. Change the ORACLE_HOME env variable to the Oracle client home pathdirectory of the Peoplesoft Unix account, and reconfigure the
application and batch domains.
Finally every should run fine.
One very interesting feature of 11gR2 for Peoplesoft is the possibility to deferred the segment creation, it will be very nice to
avoid segment creation for thousands of unused and empty tables., I should also have a try to rebuild the tables without segments...
Enjoy,
Nicolas.
Sunday, September 06, 2009
11gR2 64-bits : where is lib32 ?
From the Peopletools 8.49.14, Peoplesoft is fully certified on a 64-bits OS and 11gR1 64-bits (application server and batch server), I already post an article few months ago.
Peoplesoft was still a 32-bits application, but in 11gR1 64-bits, Oracle provided a lib32 directory containing all the required 32-bits libraries, and it worked fine by doing a symbolic link from $ORACLE_HOME/lib/libclntsh.so.9.0 to $ORACLE_HOME/lib32/libclntsh.so.11.1.
Here we go, 11gR2 does not provide anymore the 32-bits libraries, no more $ORACLE_HOME/lib32 directory.
Consequently, Peopletools 8.49 (application server and batch server) cannot be used within 11gR2 64-bits.
What’s up if you are already on a 64-bits OS ? The thing is, you cannot install 11gR2 32-bitsclient server on 64-bits Linux (blocked by the Installer).
So, what ?
Well, Peoplesoft does not say anything yet about 11gR2, but most likely, Peoplesoft 8.49.x won't be certified soon on 11gR2 64-bits... you must stay on 11gR1 64-bits, or move your application/batch server to 11gR2 32-bits client (sigh)…
I don't hink that'll easy for Peoplesoft to get this certification (11gR2 64-bits client) for existing Peopletools (8.49). Hopefully, that won’t be an issue for the coming Peopletools 8.50 (note, it won't be certified anymore on Linux 32-bits).
This is an issue not only for Peoplesoft application, but for all the 32-bits application running out an Oracle 64-bits client so far.
Update 1 (07-Sept-09): well it seems to be possible by installing the 11gR2 32-bit client onto a new Oracle home on Linux 64-bits, thanks to Fredrik Adolfsson for pointing me out the right documentation :
http://download.oracle.com/docs/cd/E11882_01/install.112/e10841/install_overview.htm#BABFICCG
Peoplesoft was still a 32-bits application, but in 11gR1 64-bits, Oracle provided a lib32 directory containing all the required 32-bits libraries, and it worked fine by doing a symbolic link from $ORACLE_HOME/lib/libclntsh.so.9.0 to $ORACLE_HOME/lib32/libclntsh.so.11.1.
Here we go, 11gR2 does not provide anymore the 32-bits libraries, no more $ORACLE_HOME/lib32 directory.
Consequently, Peopletools 8.49 (application server and batch server) cannot be used within 11gR2 64-bits.
What’s up if you are already on a 64-bits OS ? The thing is, you cannot install 11gR2 32-bits
So, what ?
Well, Peoplesoft does not say anything yet about 11gR2, but most likely, Peoplesoft 8.49.x won't be certified soon on 11gR2 64-bits... you must stay on 11gR1 64-bits, or move your application/batch server to 11gR2 32-bits client (sigh)…
I don't hink that'll easy for Peoplesoft to get this certification (11gR2 64-bits client) for existing Peopletools (8.49). Hopefully, that won’t be an issue for the coming Peopletools 8.50 (note, it won't be certified anymore on Linux 32-bits).
This is an issue not only for Peoplesoft application, but for all the 32-bits application running out an Oracle 64-bits client so far.
Update 1 (07-Sept-09): well it seems to be possible by installing the 11gR2 32-bit client onto a new Oracle home on Linux 64-bits, thanks to Fredrik Adolfsson for pointing me out the right documentation :
http://download.oracle.com/docs/cd/E11882_01/install.112/e10841/install_overview.htm#BABFICCG
Saturday, September 05, 2009
Oracle 11.2.0.1 install on OEL5.3 64 bits
It is a quite interesting release, a lot of changes in the installation screenshots, especially for those who followed the Oracle install for a long time.
Here, we go.
Firstly, be sure the following RPMs are installed on your OS :
Note the 32 bits packages requirements, it was a surprise for me.
Some of them was not installed on my server, so, need to be installed :
Moreover, modifications are required into /etc/sysctl.conf :
The run
Finally, within your prefered XWindow client :
Enjoy the new version,
Nicolas.
Here, we go.
Firstly, be sure the following RPMs are installed on your OS :
binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3
compat-libstdc++-33-3.2.3 (32 bit)
elfutils-libelf-0.125
elfutils-libelf-devel-0.125
gcc-4.1.2
gcc-c++-4.1.2
glibc-2.5-24
glibc-2.5-24 (32 bit)
glibc-common-2.5
glibc-devel-2.5
glibc-devel-2.5 (32 bit)
glibc-headers-2.5
ksh-20060214
libaio-0.3.106
libaio-0.3.106 (32 bit)
libaio-devel-0.3.106
libaio-devel-0.3.106 (32 bit)
libgcc-4.1.2
libgcc-4.1.2 (32 bit)
libstdc++-4.1.2
libstdc++-4.1.2 (32 bit)
libstdc++-devel 4.1.2
make-3.81
sysstat-7.0.2
unixODBC-2.2.11
unixODBC-2.2.11 (32 bit)
unixODBC-devel-2.2.11
unixODBC-devel-2.2.11 (32 bit)
Note the 32 bits packages requirements, it was a surprise for me.
Some of them was not installed on my server, so, need to be installed :
[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd2]# ls unix*
unixODBC-2.2.11-7.1.i386.rpm
[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd2]# rpm -Uvh unixODBC-2.2.11-7.1.i386.rpm
warning: unixODBC-2.2.11-7.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:unixODBC ########################################### [100%]
[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd2]# cd ../cd3
[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd3]# ls unix*
unixODBC-devel-2.2.11-7.1.i386.rpm unixODBC-kde-2.2.11-7.1.i386.rpm
[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd3]# rpm -Uvh unixODBC-devel-2.2.11-7.1.i386.rpm
warning: unixODBC-devel-2.2.11-7.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:unixODBC-devel ########################################### [100%]
[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd3]# ls libaio*
libaio-devel-0.3.106-3.2.i386.rpm
[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd3]# rpm -Uvh libaio-devel-0.3.106-3.2.i386.rpm
warning: libaio-devel-0.3.106-3.2.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:libaio-devel ########################################### [100%]
[root@orion2:/nfs/software/LinuxCD/OracleEntrepriseLinux/OEL5x86/OEL5.2x86/RPMs/cd3]#
Moreover, modifications are required into /etc/sysctl.conf :
#fs.file-max = 6553600
#change for 11gR2
fs.file-max = 6815744
...
#net.core.wmem_max = 262144
#change for 11gR2
net.core.wmem_max = 1048586
...
#net.ipv4.ip_local_port_range = 1024 65000
#change for 11gR2
net.ipv4.ip_local_port_range = 9000 65500
The run
/sbin/sysctl -por reboot your system.
Finally, within your prefered XWindow client :
Enjoy the new version,
Nicolas.
Tuesday, September 01, 2009
11gR2
Well, it is not a scoop anymore, but the release 2 of Oracle 11g has been out today (only for Linux 32/64 bits).
The size is amazing (or is it already the inflation?) : the 10gR2 was about 800Mb, right now, 11gR2 is splitted on two files of 1Gb each...
Anyway, it needs to be tested. Especially the LISTAGG function as Laurent mentioned here.
Download : http://www.oracle.com/technology/software/products/database/index.html
Doc : http://www.oracle.com/pls/db112/homepage
Nicolas.
The size is amazing (or is it already the inflation?) : the 10gR2 was about 800Mb, right now, 11gR2 is splitted on two files of 1Gb each...
Anyway, it needs to be tested. Especially the LISTAGG function as Laurent mentioned here.
Download : http://www.oracle.com/technology/software/products/database/index.html
Doc : http://www.oracle.com/pls/db112/homepage
Nicolas.
Peoplesoft plugin for OEM - part 3
Following the OEM 10.2.0.5 upgrade, I'm going to install the Peoplesoft plugin for OMS.
This is downloadable from hhtp://edelivery.oracle.com
Here we go.
1. Stop the OMS ($ORACLE_HOME/opmn/opmnctl stopall)
2. Go into $ORACLE_HOME/oui/bin (from OMS path directory) and run the installer.
3. Change the source location to Disk1/stage from the download file, as below :
4. Select Peoplesoft product for OMS
5. It will prompt for the SYS's password, and click on Install.
Unfortunately, in my case, I got an error :
My OMS is 10.2.0.5, and it is clearly specify the OMS should be 10.2.0.2 minimum, so ?
So, when installing OMS with all the defaults, it is installing a 10.1.0.4 repository database, it needs to be upgraded before going further.
6. Finally, after upgrading my repository database to 10.2.0.4 (today, the latest 10gR2 release), it ran smoothly.
7. It is restarting OMS automatically, and once you're connected onto OEM, you got one more tab named Peoplesoft :
(sorry for the French screenshot, my explorer is installed in French, but I think it is understandable, at least for what I wan to show).
Next step is the Peoplesoft plugin for Agent.
Enjoy,
Nicolas.
This is downloadable from hhtp://edelivery.oracle.com
Here we go.
1. Stop the OMS ($ORACLE_HOME/opmn/opmnctl stopall)
2. Go into $ORACLE_HOME/oui/bin (from OMS path directory) and run the installer.
3. Change the source location to Disk1/stage from the download file, as below :
4. Select Peoplesoft product for OMS
5. It will prompt for the SYS's password, and click on Install.Unfortunately, in my case, I got an error :
[oracle@opcenter bin]$ more /appl/oracle/oms10g/cfgtoollogs/psemca-2009-8-30_17-321.out
Getting temporary tablespace from database...
Found temporary tablespace: TEMP
Environment :
ORACLE HOME = /appl/oracle/oms10g
REPOSITORY HOME = /appl/oracle/oms10g
SQLPLUS = /appl/oracle/oms10g/bin/sqlplus
SQL SCRIPT ROOT = /appl/oracle/oms10g/sysman/admin/emdrep/sql
EXPORT = /appl/oracle/oms10g/bin/exp
IMPORT = /appl/oracle/oms10g/bin/imp
LOADJAVA = /appl/oracle/oms10g/bin/loadjava
JAR FILE ROOT = /appl/oracle/oms10g/sysman/admin/emdrep/lib
JOB TYPES ROOT = /appl/oracle/oms10g/sysman/admin/emdrep/bin
Arguments :
...
Failed to register/update the following template(s):
Get New Alerts XML Outbound
Get Updated Alerts XML Outbound
Get Response Inbound
Exception has been caught. Please check log file for more information.
Restore the tweaked target type driver scripts ..
Total time taken for parsing and reconstructing target types SQL files: 25 ms.
Optimization done.
Done.
Running setSchemaStatus: BEGIN EMD_MAINTENANCE.SET_VERSION('_UPGRADE_','0','0','SYSTEM',EMD_MAINTENANCE.G_STATUS_UPGRADED);END;
Repository Upgrade has errors. Please check file /appl/oracle/oms10g/sysman/log/emrepmgr.log.18350 for detailed errors.
....
SQL> SET ECHO OFF
MGMT_JOB_ENGINE.validate_job_type(l_job_type_name, l_commands, l_nested_jobtypes );
*
ERROR at line 42:
ORA-06550: line 42, column 21:
PLS-00302: component 'VALIDATE_JOB_TYPE' must be declared
ORA-06550: line 42, column 5:
PL/SQL: Statement ignored
ORA-06550: line 66, column 17:
PLS-00302: component 'INSERT_CLUSTER_TARGET_TYPES' must be declared
ORA-06550: line 66, column 1:
PL/SQL: Statement ignored
ORA-06550: line 130, column 64:
PL/SQL: ORA-00904: "ACTION": invalid identifier
ORA-06550: line 130, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 205, column 64:
PL/SQL: ORA-00904: "ACTION": invalid identifier
ORA-06550: line 205, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 262, column 64:
PL/SQL: ORA-00904: "ACTION": invalid identifier
ORA-06550: line 262, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 337, column 64:
PL/SQL: ORA-00904: "ACTION": invalid identifier
ORA-06550: line 337, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 394, column 64:
PL/SQL: ORA-00904: "ACTION": invalid identifier
ORA-06550: line 394, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 45
....My OMS is 10.2.0.5, and it is clearly specify the OMS should be 10.2.0.2 minimum, so ?
So, when installing OMS with all the defaults, it is installing a 10.1.0.4 repository database, it needs to be upgraded before going further.
6. Finally, after upgrading my repository database to 10.2.0.4 (today, the latest 10gR2 release), it ran smoothly.
7. It is restarting OMS automatically, and once you're connected onto OEM, you got one more tab named Peoplesoft :
(sorry for the French screenshot, my explorer is installed in French, but I think it is understandable, at least for what I wan to show).Next step is the Peoplesoft plugin for Agent.
Enjoy,
Nicolas.
Sunday, August 30, 2009
Peoplesoft plugin for OEM - part 2
The upgrade of OEM 10.2.0.3 to 10.2.0.5 on Linux x64 has not been difficult, no issue, nothing special to set.
To be upgraded, the OMS needs to be down more than 4 minutes before running the Installer, otherwise, you get the following error :
Enjoy,
Nicolas.
To be upgraded, the OMS needs to be down more than 4 minutes before running the Installer, otherwise, you get the following error :
Enjoy,Nicolas.
Metalink3 becomes My Oracle Support
Last year, the GSC - Global Support Customer - moved to Metalink3.
That was only a temporary situation to move all Peoplesoft's customers (and some others products) into an Oracle support website. This week-end, it is going more and more Oracle, the Metalnk3 will move to the My Oracle Support.
From the email we all received :
Oracle Global Customer Support is pleased to announce the launch of Oracle’s new online support portal, My Oracle Support. This coming weekend, August 28-30, 2009, Oracle will upgrade Oracle MetaLink 3 and officially change the name to "My Oracle Support." This transition will migrate existing OracleMetaLink 3 customers and partners (those using PeopleSoft, JD Edwards, Siebel, and Hyperion products) to My Oracle Support at https://support.oracle.com.
I'm still not convinced by this change, expecially the requirement to use MOS, FlashPlayer need to be installed... and some other inconvenients of the MOS usage itself (colors - too many -, pagelets - not needed -, search features - not working -...). All we want is a working and simple support website, not a "geek" or "movie" support website with all the latest Internet technologies, just a simple one to use, I'm not saying Metalink(1|2|3) was perfect, but at least it was simple. After all, we are looking for support (the two mains activities from there are Metalink note reading and SR creation), not for bying or to be convinced to use Internet technologies.
There is a thread into OTN forums, Metalink Classic - To Be Continued but it looks there is no way to avoid the change.
I'm sure, it will be discussed again and again over the Oracle community.
Enjoy,
That was only a temporary situation to move all Peoplesoft's customers (and some others products) into an Oracle support website. This week-end, it is going more and more Oracle, the Metalnk3 will move to the My Oracle Support.
From the email we all received :
Oracle Global Customer Support is pleased to announce the launch of Oracle’s new online support portal, My Oracle Support. This coming weekend, August 28-30, 2009, Oracle will upgrade Oracle MetaLink 3 and officially change the name to "My Oracle Support." This transition will migrate existing OracleMetaLink 3 customers and partners (those using PeopleSoft, JD Edwards, Siebel, and Hyperion products) to My Oracle Support at https://support.oracle.com.
I'm still not convinced by this change, expecially the requirement to use MOS, FlashPlayer need to be installed... and some other inconvenients of the MOS usage itself (colors - too many -, pagelets - not needed -, search features - not working -...). All we want is a working and simple support website, not a "geek" or "movie" support website with all the latest Internet technologies, just a simple one to use, I'm not saying Metalink(1|2|3) was perfect, but at least it was simple. After all, we are looking for support (the two mains activities from there are Metalink note reading and SR creation), not for bying or to be convinced to use Internet technologies.
There is a thread into OTN forums, Metalink Classic - To Be Continued but it looks there is no way to avoid the change.
I'm sure, it will be discussed again and again over the Oracle community.
Enjoy,
Tuesday, August 25, 2009
Peoplesoft plugin for OEM - part 1
In order to install OEM 10.2.0.5, we need to install OEM 10.2.0.3 as explain in the readme
So, let's install first OEM 10.2.0.3.
My OS is OEL 5.3 64-bits.
Linux opcenter.phoenix-nga 2.6.18-128.el5 #1 SMP Wed Jan 21 08:45:05 EST 2009 x86_64 x86_64 x86_64 GNU/Linux
[root@opcenter cd1]#
However, you could hit the following error during the Enterprise Management installation, when starting the Apache server :
09/08/23 18:37:44 Start process
--------
/appl/oracle/oms10g/Apache/Apache/bin/apachectl start: execing httpd
/appl/oracle/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory
That's because the installed application is a 32-bits application, it needs the 32-bits libraries which does not exists natively on a 64-bits plateform.
It requires to install manually one more package, gdbm-1.8.0-26.2.1.i386.rpm (which add the library /usr/lib/libgdbm.so.2), and create a symbolic link to this new library :
warning: gdbm-1.8.0-26.2.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:gdbm ########################################### [100%]
[root@opcenter cd1]# ln -s /usr/lib/libgdbm.so.2 /usr/lib/libdb.so.2
Apart from that, it is taking time, but no issue to install OEM 10.2.0.3 on OEL5.3 64-bits.
The next step is the upgrade to OEM 10.2.0.5, then the real target is the installation of the Peoplesoft plugin for OEM...
Enjoy,
Nicolas.
So, let's install first OEM 10.2.0.3.
My OS is OEL 5.3 64-bits.
[root@opcenter cd1]# uname -a
Linux opcenter.phoenix-nga 2.6.18-128.el5 #1 SMP Wed Jan 21 08:45:05 EST 2009 x86_64 x86_64 x86_64 GNU/Linux
[root@opcenter cd1]#
However, you could hit the following error during the Enterprise Management installation, when starting the Apache server :
oracle@opcenter sysman]$ more /appl/oracle/oms10g/opmn/logs/HTTP_Server~1
09/08/23 18:37:44 Start process
--------
/appl/oracle/oms10g/Apache/Apache/bin/apachectl start: execing httpd
/appl/oracle/oms10g/Apache/Apache/bin/httpd: error while loading shared libraries: libdb.so.2: cannot open shared object file: No such file or directory
That's because the installed application is a 32-bits application, it needs the 32-bits libraries which does not exists natively on a 64-bits plateform.
It requires to install manually one more package, gdbm-1.8.0-26.2.1.i386.rpm (which add the library /usr/lib/libgdbm.so.2), and create a symbolic link to this new library :
[root@opcenter cd1]# rpm -Uvh gdbm-1.8.0-26.2.1.i386.rpm
warning: gdbm-1.8.0-26.2.1.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing... ########################################### [100%]
1:gdbm ########################################### [100%]
[root@opcenter cd1]# ln -s /usr/lib/libgdbm.so.2 /usr/lib/libdb.so.2
Apart from that, it is taking time, but no issue to install OEM 10.2.0.3 on OEL5.3 64-bits.
The next step is the upgrade to OEM 10.2.0.5, then the real target is the installation of the Peoplesoft plugin for OEM...
Enjoy,
Nicolas.
Tuesday, July 14, 2009
Oracle PSU 10.2.0.4.1
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,
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,
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 !
Subscribe to:
Posts (Atom)
