Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle database with test data insertion speed     - Learn to read the source code of vmstat (Linux)

- Spark and Hadoop comparison (Server)

- Git Installation and Configuration (Network Agent settings) (Linux)

- 11.2.04 Oracle RAC directory crfclust.bdb file is too large, Bug 20186278 (Database)

- Redis Linux system installation guide (Database)

- Linux Network Programming - raw socket instance: MAC Address Scanner (Programming)

- Offline (local) Yum source structures (Linux)

- Ubuntu 14.04 Docker installation (Linux)

- Ubuntu install driver manager Mint Driver Manager and Device Driver Manager (Linux)

- Java reflection summary (Programming)

- Redis master-slave replication switch (Database)

- Adding SSH to Github (Linux)

- Precautions against hackers Linux environment (Linux)

- Ubuntu install Vendetta Online 14.04 (Linux)

- Linux directory configuration (Linux)

- MySQL backup tool to back up mydumper (Database)

- PHP 5.3 New Features Detail (Linux)

- CentOS7 iptables (Linux)

- JavaScript in this usage (Programming)

- Linux system package manager (rpm, yum, source packages installation) (Linux)

 
         
  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:      
 
- Linux System Getting Started Learning: DeVeDe installed on Linux to create a video DVD (Linux)
- CentOS7 install NTFS-3G driver (Linux)
- Sublime Text 3 (Linux)
- Simple configuration shell scripts virtual machine environment experiment (Linux)
- RHEL / CentOS / Fedora Install Nagios 4.0.1 (Linux)
- Ubuntu set Swap Space Tutorial (Linux)
- Linux Command study manual - GPG command (Linux)
- Hazelcast integration with MongoDB (Database)
- Zabbix monitoring Oracle Database use Orabbix plug (Enhanced Edition) (Database)
- Import and export myloader accelerate mydumper (Database)
- Tecplot Installation under Linux (Linux)
- map and hash_map STL containers (Programming)
- CentOS 7 server environment to quickly build Linux (Server)
- Linux Getting Started tutorial: Experience VirtualBox Virtual Machine chapter (Linux)
- Linux Shell Scripting Interview Question (Linux)
- Understanding the Linux load average on other UNIX-Like systems (Linux)
- Shell programming entry (Programming)
- Smooth upgrade to OpenSSH 6.1 Procedure (Linux)
- Three kinds of binary tree traversal recursive and iterative solution (Programming)
- XP virtual machine under VirtualBox solve occupy 100% CPU problem (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.