how to generate execution plan for oracle loop

Question

How can I generate execution plan for an oracle loop query like below:

BEGIN 
  FOR L IN (SELECT FIRST_NAME, LAST_NAME, SOME_ID FROM TABLE1)
  LOOP
      INSERT INTO TABLE2 (FIRSTNAME, LASTNAME)
      (SELECT FNAME, LASTNAME FROM TABLE2 WHERE SOME_ID = L.SOME_ID)

  V_CNT := V_CNT + 1;

  IF (MOD(V_CNT, 1000)=0) THEN
    COMMIT;
  END IF;

END LOOP;

How can I generate execution plan for a block like above?

I asked similar question earlier and decided to extract blocks from my stored procedure and generate execution plans for the individually. Though I know how to generate plans for simple queries, I dont know how to generate them for loop blocks like above.

Eddie Awad · Answer 1 · 2010-12-22T18:13:53.237

3

Your PL/SQL block can be re-written in SQL:

INSERT INTO table2 (firstname, lastname)
   SELECT fname, lastname
     FROM table2
    WHERE some_id IN (SELECT some_id FROM table1);

You can then "explain plan" the above INSERT.

If you have to do batch commits then using BULK COLLECT ... LIMIT and FORALL is more efficient than a cursor for loop, especially if you are processing millions of rows. Useful links:

edited Dec 22 '10 at 18:13

answered Dec 22 '10 at 17:24

Eddie Awad

3,669
1
19
17

I believe he wants intermediate commits. On a huge table this can run out of `UNDO` space. – Quassnoi Dec 22 '10 at 17:26
Ah! Then I believe BULK COLLECT ... LIMIT/FORALL is a good solution. – Eddie Awad Dec 22 '10 at 18:16

Quassnoi · Accepted Answer · 2010-12-22T17:22:43.163

You can't generate execution plans for PL/SQL blocks, only for queries.

Execution plan shows how declarative commands (SQL) are executed under the hood, i. e. what procedures are used. It transforms the declarative constructs (set operations) into procedures (nested loops, hash joins, sorts etc).

PL/SQL, being, as the name suggests, a procedural language, defines the execution plan itself — nested loops in your case.

If you want to see the execution plan for the inner query, you should copy it and issue EXPLAIN PLAN FOR …

Note that this can be more efficiently rewritten as:

BEGIN 
        FOR L IN
        (
        SELECT  MIN(some_id) AS minid, MAX(some_id) AS maxid
        FROM    (
                SELECT  some_id, rownum AS rn
                FROM    (
                        SELECT  some_id
                        FROM    table1
                        ORDER BY
                                some_id
                        )
                )
        GROUP BY
                TRUNC(rn / 1000)
        )
        LOOP
                INSERT
                INTO    TABLE2 (FIRSTNAME, LASTNAME)
                SELECT  FNAME, LASTNAME
                FROM    table1
                JOIN    table2
                ON      table2.some_id = table1.some_id
                WHERE   table1.some_id BETWEEN l.minid AND l.maxid
                V_CNT := V_CNT + 1;
                IF (MOD(V_CNT, 1000)=0) THEN
                        COMMIT;
                END IF;
        END LOOP;
END;

ok. Lets say if I take the above query out of a loop and make it just simple one query and generate the execution plan. If I analyze that execution plan, would it be comparable to when the same query runs in a loop and commits an insert ever 1000 recods? — learn_plsql, Dec 22 '10 at 17:13
@learn_plsql: yes, plus the overhead of switching between `SQL` and `PL/SQL`. Actually, why don't you just use a join? — Quassnoi, Dec 22 '10 at 17:15
oh thats another story about not using join. it is inserting millions of queries into the table so we were advised (more like imposed) by our dba to use loops. — learn_plsql, Dec 22 '10 at 17:19
@learn_plsql: show this query to your DBA. It makes intermediate commits too but with `SQL / PL/SQL` switching greatly reduced. — Quassnoi, Dec 22 '10 at 17:24

score 1 · Answer 3 · answered Dec 22 '10 at 20:01

If you are interested in the performance of your procedural PL/SQL code you may want to investigate PL/SQL Profiling.

The Profiler allows you to collect information about the run-time performance of your PL/SQL. After the profiling run is complete you can report on the results and view information like # of times each line was executed, average execution time of each line. This can help you identify bottlenecks and should point you to the first things that may need tuning attention.

Your biggest potential payoff is by looking at the worst performing code and making it run better. Once that is improved, profile again to verify your improvements and to find the next statement that may need attention.

A good first introduction is here: Oracle's DBMS Profiler

how to generate execution plan for oracle loop

3 Answers3