px 线程争用造成的 insert rt 高问题分析

INSERT
    /*+ opt_param('enable_pdml_insert_up', 'true') */
    INTO `all_car_biz_datas`(
        gmt_create,
        stat_time,
        mini_program_type,
        adcode,
        gd_service_id,
        cp_code,
        cp_type,
        type,
        price_mode,
        amap_ride_type,
        business_model,
        time_range_type,
        is_atom_data,
        cancel_before_reply_count,
        cube_hash
    )
select
    gmt_create,
    stat_time,
    mini_program_type,
    adcode,
    gd_service_id,
    cp_code,
    cp_type,
    type,
    price_mode,
    amap_ride_type,
    business_model,
    3 as time_range_type,
    case
        when cp_code is not null
        and adcode is not null
        and amap_ride_type is not null
        and gd_service_id is not null
        and mini_program_type is not null
        and type is not null
        and business_model is not null
        and price_mode is not null
        and cp_type is not null then 1
        else 0
    end as is_atom_data,
    cancel_before_reply_count,
    CONCAT(
        IFNULL(cp_code, 'ALL'),
        ';',
        IFNULL(adcode, 'ALL'),
        ';',
        IFNULL(amap_ride_type, 'ALL'),
        ';',
        IFNULL(gd_service_id, 'ALL'),
        ';',
        IFNULL(mini_program_type, 'ALL'),
        ';',
        IFNULL(type, 'ALL'),
        ';',
        IFNULL(business_model, 'ALL'),
        ';',
        IFNULL(price_mode, 'ALL'),
        ';',
        IFNULL(cp_type, 'ALL')
    ) as `cube_hash`
from
    (
        select
            /*+materialize*/
            now() as gmt_create,
            '2025-12-09 09:00:00' as stat_time,
            mini_program_type,
            adcode,
            gd_service_id,
            cp_code,
            cp_type,
            type,
            price_mode,
            amap_ride_type,
            business_model,
            count(distinct(amap_order_id)) as cancel_before_reply_count
        from
(
                select
                    dw_t.amap_order_id as amap_order_id,
                    gd_service_id,
                    mini_program_type,
                    adcode,
                    amap_ride_type,
                    business_model,
                    cp_code,
                    cp_type,
                    type,
                    price_mode
                from
                    (
                        select
                            `amap_order_id`,
                            `gd_service_id`,
                            case
                                when mini_program_type not in (
                                    0,
                                    1,
                                    2,
                                    5,
                                    14,
                                    15,
                                    71,
                                    93,
                                    99,
                                    105,
                                    106,
                                    111,
                                    112,
                                    121,
                                    124,
                                    168,
                                    175,
                                    176,
                                    177
                                ) then 10000
                                else mini_program_type
                            end as mini_program_type,
                            CASE
                                WHEN length(`adcode`) = 9 then substr(`adcode`, 1, 3)
                                WHEN `adcode` LIKE '11%' THEN '11'
                                WHEN `adcode` LIKE '31%' THEN '31'
                                WHEN `adcode` LIKE '12%' THEN '12'
                                WHEN `adcode` LIKE '50%' THEN '50'
                                ELSE substr(`adcode`, 1, 4)
                            END AS `adcode`
                        from
                            `car_order_dw`
                        WHERE
                            `status_create` >= '2025-12-09 09:00:00'
                            and `status_create` = '2025-12-09 09:00:00' - interval 30 minute
                            and gmt_create < '2025-12-09 09:00:00'
                    ) dw_t
            ) t
        group by
            mini_program_type,
            adcode,
            gd_service_id,
            cp_code,
            cp_type,
            type,
            price_mode,
            amap_ride_type,
            business_model
    ) t
ON DUPLICATE KEY UPDATE
    cancel_before_reply_count = VALUES(cancel_before_reply_count),
    cube_hash = VALUES(cube_hash),
    is_atom_data = VALUES(is_atom_data);