DBA Hub

📋Steps in this guide1/3

Implicit vs. Explicit Cursors in Oracle PL/SQL

A comparison of the relative performance of implicit and explicit cursors in Oracle PL/SQL.

oracle miscconfigurationintermediate
by OracleDba
13 views
1

SELECT INTO vs. FETCH (1)

The point of this example is to compare the performance of a single , an implicit cursor, and , an explicit cursor. Since both these actions are really quick of an individual statement, we will repeat them in a loop to magnify the impact of the difference. Remember that a real system will be running many individual queries, so although each may appear to be quick on it's own, any unnecessary performance overhead, like , will affect whole system performance. This clearly demonstrates the implicit cursor is measurably faster than the explicit cursor.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
SET SERVEROUTPUT ON
DECLARE
  l_loops  NUMBER := 100000;
  l_dummy  dual.dummy%TYPE;
  l_start  NUMBER;

  CURSOR c_dual IS
    SELECT dummy
    FROM   dual;
BEGIN
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    OPEN  c_dual;
    FETCH c_dual
    INTO  l_dummy;
    CLOSE c_dual;
  END LOOP;

  DBMS_OUTPUT.put_line('Explicit: ' || 
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    SELECT dummy
    INTO   l_dummy
    FROM   dual;
  END LOOP;

  DBMS_OUTPUT.put_line('Implicit: ' || 
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END;
/
Explicit: 273 hsecs
Implicit: 234 hsecs

PL/SQL procedure successfully completed.

SQL>
2

SELECT INTO vs. FETCH (2)

The implicit cursor is not only faster, but it is actually doing more work, since it includes a and a exception check. If we manually code the to do logically the same amount of work, the comparison would look like that listed below. This makes the performance difference between the explicit and implicit cursor even more extreme.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
SET SERVEROUTPUT ON
DECLARE
  l_loops  NUMBER := 100000;
  l_dummy  dual.dummy%TYPE;
  l_start  NUMBER;

  CURSOR c_dual IS
    SELECT dummy
    FROM   dual;
BEGIN
  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    OPEN  c_dual;
    FETCH c_dual
    INTO  l_dummy;

    IF (c_dual%NOTFOUND) THEN
      RAISE NO_DATA_FOUND;
    END IF; 

    FETCH c_dual
    INTO l_dummy;
    IF (c_dual%FOUND) THEN
      RAISE TOO_MANY_ROWS;
    END IF;
    CLOSE c_dual;
  END LOOP;

  DBMS_OUTPUT.put_line('Explicit: ' || 
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR i IN 1 .. l_loops LOOP
    SELECT dummy
    INTO   l_dummy
    FROM   dual;
  END LOOP;

  DBMS_OUTPUT.put_line('Implicit: ' || 
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END;
/
Explicit: 402 hsecs
Implicit: 234 hsecs

PL/SQL procedure successfully completed.

SQL>
3

Cursor FOR Loops

The performance impact of using is also evident in a comparison with a cursor loop, as shown in the example below. As expected, manually performing a fetch has a negative impact on performance. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
SET SERVEROUTPUT ON
DECLARE
  l_obj    all_objects%ROWTYPE;
  l_start  NUMBER;

  CURSOR c_obj IS
    SELECT *
    FROM   all_objects;
BEGIN
  l_start := DBMS_UTILITY.get_time;

  OPEN  c_obj;
  LOOP
    FETCH c_obj 
    INTO  l_obj;

    EXIT WHEN c_obj%NOTFOUND;

    -- Do something.
    NULL;
  END LOOP;
  CLOSE c_obj;

  DBMS_OUTPUT.put_line('Explicit Fetch Loop: ' || 
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR cur_rec IN c_obj LOOP
    -- Do something.
    NULL;
  END LOOP;

  DBMS_OUTPUT.put_line('Explicit For Loop  : ' || 
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');

  l_start := DBMS_UTILITY.get_time;

  FOR cur_rec IN (SELECT * FROM all_objects) LOOP
    -- Do something.
    NULL;
  END LOOP;

  DBMS_OUTPUT.put_line('Implicit For Loop  : ' || 
                       (DBMS_UTILITY.get_time - l_start) || ' hsecs');
END;
/
Explicit Fetch Loop: 226 hsecs
Explicit For Loop  : 94 hsecs
Implicit For Loop  : 93 hsecs

PL/SQL procedure successfully completed.

SQL>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!