Diagnosing and Optimizing Running Slow SQL in GBase 8s Database
Tags: database, sql
Published: 2024-06-21 08:05:32

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