Hi All,
I finally understood what I have done for this test case.
I am actually performing a (noarchivelog mode) restore from an older RMAN backup.
It works perfectly.
Regarding the data load performance issue on a archive log mode database, I tested it out loading the same millions of records from SQLPLUS and the timings for noarchivelog and archivelog are the same.
So, I suspect the issue might be on the datawarehouse loading engine.
Thanks all for your wonderful comments.
Cheers
Oracbase