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
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.