How to increase the speed of inserting 100,000 records

A recent test has been made to see how long it will take to insert 10 bars into the database.
System: Redhat + Oracle 9.2.0.4,+ RAID5 + 10,000-turn SCSI hard drive

Condition: Commit every insertion of a piece.
The client processes the INSERT statement and then sends it to the database.

Exhausted the method, found only about 600 inserts per second, but under the same conditions SQL Server can insert more than 1000 per second, so I this Oracle DBA very face, so decided to tune a.

So I tried to do what I could to optimize:
Build the data file with the online log file on the bare device.
Insert/*+ Append */into .....

But no improvement, the insertion speed increased to 65 per second.

A large number of v$system_event views are found:
Log file Parallel write 27088 27078 11
Log file Sync 26935 0 784
Wait.

Who can help me.
I'll say two more. View all comments related comments Zhanghuajie (2004-7-20 09:39:33)Can not make these 100,000 records file, with Sqlload import?? Or make a excle file, yes!!!! Sunnyxu (2004-7-20 09:55:27)No, because through the program, we have a lot of concurrent access! Tim_wong (2004-7-20 11:12:40)2000 commits a time. Tim_wong (2004-7-20 11:15:32)Sorry

The condition was overlooked.

Maybe you have too many restrictions on your watch. Sunnyxu (2004-7-20 12:07:50)This table has no indexes and no constraints.

Under the same conditions, the SQL Server database inserts more than 1200 rows per second! Jametong (2004-7-20 12:15:04)Why not insert a commit once, you can bulk INSERT, such a transaction causes, rollback segment allocation is too frequent, write log files too frequent, Zhanhu_hou (2004-7-20 12:56:42)Never studied optimization, but personally feel that Oracle's data processing speed may be less than SQL Server, Oracle may be more reflected in stability and cross-platform. Basically I don't have much hope for the speed at which it is processed. Jametong (2004-7-20 12:59:44)Do not agree with the view of the above, Oracle performance can be adjusted,
Specific situations require DBA to adjust and trade-offs (stability, high user, flexibility, manageability) Zhanhu_hou (2004-7-20 13:23:30)So that it aroused my interest, now that Oracle has been properly optimized (SQL Server has not yet been optimized), it's 650/s (see what the author means), and everyone looks at how to optimize it, the way I think about it is to modify the program first, without having to insert every record and commit it. So optimize the SQL statement first. Jametong (2004-7-20 13:29:28)In the present case, if you change to 10,000 commits once, I believe that the efficiency of doubling is no problem, the specific test today did not have time to do.
I've done a SQL Loader import test before.
200,000 data, by default, the time to import the database is 1 minutes and 50 seconds,
When I modified the parameter readsize=21971520, the loading time is about 40 seconds,
Add a direcy=y parameter, the load time is about 10 seconds

The following article is a test by a dbaspecialist engineer

Load Your Data faster.htm
(, size:29.5 KB, downloads:138)Zhanhu_hou (2004-7-20 13:49:10)It seems that Oracle's scalability is really great, different data loading methods, the difference is so big.
From this perspective, really to manage the database, is not a very easy thing.Jametong (2004-7-20 14:31:51)OhSunnyxu (2004-7-20 15:58:52)I have to make a commit if I don't insert a piece. This is the application of the requirements.
I am not the load part of the data to do the test, this is a formal application.D.C.B.A (2004-7-20 20:30:25)I have a Java-written text Import tool that has no problem with 10,000 articles per second in Oracle.
This must be a problem with the program, which is running on Windows.Jametong (2004-7-20 20:39:32)Brother, could you consider modifying the parameters of the rollback segment?chao_ping (2004-7-20 23:04:25)Every insertion of a commit, which is the design above, limits his scale.

In this respect, Oracle's implementation mechanism determines that it will not be as fast as SQL Server I think is understandable. But the difference should not be so uneven.

Another possibility is that every time your connection is transmitted through a sql*net, it also limits your speed.


I did a simple test for PC server, RAID5 's CX300 disk array.
The speed can reach 3,000.
Of course, if with batch commit, efficiency should be able to improve a lot.

1 begin
2 for x in 1..100000 loop
3 INSERT INTO test values (x);
4 commit;
5 end Loop;
6* end;
Sql>/

Pl/sql procedure successfully completed.

elapsed:00:00:35.93
Sql> select 100000/35 from dual;

100000/35
----------
2857.14286

elapsed:00:00:00.0D.C.B.A (2004-7-21 08:05:39)The index should also be posted, and MS SQL's primary key is generally equivalent to Oracle's IoT.Rollingpig (2004-7-21 08:51:29)Look at what Oracle is inserting into a piece of data and making a commit.

Then look at what SQL Server inserts into a piece of data and commits it.

You should see Oracle doing more than Sql-server.
And these things are guaranteed Oracle's stable/high concurrency/etc. necessary for

The above is purely commonsense conjecture, not carefully verifiedSunnyxu (2004-7-21 10:14:24)Thank you for your enthusiastic participation!

Let me make a summary:

Test Prerequisites:
Because it is a website, the number of visits to each person counts, business requirements require inserting a must commit;

test with Test program 1:
Insert 100,000 bar, averaging 600 per second.

test with Test program 2:
Insert 100,000 bar, average 1000 per second

Test with Test program 3:
The insert operation is implemented using a stored procedure that is invoked in the program to implement the insert operation:
Insert 100,000 bar, average 500 per second

Test with Test program 4:
The test program with the test program 1, but the cursor_sharing parameter set to similar
Insert 100,000 bar, average 800 per second


cause Analysis
In Oracle 9.2.0.1, the cursor_sharing parameter is set to exact by default, so that because each INSERT statement is only a different value, Oracle can still cause a parse to be restarted, consuming considerable time. Therefore, the insertion statement is changed to PreparedStatement mode, which avoids the parse again, so the performance is improved very quickly. In the same way, setting the cursor_sharing parameter to similar can also achieve similar effects.

As for the reason for poor insertion performance with stored procedures, you can analyze this: because the implementation of the stored procedure is not much, but simply insert a record, at this time, does not reflect the benefits of stored procedures, but because each insert a call to a stored procedure, but will increase overhead. That is, if you use stored procedures to achieve large amounts of data access or complex queries, it will reflect the benefits of using stored procedures.


As for why SQL Server inserts 100,000 more quickly than Oracle, I think it has something to do with the optimizer's optimization approach. If SQL Server defaults to executing SQL statements in the mode of force using the Oracle cursor_sharing parameter.

I also use stored procedures on Oracle and SQL Server to test, under the same conditions, Oracle can insert at least 50,000 per second, far greater than the SQL Server (about 10,000). This also shows that Oracle data has a strong ability to handle itself.


Test Program 1:
Import java.sql.*;
Import java.net.*;
Import java.io.*;
Import java.util.*;
Import oracle.jdbc.OracleConnection;


public class FailOver {

Static final String user = "Test";
Static final String password = "Test";
Static final String Driver_class = "Racle.jdbc.driver.OracleDriver";
Static final String URL = "Jdbc:oracle:thin 192.168.11.180:1521:sensky";


Static final String user = "sa";
Static final String password = "sa";
Static final String Driver_class = "Com.microsoft.jdbc.sqlserver.SQLServerDriver";
Static final String URL = "Jdbc:microsoft:sqlserver://192.168.11.172:1433;databasename=wap";

public static void Main (string[] args) throws Exception {

Connection conn = null;
String msg = NULL;
Statement stmt = null;
ResultSet rset = null;

Load JDBC Driver
try {
Class.forName (Driver_class);
}
catch (Exception e) {
System.out.println (e);
}

Connect to the database
conn = Drivermanager.getconnection (URL, user, password);
Conn.setautocommit (FALSE);

Create a Statement
stmt = Conn.createstatement ();
Thread.Sleep (100000);
Long Start=system.currenttimemillis ();
Conn.begintrans ();
For (long i=800000 i<900000; i++) {
Select the NAMEs column from the EMPLOYEES table
Stmt.execute ("INSERT into Tbl_test (ID, name, age, addr) VALUES (" + i + ", ' ASDFASDFSA ',", ' aaaaaaaaaaaaaa ') ");
Stmt.execute ("INSERT into Tbl_test (ID, name, age, addr) values (M, ' ASDFASDFSA ',, ' aaaaaaaaaaaaaa ')");
If (i% 2000 = 1)
//        {
Conn.commit ();
//     }
}//End for
Conn.commit ();
Long End=system.currenttimemillis ();
SYSTEM.OUT.PRINTLN ("Waste time:" + (End-start));
Close the Statement
Stmt.close ();
Close the connection
Conn.close ();
}//End Main ()
}


Test Program 2:
Import java.sql.*;
Import java.net.*;
Import java.io.*;
Import java.util.*;
Import oracle.jdbc.OracleConnection;


public class Prestat {

Static final String user = "Test";
Static final String password = "Test";
Static final String Driver_class = "Racle.jdbc.driver.OracleDriver";
Static final String URL = "Jdbc:oracle:thin 192.168.11.180:1521:sensky";


public static void Main (string[] args) throws Exception {

Connection conn = null;
String msg = NULL;
Statement stmt = null;
ResultSet rset = null;

Load JDBC Driver
try {
Class.forName (Driver_class);
}
catch (Exception e) {
System.out.println (e);
}

Connect to the database
conn = Drivermanager.getconnection (URL, user, password);
Conn.setautocommit (FALSE);

Create a Statement
PreparedStatement pstat = conn.preparestatement ("INSERT into tbl_test values (?,?,?,?)");

Long Start=system.currenttimemillis ();
Conn.begintrans ();
for (int i=0; i<100000; i++) {
Select the NAMEs column from the EMPLOYEES table
Pstat.setint (1, i);
Pstat.setstring (2, "aaaaaaaa");
Pstat.setint (3, I);
Pstat.setstring (4, "bbbbbbb");
Pstat.executeupdate (); JDBC queues this for later execution
Conn.commit ();
Sleep one second to make it possible to shutdown the DB.
}//End for
Conn.commit ();
Long End=system.currenttimemillis ();
SYSTEM.OUT.PRINTLN ("Waste time:" + (End-start));
Close the Statement
Pstat.close ();

Close the connection
Conn.close ();


}//End Main ()

}//End Class Jdemofo


Test Program 3:
Import java.sql.*;
Import java.net.*;
Import java.io.*;
Import java.util.*;
Import oracle.jdbc.OracleConnection;


public class Prcejava {

Static final String user = "Test";
Static final String password = "Test";
Static final String Driver_class = "Racle.jdbc.driver.OracleDriver";
Static final String URL = "Jdbc:oracle:thin 192.168.11.180:1521:sensky";


Static final String user = "sa";
Static final String password = "sa";
Static final String Driver_class = "Com.microsoft.jdbc.sqlserver.SQLServerDriver";
Static final String URL = "Jdbc:microsoft:sqlserver://192.168.11.172:1433;databasename=wap";

public static void Main (string[] args) throws Exception {

Connection conn = null;
String msg = NULL;
Statement stmt = null;
ResultSet rset = null;

Load JDBC Driver
try {
Class.forName (Driver_class);
}
catch (Exception e) {
System.out.println (e);
}

Connect to the database
conn = Drivermanager.getconnection (URL, user, password);
Conn.setautocommit (FALSE);

Create a Statement
CallableStatement stmt = Conn.preparecall ("Begin Pro_ins" (?,?,?,?,?); end; ");
Long Start=system.currenttimemillis ();
Conn.begintrans ();
Int J;
for (int i=0; i<100000; i++) {
Select the NAMEs column from the EMPLOYEES table
Stmt.execute ("INSERT into Tbl_test (ID, name, age, addr) VALUES (" + i + ", ' ASDFASDFSA ',", ' aaaaaaaaaaaaaa ') ");
Stmt.execute ("INSERT into Tbl_test (ID, name, age, addr) values (M, ' ASDFASDFSA ',, ' aaaaaaaaaaaaaa ')");

Stmt.setint (1, i);
Stmt.setstring (2, "aaaaaaaa");
Stmt.setint (3, I);
Stmt.setstring (4, "bbbbbbb");
Stmt.registeroutparameter (5, Types.tinyint);
Stmt.execute ();
j = stmt.getint (1);
}//End for
Conn.commit ();
Long End=system.currenttimemillis ();
SYSTEM.OUT.PRINTLN ("Waste time:" + (End-start));
Close the Statement
Stmt.close ();
Close the connection
Conn.close ();
}//End Main ()
}


To create a statement for a stored procedure
Create or Replace procedure Pro_ins (p_id in Number,p_name at VARCHAR2, p_age in number, p_addr in Varchar2,p_return ou T number)
As
Begin
INSERT into Tbl_test (IDs, name, age, addr) values (P_ID,P_NAME,P_AGE,P_ADDR);
P_return: = 1;
End
///

To create a statement for a table
Create Tabel tbl_test (ID number (10,0), name VARCHAR2, age number (10,0), addr varchar2 (50));Tengrid (2004-7-22 18:23:26)Judging from your test program, there seems to be two problems.
1, when testing on SQL Server, it is submitted once per 600. And Oracle is 1 commits once.
2, in addition, you are using JDBC thin driver, proposed to replace JDBC OCI driver try.