各位，最近我参与了一项Oracle SQL优化任务，我认为我遇到了一个非常困难的问题，我甚至可以说我被这个问题吓到了，我从DBA那里得到了AWR报告，AMR中的红线SQL似乎需要做一些调整（我已经把这些SQL粘贴到下面了，这个SQL是在SP里面的），但是不知道是什么原因造成的性能不好，任何人都可以帮助提供一些解决方案或想法调优SQL？
如果你需要更多的证据，请告诉我。
先谢了...
UPDATE tax_ratio tar
SET
( ecm,
esm,
epm,
ecam,
update_dt,
update_by ) = (
SELECT
nvl(src_t1.ecm,0) AS ecm,
nvl(src_t1.esm,0) AS esm,
nvl(src_t1.epm,0) AS epm,
nvl(src_t1.ecam,0) AS ecam,
SYSDATE,
'ffee_user'
FROM
(
SELECT
city_code,
tax_type,
company_type,
taxpayer,
company_group,
company_tax_type,
SUM(new_tax_current_mth) /12 AS ecm,
SUM(new_tax_miss_current_mth) /12 AS esm,
SUM(new_tax_get_current_mth) /12 AS epm,
SUM(new_tax_special_current_mth) /12 AS ecam
FROM
tax_ratio
WHERE
city_code ='001'
AND company_type ='typ_01'
AND tax_mth <= add_months(TO_DATE('08-JUL-2015'),-3)
AND tax_mth >= add_months(TO_DATE('08-JUL-2015'),-14)
AND eff_date =TO_DATE('08-JUL-2015')
AND tax_type = '00'
GROUP BY
city_code,
tax_type,
company_type,
taxpayer,
company_group,
company_tax_type
HAVING SUM(new_tax_current_mth) <> 0
OR SUM(new_tax_miss_current_mth) <> 0
OR SUM(new_tax_get_current_mth) <> 0
OR SUM(new_tax_special_current_mth) <> 0
) src_t1
WHERE
tar.city_code = src_t1.city_code
AND tar.tax_type = src_t1.tax_type
AND tar.company_type = src_t1.company_type
AND tar.taxpayer = src_t1.taxpayer
AND nvl(tar.company_group,'-99999') = nvl(src_t1.company_group,'-99999')
AND (
src_t1.ecm IS NOT NULL
OR src_t1.esm IS NOT NULL
OR src_t1.epm IS NOT NULL
OR src_t1.ecam IS NOT NULL
)
AND tar.tax_mth =TO_DATE('08-JUL-2015')
AND tar.company_tax_type = src_t1.company_tax_type
)
WHERE
tar.city_code ='001'
AND tar.company_type ='typ_01'
AND tar.tax_mth =TO_DATE('08-JUL-2015')
AND EXISTS (
SELECT
1
FROM
(
SELECT
city_code,
tax_type,
company_type,
taxpayer,
company_group,
company_tax_type,
SUM(new_tax_current_mth) /12 AS ecm,
SUM(new_tax_miss_current_mth) /12 AS esm,
SUM(new_tax_get_current_mth) /12 AS epm,
SUM(new_tax_special_current_mth) /12 AS ecam
FROM
tax_ratio
WHERE
city_code ='001'
AND company_type ='typ_01'
AND tax_mth <= add_months(TO_DATE('08-JUL-2015'),-3)
AND tax_mth >= add_months(TO_DATE('08-JUL-2015'),-14)
AND eff_date =TO_DATE('08-Aug-2015')
AND tax_type = '00'
GROUP BY
city_code,
tax_type,
company_type,
taxpayer,
company_group,
company_tax_type
HAVING SUM(new_tax_current_mth) <> 0
OR SUM(new_tax_miss_current_mth) <> 0
OR SUM(new_tax_get_current_mth) <> 0
OR SUM(new_tax_special_current_mth) <> 0
) src_t1
WHERE
tar.city_code = src_t1.city_code
AND tar.tax_type = src_t1.tax_type
AND tar.company_type = src_t1.company_type
AND tar.taxpayer = src_t1.taxpayer
AND nvl(tar.company_group,'-99999') = nvl(src_t1.company_group,'-99999')
AND (
src_t1.ecm IS NOT NULL
OR src_t1.esm IS NOT NULL
OR src_t1.epm IS NOT NULL
OR src_t1.ecam IS NOT NULL
)
AND tar.tax_mth =TO_DATE('08-JUL-2015')
AND tar.company_tax_type = src_t1.company_tax_type
)
add the EXPLAIN PLAN
PLAN HASH VALUE: 3650439649
----------------------------------------------------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES |TEMPSPC| COST (%CPU)| TIME |
----------------------------------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 1698 | 179K| | 6169K (1)| 00:08:02 |
| 1 | UPDATE | TAX_RATIO | | | | | |
|* 2 | HASH JOIN RIGHT SEMI | | 1698 | 179K| | 732K (1)| 00:00:58 |
| 3 | VIEW | | 39251 | 1111K| | 371K (2)| 00:00:29 |
|* 4 | FILTER | | | | | | |
| 5 | SORT GROUP BY | | 39251 | 2414K| 100M| 371K (2)| 00:00:29 |
|* 6 | TABLE ACCESS FULL | TAX_RATIO | 1140K| 68M| | 365K (2)| 00:00:29 |
|* 7 | TABLE ACCESS FULL | TAX_RATIO | 207K| 15M| | 361K (1)| 00:00:29 |
| 8 | VIEW | | 1 | 81 | | 484 (1)| 00:00:01 |
|* 9 | FILTER | | | | | | |
| 10 | SORT GROUP BY | | 1 | 63 | | 484 (1)| 00:00:01 |
|* 11 | FILTER | | | | | | |
|* 12 | TABLE ACCESS BY INDEX ROWID BATCHED| TAX_RATIO | 1 | 63 | | 483 (0)| 00:00:01 |
|* 13 | INDEX RANGE SCAN | TAX_RATIO_TAXPAYER_IDX | 544 | | | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
2 - ACCESS("TAR"."CITY_CODE"="SRC_T1"."CITY_CODE" AND "TAR"."TAX_TYPE"="SRC_T1"."TAX_TYPE" AND
"TAR"."COMPANY_TYPE"="SRC_T1"."COMPANY_TYPE" AND "TAR"."TAXPAYER"="SRC_T1"."TAXPAYER" AND
NVL("TAR"."COMPANY_GROUP",'-99999')=NVL("SRC_T1"."COMPANY_GROUP",'-99999') AND "TAR"."COMPANY_TAX_TYPE"="SRC_T1"."COMPANY_TAX_TYPE")
4 - FILTER((SUM("NEW_TAX_CURRENT_MTH")<>0 OR SUM("NEW_TAX_MISS_CURRENT_MTH")<>0 OR SUM("NEW_TAX_GET_CURRENT_MTH")<>0 OR SUM("NEW_TAX_SPECIAL_CURRENT_MTH")<>0) AND
(SUM("NEW_TAX_CURRENT_MTH")/12 IS NOT NULL OR SUM("NEW_TAX_MISS_CURRENT_MTH")/12 IS NOT NULL OR SUM("NEW_TAX_GET_CURRENT_MTH")/12 IS NOT NULL OR
SUM("NEW_TAX_SPECIAL_CURRENT_MTH")/12 IS NOT NULL))
6 - FILTER("COMPANY_TYPE"='LIMIT' AND "TAX_TYPE"='00' AND "TAX_MTH">=TO_DATE(' 2017-03-31 00:00:00',
'SYYYY-MM-DD HH24:MI:SS') AND "TAX_MTH"<=TO_DATE(' 2018-02-28 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AND
"CITY_CODE"='001' AND "NEW_TAX_MISS_CURRENT_MTH"=TO_DATE(' 2200-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
7 - FILTER("TAR"."TAX_MTH"=TO_DATE(' 2018-05-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AND
"TAR"."COMPANY_TYPE"='LIMIT' AND "TAR"."CITY_CODE"='001')
9 - FILTER((SUM("NEW_TAX_CURRENT_MTH")<>0 OR SUM("NEW_TAX_MISS_CURRENT_MTH")<>0 OR SUM("NEW_TAX_GET_CURRENT_MTH")<>0 OR SUM("NEW_TAX_SPECIAL_CURRENT_MTH")<>0) AND
(SUM("NEW_TAX_CURRENT_MTH")/12 IS NOT NULL OR SUM("NEW_TAX_MISS_CURRENT_MTH")/12 IS NOT NULL OR SUM("NEW_TAX_GET_CURRENT_MTH")/12 IS NOT NULL OR
SUM("NEW_TAX_SPECIAL_CURRENT_MTH")/12 IS NOT NULL))
11 - FILTER(:B1=TO_DATE(' 2018-05-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AND :B2='LIMIT' AND :B3='00' AND
:B4='001')
12 - FILTER("COMPANY_TYPE"='LIMIT' AND "COMPANY_TAX_TYPE"=:B1 AND "TAX_TYPE"='00' AND "TAX_MTH">=TO_DATE('
2017-03-31 00:00:00', 'SYYYY-MM-DD HH24:MI:SS') AND "TAX_MTH"<=TO_DATE(' 2018-02-28 00:00:00', 'SYYYY-MM-DD
HH24:MI:SS') AND NVL("COMPANY_GROUP",'-99999')=NVL(:B2,'-99999') AND "CITY_CODE"='001' AND "NEW_TAX_MISS_CURRENT_MTH"=TO_DATE('
2200-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
13 - ACCESS("TAXPAYER"=:B1)
2条答案
按热度按时间0s7z1bwu1#
我尝试的第一件事是使用MERGE语句而不是UPDATE语句-这很容易想到，因为您实际上是在where子句的set子句中重复子查询。
我认为您的UPDATE可以重写为如下内容：
注：
1.我更改了
to_dates()以包含格式掩码（否则，如果更改了NLS_DATE_FORMAT nls参数，to_date()将失败），并且由于您使用"JUL"作为月份，附加的第三个参数使日期字符串转换nls设置独立（例如，如果NLS_DATE_FORMAT nls参数设置为JUL不是有效的短月份，如果没有设置第三个参数，to_date()将失败）。* 您可以通过将日期作为数字传入来避免使用第三个参数，例如to_date('05/07/2015', 'dd/mm/yyyy')。*1.我将
and ecm is not null and esm is not null and ...改为COALESCE，因为它返回列表中的第一个非空值，因此您的检查变为and COALESCE(ecm, esm, ...) is not null。1.实际上并不需要COALESCE，因为HAVING子句有效地排除了所有值都为NULL和0的行。
1.用于使子查询相关的 predicate 将成为目标表和源子查询之间的联接条件，这意味着您不再需要以前在相关子查询中使用的外部查询。
1.我将仅更新具有特定纳税月份的行的条件从join子句移到了update部分的WHERE子句中。我非常肯定它可以留在join子句中，但我认为如果将它放在update的WHERE子句中，其意图会更清楚。
我将测试新的MERGE语句，以确保它正在做正确的事情（或者修复它，直到它做正确的事情），然后看看这对性能有何影响。
如果它仍然作为AWR中的问题语句弹出，那么我将进一步研究如何调优它;可能需要更新的/附加的索引、可能需要物化视图等。
jaxagkaj2#
1.我建议您在表格级别访问批次数据的条件下创建索引。创建索引后，将节省时间
税额比率，其中城市代码=“001”且公司类型=“类型01”且税额月份〈=添加月份（截止日期（“2015年7月8日”），-3）且税额月份〉=添加月份（截止日期（“2015年7月8日”），-14）且生效日期=截止日期（“2015年7月8日”）且税额类型=“00”
1.用临时表重写复杂的子表。使用临时表中的索引可以更快地检索数据。
1.使用减号代替现有子查询。