为了客观反映信息，步骤略详细… 使用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%左右
![ora-07445[kcbo_link_ql] ora-07445[kggckh]bug引发实例意外关闭](https://www.herecours.com/d/file/efpub/2026/03-03/20260303162514844575.jpg?x-oss-process=style/bb "ora-07445[kcbo_link_ql] ora-07445[kggckh]bug引发实例意外关闭")
