SQL> create or replace
2 procedure p
3 is
4 l_cursor sys_refcursor;
5 l_rec dual%rowtype;
6 cursor c is select
from dual d1;
7 begin
8 open c;
9 fetch c into l_rec;
10 close c;
11
12 open l_cursor for select *
from dual d2;
13 fetch l_cursor into l_rec;
14 close l_cursor;
15 end;
16 /
Code Listing 4: 1,000-column table created, queried, and reported
declare
l_create long := 'create table t ( c1 number';
begin
for i in 2 .. 1000
loop
l_create := l_create || ',c'||i||' number default ' || i;
end loop;
execute immediate l_create || ')';
exec dbms_stats.gather_table_stats( user, 'T' );
SELECT C1 FROM T
Procedure created.
call count cpu elapsed disk query current rows
————————— ———————— ——————————— —————————————— —————————————— —————————————— —————————————— ——————————
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 7230 6. 41 15. 72 414610 420920 0722790
————————— ———————— ——————————— —————————————— —————————————— —————————————— —————————————— ——————————
total 7241 6. 41 15. 72 414610 420920 0722790
Now, if I run this procedure in SQL*Plus
10 times, using “exec p;” with SQL tracing
enabled, my TKPROF report will show the
following:
SELECT FROM DUAL D1
SELECT C1000 FROM T
call count cpu elapsed disk query current rows
————————— ———————— ——————————— —————————————— —————————————— —————————————— —————————————— ——————————
Parse 1 0.00 0.00 0 0 0 0
Execute 10 0.00 0.00 0 0 0 0
Fetch 7230 8. 66 17.93 421260 3304860 0722790
————————— ———————— ——————————— —————————————— —————————————— —————————————— —————————————— ——————————
total 7241 8. 66 17.94 421260 3304860 0722790
call count
————————— ————————
Parse 1
Execute 10
Fetch 10
————————— ————————
total 21
SELECT FROM DUAL D2
call count
————————— ————————
Parse 10
Execute 10
Fetch 10
————————— ————————
total 30
Note how both SQL statements were exe-
cuted 10 times apiece, which was expected,
because I ran the code 10 times, but the query
associated with the ref cursor was also parsed
10 times, as compared to the regular cursor’s
query, which was parsed only once. In PL/SQL,
regular cursors are cached in an open state in
the hope that you’ll execute them again.
PL/SQL can then skip the entire parse
process, which can lead to a significant reduc-
tion in CPU utilization. (If you don’t want
to take my word for it, watch this video and
be convinced: bit.ly/zPMeVw.) This PL/SQL
cursor cache is controlled by the SESSION_
CACHED_CURSORS database initialization
parameter and is 100 percent transparent to
you and completely nonintrusive. By that, I
mean that PL/SQL’s caching of cursors open
will not affect your programming at all. If the
number of open cursors (not PL/SQL cached
cursors) needs to exceed the number of cur-
rently free open cursor slots, PL/SQL will
silently start closing the cached open cursors.
PL/SQL is not taking a chunk of open cursor
slots away from you—it just transparently
uses the ones you are not currently using.
