Version Details
SQL> SELECT *
2 FROM V$VERSION;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Hello Experts,
My source table v_src consists of 10million rows ,I need to process them(Huge processing) and insert into two destination tables .Currently this task is taking 10hours in our production environament.The code was developed long back almmost 10years ago which is slow-by-slow process full of cursors.
Now the task is assignedto me to tune ,After doing R&D as this is a batch job which runs with ample CPU resources I've choosen
1) Pipelined Parallel_Enable function and partitioned my ref cursor which is just 'select * from v_src' by any.
2)Provided parallel hints in my ref cursor query
3)Used bulk collect and piping 1000 rows
4)Altered session enabled parallel dml
5)Provided parallel hint in my dmls without any degree of parallelsim.
6)Doing multi-table insert into my two destination tables using Insert All When.
7)Disabled all my indexes on destination tables
)Enabled nologging on my destination tables.
The reason for posting this question is to seek some suggestion from you adepts .You experts might have successfully dealt with more than 10million rows.Please suggest me if I'm missing any thing that can fasten things further.
Edited by: RGH on May 22, 2011 7:24 AM