Oracle 19.16 benchmarksql 下压测

来源：恒创科技编辑：恒创科技编辑部

2023-12-06 17:25:59

使用基准sql压测Oracle19c

操作系统基础信息

操作系统

[root@machine199 ~]# cat /etc/system-release

CentOS Linux 版本 7.9.2009（核心）

内存信息

[root@machine199 ~]# free -g

可用的免费共享缓冲区/缓存总数

内存：376 224 8 0 142 141

交换：7 3 4

[root@machine199 ~]$ cat /proc/meminfo | grep -i 巨大的

AnonHugePages：0 kB

HugePages_Total：100000

HugePages_免费：4952

HugePages_Rsvd：1013

HugePages_Surp: 0

巨页大小：2048 kB

[root@machine199 ~]# dmidecode|grep -A16 "内存设备"

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：32 GB

外形尺寸：DIMM

套装：1

定位器：A1

银行定位器：未指定

类型：DDR4

类型详细信息：同步注册（缓冲）

速度：3200 公吨/秒

制造商：002C0632002C

序列号：2D5F753E

资产标签：0F2109D1

零件编号：36ASF4G72PZ-3G2J3

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：32 GB

外形尺寸：DIMM

套装：1

定位器：A2

银行定位器：未指定

类型：DDR4

类型详细信息：同步注册（缓冲）

速度：2933 公吨/秒

制造商：002C00B3002C

序列号：2A5E68D6

资产标签：0F203661

零件编号：36ASF4G72PZ-2G9E2

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：32 GB

外形尺寸：DIMM

套装：1

定位器：A3

银行定位器：未指定

类型：DDR4

类型详细信息：同步注册（缓冲）

速度：3200 公吨/秒

制造商：00AD063200AD

序列号：22B3AB74

资产标签：022051D1

零件编号：HMA84GR7DJR4N-XN

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：32 GB

外形尺寸：DIMM

套装：1

定位器：A4

银行定位器：未指定

类型：DDR4

类型详细信息：同步注册（缓冲）

速度：3200 公吨/秒

制造商：002C0632002C

序列号：2D5F7509

资产标签：0F2109D1

零件编号：36ASF4G72PZ-3G2J3

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：32 GB

外形尺寸：DIMM

套装：1

定位器：A5

银行定位器：未指定

类型：DDR4

类型详细信息：同步注册（缓冲）

速度：2933 公吨/秒

制造商：002C00B3002C

序列号：2A5E679F

资产标签：0F203661

零件编号：36ASF4G72PZ-2G9E2

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：32 GB

外形尺寸：DIMM

套装：1

定位器：A6

银行定位器：未指定

类型：DDR4

类型详细信息：同步注册（缓冲）

速度：3200 公吨/秒

制造商：00AD063200AD

序列号：22B3AB57

资产标签：022051D1

零件编号：HMA84GR7DJR4N-XN

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：128 GB

外形尺寸：DIMM

套装：2

定位器：A7

银行定位器：未指定

类型：DDR4

类型详细信息：同步非易失性 LRDIMM

速度：2666 公吨/秒

制造商：008906320000

序列号：000026BA

资产标签：A22015C0

零件编号：NMA1XXD128GPS

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：128 GB

外形尺寸：DIMM

套装：2

定位器：A8

银行定位器：未指定

类型：DDR4

类型详细信息：同步非易失性 LRDIMM

速度：2666 公吨/秒

制造商：008906320000

序列号：0000241A

资产标签：A22015C0

零件编号：NMA1XXD128GPS

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：未知

数据宽度：未知

尺寸：未安装模块

外形尺寸：未知

套装：2

定位器：A9

银行定位器：未指定

类型：未知

类型详细信息：无

句柄 0x1109，DMI 类型 17，84 字节

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：128 GB

外形尺寸：DIMM

套装：2

定位器：A10

银行定位器：未指定

类型：DDR4

类型详细信息：同步非易失性 LRDIMM

速度：2666 公吨/秒

制造商：008906320000

序列号：00000A11

资产标签：A22116C0

零件编号：NMA1XXD128GPS

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：128 GB

外形尺寸：DIMM

套装：2

定位器：A11

银行定位器：未指定

类型：DDR4

类型详细信息：同步非易失性 LRDIMM

速度：2666 公吨/秒

制造商：008906320000

序列号：00000682

资产标签：A22116C0

零件编号：NMA1XXD128GPS

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：未知

数据宽度：未知

尺寸：未安装模块

外形尺寸：未知

套装：2

定位器：A12

银行定位器：未指定

类型：未知

类型详细信息：无

句柄 0x110C，DMI 类型 17，84 字节

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：32 GB

外形尺寸：DIMM

套装：3

定位器：B1

银行定位器：未指定

类型：DDR4

类型详细信息：同步注册（缓冲）

速度：3200 公吨/秒

制造商：002C0632002C

序列号：2D5F7616

资产标签：0F2109D1

零件编号：36ASF4G72PZ-3G2J3

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：32 GB

外形尺寸：DIMM

套装：3

定位器：B2

银行定位器：未指定

类型：DDR4

类型详细信息：同步注册（缓冲）

速度：2933 公吨/秒

制造商：002C00B3002C

序列号：2B9B1627

资产标签：0F204761

零件编号：36ASF4G72PZ-2G9E2

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：32 GB

外形尺寸：DIMM

套装：3

定位器：B3

银行定位器：未指定

类型：DDR4

类型详细信息：同步注册（缓冲）

速度：3200 公吨/秒

制造商：00AD063200AD

序列号：22B3AC09

资产标签：022051D1

零件编号：HMA84GR7DJR4N-XN

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：32 GB

外形尺寸：DIMM

套装：3

定位器：B4

银行定位器：未指定

类型：DDR4

类型详细信息：同步注册（缓冲）

速度：3200 公吨/秒

制造商：002C0632002C

序列号：2D5F756C

资产标签：0F2109D1

零件编号：36ASF4G72PZ-3G2J3

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：32 GB

外形尺寸：DIMM

套装：3

定位器：B5

银行定位器：未指定

类型：DDR4

类型详细信息：同步注册（缓冲）

速度：2933 公吨/秒

制造商：002C00B3002C

序列号：2A5E7457

资产标签：0F203661

零件编号：36ASF4G72PZ-2G9E2

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：32 GB

外形尺寸：DIMM

套装：3

定位器：B6

银行定位器：未指定

类型：DDR4

类型详细信息：同步注册（缓冲）

速度：3200 公吨/秒

制造商：00AD063200AD

序列号：22B3AB51

资产标签：022051D1

零件编号：HMA84GR7DJR4N-XN

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：128 GB

外形尺寸：DIMM

套装：4

定位器：B7

银行定位器：未指定

类型：DDR4

类型详细信息：同步非易失性 LRDIMM

速度：2666 公吨/秒

制造商：008906320000

序列号：00002445

资产标签：A22015C0

零件编号：NMA1XXD128GPS

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：128 GB

外形尺寸：DIMM

套装：4

定位器：B8

银行定位器：未指定

类型：DDR4

类型详细信息：同步非易失性 LRDIMM

速度：2666 公吨/秒

制造商：008906320000

序列号：000026B3

资产标签：A22015C0

零件编号：NMA1XXD128GPS

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：未知

数据宽度：未知

尺寸：未安装模块

外形尺寸：未知

套装：4

定位器：B9

银行定位器：未指定

类型：未知

类型详细信息：无

句柄 0x1115，DMI 类型 17，84 字节

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：128 GB

外形尺寸：DIMM

套装：4

定位器：B10

银行定位器：未指定

类型：DDR4

类型详细信息：同步非易失性 LRDIMM

速度：2666 公吨/秒

制造商：008906320000

序列号：00000B97

资产标签：A22116C0

零件编号：NMA1XXD128GPS

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：72 位

数据宽度：64位

大小：128 GB

外形尺寸：DIMM

套装：4

定位器：B11

银行定位器：未指定

类型：DDR4

类型详细信息：同步非易失性 LRDIMM

速度：2666 公吨/秒

制造商：008906320000

序列号：00000792

资产标签：A22116C0

零件编号：NMA1XXD128GPS

存储设备

数组句柄：0x1000

错误信息句柄：未提供

总宽度：未知

数据宽度：未知

尺寸：未安装模块

外形尺寸：未知

套装：4

定位器：B12

银行定位器：未指定

类型：未知

类型详细信息：无

句柄 0x1300，DMI 类型 19，31 字节

内存阵列映射地址

起始地址：0x00000000000

结束地址：0x0007FFFFFFF

中央处理器信息

[root@machine199 ~]# lscpu

架构：x86_64

CPU 操作模式：32 位、64 位

字节顺序：小尾数

CPU：32

在线 CPU(s) 列表：0-31

每个内核的线程数：2

每个插槽的核心数：8

插座：2

NUMA 节点：1

供应商 ID：GenuineIntel

CPU系列：6

型号：85

型号名称：Intel(R) Xeon(R) Gold 6250 CPU @ 3.90GHz

步进：7

中央处理器频率：4169.219

CPU最大MHz：4500.0000

CPU 最小 MHz：1200.0000

BogoMIPS：7800.00

虚拟化：VT-x

L1d 缓存：32K

L1i 缓存：32K

二级缓存：1024K

三级缓存：36608K

NUMA node0 CPU(s): 0-31

标志：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 秒

此平台支持 O_DIRECT 用于 open_datasync 和 open_sync。

比较使用 8kB 写入的文件同步方法：

（在 wal_sync_method 优先顺序中，除了 fdatasync 是 Linux 的默认值）

open_datasync 39924.473 ops/sec 25 usecs/op

fdatasync 37876.309 操作/秒 26 微秒/操作

fsync 11237.701 操作/秒 89 微秒/操作

fsync_writethrough 不适用

open_sync 11357.318 ops/sec 88 usecs/op

比较使用两个 8kB 写入的文件同步方法：

（在 wal_sync_method 优先顺序中，除了 fdatasync 是 Linux 的默认值）

open_datasync 20661.406 ops/sec 48 usecs/op

fdatasync 30981.034 操作/秒 32 微秒/操作

fsync 9830.211 操作/秒 102 微秒/操作

fsync_writethrough 不适用

open_sync 4618.354 ops/sec 217 usecs/op

比较 open_sync 与不同的写入大小：

（这个是为了比较不同写16kB的开销

open_sync 大小。）

1 * 16kB open_sync 写入 9169.323 ops/sec 109 usecs/op

2 * 8kB open_sync 写入 5058.727 ops/sec 198 usecs/op

4 * 4kB open_sync 写入 2332.890 ops/sec 429 usecs/op

8 * 2kB open_sync 写入 1264.765 ops/sec 791 usecs/op

16 * 1kB open_sync 写入 626.323 ops/sec 1597 usecs/op

测试是否支持非写入文件描述符上的 fsync：

（如果时间相似， fsync() 可以同步写入不同的数据

描述符。）

写入、fsync、关闭 8122.772 ops/sec 123 usecs/op

写入、关闭、fsync 9137.632 ops/sec 109 usecs/op

非同步的 8kB 写道：

写入 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 从环境中设置

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=假

*._log_parallelism_max=16

*._resource_manager_always_off=TRUE

*._rollback_segment_count=12000

*._serial_direct_read='自动'

*._undo_autotune=假

*._use_adaptive_log_file_sync='假'

*._use_single_log_writer='真'

*.audit_file_dest='/u01/app/oracle/admin/test/adump'

*.audit_trail='无'

*.commit_logging='BATCH'

*.commit_wait='现在等待'

*.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='测试'

*.deferred_segment_creation=假

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.enable_goldengate_replication=TRUE

*.event='28401 跟踪名称上下文永远，级别 1'

*.job_queue_processes=1000

*.local_listener='LISTENER_TEST'

*.log_archive_dest_1='位置=/data/oracle/archive'

*.log_buffer=4294967296

*.memory_max_target=0

*.memory_target=0

*.nls_language='美国'

*.nls_territory='美国'

*.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='独家'

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

图像.png

改成分区表，并且主键索引导致改成本地模式（不改本地索引，优化效果）

创建表 bmsql_history (

hist_id 整数，

h_c_id 整数，

h_c_d_id 整数，

h_c_w_id 整数，

h_d_id 整数，

h_w_id 整数，

h_date 时间戳，

h_数量（6,2），

h_data varchar2(24)

) 按哈希分区 (hist_id) 分区 64;--alter table bmsql_history 添加主键 (hist_id);alter table BMSQL_HISTORY 添加约束 pk_bmsql_history 主键 (hist_id) 使用索引本地 PCTFREE 60 INITRANS 100 MAXTRANS 255 COMPUTE STATISTICS;

2、遭遇序列争用

再次压测遇到遭遇seq: SQ - 内容序列争用

图像.png

用捞出想要的信息

图像.png

创建序列 bmsql_hist_id_seq 缓存 10000；

3、更改序列后再次遇到buffer busy waits

提前准备好等待处理表的热分区表

图像.png

正在等待处理，第一次发生是在显示热表的显示方式，然后调整后重新执行相关的闩锁显示的显示方式

图像.png

4、处理latch:redo allocation