Cong LiDetecting and optimizing slow SQL queries is a critical step in enhancing database performance during routine maintenance. Slow SQL queries not only impact user experience but can also become a source of system performance bottlenecks. This article will discuss how to check for running slow SQL queries and provide corresponding optimization strategies.
1. Using SQL Queries to Detect Slow SQL
1.1. Basic SQL Querying: By running specific SQL queries, you can quickly identify high-cost, potentially slow-executing SQL statements. This provides a basis for further analysis and optimization.
dbaccess sysmaster -<<!
select first 10 sqx_estcost,
sqx_estrows, sqx_sessionid,
sqx_sqlstatement
from sysmaster:syssqexplain
where 1=1
order by sqx_estcost desc;
!
1.2. Analyzing Query Output: Analyze the query results to identify SQL statements with the highest estimated cost and row count. These statements are often the primary targets for optimization efforts.
sqx_estcost 2147483647
sqx_estrows 49
sqx_sessionid 51
sqx_sqlstatement select max(t3.tabid) as id from systables t1,systables t2,sys
tables t3, systables t4,systables t5,systables t6
group by t
1.tabname,t2.tabname
1.3. Interpreting Output: Understand the meaning of each field in the output, such as estimated cost (sqx_estcost), estimated rows (sqx_estrows), session ID (sqx_sessionid), and the SQL statement itself (sqx_sqlstatement).
2. Using Commands to Check Running Slow SQL
2.1. Check Continuously Running Threads (rstcb): Identify threads with a constant third column in the output, indicating ongoing execution.
onstat -g act -r 1 | egrep "sqlexec|threads"
Output:
Running threads:
215 4a645178 470f33e8 1 running 8cpu sqlexec
Running threads:
215 4a645178 470f33e8 1 running 8cpu sqlexec
2.2. View Thread Sessions: Based on the previous output, inspect thread information.
onstat -u |grep 470f33e8
Output:
470f33e8 ---P--- 51 gbasedbt - 0 0 1 5 0
2.3. Check Session Information: From the previous step, examine session information and the executing SQL.
onstat -g ses 51
Output:
On-Line -- Up 14 days 19:53:19 -- 674664 Kbytes
session effective #RSAM total used dynamic
id user user tty pid hostname threads memory memory explain
51 gbasedbt - - 1486 dbhost1 1 221184 218648 off
Program :
/opt/gbase/bin/dbaccess
tid name rstcb flags curstk status
215 sqlexec 470f33e8 ---P--- 10528 running-
Memory pools count 2
name class addr totalsize freesize #allocfrag #freefrag
51 V 4a745040 217088 1728 453 6
51*O0 V 4a788040 4096 808 1 1
name free used name free used
overhead 0 6576 scb 0 144
opentable 0 9192 filetable 0 904
log 0 16536 temprec 0 22688
keys 0 176 ralloc 0 80024
gentcb 0 1616 ostcb 0 2968
sqscb 0 21064 sql 0 18952
hashfiletab 0 552 osenv 0 2768
sqtcb 0 9688 fragman 0 1240
shmblklist 0 22568 rsam_seqscan 0 992
sqscb info
scb sqscb optofc pdqpriority optcompind directives
47b61290 4a735028 0 0 2 1
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
51 SELECT testdb LC Not Wait 0 0 9.24 Off
Current statement name : unlcur
Current SQL statement (2) :
select max(t3.tabid) as id from systables t1,systables t2,systables t3,
systables t4,systables t5,systables t6 group by t1.tabname,t2.tabname
Last parsed SQL statement :
select max(t3.tabid) as id from systables t1,systables t2,systables t3,
systables t4,systables t5,systables t6 group by t1.tabname,t2.tabname
By checking and analyzing running slow SQL queries, we can more accurately identify performance bottlenecks and take appropriate optimization measures. Whether using SQL queries or system commands, the key is to understand the output results and develop optimization strategies accordingly.
