Optimizer ignoring hints

From: Hameed, Amir <Amir.Hameed_at_xerox.com>
Date: Thu, 12 Sep 2013 14:16:06 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED0DE162BC_at_USA7109MB012.na.xerox.net>

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
                      ('AAAAAA', 'BBBBBB', 'CCCCCC', 'DDDDDD')
     AND EXISTS (
            SELECT 'Y'
              FROM oe_order_headers_all oeh
             WHERE oeh.header_id = oel.header_id
               AND oeh.flow_status_code IN ('MMMMMM', 'NNNNNN', 'OOOOOO'))
     AND EXISTS (
            SELECT 'Y'
              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)
    AND EXISTS (
            SELECT DISTINCT 'Y'
                       FROM (SELECT lookup_code, meaning
                               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') l1,
                            (SELECT lookup_code, meaning, attribute2
                               FROM fnd_lookup_values flv
                              WHERE flv.lookup_type = 'GGGGGGGGGGGGGG'
                                AND flv.LANGUAGE = 'US'
                                AND NVL (flv.start_date_active, :b1 - 5) <= :b1
                                AND NVL (flv.end_date_active, :b1 + 5) >= :b1
                                AND flv.enabled_flag = 'Y') l2
                      WHERE DECODE (l1.meaning, 'HHHHHH', '99999', l1.meaning)                                                                  l2.attribute2
                        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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Sep 12 2013 - 16:16:06 CEST