Sql trace table schemas of mysql.sql_sharing and information_schema.sql_sharing - PolarDB

SQL Trace uses two tables to store and expose tracking data: mysql.sql_sharing persists the SQL statements registered for tracking, and information_schema.sql_sharing holds real-time execution statistics for those statements and their execution plans. Both tables are created automatically at startup.

mysql.sql_sharing

mysql.sql_sharing is an InnoDB system table in the mysql database. It stores the templated SQL statements registered for tracking.

CREATE TABLE `sql_sharing` (
  `Id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Sql_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Schema_name` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Type` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Digest_text` longtext COLLATE utf8_bin,
  `Plan_id` varchar(64) COLLATE utf8_bin DEFAULT NULL,
  `Plan` text COLLATE utf8_bin,
  `Version` int(11) unsigned DEFAULT NULL,
  `Create_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `Update_time` timestamp(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
  `Extra_info` longtext COLLATE utf8_bin,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `sqlid_schema_type` (`Sql_id`,`Schema_name`,`Type`)
) /*!50100 TABLESPACE `mysql` */ ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

Field	Description
`Id`	Auto-increment row ID.
`Sql_id`	ID of the templated SQL statement.
`Schema_name`	Schema on which the SQL statements are executed.
`Type`	Feature that uses the record.
`Digest_text`	Content of the templated SQL statement.
`Plan_id`	ID of the execution plan.
`Plan`	Name of the execution plan.
`Version`	Version number of the record.
`Create_time`	Time when the record was inserted.
`Update_time`	Time when the record was last updated.
`Extra_info`	Additional information recorded by the system.

information_schema.sql_sharing

information_schema.sql_sharing is a MEMORY table that records real-time execution statistics for tracked SQL statements and their execution plans. Each row represents either a tracked SQL statement (TYPE = SQL) or one of its execution plans (TYPE = PLAN).

CREATE TEMPORARY TABLE `SQL_SHARING` (
  `TYPE` varchar(16) DEFAULT NULL,
  `SQL_ID` varchar(64) DEFAULT NULL,
  `SCHEMA_NAME` varchar(64) DEFAULT NULL,
  `DIGEST_TEXT` varchar(2048) DEFAULT NULL,
  `PLAN_ID` varchar(64) DEFAULT NULL,
  `PLAN` varchar(1024) DEFAULT NULL,
  `PLAN_EXTRA` varchar(1024) DEFAULT NULL,
  `ERROR_CODE` bigint(21) DEFAULT NULL,
  `REF_BY` varchar(512) DEFAULT NULL,
  `FIRST_LOAD_TIME` datetime DEFAULT NULL,
  `LAST_HIT_TIME` datetime DEFAULT NULL,
  `EXECUTIONS` bigint(21) unsigned NOT NULL DEFAULT '0',
  `SUM_WAIT_TIME` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MIN_WAIT_TIME` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MAX_WAIT_TIME` bigint(21) unsigned NOT NULL DEFAULT '0',
  `SUM_EXEC_TIME` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MIN_EXEC_TIME` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MAX_EXEC_TIME` bigint(21) unsigned NOT NULL DEFAULT '0',
  `SUM_ROWS_SENT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MIN_ROWS_SENT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MAX_ROWS_SENT` bigint(21) unsigned NOT NULL DEFAULT '0',
  `SUM_ROWS_EXAMINED` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MIN_ROWS_EXAMINED` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MAX_ROWS_EXAMINED` bigint(21) unsigned NOT NULL DEFAULT '0',
  `SUM_ROWS_AFFECTED` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MIN_ROWS_AFFECTED` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MAX_ROWS_AFFECTED` bigint(21) unsigned NOT NULL DEFAULT '0',
  `SUM_LOGICAL_READ` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MIN_LOGICAL_READ` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MAX_LOGICAL_READ` bigint(21) unsigned NOT NULL DEFAULT '0',
  `SUM_PHY_SYNC_READ` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MIN_PHY_SYNC_READ` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MAX_PHY_SYNC_READ` bigint(21) unsigned NOT NULL DEFAULT '0',
  `SUM_PHY_ASYNC_READ` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MIN_PHY_ASYNC_READ` bigint(21) unsigned NOT NULL DEFAULT '0',
  `MAX_PHY_ASYNC_READ` bigint(21) unsigned NOT NULL DEFAULT '0',
  `EXTRA` varchar(1024) DEFAULT NULL
) ENGINE=MEMORY DEFAULT CHARSET=utf8;

Field descriptions

Identification fields

Field	Description
`TYPE`	Record type. `SQL`: the templated SQL statement. `PLAN`: an execution plan generated for that statement.
`SQL_ID`	ID of the templated SQL statement.
`SCHEMA_NAME`	Schema on which the SQL statements are executed.
`DIGEST_TEXT`	Content of the templated SQL statement.
`PLAN_ID`	ID of the execution plan.
`PLAN`	Name of the execution plan.
`PLAN_EXTRA`	Additional information, such as which method is used to access the table and whether to use the prepared statement protocol. Access methods include full table scan, index range scan, and same index access.
`ERROR_CODE`	Error code.
`REF_BY`	Feature that registered this statement or plan. `SQL_TRACE`: all statements are tracked. `SQL_TRACE(DEMAND)`: only specified statements are tracked.

Timestamp fields

Field	Description
`FIRST_LOAD_TIME`	Time when the statement or plan was first recorded in memory.
`LAST_HIT_TIME`	Time when the statement or plan was last executed.

Execution statistics

Each metric is captured as three aggregates: SUM_* (cumulative total), MIN_* (minimum across all executions), and MAX_* (maximum across all executions).

Metric	Fields	Unit
Execution count	`EXECUTIONS`	—
Wait time	`SUM_WAIT_TIME`, `MIN_WAIT_TIME`, `MAX_WAIT_TIME`	Microseconds
Execution time	`SUM_EXEC_TIME`, `MIN_EXEC_TIME`, `MAX_EXEC_TIME`	Microseconds
Rows returned	`SUM_ROWS_SENT`, `MIN_ROWS_SENT`, `MAX_ROWS_SENT`	—
Rows examined	`SUM_ROWS_EXAMINED`, `MIN_ROWS_EXAMINED`, `MAX_ROWS_EXAMINED`	—
Rows affected	`SUM_ROWS_AFFECTED`, `MIN_ROWS_AFFECTED`, `MAX_ROWS_AFFECTED`	—
Logical reads	`SUM_LOGICAL_READ`, `MIN_LOGICAL_READ`, `MAX_LOGICAL_READ`	—
Physical synchronous reads	`SUM_PHY_SYNC_READ`, `MIN_PHY_SYNC_READ`, `MAX_PHY_SYNC_READ`	—
Physical asynchronous reads	`SUM_PHY_ASYNC_READ`, `MIN_PHY_ASYNC_READ`, `MAX_PHY_ASYNC_READ`	—
Additional information	`EXTRA`	—

Example

The following example shows how SQL Trace populates information_schema.sql_sharing when loose_sql_trace_type is set to ALL.

Run the setup statements:

CREATE TABLE t AS WITH RECURSIVE t(c1, c2, c3) AS (SELECT 1, 1, 1 UNION ALL SELECT c1+1, c2 + 1, c3 + 1 FROM t WHERE c1 < 100) SELECT c1, c2, c3 FROM t;
CREATE index i_c1 ON t(c1);
SELECT  COUNT(*) FROM t;

Output:

+----------+
| count(*) |
+----------+
|      100 |
+----------+

Query the trace data:

SELECT * FROM information_schema.sql_sharing;

Output:

*************************** 1. row ***************************
              TYPE: SQL
            SQL_ID: 83qmtgr8d6rwn
       SCHEMA_NAME: test
       DIGEST_TEXT: SELECT * FROM `t` WHERE `c2` < ?
           PLAN_ID: NULL
              PLAN: NULL
        PLAN_EXTRA: NULL
        ERROR_CODE: NULL
            REF_BY: SQL_TRACE
   FIRST_LOAD_TIME: 2022-11-07 19:04:30
     LAST_HIT_TIME: 2022-11-07 19:04:30
        EXECUTIONS: 1
     SUM_WAIT_TIME: 184
     MIN_WAIT_TIME: 184
     MAX_WAIT_TIME: 184
     SUM_EXEC_TIME: 417
     MIN_EXEC_TIME: 417
     MAX_EXEC_TIME: 417
     SUM_ROWS_SENT: 2
     MIN_ROWS_SENT: 2
     MAX_ROWS_SENT: 2
 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: 101
  MIN_LOGICAL_READ: 101
  MAX_LOGICAL_READ: 101
 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
*************************** 2. row ***************************
              TYPE: PLAN
            SQL_ID: 83qmtgr8d6rwn
       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
   FIRST_LOAD_TIME: 2022-11-07 19:04:30
     LAST_HIT_TIME: 2022-11-07 19:04:30
        EXECUTIONS: 1
     SUM_WAIT_TIME: 184
     MIN_WAIT_TIME: 184
     MAX_WAIT_TIME: 184
     SUM_EXEC_TIME: 417
     MIN_EXEC_TIME: 417
     MAX_EXEC_TIME: 417
     SUM_ROWS_SENT: 2
     MIN_ROWS_SENT: 2
     MAX_ROWS_SENT: 2
 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: 101
  MIN_LOGICAL_READ: 101
  MAX_LOGICAL_READ: 101
 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: SQL
            SQL_ID: cvdqw6ncsmwgh
       SCHEMA_NAME: test
       DIGEST_TEXT: CREATE TABLE `t` AS WITH RECURSIVE `t` ( `c1` , `c2` , `c3` ) AS ( SELECT ?, ... UNION ALL SELECT `c1` + ?  , `c2` + ?  , `c3` + ?  FROM `t` WHERE `c1` < ?  ) SELECT `c1` , `c2` , `c3` FROM `t`
           PLAN_ID: NULL
              PLAN: NULL
        PLAN_EXTRA: NULL
        ERROR_CODE: NULL
            REF_BY: SQL_TRACE
   FIRST_LOAD_TIME: 2022-11-07 19:04:15
     LAST_HIT_TIME: 2022-11-07 19:04:15
        EXECUTIONS: 1
     SUM_WAIT_TIME: 376
     MIN_WAIT_TIME: 376
     MAX_WAIT_TIME: 376
     SUM_EXEC_TIME: 10679
     MIN_EXEC_TIME: 10679
     MAX_EXEC_TIME: 10679
     SUM_ROWS_SENT: 0
     MIN_ROWS_SENT: 0
     MAX_ROWS_SENT: 0
 SUM_ROWS_EXAMINED: 200
 MIN_ROWS_EXAMINED: 200
 MAX_ROWS_EXAMINED: 200
 SUM_ROWS_AFFECTED: 100
 MIN_ROWS_AFFECTED: 100
 MAX_ROWS_AFFECTED: 100
  SUM_LOGICAL_READ: 1089
  MIN_LOGICAL_READ: 1089
  MAX_LOGICAL_READ: 1089
 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
*************************** 4. row ***************************
              TYPE: SQL
            SQL_ID: btcj0kh12wx26
       SCHEMA_NAME: test
       DIGEST_TEXT: CREATE INDEX `i_c1` ON `t` ( `c1` )
           PLAN_ID: NULL
              PLAN: NULL
        PLAN_EXTRA: NULL
        ERROR_CODE: NULL
            REF_BY: SQL_TRACE
   FIRST_LOAD_TIME: 2022-11-07 19:04:21
     LAST_HIT_TIME: 2022-11-07 19:04:21
        EXECUTIONS: 1
     SUM_WAIT_TIME: 295
     MIN_WAIT_TIME: 295
     MAX_WAIT_TIME: 295
     SUM_EXEC_TIME: 8620
     MIN_EXEC_TIME: 8620
     MAX_EXEC_TIME: 8620
     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: 761
  MIN_LOGICAL_READ: 761
  MAX_LOGICAL_READ: 761
 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 shows three tracked SQL statements (rows 1, 3, and 4 with TYPE = SQL) and one execution plan (row 2 with TYPE = PLAN). The SQL_ID links a SQL row to its PLAN rows — both row 1 and row 2 share SQL_ID: 83qmtgr8d6rwn.