Optimize Siebel Repository Import in Oracle Database
It was noticed during siebel repository import in an Oracle Database 18.104.22.168, most of database wait time was log file switch.
srvrupgwiz /m master_imprep.ucf
It took 163 minutes for 3,639,432 rows (98% waits log file switch).
2012-11-18 09:53:02 TOTAL TABLES: 328 2012-11-18 09:53:02 TOTAL ROWS : 3639432 2012-11-18 09:53:02 Cleaning up, disconnecting from the database. 2012-11-18 09:53:02 Elapsed time: 163 min 28 sec.
The database session did ~730.000 commits (meaning 1 commit every 5 rows).
repimexp /a I /G ENU /u sadmin /p ***** /c siebelpro_DSN /d siebel /r "Siebel Repository" /Z 5000 /h 5000 /f siebel_rep.data /l imprep_prim.log
If the /Z and /h option are used (with 5000 as argument) the time is less than 10 minutes for the same repository.
2013-01-27 11:34:35 Verified successfully. 2013-01-27 11:34:35 TOTAL TABLES: 328 2013-01-27 11:34:35 TOTAL ROWS : 3928282 2013-01-27 11:34:35 Cleaning up, disconnecting from the database. 2013-01-27 11:34:35 Elapsed time: 9 min 46 sec.
There is no way (until Dec 2012) to put /Z or /h in ucm file, so the only solution is to use the command line interface (Oracle support replied that is a product defect).
Reference from Oracle documentation:
Importing a Siebel Repository
NOTE: When you import data into the Siebel Repository tables, a commit is performed once for each table into which repository data is imported. Alternatively, the commit frequency can be set to a specified number of rows by including the command-line option /h num_rows_per_commit when the repimexp.exe utility is invoked.
/H [number] (Optional) Number of rows per commit
/Z [number] (Optional) Array Insert Size (Default: 5)
Article written by George Giannoukos