當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範圍掃描和全表掃描。