为了客观反映信息，步骤略详细…
使用benchmarksql压测Oracle19c
操作系统基础信息
操作系统
[root@machine199 ~]# cat /etc/system-release
CentOS Linux release 7.9.2009 (Core)
内存信息
[root@machine199 ~]# free -g
total used free shared buff/cache available
Mem: 376 224 8 0 142 141
Swap: 7 3 4
[root@machine199 ~]$ cat /proc/meminfo | grep -i huge
AnonHugePages: 0 kB
HugePages_Total: 100000
HugePages_Free: 4952
HugePages_Rsvd: 1013
HugePages_Surp: 0
Hugepagesize: 2048 kB
[root@machine199 ~]# dmidecode|grep -A16 "Memory Device"
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 32 GB
Form Factor: DIMM
Set: 1
Locator: A1
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Registered (Buffered)
Speed: 3200 MT/s
Manufacturer: 002C0632002C
Serial Number: 2D5F753E
Asset Tag: 0F2109D1
Part Number: 36ASF4G72PZ-3G2J3
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 32 GB
Form Factor: DIMM
Set: 1
Locator: A2
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Registered (Buffered)
Speed: 2933 MT/s
Manufacturer: 002C00B3002C
Serial Number: 2A5E68D6
Asset Tag: 0F203661
Part Number: 36ASF4G72PZ-2G9E2
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 32 GB
Form Factor: DIMM
Set: 1
Locator: A3
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Registered (Buffered)
Speed: 3200 MT/s
Manufacturer: 00AD063200AD
Serial Number: 22B3AB74
Asset Tag: 022051D1
Part Number: HMA84GR7DJR4N-XN
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 32 GB
Form Factor: DIMM
Set: 1
Locator: A4
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Registered (Buffered)
Speed: 3200 MT/s
Manufacturer: 002C0632002C
Serial Number: 2D5F7509
Asset Tag: 0F2109D1
Part Number: 36ASF4G72PZ-3G2J3
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 32 GB
Form Factor: DIMM
Set: 1
Locator: A5
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Registered (Buffered)
Speed: 2933 MT/s
Manufacturer: 002C00B3002C
Serial Number: 2A5E679F
Asset Tag: 0F203661
Part Number: 36ASF4G72PZ-2G9E2
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 32 GB
Form Factor: DIMM
Set: 1
Locator: A6
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Registered (Buffered)
Speed: 3200 MT/s
Manufacturer: 00AD063200AD
Serial Number: 22B3AB57
Asset Tag: 022051D1
Part Number: HMA84GR7DJR4N-XN
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 128 GB
Form Factor: DIMM
Set: 2
Locator: A7
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Non-Volatile LRDIMM
Speed: 2666 MT/s
Manufacturer: 008906320000
Serial Number: 000026BA
Asset Tag: A22015C0
Part Number: NMA1XXD128GPS
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 128 GB
Form Factor: DIMM
Set: 2
Locator: A8
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Non-Volatile LRDIMM
Speed: 2666 MT/s
Manufacturer: 008906320000
Serial Number: 0000241A
Asset Tag: A22015C0
Part Number: NMA1XXD128GPS
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: Unknown
Data Width: Unknown
Size: No Module Installed
Form Factor: Unknown
Set: 2
Locator: A9
Bank Locator: Not Specified
Type: Unknown
Type Detail: None
Handle 0x1109, DMI type 17, 84 bytes
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 128 GB
Form Factor: DIMM
Set: 2
Locator: A10
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Non-Volatile LRDIMM
Speed: 2666 MT/s
Manufacturer: 008906320000
Serial Number: 00000A11
Asset Tag: A22116C0
Part Number: NMA1XXD128GPS
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 128 GB
Form Factor: DIMM
Set: 2
Locator: A11
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Non-Volatile LRDIMM
Speed: 2666 MT/s
Manufacturer: 008906320000
Serial Number: 00000682
Asset Tag: A22116C0
Part Number: NMA1XXD128GPS
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: Unknown
Data Width: Unknown
Size: No Module Installed
Form Factor: Unknown
Set: 2
Locator: A12
Bank Locator: Not Specified
Type: Unknown
Type Detail: None
Handle 0x110C, DMI type 17, 84 bytes
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 32 GB
Form Factor: DIMM
Set: 3
Locator: B1
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Registered (Buffered)
Speed: 3200 MT/s
Manufacturer: 002C0632002C
Serial Number: 2D5F7616
Asset Tag: 0F2109D1
Part Number: 36ASF4G72PZ-3G2J3
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 32 GB
Form Factor: DIMM
Set: 3
Locator: B2
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Registered (Buffered)
Speed: 2933 MT/s
Manufacturer: 002C00B3002C
Serial Number: 2B9B1627
Asset Tag: 0F204761
Part Number: 36ASF4G72PZ-2G9E2
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 32 GB
Form Factor: DIMM
Set: 3
Locator: B3
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Registered (Buffered)
Speed: 3200 MT/s
Manufacturer: 00AD063200AD
Serial Number: 22B3AC09
Asset Tag: 022051D1
Part Number: HMA84GR7DJR4N-XN
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 32 GB
Form Factor: DIMM
Set: 3
Locator: B4
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Registered (Buffered)
Speed: 3200 MT/s
Manufacturer: 002C0632002C
Serial Number: 2D5F756C
Asset Tag: 0F2109D1
Part Number: 36ASF4G72PZ-3G2J3
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 32 GB
Form Factor: DIMM
Set: 3
Locator: B5
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Registered (Buffered)
Speed: 2933 MT/s
Manufacturer: 002C00B3002C
Serial Number: 2A5E7457
Asset Tag: 0F203661
Part Number: 36ASF4G72PZ-2G9E2
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 32 GB
Form Factor: DIMM
Set: 3
Locator: B6
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Registered (Buffered)
Speed: 3200 MT/s
Manufacturer: 00AD063200AD
Serial Number: 22B3AB51
Asset Tag: 022051D1
Part Number: HMA84GR7DJR4N-XN
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 128 GB
Form Factor: DIMM
Set: 4
Locator: B7
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Non-Volatile LRDIMM
Speed: 2666 MT/s
Manufacturer: 008906320000
Serial Number: 00002445
Asset Tag: A22015C0
Part Number: NMA1XXD128GPS
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 128 GB
Form Factor: DIMM
Set: 4
Locator: B8
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Non-Volatile LRDIMM
Speed: 2666 MT/s
Manufacturer: 008906320000
Serial Number: 000026B3
Asset Tag: A22015C0
Part Number: NMA1XXD128GPS
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: Unknown
Data Width: Unknown
Size: No Module Installed
Form Factor: Unknown
Set: 4
Locator: B9
Bank Locator: Not Specified
Type: Unknown
Type Detail: None
Handle 0x1115, DMI type 17, 84 bytes
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 128 GB
Form Factor: DIMM
Set: 4
Locator: B10
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Non-Volatile LRDIMM
Speed: 2666 MT/s
Manufacturer: 008906320000
Serial Number: 00000B97
Asset Tag: A22116C0
Part Number: NMA1XXD128GPS
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: 72 bits
Data Width: 64 bits
Size: 128 GB
Form Factor: DIMM
Set: 4
Locator: B11
Bank Locator: Not Specified
Type: DDR4
Type Detail: Synchronous Non-Volatile LRDIMM
Speed: 2666 MT/s
Manufacturer: 008906320000
Serial Number: 00000792
Asset Tag: A22116C0
Part Number: NMA1XXD128GPS
--
Memory Device
Array Handle: 0x1000
Error Information Handle: Not Provided
Total Width: Unknown
Data Width: Unknown
Size: No Module Installed
Form Factor: Unknown
Set: 4
Locator: B12
Bank Locator: Not Specified
Type: Unknown
Type Detail: None
Handle 0x1300, DMI type 19, 31 bytes
Memory Array Mapped Address
Starting Address: 0x00000000000
Ending Address: 0x0007FFFFFFF
CPU信息
[root@machine199 ~]# lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 32
On-line CPU(s) list: 0-31
Thread(s) per core: 2
Core(s) per socket: 8
Socket(s): 2
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 85
Model name: Intel(R) Xeon(R) Gold 6250 CPU @ 3.90GHz
Stepping: 7
CPU MHz: 4169.219
CPU max MHz: 4500.0000
CPU min MHz: 1200.0000
BogoMIPS: 7800.00
Virtualization: VT-x
L1d cache: 32K
L1i cache: 32K
L2 cache: 1024K
L3 cache: 36608K
NUMA node0 CPU(s): 0-31
Flags: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe syscall nx pdpe1gb rdtscp lm constant_tsc art arch_perfmon pebs bts rep_good nopl xtopology nonstop_tsc aperfmperf eagerfpu pni pclmulqdq dtes64 monitor ds_cpl vmx smx est tm2 ssse3 sdbg fma cx16 xtpr pdcm pcid dca sse4_1 sse4_2 x2apic movbe popcnt tsc_deadline_timer aes xsave avx f16c rdrand lahf_lm abm 3dnowprefetch epb cat_l3 cdp_l3 invpcid_single intel_ppin intel_pt ssbd mba ibrs ibpb stibp ibrs_enhanced tpr_shadow vnmi flexpriority ept vpid fsgsbase tsc_adjust bmi1 hle avx2 smep bmi2 erms invpcid rtm cqm mpx rdt_a avx512f avx512dq rdseed adx smap clflushopt clwb avx512cd avx512bw avx512vl xsaveopt xsavec xgetbv1 cqm_llc cqm_occup_llc cqm_mbm_total cqm_mbm_local dtherm ida arat pln pts pku ospke avx512_vnni md_clear spec_ctrl intel_stibp flush_l1d arch_capabilities
磁盘性能
5 seconds per test O_DIRECT supported on this platform for open_datasync and open_sync. Compare file sync methods using one 8kB write: (in wal_sync_method preference order, except fdatasync is Linux's default) open_datasync 39924.473 ops/sec 25 usecs/op fdatasync 37876.309 ops/sec 26 usecs/op fsync 11237.701 ops/sec 89 usecs/op fsync_writethrough n/a open_sync 11357.318 ops/sec 88 usecs/op Compare file sync methods using two 8kB writes: (in wal_sync_method preference order, except fdatasync is Linux's default) open_datasync 20661.406 ops/sec 48 usecs/op fdatasync 30981.034 ops/sec 32 usecs/op fsync 9830.211 ops/sec 102 usecs/op fsync_writethrough n/a open_sync 4618.354 ops/sec 217 usecs/op Compare open_sync with different write sizes: (This is designed to compare the cost of writing 16kB in different write open_sync sizes.) 1 * 16kB open_sync write 9169.323 ops/sec 109 usecs/op 2 * 8kB open_sync writes 5058.727 ops/sec 198 usecs/op 4 * 4kB open_sync writes 2332.890 ops/sec 429 usecs/op 8 * 2kB open_sync writes 1264.765 ops/sec 791 usecs/op 16 * 1kB open_sync writes 626.323 ops/sec 1597 usecs/op Test if fsync on non-write file descriptor is honored: (If the times are similar, fsync() can sync data written on a different descriptor.) write, fsync, close 8122.772 ops/sec 123 usecs/op write, close, fsync 9137.632 ops/sec 109 usecs/op Non-sync'ed 8kB writes: write 349254.301 ops/sec 3 usecs/op
Oracle测试步骤
数据库参数
[oracle@machine199 ~]$
[oracle@machine199 ~]$ cat pfile.ora
test.__data_transfer_cache_size=0
test.__db_cache_size=182536110080
test.__inmemory_ext_roarea=0
test.__inmemory_ext_rwarea=0
test.__java_pool_size=0
test.__large_pool_size=1073741824
test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
test.__pga_aggregate_target=53687091200
test.__sga_target=193273528320
test.__shared_io_pool_size=536870912
test.__shared_pool_size=7516192768
test.__streams_pool_size=0
test.__unified_pga_pool_size=0
*._gc_policy_time=0
*._gc_undo_affinity=FALSE
*._log_parallelism_max=16
*._resource_manager_always_off=TRUE
*._rollback_segment_count=12000
*._serial_direct_read='AUTO'
*._undo_autotune=FALSE
*._use_adaptive_log_file_sync='FALSE'
*._use_single_log_writer='TRUE'
*.audit_file_dest='/u01/app/oracle/admin/test/adump'
*.audit_trail='NONE'
*.commit_logging='BATCH'
*.commit_wait='NOWAIT'
*.compatible='19.0.0'
*.control_file_record_keep_time=31
*.control_files='/data2/oradata/TEST/control01.ctl','/data2/oradata/TEST/control02.ctl'
*.db_block_size=8192
*.db_cache_size=10737418240
*.db_create_file_dest='/data/oracle/'
*.db_create_online_log_dest_1=' /data/oracle/'
*.db_file_multiblock_read_count=128
*.db_files=5000
*.db_name='test'
*.deferred_segment_creation=FALSE
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'
*.enable_goldengate_replication=TRUE
*.event='28401 TRACE NAME CONTEXT FOREVER, LEVEL 1'
*.job_queue_processes=1000
*.local_listener='LISTENER_TEST'
*.log_archive_dest_1='location=/data/oracle/archive'
*.log_buffer=4294967296
*.memory_max_target=0
*.memory_target=0
*.nls_language='AMERICAN'
*.nls_territory='AMERICA'
*.nls_timestamp_format='yyyy-mm-dd hh24:mi:ssxff'
*.open_cursors=20000
*.pga_aggregate_limit=107374182400
*.pga_aggregate_target=53687091200
*.processes=5000
*.remote_login_passwordfile='EXCLUSIVE'
*.result_cache_max_size=0
*.session_cached_cursors=300
*.sessions=5000
*.sga_max_size=193273528320
*.sga_target=193273528320
*.shared_pool_size=5368709120
*.transactions=5500
*.undo_retention=10800
*.undo_tablespace='UNDOTBS1'
1、遇见索引上的buffer busy waits
表改成分区表，并且主键索引改成local模式(不改造成local索引，优化效果无效)
create table bmsql_history (
hist_id integer,
h_c_id integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id integer,
h_w_id integer,
h_date timestamp,
h_amount number(6,2),
h_data varchar2(24)
) partition by hash(hist_id) partitions 64;
--alter table bmsql_history add primary key (hist_id);
alter table BMSQL_HISTORY add constraint pk_bmsql_history primary key (hist_id) using index local PCTFREE 60 INITRANS 100 MAXTRANS 255 COMPUTE STATISTICS;
2、遇到sequence争用
再次压测遇到seq: SQ - contention sequence争用
用脚本捞出想要的信息
create sequence bmsql_hist_id_seq cache 10000;
3、更改sequence后再次遇到buffer busy waits
同样根据第一步的buffer busy waits处理方式调整热分区表
只不过这次是在表上，第一次发生的是位于索引热块，调整后redo相关latch争用显现
4、处理 latch:redo allocation
alter system set "_log_parallelism_max" = 16 scope=spfile;
压测过程中的系统情况
将sga改成64G 转CPU bound为IO bound
压测的过程中几分钟后IO成为瓶颈并且等待事件为free buffer waits说明，sga太小了，同时IO也成为了瓶颈
结论
Oracle 180G sga CPU Bound
Oracle 64G sga IO Bound
到此为止，其他数据库性能为Oracle80%左右
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
