RE: Optimizer ignoring hints

From: Hameed, Amir <>
Date: Sat, 14 Sep 2013 15:46:46 +0000
Message-ID: <>

Thanks Jonathan. The developer finally made a change to the Where clause which started driving the optimal plan. I am going to follow your tip of "outline" and run it on the "sub-optimal" query to see what kind of hints the optimizer generates.

-----Original Message-----
From: [] On Behalf Of Jonathan Lewis Sent: Saturday, September 14, 2013 1:47 AM To: 'ORACLE-L'
Subject: Re: Optimizer ignoring hints

Your hints "leading" and "use_nl" hints are invalid - they refer to objects that aren't in the query block where you've put the hint (and because of the nature of the leading hint you can't put it into the query block that is going to be unnested to make it possible for flv to be the leading table in the join order).

It's a matter of luck that you get the plan you want when you replace the binds with SYSDATE - presumably the statistics of the run-time value of the bind when the query is optimised causes a different path to be taken. It's possible, though, that since you have some predicates involving the nvl() function combined with the binds that the optimizer has produced a plan with binds that will give the right answer whether or not the bind is null - since SYSDATE is never null its presence may allow the optimizer to choose a different path. (The latter point is a generic warning - I'm not sure it's relevant in this case).

If you want to replay the SYSDATE plan, use the 'outline' option to get the set of hints that the optimizer generates for the plan and paste all the structural (e.g. leading, unnest, push_subq) , access, and join method hints back into the text. That may be sufficient.


Jonathan Lewis

Author: Oracle Core (Apress 2011)

  • Original Message ----- From: "Hameed, Amir" <> To: "'ORACLE-L'" <> Sent: Thursday, September 12, 2013 3:16 PM Subject: Optimizer ignoring hints

|I have a SQL statement that is part of a PL/SQL package. I have fudged
values in the statement for privacy reasons.
| SELECT oel.header_id, oel.line_id, oel.org_id,
| oel.ordered_item part_number_ordered,
| ship_from_org.organization_code source_location,
| ...
| oel.flow_status_code line_status, oel.ship_from_org_id,
| oel.subinventory, sources.NAME order_source
| FROM oe_order_lines_all oel,
| oe_order_headers_all h,
| oe_order_sources sources,
| hz_cust_site_uses_all ship_su,
| mtl_parameters ship_from_org,
| (SELECT mic2.inventory_item_id, mic2.organization_id
| FROM mtl_item_categories_v mic2
| WHERE mic2.category_set_name = 'WWWWW'
| AND mic2.segment1 IN ('XXXXX', 'YYYYY', 'ZZZZZZ')
| AND NVL (mic2.category_disable_date, :b1 + 5) >= :b1
| AND mic2.enabled_flag = 'Y') mic
| WHERE oel.header_id = h.header_id
| AND NVL (oel.line_category_code, 'ORDER') <> 'RETURN'
| AND sources.order_source_id = oel.order_source_id
| AND ship_su.site_use_id(+) = oel.ship_to_org_id
| AND oel.ship_from_org_id = ship_from_org.organization_id(+)
| AND (oel.creation_date > :b2 OR oel.last_update_date > :b2)
| AND mic.inventory_item_id = oel.inventory_item_id
| AND mic.organization_id = oel.ship_from_org_id
| AND oel.flow_status_code IN
| FROM oe_order_headers_all oeh
| WHERE oeh.header_id = oel.header_id
| AND oeh.flow_status_code IN ('MMMMMM', 'NNNNNN', 'OOOOOO'))
| FROM txrlo0_lookups tl2
| WHERE tl2.lookup_type = 'FFFFFFFFFFFFFFFF'
| AND NVL (tl2.start_dat_active, :b1 - 5) <= :b1
| AND NVL (tl2.end_date_active, :b1 + 5) >= :b1
| AND tl2.enabled_flag = 'Y'
| AND ship_from_org.organization_code = tl2.lookup_code)
| FROM (SELECT lookup_code, meaning
| FROM txrlo0_lookups tl2
| WHERE tl2.lookup_type


| AND NVL (tl2.start_dat_active, :b1 - 5) <=
| AND NVL (tl2.end_date_active, :b1 + 5) >=
| AND tl2.enabled_flag = 'Y') l1,
| (SELECT lookup_code, meaning, attribute2
| FROM fnd_lookup_values flv
| WHERE flv.lookup_type = 'GGGGGGGGGGGGGG'
| AND NVL (flv.start_date_active, :b1 - 5)
<= :b1
| AND NVL (flv.end_date_active, :b1 + 5) >=
| AND flv.enabled_flag = 'Y') l2
| WHERE DECODE (l1.meaning, 'HHHHHH', '99999',
| AND ship_su.LOCATION = l2.lookup_code)
| ORDER BY header_id, inventory_item_id
| ;
| The current explain plan is sub-optimal and is causing the statement to
run for a long time. When I try to drive a specific plan by adding the following hints to the statement, the optimizer seems to ignore the hints as I see the same sub-optimal explain plan even after adding the hints:
| SELECT /*+ LEADING(flv tl2) USE_NL(flv tl2) INDEX(oel
OE_ORDER_LINES_N2) */ oel.header_id, oel.line_id, oel.org_id
| However, if I put the statement in a script and replace the bind
variables with SYSDATE and use hints, the optimizer takes the hints and produces the plan I am trying to drive.
| Does anyone know any reason, why the optimizer would ignore hints when
used with binds (in the package) but take the hints when binds are replaced with literals.
| Thanks,
| Amir
| --
| -----
| No virus found in this message.
| Checked by AVG -
| Version: 2013.0.3392 / Virus Database: 3222/6656 - Release Date: 09/11/13

Received on Sat Sep 14 2013 - 17:46:46 CEST

Original text of this message