Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle database with test data insertion speed     - How to use the TF / SD card making Exynos 4412 u-boot boot disk in Ubuntu (Linux)

- Bootable ISO image using GRUB (Linux)

- How do you change the default browser and Email Client in Ubuntu (Linux)

- grep, egrep and regular expressions (Linux)

- MySQL bulk insert data script (Database)

- RCU lock in the evolution of the Linux kernel (Linux)

- Java Builder mode (Programming)

- Oracle 12C with multi-column index (Database)

- Linux Getting Started tutorial: build your own Vim (Linux)

- Revised OpenJDK Java Memory Model (Programming)

- See Shell Script Linux Server network traffic (Server)

- CentOS 6.6 install JDK7 (Linux)

- Use Ansible efficient delivery Docker container (Server)

- How to install Hadoop on CentOS7 (Server)

- Arduino UNO simulation development environment set up and run simulation (Linux)

- When should I use Angular 2 (Programming)

- How to Install Puppet in the Ubuntu 15.04 (Server)

- The Rabbitmq installation under CentOS 6.4 (Linux)

- 7 extremely dangerous Linux commands (Linux)

- Use Visual Studio 2015 to develop Android program (Programming)

 
         
  Oracle database with test data insertion speed
     
  Add Date : 2018-11-21      
         
       
         
  First, non-optimized speed: Executed in 69.436 seconds

drop table t purge;
create table t (x int);
/ * Clear the shared pool, pay attention to the production environment must not do this operation * /
alter system flush shared_pool;

create or replace procedure proc1
as
begin
for i in 1 .. 100000
loop
execute immediate
'Insert into t values ​​(' || i || ')';
commit;
end loop;
end;
/

See below under proc1 insert 100,000 records execution time
SQL> set timing on;
SQL> exec proc1;

PL / SQL procedure successfully completed

Executed in 69.436 seconds

 

/ * We can see the following statement by concrete steps in this stored procedure execution * /
select t.sql_text, t.sql_id, t.parse_calls, t.executions from v $ sql t where sql_text like '% insert into t values%';

For easy viewing I use PL / SQL DEVELOPER execution of the above statement
As can be seen from the above, each statement is resolved once and performed a total of 100,000 times resolved, you might ask you with only 7136 rows ah, how you parse it 100,000 times. I can tell you for sure is to resolve 10 million times, because I shared pool little space to accommodate not less 100 000 information, according to the FIFO principle you can see, now I checked out more than 92,000 are from the beginning SQL STATEMENT record. We know that these are similar to the SQL statement is not necessary to resolve 100,000 times, that each statement parsed once. This PROC1 did not use bind variables, this is what we can optimize the place. We use bind variables to re-test, the following PROC2 will only resolve it once, of course, the speed will certainly improve a lot.

 

Second, the speed using bind variables optimized: Executed in 26.505 seconds

drop table t purge;
create table t (x int);
/ * Clear the shared pool, pay attention to the production environment must not do this operation * /
alter system flush shared_pool;


create or replace procedure proc2
as
begin
for i in 1 .. 100000
loop
execute immediate
'Insert into t values ​​(: x)' using i;
commit;
end loop;
end;
/

SQL> set timing on;
SQL> exec proc2;

PL / SQL procedure successfully completed

Executed in 26.505 seconds

As can be seen from the above, the time is substantially reduced by half.

/ * We can see the following statement by concrete steps in this stored procedure execution * /
select t.sql_text, t.sql_id, t.parse_calls, t.executions from v $ sql t where sql_text like '% insert into t values%' order by 1;



From the implementation of the above can know, once resolved, it performed 100,000 times. Entirely consistent with our conjecture, so the speed is greatly enhanced.

execute immediate is a dynamic SQL wording commonly used in the table name field name is variable, into the reference case, because I do not know the name of the table, so you can not write directly to SQL, so rely on dynamic SQL statement successor table and field names as arguments splicing SQLSTATEMENT, there are calls execute immediate execution. But I do not need this example can dynamically, you can use static written.
Third, with the speed of static rewritten: Executed in 19.391 seconds

drop table t purge;
create table t (x int);
/ * Clear the shared pool, pay attention to the production environment must not do this operation * /
alter system flush shared_pool;

create or replace procedure proc3
as
begin
for i in 1 .. 100000
loop
insert into t values ​​(i);
commit;
end loop;
end;
/

SQL> set timing on;
SQL> exec proc3;

PL / SQL procedure successfully completed

Executed in 19.391 seconds



As can be seen from the above, proc3 also achieved bind variables, and the dynamic nature of the implementation process of re-analysis, which is characterized by a static SQL compilation process is parsed, so the above PRARSE_CALLS is 0. Note that the above diagram a comparison, when PARSE_CALLS is above 1, and this is 0, so the less static and a resolution process execution time.

We can see that the above three are the PROC statement to commit time, we have no need to do so, we can submit together. In the following example: when the commit of the log_buffer by LGWR writes the information in online redo log, the trigger LGWR to write 100,000 times, and we know LGWR write too often.

 

Fourth, the speed batch submitted: Executed in 11.42 seconds

drop table t purge;
create table t (x int);
/ * Clear the shared pool, pay attention to the production environment must not do this operation * /
alter system flush shared_pool;
create or replace procedure proc4
as
begin
for i in 1 .. 100000
loop
insert into t values ​​(i);
end loop;
commit;
end;
/

SQL> set timing on;
SQL> exec proc4;

PL / SQL procedure successfully completed

Executed in 11.42 seconds

We can see that we use less time.

 

Five, a collection of writing speed: Executed in 0.452 seconds

drop table t purge;
create table t (x int);
/ * Clear the shared pool, pay attention to the production environment must not do this operation * /
alter system flush shared_pool;

/ * The following statement is from the above one by one to insert a written into the data buffer area in the whole batch, it is faster than the above, a batch definitely better than a quick execution * /
insert into t select rownum from dual connect by level <= 100000;

SQL> set timing on;
SQL> insert into t select rownum from dual connect by level <= 100000;

100000 rows inserted

Executed in 0.452 seconds

This is a one of the first four are inserted above, I set this wording is a whole batch-written to DATA BUFFER years, so much faster than the four cases above.

 

Sixth, with the direct path of writing speed (1 million records): Executed in 1.514 seconds
/ * Here use direct path to operate, the speed will be faster than the above, it is the so-called direct path data without database buffer, but directly to disk, less step writes data buffer (database buffer) operation * /
drop table t purge;
alter system flush shared_pool;
SQL> set timing on;
SQL> create table t as select rownum x from dual connect by level <= 1000000;

Table created

Executed in 1.514 seconds

Note that at this time I insert a record 10 times tens above me is to insert 1 million records took only 1.514 seconds.

Note: The wording than the direct path of joy because of the wording set, insert into select .... way the data is first written to the data buffer, and then painted to disk. And create as t the way skipping the data buffer (data buffer), written directly to disk, in this way is called direct path read and write mode. Originally a first memory in the disk, change directly to disk, one less step, so faster.

 

Seven parallel writing speed (1 million records): Executed in 0.733 seconds

/ * Add parallel direct path, but do not write the log, so the speed is faster than the above * /
drop table t purge;
alter system flush shared_pool;
set timing on;
create table t nologging parallel 64 as select rownum x from dual connect by level <= 100000;

SQL> set timing on;
SQL> create table t nologging parallel 4 as select rownum x from dual connect by level <= 1000000;

Table created

Executed in 0.733 seconds

I only above a parallel 4, if more, then sooner !!!
     
         
       
         
  More:      
 
- Python Socket Network Programming (Programming)
- Linux System Getting Started Learning: Linux command in w (Linux)
- Polymorphism of the C ++ compiler and run-time polymorphism (Programming)
- Virtual Judge environment to build and configure under Ubuntu (Server)
- Using Python to find a particular file extension directory (Programming)
- How to Create a file can not be changed under Linux (Linux)
- Shell Script: create a function and specify a directory for download (Programming)
- Manually compile Hadoop 2.6.0 under Ubuntu 14.04 (Server)
- 10 Nginx safety tips (Linux)
- Ubuntu 64-bit installation Adobe Reader 9.5.5 (Linux)
- CentOS / Linux kernel upgrade (Linux)
- How to make GRub instead of the default Ubuntu software center (Linux)
- Ubuntu Live CD by updating Grub resume boot the Boot Menu (Linux)
- Automatic and Manual Proxy Settings Switch GNOME Shell Extension Proxy Switcher Recommend (Linux)
- Ubuntu 14.04 Configuring cuda-convnet (Linux)
- Easy to install CentOS 6.6 desktop environment (Linux)
- Linux environment MySQL master-slave synchronization (Database)
- Debian 8 (amd64) installation deployment Memcached management tools MemAdmin (Server)
- Nginx version information hidden or modified (Server)
- Developing a Web server yourself (Server)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.