Skip to Main Content
  • Questions
  • Select Performance Too Bad on Oracle

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Emad.

Asked: August 14, 2019 - 12:30 pm UTC

Last updated: August 15, 2019 - 4:25 pm UTC

Version: 19C

Viewed 1000+ times

You Asked

Dear Tom,

I just faced an issue that I could not solve it. I have two virtual machines:

Client: 32Core, 32G Ram, Virtual Machine on HPE Server
Server: 96Core, 256G Ram, Virtual Machine on HPE Server

I did write a simple Java application using ojdbc8-12.2.0.1 and 16 threads doing "select 1 from dual where 1=2". The problem is total QPS which is around 46000.

Two servers are connected using QSFP+ 10G and iperf (Linux network monitoring tool) throughput is 1GB (8.2Gbps) per second so the link is OK

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
Total System Global Area 1.6750E+11 bytes
Fixed Size     30141128 bytes
Variable Size   1.9327E+10 bytes
Database Buffers  1.3046E+11 bytes
Redo Buffers    506728448 bytes

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

NAME                            TYPE    VALUE       
------------------------------- ------- ----------- 
cursor_bind_capture_destination string  memory+disk 
cursor_invalidation             string  IMMEDIATE   
cursor_sharing                  string  EXACT       
cursor_space_for_time           boolean FALSE       
open_cursors                    integer 3000        
session_cached_cursors          integer 512      


%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Session Events During High Load:
select event,total_waits,time_waited from v$system_event where wait_class in ('Application','Concurrency') order by time_waited desc ;


EVENT,TOTAL_WAITS,TIME_WAITED
cursor: pin S,18087,2044
library cache: mutex X,1019,377
resmgr:internal state change,29,289
library cache load lock,200,213
row cache mutex,381,172
cursor: pin S wait on X,222,85
library cache lock,65,35
row cache lock,112,32
library cache: bucket mutex X,70,14
latch: shared pool,527,13
row cache read,34,10
cursor: mutex X,11,8
cursor: mutex S,10,2

%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

Also I tried same client application exactly on the Oracle Server and same results. What I need is to tune oracle such how it returns more than 200,000 results per second.

Thanks in Advance

and Chris said...

Your database has 96 cores. But you're only running 16 threds. So you're only using ~16% of the available capacity on the database server. I doubt the database is the bottleneck here...

You should be able to get more QPS (queries per second?) by running more client threads!

But really, what exactly are you trying to prove by doing this?

Blasting a database with a toy query that returns no rows doesn't tell you much.

Try and simlulate a realistic load using your application. Only that will tell you whether you configuration can support your application.

PS - the values in v$system_event are cumulative totals since the database started. So these tell us almost nothing about how the database performed while you were hammering it.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

The Reason

Emad, August 14, 2019 - 1:32 pm UTC

Dear Chris,

Thank you for your super fast answer. Let me describe more.

> Your database has 96 cores. But you're only running 16 threds. So you're only using ~16% of the available capacity on the database server. I doubt the database is the bottleneck here
I tried with 64, 46000 becomes 64000. Not a big jump

> But really, what exactly are you trying to prove by doing this?
Blasting a database with a toy query that returns no rows doesn't tell you much.

Really this is not my query, but if I cant get 200K~400K using this no-table(dual) query, so the real physical table would be the same or worse. I checked with a one-row table, same results, (select count(1) from onerowtable;)

> PS - the values in v$system_event are cumulative totals since the database started. So these tell us almost nothing about how the database performed while you were hammering it.
Yes, exactly, but its a just-started oracle. Everytime I shut and start the DB to check changes effects.

I checked many things but no success, the total CPU on both client and server is not more than 11%. The network is moving 10MB or Less. I dont know what's the bottleneck. Would you guide me where to investigate ?

Chris Saxon
August 15, 2019 - 4:25 pm UTC

I checked many things but no success, the total CPU on both client and server is not more than 11%

Then you certainly have the capacity to get more throughput. It's just a matter of fixing your configuration.

If you have Diagnostics & Tuning licenses, you can check the ASH (active session history) & AWR (active workload repository) reports to see what's going in your server.

I'd also trace some of your sessions to see what they're doing. For more on how to do this & format the trace files, see https://blogs.oracle.com/sql/how-to-create-an-execution-plan#tkprof

Provide parameters

A reader, August 14, 2019 - 2:11 pm UTC

Provide all parameters such as cpu count and all related to sga pga.. shared pool...

All the conf of the network also.

Just a guess

A reader, August 14, 2019 - 7:27 pm UTC

Just a guess but I would check if the Java app on your client isn't consuming all the memory on your client.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database