And the worst data migration practices oscar goes to..

Siebel! Yes, the CRM software Oracle owns for some time;
http://www.oracle.com/applications/crm/siebel/index.html

I think this application is one of the best example of Kyte’s “database independent” black box applications, which usually means designed to work on every database platform but born to perform and scale bad on all of them. The reason is simple, it do not use any feature of your already purchased database software like partitioning, parallel query or even constraints.

Oh but Siebel loves indexes, yes believe it or not they indexed nearly each column of their heacy OLTP write tables, so I think they earned this oscar properly :)

Last week my help was requested for a Siebel data migration. After several minutes of monitoring I thought of leaving the building immediately. The migration utility inside Siebel did not allow you to define hints, like APPEND, did not support parallel query option, indexes on huge tables were not allowed to be dropped, so huge UPDATEs and INSERTs produced enormous amount of redo etc. And the root cause conclusion was of course easy, I/O capability of the system was bad :)

Only thing I was able to help was taking the availability risk and keeping target database on NOARHIVELOG mode, creating huge(10 GB each) single membered redo log files, re-collecting appropriate optimizer statistics on source and increasing buffer cache on both ends up to the physical limits of the machines. Before these actions group was unhappy with 500,000 rows per hour processing and afterwards it was 3,000,000 rows per hour and everybody was thankful. But I believe this 20 days and night data migration could finish within hours if Siebel was only an Oracle database friendly application, meaning if it allowed dropping indexes before migration starts and rebuilding after migration ends, allowing CREATE TABLE as SELECT in PARALLEL and NOLOGGING instead of these huge UPDATEs and INSERTs. I experienced TBs of data moving around within hours with these simple data migration best practices.

So let’s hope maybe some day Oracle will force the tools it buys to have an Oracle friendly version also and lets its customers to utilize their purchases.

ps: if you want to continue reading on data migration experiences of mine please check these out;

Direct Path Load Operations versus Integrity Constraints, Triggers and Instance Recovery
http://tinyurl.com/6xwxyt

I am back :)
http://tinyurl.com/6gk2wo

Some Best Practices of Upgrading Oracle Database
http://tinyurl.com/5ds6hv