SQL Tuning Technics-Recursive Procedures

SQL Parsing in Recursive Procedures
Reduce the parse-to-execution ratio in your applications.
Description: Extensive SQL parsing can become a serious problem for a heavily loaded Oracle instance. This is especially true if a SQL statement executed many times is also parsed many times. There are a number of standard techniques and guidelines, which help reduce unnecessary parsing. In some special cases, though, even following all the guidelines does not save you from extensive parsing. Find out below how to apply a workaround in a specific situation to further reduce the parse-to-execute ratio of a statement.
The effect can be observed when a SQL cursor is used in a recursively-structured procedure. In such procedures, very often a cursor is opened and for each fetched row the same procedure is called, which opens the same cursor, etc. In most of the cases the recursive call is executed before the cursor is closed. As a result of this, for each new "cursor open" a parse call is executed resulting in as many parses as executions.
Take a look at a simplified recursive procedure using the SCOTT schema:

PROCEDURE recurs (p_mgr IN emp.mgr%TYPE)
IS
    CURSOR emp_mgr IS
    SELECT empno
    FROM emp
    WHERE mgr = p_mgr;

BEGIN
    FOR c IN emp_mgr
    LOOP
        recurs(c.empno);
    END LOOP;
END recurs;
As you can see the recursive call is executed before the (implicit) cursor is closed. The main idea for reducing the parse calls is to first collect the results of the cursor (for example in a PL/SQL table), then close the cursor and finally cycle through the results and perform the recursive procedure calls.
See below and example of such procedure (In this procedure I have used a bulk bind select, but a normal cursor loop can be used too):

PROCEDURE recurs_close (p_mgr IN emp.mgr%TYPE)
IS
    CURSOR emp_mgr IS
    SELECT empno
    FROM emp
    WHERE mgr = p_mgr;
    TYPE t_empno IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
    p_empno t_empno;
    i PLS_INTEGER := 0;
BEGIN

    OPEN emp_mgr;
    FETCH emp_mgr BULK COLLECT INTO p_empno;
    i := emp_mgr%ROWCOUNT;
    CLOSE emp_mgr;
    FOR j IN 1..i
    LOOP
        recurs_close(p_empno(j));
    END LOOP;
END recurs_close;
In the excerpts of the trace files generated during the procedure execution can be seen that the first procedure has as many parses as executions (14), while the second has 1 parse only.


exec cursor_parse.recurs(7839);

SELECT empno
    FROM emp
    WHERE mgr = :b1


call     count    cpu    elapsed    disk      query    current rows
------- ------  ----- ---------- ------- ---------- ---------- ----------
Parse       14   0.02       0.15       0          0          0 0
Execute     14   0.00       0.00       0          0          0 0
Fetch       27   0.00       0.05       1         26         28 13
------- ------  ----- ---------- ------- ---------- ---------- ----------
total       55   0.02       0.20       1         26         28 13


exec cursor_parse.recurs_close(7839);

SELECT empno
    FROM emp
    WHERE mgr = :b1


call    count    cpu     elapsed  disk  query  current rows
------- -----  ---- -------  ----  -----  ------- --------
Parse       1     0.00    0.00     0      0        0 0
Execute    14   0.00    0.00     0      0        0 0
Fetch      14     0.00    0.00     0     14       28 13
------- -----  ---- ------- ----- ------ -------- --------
total      29  0.00    0.00     0     14       28 13

Most of the important statistics are better for the execution of the recurs_close than the recurs procedure.

Statistic name                          recurs  recurs_close
opened cursors cumulative           26      12
recursive calls                                               89      50
session logical reads                       84      72
consistent gets                            41      29
no work - consistent read gets          32      20
cursor authentications                        2       1
parse count (total)                        26      12