Comparison of Three Types of cursor Cycle Efficiency in PL/SQL

Here we mainly compare the following three formats of cursor loops: 1. Single processing of open cursors; LOOPFETCH cursinto variables; EXITWHEN conditions; ENDLOOP; CLOSE game

Here we mainly compare the following three formats of cursor loops: 1. Single processing of open cursors; loop fetch cursinto variables; exit when conditions; end loop; CLOSE games

Here we mainly compare the cursor loops in the following three formats:

1. Single Processing

Open cursor;

LOOP

FETCH cursor INTO variable;

Exit when condition;

End loop;

CLOSE cursor;

2. Batch Processing

Open cursor;

FETCH cursor bulk collect into collection variable;

CLOSE cursor;

3. Implicit cursor

For x in (SQL statement) loop

... -- Logical Processing

End loop;

The above are several common cursor processing methods in the work. Generally, the batch processing speed is the best, the implicit cursor is the second, and the single processing is the worst, however, in my actual work, I found that most of them use the first type of cursor processing.

The reason is that you do not know about the efficiency of collection variables and batch processing.

Recommended reading:

Connect to a remote Oracle database under PL/SQL

PL/SQL ORA-14551: cannot perform DML operations in queries solved

Here, we will briefly test the efficiency of the above three types of cursors, and analyze the trace file to view the nature of the three processing methods.

-- Create a test table

00:09:54 SCOTT @ orcl> create table big_data as select 'cc' | mod (level, 8) a, 'dd' |
Mod (level, 13) B from dual connect by level <1000000;

Table created.

Elapsed: 00:00:05. 87
At 00:11:17 SCOTT @ orcl> select count (*) from big_data;

COUNT (*)
----------
999999

1 row selected.

Elapsed: 00:00:00. 07

-- Execute plsql blocks processed by the cursor in the preceding three methods respectively

At 00:11:21 SCOTT @ orcl> declare
00:17:54 2 cursor c_a is
00:17:54 3 select a from big_data;
00:17:54
00:17:54 5 v_a big_data.a % type;
00:17:54 begin
00:17:54 open c_a;
00:17:54 loop
00:17:54 9 fetch c_a into v_a;
00:17:54 10 exit when c_a % notfound;
00:17:54 end loop;
00:17:54 close c_a;
00:17:54 end;
00:17:56/

PL/SQL procedure successfully completed.

Elapsed: 00:00:07. 42
At 00:18:05 SCOTT @ orcl> declare
00:19:56 2 cursor c_a is
00:19:56 3 select a from big_data;
00:19:56
00:19:56 5 type t_a is table of c_a % rowtype;
00:19:56 6 v_a t_a;
00:19:56 7 begin
00:19:56 8 open c_a;
00:19:56 -- Batch Processing
00:19:56 10 fetch c_a bulk collect into v_a;
Close c_a at 00:19:56;
00:19:56 12 end;
00:19:57/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00. 64
At 00:22:55 SCOTT @ orcl> declare
00:23:18 2 v_a big_data.a % type;
00:23:18 3 begin
00:23:18 4 -- Batch Processing
00:23:18 5 for x in (select a from big_data) loop
00:23:18 6 v_a: = x.;
00:23:18 7 end loop;
00:23:18 end;
00:23:18/

PL/SQL procedure successfully completed.

Elapsed: 00:00:00. 79

Note: ensure that the plsql_optimize_level parameter is 2. If it is not 2, the test results may be different.
Note the time consumed by comparison. 1 is 7.42 s, 2 is 0.64 s, and 3 is 0.79 s.

Continue to read:

,