SQL Monitor Report

trace_id query_sql RETURN_ROWS REQUEST_TIME REQUEST_END_TIME ELAPSED_TIME TENANT_NAME user_name db_name plan_id tenant_id mysql_version svr_ip svr_port
YB420AC62872-00062857D1EFB534-0-0 /* ApplicationName=DBeaver 24.1.3 - SQLEditor <Script-2.sql> */ select /*+monitor */ sum(GC012_Client) from GT012_LOGININFO_6
LIMIT 0, 200		 1 2024-12-04 09:13:42.903300 2024-12-04 09:13:43.654700 751395 xcdev csp test 2287 1006 5.7.25-OceanBase_CE-v4.2.5.0 10.198.40.114 2882 
explain extended /* ApplicationName=DBeaver 24.1.3 - SQLEditor  */ select /*+monitor */ sum(GC012_Client) from GT012_LOGININFO_6
LIMIT 0, 200
+--------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                         |
+--------------------------------------------------------------------------------------------------------------------+
| ====================================================================                                               |
| |ID|OPERATOR               |NAME             |EST.ROWS|EST.TIME(us)|                                               |
| --------------------------------------------------------------------                                               |
| |0 |EXCHANGE IN REMOTE     |                 |1       |466657      |                                               |
| |1 |└─EXCHANGE OUT REMOTE  |                 |1       |466657      |                                               |
| |2 |  └─LIMIT              |                 |1       |466657      |                                               |
| |3 |    └─SCALAR GROUP BY  |                 |1       |466657      |                                               |
| |4 |      └─TABLE FULL SCAN|GT012_LOGININFO_6|8400000 |314415      |                                               |
| ====================================================================                                               |
| Outputs & filters:                                                                                                 |
| -------------------------------------                                                                              |
|   0 - output([T_FUN_SUM(GT012_LOGININFO_6.GC012_Client(0x1518012223d0))(0x151801221c60)]), filter(nil)             |
|   1 - output([T_FUN_SUM(GT012_LOGININFO_6.GC012_Client(0x1518012223d0))(0x151801221c60)]), filter(nil)             |
|   2 - output([T_FUN_SUM(GT012_LOGININFO_6.GC012_Client(0x1518012223d0))(0x151801221c60)]), filter(nil), rowset=256 |
|       limit(200(0x151801222a50)), offset(0(0x151801222770))                                                        |
|   3 - output([T_FUN_SUM(GT012_LOGININFO_6.GC012_Client(0x1518012223d0))(0x151801221c60)]), filter(nil), rowset=256 |
|       group(nil), agg_func([T_FUN_SUM(GT012_LOGININFO_6.GC012_Client(0x1518012223d0))(0x151801221c60)])            |
|   4 - output([GT012_LOGININFO_6.GC012_Client(0x1518012223d0)]), filter(nil), rowset=256                            |
|       access([GT012_LOGININFO_6.GC012_Client(0x1518012223d0)]), partitions(p0)                                     |
|       is_index_back=false, is_global_index=false,                                                                  |
|       range_key([GT012_LOGININFO_6.GC012_ID(0x151801222f90)]), range(MIN ; MAX)always true                         |
| Used Hint:                                                                                                         |
| -------------------------------------                                                                              |
|   /*+                                                                                                              |
|                                                                                                                    |
|       MONITOR                                                                                                      |
|   */                                                                                                               |
| Qb name trace:                                                                                                     |
| -------------------------------------                                                                              |
|   stmt_id:0, stmt_type:T_EXPLAIN                                                                                   |
|   stmt_id:1, SEL$1                                                                                                 |
| Outline Data:                                                                                                      |
| -------------------------------------                                                                              |
|   /*+                                                                                                              |
|       BEGIN_OUTLINE_DATA                                                                                           |
|       FULL(@"SEL$1" "obkv_redis"."GT012_LOGININFO_6"@"SEL$1")                                                      |
|       MONITOR                                                                                                      |
|       OPTIMIZER_FEATURES_ENABLE('4.2.5.0')                                                                         |
|       END_OUTLINE_DATA                                                                                             |
|   */                                                                                                               |
| Optimization Info:                                                                                                 |
| -------------------------------------                                                                              |
|   GT012_LOGININFO_6:                                                                                               |
|       table_rows:11581963                                                                                          |
|       physical_range_rows:8400000                                                                                  |
|       logical_range_rows:8400000                                                                                   |
|       index_back_rows:0                                                                                            |
|       output_rows:8400000                                                                                          |
|       table_dop:1                                                                                                  |
|       dop_method:Table DOP                                                                                         |
|       avaiable_index_name:[GT012_LOGININFO_6]                                                                      |
|       stats info:[version=2024-12-04 09:03:49.265635, is_locked=0, is_expired=0]                                   |
|       dynamic sampling level:0                                                                                     |
|       estimation method:[OPTIMIZER STATISTICS, STORAGE]                                                            |
|   Plan Type:                                                                                                       |
|       REMOTE                                                                                                       |
|   Parameters:                                                                                                      |
|       :0 => 0                                                                                                      |
|       :1 => 200                                                                                                    |
|   Note:                                                                                                            |
|       Degree of Parallelisim is 1 because of table property                                                        |
+--------------------------------------------------------------------------------------------------------------------+
select * from oceanbase.gv$ob_plan_cache_plan_explain where tenant_id = 1006 and plan_id = 2287  and svr_ip = '10.198.40.114' and svr_port = 2882
+-----------+---------------+----------+---------+------------+--------------+-----------------------+-------------------+---------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TENANT_ID | SVR_IP        | SVR_PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR              | NAME              | ROWS    | COST   | PROPERTY                                                                                                                                                                                                                                                                                                                                                                                                                         |
+-----------+---------------+----------+---------+------------+--------------+-----------------------+-------------------+---------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1006      | 10.198.40.114 | 2882     | 2287    | 0          | 0            | PHY_LIMIT             | NULL              | 1       | 479109 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 1006      | 10.198.40.114 | 2882     | 2287    | 1          | 1            |  PHY_SCALAR_AGGREGATE | NULL              | 1       | 479109 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                             |
| 1006      | 10.198.40.114 | 2882     | 2287    | 2          | 2            |   PHY_TABLE_SCAN      | GT012_LOGININFO_6 | 8400001 | 326867 | table_rows:8400000, physical_range_rows:8400001, logical_range_rows:8400001, index_back_rows:0, output_rows:8400001, avaiable_index_name[GT012_LOGININFO_6], estimation info[table_id:505455, (table_type:12, version:-1--1--1, logical_rc:3110511, physical_rc:3110511), (table_type:12, version:-1--1--1, logical_rc:3034999, physical_rc:3034999), (table_type:0, version:-1--1--1, logical_rc:2254491, physical_rc:2254491)] |
+-----------+---------------+----------+---------+------------+--------------+-----------------------+-------------------+---------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

SCHEMA 信息

SQL_AUDIT 信息

SQL_PLAN_MONITOR DFO 级调度时序汇总

调度时序图

数据时序图

SQL_PLAN_MONITOR SQC 级汇总

Goto 算子优先 Goto 机器优先

算子优先视图

机器优先视图

SQL_PLAN_MONITOR 详情

Goto 算子优先 Goto 线程优先

算子优先视图

线程优先视图

本报告在租户下使用的 SQL

DFO 级
select
  AVG(ROWS) EST_ROWS, /*0*/
  plan_monitor.PLAN_DEPTH PLAN_DEPTH,
  plan_monitor.PLAN_LINE_ID PLAN_LINE_ID,
  PLAN_OPERATION,
  COUNT(*) PARALLEL,
  MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME,/*5*/
  MAX(LAST_REFRESH_TIME) MAX_LAST_REFRESH_TIME,
  MIN(FIRST_CHANGE_TIME) MIN_FIRST_CHANGE_TIME,
  MAX(LAST_CHANGE_TIME) MAX_LAST_CHANGE_TIME,
  UNIX_TIMESTAMP(MIN(FIRST_REFRESH_TIME)) MIN_FIRST_REFRESH_TS,
  UNIX_TIMESTAMP(MAX(LAST_REFRESH_TIME)) MAX_LAST_REFRESH_TS, /*10*/
  UNIX_TIMESTAMP(MIN(FIRST_CHANGE_TIME)) MIN_FIRST_CHANGE_TS,
  UNIX_TIMESTAMP(MAX(LAST_CHANGE_TIME)) MAX_LAST_CHANGE_TS,
  AVG(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) AVG_REFRESH_TIME,
  MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) MAX_REFRESH_TIME,
  MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) MIN_REFRESH_TIME, /*15 */
  AVG(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) AVG_CHANGE_TIME,
  MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) MAX_CHANGE_TIME,
  MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) MIN_CHANGE_TIME,
  SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
  (MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) - MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME))) / MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)+0.00000001) SKEWNESS,
  SUM(STARTS) TOTAL_RESCAN_TIMES,/* 20 */
  MAX(OTHERSTAT_1_ID) OTHERSTAT_1_ID,
  SUM(OTHERSTAT_1_VALUE) SUM_STAT_1,
  MAX(OTHERSTAT_1_VALUE) MAX_STAT_1,
  MIN(OTHERSTAT_1_VALUE) MIN_STAT_1,
  AVG(OTHERSTAT_1_VALUE) AVG_STAT_1, /* 25 */
  MAX(OTHERSTAT_2_ID) OTHERSTAT_2_ID,
  SUM(OTHERSTAT_2_VALUE) SUM_STAT_2,
  MAX(OTHERSTAT_2_VALUE) MAX_STAT_2,
  MIN(OTHERSTAT_2_VALUE) MIN_STAT_2,
  AVG(OTHERSTAT_2_VALUE) AVG_STAT_2, /* 30 */
  MAX(OTHERSTAT_3_ID) OTHERSTAT_3_ID,
  SUM(OTHERSTAT_3_VALUE) SUM_STAT_3,
  MAX(OTHERSTAT_3_VALUE) MAX_STAT_3,
  MIN(OTHERSTAT_3_VALUE) MIN_STAT_3,
  AVG(OTHERSTAT_3_VALUE) AVG_STAT_3, /* 35 */
  MAX(OTHERSTAT_4_ID) OTHERSTAT_4_ID,
  SUM(OTHERSTAT_4_VALUE) SUM_STAT_4,
  MAX(OTHERSTAT_4_VALUE) MAX_STAT_4,
  MIN(OTHERSTAT_4_VALUE) MIN_STAT_4,
  AVG(OTHERSTAT_4_VALUE) AVG_STAT_4, /* 40 */
  MAX(OTHERSTAT_5_ID) OTHERSTAT_5_ID,
  SUM(OTHERSTAT_5_VALUE) SUM_STAT_5,
  MAX(OTHERSTAT_5_VALUE) MAX_STAT_5,
  MIN(OTHERSTAT_5_VALUE) MIN_STAT_5,
  AVG(OTHERSTAT_5_VALUE) AVG_STAT_5, /* 45*/
  MAX(OTHERSTAT_6_ID) OTHERSTAT_6_ID,
  SUM(OTHERSTAT_6_VALUE) SUM_STAT_6,
  MAX(OTHERSTAT_6_VALUE) MAX_STAT_6,
  MIN(OTHERSTAT_6_VALUE) MIN_STAT_6,
  AVG(OTHERSTAT_6_VALUE) AVG_STAT_6 /* 50 */,
  TRUNCATE(AVG(DB_TIME)/1000000000.0/2.5, 4) MY_DB_TIME,
  TRUNCATE(AVG(DB_TIME-USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_CPU_TIME,
  TRUNCATE(AVG(USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_IO_TIME
from
(
  select * FROM oceanbase.gv$sql_plan_monitor
where
  trace_id = 'YB420AC62872-00062857D1EFB534-0-0'
) plan_monitor
LEFT JOIN
(
 SELECT ROWS, PLAN_LINE_ID FROM oceanbase.gv$ob_plan_cache_plan_explain WHERE plan_id = 2287 AND tenant_id = 1006 and svr_ip = '10.198.40.114'  and svr_port = 2882
) plan_explain
ON
  plan_monitor.PLAN_LINE_ID = plan_explain.PLAN_LINE_ID
GROUP BY
  plan_monitor.PLAN_LINE_ID, plan_monitor.PLAN_OPERATION
ORDER BY
  plan_monitor.PLAN_LINE_ID ASC;

机器级
select
  PLAN_DEPTH,
  PLAN_LINE_ID,
  PLAN_OPERATION,
  COUNT(*) PARALLEL,
  MIN(FIRST_REFRESH_TIME) MIN_FIRST_REFRESH_TIME,
  MAX(LAST_REFRESH_TIME) MAX_LAST_REFRESH_TIME,
  MIN(FIRST_CHANGE_TIME) MIN_FIRST_CHANGE_TIME,
  MAX(LAST_CHANGE_TIME) MAX_LAST_CHANGE_TIME,
  UNIX_TIMESTAMP(MIN(FIRST_REFRESH_TIME)) MIN_FIRST_REFRESH_TS,
  UNIX_TIMESTAMP(MAX(LAST_REFRESH_TIME)) MAX_LAST_REFRESH_TS,
  UNIX_TIMESTAMP(MIN(FIRST_CHANGE_TIME)) MIN_FIRST_CHANGE_TS,
  UNIX_TIMESTAMP(MAX(LAST_CHANGE_TIME)) MAX_LAST_CHANGE_TS,
  AVG(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) AVG_REFRESH_TIME,
  MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) MAX_REFRESH_TIME,
  MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_REFRESH_TIME, LAST_REFRESH_TIME)) MIN_REFRESH_TIME,
  AVG(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) AVG_CHANGE_TIME,
  MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) MAX_CHANGE_TIME,
  MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) MIN_CHANGE_TIME,
  SUM(OUTPUT_ROWS) TOTAL_OUTPUT_ROWS,
  (MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) - MIN(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME))) / MAX(TIMESTAMPDIFF(MICROSECOND, FIRST_CHANGE_TIME, LAST_CHANGE_TIME)) SKEWNESS,
  SUM(STARTS) TOTAL_RESCAN_TIMES,
  SVR_IP,
  SVR_PORT,
  MAX(OTHERSTAT_1_VALUE) MAX_STAT_1,
  MIN(OTHERSTAT_1_VALUE) MIN_STAT_1,
  AVG(OTHERSTAT_1_VALUE) AVG_STAT_1,
  MAX(OTHERSTAT_2_VALUE) MAX_STAT_2,
  MIN(OTHERSTAT_2_VALUE) MIN_STAT_2,
  AVG(OTHERSTAT_2_VALUE) AVG_STAT_2,
  MAX(OTHERSTAT_3_VALUE) MAX_STAT_3,
  MIN(OTHERSTAT_3_VALUE) MIN_STAT_3,
  AVG(OTHERSTAT_3_VALUE) AVG_STAT_3,
  MAX(OTHERSTAT_4_VALUE) MAX_STAT_4,
  MIN(OTHERSTAT_4_VALUE) MIN_STAT_4,
  AVG(OTHERSTAT_4_VALUE) AVG_STAT_4,
  MAX(OTHERSTAT_5_VALUE) MAX_STAT_5,
  MIN(OTHERSTAT_5_VALUE) MIN_STAT_5,
  AVG(OTHERSTAT_5_VALUE) AVG_STAT_5,
  MAX(OTHERSTAT_6_VALUE) MAX_STAT_6,
  MIN(OTHERSTAT_6_VALUE) MIN_STAT_6,
  AVG(OTHERSTAT_6_VALUE) AVG_STAT_6,
  TRUNCATE(AVG(DB_TIME)/1000000000.0/2.5, 4) MY_DB_TIME,
  TRUNCATE(AVG(DB_TIME-USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_CPU_TIME,
  TRUNCATE(AVG(USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_IO_TIME
from
  oceanbase.gv$sql_plan_monitor
where
  trace_id = 'YB420AC62872-00062857D1EFB534-0-0'
GROUP BY
  PLAN_LINE_ID,PLAN_OPERATION,SVR_IP,SVR_PORT
ORDER BY
  PLAN_LINE_ID ASC, MAX_CHANGE_TIME ASC, SVR_IP, SVR_PORT;

线程级
select
  PLAN_DEPTH,/*0*/
  SVR_IP,
  SVR_PORT,
  PROCESS_NAME,
  PLAN_LINE_ID,
  PLAN_OPERATION, /*5*/
  OUTPUT_ROWS,
  STARTS RESCAN_TIMES,
  FIRST_REFRESH_TIME,
  LAST_REFRESH_TIME,
  FIRST_CHANGE_TIME,/*10*/
  LAST_CHANGE_TIME,
  UNIX_TIMESTAMP(FIRST_REFRESH_TIME) FIRST_REFRESH_TS,
  UNIX_TIMESTAMP(LAST_REFRESH_TIME) LAST_REFRESH_TS,
  UNIX_TIMESTAMP(LAST_REFRESH_TIME) - UNIX_TIMESTAMP(FIRST_REFRESH_TIME) REFRESH_TS,
  UNIX_TIMESTAMP(FIRST_CHANGE_TIME) FIRST_CHANGE_TS, /*15*/
  UNIX_TIMESTAMP(LAST_CHANGE_TIME) LAST_CHANGE_TS,
  UNIX_TIMESTAMP(LAST_CHANGE_TIME) - UNIX_TIMESTAMP(FIRST_CHANGE_TIME) CHANGE_TS,
  OTHERSTAT_1_ID,
  OTHERSTAT_1_VALUE,
  OTHERSTAT_2_ID, /*20*/
  OTHERSTAT_2_VALUE,
  OTHERSTAT_3_ID,
  OTHERSTAT_3_VALUE,
  OTHERSTAT_4_ID,
  OTHERSTAT_4_VALUE, /*25*/
  OTHERSTAT_5_ID,
  OTHERSTAT_5_VALUE,
  OTHERSTAT_6_ID,
  OTHERSTAT_6_VALUE,
  TRUNCATE((DB_TIME)/1000000000.0/2.5, 4) MY_DB_TIME,
  TRUNCATE((DB_TIME-USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_CPU_TIME,
  TRUNCATE((USER_IO_WAIT_TIME)/1000000000.0/2.5, 4) MY_IO_TIME
from
  oceanbase.gv$sql_plan_monitor
where
    trace_id = 'YB420AC62872-00062857D1EFB534-0-0'
ORDER BY
  PLAN_LINE_ID ASC, SVR_IP, SVR_PORT, CHANGE_TS, PROCESS_NAME ASC;

报告生成时间： 2024-12-04 09:15:54