GBase 8s Performance Optimization Tool `sqltrace` Guide
Published: 2024-06-28 04:07:09

In database management, performance monitoring and optimization are critical to ensuring efficient system operation. The `sqltrace` tool in the GBase 8s database, as a reliable assistant for performance monitoring, helps us understand database activities, quickly locate and resolve performance bottlenecks.

## 1. Overview of the sqltrace Tool

`sqltrace` is a built-in tracing tool in the GBase 8s database, specifically designed to capture and analyze SQL execution activities within the database. With `sqltrace`, we can monitor the execution of SQL statements, assess performance, and make optimizations accordingly.

## 2. Usage of the sqltrace Tool

The following commands are executed in the `sysadmin` database:

**(1) Global Tracing (set sql tracing):** Tracks operations for any user on any database, reset upon database restart.

- set sql tracing info: Displays the status of global SQL tracing, indicating whether sqltrace is enabled and showing configuration parameters.

**`Sqltrace` ON**

```sql
> execute function task ("set sql tracing info");
(expression)  SQL Tracing ON: ntraces=15000, size=4056, level=High, mode=Global
```

**`Sqltrace` OFF**

```sql
> execute function task ("set sql tracing info");
(expression)  SQL Tracing OFF.
```

- set sql tracing on: Starts global SQL tracing, optionally specifying the tracing level and mode, or changing the size of the trace buffer.

```sql
execute function task("set sql tracing on", "number_traces", "trace_size", "level", "mode");
```

**Parameter Descriptions:**

| Parameter       | Descriptions                                                                                               |
|-----------------|------------------------------------------------------------------------------------------------------------|
| `number_traces` | Number of SQL statements to trace, default is 1000                                                         |
| `trace_size`    | Size of the trace buffer in KB. If the buffer is exceeded, the database server discards the saved data. The default size is 2KB |
| `level`         | Tracing level, supports `low`, `med`, `high` <br> **low**: Captures statement statistics, statement text, and statement iterators <br> **med**: Captures `low` level information plus table names, database names, and stored procedure stacks <br> **high**: Captures `med` level information plus host variables |
| `mode`          | Trace all users or selected users, supports `global`, `user` <br> **global**: Enables tracing for all users <br> **user**: Enables tracing for users specified by the `set sql tracing user` parameter |


Example: Enable high-level global tracing for 1500 SQL statements with a trace buffer of 4KB

```sql
execute function task("set sql tracing on", "1500", "4", "high", "global");
```

-set sql tracing off: Disable Global SQL Tracing

```sql
execute function task("set sql tracing off");
```

**(2) Database-Level Tracing (set sql tracing database):** Tracks operations only on a specified database.

- set sql tracing database add: Specifies tracing for a particular database, capturing successful SQL executions within that database, including cross-database queries. Only one database can be specified at a time, with a maximum of 16 traceable databases.

Example: Enable tracing for the `mydb` database

```sql
execute function task("set sql tracing database add", "mydb");
```

Example: Cancels tracing for a specific database

```sql
execute function task("set sql tracing database remove","mydb");
```

- set sql tracing database list: Queries the list of traced databases

```sql
execute function task("set sql tracing database list");
```

- set sql tracing database clear: Clears the list of traced databases, returning to the state of tracing all databases.

```sql
execute function task("set sql tracing database clear");
```

**Output:**
```sql
execute function task("set sql tracing database list");
```

**Go Back:**
```sql
(expression) SQLTrace is tracing all databases
```

## 3. Querying Trace Results

Execute queries in the `sysmaster` database.

View Long-Running SQL Executions:

```sql
unload to sql.unl;
select sql_maxtime, sql_avgtime, sql_statement from syssqltrace order by sql_maxtime desc;
```

Check the `sql.unl` file generated in the current path.

**Query Trace Information and Sort as Needed**

```sql
select * from syssqltrace;
```

**Command to View Trace Information**

```sh
onstat -g his
```

In summary, the `sqltrace` tool is a powerful asset for database administrators to monitor and optimize performance. By configuring and using `sqltrace` appropriately, we can enhance database performance, ensuring business continuity and stability.