Sunday, October 3, 2010

refcursor- reparse

The refcursor makes the underlying query to go for reparsing all the time when run though the query is not changed.

Let say the following procedure is created successfully and ran 6 times.

CREATE OR REPLACE PROCEDURE "SRIKAR"."P"
as
l_cursor sys_refcursor;
l_rec dual%rowtype;
cursor c is select * from dual d2;
begin
open l_cursor for select * from dual d1;
fetch l_cursor into l_rec;
close l_cursor;
open c;
fetch c into l_rec;
close c;
end;

The above procedure is executed 6 times.
When I look at the TKPROF output,



SELECT * FROM DUAL D1 is parsed 6 times as below

No comments: