Simple Test of forall in PL/SQL

I wrote a bulkcollect article, but I made a simple example of bulkcollect. Www.linuxidc.comLinux2015-07119948.htm in fact

I wrote a bulk collect article, but I made a simple example of bulk collect. Http://www.linuxidc.com/linux/2015-07/119948.htmactually

I wrote a bulk collect article, but I made a simple example of bulk collect.
In fact, it is not only bulk collect, but forall also greatly improves pl/SQL Performance.
You can see the two figures below. In pl/SQL, the pl/SQL code we write may switch the context in the SQL engine and plsql engine, this process is still time-consuming.

On the contrary, forall provides a context switch to encapsulate data during the forall process. One request is sent to the SQL executor for processing, which greatly reduces the context switching time.

As you can imagine, if the result set in cursor is large, a large number of context switches may be performed, resulting in a sudden drop in execution speed.
Let's make a simple example.

We create a table named test_data, which contains about 70 thousand million data records.
N1 @ TEST11G> create table test_data as select * from all_objects;
Table created.

N1 @ TEST11G> select count (*) from test_data;
COUNT (*)
----------
71659
1 row selected

N1 @ TEST11G> create unique index inx_test_data_pk on test_data (object_id );
Index created.
Elapsed: 00:00:00. 48

Then begin to execute the Stored Procedure
[Ora11g @ oel1 plsql] $ cat a. SQL
Create or replace procedure test_proc
Cursor test_cur is select * from test_data;
I number;
Begin
I: = 1;
For cur in test_cur
Loop
Update test_data set object_name = cur. object_name
Where object_id = cur. object_id;
Dbms_output.put_line ('this is a test ');
I: = I + 1;
End loop;
End;
/

Exec test_proc;

During execution, the process occupies a large amount of cpu resources. As you can see, a large number of context switches are performed. In fact, a major information point is that a large amount of log Content is output, and finally exited due to cache.

......
This is a test
This is a test
This is a test
This is a test
This is a test
This is a test
This is a test
This is a test
This is a test
This is a test
This is a test
This is a test
This is a test
This is a test
This is a test
BEGIN test_proc; END;

*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS. DBMS_OUTPUT", line 32
ORA-06512: at "SYS. DBMS_OUTPUT", line 97
ORA-06512: at "SYS. DBMS_OUTPUT", line 112
ORA-06512: at "N1.TEST _ PROC", line 10
ORA-06512: at line 1

Elapsed: 00:00:13. 73

If the forall format is used, the code used is as follows.
[Ora11g @ oel1 plsql] $ cat B. SQL
Create or replace procedure test_proc
Cursor test_cur is select * from test_data;
Type rec_type is table of test_cur % rowtype index by binary_integer;
Recs rec_type;
Begin
Open test_cur;
Fetch test_cur bulk collect into recs;
Close test_cur;
Forall I in 1 .. recs. COUNT
Update test_data set object_name = recs (I). object_name
Where object_id = recs (I). object_id;
Dbms_output.put_line ('this is a test ');
End;
/

This effect is much better. We can see that only one log information is output in the log, which means that only one context switch is performed. This method is much better.
N1 @ TEST11G> exec test_proc;
This is a test
PL/SQL procedure successfully completed.
Elapsed: 00:00:01. 67
We recommend that you process large volumes of data. Dbms_profiler will be used in the future for this test. It can be seen that there are still many differences in some implementation points.

Basic Design of PL/SQL programs for Oracle databases

PL/SQL Developer Practical Skills

This article permanently updates the link address: