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

Comparison of the Efficiency of three types of cursor loops in pl/SQL mainly compares the following three formats:

1. one open cursor is processed; loop fetch cursor INTO variable; exit when condition; end loop; CLOSE cursor; 2. batch Processing of open cursors; FETCH cursbulk collect into collection variables; CLOSE cursors; 3. implicit cursor for x in (SQL statement) loop... -- end loop for logical processing;

 

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. 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 Big table [SQL] 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 00:11:17 SCOTT @ orcl> select count (*) from big_data; COUNT (*) ---------- 999999 1 row selected. elapsed: 00:00:00. 07 -- execute the plsql block [SQL] 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 4 00:17:54 5 v_a big_data.a % type; 00:17:54 6 begin 00:17:54 7 open c_a; 00:17:54 8 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 12 close c_a; 00:17:54 end; 00:17:56 14/PL/SQL procedure successfully completed. elapsed: 00:00:07. 42 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 4 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 9 -- batch processing 00:19:56 10 fetch c_a bulk collect into v_a; 00:19:56 11 close c_a; 00:19:56 end; 00:19:57 13/PL/SQL procedure successfully completed. elapsed: 00:00:00. 64 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. a; 00:23:18 7 end loop; 00:23:18 8 end; 00:23:18 9/PL/SQL procedure successfully completed. elapsed: 00:00:00. 79

 

Note the consumption time of comparison. 1 is 7.42 s, 2 is 0.64 s, and 3 is 0.79 s. Before executing the pl/SQL block, run the statement: alter session set SQL _trace = true; to view the trace file later. the trace file in the first cursor mode is as follows: (single processing)

PARSING IN CURSOR #7 len=181 dep=0 uid=84 oct=47 lid=84 tim=1357453194221500 hv=4093379502 ad='3ab9f6ec' sqlid='3nz96vvtzs0xf'declare  cursor c_a is    select a from big_data;  v_a big_data.a%type;begin  open c_a;  loop    fetch c_a into v_a;    exit when c_a%notfound;  end loop;  close c_a;end;END OF STMTPARSE #7:c=7998,e=8406,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1357453194221495=====================PARSING IN CURSOR #4 len=444 dep=2 uid=84 oct=3 lid=84 tim=1357453194225811 hv=1611503607 ad='3ab64c10' sqlid='c7tu1h9h0v5zr'SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),:"SYS_B_0"), NVL(SUM(C2),:"SYS_B_1") FROM (SELECT /*+ NO_PARALLEL("BIG_DATA") FULL("BIG_DATA") NO_PARALLEL_INDEX("BIG_DATA") */ :"SYS_B_2" AS C1, :"SYS_B_3" AS C2 FROM "BIG_DATA" SAMPLE BLOCK (:"SYS_B_4" , :"SYS_B_5") SEED (:"SYS_B_6") "BIG_DATA") SAMPLESUBEND OF STMTPARSE #4:c=2000,e=1958,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=0,tim=1357453194225807*** 2013-01-06 14:19:54.284EXEC #4:c=3998,e=58289,p=0,cr=0,cu=0,mis=1,r=0,dep=2,og=1,plh=3098652591,tim=1357453194284371FETCH #4:c=18997,e=19074,p=0,cr=55,cu=0,mis=0,r=1,dep=2,og=1,plh=3098652591,tim=1357453194303593STAT #4 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=55 pr=0 pw=0 time=0 us)'STAT #4 id=2 cnt=27300 pid=1 pos=1 obj=75053 op='TABLE ACCESS SAMPLE BIG_DATA (cr=55 pr=0 pw=0 time=130371 us cost=19 size=61752 card=5146)'CLOSE #4:c=0,e=86,dep=2,type=0,tim=1357453194318217=====================PARSING IN CURSOR #6 len=22 dep=1 uid=84 oct=3 lid=84 tim=1357453194318768 hv=3992159408 ad='3aae4de0' sqlid='3w21sgzqz715h'SELECT A FROM BIG_DATAEND OF STMTPARSE #6:c=28995,e=96556,p=0,cr=56,cu=0,mis=1,r=0,dep=1,og=1,plh=3104650627,tim=1357453194318766EXEC #6:c=0,e=31,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3104650627,tim=1357453194318875FETCH #6:c=0,e=405,p=20,cr=4,cu=0,mis=0,r=1,dep=1,og=1,plh=3104650627,tim=1357453194319360FETCH #6:c=0,e=13,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=3104650627,tim=1357453194319425FETCH #6:c=0,e=6,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=3104650627,tim=1357453194319463FETCH #6:c=0,e=5,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=3104650627,tim=1357453194319496FETCH #6:c=0,e=7,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=3104650627,tim=1357453194319531FETCH #6:c=0,e=5,p=0,cr=1,cu=0,mis=0,r=1,dep=1,og=1,plh=3104650627,tim=1357453194319564...1000108 FETCH #6:c=0,e=47,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3104650627,tim=13574532141422181000109 STAT #6 id=1 cnt=999999 pid=0 pos=1 obj=75053 op='TABLE ACCESS FULL BIG_DATA (cr=1000002 pr=1832 pw=0 time=2281997 us cost=512 size=18637659 card=810333)'1000110 CLOSE #6:c=0,e=1,dep=1,type=3,tim=13574532141423171000111 EXEC #7:c=19290067,e=19920346,p=1832,cr=1000058,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=13574532141423381000112 =====================

 

SELECT/* OPT_DYN_SAMP */The large SQL is the dynamic sampling SQL of CBO. it also consumes some CPU time (that is, the value of c ). we found that about more than 1 million of FETCH statements are processed in trace, that is, one by one, and the cpu time consumed is as high as 19290067. Obviously, the processing efficiency of such cursors is extremely low. (in particular, many developers also like to lock such cursors and process them in a single way, which is very inefficient.) the trace file in the second cursor mode is as follows: (batch processing)

PARSING IN CURSOR #5 len=182 dep=0 uid=84 oct=47 lid=84 tim=1357454222243170 hv=3525186369 ad='3aa08740' sqlid='fr3sb9r91w4u1'declare  cursor c_a is    select a from big_data;type t_a is table of c_a%rowtype;  v_a t_a;begin  open c_a;  --?úá?′|àí    fetch c_a bulk collect into v_a;  close c_a;end;END OF STMTPARSE #5:c=47993,e=48253,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1357454222243163=====================PARSING IN CURSOR #7 len=22 dep=1 uid=84 oct=3 lid=84 tim=1357454222243720 hv=3992159408 ad='3aae4de0' sqlid='3w21sgzqz715h'SELECT A FROM BIG_DATAEND OF STMTPARSE #7:c=0,e=59,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3104650627,tim=1357454222243719EXEC #7:c=1000,e=61,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3104650627,tim=1357454222243839*** 2013-01-06 14:37:02.816FETCH #7:c=572913,e=572454,p=1832,cr=1835,cu=0,mis=0,r=999999,dep=1,og=1,plh=3104650627,tim=1357454222816387STAT #7 id=1 cnt=999999 pid=0 pos=1 obj=75053 op='TABLE ACCESS FULL BIG_DATA (cr=1835 pr=1832 pw=0 time=633174 us cost=512 size=18637659 card=810333)'CLOSE #7:c=0,e=2,dep=1,type=3,tim=1357454222816543EXEC #5:c=586911,e=586709,p=1832,cr=1835,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1357454222830293

 

The garbled characters are Chinese characters that are annotated. bulk collect batch processing is obviously much faster. we can see that it runs the cursor statement select a from BIG_DATA first and then FETCH it out. process 999999 rows at a time. the trace file in the third cursor mode is as follows: (Multiple processes)

763 PARSING IN CURSOR #6 len=105 dep=0 uid=84 oct=47 lid=84 tim=1357454481979282 hv=97100697 ad='3faaba00' sqlid='46bkjvc2wm8wt'  764 declare  765   v_a big_data.a%type;  766 begin  767   for x in (select a from big_data) loop  768    v_a:=x.a;  769 end loop;  770 end;  771 END OF STMT  772 PARSE #6:c=9998,e=10050,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=0,tim=1357454481979278  773 =====================  774 PARSING IN CURSOR #4 len=22 dep=1 uid=84 oct=3 lid=84 tim=1357454481979809 hv=3992159408 ad='3aae4de0' sqlid='3w21sgzqz715h'  775 SELECT A FROM BIG_DATA  776 END OF STMT  777 PARSE #4:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3104650627,tim=1357454481979806  778 EXEC #4:c=0,e=48,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=1,plh=3104650627,tim=1357454481980067  779 FETCH #4:c=1000,e=1012,p=20,cr=4,cu=0,mis=0,r=100,dep=1,og=1,plh=3104650627,tim=1357454481981179  ...10778 FETCH #4:c=0,e=78,p=0,cr=1,cu=0,mis=0,r=99,dep=1,og=1,plh=3104650627,tim=135745448275985710779 CLOSE #4:c=0,e=2,dep=1,type=3,tim=135745448275990610780 EXEC #6:c=780882,e=780310,p=1832,cr=11798,cu=0,mis=0,r=1,dep=0,og=1,plh=0,tim=1357454482759962

 

We can see that the CPU time of this processing method is very close to the second type, which is only an order of magnitude different. This implicit loop cursor statement is actually a batch processing process, it reads multiple rows of data to the cache each time. we can see that the total FETCH count is only a little more than 10 thousand, which is more than 1 million times lower than the first 100. we can see from the r value in the fetch row that nearly 100 rows of data are retrieved each time. It can be seen that this implicit cursor loop is also a batch processing process. in general, I like the third method of cursor processing, for two reasons: 1. The code is short, saving the definition of the cursor variable; 2. when you do not need to use collection variables (when you do not use bulk collect), the speed is fast.