Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ PL / SQL in forall simple test     - Installation Experience open source car Automotive Grade Linux system (Linux)

- Spring loaded container finishes executing a method (Programming)

- Python class of operator overloading (Programming)

- Linux batch copy data script (Linux)

- Linux Crontab Timing task command Detailed (Linux)

- The execution order of Oracle WHERE condition is not from right to left (Database)

- By creating a personal technology blog Detailed GitHub (Linux)

- Linux find command to find files (Linux)

- Use SecureCRT to transfer files between Linux and Windows (Linux)

- Build RPM package uses Docker mirror (Linux)

- How to build Memcached Docker container (Server)

- ORA-00845: MEMORY_TARGET not supported on this system Problem (Database)

- MySQL Error Code Complete (Database)

- Ubuntu install video conversion tool Selene (Linux)

- CentOS 7 update source - the use of domestic sources 163 yum (Linux)

- Use innobackupex full realization of MySQL hot backup (Database)

- C ++ Supplements - Smart Pointers (Programming)

- Dynamic programming Android (Programming)

- The principle Httpclient4.4 (execution request) (Programming)

- Linux systems for entry-learning: Install closed-source packages in Debian (Linux)

 
         
  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:      
 
- How to install Git on CentOS 7 (Linux)
- GO five stages of language learning (Programming)
- How to use the character in C ++ without pressing the Enter key to enter the Show (Programming)
- Git and GitHub use of Eclipse and Android Studio (Programming)
- Linux NIC driver and version information (Linux)
- Ubuntu 15.10 / 14.04 install subtitling software Aegisub (Linux)
- SSH does not require a password to log on to a Linux server (Server)
- Easily solve the MySQL database connection error too many (Database)
- Delegate in C # (Programming)
- JBoss7 configuration - Supports IPv4 and IPv6 dual-stack environment (Server)
- blecat: Bluetooth Gadgets (Linux)
- Configuring Proxy on a Unix terminal, accelerate Android Studio Construction (Linux)
- Java Set and List in the relationship and difference (Programming)
- Android judgment toward camera pictures (Programming)
- Construction LVM-based raw device Oracle10g Database on Oracle Linux 5.11 (Database)
- Installation Eduspec university management systems 17 Linux Mint (Server)
- How to properly set up a Linux swap partition (Linux)
- Ubuntu 14.04 solved using MyEclipse 10.7 flash back (Linux)
- Java deserialization test (Programming)
- RAID configuration and management under linux (Server)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.