| Oracle - Performance Comparisoin : Implcit vs. Explicit Cursors |
|
Original Article :
http://otn.oracle.com/oramag/oracle/03-jan/o13asktom.html
|
Technology ASK TOM
On the Explicit, Size, and Complex By Tom Kyte
Is it true that from Oracle7 Release 7.3 onward, implicit cursors are optimized and they don't double-fetch? Also, why does the following implicit cursor run faster than the explicit cursor below it when table T has an index on column X, but otherwise the explicit cursor runs faster?
Implicit Cursor:
Select x into y from T where x = j;
Explicit Cursor:
cursor c(p number) is select x from blah where x = p; open c(j); fetch c into y; close c;
I'll start with a quick definition so that everyone understands what implicit
and explicit cursors are. In general, an implicit cursor is one that the programmer
does not "explicitly" declare, open, fetch from, or close; these operations
are implicit. So, in the above example, the SELECT X INTO Y query
is an implicit cursor. There is no "cursor cursor_name is ..." definition for
it. The second example, on the other hand, is the classic explicit cursor.
The programmer declared, opened, fetched, and closed it explicitly. Now, it
is a fact that implicit cursors are faster in PL/SQL than explicit cursors,
and implicit cursors have been faster since releases before Oracle7 Release
7.3. In fact, I have test cases that show this is true going back to Oracle7
Release 7.1 (see asktom.oracle.com/~tkyte/ivse.html for those tests). The reason
implicit cursors are faster (both cursor FOR LOOP implicit cursors as well
as SELECT INTO implicit cursors) is that the PL/SQL engine has a lot less of
your code to interpret and execute. The more PL/SQL can do under the covers,
the faster it will be in general. The implicit cursor above takes one line
of PL/SQL code; the explicit cursor takes at least three lines of code and,
if done "correctly," actually takes six lines of code. Your explicit code does
not do the work of the implicit cursor, which looks to make sure you are getting
at least one row and at most one row. Your explicit code is missing a lot of
stuff you need to do. In order to accurately compare your two cursor examples,
your explicit code should be more along the lines of:
open c(j); fetch c into y; if ( c%notfound ) then raise NO_DATA_FOUND; end if; fetch c into y; if ( c%found ) then raise TOO_MANY_ROWS; end if; close c;
If this were your explicit cursor, you would find that the explicit cursor is slower in all cases, even in your example with the index versus no index. Now, to get to the crux of your question: Why in your example without the index does the implicit cursor appear to perform so badly, whereas when there is an index, the implicit cursor wins? The answer lies in the work a full scan does, and the fact that your explicit test stops after exactly one row. I'll cook up an example to show you the difference between the two:
SQL create table t ( x int )
2 pctfree 99 pctused 1;
Table created.
SQL> insert into t
2 select rownum
3 from all_objects;
29264 rows created.
SQL> analyze table t compute statistics;
Table analyzed.
SQL> select blocks, empty_blocks, num_rows
2 from user_tables
3 where table_name = 'T';
BLOCKS EMPTY_BLOCKS NUM_ROWS
------------- ------------ -----------
4212 140 29264
I've created a table with a lot of blocks; the pctfree 99 value did that for me, reserving 99 percent of the block as "free space" for subsequent updates to the data. So, even though the amount of data in the table is small, the table itself is pretty big. Also, I had the INSERT put the values 1, 2, 3, ... up to 29,264 in the table pretty much in order. So, X=1 is on the "first" block of the table and X=29,000 is pretty near the last block in the table.
Next, I'll run a small PL/SQL block that will show the consistent gets performed by various implicit and explicit cursors against this data. Since there are no indexes, queries will do a full scan of the entire table. The differences in performance will be easy to quantify once I do this and review the results:
SQL> declare
2 l_last_cgets number default 0;
3 l_x number;
4 cursor c( p_x in number ) is
5 select x
6 from t
7 where x = p_x;
8
9 procedure cgets( p_msg in varchar2 )
10 is
11 l_value number;
12 begin
13 select b.value into l_value
14 from v$statname a, v$mystat b
15 where a.statistic# = b.statistic#
16 and a.name = 'consistent gets';
17
18 dbms_output.put_line( p_msg );
19 dbms_output.put_line
20 ( 'Incremental cgets: ' ||
21 to_char(l_value-l_last_cgets,
22 '999,999') );
23 l_last_cgets := l_value;
24 end;
25
26 begin
27 cgets('Starting');
28
29 open c(1);
30 fetch c into l_x;
31 close c;
32 cgets('Explicit to find X=1 ' ||
33 'stop at first hit' );
34
35 open c(1);
36 fetch c into l_x;
37 fetch c into l_x;
38 close c;
39 cgets('Explicit to find X=1 ' ||
40 'check for dups' );
41
42 select x into l_x
43 from t
44 where x = 1 AND rownum = 1;
45 cgets('Implicit to find X=1 ' ||
46 'stop at first hit' );
47
48 select x into l_x
49 from t
50 where x = 1;
51 cgets('Implicit to find X=1 ' ||
52 'check for dups' );
53
54 open c(29000);
55 fetch c into l_x;
56 close c;
57 cgets('Explicit to find X=29000');
58
59 select x into l_x
60 from t
61 where x = 29000;
62 cgets('Implicit to find X=29000');
63 end;
64 /
Starting
Incremental cgets: 514,690
Explicit to find X=1 stop at first hit
Incremental cgets: 4
Explicit to find X=1 check for dups
Incremental cgets: 4,220
Implicit to find X=1 stop at first hit
Incremental cgets: 4
Implicit to find X=1 check for dups
Incremental cgets: 4,219
Explicit to find X=29000
Incremental cgets: 4,101
Implicit to find X=29000
Incremental cgets: 4,219
PL/SQL procedure successfully completed.
Here you can see why in your example the explicit cursor appeared faster than
the implicit cursor. When I do the explicit cursor test and do just one fetch
for X=1, that query needs to scan very few blocks (very few consistent gets) in
order to find the answer. However, as soon as I make the explicit cursor do the
work of the implicit cursor, checking to make sure no other row satisfies that
same criteria, you see that the explicit cursor looks at each and every block
in the table. Now, I move on to the implicit cursor and see that if I tell it
also to stop at the very first hit, using ROWNUM=1, it does the same amount of
work as the explicit cursor. When it checks for a second row in the table matching
the criteria, you see that it again does the same number of consistent gets as
the explicit cursor does; it, too, had to finish full scan of the table to verify
that there was only one row with X=1. The interesting part is when I query for
X=29,000. Since that row is near the "end" of the table, no matter what approach
I take, the two queries will do about the same amount of work. They both must
scan almost the entire table to find the first row. Now, if there were an index
on X, both queries would tend to use an index range scan and both queries would
rapidly find that there is but one row, without having to do a full scan of the
table. This accounts for your cursor behavior: the SELECT INTO checked for a second
row, whereas your explicit cursor did not. If you compare apples to apples-either
do the second explicit fetch or add "rownum = 1" to the SELECT INTO-you will find
that both cursors do the same amount of work. In short, implicit cursors are great.
They execute faster than the same code with an explicit cursor, they are easier
to code (less code to type), and I personally find the code more readable and
understandable.
|
Copyright © : 1997 - 2005 |
 |
