热门搜索
分布式
数据库一体机
云数据库
建设银行
保险核心
密集交易
登录
注册
性能优化
技术分享/
文章详情
/
性能优化
赵国伟
2025/06/13
208
1
0
一、硬件性能分析
1.1、查看cpu负载
top 命令查看负载
top - 14:39:31 up 5:22, 1 user, load average: 0.02, 0.03, 0.06
Tasks: 168 total, 1 running, 167 sleeping, 0 stopped, 0 zombie
%Cpu0 : 0.3 us, 0.0 sy, 0.0 ni, 99.7 id, 0.0 wa, 0.0 hi, 0.0 si, 0.0 st
%Cpu1 : 0.0 us, 0.0 sy, 0.0 ni, 99.3 id, 0.0 wa, 0.3 hi, 0.3 si, 0.0 st
MiB Mem : 2888.3 total, 735.0 free, 972.5 used, 1180.9 buff/cache
MiB Swap: 4044.0 total, 4044.0 free, 0.0 used. 1630.6 avail Mem
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
1346 dmdba 20 0 6631252 700760 63164 S 0.3 23.7 0:41.97 dmserver
……
127 root -51 0 0 0 0 S 0.0 0.0 0:00.00 irq/47-pciehp
load average（系统负载）
load average: 0.02, 0.03, 0.06 三个数值分别代表过去 1分钟，5分钟，15分钟的系统负载该值越大系统越繁忙，通常认为该值小于系统cpu核心数则认为系统负载正常
%Cpu0~n （特定cpu核心的使用状态细分）重点关注 us(用户),sy(系统),id(空闲),wa(等待)
总结如下：
- 1、id 低且 us 高（如 us=80%，id=20%）：
原因：用户态程序（如应用程序、数据库）占用大量 CPU，属于 CPU 密集型负载。
排查：用 top 按 P 键排序，定位高 us 的进程（如 Java 程序、编译任务）。 - 2、id 低且 sy 高（如 sy=70%，id=30%）：
原因：内核态程序（如驱动、系统调用）占用大量 CPU，可能是硬件驱动异常或系
统调用频繁。
排查：检查内核日志（dmesg）或用 perf 工具分析内核函数调用。 - 3、wa 高（如 wa=50%，id=40%）：
原因：CPU 空闲但在等待 I/O 操作（如磁盘读写、网络请求），属于 I/O 密集型
负载。
排查：用 iostat -x 查看磁盘 I/O 队列（avgqu-sz）和等待时间（await），或用 iotop
定位高 I/O 进程。
1.2、查看磁盘IO负载情况
[root@STANDALONE ~]# iostat -x
Linux 4.19.90-24.4.v2101.ky10.x86_64 (STANDALONE) 2025年06月04日 _x86_64_ (2 CPU)
avg-cpu: %user %nice %system %iowait %steal %idle
0.07 0.00 0.70 0.00 0.00 99.23
Device r/s rkB/s rrqm/s %rrqm r_await rareq-sz w/s wkB/s wrqm/s %wrqm w_await wareq-sz d/s dkB/s drqm/s %drqm d_await dareq-sz aqu-sz %util
dm-0 0.62 42.30 0.00 0.00 1.01 67.88 0.65 14.78 0.00 0.00 1.10 22.68 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.07
dm-1 0.00 0.10 0.00 0.00 0.33 22.85 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
dm-2 0.00 0.05 0.00 0.00 1.00 21.75 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
sda 0.68 45.02 0.00 0.20 0.81 66.61 0.59 14.87 0.06 9.29 0.98 25.15 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.07
scd0 0.00 0.05 0.00 0.00 1.57 37.43 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00 0.00
[root@STANDALONE ~]#
关键指标解读
%util：磁盘利用率，越高代表磁盘越繁忙。
aqu-sz：平均队列长度，越高说明队列严重堆积（若 aqu-sz 高且 %util 高，瓶颈更严重）。
r_await/w_await：读/写平均等待时间（SSD 正常应低于 5ms），若较高可能由高队列或硬件老化导致。
磁盘读写测试
time dd if=/dev/zero of=/sda/test-random-write-out.img bs=32k count=32768 conv=direct,fdatasync
time dd if=/sda/test-write-out.img of=/dev/null bs=32k count=32768
time dd if=/sda/test-write-out.img of=/sda/test-write.img bs=32k count=32768 conv=direct,fdatasync
增加队列深度（如32）提升性能（NVMe SSD）
dd if=/dev/zero of=/data/test.img bs=32k count=32768 oflag=direct,fdatasync,queue_depth=32
1.3、进程资源占用
1.3.1监控进程 CPU
[root@STANDALONE ~]# pidstat -u 1 1
Linux 4.19.90-24.4.v2101.ky10.x86_64 (STANDALONE) 2025年06月04日 _x86_64_ (2 CPU)
15时58分18秒 UID PID %usr %system %guest %wait %CPU CPU Command
15时58分19秒 1000 1346 0.00 0.98 0.00 0.00 0.98 0 dmserver
15时58分19秒 993 1630 0.00 0.98 0.00 0.00 0.98 1 ukui-greeter
15时58分19秒 0 23199 0.00 0.98 0.00 0.00 0.98 0 pidstat
平均时间: UID PID %usr %system %guest %wait %CPU CPU Command
平均时间: 1000 1346 0.00 0.98 0.00 0.00 0.98 - dmserver
平均时间: 993 1630 0.00 0.98 0.00 0.00 0.98 - ukui-greeter
平均时间: 0 23199 0.00 0.98 0.00 0.00 0.98 - pidstat
%usr 进程在用户空间占用的 CPU 百分比
%system 进程在内核空间占用的 CPU 百分比
%guest 进程在虚拟机（Guest）中占用的 CPU 百分比（如 Docker 容器）
%CPU 总 CPU 占用率（%usr + %system + %guest）
1.3.2监控进程内存
[root@STANDALONE ~]# pidstat -r 1 1
Linux 4.19.90-24.4.v2101.ky10.x86_64 (STANDALONE) 2025年06月04日 _x86_64_ (2 CPU)
15时56分12秒 UID PID minflt/s majflt/s VSZ RSS %MEM Command
15时56分13秒 0 23196 918.63 0.00 219024 7424 0.25 pidstat
平均时间: UID PID minflt/s majflt/s VSZ RSS %MEM Command
平均时间: 0 23196 918.63 0.00 219024 7424 0.25 pidstat
[root@STANDALONE ~]#
RSS 进程占用的物理内存（Resident Set Size，单位：KB）
%MEM 进程占用内存占系统总内存的百分比
1.3.3监控进程磁盘I/O
[root@STANDALONE ~]# pidstat -d 1 1
Linux 4.19.90-24.4.v2101.ky10.x86_64 (STANDALONE) 2025年06月04日 _x86_64_ (2 CPU)
16时01分58秒 UID PID kB_rd/s kB_wr/s kB_ccwr/s iodelay Command
16时01分59秒 1000 1346 0.00 312.00 0.00 0 dmserver
平均时间: UID PID kB_rd/s kB_wr/s kB_ccwr/s iodelay Command
平均时间: 1000 1346 0.00 312.00 0.00 0 dmserver
[root@STANDALONE ~]#
kB_rd/s 进程每秒从磁盘读取的数据量（单位：KB）
kB_wr/s 进程每秒写入磁盘的数据量（单位：KB）
kB_ccwr/s 进程每秒取消的写入数据量（如缓存中被覆盖的未写入数据）
1.3.4 监控指定进程所包含线程
1346进程包含线程按cpu使用率降序取前十条
[root@STANDALONE ~]# pidstat -p 1346 -ut|sort -k 9 -r|head -10
16时14分50秒 UID TGID TID %usr %system %guest %wait %CPU CPU Command
16时14分50秒 1000 1346 - 0.06 0.15 0.00 0.01 0.21 0 dmserver
16时14分50秒 1000 - 2358 0.01 0.02 0.00 0.03 0.03 1 |__dm_sched_thd
16时14分50秒 1000 - 2357 0.01 0.01 0.00 0.00 0.02 0 |__nlgn_task_threa
16时14分50秒 1000 - 2325 0.01 0.01 0.00 0.02 0.01 1 |__dm_purge_thd
16时14分50秒 1000 - 1346 0.00 0.01 0.00 0.01 0.01 0 |__dmserver
16时14分50秒 1000 - 2210 0.00 0.00 0.00 0.01 0.01 0 |__dm_redolog_thd
16时14分50秒 1000 - 2354 0.00 0.00 0.00 0.02 0.00 1 |__dm_wrkgrp_thd
16时14分50秒 1000 - 2352 0.00 0.00 0.00 0.02 0.00 1 |__dm_wrkgrp_thd
16时14分50秒 1000 - 2351 0.00 0.00 0.00 0.02 0.00 1 |__dm_wrkgrp_thd
[root@STANDALONE ~]#
1346进程包含线程按上下文切换降序取前十条
[root@STANDALONE ~]# pidstat -p 1346 -wt|sort -k 5 -r|head -10
Linux 4.19.90-24.4.v2101.ky10.x86_64 (STANDALONE) 2025年06月04日 _x86_64_ (2 CPU)
16时48分29秒 UID TGID TID cswch/s nvcswch/s Command
16时48分29秒 1000 - 2325 1.97 0.01 |__dm_purge_thd
16时48分29秒 1000 - 2358 1.14 3.09 |__dm_sched_thd
16时48分29秒 1000 - 1973 1.06 0.00 |__dm_pwr_thd
16时48分29秒 1000 - 2354 1.00 0.00 |__dm_wrkgrp_thd
16时48分29秒 1000 - 2353 1.00 0.00 |__dm_wrkgrp_thd
16时48分29秒 1000 - 2352 1.00 0.00 |__dm_wrkgrp_thd
16时48分29秒 1000 - 2351 1.00 0.00 |__dm_wrkgrp_thd
16时48分29秒 1000 - 2350 1.00 0.00 |__dm_wrkgrp_thd
[root@STANDALONE ~]#
1346进程包含线程按磁盘读取量降序取前三十条
[root@STANDALONE ~]# pidstat -p 1346 -dt|sort -k 5 -r|head -30
Linux 4.19.90-24.4.v2101.ky10.x86_64 (STANDALONE) 2025年06月04日 _x86_64_ (2 CPU)
16时24分35秒 UID TGID TID kB_rd/s kB_wr/s kB_ccwr/s iodelay Command
16时24分35秒 1000 1346 - 5.01 8.26 0.00 37 dmserver
16时24分35秒 1000 - 1346 2.78 0.00 0.00 37 |__dmserver
16时24分35秒 1000 - 1922 0.37 0.43 0.00 0 |__dm_osio_thd
16时24分35秒 1000 - 1924 0.23 0.44 0.00 0 |__dm_osio_thd
16时24分35秒 1000 - 1921 0.21 0.40 0.00 0 |__dm_osio_thd
16时24分35秒 1000 - 1935 0.18 0.50 0.00 0 |__dm_osio_thd
16时24分35秒 1000 - 1926 0.18 0.49 0.00 1 |__dm_osio_thd
16时24分35秒 1000 - 2209 0.17 0.03 0.00 12 |__dm_chkpnt_thd
16时24分35秒 1000 - 1932 0.11 0.35 0.00 0 |__dm_osio_thd
16时24分35秒 1000 - 1919 0.10 0.44 0.00 0 |__dm_osio_thd
16时24分35秒 1000 - 1929 0.10 0.41 0.00 1 |__dm_osio_thd
16时24分35秒 1000 - 1927 0.09 0.49 0.00 2 |__dm_osio_thd
16时24分35秒 1000 - 1931 0.08 0.32 0.00 1 |__dm_osio_thd
16时24分35秒 1000 - 1923 0.07 0.75 0.00 0 |__dm_osio_thd
16时24分35秒 1000 - 1928 0.06 0.62 0.00 1 |__dm_osio_thd
16时24分35秒 1000 - 1920 0.05 0.42 0.00 0 |__dm_osio_thd
16时24分35秒 1000 - 1930 0.02 0.53 0.00 0 |__dm_osio_thd
16时24分35秒 1000 - 1925 0.02 0.48 0.00 0 |__dm_osio_thd
16时24分35秒 1000 - 1934 0.02 0.47 0.00 0 |__dm_osio_thd
16时24分35秒 1000 - 2210 0.00 0.67 0.00 257 |__dm_redolog_thd
16时24分35秒 1000 - 2358 0.00 0.02 0.00 0 |__dm_sched_thd
16时24分35秒 1000 - 1973 0.00 0.00 0.00 4 |__dm_pwr_thd
16时24分35秒 1000 - 2357 0.00 0.00 0.00 0 |__nlgn_task_threa
16时24分35秒 1000 - 2354 0.00 0.00 0.00 0 |__dm_wrkgrp_thd
16时24分35秒 1000 - 2353 0.00 0.00 0.00 0 |__dm_wrkgrp_thd
16时24分35秒 1000 - 2352 0.00 0.00 0.00 0 |__dm_wrkgrp_thd
16时24分35秒 1000 - 2351 0.00 0.00 0.00 0 |__dm_wrkgrp_thd
16时24分35秒 1000 - 2350 0.00 0.00 0.00 0 |__dm_wrkgrp_thd
1346进程包含线程按磁盘写入量降序取前三十条
[root@STANDALONE ~]# pidstat -p 1346 -dt|sort -k 6 -r|head -30
16时24分39秒 UID TGID TID kB_rd/s kB_wr/s kB_ccwr/s iodelay Command
16时24分39秒 1000 1346 - 5.01 8.26 0.00 37 dmserver
Linux 4.19.90-24.4.v2101.ky10.x86_64 (STANDALONE) 2025年06月04日 _x86_64_ (2 CPU)
16时24分39秒 1000 - 1923 0.07 0.75 0.00 0 |__dm_osio_thd
16时24分39秒 1000 - 2210 0.00 0.67 0.00 257 |__dm_redolog_thd
16时24分39秒 1000 - 1928 0.06 0.62 0.00 1 |__dm_osio_thd
16时24分39秒 1000 - 1930 0.02 0.53 0.00 0 |__dm_osio_thd
16时24分39秒 1000 - 1935 0.18 0.50 0.00 0 |__dm_osio_thd
16时24分39秒 1000 - 1927 0.09 0.49 0.00 2 |__dm_osio_thd
16时24分39秒 1000 - 1926 0.18 0.49 0.00 1 |__dm_osio_thd
16时24分39秒 1000 - 1925 0.02 0.48 0.00 0 |__dm_osio_thd
16时24分39秒 1000 - 1934 0.02 0.47 0.00 0 |__dm_osio_thd
16时24分39秒 1000 - 1924 0.23 0.44 0.00 0 |__dm_osio_thd
16时24分39秒 1000 - 1919 0.10 0.44 0.00 0 |__dm_osio_thd
16时24分39秒 1000 - 1922 0.37 0.43 0.00 0 |__dm_osio_thd
16时24分39秒 1000 - 1920 0.05 0.42 0.00 0 |__dm_osio_thd
16时24分39秒 1000 - 1929 0.10 0.41 0.00 1 |__dm_osio_thd
16时24分39秒 1000 - 1921 0.21 0.40 0.00 0 |__dm_osio_thd
16时24分39秒 1000 - 1932 0.11 0.35 0.00 0 |__dm_osio_thd
16时24分39秒 1000 - 1931 0.08 0.32 0.00 1 |__dm_osio_thd
16时24分39秒 1000 - 2209 0.17 0.03 0.00 12 |__dm_chkpnt_thd
16时24分39秒 1000 - 2358 0.00 0.02 0.00 0 |__dm_sched_thd
16时24分39秒 1000 - 1973 0.00 0.00 0.00 4 |__dm_pwr_thd
16时24分39秒 1000 - 1346 2.78 0.00 0.00 37 |__dmserver
16时24分39秒 1000 - 2357 0.00 0.00 0.00 0 |__nlgn_task_threa
16时24分39秒 1000 - 2354 0.00 0.00 0.00 0 |__dm_wrkgrp_thd
16时24分39秒 1000 - 2353 0.00 0.00 0.00 0 |__dm_wrkgrp_thd
16时24分39秒 1000 - 2352 0.00 0.00 0.00 0 |__dm_wrkgrp_thd
16时24分39秒 1000 - 2351 0.00 0.00 0.00 0 |__dm_wrkgrp_thd
16时24分39秒 1000 - 2350 0.00 0.00 0.00 0 |__dm_wrkgrp_thd
[root@STANDALONE ~]#
二、问题SQL定位排查方法
不良的SQL语句最明显的问题，就会造成服务器资源使用率的飙升，影响其他正常的业务无法获取资源造成等待，通过对操作系统cpu及内存的查看，来识别系统资源的瓶颈。
2.1通过活动会话识别慢SQL
2.1.1.实时会话执行时间
通过执行以下SQL可以快速定位正在运行的SQL语句，找到run_time数值大，且出现频率高的SQL就是需要重点进行处理的。
SQL> select
2 SESS_ID ,
3 state ,
4 thrd_id ,
5 dbms_lob.substr(sf_get_session_sql(sess_id)) sql_text,
6 datediff(ss, last_send_time, sysdate) run_time
7 from
8 v$sessions
9 where
10 state<>'IDLE'
11 and sess_id != sessid
12 order by
13 5 desc;
行号 SESS_ID state thrd_id sql_text run_time
---------- -------------------- ------ ----------- ------------------------------ -----------
1 140041302802992 ACTIVE 24496 delete test_table where id=99; 194
已用时间: 0.531(毫秒). 执行号:486016.
SQL>
2.1.2.查看数据库最慢的sql
SQL> select * from V$LONG_EXEC_SQLS order by exec_time desc;
行号 SESS_ID SQL_ID SQL_TEXT EXEC_TIME FINISH_TIME N_RUNS SEQNO
---------- -------------------- ----------- --------------------------------------------------------------------------------------- -------------------- ------------------- ----------- -----------
TRX_ID SESS_SEQ EXEC_ID
-------------------- ----------- -----------
1 139683476720512 17 insert into test_table(id,name) select level,'aaa' FROM DUAL CONNECT BY LEVEL<=1000000; 1908 2025-06-04 17:52:05 1 12
144179 6 605
已用时间: 2.546(毫秒). 执行号:1003.
2.2.CPU使用情况分析
dm_sql_thd线程为sql执行线程，如果存在此线程cpu使用率高，则可以通过线程号与数据库中v$sessions视图中的THRD_ID，进行关联查询到相对应sql;
[dmdba@STANDALONE ~]$ pidstat -p 1353 -ut|grep dm_sql_thd
09时42分02秒 1000 - 3644 0.00 0.00 0.00 0.00 0.00 0 |__dm_sql_thd
[dmdba@STANDALONE ~]$ disql sysdba/Dameng_123@dm8
服务器[10.12.10.124:5237]:处于普通打开状态
登录使用时间 : 5.167(ms)
disql V8
SQL> select * from v$sessions where thrd_id = 3644;
行号 SESS_ID SESS_SEQ SQL_TEXT STATE N_STMT N_USED_STMT SEQ_NO CURR_SCH USER_NAME TRX_ID CREATE_TIME CLNT_TYPE
---------- -------------------- ----------- ------------ ----- ----------- ----------- ----------- -------- --------- -------------------- -------------------------- ---------
TIME_ZONE CHK_CONS CHK_IDENT RDONLY INS_NULL COMPILE_FLAG AUTO_CMT DDL_AUTOCMT RS_FOR_QRY CHK_NET ISO_LEVEL CLNT_HOST APPNAME CLNT_IP OSNAME
--------- -------- --------- ------ -------- ------------ -------- ----------- ---------- ------- ----------- ---------- ------- ------------------------- ------
CONN_TYPE VPOOLADDR RUN_STATUS MSG_STATUS LAST_RECV_TIME LAST_SEND_TIME DCP_FLAG THRD_ID CONNECTED PORT_TYPE SRC_SITE
----------- -------------------- ---------- ---------- -------------------------- -------------------------- -------- ----------- ----------- ----------- -----------
MAL_ID CONCURRENT_FLAG CUR_LINENO CUR_MTDNAME CUR_SQLSTR CLNT_VER SQL_ID EID CLIENT_INFO CLIENT_IDENTIFIER MODULE ACTION
-------------------- --------------- ----------- ----------- ---------- -------- ----------- -------------------- ----------- ----------------- ------ ------
HEART_BEAT_INTERVAL HEART_BEAT_TIMEOUT TMP_USED_TYPE TMP_USED_EXTENT_NUM
------------------- ------------------ ------------- -------------------
1 140499969223072 10 select user; IDLE 64 1 3 SYSDBA SYSDBA 0 2025-06-05 09:35:28.000000 SQL3
+08:00 N N N Y N N Y N N 1 STANDALONE disql ::ffff:10.12.10.124:55868 Linux
HOMOGENEOUS 140499969156952 IDLE SEND 2025-06-05 09:35:28.116668 2025-06-05 09:35:28.116736 N 3644 1 0 65535
NULL 0 NULL NULL NULL 8.1.4.6 -1 -1
0 0 NULL 0
已用时间: 1.977(毫秒). 执行号:1101.
SQL>
2.3.内存使用情况分析
查询占用内存高的SQL语句
SQL> select SESSID,SQL_TXT,LOGIC_READ_CNT,PHY_READ_CNT,MAX_MEM_USED/1024 MEM_USED_MB from V$SQL_STAT order by MAX_MEM_USED desc ;
行号 SESSID SQL_TXT
---------- -------------------- ----------------------------------------------------------------------------------------------------------------------------
LOGIC_READ_CNT PHY_READ_CNT MEM_USED_MB
-------------------- -------------------- --------------------
1 140499788989280 select SESSID,SQL_TXT,LOGIC_READ_CNT,PHY_READ_CNT,MAX_MEM_USED/1024 MEM_USED_MB from V$SQL_STAT order by MAX_MEM_USED desc ;
4 0 5
2 140499969223072 select user;
0 0 0
已用时间: 32.753(毫秒). 执行号:1102.
SQL>
2.4.使用日志分析工具
上传解压
unzip sqllog实时解析工具V1.3.1.zip
mv sqllog实时解析工具V1.3.1/ sqllog
修改配置文件
[dmdba@STANDALONE ~]$ cd sqllog/
[dmdba@STANDALONE sqllog]$ vim application.yml
server:
port: 9000
parselogconfig:
# 1 分析本地sqllog日志 2 web端的数据展示 3 1+2的模式
enableMode: 3
# sqlLog的分析路径
sqlLogPath: /dm8/log
# linux写法
#sqlLogPath: /opt/sqllog
# 不填的话，默认分析所有,此处是为了规避一台数据库多台实例日志
dbInstanceName:
## 0表示从头开始分析,解析所有的SQLLOG日志 1表示从最新生成的日志开始分析 2 仅分析已有SQLLOG日志并生成sql文档
parseLogMode: 0
## 设置过滤条件
filtercond:
ignoreExceptionSql: 1 ## 1表示开启 0表示关闭 表示是否忽略异常SQL(例如：执行错误/未找到exectime)
fillFetchDataSql: 0 ## 1表示开启 0表示关闭 表示解析fetch data填充SQL的场景,形如右侧这种:( [SEL] EXECTIME: 0(ms) ROWCOUNT: 1056(rows).)
paramsListNum: 10 #解析单个SQL时参数列表上限数量值，默认值为10
condRelation: AND ## OR/AND 表示以下解析时过滤条件的关联方式
exectime: 10 ## 解析大于等于多少毫秒的SQL,不建议设置为0(全量解析,批量入库可能存在瓶颈)
rowcount: # 解析大于等于多少影响行数的SQL,默认不配置
dbmstype:
dbName: DM # DM或INFLUXDB
dmdbmsconfig:
dbUrl: jdbc:dm://10.12.10.124:5237
dbUserName: dmtest
dbPassword: Dameng_123
dbDriver: dm.jdbc.driver.DmDriver
dbSqllogTableName: SQLLOG_001 #存数据的表名 自定义表名必须以SQLLOG_开头命名
dataBatchSize: 2000 #批量提交条数
dataQueueMaxSize: 10000 #数据入库前的缓冲队列的最大深度(FIFO队列)(理论值不可低于dataBatchSize * maximumPoolSize * 1.5)
maximumPoolSize: 4 #入库线程池的最大线程数
querySqlParallel: 4 # web查询sql时的并行度即PARALLEL参数，0表示不加并行hint,例如 4表示并行度hint PARALLEL(4)
dataSaveMinute: 0 #数据在库内保留的时长，O表示长期保存 600表示600分钟 清理调度执行为clearDataListCron参数的时间
spiltDataFileNum: 0 #是否对sqllog日志文件进行切分存储(分表)，默认为0不切分 1表示每一个sqllog文件存储到一个表中
influxdbconfig:
dbUrl: http://127.0.0.1:8086
token: T68SdNIAONHj_hckD2CsTPi0Dn5PqMfQv__bpjOfGBwSE54cvW6579S2jB-s-4kdJTZ9r2hhiL4Z-hfhIJK6UA==
bucket: sqllog_oa_20240125
org: dameng
dataBatchSize: 2000 #批量提交条数
bufferLimit: 300000 # 客户端为失败的写入维护缓冲区，以便稍后重试。有助于克服临时网络问题或InfluxDB负载高峰。当缓冲区已满并且写入新点时，缓冲区中最旧的条目将丢失。
################以下配置项默认不进行改动##############################
otherconfig:
readLogBufferSize: 10240 ## 每次读取sqllog文件的大小(10240=10M)
readLogFinishThreadSleepTime: 1000 ##读取完本批日志后线程的睡眠时间(1000=1s)
lineGroupsListSize: 500 ##维护全局数据的列表(LRU链表),值越大占用内存会越大.(理论值应该是数据库的MAX_SESSION*1.5)
fillFetchDataCacheListSize: 5000 ##当参数fillFetchDataSql=1时生效,表示:去缓存的SQL列表中中寻找上层SQL,若未找到数据可放大该值
sqlLogFileCharset: AUTO # AUTO / GBK / UTF-8 #解析sqlog文件时的编码格式，AUTO为自适应
configPwdEncode: 0 #对配置文件的密码进行脱敏， 0 关闭 1 开启（因有乱码问题，开启后会清除配置文件中的注释）
watchDirMonitorDealy: 2000 ## 每次监听sqllog目录文件变动的频率(2000=2s)
watchDirMonitorMaxDepth: 1 ## 监听sqllog目录文件的深度(1=同级目录同时不监听子目录)
echartDataSplitSize: 5 # web端图表中分段中切分的个数(n+1)
echartSqlTypeArr: SEL|INS|UPD|DEL|DDL # web端图表中显示的语句类型
echartSqlTimeArr: 0ms_100ms|100ms_500ms|500ms_1s|1s_5s|5s_10s|10s_30s|30s_1min|1min_5min|5min_10min|10min__
schedulingconfig:
initialDelay: 1500 ## 程序启动后,首次执行解析任务的延迟(1500=1.5s)
loopParseSqlFixedRate: 5000 # ##读取完所有sqllog日志后线程的睡眠时间(5000=5s)
clearDataListCron: "0 35 23 * * ?" # 每天定时清理日志/过期SQL数据的时间(0 35 23 * * ? 表示每晚23:35分)
logging:
config: classpath:log4j2.xml
[dmdba@STANDALONE sqllog]$
启动
nohup java -jar -Xms256m -Xmx4096m sql_realtime_parse_tool-1.3.1_20250116.jar > ./sql_realtime_parse_tool.log 2>&1
查看分析结果
2.5.事务等待检查
SQL执行缓慢，也有可能是业务逻辑设计不合理导致的事务阻塞，应检查是否存在事务等待信息。提供执行以下SQL可以查询是否存在锁等待的情况。
SQL> select
2 tw.id as "等待的事务ID",
3 tw.wait_for_id as "正在运行的事务ID",
4 s.sess_id as "等待的会话",
5 ws.sess_id as "正在执行的会话",
6 s.sql_text as "等待的sql",
7 ws.sql_text as "正在执行的sql" ,
8 'SP_CLOSE_SESSION('||ws.sess_id||');' "关闭会话的语句",
9 ws.thrd_id "会话线程"
10 from v$trxwait tw left join v$sessions s
11 on tw.id=s.trx_id
12 left join v$sessions ws
13 on tw.wait_for_id=ws.trx_id;
行号 等待的事务ID 正在运行的事务ID 等待的会话 正在执行的会话 等待的sql 正在执行的sql
---------- -------------------- ----------------------- -------------------- --------------------- ------------------------------ --------------------------------------------
关闭会话的语句 会话线程
---------------------------- ------------
1 152380 152371 121647312 120466608 delete test_table where id=99; update test_table set name='bb' where id=99;
SP_CLOSE_SESSION(120466608); 19064
已用时间: 33.225(毫秒). 执行号:44501.
SQL>
三、SQL优化分析
3.1.查看执行计划
达梦客户端直接点击菜单栏按钮或按F9生成执行计划
执行EXPLAIN +SQL 生成执行计划
SQL> explain select * from test_table;
1 #NSET2: [118, 1000000, 64]
2 #PRJT2: [118, 1000000, 64]; exp_num(3), is_atom(FALSE)
3 #CSCN2: [118, 1000000, 64]; INDEX33557127(test_table); btr_scan(1)
已用时间: 0.771(毫秒). 执行号:0.
SQL>
3.2.ET分析
开启ET功能配置 INI 参数中的 ENABLE_MONITOR=1、MONITOR_SQL_EXEC=1 开启该功能。
–查询数据库是否开启相关功能
SQL> select para_name,para_value from v$dm_ini where para_name in ('ENABLE_MONITOR','MONITOR_SQL_EXEC');
行号 para_name para_value
---------- ---------------- ----------
1 ENABLE_MONITOR 1
2 MONITOR_SQL_EXEC 0
已用时间: 8.563(毫秒). 执行号:44103.
SQL>
–参数均为动态参数，可直接调用系统函数进行修改
SP_SET_PARA_VALUE(1,'ENABLE_MONITOR',1);
--MONITOR_SQL_EXEC建议会话级开启，仅在当前会话生效
call sf_set_session_para_value('MONITOR_SQL_EXEC',1)
执行完成后，点击执行号，弹出ET分析页面
SQL> select * from test_table where id=999;
行号 id name
---------- ----------- ----
1 999 aaa
已用时间: 1.246(毫秒). 执行号:44105.
SQL> ET(44105)
2 ;
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS HASH_CONFLICT
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- --------------------
DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
----------------- --------------- --------------------
1 PRJT2 3 2.17% 5 2 4 0 0 0 0
NULL NULL 0
2 DLCK 8 5.80% 4 0 2 0 0 0 0
NULL NULL 0
3 BLKUP2 15 10.87% 3 3 4 0 0 0 0
NULL NULL 0
行号 OP TIME(US) PERCENT RANK SEQ N_ENTER MEM_USED(KB) DISK_USED(KB) HASH_USED_CELLS HASH_CONFLICT
---------- ------ -------------------- ------- -------------------- ----------- ----------- -------------------- -------------------- -------------------- --------------------
DHASH3_USED_CELLS DHASH3_CONFLICT HASH_SAME_VALUE
----------------- --------------- --------------------
4 SSEK2 22 15.94% 2 4 2 0 0 0 0
NULL NULL 0
5 NSET2 90 65.22% 1 1 3 0 0 0 0
NULL NULL 0
已用时间: 62.882(毫秒). 执行号:44106.
SQL>
ET中执行时间占比（PERCENT）高的操作符，就是接下来我们需要重点进行分析优化的点。
四、SQL优化步骤
合理的建立索引，统计信息收集，可以解决大部分的SQL优化问题。
4.1.消除全表扫描
当看到CSCN2操作符，说明进行了全表扫描，向上寻找SLCT2操作符，给SLCT2中的过滤条件增加索引可以消除全表扫描。通过消除全表扫描可以解决大部分的sql性能问题。
示例:
SQL> explain select * from test_table where name='aa5000';
1 #NSET2: [1, 250, 64]
2 #PRJT2: [1, 250, 64]; exp_num(3), is_atom(FALSE)
3 #SLCT2: [1, 250, 64]; test_table.name = 'aa5000' SLCT_PUSHDOWN(TRUE)
4 #CSCN2: [1, 10001, 64]; INDEX33557127(test_table) NEED_SLCT(TRUE); btr_scan(1)
已用时间: 0.721(毫秒). 执行号:0.
SQL> create index idx_name on test_table(name);
操作已执行
已用时间: 22.097(毫秒). 执行号:44308.
SQL> explain select * from test_table where name='aa5000';
1 #NSET2: [1, 250, 64]
2 #PRJT2: [1, 250, 64]; exp_num(3), is_atom(FALSE)
3 #BLKUP2: [1, 250, 64]; idx_name(test_table)
4 #SSEK2: [1, 250, 64]; scan_type(ASC), idx_name(test_table), scan_range['aa5000','aa5000'], is_global(0)
已用时间: 1.155(毫秒). 执行号:0.
SQL>
4.2.创建合适的索引
第一步：SSEK2代表SQL的执行是采用的索引定位。
第二步：BLKUP2回表操作，代表需要的数据索引不能覆盖，再次到数据块里取数据。
第三步：SLCT2 条件过滤，条件是age ,再次执行了一次过滤。
SQL> explain select * from test_table where name='aa503' and age=3;
1 #NSET2: [1, 6, 68]
2 #PRJT2: [1, 6, 68]; exp_num(4), is_atom(FALSE)
3 #SLCT2: [1, 6, 68]; test_table.age = 3
4 #BLKUP2: [1, 250, 68]; idx_name(test_table)
5 #SSEK2: [1, 250, 68]; scan_type(ASC), idx_name(test_table), scan_range['aa503','aa503'], is_global(0)
已用时间: 0.925(毫秒). 执行号:0.
–通过创建联合索引，减少第二次的条件过滤
SQL> create index idx_name_age on test_table(name,age);
操作已执行
已用时间: 18.729(毫秒). 执行号:44320.
SQL> explain select * from test_table where name='aa503' and age=3;
1 #NSET2: [1, 6, 68]
2 #PRJT2: [1, 6, 68]; exp_num(4), is_atom(FALSE)
3 #BLKUP2: [1, 6, 68]; idx_name_age(test_table)
4 #SSEK2: [1, 6, 68]; scan_type(ASC), idx_name_age(test_table), scan_range[('aa503',3),('aa503',3)], is_global(0)
已用时间: 1.172(毫秒). 执行号:0.
SQL>
4.3.检查类型转换
数据库类型隐式转换会导致索引不生效条件中name为char类型查询值为数值类型索引不生效
SQL> explain select * from test_table where name=12345;
1 #NSET2: [1, 500, 68]
2 #PRJT2: [1, 500, 68]; exp_num(4), is_atom(FALSE)
3 #SLCT2: [1, 500, 68]; exp_cast(test_table.name) = 12345 SLCT_PUSHDOWN(TRUE)
4 #CSCN2: [1, 10001, 68]; INDEX33557135(test_table) NEED_SLCT(TRUE); btr_scan(1)
已用时间: 1.009(毫秒). 执行号:0.
–修改条件中的数值类型为字符串类型，索引生效
SQL> explain select * from test_table where name='12345';
1 #NSET2: [1, 250, 68]
2 #PRJT2: [1, 250, 68]; exp_num(4), is_atom(FALSE)
3 #BLKUP2: [1, 250, 68]; idx_name(test_table)
4 #SSEK2: [1, 250, 68]; scan_type(ASC), idx_name(test_table), scan_range['12345','12345'], is_global(0)
已用时间: 0.990(毫秒). 执行号:0.
SQL>
4.4.分组的优化
HAGR 是最基础的分组方式, 如果基表数据非常庞大时，HAGR 的计算量非常大，满足一定条件的情况下，我们可以利用有序性走SAGR 操作符。SAGR, 不同于 HASH AGR，由于下层数据有序，同一分组的数据按照顺序取出就行，节省了大量的计算。
SQL> explain select age,count(*) from test_table group by age;
1 #NSET2: [2, 100, 4]
2 #PRJT2: [2, 100, 4]; exp_num(2), is_atom(FALSE)
3 #HAGR2: [2, 100, 4]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(test_table.age)
4 #CSCN2: [1, 10001, 4]; INDEX33557135(test_table); btr_scan(1)
已用时间: 0.982(毫秒). 执行号:0.
–对分组列增加索引，执行计划使用了#SAGR2操作符。
SQL> create index idx_age on test_table(age);
操作已执行
已用时间: 10.597(毫秒). 执行号:44325.
SQL> explain select age,count(*) from test_table group by age;
1 #NSET2: [2, 100, 4]
2 #PRJT2: [2, 100, 4]; exp_num(2), is_atom(FALSE)
3 #SAGR2: [2, 100, 4]; grp_num(1), sfun_num(1), distinct_flag[0]; slave_empty(0) keys(test_table.age)
4 #SSCN: [1, 10001, 4]; idx_age(test_table); btr_scan(1); is_global(0)
已用时间: 1.369(毫秒). 执行号:0.
SQL>
4.5.排序的优化
排序操作会消耗大量的内存，大数据量的排序会严重影响SQL的执行消耗，排序的操符为#SORT3。
SQL> explain select * from test_table order by name;
1 #NSET2: [2, 10001, 68]
2 #PRJT2: [2, 10001, 68]; exp_num(4), is_atom(FALSE)
3 #SORT3: [2, 10001, 68]; key_num(1), partition_key_num(0), is_distinct(FALSE), top_flag(0), is_adaptive(0)
4 #CSCN2: [1, 10001, 68]; INDEX33557135(test_table); btr_scan(1)
已用时间: 0.738(毫秒). 执行号:0.
–索引是有序的，下层的#SSCN已经按顺序把数据读取出来，不需要在额外进行排序。
SQL> create index idx_name on test_table(name);
操作已执行
已用时间: 17.831(毫秒). 执行号:44326.
SQL> explain select * from test_table order by name;
1 #NSET2: [1, 10001, 68]
2 #PRJT2: [1, 10001, 68]; exp_num(4), is_atom(FALSE)
3 #BLKUP2: [1, 10001, 68]; idx_name(test_table)
4 #SSCN: [1, 10001, 68]; idx_name(test_table); btr_scan(1); is_global(0)
已用时间: 1.063(毫秒). 执行号:0.
SQL>
4.6.统计信息收集
当我们发现过滤列已经创建了索引但是索引未生效，可以尝试收集统计信息。
– 收集统计信息
SQL> DBMS_STATS.GATHER_TABLE_STATS('DMTEST', 'TEST_TABLE',null,100,false, 'FOR ALL COLUMNS SIZE AUTO',16);
DMSQL 过程已成功完成
已用时间: 171.544(毫秒). 执行号:44333.
– 查看表统计信息
SQL> DBMS_STATS.TABLE_STATS_SHOW('DMTEST', 'TEST_TABLE');
行号 NUM_ROWS LEAF_BLOCKS LEAF_USED_BLOCKS
---------- -------------------- -------------------- --------------------
1 10001 32 31
已用时间: 1.738(毫秒). 执行号:44334.
– 查看列统计信息
SQL> DBMS_STATS.COLUMN_STATS_SHOW('DMTEST', 'TEST_TABLE','AGE');
行号 NUM_DISTINCT LOW_VALUE HIGH_VALUE NUM_NULLS NUM_BUCKETS SAMPLE_SIZE HISTOGRAM
---------- -------------------- --------- ---------- -------------------- ----------- -------------------- ---------
1 11 0 12 0 11 10001 FREQUENCY
已用时间: 1.884(毫秒). 执行号:44335.
– 查看索引统计信息
SQL> DBMS_STATS.INDEX_STATS_SHOW('DMTEST','IDX_AGE');
行号 BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR NUM_ROWS SAMPLE_SIZE
---------- ----------- -------------------- -------------------- ----------------- -------------------- --------------------
1 1 32 11 0 10001 10001
已用时间: 2.058(毫秒). 执行号:44336.
SQL>
4.7.清理执行计划缓存
当执行计划不生效是可以尝试清理执行计划查询相关sql 的cache_item
SQL> select cache_item,sqlstr from v$cachepln where sqlstr like 'insert%';
行号 cache_item
---------- --------------------
sqlstr
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 140494442078160
insert into SQLLOG_001(SQLRECORDTIME, SESSSTR, THRDSTR, USERSTR, TRXIDSTR, STMTSTR, APPNAMESTR, IPSTR, EXECSQLID, EXECSQLEXECTIME, EXECSQLEXECTIMERANGE,EXECSQLTYPE, SQLROWCOUNT, PARAMSSTATUS, EXECSQLSTR, PARAMSSTR, PARAMSID) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
已用时间: 1.280(毫秒). 执行号:44330.
清理改sql的执行计划缓存
SQL> call sp_clear_plan_cache(140494442078160);
DMSQL 过程已成功完成
已用时间: 0.734(毫秒). 执行号:44331.
SQL> select cache_item,sqlstr from v$cachepln where sqlstr like 'insert%';
未选定行
已用时间: 0.619(毫秒). 执行号:44332.
SQL>
五、Hint常用优化手段
5.1.层次查询优化
执行计划中包含#HIERARCHICAL QUERY操作符，涉及到层级查询时可以通过CNNTB_OPT_FLAG进行优化。CNNTB_OPT_FLAG是否使用优化的层次查询执行机制，默认0。
1：强制使用；
2：优化器自动决定是否使用；
4：层次查询不支持并行；
8：检查层次查询的表达式是否包含 ROWNUM，若包含则报错；
16：层次查询的条件包含 OR 时仍尝试使用优化，且将OR 连接的条件表达式当做整体处理；
32：根据PRIOR关联列的DISTINCT值与统计信息中总行数的比例自适应选择执行机制；
64：对于简单条件（如：DUAL.LEVEL<=10）准确估算层次查询行数。
SQL> explain select level as 层级,id,pid,name
2 from test_t1
3 where level <'2'
4 start with pid = '0'
5 connect by PRIOR id = pid
6 order by name ;
1 #NSET2: [1383, 12505, 56]
2 #PRJT2: [1383, 12505, 56]; exp_num(4), is_atom(FALSE)
3 #SORT3: [1383, 12505, 56]; key_num(1), partition_key_num(0), is_distinct(FALSE), top_flag(0), is_adaptive(0)
4 #SLCT2: [1382, 12505, 56]; DMTEMPVIEW_889195871.TMPCOL1 < var3
5 #PRJT2: [1374, 250100, 56]; exp_num(4), is_atom(FALSE)
6 #HIERARCHICAL QUERY: [1374, 250100, 56]; KEY_NUM(0);
7 #SLCT2: [2, 500, 56]; test_t1.pid = var4 SLCT_PUSHDOWN(TRUE)
8 #CSCN2: [2, 20004, 56]; INDEX33557141(test_t1) NEED_SLCT(TRUE); btr_scan(1)
9 #SLCT2: [2, 500, 56]; test_t1.pid = var1 SLCT_PUSHDOWN(TRUE)
10 #CSCN2: [2, 20004, 56]; INDEX33557141(test_t1) NEED_SLCT(TRUE); btr_scan(1)
已用时间: 0.787(毫秒). 执行号:0.
SQL> explain select /*+CNNTB_OPT_FLAG(1)*/ level as 层级,id,pid,name
2 from test_t1
3 where level <'2'
4 start with pid = '0'
5 connect by PRIOR id = pid
6 order by name ;
1 #NSET2: [371528, 500200, 56]
2 #PRJT2: [371528, 500200, 56]; exp_num(4), is_atom(FALSE)
3 #SORT3: [371528, 500200, 56]; key_num(1), partition_key_num(0), is_distinct(FALSE), top_flag(0), is_adaptive(0)
4 #SLCT2: [371493, 500200, 56]; DMTEMPVIEW_889195877.TMPCOL1 < var3
5 #PRJT2: [371159, 10004000, 56]; exp_num(4), is_atom(FALSE)
6 #HIERARCHICAL QUERY: [371159, 10004000, 56]; KEY_NUM(1);
7 #SLCT2: [2, 500, 56]; test_t1.pid = var4 SLCT_PUSHDOWN(TRUE)
8 #CSCN2: [2, 20004, 56]; INDEX33557141(test_t1) NEED_SLCT(TRUE); btr_scan(1)
9 #CSCN2: [2, 20004, 56]; INDEX33557141(test_t1); btr_scan(1)
已用时间: 1.294(毫秒). 执行号:0.
SQL>
5.2.OR语法优化
当遇到#UNION_FOR_OR操作符时可以尝试采用OPTIMIZER_OR_NBEXP进行优化。
OPTIMIZER_OR_NBEXP OR 表达式的优化方式。默认 0： 不优化；
1： 生成 UNION_FOR_OR 操作符时，优化为无 KEY 比较方式；
2： OR 表达式优先考虑整体处理方式；
4：相关子查询的 OR 表达也优考虑整体处理方式；
8： OR 布尔表达式的范围合并优化；
16：同一列上同时存在常量范围过滤和 IS NULL 过滤时的优化，如 C1 > 5 OR C1 IS NULL。
支持使用上述有效值的组合值，如 7 表示同时进行 1、 2、 4 的优化
SQL> explain select * from test_table where name='aa500' or id='600';
1 #NSET2: [1, 2, 68]
2 #PRJT2: [1, 2, 68]; exp_num(4), is_atom(FALSE)
3 #UNION FOR OR2: [1, 2, 68]; key_num(0), outer_join(-)
4 #BLKUP2: [1, 1, 68]; idx_name(test_table)
5 #SSEK2: [1, 1, 68]; scan_type(ASC), idx_name(test_table), scan_range['aa500','aa500'], is_global(0)
6 #SLCT2: [1, 1, 68]; exp11
7 #BLKUP2: [1, 1, 68]; INDEX33557136(test_table)
8 #SSEK2: [1, 1, 68]; scan_type(ASC), INDEX33557136(test_table), scan_range[exp_cast('600'),exp_cast('600')], is_global(0)
已用时间: 1.410(毫秒). 执行号:0.
SQL> explain select /*+OPTIMIZER_OR_NBEXP(7)*/ * from test_table where name='aa500' or id='600';
1 #NSET2: [1, 251, 68]
2 #PRJT2: [1, 251, 68]; exp_num(4), is_atom(FALSE)
3 #SLCT2: [1, 251, 68]; (test_table.name = 'aa500' OR test_table.id = var1)
4 #CSCN2: [1, 10001, 68]; INDEX33557135(test_table); btr_scan(1)
已用时间: 0.896(毫秒). 执行号:0.
SQL>
评论
后发表回复
作者
文章
阅读量
获赞
扫一扫
联系客服
联系客服
鄂Copyright © 达梦国产化应用创新实验室
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1
达梦在线服务平台社区用户管理规范
鄂ICP备18017926号-1