SQL Query Explain plan analysis

MohammedImran

Hi Tuning Experts,

We have a function which is rarely used but takes around 2hours to complete and per AWR a query takes around 20mins for single execution. Below is the query and its explain plan. I am not sure what could be cause of issue from explain plan and is it possible to convert/generate this plan in Tree form to understand better?

SELECT  /*+ FIRST_ROWS ORDERED USE_NL(IT1) USE_MERGE(RPE) INDEX(IT1 IT1P1) INDEX(RPE RPEP1) */

     IT1.OCHD OCHD,

     RPE.RPRD PCHD,

     IT1.TYPE TYPE,

     IT1.YR YR,

     IT1.PR PR,

     IT1.WK WK,

     IT1.DATE DATE,

     RPE.MULT * NVL (IT1.RTL, 0) RTL,

     RPE.MULT * NVL (IT1.CST, 0) CST,

     RPE.MULT * NVL (IT1.ST_OUT, 0) ST_OUT,

     RPE.MULT * NVL (IT1.QTY, 0) QTY,

     NVL (IT1.CUR, ' ') CUR,

     IT1.IN_QTY,

     IT1.IN_RTL,

     IT1.IN_CST

 FROM  PRV, IT1, RPE

 WHERE    PRV.PCHD = :B2

     AND IT1.OCHD = PRV.OCHD

     AND IT1.PCHD = PRV.PCHD

     AND IT1.TYPE = 'T'

     AND RPE.RTYPE = :B1

     AND RPE.RCHD = IT1.PCHD

UNION ALL

SELECT  /*+ FIRST_ROWS ORDERED USE_NL(IT3) USE_MERGE(RPE) INDEX(IT3 IT3P1) INDEX(RPE RPEP1) */

     IT3.OCHD OCHD,

     RPE.RPRD PCHD,

     IT3.TYPE TYPE,

     IT3.YR YR,

     IT3.PR PR,

     IT3.WK WK,

     IT3.DATE DATE,

     RPE.MULT * NVL (IT3.RTL, 0) RTL,

     RPE.MULT * NVL (IT3.CST, 0) CST,

     RPE.MULT * NVL (IT3.ST_OUT, 0) ST_OUT,

     RPE.MULT * NVL (IT3.QTY, 0) QTY,

     NVL (IT3.CUR, ' ') CUR,

     IT3.IN_QTY,

     IT3.IN_RTL,

     IT3.IN_CST

 FROM  PLV, IT3, RPE

 WHERE    PLV.PCHD = :B2

     AND IT3.OCHD = PLV.OCHD

     AND IT3.PCHD = PLV.PCHD

     AND IT3.TYPE = 'T'

     AND RPE.RTYPE = :B1

     AND RPE.RCHD = IT3.PCHD

ORDER BY  OCHD,

      PCHD,

      TYPE,

      YR,

      PR,

      WK

Plan hash value: 3672387809

----------------------------------------------------------------------------------------------------------------------

| Id | Operation                | Name    | Rows | Bytes | Cost (%CPU)| Time   | Pstart| Pstop |

----------------------------------------------------------------------------------------------------------------------

|  0 | SELECT STATEMENT            |      | 1824 |  229K| 3982K (1)| 00:02:36 |    |    |

|  1 | SORT ORDER BY             |      | 1824 |  229K| 3982K (1)| 00:02:36 |    |    |

|  2 |  UNION-ALL               |      |    |    |      |     |    |    |

|  3 |  MERGE JOIN              |      |  11 | 1441 |  166K (1)| 00:00:07 |    |    |

|  4 |   NESTED LOOPS            |      |  314 | 27946 |  166K (1)| 00:00:07 |    |    |

|  5 |   NESTED LOOPS            |      |  368 | 27946 |  166K (1)| 00:00:07 |    |    |

|  6 |    NESTED LOOPS           |      |  368 | 11040 |  21  (0)| 00:00:01 |    |    |

|  7 |    MERGE JOIN CARTESIAN       |      |   1 |  22 |   3  (0)| 00:00:01 |    |    |

|* 8 |     INDEX RANGE SCAN        | PRV1 |   1 |  13 |   2  (0)| 00:00:01 |    |    |

|  9 |     BUFFER SORT           |      |   1 |   9 |   1  (0)| 00:00:01 |    |    |

| 10 |     TABLE ACCESS BY INDEX ROWID  | PRS   |   1 |   9 |   1  (0)| 00:00:01 |    |    |

|* 11 |      INDEX UNIQUE SCAN       | PRSP1 |   1 |    |   0  (0)| 00:00:01 |    |    |

|* 12 |    TABLE ACCESS FULL        | ORS   |  368 | 2944 |  18  (0)| 00:00:01 |    |    |

| 13 |    PARTITION RANGE ALL        |      |   1 |    |  452  (0)| 00:00:01 |   1 |  226 |

|* 14 |    INDEX RANGE SCAN         | IT1P1 |   1 |    |  452  (0)| 00:00:01 |   1 |  226 |

| 15 |   TABLE ACCESS BY LOCAL INDEX ROWID | IT1   |   1 |  59 |  453  (0)| 00:00:01 |   1 |   1 |

|* 16 |   SORT JOIN              |      |   1 |  42 |   4 (25)| 00:00:01 |    |    |

| 17 |   TABLE ACCESS BY INDEX ROWID BATCHED| RPE   |   1 |  42 |   3  (0)| 00:00:01 |    |    |

|* 18 |    INDEX RANGE SCAN         | RPEP1 |   1 |    |   2  (0)| 00:00:01 |    |    |

| 19 |  MERGE JOIN              |      | 1813 |  228K| 3815K (1)| 00:02:30 |    |    |

| 20 |   NESTED LOOPS            |      | 50822 | 4317K| 3815K (1)| 00:02:30 |    |    |

| 21 |   NESTED LOOPS            |      | 71566 | 4317K| 3815K (1)| 00:02:30 |    |    |

| 22 |    MERGE JOIN CARTESIAN       |      | 6506 |  190K|  119  (0)| 00:00:01 |    |    |

| 23 |    MERGE JOIN CARTESIAN       |      |   1 |  22 |   3  (0)| 00:00:01 |    |    |

|* 24 |     INDEX RANGE SCAN        | PRV1 |   1 |  13 |   2  (0)| 00:00:01 |    |    |

| 25 |     BUFFER SORT           |      |   1 |   9 |   1  (0)| 00:00:01 |    |    |

|* 26 |     TABLE ACCESS BY INDEX ROWID  | PRS   |   1 |   9 |   1  (0)| 00:00:01 |    |    |

|* 27 |      INDEX UNIQUE SCAN       | PRSP1 |   1 |    |   0  (0)| 00:00:01 |    |    |

| 28 |    BUFFER SORT           |      | 6511 | 52088 |  118  (0)| 00:00:01 |    |    |

| 29 |     TABLE ACCESS BY INDEX ROWID   | ORS   | 6511 | 52088 |  116  (0)| 00:00:01 |    |    |

|* 30 |     INDEX RANGE SCAN        | ORSI5 | 6511 |    |  20  (0)| 00:00:01 |    |    |

| 31 |    PARTITION RANGE ALL        |      |  11 |    |  579  (0)| 00:00:01 |   1 |  193 |

|* 32 |    INDEX RANGE SCAN         | IT3P1 |  11 |    |  579  (0)| 00:00:01 |   1 |  193 |

| 33 |   TABLE ACCESS BY LOCAL INDEX ROWID | IT3   |   8 |  456 |  590  (0)| 00:00:01 |   1 |   1 |

|* 34 |   SORT JOIN              |      |   1 |  42 |   4 (25)| 00:00:01 |    |    |

| 35 |   TABLE ACCESS BY INDEX ROWID BATCHED| RPE   |   1 |  42 |   3  (0)| 00:00:01 |    |    |

|* 36 |    INDEX RANGE SCAN         | RPEP1 |   1 |    |   2  (0)| 00:00:01 |    |    |

----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  8 - access("PRE"."PCHD"=TO_NUMBER(:B2))

 11 - access("PRS"."PCHD"=TO_NUMBER(:B2))

 12 - filter("PRS"."PLI">1 OR NVL("ORS"."OLI",99)>1)

 14 - access("IT1"."OCHD"="ORS"."OCHD" AND

       "IT1"."PCHD"=TO_NUMBER(:B2) AND "IT1"."TYPE"='T')

 16 - access("RPE"."RCHD"="IT1"."PCHD")

    filter("RPE"."RCHD"="IT1"."PCHD")

 18 - access("RPE"."RTYPE"=:B1 AND "RPE"."RCHD"=TO_NUMBER(:B2))

 24 - access("PRE"."PCHD"=TO_NUMBER(:B2))

 26 - filter("PRS"."PLI"=1)

 27 - access("PRS"."PCHD"=TO_NUMBER(:B2))

 30 - access("ORS"."OLI"=1)

 32 - access("IT3"."OCHD"="ORS"."OCHD" AND

       "IT3"."PCHD"=TO_NUMBER(:B2) AND "IT3"."TYPE"='T')

 34 - access("RPE"."RCHD"="IT3"."PCHD")

    filter("RPE"."RCHD"="IT3"."PCHD")

 36 - access("RPE"."RTYPE"=:B1 AND "RPE"."RCHD"=TO_NUMBER(:B2))

Note

-----

  - dynamic statistics used: dynamic sampling (level=2)

68 rows selected.

Note:

1. PRV view definition

CREATE OR REPLACE FORCE VIEW PRV

(

  PCHD,

  OCHD

) AS

  SELECT /*+

    ORDERED USE_NL(PRS) USE_MERGE(ORS)

      INDEX(PRS PRSP1)

     INDEX(PRE PRV1)

    */

     PRE.pchd, ORS.ochd

   FROM  PRE, PRS, ORS

  WHERE  PRS.pchd = PRE.pchd

      AND (PRS.pid > 1 OR NVL (ORS.oid, 99) > 1);

2. PLV view definition

CREATE OR REPLACE FORCE VIEW PLV

(

  PCHD,

  OCHD

) AS

  SELECT /*+

    ORDERED USE_NL(PRS) USE_MERGE(ORS)

      INDEX(PRS PRSP1)

     INDEX(PRE PREP1)

    */

     PRE.PCHD, ORS.OCHD

   FROM  PRE, PRS, ORS

  WHERE    PRS.PCHD = PRE.PCHD

      AND PRS.pid = 1

      AND ORS.oid = 1;

3. Table IT1 

had num_rows 919,049,343

has PARTITION BY RANGE (yr, pr, wk)

4. Table IT3

has num_rows 2,903,600,902

PARTITION BY RANGE (yr, pr, wk)

5. Table RPE has num_rows 264

  TABLE ORS has num_rows 6878

  Table PRS has num_rows 154715

  Table PRE has num_rows 200

Thanks,

Imran.

BEDE

I think you should not use those views which have also hints in them, but rather try to get the data from the tables directly. Thus you may possibly eliminate unnecessary joins and give hints that may help your particular task.

Haven't actually taken all the time to look carefully at the views' definitions and the columns in your query as to produce a query using the tables and not those views, yet that is what I would do if I was compelled to deal with such an issue

Jonathan Lewis

Which version of Oracle?

Do you have access to Enterprise Manager, if so you can drill down through the performance screens to this statement, pull up the execution plan, and display it in a tree-like format (though the EM version of a tree is pretty poor).

How many rows you think this query should return for one set of input values?

The optimizer is obeying the hints as closely as it can. So you may find that the first best step you can take is to get rid of the hints to see what happens. It's particularly significant that the hints include FIRST_ROWS and ORDERED: the former was reported in the manuals as "exists for backwards compatibility" about 20 years ago, the latter is highly undesirable and should be replaced with a leading() hint. This suggests that the hints may have been added a long time ago when the version was different and the data was much smaller.

You should be aware that the Note "dynamic statistics used" suggests you've got some tables with missing statistics.

Regards

Jonathan Lewis

MohammedImran

Thanks for the comments Jonathan.

I do not have access to EM, If EM version of tree is poor is there any better way? I was looking for easy way to understand query plans.

I see there is improvement in the cost after removing Hint ORDERED , we are planning to release the change in Test and confirm if removing Hint really improves performance.

The stats for couple of tables (very small record count in hundreds) are not collected so dynamic statistics note is showing anyways we will collect stats for them when we test.

Thanks!

BEDE

The problem is to test thoroughly how everything performs after having removed the hints. It might be that some queries will perform worse. That is the main reason why I thought to avoid using those views for your particular task and leave the views as they are if things work quite satisfactory in other respects.

Some recommend not to use hints at all. But then you definitely must have statistics up to date, otherwise certain queries will run terribly slow, while hints may provide decent performance even when having stale statistics.

MohammedImran

yes correct Bede but replacing the views with tables would be considered a bigger change so for now I have just removed hints from main query and trying to get results from customer test env also the predicates are pushed into view as per execution plan so lets reserve the idea of replacing views for now.

I am not sure why Hints are used at all when optimizer does this already, anyways I have seen many legacy codes that use Hints and yes all the table stats are updated regularly.

BEDE

So, the optimizer sometimes gets it wrong. I've seen that in the past, I mean for Oracle 9, 10 and 11. Plus, the optimizer relies on statistics and sometimes statistics get to be stale. So, in some occasions hints may be a saver. But it's best to run the SQL without any hints, then, if things don't look good, try to use hints to determine a certain access path that seems to be preferable in most cases.

As to big change. Yes, using tables instead of views is a big change in terms of writing more code, but it is a more local change, not affecting the views, which views are very likely used in many other places. So, the thing is: do you wish to write more code but have less things affected, or write less code (in this case practically no new code at all for you only delete hints) and have more things affected? Tests will prove what is best to do.

Jonathan Lewis

You haven't answer these questions:

Which version of Oracle?

How many rows you think this query should return for one set of input values?

You said:

I see there is improvement in the cost after removing Hint ORDERED

An improvement in the cost doesn't necessarily mean an improvement in the response time - in theory it ought to but there are enough limitations in the optimizer's model that in practice you should not depend on the cost as a good indicator of time. This is particularly true when given the optimizer the FIRST_ROWS directive, and included a couple of hints that force it to use particular data access methods.

Note, particularly that the FIRST_ROWS hint will result in the optimizer using an out-dated strategy for returning the first row of the query as quickly as possible, without worrying about how long it might take to return the rest of the data.

A couple of suggestions (assuming you have enough access to do the experiments):

a) When you extract the execution plan, include the format string 'OUTLINE', that will tell us what other features and parameter tweaks may be affecting the execution plan.

b) if you can create your own views to match the views (minus the hints) used in the query, test a copy of this query that has no hints and uses your views to see what the execution path is.

c1) If you can access trace files, enable tracing for the query, then turn tkprof against the trace file to produce the "row source statistics" plan that appears below the SQL and summary stats so that you can compare estimated rows with actual rows, and check where most of the time and work was spent.

OR

c2) Since you are licensed to use the AWR add the MONITOR hint to the query, execute it, then use the SQL Monitor report to show the plan with execution stats: https://jonathanlewis.wordpress.com/2018/04/06/sql-monitor/

OR

c3) enable rowsource execution statistics for the query, run it, and report the stats - example here using a hint to enable the feature: https://jonathanlewis.wordpress.com/2006/11/09/dbms_xplan-in-10g/ However you could also 'alter session set statistics_level = all' or 'alter session set "_rowsource_execution_statistics=true";'

Regards

Jonathan Lewis