DEV Community

GBASE Database
GBASE Database

Posted on

GBase 8s Performance Optimization Tool `sqltrace` Guide

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

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

Sqltrace OFF

> execute function task ("set sql tracing info");
(expression)  SQL Tracing OFF.
Enter fullscreen mode Exit fullscreen mode
  • set sql tracing on: Starts global SQL tracing, optionally specifying the tracing level and mode, or changing the size of the trace buffer. 
execute function task("set sql tracing on", "number_traces", "trace_size", "level", "mode");
Enter fullscreen mode Exit fullscreen 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
low: Captures statement statistics, statement text, and statement iterators
med: Captures low level information plus table names, database names, and stored procedure stacks
high: Captures med level information plus host variables
mode Trace all users or selected users, supports global, user
global: Enables tracing for all users
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

execute function task("set sql tracing on", "1500", "4", "high", "global");
Enter fullscreen mode Exit fullscreen mode

-set sql tracing off: Disable Global SQL Tracing

execute function task("set sql tracing off");
Enter fullscreen mode Exit fullscreen mode

(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

execute function task("set sql tracing database add", "mydb");
Enter fullscreen mode Exit fullscreen mode

Example: Cancels tracing for a specific database

execute function task("set sql tracing database remove","mydb");
Enter fullscreen mode Exit fullscreen mode
  • set sql tracing database list: Queries the list of traced databases 
execute function task("set sql tracing database list");
Enter fullscreen mode Exit fullscreen mode
  • set sql tracing database clear: Clears the list of traced databases, returning to the state of tracing all databases. 
execute function task("set sql tracing database clear");
Enter fullscreen mode Exit fullscreen mode

Output:

execute function task("set sql tracing database list");
Enter fullscreen mode Exit fullscreen mode

Go Back:

(expression) SQLTrace is tracing all databases
Enter fullscreen mode Exit fullscreen mode

3. Querying Trace Results

Execute queries in the sysmaster database.

View Long-Running SQL Executions:

unload to sql.unl;
select sql_maxtime, sql_avgtime, sql_statement from syssqltrace order by sql_maxtime desc;
Enter fullscreen mode Exit fullscreen mode

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

Query Trace Information and Sort as Needed

select * from syssqltrace;
Enter fullscreen mode Exit fullscreen mode

Command to View Trace Information

onstat -g his
Enter fullscreen mode Exit fullscreen mode

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.

Top comments (0)