在Oracle上选择性能太差

亲爱的汤姆，

我只是面临一个无法解决的问题。我有两个虚拟机:

客户端: 32核、32G Ram、HPE服务器上的虚拟机
服务器: 96Core，256G Ram，HPE服务器上的虚拟机

我确实使用ojdbc8-12.2.0.1和16个线程编写了一个简单的Java应用程序，执行 “从对偶中选择1，其中1 = 2”。问题是总QPS约为46000。

使用QSFP 10G和iperf (Linux网络监控工具) 连接两台服务器，吞吐量为每秒1gb (8.2Gbps)，因此链接正常

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

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      

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

高负载期间的会话事件:

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

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

我也在Oracle服务器上尝试了相同的客户端应用程序，结果相同。我需要的是调整oracle，这样它每秒返回200,000多个结果。

提前感谢

您的数据库有96个内核。但你只跑了16个threds。因此，您只使用数据库服务器上的16% 可用容量。我怀疑数据库是这里的瓶颈...

您应该能够通过运行更多的客户端线程来获得更多的QPS (每秒查询？)!

但说真的，你到底想通过这样做来证明什么？

用不返回行的玩具查询爆破数据库并不能告诉您太多信息。

尝试使用您的应用程序模拟现实的负载。只有这样才能告诉您配置是否可以支持您的应用程序。

PS-v $ system_event中的值是数据库启动以来的累计总数。因此，这些几乎没有告诉我们有关数据库在锤击时的表现。