实战案例 | 从执行画像入手诊断 SQL 性能瓶颈(十五)

1、优化过程

1.1 原始SQL

SELECT DISTINCT CP.PRODUCT_ID
FROM CT_PRODUCT CP
LEFT JOIN CT_STORE_PRODUCT_REL CSPR ON CSPR.PRODUCT_ID = CP.PRODUCT_ID
LEFT JOIN CT_STORE CS ON CS.STORE_ID = CSPR.STORE_ID
LEFT JOIN CT_VENDOR CV ON CV.VENDOR_ID = CS.VENDOR_ID
LEFT JOIN CT_ACT_STAGE_PRODUCT CASP ON CASP.PRODUCT_ID = CP.PRODUCT_ID
LEFT JOIN CT_ACT_STAGE CAS ON CAS.STAGE_ID = CASP.STAGE_ID
LEFT JOIN CT_ACT CA ON CA.ACT_ID = CAS.ACT_ID
WHERE
CP.PRODUCT_STATUS = '01'
AND CSPR.RELATIONSHIP_STATUS = '01'
AND CS.STORE_STATUS = '0' AND CV.VENDOR_STATUS = '00'
AND CASP.PRODUCT_STATUS = '1' AND CA.ACT_LABEL IN ('最红星期五')
AND CA.ACT_TYPE = '98' AND CA.ACT_STATUS = '2'
AND DATE(CA.ACT_START_TIME) <= current_date
AND DATE(CA.ACT_END_TIME) >= current_date
AND CA.DAILY_START_TIME IN ('00:00:00')
AND CV.TRADE_SUB_TYPE IN ('5812')
AND CV.MRCHNT_LEVEL IN ('1','2')
and ca.ACT_ID in ('CA1202406270729');

2.2 最优执行计划explain

MySQL [nctm_db]> explain extended SELECT DISTINCT CP.PRODUCT_ID FROM CT_PRODUCT CP LEFT JOIN CT_STORE_PRODUCT_REL CSPR ON CSPR.PRODUCT_ID = CP.PRODUCT_ID LEFT JOIN CT_STORE CS ON CS.STORE_ID = CSPR.STORE_ID LEFT JOIN CT_VENDOR CV ON CV.VENDOR_ID = CS.VENDOR_ID LEFT JOIN CT_ACT_STAGE_PRODUCT CASP ON CASP.PRODUCT_ID = CP.PRODUCT_ID LEFT JOIN CT_ACT_STAGE CAS ON CAS.STAGE_ID = CASP.STAGE_ID LEFT JOIN CT_ACT CA ON CA.ACT_ID = CAS.ACT_ID WHERE CP.PRODUCT_STATUS = '01' AND CSPR.RELATIONSHIP_STATUS = '01' AND CS.STORE_STATUS = '0' AND CV.VENDOR_STATUS = '00' AND CASP.PRODUCT_STATUS = '1' AND CA.ACT_LABEL IN ('最红星期五') AND CA.ACT_TYPE = '98' AND CA.ACT_STATUS = '2' AND DATE(CA.ACT_START_TIME) <= current_date AND DATE(CA.ACT_END_TIME) >= current_date AND CA.DAILY_START_TIME IN ('00:00:00') AND CV.TRADE_SUB_TYPE IN ('5812') AND CV.MRCHNT_LEVEL IN ('1','2') and ca.ACT_ID in ('CA1202406270729')\G;
*************************** 1. row ***************************
Query Plan: =============================================================================================================
|ID|OPERATOR                    |NAME                                                     |EST. ROWS|COST   |
-------------------------------------------------------------------------------------------------------------
|0 |MERGE DISTINCT              |                                                         |1        |1451962|
|1 | SORT                       |                                                         |1        |1451962|
|2 |  NESTED-LOOP JOIN CARTESIAN|                                                         |1        |1451962|
|3 |   HASH JOIN                |                                                         |15730    |1451871|
|4 |    TABLE SCAN              |CV                                                       |30000    |11808  |
|5 |    HASH JOIN               |                                                         |88390    |1356459|
|6 |     HASH JOIN              |                                                         |86598    |753023 |
|7 |      HASH JOIN             |                                                         |5937     |198923 |
|8 |       NESTED-LOOP JOIN     |                                                         |11876    |152599 |
|9 |        TABLE SCAN          |CAS(IDX_CT_ACT_STAGE_ACT_ID)                             |181      |71     |
|10|        TABLE SCAN          |CASP(IDX_CT_ACT_STAGE_PRODUCT_STAGE_ID)                  |33       |835    |
|11|       TABLE SCAN           |CP                                                       |28453    |17656  |
|12|      TABLE SCAN            |CSPR(IDX_CT_STORE_PRODUCT_REL_STATUS_STORE_ID_PRODUCT_ID)|512294   |198158 |
|13|     TABLE SCAN             |CS                                                       |455981   |219409 |
|14|   MATERIAL                 |                                                         |1        |92     |
|15|    TABLE SCAN              |CA(UK_CT_ACT_ACT_ID)                                     |1        |92     |
=============================================================================================================
  
Outputs & filters:
-------------------------------------
  0 - output([CP.PRODUCT_ID(0x7e50d50374b0)]), filter(nil),
      distinct([CP.PRODUCT_ID(0x7e50d50374b0)])
  1 - output([CP.PRODUCT_ID(0x7e50d50374b0)]), filter(nil), sort_keys([CP.PRODUCT_ID(0x7e50d50374b0), ASC])
  2 - output([CP.PRODUCT_ID(0x7e50d50374b0)]), filter(nil),
      conds(nil), nl_params_(nil), batch_join=false
  3 - output([CP.PRODUCT_ID(0x7e50d50374b0)]), filter(nil),
      equal_conds([CV.VENDOR_ID(0x7e50d5039c00) = CS.VENDOR_ID(0x7e50d5039ef0)(0x7e50d50394e0)]), other_conds(nil)
  4 - output([CV.VENDOR_ID(0x7e50d5039c00)]), filter([CV.VENDOR_STATUS(0x7e50d5043dc0) = '00'(0x7e50d50436a0)], [CV.TRADE_SUB_TYPE(0x7e50d5051150) = '5812'(0x7e50d504fdb0)], [CV.MRCHNT_LEVEL(0x7e50d5052b90) IN ('1', '2')(0x7e50d5051f10)(0x7e50d5051520)]),
      access([CV.VENDOR_ID(0x7e50d5039c00)], [CV.VENDOR_STATUS(0x7e50d5043dc0)], [CV.TRADE_SUB_TYPE(0x7e50d5051150)], [CV.MRCHNT_LEVEL(0x7e50d5052b90)]), partitions(p0),
      is_index_back=false, filter_before_indexback[false,false,false],
      range_key([CV.ID(0x7e50d5106d40)]), range(MIN ; MAX)always true
  5 - output([CP.PRODUCT_ID(0x7e50d50374b0)], [CS.VENDOR_ID(0x7e50d5039ef0)]), filter(nil),
      equal_conds([CS.STORE_ID(0x7e50d50386e0) = CSPR.STORE_ID(0x7e50d50389d0)(0x7e50d5037fc0)]), other_conds(nil)
  6 - output([CP.PRODUCT_ID(0x7e50d50374b0)], [CSPR.STORE_ID(0x7e50d50389d0)]), filter(nil),
      equal_conds([CSPR.PRODUCT_ID(0x7e50d50371c0) = CP.PRODUCT_ID(0x7e50d50374b0)(0x7e50d5036aa0)]), other_conds(nil)
  7 - output([CP.PRODUCT_ID(0x7e50d50374b0)]), filter(nil),
      equal_conds([CASP.PRODUCT_ID(0x7e50d503d140) = CP.PRODUCT_ID(0x7e50d50374b0)(0x7e50d503ca20)]), other_conds(nil)
  8 - output([CASP.PRODUCT_ID(0x7e50d503d140)]), filter(nil),
      conds(nil), nl_params_([CAS.STAGE_ID(0x7e50d503e370)]), batch_join=true
  9 - output([CAS.STAGE_ID(0x7e50d503e370)]), filter(nil),
      access([CAS.STAGE_ID(0x7e50d503e370)]), partitions(p0),
      is_index_back=false,
      range_key([CAS.ACT_ID(0x7e50d503fb80)], [CAS.STAGE_ID(0x7e50d503e370)]), range(CA1202406270729,MIN ; CA1202406270729,MAX),
      range_cond([CAS.ACT_ID(0x7e50d503fb80) = 'CA1202406270729'(0x7e50d5067600)])
  10 - output([CASP.PRODUCT_ID(0x7e50d503d140)]), filter([CASP.PRODUCT_STATUS(0x7e50d5044e50) = '1'(0x7e50d5044730)]),
      access([CASP.PRODUCT_ID(0x7e50d503d140)], [CASP.PRODUCT_STATUS(0x7e50d5044e50)]), partitions(p0),
      is_index_back=true, filter_before_indexback[false],
      range_key([CASP.STAGE_ID(0x7e50d503e660)], [CASP.ID(0x7e50d5114e20)]), range(MIN ; MAX),
      range_cond([? = CASP.STAGE_ID(0x7e50d503e660)(0x7e40bfcaf3d0)])
  11 - output([CP.PRODUCT_ID(0x7e50d50374b0)]), filter([CP.PRODUCT_STATUS(0x7e50d5040c10) = '01'(0x7e50d50404f0)]),
      access([CP.PRODUCT_ID(0x7e50d50374b0)], [CP.PRODUCT_STATUS(0x7e50d5040c10)]), partitions(p0),
      is_index_back=false, filter_before_indexback[false],
      range_key([CP.ID(0x7e50d50d1560)]), range(MIN ; MAX)always true
  12 - output([CSPR.PRODUCT_ID(0x7e50d50371c0)], [CSPR.STORE_ID(0x7e50d50389d0)]), filter(nil),
      access([CSPR.PRODUCT_ID(0x7e50d50371c0)], [CSPR.STORE_ID(0x7e50d50389d0)]), partitions(p0),
      is_index_back=false,
      range_key([CSPR.RELATIONSHIP_STATUS(0x7e50d5041ca0)], [CSPR.STORE_ID(0x7e50d50389d0)], [CSPR.PRODUCT_ID(0x7e50d50371c0)], [CSPR.ID(0x7e50d50e05f0)]), range(01,MIN,MIN,MIN ; 01,MAX,MAX,MAX),
      range_cond([CSPR.RELATIONSHIP_STATUS(0x7e50d5041ca0) = '01'(0x7e50d5041580)])
  13 - output([CS.STORE_ID(0x7e50d50386e0)], [CS.VENDOR_ID(0x7e50d5039ef0)]), filter([CS.STORE_STATUS(0x7e50d5042d30) = '0'(0x7e50d5042610)]),
      access([CS.STORE_ID(0x7e50d50386e0)], [CS.VENDOR_ID(0x7e50d5039ef0)], [CS.STORE_STATUS(0x7e50d5042d30)]), partitions(p0),
      is_index_back=false, filter_before_indexback[false],
      range_key([CS.ID(0x7e50d50eeae0)]), range(MIN ; MAX)always true
  14 - output([remove_const(1)(0x7e40bfe3d100)]), filter(nil)
  15 - output([remove_const(1)(0x7e40bfe3d950)]), filter([CA.DAILY_START_TIME(0x7e50d504f9e0) = ?(0x7e50d504e640)], [CA.ACT_LABEL(0x7e50d50465c0) = '最红星期五'(0x7e50d5045220)], [CA.ACT_TYPE(0x7e50d5049660) = '98'(0x7e50d5048f40)], [CA.ACT_STATUS(0x7e50d504a6f0) = '2'(0x7e50d5049fd0)], [date(cast(CA.ACT_START_TIME(0x7e50d504c450), DATE(-1, -1))(0x7e50d5058090))(0x7e50d504aac0) <= ?(0x7e50d504bd10)], [date(cast(CA.ACT_END_TIME(0x7e50d504e210), DATE(-1, -1))(0x7e50d5058ec0))(0x7e50d504c880) >= ?(0x7e50d504dad0)]),
      access([CA.ACT_LABEL(0x7e50d50465c0)], [CA.ACT_TYPE(0x7e50d5049660)], [CA.ACT_STATUS(0x7e50d504a6f0)], [CA.ACT_START_TIME(0x7e50d504c450)], [CA.ACT_END_TIME(0x7e50d504e210)], [CA.DAILY_START_TIME(0x7e50d504f9e0)]), partitions(p0),
      is_index_back=true, filter_before_indexback[false,false,false,false,false,false],
      range_key([CA.ACT_ID(0x7e50d503f890)], [CA.shadow_pk_0(0x7e50d512f330)]), range(CA1202406270729,MIN ; CA1202406270729,MAX),
      range_cond([CA.ACT_ID(0x7e50d503f890) = 'CA1202406270729'(0x7e50d5053020)])
  
Used Hint:
-------------------------------------
  /*+
  */
  
Outline Data:
-------------------------------------
  /*+
      BEGIN_OUTLINE_DATA
      NO_USE_HASH_AGGREGATION(@"SEL$1")
      LEADING(@"SEL$1" (("nctm_db.CV"@"SEL$1" (((("nctm_db.CAS"@"SEL$1" "nctm_db.CASP"@"SEL$1" )"nctm_db.CP"@"SEL$1" )"nctm_db.CSPR"@"SEL$1" )"nctm_db.CS"@"SEL$1" ))"nctm_db.CA"@"SEL$1" ))
      USE_NL(@"SEL$1" ("nctm_db.CA"@"SEL$1" ))
      PQ_DISTRIBUTE(@"SEL$1" ("nctm_db.CA"@"SEL$1" ) LOCAL LOCAL)
      USE_NL_MATERIALIZATION(@"SEL$1" ("nctm_db.CA"@"SEL$1" ))
      USE_HASH(@"SEL$1" ("nctm_db.CAS"@"SEL$1" "nctm_db.CASP"@"SEL$1" "nctm_db.CP"@"SEL$1" "nctm_db.CSPR"@"SEL$1" "nctm_db.CS"@"SEL$1" ))
      PQ_DISTRIBUTE(@"SEL$1" ("nctm_db.CAS"@"SEL$1" "nctm_db.CASP"@"SEL$1" "nctm_db.CP"@"SEL$1" "nctm_db.CSPR"@"SEL$1" "nctm_db.CS"@"SEL$1" ) LOCAL LOCAL)
      FULL(@"SEL$1" "nctm_db.CV"@"SEL$1")
      USE_HASH(@"SEL$1" ("nctm_db.CS"@"SEL$1" ))
      PQ_DISTRIBUTE(@"SEL$1" ("nctm_db.CS"@"SEL$1" ) LOCAL LOCAL)
      USE_HASH(@"SEL$1" ("nctm_db.CSPR"@"SEL$1" ))
      PQ_DISTRIBUTE(@"SEL$1" ("nctm_db.CSPR"@"SEL$1" ) LOCAL LOCAL)
      USE_HASH(@"SEL$1" ("nctm_db.CP"@"SEL$1" ))
      PQ_DISTRIBUTE(@"SEL$1" ("nctm_db.CP"@"SEL$1" ) LOCAL LOCAL)
      USE_NL(@"SEL$1" ("nctm_db.CASP"@"SEL$1" ))
      PQ_DISTRIBUTE(@"SEL$1" ("nctm_db.CASP"@"SEL$1" ) LOCAL LOCAL)
      NO_USE_NL_MATERIALIZATION(@"SEL$1" ("nctm_db.CASP"@"SEL$1" ))
      INDEX(@"SEL$1" "nctm_db.CAS"@"SEL$1" "IDX_CT_ACT_STAGE_ACT_ID")
      INDEX(@"SEL$1" "nctm_db.CASP"@"SEL$1" "IDX_CT_ACT_STAGE_PRODUCT_STAGE_ID")
      FULL(@"SEL$1" "nctm_db.CP"@"SEL$1")
      INDEX(@"SEL$1" "nctm_db.CSPR"@"SEL$1" "IDX_CT_STORE_PRODUCT_REL_STATUS_STORE_ID_PRODUCT_ID")
      FULL(@"SEL$1" "nctm_db.CS"@"SEL$1")
      INDEX(@"SEL$1" "nctm_db.CA"@"SEL$1" "UK_CT_ACT_ACT_ID")
      END_OUTLINE_DATA
  */
  
Plan Type:
-------------------------------------
LOCAL
  
Optimization Info:
-------------------------------------
  
CV:table_rows:30000, physical_range_rows:30000, logical_range_rows:30000, index_back_rows:0, output_rows:30000, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[UK_CT_VENDOR_VENDOR_ID,ct_vendor], pruned_index_name[IDX_CT_VENDOR_AGREEMENT_ID], estimation info[table_id:1117103813870655, (table_type:1, version:0-1730138410643530-1730138410643530, logical_rc:30000, physical_rc:30000), (table_type:7, version:1730138401530421-1730138401530421-1730138461074462, logical_rc:0, physical_rc:0), (table_type:7, version:1730138461074462-1730143860246234-1730145618523786, logical_rc:0, physical_rc:0), (table_type:5, version:1730138461074462-1730143860246234-1730145618523786, logical_rc:0, physical_rc:0), (table_type:0, version:1730145618523786-1730145618523786-9223372036854775807, logical_rc:0, physical_rc:0)]
  
  
CAS:table_rows:84739, physical_range_rows:181, logical_range_rows:181, index_back_rows:0, output_rows:181, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[IDX_CT_ACT_STAGE_ACT_ID], pruned_index_name[IDX_CT_ACT_STAGE_STAGE_END_DATE,IDX_CT_ACT_STAGE_STAGE_START_DATE], unstable_index_name[ct_act_stage], estimation info[table_id:1117103813870438, (table_type:1, version:0-1730138410643530-1730138410643530, logical_rc:181, physical_rc:181), (table_type:7, version:1730138406788363-1730138406788363-1730138467329247, logical_rc:0, physical_rc:0), (table_type:7, version:1730138467329247-1730143858239559-1730145624176862, logical_rc:0, physical_rc:0), (table_type:5, version:1730138467329247-1730143858239559-1730145624176862, logical_rc:0, physical_rc:0), (table_type:0, version:1730145624176862-1730145624176862-9223372036854775807, logical_rc:0, physical_rc:0)]
  
CASP:table_rows:5310126, physical_range_rows:65, logical_range_rows:65, index_back_rows:65, output_rows:32, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[IDX_CT_ACT_STAGE_PRODUCT_STAGE_ID,IDX_CT_ACT_STAGE_PRODUCT_PRODUCT_STATUS,IDX_CT_ACT_STAGE_PRODUCT_PRODUCT_ID], unstable_index_name[ct_act_stage_product], estimation info[table_id:1117103813870440, (table_type:1, version:0-1730138410643530-1730138410643530, logical_rc:5310126, physical_rc:5310126), (table_type:7, version:1730138401532537-1730138401532537-1730138461281729, logical_rc:0, physical_rc:0), (table_type:7, version:1730138461281729-1730143858239559-1730145618525899, logical_rc:0, physical_rc:0), (table_type:5, version:1730138461281729-1730143858239559-1730145618525899, logical_rc:0, physical_rc:0), (table_type:0, version:1730145618525899-1730145618525899-9223372036854775807, logical_rc:0, physical_rc:0)]
  
CP:table_rows:44943, physical_range_rows:44943, logical_range_rows:44943, index_back_rows:0, output_rows:22471, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[UK_CT_PRODUCT_PRODUCT_ID,IDX_CT_PRODUCT_PRODUCT_STATUS,ct_product], pruned_index_name[IDX_CT_PRODUCT_PRODUCT_KIND,IDX_CT_PRODUCT_PRODUCT_TYPE,IDX_CT_PRODUCT_PAYMENT_METHOD,IDX_CT_PRODUCT_OFF_DATE_ON_DATE_PRODUCT_ID], estimation info[table_id:1117103813870552, (table_type:1, version:0-1730138410643530-1730138410643530, logical_rc:44943, physical_rc:44943), (table_type:7, version:1730138402130251-1730138402130251-1730138461891023, logical_rc:0, physical_rc:0), (table_type:7, version:1730138461891023-1730143858239559-1730145619124485, logical_rc:0, physical_rc:0), (table_type:5, version:1730138461891023-1730143858239559-1730145619124485, logical_rc:0, physical_rc:0), (table_type:0, version:1730145619124485-1730145619124485-9223372036854775807, logical_rc:0, physical_rc:0)]
  
CSPR:table_rows:734949, physical_range_rows:512294, logical_range_rows:512294, index_back_rows:0, output_rows:512294, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[IDX_CT_STORE_PRODUCT_REL_PRODUCT_ID,IDX_CT_STORE_PRODUCT_REL_STATUS_STORE_ID_PRODUCT_ID,IDX_CT_STORE_PRODUCT_REL_STORE_ID], pruned_index_name[IDX_CT_STORE_PRODUCT_REL_AGREEMENT_ID,IDX_CT_STORE_PRODUCT_REL_ADD_UPD_STATUS,IDX_CT_STORE_PRODUCT_REL_RELATIONSHIP_STATUS,ct_store_product_rel], estimation info[table_id:1117103813870636, (table_type:1, version:0-1730138410643530-1730138410643530, logical_rc:512294, physical_rc:512294), (table_type:7, version:1730138403757484-1730138403757484-1730138463696067, logical_rc:0, physical_rc:0), (table_type:7, version:1730138463696067-1730143858239559-1730145620760470, logical_rc:0, physical_rc:0), (table_type:5, version:1730138463696067-1730143858239559-1730145620760470, logical_rc:0, physical_rc:0), (table_type:0, version:1730145620760470-1730145620760470-9223372036854775807, logical_rc:0, physical_rc:0)]
  
CS:table_rows:558500, physical_range_rows:558500, logical_range_rows:558500, index_back_rows:0, output_rows:186166, est_method:local_storage, optimization_method=cost_based, avaiable_index_name[UK_CT_STORE_STORE_ID,IDX_CT_STORE_STORE_STATUS,IDX_CT_STORE_VENDOR_ID_CITY_ID,ct_store], pruned_index_name[IDX_CT_STORE_BIZ_DISTRICT_ID,IDX_CT_STORE_CUISINE_ID,IDX_CT_STORE_CITY_ID,IDX_CT_STORE_DISTRICT_ID,IDX_CT_STORE_CITY_ID_STORE_STATUS,IDX_CT_STORE_DISTRICT_ID_STORE_STATUS], estimation info[table_id:1117103813870611, (table_type:1, version:0-1730138410643530-1730138410643530, logical_rc:558500, physical_rc:558500), (table_type:7, version:1730138403163616-1730138403163616-1730138463292202, logical_rc:0, physical_rc:0), (table_type:7, version:1730138463292202-1730143858239559-1730145620355046, logical_rc:0, physical_rc:0), (table_type:5, version:1730138463292202-1730143858239559-1730145620355046, logical_rc:0, physical_rc:0), (table_type:0, version:1730145620355046-1730145620355046-9223372036854775807, logical_rc:0, physical_rc:0)]
  
  
CA:table_rows:1302, physical_range_rows:1, logical_range_rows:1, index_back_rows:1, output_rows:0, est_method:local_storage, optimization_method=rule_based, heuristic_rule=unique_index_with_indexback
  
Parameters
-------------------------------------
{obj:{"DATE":"2024-10-29"}, accuracy:{length:-1, precision:-1, scale:0}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:"DATE", collation:"binary", coercibility:"NUMERIC"}}, {obj:{"DATE":"2024-10-29"}, accuracy:{length:-1, precision:-1, scale:0}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:"DATE", collation:"binary", coercibility:"NUMERIC"}}, {obj:{"TIME":"00:00:00"}, accuracy:{length:-1, precision:-1, scale:-1}, flag:0, raw_text_pos:-1, raw_text_len:-1, param_meta:{type:"TIME", collation:"binary", coercibility:"NUMERIC"}}
1 row in set (0.10 sec)
  
ERROR: No query specified

1.3 SQL执行画像

MySQL [oceanbase]> SELECT op_id, op, output_rows, rescan, threads,
    ->        close_time - open_time AS open_dt,
    ->        last_row_eof_time - first_row_time AS row_dt,
    ->        open_time, close_time, first_row_time, last_row_eof_time
    -> FROM (
    ->         SELECT plan_line_id AS op_id,
    ->                concat(lpad(' ', max(plan_depth), ' '), plan_operation) AS op,
    ->                sum(output_rows) AS output_rows,
    ->                sum(STARTS) AS rescan,
    ->                min(first_refresh_time) AS open_time,
    ->                max(last_refresh_time) AS close_time,
    ->                min(first_change_time) AS first_row_time,
    ->                max(last_change_time) AS last_row_eof_time,
    ->                count(1) AS threads
    ->         FROM gv$sql_plan_monitor
    ->         WHERE trace_id = 'YB4235010383-0006185DE18DD093-0-0'
    ->         GROUP BY plan_line_id, plan_operation, plan_depth
    ->         ORDER BY plan_line_id
    -> ) a;
+-------+-----------------------------+-------------+---------+---------+-----------+-----------+----------------------------+----------------------------+----------------------------+----------------------------+
| op_id | op                          | output_rows | rescan  | threads | open_dt   | row_dt    | open_time                  | close_time                 | first_row_time             | last_row_eof_time          |
+-------+-----------------------------+-------------+---------+---------+-----------+-----------+----------------------------+----------------------------+----------------------------+----------------------------+
|     0 | PHY_MERGE_DISTINCT          |       30000 |       0 |       1 | 85.193829 |  0.993423 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:19:24.496837 | 2024-10-29 11:19:25.490260 |
|     1 |  PHY_SORT                   |     5310000 |       0 |       1 | 85.193829 |  0.993423 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:19:24.496837 | 2024-10-29 11:19:25.490260 |
|     2 |   PHY_NESTED_LOOP_JOIN      |     5310000 |       0 |       1 | 85.193829 |  7.310275 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:19:03.751663 | 2024-10-29 11:19:11.061938 |
|     3 |    PHY_HASH_JOIN            |     5310000 |       0 |       1 | 85.193829 |  7.310275 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:19:03.751663 | 2024-10-29 11:19:11.061938 |
|     4 |     PHY_TABLE_SCAN          |       30000 |       0 |       1 | 85.193829 |  0.030542 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:18:40.326973 |
|     5 |     PHY_HASH_JOIN           |     5310000 |       0 |       1 | 85.193829 |  7.313438 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:19:03.748500 | 2024-10-29 11:19:11.061938 |
|     6 |      PHY_HASH_JOIN          |     5310000 |       0 |       1 | 85.193829 |  2.506844 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:19:00.115858 | 2024-10-29 11:19:02.622702 |
|     7 |       PHY_HASH_JOIN         |     5310000 |       0 |       1 | 85.193829 |  3.166893 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:18:55.855525 | 2024-10-29 11:18:59.022418 |
|     8 |        PHY_NESTED_LOOP_JOIN |     5310000 |       0 |       1 | 85.193829 | 14.865460 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:18:40.329047 | 2024-10-29 11:18:55.194507 |
|     9 |         PHY_TABLE_SCAN      |         181 |       0 |       1 | 85.193829 |  0.001058 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:18:40.326973 | 2024-10-29 11:18:40.328031 |
|    10 |         PHY_TABLE_SCAN      |     5310000 |     181 |       1 | 85.193829 | 14.865460 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:18:40.329047 | 2024-10-29 11:18:55.194507 |
|    11 |        PHY_TABLE_SCAN       |       32069 |       0 |       1 | 85.193829 |  0.025270 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:18:55.195562 | 2024-10-29 11:18:55.220832 |
|    12 |       PHY_TABLE_SCAN        |      546047 |       0 |       1 | 85.193829 |  0.471327 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:18:59.023438 | 2024-10-29 11:18:59.494765 |
|    13 |      PHY_TABLE_SCAN         |      472935 |       0 |       1 | 85.193829 |  0.503690 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:19:02.623759 | 2024-10-29 11:19:03.127449 |
|    14 |    PHY_MATERIAL             |     5310000 | 5310000 |       1 | 85.193829 |  7.301836 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:19:03.751663 | 2024-10-29 11:19:11.053499 |
|    15 |     PHY_TABLE_SCAN          |           1 |       0 |       1 | 85.193829 |  0.000000 | 2024-10-29 11:18:40.296431 | 2024-10-29 11:19:25.490260 | 2024-10-29 11:19:03.751663 | 2024-10-29 11:19:03.751663 |
+-------+-----------------------------+-------------+---------+---------+-----------+-----------+----------------------------+----------------------------+----------------------------+----------------------------+
16 rows in set (0.47 sec)

1.4 结论

（1）查看执行画像

row_dt(算子吐出首行所需时间)较高时是出现在8行，10行。

第8行：CAS表和CASP表关联

第10行：CASP表

综上所述：SQL主要的消耗都在CASP表上。

（2）CASP表使用USE_HASH关联

检查Outline Data:

USE_NL(@"SEL$1" ("nctm_db.CASP"@"SEL$1" ))

检查发现，CASP表使用的是USE_NL关联，故尝试使用USE_HASH(@"SEL$1" ("nctm_db.CASP"@"SEL$1" )) 。

SQL耗时提升有限

-- 表数据量
select count(*) from CT_PRODUCT; CP 44943
select count(*) from CT_STORE_PRODUCT_REL; CSPR 734949
select count(*) from CT_STORE; CS 558500
select count(*) from CT_VENDOR; CV 30000
select count(*) from CT_ACT_STAGE_PRODUCT; CASP 5310126
select count(*) from CT_ACT_STAGE; CAS 84739
select count(*) from CT_ACT; CA 1302

（3）检查CASP表过滤条件

select count(distinct(PRODUCT_STATUS)) from CASP;

因为大表上没有有效的过滤条件，需要查全部530w数据再与其他表做左外连接，预期内此sql就会很慢。

此表的NDV是2，数据倾斜十分严重，生成了错误的NLJ计划，导致耗时很高。

故改为hash关联意义不大，符合预期。

2、总结

2.1 执行画像统计表的性能瓶颈

row_dt(算子吐出首行所需时间)

结合执行计划中的Outputs & filters:找到消耗较大的表

2.2：执行计划中的Outline Data

USE_NL USE_HASH LEADING() INDEX()

• 大表且有有效的过滤条件，可以使用HASH关联;
• 小表使用USE_NL即可;
• LEADING()：驱动表
• INDEX()使用的索引

2.3 如何快速获得表中某列的NDV值

SELECT APPROX_COUNT_DISTINCT(col2) FROM tbl1;

• 通过函数approx_count_distinct对参数列去重后的行数进行计算，返回行数值，且该值是近似值，该函数可以用于进一步计算被引用的列的选择性。

• 与函数COUNT(DISTINCT x)相比，APPROX_COUNT_DISTINCT返回的是近似值，所以计算速度极快。

• 在处理大量级的数据时COUNT(DISTINCT x)经常要花费很长的时间，使用APPROX_COUNT_DISTINCT牺牲了少量的精确度，却换来了计算效率的极大提升。

想获取更多实用干货，关注微信公众号【雅俗数据库】。