4 Replies Latest reply: Apr 19, 2014 7:10 AM by Jjun.Tan RSS

    Applying a time taking sql script in parallel using pl/sql function

    orausern

      Hi Experts,

       

      I am on Oracle 11.2.0.3 on Linux. I have a sql script that runs for about 3 hours and I am checking if there is a way to make it go faster by using the pl/sql package : dbms_parallel_execute. Other appoaches of trying out with indexes etc. have already been used and after that it still takes 3 hours and so I am checking of how to make it go faster by using this pacakge.  The actualy sql script inserts huge amount of CLOB data and is likely to take 3 hours in production. My question is how to use this pacakge to apply it in chunks. I mean how to specify the chunks for this sql:

       

      Here is the simplified form of the sql and my actual sql is very similar with changed table names and it inserts huge amount of CLOBs:

       

      INSERT INTO emp1
         SELECT *
           FROM emp2 a
          WHERE     a.empno IN (SELECT empno
                                      FROM emp3
                                     WHERE name LIKE 'A%');
      

       

       

       

      My quesiton is how to specify chunks for the above sql using dbms_parallel_execute? I have not used this package and will be thankful for suggestions.

       

      OrauserN