Friday, March 8, 2013

Optimize Siebel Repository Import in Oracle Database

It was noticed during siebel repository import in an Oracle Database 11.2.0.3, 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)

1 comment:

  1. It was a great help; repo import time reduced to 168 min to 36 min. Thanks

    ReplyDelete