how to do SQL tuning on below update sql

Question

everyone,Recently I was involved one SQL tuning task for oracle, I had a very difficult problem I think,I could even say I was scared by this question, I get the AWR report from DBA,and seems the red line SQL from the AMR need do some adjustment(I have pasted these SQL as below,this sql was in the SP).But I do not know what caused the poor performance,Anyone can help provide some solutions or thoughts on tune the SQL?

if you need some more evidence from AWR, Please let me know.

Thanks in advance...

    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)

Answer 1

I will recommend you have create index on where condition where lot data is access on table level. After creation on index, there will time saved

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'

Rewrite complex subqueries with temporary tables. You can use indexes in temp tables to faster retrieval data.

Use minus instead of exist subqueries.

Answer 2

The first thing I'd try is to use a MERGE statement instead of that UPDATE statement - this leaps to mind because you are effectively repeating the subquery in the set clause in the where clause.

I think your UPDATE could be rewritten to something like:

MERGE INTO tax_ratio tgt
  USING (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', 'dd-MON-yyyy', 'nls_date_language = english'), -3)
         AND    tax_mth >= add_months(to_date('08-JUL-2015', 'dd-MON-yyyy', 'nls_date_language = english'), -3)
         AND    eff_date = to_date('08-JUL-2015', 'dd-MON-yyyy', 'nls_date_language = english')
         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
  ON (tgt.city_code = src.city_code
      AND    tgt.tax_type = src.tax_type
      AND    tgt.company_type = src.company_type
      AND    tgt.taxpayer = src.taxpayer
      AND    NVL(tgt.company_group, '-99999') = NVL(src.company_group, '-99999')
      --AND    COALESCE(src.ecm, src.esm, src.epm, src.ecam) IS NOT NULL -- unnecessary, since your having clause excludes rows where all are null anyway
      AND    tgt.company_tax_type = src.company_tax_type)
WHEN MATCHED THEN
  UPDATE SET tgt.ecm = NVL(src.ecm, 0),
             tgt.esm = NVL(src.esm, 0),
             tgt.epm = NVL(src.epm, 0),
             tgt.ecam = NVL(src.ecam, 0),
             tgt.update_dt = SYSDATE,
             tgt.update_by = 'ffee_user')
  WHERE  tgt.tax_mth = to_date('08-JUL-2015', 'dd-MON-yyyy', 'nls_date_language = english');

Notes:

1. I changed the to_dates() to include a format mask (otherwise, if your NLS_DATE_FORMAT nls parameter was changed, the to_date() would fail) and, since you were using "JUL" as the month, the additional third parameter to make the date-as-a-string conversion nls setting independent (e.g. if your NLS_DATE_FORMAT nls parameter was set to something where JUL isn't a valid short-month, the to_date() would fail if this third parameter wasn't set). You could avoid the use of the third parameter by passing the date in as numbers, e.g. to_date('05/07/2015', 'dd/mm/yyyy').
2. I changed the and ecm is not null and esm is not null and ... to use COALESCE instead, since this returns the first non-null value in the list. Thus your check becomes and COALESCE(ecm, esm, ...) is not null.
3. The COALESCE isn't actually needed, since your HAVING clause effectively excludes rows where all the values are NULL as well as 0.
4. The predicates that were used to make the subquery correlated become the join conditions between the target table and source subquery, which means you no longer need the outer query that you previously had in the correlated subquery.
5. I moved the condition to only update the rows with the specific tax month from the join clause into the WHERE clause of the update section. I'm pretty sure it could have been left in the join clause, but I think the intention is clearer if it's put in the update's WHERE clause.

I'd test the new MERGE statement to make sure it's doing the right thing (or fix it until it does) and then see how that affects the performance.

If it still pops up as a problem statement in AWR, then I'd take a further look into how to tune it; maybe updated/additional indexes are required, maybe a materialized view, etc.