Home IT Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Oracle database with test data insertion speed     - Hadoop 2.6.0 standalone configuration and pseudo-distributed configuration under Ubuntu 14.04 (Server)

- SSH mutual trust configuration (Server)

- Let Markdown code syntax highlighting and support Django1.6 (Linux)

- Oracle utilized undo data recovery operations (Database)

- Repair after installing Ubuntu no boot device error (Linux)

- Manage SQL Server services login (start) account and password (Database)

- Git uses a small mind (Linux)

- Install JDK 1.7 + Eclipse in CentOS 6.4 in (Linux)

- Linux beginners to develop the seven habits (Linux)

- Linux landing problem (Linux)

- SecureCRT in Python scripting study guide (Programming)

- Ubuntu 14.10 installation GNOME 3.14 (Linux)

- Java thread pool: ExecutorService, Executors (Programming)

- Retro terminal in Linux (Linux)

- KVM usb passthrough configuration (Linux)

- To install MySQL 5.6 binary packages under CentOS 6.4 64bit (Database)

- ctop: monitor container performance Linux command line artifact (Linux)

- CentOS7 installation configuration (Server)

- xCAT line installation on CentOS 6.X (Linux)

- Btrfs file system creation and their characteristics in Linux (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:      
 
- The method of CentOS-7.0. The installation and configuration of Tomcat-7 (Server)
- Ubuntu way of decompressing files (Linux)
- Install Kali Linux via PXE network (Linux)
- To use slay kill user processes (Linux)
- Shell Scripting Interview Questions (Programming)
- Android determine the device network connection status, and determine the connection (Programming)
- To compile and install Clang 3.5.0 in Linux CentOS (Linux)
- How to configure AWStats in Ubuntu Server (Server)
- MySQL Tutorial: Philosophical Reflections on the unauthenticated user (Database)
- Use dump restore mode fast backup and recovery system FreeBSD (Linux)
- Oracle table compression Technology Introduction (Database)
- redis configuration in detail (English) (Database)
- A deep understanding of Java enum (Programming)
- Java eight new features 8 (Programming)
- Linux Variable content removal and replacement (Linux)
- Oracle row and column switch to turn columns (Database)
- How Oracle implements random reads from specific combinations (Database)
- Upgrading Oracle 11.2.0.1 to 11.2.0.3 (Database)
- Laravel 4.2 Laravel5 comprehensive upgrade Raiders (Server)
- Ubuntu the ARP (arptables) (Linux)
     
           
     
  CopyRight 2002-2016 newfreesoft.com, All Rights Reserved.