Need help to optimize Oracle query in 12c

Question

This particular query was working fine till we were in 11i but as soon as we switched to 12c the query has been doing really bad. I tried to remove the hint and it seems to be doing little better but our DBA wants us to remove all the multiple subqueries which cause serialization of the various sub-steps. I am at a loss on how to change it. Any pointers on what I can do or start with?

     WITH
       -- Get all active accounts that have not been suspended and the suspension rules
       -- apply for the account
       TT1 AS (SELECT 
                      pl.employer_id,
                      ma.employee_id,
                      ma.member_account_id,
                      pl.plan_id
                 FROM sa_plans pl,
                      sa_accounts ma
                WHERE TRUNC(SYSDATE) BETWEEN pl.start_date AND NVL(pl.end_date, TRUNC(SYSDATE))
                  AND pl.employer_id = 23
                  AND pl.plan_type_id NOT IN (4 
                                              ,1) 
                  AND pl.plan_id = ma.plan_id
                  AND sa_mc_info.fn_ee_susp_exempt(ma.employee_id, DECODE(pl.plan_type_id, 1, 6 ,5 )) = 0 
                  AND p_outstanding_threshold_perc > 0
                  AND NVL(ma.auto_suspension_flag, 0) = 0 
                  AND TRUNC(SYSDATE) BETWEEN ma.account_effective_date AND NVL(ma.account_end_date, TRUNC(SYSDATE))
                  AND ma.account_effective_date != NVL(ma.account_end_date, ma.account_effective_date + 1)
              ),
       -- Get all accounts that were active during the plan year for the current accounts
       -- and the outstanding transactions
       TT2 AS (SELECT /*+ MATERIALIZE */
                      ma1.member_account_id,
                      ma1.plan_id,
                      ma1.employee_id ,
                      wwsa.sa_mc_info.fn_acnt_unverified_txns(ma1.member_account_id) outstd_amount
                 FROM sa_accounts ma1,
                      TT1 ma
                WHERE ma1.account_effective_date != NVL(ma1.account_end_date, ma1.account_effective_date + 1)
                  AND ma1.plan_id = ma.plan_id
                  AND ma1.employee_id = ma.employee_id
                  AND ma1.member_account_id = TT1.member_account_id),
       -- Sum the outstanding transactions for the EE and plan 
       TT3 AS (SELECT /*+ MATERIALIZE ORDERED */
                      TT1.member_account_id,
                      SUM(TT2.outstd_amount) outstd_amount
                 FROM TT1,
                      TT2
                WHERE TT1.employee_id = TT2.employee_id
                  AND TT1.plan_id = TT2.plan_id
                GROUP BY TT1.member_account_id
                HAVING SUM(TT2.outstd_amount) > 0),               
       -- Get the current account balance for accounts with outstanding transactions
       TT4 AS (SELECT /*+ MATERIALIZE ORDERED */
                      TT1.*,
                      sa_bal_info.fn_account_balance(TT1.member_account_id) balance,
                      TT3.outstd_amount
                 FROM TT1,
                      TT3
                WHERE TT1.member_account_id = TT3.member_account_id),
       -- Get the list of accounts that need to be suspended
       TT5 as (SELECT /*+ MATERIALIZE */
                      member_account_id,
                      employee_id
                 FROM TT4
                WHERE outstd_amount > balance * p_outstanding_threshold_perc
                  AND outstd_amount > NVL(p_minimum_threshold_amount, 0)  )
SELECT *
  FROM TT5
ORDER BY employee_id;

This is the explain plan

    -----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name                        | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                             |     1 |    26 |  8622   (1)| 00:00:01 |
|   1 |  TEMP TABLE TRANSFORMATION              |                             |       |       |         |             |
|   2 |   LOAD AS SELECT                        | SYS_TEMP_0FD9D666F_DCBC1560 |       |       |         |             |
|   3 |    NESTED LOOPS                         |                             |     4 |   252 |  8600   (1)| 00:00:01 |
|   4 |     NESTED LOOPS                        |                             |  1707 |   252 |  8600   (1)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID BATCHED| SA_PLANS                    |     3 |    84 |  8452   (1)| 00:00:01 |
|   6 |       INDEX FULL SCAN                   | SA_PLANS_PK                 | 19218 |       |    75   (2)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN                   | SA_ACCOUNTS_N03             |   569 |       |     2   (0)| 00:00:01 |
|*  8 |     TABLE ACCESS BY INDEX ROWID         | SA_ACCOUNTS                 |     1 |    35 |    67   (0)| 00:00:01 |
|   9 |   LOAD AS SELECT                        | SYS_TEMP_0FD9D6670_DCBC1560 |       |       |         |             |
|  10 |    NESTED LOOPS                         |                             |     3 |   177 |     8   (0)| 00:00:01 |
|  11 |     NESTED LOOPS                        |                             |     3 |   177 |     8   (0)| 00:00:01 |
|  12 |      VIEW                               |                             |     1 |    26 |     2   (0)| 00:00:01 |
|  13 |       TABLE ACCESS FULL                 | SYS_TEMP_0FD9D666F_DCBC1560 |     1 |    22 |     2   (0)| 00:00:01 |
|* 14 |      INDEX RANGE SCAN                   | SA_ACCOUNTS_N01             |     3 |       |     2   (0)| 00:00:01 |
|* 15 |     TABLE ACCESS BY INDEX ROWID         | SA_ACCOUNTS                 |     3 |    99 |     6   (0)| 00:00:01 |
|  16 |   LOAD AS SELECT                        | SYS_TEMP_0FD9D6671_DCBC1560 |       |       |         |             |
|* 17 |    FILTER                               |                             |       |       |         |             |
|  18 |     HASH GROUP BY                       |                             |     1 |    41 |     5  (20)| 00:00:01 |
|* 19 |      HASH JOIN                          |                             |     1 |    41 |     4   (0)| 00:00:01 |
|  20 |       VIEW                              |                             |     1 |    17 |     2   (0)| 00:00:01 |
|  21 |        TABLE ACCESS FULL                | SYS_TEMP_0FD9D666F_DCBC1560 |     1 |    22 |     2   (0)| 00:00:01 |
|  22 |       VIEW                              |                             |     1 |    24 |     2   (0)| 00:00:01 |
|  23 |        TABLE ACCESS FULL                | SYS_TEMP_0FD9D6670_DCBC1560 |     1 |    30 |     2   (0)| 00:00:01 |
|  24 |   LOAD AS SELECT                        | SYS_TEMP_0FD9D6672_DCBC1560 |       |       |         |             |
|* 25 |    HASH JOIN                            |                             |     1 |    78 |     4   (0)| 00:00:01 |
|  26 |     VIEW                                |                             |     1 |    52 |     2   (0)| 00:00:01 |
|  27 |      TABLE ACCESS FULL                  | SYS_TEMP_0FD9D666F_DCBC1560 |     1 |    22 |     2   (0)| 00:00:01 |
|  28 |     VIEW                                |                             |     1 |    26 |     2   (0)| 00:00:01 |
|  29 |      TABLE ACCESS FULL                  | SYS_TEMP_0FD9D6671_DCBC1560 |     1 |    19 |     2   (0)| 00:00:01 |
|  30 |   LOAD AS SELECT                        | SYS_TEMP_0FD9D6673_DCBC1560 |       |       |         |             |
|* 31 |    VIEW                                 |                             |     1 |    52 |     2   (0)| 00:00:01 |
|  32 |     TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6672_DCBC1560 |     1 |    48 |     2   (0)| 00:00:01 |
|  33 |   SORT ORDER BY                         |                             |     1 |    26 |     3  (34)| 00:00:01 |
|  34 |    VIEW                                 |                             |     1 |    26 |     2   (0)| 00:00:01 |
|  35 |     TABLE ACCESS FULL                   | SYS_TEMP_0FD9D6673_DCBC1560 |     1 |    12 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------

What was the plan in the previous version (11i is a version of Oracle Applications, I assume your intent here was to say that it ran well in a previous version of the Oracle database like 11g)? The query you posted still has a bunch of hints but in the text of your question is says that you removed "the hint" which is confusing. Does the query plan you posted come from the version of the query with all the hints? Or the one with the hints removed? — Justin Cave, Mar 07 '16 at 20:31
There is missing "predicate information" - it's printed under the plan table, could you supplement the plan with it? — krokodilko, Mar 07 '16 at 20:40
i would take out the MATERIALIZE and ORDERED hints, (Oracle will decide what should he use) and i would try a PARALLEL(8) hint instead. Optimizing its about "aim-shoot-aim-shoot...", you should try out a few way, because its depending on a lof of thing — Thomas, Mar 10 '16 at 09:52
is that function sa_mc_info.fn_ee_susp_exempt() DETERMINISTIC? when not then change it to — Thomas, Mar 10 '16 at 09:58

score 0 · Answer 1 · answered Mar 15 '16 at 08:07

0

Try to execute TT1 and evaluate execution time. If it's OK, then continue with TT1+TT2 and so on. You need to understand where problem is before truing to solve it.

answered Mar 15 '16 at 08:07

Anna Mishakina

11
2

score 0 · Answer 2 · answered Aug 05 '16 at 18:43

I would assume your problem is directed related to a bad plan.

So, as you said, it performed just fine on 11g database so there basic two things you could do to try to solve your problem in a short term manner:

Check the optimizer_features_enable parameter on 12c version. If it is set to 12.* or an upper version, try to downgrade the session who is running the query to the 11.* version of your old database.
Assuming you have Tuning Pack (licensing issues - take care) enabled into your production environment, you could use SQL Profiles. How? You could manually copy your the plan from the 11g and import it to the 12c database. Take a look at dbms_sqltune.import_sql_profile. https://oracle-randolf.blogspot.com.br/2009/03/plan-stability-in-10g-using-existing.html

As I said before, these are short term action. It would be better to evaluate your SQL.

Need help to optimize Oracle query in 12c

2 Answers2