Use dbms_sql.reload_trace to load templated SQL statements - PolarDB

dbms_sql.reload_trace() loads templated SQL statements from the mysql.sql_sharing table into the information_schema.sql_sharing table.

Syntax

dbms_sql.reload_trace()

Parameters

None.

Example

The following example shows how to load templated SQL statements and then query the results.

Step 1. Load the templated SQL statements:

call dbms_sql.reload_trace();

Step 2. Query the loaded statements from information_schema.sql_sharing:

select * from information_schema.sql_sharing\G

The \G flag formats each row vertically, which makes wide result sets easier to read.

The following output is returned:

*************************** 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: NULL
        EXECUTIONS: 0
     SUM_WAIT_TIME: 0
     MIN_WAIT_TIME: 0
     MAX_WAIT_TIME: 0
     SUM_EXEC_TIME: 0
     MIN_EXEC_TIME: 0
     MAX_EXEC_TIME: 0
     SUM_ROWS_SENT: 0
     MIN_ROWS_SENT: 0
     MAX_ROWS_SENT: 0
 SUM_ROWS_EXAMINED: 0
 MIN_ROWS_EXAMINED: 0
 MAX_ROWS_EXAMINED: 0
 SUM_ROWS_AFFECTED: 0
 MIN_ROWS_AFFECTED: 0
 MAX_ROWS_AFFECTED: 0
  SUM_LOGICAL_READ: 0
  MIN_LOGICAL_READ: 0
  MAX_LOGICAL_READ: 0
 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}

Output fields

Field	Description
`TYPE`	The type of the shared SQL entry.
`SQL_ID`	The unique identifier of the SQL template.
`SCHEMA_NAME`	The database schema where the SQL was executed.
`DIGEST_TEXT`	The normalized SQL template, with literal values replaced by `?`.
`PLAN_ID`	The ID of the cached execution plan. `NULL` if no plan is cached.
`PLAN`	The cached execution plan. `NULL` if no plan is cached.
`PLAN_EXTRA`	Additional execution plan metadata. `NULL` if not available.
`ERROR_CODE`	The error code from the last execution. `NULL` if no error occurred.
`REF_BY`	The source that added this entry. `SQL_TRACE(DEMAND)` means the entry was added by on-demand SQL Trace.
`FIRST_LOAD_TIME`	The timestamp when this entry was first loaded.
`LAST_HIT_TIME`	The timestamp of the last cache hit. `NULL` if the template has not been matched since loading.
`EXECUTIONS`	The total number of executions matched to this template since loading.
`SUM_WAIT_TIME` / `MIN_WAIT_TIME` / `MAX_WAIT_TIME`	Total, minimum, and maximum wait time across all matched executions.
`SUM_EXEC_TIME` / `MIN_EXEC_TIME` / `MAX_EXEC_TIME`	Total, minimum, and maximum execution time across all matched executions.
`SUM_ROWS_SENT` / `MIN_ROWS_SENT` / `MAX_ROWS_SENT`	Total, minimum, and maximum rows sent to the client.
`SUM_ROWS_EXAMINED` / `MIN_ROWS_EXAMINED` / `MAX_ROWS_EXAMINED`	Total, minimum, and maximum rows examined.
`SUM_ROWS_AFFECTED` / `MIN_ROWS_AFFECTED` / `MAX_ROWS_AFFECTED`	Total, minimum, and maximum rows affected.
`SUM_LOGICAL_READ` / `MIN_LOGICAL_READ` / `MAX_LOGICAL_READ`	Total, minimum, and maximum logical reads.
`SUM_PHY_SYNC_READ` / `MIN_PHY_SYNC_READ` / `MAX_PHY_SYNC_READ`	Total, minimum, and maximum synchronous physical reads.
`SUM_PHY_ASYNC_READ` / `MIN_PHY_ASYNC_READ` / `MAX_PHY_ASYNC_READ`	Total, minimum, and maximum asynchronous physical reads.
`EXTRA`	Additional metadata for the entry. `TRACE_ROW_ID` identifies the corresponding row in the SQL Trace data.