For every SQL statement execution certain area in memory is allocated on the Shared Pool portion of the SGA. PL/SQL allow you to name this area. This private SQL area is called context area or cursor. A cursor acts as a handle or pointer into the context area. A PL/SQL program controls the context area using the cursor. Cursor represents a structure in memory and is different from cursor variable.
When you declare a cursor, you get a pointer variable, which does not point any thing. When the cursor is opened, memory is allocated and the cursor structure is created. The cursor variable now points the cursor. When the cursor is closed the memory allocated for the cursor is released.
Cursors allow the programmer to retrieve data from a table and perform actions on that data one row at a time. There are two types of cursors implicit cursors and explicit cursors.
IMPLICIT CURSORS
PL/SQL issues an implicit cursor whenever you execute a SQL statement directly in your code, as long as that code does not employ an explicit cursor. It is called an "implicit" cursor because you, the developer, do not explicitly declare a cursor for the SQL statement.
In PL/SQL, you can refer to the most recent implicit cursor as the SQL cursor, which always has the attributes %FOUND, %ISOPEN, %NOTFOUND, and %ROWCOUNT.
EXPLICIT CURSORS
An explicit cursor is a SELECT statement that is explicitly defined in the declaration section of your code and, in the process, assigned a name. There is no such thing as an explicit cursor for UPDATE, DELETE, and INSERT statements.
With explicit cursors, you have complete control over how to access information in the database. You decide when to OPEN the cursor, when to FETCH records from the cursor (and therefore from the table or tables in the SELECT statement of the cursor) how many records to fetch, and when to CLOSE the cursor. Information about the current state of your cursor is available through examination of the cursor attributes. This granularity of control makes the explicit cursor an invaluable tool for your development effort.
An explicit cursor is a named query that is defined as a cursor in PL/SQL by use of the keyword CURSOR. UPDATE, DELETE and INSERT statements always use an implicit cursor (because they are not named).
The difference between explicit and implicit cursors in Oracle PL SQL is that explicit cursors give you complete control over opening, closing and fetching from an explicit cursor, including the number of rows fetched. Also, fetching from an explicit cursor will never raise a NO_DATA_FOUND or a TOO_MANY_ROWS exception.
TEST ENVIROMENT
Database: Oracle 10g Express edition 10.2.0.1.0
Server: Windows XP Proffessional, 512 MB of RAM, P4 2.4 Ghz
Client: Same machine, SQL*Plus on Windows Console
TEST CASE #1
create or replace procedure impl_cursor as
v_row dba_source%ROWTYPE;
begin
select * into v_row from dba_source where line=9999;
dbms_output.put_line(v_row.text);
end;
create or replace procedure expl_cursor as
v_row dba_source%ROWTYPE;
cursor test_cur is select * from dba_source where line=9999;
begin
open test_cur;
fetch test_cur into v_row;
close test_cur;
dbms_output.put_line(v_row.text);
end;
SQL> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> exec expl_cursor;
PL/SQL procedure successfully completed.
SQL> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> exec impl_cursor;
PL/SQL procedure successfully completed.
SQL> exec runStats_pkg.rs_stop;
Run1 ran in 14 hsecs
Run2 ran in 10 hsecs
run 1 ran in 140% of the time
Name Run1 Run2 Diff
STAT...free buffer requested 0 1 1
STAT...opened cursors cumulati 4 5 1
STAT...execute count 6 7 1
LATCH.undo global data 5 6 1
LATCH.cache buffers lru chain 1 0 -1
LATCH.KMG MMAN ready and start 0 1 1
STAT...session cursor cache hi 4 5 1
STAT...table scans (short tabl 1 2 1
STAT...parse count (total) 4 5 1
STAT...bytes received via SQL* 1,141 1,139 -2
LATCH.messages 0 2 2
STAT...active txn count during 4 6 2
STAT...cleanout - number of kt 4 6 2
STAT...calls to kcmgcs 4 6 2
LATCH.object queue header oper 2 0 -2
STAT...redo entries 9 11 2
STAT...CPU used by this sessio 10 7 -3
LATCH.library cache 61 65 4
LATCH.library cache pin 48 52 4
STAT...recursive cpu usage 10 6 -4
STAT...Elapsed Time 16 12 -4
STAT...calls to get snapshot s 6 11 5
STAT...consistent changes 17 23 6
STAT...recursive calls 4 10 6
STAT...db block gets 17 25 8
STAT...db block gets from cach 17 25 8
STAT...db block changes 26 35 9
STAT...bytes sent via SQL*Net 650 637 -13
STAT...CPU used when call star 23 7 -16
STAT...DB time 27 7 -20
LATCH.simulator lru latch 189 236 47
LATCH.simulator hash latch 189 236 47
STAT...undo change vector size 2,128 2,268 140
STAT...index fetch by key 4,086 4,244 158
STAT...buffer is pinned count 8,124 8,439 315
STAT...consistent gets - exami 8,177 8,495 318
STAT...redo size 2,776 3,120 344
STAT...table scan blocks gotte 596 1,222 626
STAT...no work - consistent re 598 1,224 626
STAT...consistent gets 8,784 9,733 949
STAT...consistent gets from ca 8,784 9,733 949
STAT...session logical reads 8,801 9,758 957
LATCH.cache buffers chains 9,489 11,074 1,585
STAT...table scan rows gotten 50,650 103,488 52,838
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
10,226 11,914 1,688 85.83%
PL/SQL procedure successfully completed.
Download Code
TEST CASE #2
create or replace procedure impl_cursor_bulk as
TYPE test1_tab IS TABLE OF dba_source%ROWTYPE;
t_tab test1_tab;
begin
select * bulk collect into t_tab from dba_source;
dbms_output.put_line(t_tab.COUNT);
end;
create or replace procedure expl_cursor_bulk as
TYPE test1_tab IS TABLE OF dba_source%ROWTYPE;
t_tab test1_tab;
cursor test_cur is select * from dba_source;
begin
open test_cur;
fetch test_cur bulk collect into t_tab;
close test_cur;
dbms_output.put_line(t_tab.COUNT);
end;
SQL> exec runStats_pkg.rs_start;
PL/SQL procedure successfully completed.
SQL> exec expl_cursor_bulk;
292566
PL/SQL procedure successfully completed.
SQL> exec runStats_pkg.rs_middle;
PL/SQL procedure successfully completed.
SQL> exec impl_cursor_bulk;
292566
PL/SQL procedure successfully completed.
SQL> exec runStats_pkg.rs_stop;
Run1 ran in 267 hsecs
Run2 ran in 213 hsecs
run 1 ran in 125,35% of the time
Name Run1 Run2 Diff
LATCH.parameter table allocati 0 1 1
LATCH.resmgr:free threads list 0 1 1
LATCH.resmgr:actses active lis 0 1 1
LATCH.process group creation 0 1 1
LATCH.process allocation 0 1 1
LATCH.simulator hash latch 1,700 1,701 1
STAT...physical read total IO 334 335 1
STAT...recursive calls 3 2 -1
LATCH.simulator lru latch 731 732 1
LATCH.Consistent RBA 0 1 1
LATCH.lgwr LWN SCN 0 1 1
LATCH.mostly latch-free SCN 0 1 1
LATCH.FOB s.o list latch 0 1 1
LATCH.OS process: request allo 0 1 1
LATCH.OS process allocation 1 2 1
STAT...physical read IO reques 334 335 1
LATCH.channel handle pool latc 0 1 1
LATCH.session timer 1 2 1
LATCH.dummy allocation 0 1 1
STAT...physical reads cache pr 4,794 4,796 2
STAT...consistent gets - exami 7 9 2
STAT...physical read total mul 331 333 2
STAT...calls to kcmgcs 4 6 2
STAT...cleanout - number of kt 4 6 2
STAT...active txn count during 4 6 2
STAT...bytes received via SQL* 1,146 1,144 -2
LATCH.JS slv state obj latch 0 2 2
LATCH.redo writing 3 6 3
LATCH.cache buffers lru chain 5,128 5,131 3
STAT...physical reads 5,128 5,131 3
LATCH.OS process 0 3 3
STAT...physical reads cache 5,128 5,131 3
STAT...redo entries 9 12 3
STAT...free buffer requested 5,128 5,131 3
LATCH.compile environment latc 3 0 -3
LATCH.In memory undo latch 6 2 -4
STAT...consistent gets from ca 6,419 6,423 4
LATCH.session allocation 6 2 -4
STAT...consistent gets 6,419 6,423 4
LATCH.multiblock read objects 662 666 4
LATCH.object queue header oper 10,257 10,263 6
LATCH.library cache lock 12 6 -6
LATCH.messages 15 21 6
STAT...workarea memory allocat -3 3 6
LATCH.shared pool 5 11 6
STAT...db block gets from cach 17 24 7
STAT...consistent changes 17 24 7
STAT...db block gets 17 24 7
STAT...free buffer inspected 5,129 5,121 -8
STAT...hot buffers moved to he 9 0 -9
STAT...db block changes 26 36 10
LATCH.active service list 0 11 11
STAT...session logical reads 6,436 6,447 11
STAT...bytes sent via SQL*Net 644 631 -13
LATCH.session idle bit 41 27 -14
LATCH.dml lock allocation 16 0 -16
STAT...CPU used by this sessio 233 209 -24
STAT...recursive cpu usage 224 200 -24
LATCH.cache buffers chains 18,101 18,075 -26
STAT...CPU used when call star 244 209 -35
LATCH.JS queue state obj latch 0 36 36
STAT...user I/O wait time 48 8 -40
LATCH.library cache pin 104 54 -50
STAT...Elapsed Time 269 214 -55
LATCH.row cache objects 135 78 -57
LATCH.enqueue hash chains 18 79 61
LATCH.library cache 139 69 -70
STAT...DB time 282 210 -72
LATCH.enqueues 5 79 74
STAT...undo change vector size 2,136 2,340 204
STAT...redo size 2,784 3,204 420
STAT...physical read bytes 42,008,576 42,033,152 24,576
STAT...physical read total byt 42,008,576 42,033,152 24,576
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
37,491 37,472 -19 100.05%
PL/SQL procedure successfully completed.
Download Code
TEST CASE #3
create table tcur ( x number primary key ) ;
insert into tcur
select rownum x
from all_objects
where rownum < 1000;
analyze table tcur compute statistics;
create or replace procedure imp_test is
y number;
begin
for i in 1 .. 50 loop
for j in 1 .. 999 loop
select x
into y
from tcur
where x = j;
end loop;
end loop;
end;
/
create or replace procedure exp_test is
cursor c(p number) is
select x
from tcur
where x = p;
y number;
begin
for i in 1 .. 50 loop
for j in 1 .. 999 loop
open c(j);
fetch c into y;
close c;
end loop;
end loop;
end;
/
SQL> set timing on
SQL> exec imp_test
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.15
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> exec exp_test
PL/SQL procedure successfully completed.
Elapsed: 00:00:03.26
Download Code
You can download the presentation and presentation notes from here.
You can also download the 10046 SQL Trace and tkprof output files of the test case #3 from here.
CONCLUSION
Pick any tuning manual, and you'll often see a section about coding with explicit cursors as opposed to implicit ones. The argument seems sound, typically going along the lines of:
"With an explicit cursor, you can open, fetch and close, whereas with an implicit cursor must issue open, fetch, second fetch, close - the second fetch being needed to check for a too many rows exception.
However, since 7.3, those cunning people at Oracle have worked around this problem and the second fetch is no longer required. Because PL/SQL is interpreted (watch out for the new native compile option in 9i), implicit cursors will actually run FASTER than explicit since typically you are using less code to achieve the same task.
Performance: Nearly same.
If you use an explicit cursor, you are more likely (or at least able) to reuse that cursor, which increases the chance that it will be pre-parsed in shared memory when needed.
In previous versions, implicit cursors cannot handle multiple rows. You have to be sure that a particular SELECT statement will return only one row: That’s an old issue!
Implicit cursors are easier to handle and less code to write.
Explicit cursors give you complete control over opening, closing and fetching from an explicit cursor, including the number of rows fetched.
If you don’t have to use explicit cursors, use implicit!
|
Set as Homepage
Add to Favourites
Recommend
Contact
