Use dbms_sql.delete_trace to delete SQL statements from SQL Trace - PolarDB

dbms_sql.delete_trace removes a SQL statement from SQL Trace tracking when loose_sql_trace_type is set to DEMAND.

Syntax

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

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

When you call dbms_sql.delete_trace, it performs the following actions:

Converts the literal constants in the SQL statement into wildcards (?), producing a SQL template.
Deletes all rows in mysql.sql_sharing that match the template.
Stops SQL Trace from recording any future SQL statements that match the template.

Primary node vs. read-only nodes

The stored procedure behaves differently depending on which node it runs on:

Primary node: The deletion is permanent. Matching rows are removed from mysql.sql_sharing on the primary node and the change is synchronized to all read-only nodes.
Read-only nodes: The deletion applies only to the node where it runs and is not persisted. When you access a database through a cluster endpoint, templated SQL statements are automatically routed to the primary node.

Remove a SQL statement from SQL Trace tracking:

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

If the call succeeds, no output is returned.

The stored procedure creates the following template from the statement and removes all matching rows from mysql.sql_sharing:

SELECT * FROM `t` WHERE `c1` > ? AND `c1` < ?