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
YB4214083F06-000627B98ECCC111-0-0 SELECT O.crdt_contract_no, O.customer_no, O.customer_nm, O.prod_no, O.occr_type_cd, O.busi_ccy_cd, O.contract_amt, O.contract_bal, O.overdue_amt, O.fix_rate, O.exe_rate, O.start_dt, O.mat_dt, O.bfchg_major_gatmhd_cd, O.risk_classify_cd, O.optr_no, O.hndl_org_no, O.hndl_dt, O.afrnwl_contract_epday, CI.dcmtp_cd, CI.document_no FROM contract_bscinf O INNER JOIN cust_info CI ON O.customer_no = CI.customer_no INNER JOIN prd_catalog PC ON O.prod_no = PC.prod_no AND PC.onbs_ofbs_ind = '01' WHERE O.CONTRACT_STATUS IN ('320', '380') AND O.optr_no = '3954' AND O.hndl_org_no = '0011' 385382 2024-11-27 11:26:55.935500 2024-11-27 11:26:58.625300 2689720 mysql root sjcscredit 3715 1002 5.7.25-OceanBase-v4.2.1.7 20.8.63.6 2882 
explain SELECT     O.crdt_contract_no,     O.customer_no,     O.customer_nm,     O.prod_no,     O.occr_type_cd,     O.busi_ccy_cd,     O.contract_amt,     O.contract_bal,     O.overdue_amt,     O.fix_rate,     O.exe_rate,     O.start_dt,     O.mat_dt,     O.bfchg_major_gatmhd_cd,     O.risk_classify_cd,     O.optr_no,     O.hndl_org_no,     O.hndl_dt,     O.afrnwl_contract_epday,     CI.dcmtp_cd,     CI.document_no FROM     contract_bscinf O INNER JOIN     cust_info CI ON O.customer_no = CI.customer_no INNER JOIN     prd_catalog PC ON O.prod_no = PC.prod_no AND PC.onbs_ofbs_ind = '01' WHERE     O.CONTRACT_STATUS IN ('320', '380')     AND O.optr_no = '3954'     AND O.hndl_org_no = '0011'
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Query Plan                                                                                                                                                                     |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| =====================================================================                                                                                                          |
| |ID|OPERATOR                         |NAME    |EST.ROWS|EST.TIME(us)|                                                                                                          |
| ---------------------------------------------------------------------                                                                                                          |
| |0 |PX COORDINATOR                   |        |385383  |10240861    |                                                                                                          |
| |1 |└─EXCHANGE OUT DISTR             |:EX10001|385383  |8859001     |                                                                                                          |
| |2 |  └─HASH JOIN                    |        |385383  |5748888     |                                                                                                          |
| |3 |    ├─EXCHANGE IN DISTR          |        |385383  |4341154     |                                                                                                          |
| |4 |    │ └─EXCHANGE OUT DISTR (PKEY)|:EX10000|385383  |3134176     |                                                                                                          |
| |5 |    │   └─HASH JOIN              |        |385383  |417827      |                                                                                                          |
| |6 |    │     ├─TABLE FULL SCAN      |PC      |180     |33          |                                                                                                          |
| |7 |    │     └─TABLE FULL SCAN      |O       |385382  |293261      |                                                                                                          |
| |8 |    └─PX PARTITION ITERATOR      |        |3270518 |148645      |                                                                                                          |
| |9 |      └─TABLE FULL SCAN          |CI(tt)  |3270518 |148645      |                                                                                                          |
| =====================================================================                                                                                                          |
| Outputs & filters:                                                                                                                                                             |
| -------------------------------------                                                                                                                                          |
|   0 - output([INTERNAL_FUNCTION(O.CRDT_CONTRACT_NO, O.CUSTOMER_NO, O.CUSTOMER_NM, O.PROD_NO, O.OCCR_TYPE_CD, O.BUSI_CCY_CD, O.CONTRACT_AMT, O.CONTRACT_BAL,                    |
|        O.OVERDUE_AMT, O.FIX_RATE, O.EXE_RATE, O.START_DT, O.MAT_DT, O.BFCHG_MAJOR_GATMHD_CD, O.RISK_CLASSIFY_CD, O.OPTR_NO, O.HNDL_ORG_NO, O.HNDL_DT, O.AFRNWL_CONTRACT_EPDAY, |
|        CI.DCMTP_CD, CI.DOCUMENT_NO)]), filter(nil), rowset=256                                                                                                                 |
|   1 - output([INTERNAL_FUNCTION(O.CRDT_CONTRACT_NO, O.CUSTOMER_NO, O.CUSTOMER_NM, O.PROD_NO, O.OCCR_TYPE_CD, O.BUSI_CCY_CD, O.CONTRACT_AMT, O.CONTRACT_BAL,                    |
|        O.OVERDUE_AMT, O.FIX_RATE, O.EXE_RATE, O.START_DT, O.MAT_DT, O.BFCHG_MAJOR_GATMHD_CD, O.RISK_CLASSIFY_CD, O.OPTR_NO, O.HNDL_ORG_NO, O.HNDL_DT, O.AFRNWL_CONTRACT_EPDAY, |
|        CI.DCMTP_CD, CI.DOCUMENT_NO)]), filter(nil), rowset=256                                                                                                                 |
|       dop=1                                                                                                                                                                    |
|   2 - output([O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],           |
|        [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],                  |
|       [O.AFRNWL_CONTRACT_EPDAY], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), filter(nil), rowset=256                                                                                     |
|       equal_conds([O.CUSTOMER_NO = CI.CUSTOMER_NO]), other_conds(nil)                                                                                                          |
|   3 - output([O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],           |
|        [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],                  |
|       [O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=256                                                                                                                      |
|   4 - output([O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],           |
|        [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],                  |
|       [O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=256                                                                                                                      |
|       (#keys=1, [O.CUSTOMER_NO]), is_single, dop=1                                                                                                                             |
|   5 - output([O.CUSTOMER_NO], [O.PROD_NO], [O.CRDT_CONTRACT_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],           |
|        [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],                  |
|       [O.AFRNWL_CONTRACT_EPDAY]), filter(nil), rowset=256                                                                                                                      |
|       equal_conds([O.PROD_NO = PC.PROD_NO]), other_conds(nil)                                                                                                                  |
|   6 - output([PC.PROD_NO]), filter([PC.ONBS_OFBS_IND = cast('01', VARCHAR(1048576))]), rowset=256                                                                              |
|       access([PC.PROD_NO], [PC.ONBS_OFBS_IND]), partitions(p0)                                                                                                                 |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false],                                                                                              |
|       range_key([PC.PROD_NO]), range(MIN ; MAX)always true                                                                                                                     |
|   7 - output([O.CRDT_CONTRACT_NO], [O.CUSTOMER_NO], [O.PROD_NO], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD], [O.CONTRACT_AMT],           |
|        [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD], [O.HNDL_DT],                  |
|       [O.AFRNWL_CONTRACT_EPDAY]), filter([O.CONTRACT_STATUS IN (cast('320', VARCHAR(1048576)), cast('380', VARCHAR(1048576)))], [O.HNDL_ORG_NO = cast('0011',                  |
|       VARCHAR(1048576))], [O.OPTR_NO = cast('3954', VARCHAR(1048576))]), rowset=256                                                                                            |
|       access([O.CRDT_CONTRACT_NO], [O.CUSTOMER_NO], [O.PROD_NO], [O.CONTRACT_STATUS], [O.OPTR_NO], [O.HNDL_ORG_NO], [O.CUSTOMER_NM], [O.OCCR_TYPE_CD], [O.BUSI_CCY_CD],        |
|        [O.CONTRACT_AMT], [O.CONTRACT_BAL], [O.OVERDUE_AMT], [O.FIX_RATE], [O.EXE_RATE], [O.START_DT], [O.MAT_DT], [O.BFCHG_MAJOR_GATMHD_CD], [O.RISK_CLASSIFY_CD],             |
|        [O.HNDL_DT], [O.AFRNWL_CONTRACT_EPDAY]), partitions(p0)                                                                                                                 |
|       is_index_back=false, is_global_index=false, filter_before_indexback[false,false,false],                                                                                  |
|       range_key([O.CRDT_CONTRACT_NO]), range(MIN ; MAX)always true                                                                                                             |
|   8 - output([CI.CUSTOMER_NO], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), filter(nil), rowset=256                                                                                       |
|       affinitize, force partition granule                                                                                                                                      |
|   9 - output([CI.CUSTOMER_NO], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), filter(nil), rowset=256                                                                                       |
|       access([CI.CUSTOMER_NO], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), partitions(p[0-255])                                                                                          |
|       is_index_back=false, is_global_index=false,                                                                                                                              |
|       range_key([CI.CUSTOMER_NO], [CI.DCMTP_CD], [CI.DOCUMENT_NO]), range(MIN,MIN,MIN ; MAX,MAX,MAX)always true                                                                |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
select * from oceanbase.gv$ob_plan_cache_plan_explain where tenant_id = 1002 and plan_id = 3715  and svr_ip = '20.8.63.6' and svr_port = 2882
+-----------+-----------+----------+---------+------------+--------------+----------------------------+--------+---------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| TENANT_ID | SVR_IP    | SVR_PORT | PLAN_ID | PLAN_DEPTH | PLAN_LINE_ID | OPERATOR                   | NAME   | ROWS    | COST     | PROPERTY                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+-----------+-----------+----------+---------+------------+--------------+----------------------------+--------+---------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 1002      | 20.8.63.6 | 2882     | 3715    | 0          | 0            | PHY_PX_FIFO_COORD          | NULL   | 385383  | 10240860 | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 1002      | 20.8.63.6 | 2882     | 3715    | 1          | 1            |  PHY_PX_REDUCE_TRANSMIT    | NULL   | 385383  | 8859000  | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 1002      | 20.8.63.6 | 2882     | 3715    | 2          | 2            |   PHY_HASH_JOIN            | NULL   | 385383  | 5748887  | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 1002      | 20.8.63.6 | 2882     | 3715    | 3          | 3            |    PHY_PX_FIFO_RECEIVE     | NULL   | 385383  | 4341153  | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 1002      | 20.8.63.6 | 2882     | 3715    | 4          | 4            |     PHY_PX_REPART_TRANSMIT | NULL   | 385383  | 3134175  | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 1002      | 20.8.63.6 | 2882     | 3715    | 5          | 5            |      PHY_HASH_JOIN         | NULL   | 385383  | 417826   | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 1002      | 20.8.63.6 | 2882     | 3715    | 6          | 6            |       PHY_TABLE_SCAN       | PC     | 180     | 32       | table_rows:420, physical_range_rows:420, logical_range_rows:420, index_back_rows:0, output_rows:179, avaiable_index_name[prd_catalog], estimation info[table_id:500003, (table_type:10, version:-1--1--1, logical_rc:420, physical_rc:420)]                                                                                                                                                                                                                                                                                                                                                                        |
| 1002      | 20.8.63.6 | 2882     | 3715    | 6          | 7            |       PHY_TABLE_SCAN       | O      | 385382  | 293260   | table_rows:4867193, physical_range_rows:4867193, logical_range_rows:4867193, index_back_rows:0, output_rows:428510, avaiable_index_name[tmp,contract_bscinf_CONTRACT_STATUS_IDX,contract_bscinf_CUSTOMER_NO_IDX,contract_bscinf_OPTR_NO_IDX,contract_bscinf_CUSTOMER_NM_IDX,contract_bscinf_PROD_NO_IDX,contract_bscinf_hndl_org_no,contract_bscinf], pruned_index_name[contract_bscinf_CONTRACT_STATUS_IDX,contract_bscinf_OPTR_NO_IDX,contract_bscinf_CUSTOMER_NM_IDX,contract_bscinf_hndl_org_no], estimation info[table_id:500005, (table_type:10, version:-1--1--1, logical_rc:4867193, physical_rc:4867193)] |
| 1002      | 20.8.63.6 | 2882     | 3715    | 3          | 8            |    PHY_GRANULE_ITERATOR    | NULL   | 3270518 | 148644   | NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
| 1002      | 20.8.63.6 | 2882     | 3715    | 4          | 9            |     PHY_TABLE_SCAN         | CI(tt) | 3270518 | 148644   | table_rows:3270518, physical_range_rows:3270518, logical_range_rows:3270518, index_back_rows:0, output_rows:3270518, avaiable_index_name[cust_info_CUSTOMER_STATUS_IDX,cust_info_DOCUMENT_NO_IDX,CUST_INFO_DCMTP_CD_IDX,tt,cust_info], pruned_index_name[cust_info_CUSTOMER_STATUS_IDX,cust_info_DOCUMENT_NO_IDX,CUST_INFO_DCMTP_CD_IDX]                                                                                                                                                                                                                                                                           |
+-----------+-----------+----------+---------+------------+--------------+----------------------------+--------+---------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

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 = 'YB4214083F06-000627B98ECCC111-0-0'
) plan_monitor
LEFT JOIN
(
 SELECT ROWS, PLAN_LINE_ID FROM oceanbase.gv$ob_plan_cache_plan_explain WHERE plan_id = 3715 AND tenant_id = 1002
) 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 = 'YB4214083F06-000627B98ECCC111-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 = 'YB4214083F06-000627B98ECCC111-0-0'
ORDER BY
  PLAN_LINE_ID ASC, SVR_IP, SVR_PORT, CHANGE_TS, PROCESS_NAME ASC;

报告生成时间： 2024-11-27 11:28:30