Hi there,
I'm trying to measure db (12c) performance. The application is a high DML ones and wants to do 10K INSERTS per second. In fact I am measuring if db 12c is able to handle such a load. the INSERTS will be committed one by one and there is no way to do that in batch due to business requirements. So application guys developed a very simple application (JAVA HIBERNATE) and deployed it on two nodes. each node is using a connection pool of size 128. The test plan is to do 9K INSERT every 2 seconds and repeat it 15 times to measure overall performance. Monitoring V$SESSION at test start time, we noticed that the two connection pool successfully achieved their 128 connections (256 total) successfully and all of them were INACTIVE at the beginning. But unfortunately the database performance is awful and is just 500 TPS While OEM is showing just at most 8 sessions in wait in the test duration.
My problem is:
Why db waited sessions are such low while the huge DML test was running?
To test db performance in the absence of third party app I developed a procedure that will create 20 jobs each inserting thousands of rows in the destination table. The test results show 32K TPS while a 1k BLOB is inserting as a field and 100K TPS without that field. The db active and waited sessions were very high in this test case.
So I have to find the root cause why app's connection pool is unable to place high load on DB.
thanks in advance.
Regards.