Oracle19c Exadata一体机
SQL> select banner_full from v$version;
BANNER_FULL
--------------------------------------------------------------------------------
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.27.0.0.0SQL语句
WITH a AS
(SELECT /*+ index(h idx_ef_ap_fee_header_pc) */ h.customer_code owner_code,
cu.ebcu_name_cn,
cu.original_group_code,
cu.is_group_customer,
su.suppliers_code,
su.suppliers_name_cn,
SUM(wf.quantity) cu_area,
efor.operating_unit_id,
efor.operating_unit_name
FROM bms.ef_ap_fee_detail wf,
bms.ef_ap_fee_header h,
lmdm.eb_customer cu,
lmdm.eb_supplier su,
lmdm.ef_organ_relation efor
WHERE wf.head_pm_code=h.pm_code
AND h.CUSTOMER_CODE = cu.pm_code
AND su.suppliers_code = h.supplier_code
AND efor.operating_unit_id = h.bursar_body
AND wf.fee_code = 'CFZ001'
AND su.suppliers_type = '100'
AND substr(su.suppliers_code_group,-6,6) = efor.company_code
AND wf.fee_date >= TO_DATE ('20250601', 'yyyymmdd')
AND wf.fee_date <= TO_DATE ('20250630', 'yyyymmdd')
AND h.bursar_body = to_number('945')
GROUP BY h.CUSTOMER_CODE,
cu.ebcu_name_cn,
cu.original_group_code,
cu.is_group_customer,
su.suppliers_code,
su.suppliers_name_cn,
efor.operating_unit_id,
efor.operating_unit_name)
SELECT TO_DATE (SUBSTR ('20250601', 0, 6), 'yyyy-mm') AS fee_month,
a.operating_unit_id, a.operating_unit_name, a.suppliers_code,
a.suppliers_name_cn, a.owner_code, a.ebcu_name_cn,
a.original_group_code, a.is_group_customer, a.cu_area, b.su_area
FROM a a
LEFT JOIN
(SELECT suppliers_name_cn, SUM (cu_area) su_area
FROM a
GROUP BY suppliers_name_cn) b
ON a.suppliers_name_cn = b.suppliers_name_cn;执行计划
Plan hash value: 348546137
-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 69275 | 117M| | 166K (1)| 00:00:07 | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D8AEC_3DD52C | | | | | | | |
| 3 | HASH GROUP BY | | 69275 | 17M| 18M| 158K (1)| 00:00:07 | | |
|* 4 | HASH JOIN | | 69275 | 17M| 14M| 155K (1)| 00:00:07 | | |
| 5 | JOIN FILTER CREATE | :BF0000 | 69350 | 13M| | 146K (1)| 00:00:06 | | |
|* 6 | HASH JOIN | | 69350 | 13M| 4472K| 146K (1)| 00:00:06 | | |
| 7 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| EF_AP_FEE_DETAIL | 69350 | 3657K| | 118K (1)| 00:00:05 | ROWID | ROWID |
|* 8 | INDEX SKIP SCAN | IDX_EF_AP_FEE_DETAIL_FD_FC | 69353 | | | 103K (1)| 00:00:05 | | |
| 9 | VIEW | VW_GBF_32 | 72429 | 10M| | 27457 (1)| 00:00:02 | | |
| 10 | HASH GROUP BY | | 72429 | 12M| 13M| 27457 (1)| 00:00:02 | | |
| 11 | NESTED LOOPS | | 72429 | 12M| | 24864 (1)| 00:00:01 | | |
| 12 | NESTED LOOPS | | 73416 | 12M| | 24864 (1)| 00:00:01 | | |
| 13 | NESTED LOOPS | | 138 | 15594 | | 6685 (1)| 00:00:01 | | |
| 14 | TABLE ACCESS BY INDEX ROWID BATCHED | EF_ORGAN_RELATION | 1 | 65 | | 2 (0)| 00:00:01 | | |
|* 15 | INDEX RANGE SCAN | IDX$$_1147BA0005 | 1 | | | 1 (0)| 00:00:01 | | |
|* 16 | TABLE ACCESS BY INDEX ROWID BATCHED | EB_SUPPLIER | 138 | 6624 | | 6683 (1)| 00:00:01 | | |
|* 17 | INDEX RANGE SCAN | IND_SUPPLIERS_TYPE | 99652 | | | 408 (0)| 00:00:01 | | |
|* 18 | INDEX RANGE SCAN | IDX_EAFH_SC_SC_BB | 532 | | | 107 (0)| 00:00:01 | | |
| 19 | TABLE ACCESS BY GLOBAL INDEX ROWID | EF_AP_FEE_HEADER | 526 | 32612 | | 310 (0)| 00:00:01 | ROWID | ROWID |
| 20 | JOIN FILTER USE | :BF0000 | 368K| 20M| | 6624 (1)| 00:00:01 | | |
|* 21 | TABLE ACCESS STORAGE FULL | EB_CUSTOMER | 368K| 20M| | 6624 (1)| 00:00:01 | | |
|* 22 | HASH JOIN RIGHT OUTER | | 69275 | 117M| 35M| 7245 (1)| 00:00:01 | | |
| 23 | VIEW | | 69275 | 34M| | 1270 (1)| 00:00:01 | | |
| 24 | HASH GROUP BY | | 69275 | 2367K| 3008K| 1270 (1)| 00:00:01 | | |
| 25 | VIEW | | 69275 | 2367K| | 670 (1)| 00:00:01 | | |
| 26 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D8AEC_3DD52C | 69275 | 10M| | 670 (1)| 00:00:01 | | |
| 27 | VIEW | | 69275 | 83M| | 670 (1)| 00:00:01 | | |
| 28 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D8AEC_3DD52C | 69275 | 10M| | 670 (1)| 00:00:01 | | |
-------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("ITEM_1"="CU"."PM_CODE")
6 - access("WF"."HEAD_PM_CODE"="ITEM_2")
8 - access("WF"."FEE_DATE">=TO_DATE(' 2025-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "WF"."FEE_CODE"='CFZ001' AND
"WF"."FEE_DATE"<=TO_DATE(' 2025-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("WF"."FEE_CODE"='CFZ001')
15 - access("EFOR"."OPERATING_UNIT_ID"=945)
16 - filter("EFOR"."COMPANY_CODE"=SUBSTR("SU"."SUPPLIERS_CODE_GROUP",(-6),6))
17 - access("SU"."SUPPLIERS_TYPE"=100)
18 - access("SU"."SUPPLIERS_CODE"="H"."SUPPLIER_CODE")
filter("EFOR"."OPERATING_UNIT_ID"=TO_NUMBER("H"."BURSAR_BODY") AND TO_NUMBER("H"."BURSAR_BODY")=945)
21 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"CU"."PM_CODE"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"CU"."PM_CODE"))
22 - access("A"."SUPPLIERS_NAME_CN"="B"."SUPPLIERS_NAME_CN"(+))
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
18 - SEL$93AF3180 / H@SEL$4
U - index(h idx_ef_ap_fee_header_pc)
加了HINT/*+ index(h idx_ef_ap_fee_header_pc) */，但是h走的是IDX_EAFH_SC_SC_BB
在Oracle中，加了HINT，但是不走，要么HINT写错了，要么是Oracle优化器某些特性把HINT忽略了。秒了一眼执行计划，发现有2个HASH GROUP BY，原始SQL只有一个GROUP BY
哦，明白了，group_by_placement 这个优化器特性导致HINT不生效
现在关闭group_by_placement
WITH a AS
(SELECT /*+ index(h idx_ef_ap_fee_header_pc) opt_param('_optimizer_group_by_placement', 'false') */ h.customer_code owner_code,
cu.ebcu_name_cn,
cu.original_group_code,
cu.is_group_customer,
su.suppliers_code,
su.suppliers_name_cn,
SUM(wf.quantity) cu_area,
efor.operating_unit_id,
efor.operating_unit_name
FROM bms.ef_ap_fee_detail wf,
bms.ef_ap_fee_header h,
lmdm.eb_customer cu,
lmdm.eb_supplier su,
lmdm.ef_organ_relation efor
WHERE wf.head_pm_code=h.pm_code
AND h.CUSTOMER_CODE = cu.pm_code
AND su.suppliers_code = h.supplier_code
AND efor.operating_unit_id = h.bursar_body
AND wf.fee_code = 'CFZ001'
AND su.suppliers_type = '100'
AND substr(su.suppliers_code_group,-6,6) = efor.company_code
AND wf.fee_date >= TO_DATE ('20250601', 'yyyymmdd')
AND wf.fee_date <= TO_DATE ('20250630', 'yyyymmdd')
AND h.bursar_body = to_number('945')
GROUP BY h.CUSTOMER_CODE,
cu.ebcu_name_cn,
cu.original_group_code,
cu.is_group_customer,
su.suppliers_code,
su.suppliers_name_cn,
efor.operating_unit_id,
efor.operating_unit_name)
SELECT TO_DATE (SUBSTR ('20250601', 0, 6), 'yyyy-mm') AS fee_month,
a.operating_unit_id, a.operating_unit_name, a.suppliers_code,
a.suppliers_name_cn, a.owner_code, a.ebcu_name_cn,
a.original_group_code, a.is_group_customer, a.cu_area, b.su_area
FROM a a
LEFT JOIN
(SELECT suppliers_name_cn, SUM (cu_area) su_area
FROM a
GROUP BY suppliers_name_cn) b
ON a.suppliers_name_cn = b.suppliers_name_cn;执行计划
Plan hash value: 2148346612
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 56 | 97K| | 334K (1)| 00:00:14 | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 2 | LOAD AS SELECT (CURSOR DURATION MEMORY) | SYS_TEMP_0FD9D8AEB_3DD52C | | | | | | | |
| 3 | HASH GROUP BY | | 56 | 16072 | | 334K (1)| 00:00:14 | | |
| 4 | NESTED LOOPS | | 56 | 16072 | | 334K (1)| 00:00:14 | | |
| 5 | NESTED LOOPS | | 56 | 16072 | | 334K (1)| 00:00:14 | | |
|* 6 | HASH JOIN | | 56 | 12824 | | 333K (1)| 00:00:14 | | |
| 7 | TABLE ACCESS BY INDEX ROWID BATCHED | EF_ORGAN_RELATION | 1 | 65 | | 2 (0)| 00:00:01 | | |
|* 8 | INDEX RANGE SCAN | IDX$$_1147BA0005 | 1 | | | 1 (0)| 00:00:01 | | |
|* 9 | HASH JOIN | | 40798 | 6534K| 5840K| 333K (1)| 00:00:14 | | |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED | EB_SUPPLIER | 99652 | 4671K| | 6682 (1)| 00:00:01 | | |
|* 11 | INDEX RANGE SCAN | IND_SUPPLIERS_TYPE | 99652 | | | 409 (0)| 00:00:01 | | |
| 12 | NESTED LOOPS | | 69350 | 7856K| | 326K (1)| 00:00:13 | | |
| 13 | NESTED LOOPS | | 69350 | 7856K| | 326K (1)| 00:00:13 | | |
| 14 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| EF_AP_FEE_DETAIL | 69350 | 3657K| | 118K (1)| 00:00:05 | ROWID | ROWID |
|* 15 | INDEX SKIP SCAN | IDX_EF_AP_FEE_DETAIL_FD_FC | 69353 | | | 103K (1)| 00:00:05 | | |
|* 16 | INDEX UNIQUE SCAN | IDX_EF_AP_FEE_HEADER_PC | 1 | | | 2 (0)| 00:00:01 | | |
|* 17 | TABLE ACCESS BY GLOBAL INDEX ROWID | EF_AP_FEE_HEADER | 1 | 62 | | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 18 | INDEX UNIQUE SCAN | UNQ_EBCU_PM_CODE | 1 | | | 1 (0)| 00:00:01 | | |
| 19 | TABLE ACCESS BY INDEX ROWID | EB_CUSTOMER | 1 | 58 | | 2 (0)| 00:00:01 | | |
|* 20 | HASH JOIN RIGHT OUTER | | 56 | 97K| | 7 (15)| 00:00:01 | | |
| 21 | VIEW | | 56 | 29512 | | 4 (25)| 00:00:01 | | |
| 22 | HASH GROUP BY | | 56 | 1960 | | 4 (25)| 00:00:01 | | |
| 23 | VIEW | | 56 | 1960 | | 3 (0)| 00:00:01 | | |
| 24 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D8AEB_3DD52C | 56 | 8960 | | 3 (0)| 00:00:01 | | |
| 25 | VIEW | | 56 | 70504 | | 3 (0)| 00:00:01 | | |
| 26 | TABLE ACCESS STORAGE FULL | SYS_TEMP_0FD9D8AEB_3DD52C | 56 | 8960 | | 3 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access("EFOR"."OPERATING_UNIT_ID"=TO_NUMBER("H"."BURSAR_BODY") AND "EFOR"."COMPANY_CODE"=SUBSTR("SU"."SUPPLIERS_CODE_GROUP",(-6),6))
8 - access("EFOR"."OPERATING_UNIT_ID"=945)
9 - access("SU"."SUPPLIERS_CODE"="H"."SUPPLIER_CODE")
11 - access("SU"."SUPPLIERS_TYPE"=100)
15 - access("WF"."FEE_DATE">=TO_DATE(' 2025-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "WF"."FEE_CODE"='CFZ001' AND
"WF"."FEE_DATE"<=TO_DATE(' 2025-06-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter("WF"."FEE_CODE"='CFZ001')
16 - access("WF"."HEAD_PM_CODE"="H"."PM_CODE")
17 - filter(TO_NUMBER("H"."BURSAR_BODY")=945)
18 - access("H"."CUSTOMER_CODE"="CU"."PM_CODE")
20 - access("A"."SUPPLIERS_NAME_CN"="B"."SUPPLIERS_NAME_CN"(+))
本文不讨论SQL优化，只讨论HINT不生效，INDEX SKIP SCAN有问题，这里我们不管它
如果你遇到了HINT不生效，先检查HINT写对了没，位置放对了没，如果确定HINT没问题
1.试一试优化器降级，比如降级到11.2.0.4，11.2.0.1看HINT能否生效，如果能生效，大概率遇到了优化器某个特性阻止HINT生效
2.select * from table(dbms_xplan.display(NULL, NULL, 'advanced -projection')); 看Outline Data
3.10053