Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ PL / SQL in forall simple test     - Apple Mac computer to install Windows 10 Concise Guide (Linux)

- Debian GNU / Linux service list acquisition, shutting down services or run (Linux)

- Debian Stable (Jessie 8.1) of normal.mod not found (Linux)

- Linux operating system ARP Spoofing Defense (Linux)

- Configure the ASM process on Red Hat Linux 6.5 (Database)

- CentOS 6.5 install Firefox (Linux)

- Apache Mina framework Practice (Programming)

- Android design patterns - state mode (Programming)

- Summary Linux operating system some tips to prevent attacks (Linux)

- Inject script commands manually annotated summary (Linux)

- Mounting Windows shared directory system under the Linux (Linux)

- Ubuntu 14.04 install the NVIDIA driver + CUDA + MATLAB (Linux)

- VMware ghost Linux card error (Linux)

- PostgreSQL export data dictionary documents (Database)

- RHEL5 multipath configuration (Linux)

- Use chattr and lsattr command to manage linux files and directory attributes (Linux)

- Linux file and directory permissions settings (Linux)

- Oracle utilized undo data recovery operations (Database)

- Netapp storage routine inspections and information gathering (Linux)

- Asynchronous communication mechanism between the Android source code analysis thread (Programming)

 
         
  PL / SQL in forall simple test
     
  Add Date : 2018-11-21      
         
       
         
  In fact, not only is the bulk collect, forall for pl / sql performance improvement is quite large.
 You can see the following two figures, in fact, can be seen in pl / sql, it may be many times pl / sql code that we write will be a context switch sql plsql engine and engine construction, this process is very time-consuming.

 The forall is the opposite, to provide a context switch, it will be packaged in the process of processing data in forall. Once sent to the sql actuators to handle, greatly reducing the context switching time.

 For this, it is conceivable, if the cursor result set is very large, it is likely a lot of context switching, resulting in execution speed plummeted.
 We do a simple example to illustrate.

 We created a table test_data, which are about the amount of data of more than 70,000.
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 began to execute a stored procedure
[Ora11g @ oel1 plsql] $ cat a.sql
create or replace procedure test_proc as
  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;

The process of implementation will see the process of taking up a lot of cpu resources. Seen a lot of context switching. In fact, one of the main information point is that you can see the output of the massive logs, and finally because of the cache quit.

......
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

The same requirements, if you use the form forall, the code used is as follows.
[Ora11g @ oel1 plsql] $ cat b.sql
create or replace procedure test_proc as
  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, you can see the log output only once the log information, which means only a context switch, this approach is clearly much better.
n1 @ TEST11G> exec test_proc;
this is a test
PL / SQL procedure successfully completed.
Elapsed: 00: 00: 01.67
For high-volume data processing it is still very worthy of recommendation. Subsequent use dbms_profiler to this test, it can be seen in some implementations points or there are significant differences.
     
         
       
         
  More:      
 
- CentOS 7 Add yum source (Linux)
- Using LLVM Clang and Blocks under Linux (Programming)
- MySQL stored procedures execute dynamic sql statement (Database)
- Linux kernel update error, update-initramfs: failed Solution (Linux)
- iOS constants, variables, properties and characteristics (Programming)
- To install JDK1.7 and compiler Hadoop-2.7.1 under CentOS7 (Server)
- Linux Tutorial Share: How to sudo command to define the PATH environment variable (Linux)
- CentOS 6.6 permanent method to modify the DNS address (Linux)
- A summary of Java multi-threaded programming - acquaintance multithreading (Programming)
- How to install Nginx on FreeBSD 10.2 as an Apache reverse proxy (Server)
- TOAST function in PostgreSQL (Database)
- To use slay kill user processes (Linux)
- cursor_sharing induced error ORA-00600 (Database)
- RedHat Linux 6.4 installation RTL8188CUS wireless network card driver (Linux)
- iOS developers - a simple factory pattern and OC reflection (Programming)
- A deep understanding of Java enum (Programming)
- Linux boot process and run level (Linux)
- Servlet 3.0 interfaces of AsyncListener (Programming)
- Construction CA certificate using OpenSSL command line (Server)
- Ant command-line compiler Android project (Programming)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.