当loose_sql_trace_type参数设置为DEMAND时，您可以使用dbms_sql.add_trace存储过程来指定SQL Trace功能跟踪的SQL语句。
语法
dbms_sql.add_trace('<schema>', '<query>')说明
执行该存储过程后，SQL语句中的常量会被自动模板化，匹配该模板的SQL语句会被SQL Trace功能跟踪。
参数说明
参数 | 说明 |
schema | Schema名称。 |
query | 具体的SQL语句。 |
注意事项
当在主节点执行该存储过程时，该操作会被持久化，mysql.sql_sharing表中对应的记录会被删除，并同步至只读节点。
当在只读节点执行该存储过程时，该操作不会被持久化，且仅在该只读节点生效。通过集群地址访问数据库时，模板化后的SQL语句会自动路由至主节点。
示例
指定SQL Trace功能跟踪的SQL语句。示例如下：
call dbms_sql.add_trace('test', 'select * from t where c1 > 1 and c1 < 10');您可以在mysql.sql_sharing系统表中查询记录。
select * from mysql.sql_sharing\G执行结果如下：
*************************** 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从以上结果可以看出，SQL语句中的常量已经过模板化处理。
执行与模板匹配的SQL语句，如：
select * from t where c1 > 1 and c1 < 10;
select * from t where c1 > 1 and c1 < 100;您可以在information_schema.sql_sharing系统表中查看SQL语句的跟踪信息和执行计划统计信息。
select * from information_schema.sql_sharing\G执行结果如下：
*************************** 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结论
从以上结果可以看出，两条SQL语句对应2个执行计划，分别为索引i_c1范围扫描和全表扫描。