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
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
