issue when using diving_site hint with insert statement

Krisanth Kumar

I am facing an issue, I have some records being imported from Oracle ERP system to my dataware house system, when I run the query alone I am getting the results in less than 5 seconds and its using the index of EPR as expected. But when I add the same query to a INSERT statment its take very long time for the result to come and when I checked the explain plan it seems to be the indexes are not being used.

The query which I used is as below.

INSERT INTO ebi.weekly_report

            (week_num, insert_date, Order_source, order_number, line_ssd, line_creation_date, item_number, ordered_quantity,

            order_type,line_number,line_status,planner)  

        SELECT /*+ driving_site(ot, oh, ol, msi, oos) */ 2 lv_week,sysdate,

          oos.name AS SOURCE,

            oh.ORDER_NUMBER,

           ol.SCHEDULE_SHIP_DATE AS LINE_SSD,

           ol.CREATION_DATE ORDER_CREATION_DATE,

           msi.SEGMENT1 AS ITEM_NUMBER, ol.ORDERED_QUANTITY ,

           ot.NAME AS ORDER_TYPE,

           ol.LINE_NUMBER||'.'||ol.SHIPMENT_NUMBER ||'.'||ol.OPTION_NUMBER||'.'||ol.COMPONENT_NUMBER as LINE_NUMBER,

           ol.FLOW_STATUS_CODE,

           (select /*+ driving_site(mp) */ mp.PLANNER_CODE||' '||mp.DESCRIPTION

            from [email protected] mp

            where mp.ORGANIZATION_ID=msi.ORGANIZATION_ID

              and mp.PLANNER_CODE=msi.PLANNER_CODE

           ) PLANNER

         FROM

           [email protected] ot,

           [email protected] oh,

           [email protected] ol,

           [email protected] msi,

           [email protected] oos

         WHERE oos.order_source_id = oh.order_source_id

           AND oh.OPEN_FLAG='Y'

           AND oh.ORDER_TYPE_ID=ot.TRANSACTION_TYPE_ID

           and ot.name not in ('INTERCOMPANY_OMAR/COF_MY_ORDER')

           and ot.name not like '%RMA%'

           and msi.planning_make_buy_code = 1

           AND ot.LANGUAGE='US'

           and ol.ORDERED_QUANTITY >0

           AND ol.HEADER_ID=oh.HEADER_ID

           AND ol.ORG_ID=oh.ORG_ID

           AND ol.SHIP_FROM_ORG_ID=428

           AND ol.FLOW_STATUS_CODE not IN ('CLOSED','CANCELLED')

           AND msi.ORGANIZATION_ID=ol.SHIP_FROM_ORG_ID

           AND msi.INVENTORY_ITEM_ID=ol.INVENTORY_ITEM_ID

           and nvl(ol.schedule_ship_date,sysdate-8)<trunc(sysdate)-7;

I am using Oracle Database version 11g.

Thanks in advance,

Jarkko Turpeinen

Hi

i do not know about your case but there is bug relating to this kind of strange behavior. Another OP is questioning same kind of behavior .

Bug 10013899 - Allow CBQT for some DML / DDL (Doc ID 10013899.8)

Fix for that bug 11.2.0.4 (Server Patch Set)

FFS

Unless something has changed on later versions of the DB, if you're inserting into a table on the local host, then SQL ignores all DRIVING_SITE hints and has to pull the data across from the remote sites, as per Jonathon Lewis's statement:

"So why is Oracle “ignoring” the hint ? Because it’s supposed to. The SQL has to operate at the site which holds the target of the DML statement. It’s not a bug, it’s defined behaviour."

found here: https://jonathanlewis.wordpress.com/2008/12/05/distributed-dml/

Solomon Yakobson

FFS wrote:

"So why is Oracle “ignoring” the hint ? Because it’s supposed to.

No, it is not supposed to. We have two part DML INSERT SELECT where SELECT is not even distributed. So in general, there shouldn't even be a need for driving site hint and Oracle executes SELECT on remote site, bring results over to local site and do INSERT comparing to bringing over remote tables to local site and doing filtering locally. For example:

SQL> explain plan for
  2  insert into emp1
  3  select e.* from [email protected] e,[email protected] d where e.deptno = e.deptno
  4  /

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1788691278

----------------------------------------------------------------------
| Id  | Operation                | Name | Cost (%CPU)| Inst   |IN-OUT|
----------------------------------------------------------------------
|  0 | INSERT STATEMENT          |      |    0  (0)  |        |      |
|  1 |  LOAD TABLE CONVENTIONAL  | EMP1 |            |        |      |
|  2 |  REMOTE                   |      |            |    XXX | R->S |
----------------------------------------------------------------------

Remote SQL Information (identified by operation id):

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------

  2 - EXPLAIN PLAN INTO [email protected]! FOR SELECT
      "A2"."EMPNO","A2"."ENAME","A2"."JOB","A2"."MGR","A2"."HIREDATE","A2"."SA
      L","A2"."COMM","A2"."DEPTNO" FROM "EMP" "A2","DEPT" "A1" WHERE
      "A2"."DEPTNO"="A2"."DEPTNO" (accessing 'XXX' )

As you can see, whole SELECT was executed remotely and there is no need for driving site hint.

SY.

FFS

Ah yes, I see that now, my apologies. Thanks Solomon.

Unknown

I am facing an issue, I have some records being imported from Oracle ERP system to my dataware house system, when I run the query alone I am getting the results in less than 5 seconds and its using the index of EPR as expected. But when I add the same query to a INSERT statment its take very long time for the result to come and when I checked the explain plan it seems to be the indexes are not being used.

           (select /*+ driving_site(mp) */ mp.PLANNER_CODE||' '||mp.DESCRIPTION
            from [email protected] mp
            where mp.ORGANIZATION_ID=msi.ORGANIZATION_ID
              and mp.PLANNER_CODE=msi.PLANNER_CODE
           ) PLANNER

But you have TWO driving_site hints in your query!.

That hint is to indicate which ONE site should be used so Oracle may just be ignoring the multiple hints altogether.

That subquery above is perfectly valid as an UNCORRELATED query (even though that is NOT what you intend). So if Oracle actually took that hint above it could execute that subquery for EVERY row of the main query - that would NOT be good.

Can you move that subquery to the main query as a join?

Or try adding 'msi' to the hint above and also replacing 'msi.ORGANIZATION_ID' with the same '428' constant you use in the main query.

Jonathan Lewis

Which exact version of Oracle.

What's the execution plan for the select, what's the plan for the insert - how are you getting the execution plans.

How many rows are you selecting/inserting, how are you testing the select ?

I've done a couple of quick tests and it may be the presence of the SYSDATE in the select list that's causing the problem (although the two appearances of SYSDATE in the where clause are also likely to be contributing).

For testing purposes can you :

a) try testing the insert without the SYSDATE select

b) repeat (a) but change the two references in the where clause to (select sysdate from [email protected])

UPDATE:  changed my mind about (b) - it shouldn't make any difference; but I am seeing a change in the way that Oracle handles the inline scalar subquery when I include SYSDATE is in the main select list in my example. Don't yet know why.

Regards

Jonathan Lewis

Krisanth Kumar

Hi RP078,

Thanks for you reply, I tried to update the inline view to the outer query but unfortunately there was no performance improvement for that too.

Krisanth Kumar

Hi Jonathan,

Thanks for your inputs, your inputs solved my issue removing sysdate from SELECT clause.

Apologize for mentioning my DB version as 11 its actually 9.2.0.6.0. I use EXPLAIN PLAN FOR SELECT to get the explain plan. I have attached the explain plan of both my select and insert statement (with sysdate and without sysdate in select)

But when I replace my select statement with (select sysdate dt from [email protected]) and use this value in all sysdate reference (i.e in select and where clause) again the query performance goes down. can you help me why is this happening this way ? Also can you explain me why when I replaced the sysdate in select and not in where clause the query executed fast ?

Just to add on select alone is working fast even when I don't give the driving site hint.

Thanks in advance.