Use dbms_sql.add_trace to track specified SQL statements - PolarDB

dbms_sql.add_trace is a stored procedure that registers specific SQL statements for on-demand tracking by the SQL Trace feature.

Prerequisites

Set the loose_sql_trace_type parameter to DEMAND before calling this procedure.

Syntax

dbms_sql.add_trace('<schema>', '<query>')

After the procedure runs, PolarDB normalizes the constants in the SQL statement into a template. Statements that match this template are tracked automatically.

Parameters

Parameter	Description
`schema`	The name of the schema.
`query`	The SQL statement that is being executed.

Usage notes

The procedure behaves differently depending on which node it runs on.

Primary node: The trace registration is persisted. Related records in the mysql.sql_sharing table are deleted and synchronized to all read-only nodes.

Read-only nodes: The trace registration is not persisted. The stored procedure can be executed only on those nodes. When you access the database through a cluster endpoint, templated SQL statements are automatically routed to the primary node.

Example

This example traces a range query, then queries both system tables to inspect the template and execution statistics.

Step 1: Register the SQL statement for tracing

call dbms_sql.add_trace('test', 'select * from t where c1 > 1 and c1 < 10');

Step 2: Verify the trace template in mysql.sql_sharing

Query the mysql.sql_sharing table to confirm that the statement was registered and templated:

select * from mysql.sql_sharing\G

Expected output:

*************************** 1. row ***************************
         Id: (id number)
     Sql_id: 82t4dswtqjg02
Schema_name: test
       Type: SQL_TRACE
Digest_text: SELECT * FROM `t` WHERE `c1` > ? AND `c1` < ?
    Plan_id: NULL
       Plan: NULL
    Version: 0
Create_time: 2022-11-07 19:05:27.980605
Update_time: 2022-11-07 19:05:27.980605
 Extra_info: NULL

The Digest_text field shows the normalized template: literal values 1 and 10 are replaced with ?. Any statement that matches this pattern is now tracked, regardless of the specific constant values used.

Step 3: Run statements that match the template

select * from  t where c1 > 1 and c1 < 10;
select * from  t where c1 > 1 and c1 < 100;

Step 4: Inspect trace data and execution plan statistics

Query information_schema.sql_sharing to see execution statistics and the execution plans that were used:

select * from information_schema.sql_sharing\G

Expected output:

*************************** 1. row ***************************
              TYPE: SQL
            SQL_ID: 82t4dswtqjg02
       SCHEMA_NAME: test
       DIGEST_TEXT: SELECT * FROM `t` WHERE `c1` > ? AND `c1` < ?
           PLAN_ID: NULL
              PLAN: NULL
        PLAN_EXTRA: NULL
        ERROR_CODE: NULL
            REF_BY: SQL_TRACE(DEMAND)
   FIRST_LOAD_TIME: 2022-11-07 19:05:28
     LAST_HIT_TIME: 2022-11-07 19:17:24
        EXECUTIONS: 2
     SUM_WAIT_TIME: 363
     MIN_WAIT_TIME: 179
     MAX_WAIT_TIME: 184
     SUM_EXEC_TIME: 925
     MIN_EXEC_TIME: 438
     MAX_EXEC_TIME: 487
     SUM_ROWS_SENT: 106
     MIN_ROWS_SENT: 8
     MAX_ROWS_SENT: 98
 SUM_ROWS_EXAMINED: 108
 MIN_ROWS_EXAMINED: 8
 MAX_ROWS_EXAMINED: 100
 SUM_ROWS_AFFECTED: 0
 MIN_ROWS_AFFECTED: 0
 MAX_ROWS_AFFECTED: 0
  SUM_LOGICAL_READ: 122
  MIN_LOGICAL_READ: 19
  MAX_LOGICAL_READ: 103
 SUM_PHY_SYNC_READ: 0
 MIN_PHY_SYNC_READ: 0
 MAX_PHY_SYNC_READ: 0
SUM_PHY_ASYNC_READ: 0
MIN_PHY_ASYNC_READ: 0
MAX_PHY_ASYNC_READ: 0
             EXTRA: {TRACE_ROW_ID:10}
*************************** 2. row ***************************
              TYPE: PLAN
            SQL_ID: 82t4dswtqjg02
       SCHEMA_NAME: test
       DIGEST_TEXT: NULL
           PLAN_ID: 5a4cvp4gjqgfj
              PLAN: /*+ NO_INDEX(`t`@`select#1`) */
        PLAN_EXTRA: {`t`@`select#1`:ALL}
        ERROR_CODE: 0
            REF_BY: SQL_TRACE(DEMAND)
   FIRST_LOAD_TIME: 2022-11-07 19:17:24
     LAST_HIT_TIME: 2022-11-07 19:17:24
        EXECUTIONS: 1
     SUM_WAIT_TIME: 184
     MIN_WAIT_TIME: 184
     MAX_WAIT_TIME: 184
     SUM_EXEC_TIME: 487
     MIN_EXEC_TIME: 487
     MAX_EXEC_TIME: 487
     SUM_ROWS_SENT: 98
     MIN_ROWS_SENT: 98
     MAX_ROWS_SENT: 98
 SUM_ROWS_EXAMINED: 100
 MIN_ROWS_EXAMINED: 100
 MAX_ROWS_EXAMINED: 100
 SUM_ROWS_AFFECTED: 0
 MIN_ROWS_AFFECTED: 0
 MAX_ROWS_AFFECTED: 0
  SUM_LOGICAL_READ: 103
  MIN_LOGICAL_READ: 103
  MAX_LOGICAL_READ: 103
 SUM_PHY_SYNC_READ: 0
 MIN_PHY_SYNC_READ: 0
 MAX_PHY_SYNC_READ: 0
SUM_PHY_ASYNC_READ: 0
MIN_PHY_ASYNC_READ: 0
MAX_PHY_ASYNC_READ: 0
             EXTRA: NULL
*************************** 3. row ***************************
              TYPE: PLAN
            SQL_ID: 82t4dswtqjg02
       SCHEMA_NAME: test
       DIGEST_TEXT: NULL
           PLAN_ID: 463zszw4mbv3w
              PLAN: /*+ INDEX(`t`@`select#1` `i_c1`) */
        PLAN_EXTRA: {`t`@`select#1`:range}
        ERROR_CODE: 0
            REF_BY: SQL_TRACE(DEMAND)
   FIRST_LOAD_TIME: 2022-11-07 19:17:21
     LAST_HIT_TIME: 2022-11-07 19:17:21
        EXECUTIONS: 1
     SUM_WAIT_TIME: 179
     MIN_WAIT_TIME: 179
     MAX_WAIT_TIME: 179
     SUM_EXEC_TIME: 438
     MIN_EXEC_TIME: 438
     MAX_EXEC_TIME: 438
     SUM_ROWS_SENT: 8
     MIN_ROWS_SENT: 8
     MAX_ROWS_SENT: 8
 SUM_ROWS_EXAMINED: 8
 MIN_ROWS_EXAMINED: 8
 MAX_ROWS_EXAMINED: 8
 SUM_ROWS_AFFECTED: 0
 MIN_ROWS_AFFECTED: 0
 MAX_ROWS_AFFECTED: 0
  SUM_LOGICAL_READ: 19
  MIN_LOGICAL_READ: 19
  MAX_LOGICAL_READ: 19
 SUM_PHY_SYNC_READ: 0
 MIN_PHY_SYNC_READ: 0
 MAX_PHY_SYNC_READ: 0
SUM_PHY_ASYNC_READ: 0
MIN_PHY_ASYNC_READ: 0
MAX_PHY_ASYNC_READ: 0
             EXTRA: NULL

The output contains one SQL row and two PLAN rows, all sharing the same SQL_ID. This means the two traced executions used different execution plans:

Row 2 (PLAN): A full table scan (ALL), identified by plan hint NO_INDEX. This plan examined 100 rows and took 487 microseconds.
Row 3 (PLAN): An index range scan using the i_c1 index (range). This plan examined only 8 rows and took 438 microseconds.

The SQL statements that match the template correspond to the following execution plans: i_c1 index range scans and full table scans.