Condition: commit is used for each insert.
Create an insert Statement on the client and send it to the database.
After using all the methods, I found that only about 600 entries can be inserted per second. However, under the same conditions, SQL Server can insert more than 1000 entries per second, which makes me a good Oracle DBA, so I decided to optimize it.
So I tried my best to optimize it:
Create a data file and an online log file on a bare device.
Insert/* + append */.....
But it does not improve. The insertion speed is increased to 65 per second!
In the V $ system_event view, a large number:
Log File parallel write 27088 27078 11
Log File sync 26935 0 784
Wait.
Who can help me? I also want to view all comments in two sentences.Related Comments
The condition is missing.
Maybe you have too many table constraints.
Under the same conditions, the SQL Server database inserts more than 1200 rows per second!
The following article is a test conducted by a dbaspecialist engineer.
Load your data faster.htm
(, Size: 29.5 kb, downloads: 138)
In this regard, the implementation mechanism of Oracle determines that it will not be very fast. I think it is understandable than SQL Server, but the difference should not be so wide.
Another possibility is that your connection is transmitted through SQL * Net every time, which also limits your speed.
I did a simple test, PC server, RAID5 cx300 disk array.
The speed can reach 3000.
Of course, if batch commit is used, the efficiency should be improved 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. 0
Next, let's take a look at how SQL Server inserts a piece of data and commit it.
You should find that Oracle does more work than SQL-server.
These tasks are required to ensure Oracle stability/High concurrency/Etc ..
The above is purely based on common sense speculation and has not been carefully verified.
Let me make a summary:
Test prerequisites:
Because it is a website, the number of visits per person is counted, the business requirements require that a must be inserted into a commit;
Test with Test Procedure 1:
Insert 100000 records, with an average of 600 records per second.
Test with Test Procedure 2:
Insert 100000 entries, with an average of 1000 entries per second
Test with test procedure 3:
You can use the stored procedure to implement the insert operation, and call the stored procedure in the program to implement the insert operation:
Insert 100000 entries, with an average of 500 entries per second
Test with Test Procedure 4:
The test program used is the same as the test program 1, except that the cursor_sharing parameter is set to similar.
Insert 100000 entries, with an average of 800 entries per second
Cause Analysis
In Oracle 9.2.0.1, The cursor_sharing parameter is set to exact by default. Therefore, since each insert statement only has different values, Oracle still causes parse again, which takes a considerable amount of time. Therefore, the insert statement is implemented using the preparedstatement method, effectively avoiding re-parse, so the performance is improved quickly. Similarly, setting the cursor_sharing parameter to similar can achieve similar effects.
As for the cause of poor insertion performance using a stored procedure, we can analyze it as follows: there are not many tasks implemented using this stored procedure, but a record is simply inserted. At this time, it does not reflect the advantages of stored procedures. Instead, each insert operation calls a stored procedure, which increases the overhead. That is, if you use a stored procedure to access a large amount of data or perform complex queries, it will reflect the benefits of using a stored procedure.
As for why SQL Server inserts 100000 entries faster than Oracle, I think it is related to the optimization method used by the optimizer. For example, sqlserver uses the Oracle cursor_sharing parameter force mode by default to execute SQL statements.
I also use stored procedures for testing on Oracle and SQL server. Under the same conditions, Oracle can insert at least 50 thousand entries per second, which is far greater than SQL Server (about 10 thousand ). This also shows that the processing capability of Oracle Data is strong.
Test procedure 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: thin192.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.exe cute ("insert into tbl_test (ID, name, age, ADDR) values (" + I + ", 'asdfasdfsa ', 22, 'aaaaaaaaaaaaaaa ')");
// Stmt.exe cute ("insert into tbl_test (ID, name, age, ADDR) values (100, 'asdfasdfsa ', 22, 'aaaaaaaaaaaaaaaaa ')");
// If (I %2000 = 1)
//{
// Conn. Commit ();
//}
} // End
// 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 Procedure 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: thin192.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, "bbbbbbbbb ");
Pstat.exe cuteupdate (); // JDBC queues this for later execution
// Conn. Commit ();
// Sleep one second to make it possible to shutdown the DB.
} // End
// 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 procedure 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: thin192.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.exe cute ("insert into tbl_test (ID, name, age, ADDR) values (" + I + ", 'asdfasdfsa ', 22, 'aaaaaaaaaaaaaaa ')");
// Stmt.exe cute ("insert into tbl_test (ID, name, age, ADDR) values (100, 'asdfasdfsa ', 22, 'aaaaaaaaaaaaaaaaa ')");
Stmt. setint (1, I );
Stmt. setstring (2, "aaaaaaaa ");
Stmt. setint (3, I );
Stmt. setstring (4, "bbbbbbbbb ");
Stmt. registeroutparameter (5, types. tinyint );
Stmt.exe cute ();
// J = stmt. getint (1 );
} // End
// 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 ()
}
// Create a stored procedure statement
// Create or replace procedure pro_ins (p_id in number, p_name in varchar2, p_age in number, p_addr in varchar2, p_return out number)
//
// Begin
// Insert into tbl_test (ID, name, age, ADDR) values (p_id, p_name, p_age, p_addr );
// P_return: = 1;
// End;
///
// Create a table statement
// Create tabel tbl_test (ID number (10, 0), name varchar2 (50), age Number (10, 0), ADDR varchar2 (50 ));
Start building with 50+ products and up to 12 months usage for Elastic Compute Service
1 on 1 presale consultation
24/7 Technical Support 6 Free Tickets per Quarter Faster Response
Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.
A comprehensive suite of global cloud computing services to power your business
Payment Methods We Support
