Use SQL Trace to track SQL execution plans and diagnose performance - PolarDB

SQL Trace records execution plans and runtime statistics for SQL statements in your PolarDB for MySQL cluster. Use it to detect performance regressions caused by execution plan changes and identify the top SQL statements consuming the most resources.

Prerequisites

Before you begin, ensure that your cluster runs one of the following versions:

PolarDB for MySQL 8.0.1, revision version 8.0.1.1.30 or later
PolarDB for MySQL 8.0.2, revision version 8.0.2.2.12 or later

To check your cluster version, see Query the engine version.

Limitations

SQL Trace does not track account-related statements: CREATE USER, DROP USER, and GRANT.

Parameters

Parameter	Description
`loose_sql_trace_type`	The tracking mode. Valid values: OFF (default), DEMAND, ALL, SLOW_QUERY. SLOW_QUERY requires revision version 8.0.1.1.34 or later on PolarDB for MySQL 8.0.1.
`loose_sql_sharing_size`	The maximum memory allocated to SQL Sharing, the underlying component of SQL Trace. Valid values: 8388608–1073741824 bytes. Default: 134217728 bytes.
`loose_sql_trace_plan_expire_time`	How long a traced execution plan is retained without being reused. If no subsequent statement runs the same plan within this window, the plan is evicted. Valid values: 0–18446744073709551615 seconds. Default: 604800 seconds (7 days).

Choose a tracking mode

Select a mode based on what you want to monitor:

Mode	What it tracks	When to use
OFF	Nothing (default)	SQL Trace is inactive
DEMAND	Specific SQL statements you add	Target known slow or critical queries
ALL	Every SQL statement	Investigate unknown performance issues across the whole cluster
SLOW_QUERY	Queries exceeding the slow query threshold	Focus on slow queries only

Based on Sysbench tests (oltp_read_only and oltp_read_write) with 2,000 tables of 10,000 rows each, on clusters of 4 cores/8 GB and 8 cores/32 GB, ALL mode degrades database performance by less than 3%. SQL Trace uses lock-free designs to minimize impact under high concurrency.

Track SQL statements

Track all SQL statements

Set loose_sql_trace_type to ALL.

Track specific SQL statements

Set loose_sql_trace_type to DEMAND.
Add the statements to track using dbms_sql.add_trace.

Query execution statistics

All traced statements are stored in the information_schema.sql_sharing table. Query it to view the execution plan and statistics for any traced statement.

Get the execution plan for a specific statement

SELECT * FROM information_schema.sql_sharing WHERE sql_id = polar_sql_id('select * from t');

Find top SQL statements

Use these queries to identify the statements that need the most attention. Start with total execution time to find the highest overall cost, then use the other queries to narrow down root causes.

Top 10 by total execution time — identifies statements with the highest cumulative cost across all executions:

SELECT * FROM information_schema.sql_sharing
WHERE TYPE = 'sql'
ORDER BY SUM_EXEC_TIME DESC
LIMIT 10;

Top 10 by average execution time — identifies statements that are slow on a per-call basis, even if called infrequently:

SELECT * FROM information_schema.sql_sharing
WHERE TYPE = 'sql'
ORDER BY SUM_EXEC_TIME / EXECUTIONS DESC
LIMIT 10;

Top 10 by total scanned rows — identifies statements causing the most I/O, which often indicates missing indexes or inefficient query plans:

SELECT * FROM information_schema.sql_sharing
WHERE TYPE = 'sql'
ORDER BY SUM_ROWS_EXAMINED DESC
LIMIT 10;

Manage tracked statements and statistics

Remove tracked statements

To stop tracking specific statements added in DEMAND mode:

Remove by statement text — use dbms_sql.delete_trace.
Remove by SQL ID — use dbms_sql.delete_trace_by_sqlid.

Reset statistics

To reset all statistics in information_schema.sql_sharing, use dbms_sql.reset_trace_stats.

Clear and reload

To clear all data from information_schema.sql_sharing, use dbms_sql.flush_trace.

To reload SQL templates from mysql.sql_sharing so statistics collection resumes for previously added statements, use dbms_sql.reload_trace.