Optimize Oracle query with Hints

Question

I have this complex Oracle query.

SELECT
    r3.object_id
FROM
    nc_references r
    INNER JOIN nc_objects     o ON r.object_id = o.object_id
                               AND o.object_class_id = 90000330 /* Work Item */
    INNER JOIN nc_params      p ON p.object_id = o.object_id
                              AND p.attr_id = 90100070 /* State */  AND p.list_value_id IN ( 90100071 /* Ready */, 90100072 /* Active */ )
    INNER JOIN nc_po_actions  poa ON poa.manual_task_id = o.object_id
    INNER JOIN nc_po_tasks    pot ON pot.task_id = poa.task_id
    INNER JOIN nc_references  r1 ON r1.object_id = pot.container_id
                                   AND r1.attr_id = 9145923960313063683 /* Wave ID */
    INNER JOIN nc_references  r2 ON r2.object_id = pot.container_id
                                   AND r2.attr_id = 9145685312013687931 /* Product OrderID */
    INNER JOIN nc_references  r3 ON r1.reference = r3.reference
                                   AND r3.attr_id = 9145065302013613216 /* Project Wave */ AND r2.reference = r3.object_id
    INNER JOIN nc_objects     o2 ON r3.object_id = o2.object_id
WHERE
        r.reference = 9155224548713314821
    AND r.attr_id = 90100080 /* Assigned To */
    AND o.object_type_id = 9146598858613093106

I tried some optimization:

SELECT
    r3.object_id
FROM
         nc_references src
    INNER JOIN nc_objects     o ON o.object_type_id = 9146598858613093106
                               AND o.object_class_id = 90000330 /* Work Item */
                               AND src.object_id = o.object_id
                               AND src.reference = 9155224548713314821
                               AND src.attr_id = 90100080 /* Assigned To */
    INNER JOIN nc_params      p ON ( p.list_value_id = 90100071 /* Ready */  OR p.list_value_id = 90100072 /* Active */ )
                              AND p.object_id = o.object_id
                              AND p.attr_id = 90100070 /* State */
    INNER JOIN nc_po_actions  poa ON poa.manual_task_id = o.object_id
    INNER JOIN nc_po_tasks    pot ON pot.task_id = poa.task_id
    INNER JOIN nc_references  r1 ON r1.object_id = pot.container_id
                                   AND r1.attr_id = 9145923960313063683 /* Wave ID */
    INNER JOIN nc_references  r2 ON r2.object_id = pot.container_id
                                   AND r2.attr_id = 9145685312013687931 /* Product OrderID */
    INNER JOIN nc_references  r3 ON r2.reference = r3.object_id
                                   AND r3.attr_id = 9145065302013613216 /* Project Wave */
                                   AND r1.reference = r3.reference;

But I get more than 40+ seconds of execution. Looks like because list of tasks is quite huge.

Do you know how I can add Hints into this query in order to improve the performance?

Edit: Explain plan

    SELECT /*+ gather_plan_statistics MIDR03*/ r3.object_id
FROM
         nc_references src
    INNER JOIN nc_objects     o ON o.object_type_id = 9146598858613093106
                               AND o.object_class_id = 90000330 /* Work Item */                            AND src.object_id = o.object_id
                               AND src.reference = 9155224548713314821
                               AND src.attr_id = 90100080 /* Assigned To */     INNER JOIN nc_params      p ON ( p.list_value_id = 90100071 /* Ready */  OR p.list_value_id =
  90100072                                          
    /* Active */ )
                              AND p.object_id = o.object_id
                              AND p.attr_id = 90100070 /*                                           
    State */     INNER JOIN nc_po_actions  poa ON poa.manual_task_id = o.object_id
    INNER JOIN nc_po_tasks    pot ON pot.task_id = poa.task_id
    INNER JOIN nc_references  r1 ON r1.object_id = pot.container_id
                                   AND r1.attr_id = 9145923960313063683 /* Wave [d:1|] */     INNER JOIN nc_references r

                                    
                                            
Plan hash value: 1629775924                                         
                                            
----------------------------------------------------------------------------------------------------------------------------------------------                                          
| [d:1|]  |Lvl| Operation                                  | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |                                          
----------------------------------------------------------------------------------------------------------------------------------------------                                          
|   0 |  0|.SELECT STATEMENT                           |                              |      1 |        |      0 |00:00:43.23 |     441K|  48190 |                                          
|   1 |  1|..NESTED LOOPS                              |                              |      1 |      1 |      0 |00:00:43.23 |     441K|  48190 |                                          
|   2 |  2|...NESTED LOOPS                             |                              |      1 |      1 |      0 |00:00:43.23 |     441K|  48190 |                                          
|   3 |  3|....NESTED LOOPS                            |                              |      1 |      1 |      0 |00:00:43.23 |     441K|  48190 |                                          
|   4 |  4|.....NESTED LOOPS                           |                              |      1 |      1 |      0 |00:00:43.23 |     441K|  48190 |                                          
|   5 |  5|......NESTED LOOPS                          |                              |      1 |      1 |      0 |00:00:43.23 |     441K|  48190 |                                          
|   6 |  6|.......NESTED LOOPS                         |                              |      1 |      1 |      0 |00:00:43.23 |     441K|  48190 |                                          
|   7 |  7|........NESTED LOOPS                        |                              |      1 |      1 |  23315 |00:00:26.82 |     355K|  31538 |                                          
|   8 |  8|.........TABLE ACCESS BY INDEX ROWID BATCHED| NC_REFERENCES                |      1 |      1 |  80719 |00:00:18.92 |   40952 |  17331 |                                          
|*  9 |  9|..........INDEX RANGE SCAN                  | XIF01NC_REFERENCES           |      1 |      1 |  80719 |00:00:00.18 |     337 |      0 |                                          
|* 10 |  8|.........TABLE ACCESS BY INDEX ROWID        | NC_OBJECTS                   |  80719 |      1 |  23315 |00:00:11.79 |     314K|  14207 |                                          
|* 11 |  9|..........INDEX UNIQUE SCAN                 | XPKNC_OBJECTS                |  80719 |      1 |  80719 |00:00:01.55 |     233K|    813 |                                          
|* 12 |  7|........TABLE ACCESS BY INDEX ROWID BATCHED | NC_PARAMS                    |  23315 |      1 |      0 |00:00:12.94 |   85919 |  16652 |                                          
|* 13 |  8|.........INDEX RANGE SCAN                   | XIF12NC_PARAMS               |  23315 |      1 |  23315 |00:00:03.40 |   70073 |   4638 |                                          
|  14 |  6|.......TABLE ACCESS BY INDEX ROWID BATCHED  | NC_PO_ACTIONS                |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |                                          
|* 15 |  7|........INDEX RANGE SCAN                    | NC_PO_ACTIONS_IX_MANUAL_TASK |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |                                          
|  16 |  5|......TABLE ACCESS BY INDEX ROWID           | NC_PO_TASKS                  |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |                                          
|* 17 |  6|.......INDEX UNIQUE SCAN                    | XPKNC_PO_TASKS               |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |                                          
|* 18 |  4|.....INDEX RANGE SCAN                       | XIF02NC_REFERENCES           |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |                                          
|* 19 |  3|....INDEX RANGE SCAN                        | XIF02NC_REFERENCES           |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |                                          
|* 20 |  2|...INDEX RANGE SCAN                         | XIF02NC_REFERENCES           |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |                                          
----------------------------------------------------------------------------------------------------------------------------------------------                                          
                                            
Predicate Information (identified by operation [d:1|]):                                         
---------------------------------------------------                                         
                                            
   9 - access("SRC"."REFERENCE"=9155224548713314821 AND "SRC"."ATTR_ID"=90100080)                                           
  10 - filter(("O"."OBJECT_TYPE_ID"=9146598858613093106 AND "O"."OBJECT_CLASS_ID"=90000330))                                            
  11 - access("SRC"."OBJECT_ID"="O"."OBJECT_ID")                                            
  12 - filter(("P"."LIST_VALUE_ID"=90100071 OR "P"."LIST_VALUE_ID"=90100072))                                           
  13 - access("P"."OBJECT_ID"="O"."OBJECT_ID" AND "P"."ATTR_ID"=90100070)                                           
  15 - access("POA"."MANUAL_TASK_ID"="O"."OBJECT_ID")                                           
  17 - access("POT"."TASK_ID"="POA"."TASK_ID")                                          
  18 - access("R1"."OBJECT_ID"="POT"."CONTAINER_ID" AND "R1"."ATTR_ID"=9145923960313063683)                                         
  19 - access("R2"."OBJECT_ID"="POT"."CONTAINER_ID" AND "R2"."ATTR_ID"=9145685312013687931)                                         
  20 - access("R2"."REFERENCE"="R3"."OBJECT_ID" AND "R3"."ATTR_ID"=9145065302013613216 AND "R1"."REFERENCE"="R3"."REFERENCE")                                           
                                            

Answer 1

Firstly, make sure the statistics are up to date, so the optimiser has the best chance of generating a good execution plan. For the scan of XIF01NC_REFERENCES it estimated 1 row but it got 80719, which suggests that it thinks the table is empty or very small when in fact it is not. Nested-loops plans often don't scale well for large volumes, so using these due to a stats underestimate, when a hash join would work better, is a common problem.

What proportion is 80719 of the whole table - is that most of the rows, or only a few of the rows? A nested loop usually works best when it can pick a small subset of the table, while if you need most of the rows a full scan would usually be more efficient.

I would also lay out the query neatly. Opinions vary so it's up to you, but I prefer to put only join predicates in a join clause (it makes no difference to the optimiser for inner joins) and move filter predicates to the where clause. Also the inner and outer keywords are just clutter so I prefer not to use them, and uppercase code belongs in 1970s COBOL, so I get this:

select r3.object_id
from   nc_references r
       join nc_objects o on o.object_id = r.object_id
       join nc_params p on p.object_id = o.object_id
       join nc_po_actions poa on poa.manual_task_id = o.object_id
       join nc_po_tasks pot on pot.task_id = poa.task_id
       join nc_references r1 on r1.object_id = pot.container_id
       join nc_references r3 on r3.reference = r1.reference
       join nc_objects o2 on o2.object_id = r3.object_id
       join nc_references r2 on r2.object_id = pot.container_id
            and r2.reference = r3.object_id
where  r.reference = 9155224548713314821
and    r.attr_id = 90100080 --Assigned To
and    o.object_type_id = 9146598858613093106
and    o.object_class_id = 90000330 --Work Item
and    r1.attr_id = 9145923960313063683 --Wave ID
and    r2.attr_id = 9145685312013687931 --Product OrderID
and    r3.attr_id = 9145065302013613216 --Project Wave
and    p.attr_id = 90100070 --State
and    p.list_value_id in (90100071, 90100072) --Ready,Active

Now for the hints (assuming updating the stats didn't fix the execution plan). There are a lot of possible join orders and I don't know which is best so I am not specifying one, but you could do that with the leading hint, for example

/*+ leading(r o p) */

would tell the optimiser to only consider plans where the join order began r then o then p, and let it decide the rest for itself (though see the note below about hash joins).

To force a full scan, use the full hint, for example:

/*+ full(o) */

To force one or more hash joins, use the use_hash hint, which can be applied to multiple sources, so

/*+ use_hash(o p) */

is the same as

/*+ use_hash(o) use_hash(p) */

A hash join consists of "build" and "probe" operations, and the optimiser has the freedom to chose which table to use for each one, even if you specified a join order with leading, so you may still need to consider no_swap_join_inputs/swap_join_inputs hints if you want a specific order. The best description of hinting hash joins that I've read is by Jonathan Lewis: Hash Joins.

Now for your query, if you want to try full scans and hash joins everywhere instead of indexes and nested loops, without specifying any order, you might try:

/*+
use_hash(r o p poa pot r1 r3 o2 r2)
full(r) full(o) full(p) full(poa) full(pot) full(r1) full(r3) full(o2) full(r2)
*/

However,

1. If the stats are wrong then the optimiser might still choose an inefficient join order, so you may need to look at leading and possibly no_swap_join_inputs hints.

2. Maybe some combination of full scans and hash joins will work best. With 9 tables there are a lot of possibilities.

3. Hints are great for experimenting, when you suspect the optimiser has missed something, and sometimes in production code when for some reason it's not getting a good plan, but ideally you should fix the issue with your statistics so that the optimiser can do its job without hints.