Cong LiIn 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
Sqltrace OFF
> 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.
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 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");
-set sql tracing off: Disable Global SQL Tracing
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
execute function task("set sql tracing database add", "mydb");
Example: Cancels tracing for a specific database
execute function task("set sql tracing database remove","mydb");
- set sql tracing database list: Queries the list of traced databases
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.
execute function task("set sql tracing database clear");
Output:
execute function task("set sql tracing database list");
Go Back:
(expression) SQLTrace is tracing all databases
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;
Check the sql.unl file generated in the current path.
Query Trace Information and Sort as Needed
select * from syssqltrace;
Command to View Trace Information
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.
