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
YB42AC1A317E-00061F9271379957-0-0 SELECT
sl.p_id AS pId,sl.store_acct_id as storeAcctId,
CASE WHEN MAX(sinfo.`is_sale`) = 0 THEN 1
WHEN MIN(sinfo.`is_sale`) != MAX(sinfo.`is_sale`) THEN 0
ELSE 2 END AS prodIsSale
FROM
prod_sync_s_lazada sl,
prod_s_info sinfo
WHERE sl.prod_s_id = sinfo.id AND sl.p_id= 370533393		 1 2024-08-14 13:54:31.956700 2024-08-14 13:54:32.622100 665409 mengxuan mengxuan_admin trade_online 88967 1008 5.7.25-OceanBase_CE-v4.2.2.0 172.26.49.126 2882 
select * from oceanbase.gv$ob_plan_cache_plan_explain where tenant_id = 1008 and plan_id = 88967  and svr_ip = '172.26.49.126' and svr_port = 2882
+-----------+---------------+----------+---------+------------+--------------+---------------------------+----------------+---------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TENANT_ID | SVR_IP        | SVR_PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR                  | NAME           | ROWS    | COST   | PROPERTY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+-----------+---------------+----------+---------+------------+--------------+---------------------------+----------------+---------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1008      | 172.26.49.126 | 2882     | 88967   | 0          | 0            | PHY_SCALAR_AGGREGATE      | NULL           | 1       | 354000 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| 1008      | 172.26.49.126 | 2882     | 88967   | 1          | 1            |  PHY_HASH_JOIN            | NULL           | 28307   | 353049 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| 1008      | 172.26.49.126 | 2882     | 88967   | 2          | 2            |   PHY_PX_FIFO_COORD       | NULL           | 28663   | 165340 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| 1008      | 172.26.49.126 | 2882     | 88967   | 3          | 3            |    PHY_PX_REDUCE_TRANSMIT | NULL           | 28663   | 152257 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| 1008      | 172.26.49.126 | 2882     | 88967   | 4          | 4            |     PHY_GRANULE_ITERATOR  | NULL           | 28663   | 122895 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   |
| 1008      | 172.26.49.126 | 2882     | 88967   | 5          | 5            |      PHY_TABLE_SCAN       | sl(index_p_id) | 28663   | 122895 | table_rows:227510664, physical_range_rows:28663, logical_range_rows:28663, index_back_rows:28663, output_rows:28663, avaiable_index_name[index_p_id,index_store_sub_id,idx_acct_id,idx_shop_sku,index_prod_s_sku,index_store_s_sku,idx_prod_s_id,prod_sync_s_lazada], pruned_index_name[index_store_sub_id,idx_acct_id,idx_shop_sku,index_prod_s_sku,index_store_s_sku]                                                                                                                                                                                                                                                                                                                |
| 1008      | 172.26.49.126 | 2882     | 88967   | 2          | 6            |   PHY_TABLE_SCAN          | sinfo          | 1109843 | 78600  | table_rows:1109733, physical_range_rows:1218480, logical_range_rows:1109843, index_back_rows:0, output_rows:1109843, avaiable_index_name[idx_s_sku,idx_remark,index_p_id,idx_purchase_channel,idx_buyer,idx_is_sale_is_combination,prod_s_info], pruned_index_name[idx_s_sku,idx_remark,index_p_id,idx_purchase_channel,idx_buyer,idx_is_sale_is_combination], estimation info[table_id:601726, (table_type:10, version:-1--1--1, logical_rc:1109278, physical_rc:1109278), (table_type:11, version:-1--1--1, logical_rc:546, physical_rc:86822), (table_type:12, version:-1--1--1, logical_rc:17, physical_rc:22313), (table_type:0, version:-1--1--1, logical_rc:2, physical_rc:67)] |
+-----------+---------------+----------+---------+------------+--------------+---------------------------+----------------+---------+--------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

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 = 'YB42AC1A317E-00061F9271379957-0-0'
) plan_monitor
LEFT JOIN
(
 SELECT ROWS, PLAN_LINE_ID FROM oceanbase.gv$ob_plan_cache_plan_explain WHERE plan_id = 88967 AND tenant_id = 1008 and svr_ip = '172.26.49.126'  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 = 'YB42AC1A317E-00061F9271379957-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 = 'YB42AC1A317E-00061F9271379957-0-0'
ORDER BY
  PLAN_LINE_ID ASC, SVR_IP, SVR_PORT, CHANGE_TS, PROCESS_NAME ASC;

报告生成时间： 2024-08-14 13:54:57