Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

Performance Tuning Reference poster
 
Oracle training in Linux 

commands
 
Oracle training Weblogic Book
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> DRIVING_SITE hint is always getting ignored
soham
post Mar 16 2017, 07:01 AM
Post #1


Newbie
*

Group: Members
Posts: 1
Joined: 16-March 17
Member No.: 51,837



Dear Sir,

We have created a database link from Oracle to HANA and we are facing an issue. While the select * works just fine(within 10 seconds), the join with the remote and local tables cease to work and takes nearly 2 minutes to retrieve the records.

We have used DRIVING_SITE hint, but it seems somehow that hint gets ignored when we join tables from local(17000 rows) versus remote(1.5 million) rows and in the explain plan I can see only the remote query without the join/where. HANA also enabled their tracing and they couldn't find the join on their side of trace which means despite using the DRIVING_SITE the join doesn't travel to HANA and it gets executed in oracle only. Could you please let me know if I am missing anything, any help would be hugely appreciated, this has become a major roadblock for us.

I am pasting the explain plan and the query below and "Remote SQL Information" clearly depicts that the remote site is not receiving the complete query with the join clause.


Plan hash value: 2763985689

--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Inst |IN-OUT|
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1802K| 4047M| 4592 (1)| 00:00:56 | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10007 | 1802K| 4047M| 4592 (1)| 00:00:56 | | |
|* 3 | HASH JOIN | | 1802K| 4047M| 4592 (1)| 00:00:56 | | |
| 4 | BUFFER SORT | | | | | | | |
| 5 | PX RECEIVE | | 2000 | 4207K| 200 (0)| 00:00:03 | | |
| 6 | PX SEND BROADCAST | :TQ10000 | 2000 | 4207K| 200 (0)| 00:00:03 | | |
| 7 | REMOTE | cdw.Finance.POC/GCActualCost | 2000 | 4207K| 200 (0)| 00:00:03 | DG4OD | R->S |
|* 8 | HASH JOIN | | 90117 | 17M| 4387 (1)| 00:00:53 | | |
| 9 | PX RECEIVE | | 337K| 1976K| 219 (1)| 00:00:03 | | |
| 10 | PX SEND BROADCAST | :TQ10003 | 337K| 1976K| 219 (1)| 00:00:03 | | |
| 11 | PX BLOCK ITERATOR | | 337K| 1976K| 219 (1)| 00:00:03 | | |
| 12 | INDEX FAST FULL SCAN | SRM_RESOURCES_N11 | 337K| 1976K| 219 (1)| 00:00:03 | | |
|* 13 | HASH JOIN | | 90117 | 16M| 4166 (1)| 00:00:50 | | |
|* 14 | HASH JOIN RIGHT OUTER | | 16364 | 3020K| 3946 (1)| 00:00:48 | | |
| 15 | PX RECEIVE | | 24205 | 330K| 48 (0)| 00:00:01 | | |
| 16 | PX SEND BROADCAST | :TQ10004 | 24205 | 330K| 48 (0)| 00:00:01 | | |
| 17 | PX BLOCK ITERATOR | | 24205 | 330K| 48 (0)| 00:00:01 | | |
|* 18 | INDEX FAST FULL SCAN | PRJ_BASELINE_DETAILS_N3 | 24205 | 330K| 48 (0)| 00:00:01 | | |
| 19 | NESTED LOOPS | | 16364 | 2796K| 3897 (1)| 00:00:47 | | |
| 20 | NESTED LOOPS | | 16364 | 2700K| 3896 (1)| 00:00:47 | | |
| 21 | NESTED LOOPS | | 16364 | 2604K| 3895 (1)| 00:00:47 | | |
| 22 | NESTED LOOPS | | 16364 | 2508K| 3894 (1)| 00:00:47 | | |
|* 23 | HASH JOIN | | 16364 | 2413K| 3893 (1)| 00:00:47 | | |
| 24 | BUFFER SORT | | | | | | | |
| 25 | PX RECEIVE | | 16963 | 281K| 3403 (1)| 00:00:41 | | |
| 26 | PX SEND BROADCAST | :TQ10001 | 16963 | 281K| 3403 (1)| 00:00:41 | | |
| 27 | TABLE ACCESS FULL | ODF_CA_PROJECT | 16963 | 281K| 3403 (1)| 00:00:41 | | |
| 28 | NESTED LOOPS | | 16666 | 2180K| 490 (1)| 00:00:06 | | |
| 29 | NESTED LOOPS | | 16974 | 2121K| 489 (1)| 00:00:06 | | |
|* 30 | HASH JOIN RIGHT OUTER | | 16959 | 1888K| 489 (1)| 00:00:06 | | |
| 31 | PX RECEIVE | | 3725 | 52150 | 25 (0)| 00:00:01 | | |
| 32 | PX SEND HASH | :TQ10005 | 3725 | 52150 | 25 (0)| 00:00:01 | | |
| 33 | PX BLOCK ITERATOR | | 3725 | 52150 | 25 (0)| 00:00:01 | | |
|* 34 | INDEX FAST FULL SCAN| PRJ_EV_HISTORY_N1 | 3725 | 52150 | 25 (0)| 00:00:01 | | |
| 35 | BUFFER SORT | | | | | | | |
| 36 | PX RECEIVE | | 16959 | 1656K| 463 (1)| 00:00:06 | | |
| 37 | PX SEND HASH | :TQ10002 | 16959 | 1656K| 463 (1)| 00:00:06 | | |
|* 38 | HASH JOIN | | 16959 | 1656K| 463 (1)| 00:00:06 | | |
|* 39 | TABLE ACCESS FULL | ODF_OBJECT_INSTANCE_MAPPING | 16959 | 513K| 27 (0)| 00:00:01 | | |
| 40 | TABLE ACCESS FULL | INV_INVESTMENTS | 17276 | 1164K| 436 (1)| 00:00:06 | | |
|* 41 | INDEX UNIQUE SCAN | ODF_CA_INV_U1 | 1 | 14 | 0 (0)| 00:00:01 | | |
|* 42 | INDEX UNIQUE SCAN | INV_PROJECTS_U1 | 1 | 6 | 0 (0)| 00:00:01 | | |
|* 43 | INDEX UNIQUE SCAN | PAC_MNT_PROJECTS_PK | 1 | 6 | 0 (0)| 00:00:01 | | |
|* 44 | INDEX UNIQUE SCAN | ODF_CA_PROJFINPROPERTIES_PK | 1 | 6 | 0 (0)| 00:00:01 | | |
|* 45 | INDEX UNIQUE SCAN | FIN_FINANCIALS_PK | 1 | 6 | 0 (0)| 00:00:01 | | |
|* 46 | INDEX UNIQUE SCAN | ODF_CA_FINANCIALS_PK | 1 | 6 | 0 (0)| 00:00:01 | | |
| 47 | BUFFER SORT | | | | | | | |
| 48 | PX RECEIVE | | 337K| 1976K| 219 (1)| 00:00:03 | | |
| 49 | PX SEND BROADCAST | :TQ10006 | 337K| 1976K| 219 (1)| 00:00:03 | | |
| 50 | PX BLOCK ITERATOR | | 337K| 1976K| 219 (1)| 00:00:03 | | |
| 51 | INDEX FAST FULL SCAN | SRM_RESOURCES_N11 | 337K| 1976K| 219 (1)| 00:00:03 | | |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("HV"."ASSIGNMENT_ID"=SYS_OP_C2C("ODF_CA_PROJECT"."ERI_PROJ_NUMBER"))
8 - access("LAST_UPDATED_BY"="UPDATED"."USER_ID")
13 - access("CREATED_BY"="CREATED"."USER_ID")
14 - access("BASEREC"."BASELINE_ID"(+)="BASELINE_ID")
18 - filter("BASEREC"."OBJECT_TYPE"(+)='PROJECT')
23 - access("IL"."ID"="ODF_CA_PROJECT"."ID")
30 - access("EVREC"."OBJECT_ID"(+)="IL"."ID")
34 - filter("EVREC"."OBJECT_TYPE"(+)='PROJECT' AND "EVREC"."PERIOD_NUMBER"(+)=0)
38 - access("IL"."ID"="OIM7"."PRIMARY_OBJECT_INSTANCE_ID")
39 - filter("OIM7"."PRIMARY_OBJECT_INSTANCE_CODE"='project' AND "OIM7"."SECONDARY_OBJECT_INSTANCE_CODE"='financials')
41 - access("IL"."ID"="ODF_CA_INV"."ID" AND "ODF_CA_INV"."ODF_OBJECT_CODE"='project')
42 - access("IL"."ID"="INV_PROJECTS"."PRID")
43 - access("IL"."ID"="PAC_MNT_PROJECTS"."ID")
44 - access("IL"."ID"="ODF_CA_PROJFINPROPERTIES"."ID")
45 - access("FIN_FINANCIALS"."ID"="OIM7"."SECONDARY_OBJECT_INSTANCE_ID")
46 - access("FIN_FINANCIALS"."ID"="ODF_CA_FINANCIALS"."ID")

Remote SQL Information (identified by operation id):
----------------------------------------------------

7 - SELECT "SALES_ORDER","REGION","BUSINESS_UNIT","CUSTOMER_UNIT","CUSTOMER_UNIT_NAME","CUSTOMER_PROJECT_MANAGER_NAME","SPO
NSOR_TEXT","DEAL_IT_TEXT","ASSIGNMENT_ID","FINISH_DATE","UNADJUSTED_MARGIN_PERCENT","ACTUAL_COST","ACTUAL_REVENUE","ASSIGNED_C
OST","NET_SALES","ORDERS_BOOKED","UNADJUSTED_MARGIN","WIP_NET_BALANCE","RUC_NET_BALANCE","COST_OF_SALES","UM_PERCENT_SERVICES"
FROM "_SYS_BIC"."cdw.Finance.POC/GCActualCost" (accessing 'DG4OD' )




SELECT
/*+DRIVING_SITE(HV)*/
IL.CODE,
IL.NAME,
HV.ASSIGNMENT_ID ,
HV.CUSTOMER_PROJECT_MANAGER_NAME CPM ,
HV.SPONSOR_TEXT SPONSOR ,
HV.SALES_ORDER ,
HV.ORDERS_BOOKED ORDERS_BKD ,
HV.NET_SALES NET_SALES ,
HV.WIP_NET_BALANCE WIP_PROJECT ,
HV.ACTUAL_COST ACTL_COST_PROJ ,
HV.UNADJUSTED_MARGIN_PERCENT UNADJ_MARG_PERC ,
HV.UM_PERCENT_SERVICES UNDAJ_MARG_PERC_SRV ,
HV.ACTUAL_REVENUE ACT_REV_PROJ ,
HV.COST_OF_SALES COST_SALES ,
HV.UNADJUSTED_MARGIN UNADJ_MARG_PROJ ,
HV.RUC_NET_BALANCE APL_RUC ,
HV.ASSIGNED_COST APL_ASSIGN_COST ,
HV.REGION REGION ,
HV.CUSTOMER_UNIT_NAME CU ,
HV.CUSTOMER_UNIT CU_ID ,
HV.DEAL_IT_TEXT DEAL_ID ,
HV.BUSINESS_UNIT BUNIT ,
HV.FINISH_DATE FIN_DATE
FROM INV_INVESTMENTS IL
INNER JOIN ODF_PROJECT_V2 OP ON IL.ID = OP.ODF_PK
INNER JOIN "_SYS_BIC"."cdw.Finance.POC/GCActualCost"@DG4OD HV ON HV.ASSIGNMENT_ID = OP.ERI_PROJ_NUMBER

Sincerely'
Arnab
Attached File(s)
Attached File  explainplan.txt ( 11.55K ) Number of downloads: 3
 
Go to the top of the page
 
+Quote Post
burleson
post Mar 21 2017, 01:03 PM
Post #2


Advanced Member
***

Group: Members
Posts: 13,378
Joined: 26-January 04
Member No.: 13



Hi Arnab and welcome to the forum,

>> We have used DRIVING_SITE hint, but it seems somehow that hint gets ignored when we join tables from local(17000 rows) versus remote(1.5 million) rows and in the explain plan I can see only the remote query without the join/where.

Valid hints are never ignored, there must be a reason!

The local table should always be the driving table for a remote join. Oracle provides the driving_site hint for this purpose. The driving_site hint tells Oracle to make the site where the referenced table resides the driving site. You want the driving site to be the one that has the larger amount of data, so that the smaller amount of data will be passed over the network.

The driving site hint forces query execution to be done at a different site than the initiating instance. This is done when the remote table is much larger than the local table and you want the work (join, sorting) done remotely to save the back-and-forth network traffic. n this example, we use the driving_site hint to force the "work" to be done on the site where the huge table resides:

select /*+DRIVING_SITE(h)*/
ename
from
tiny_table t,
huge_table@remote h
where
t.deptno = h.deptno;


Note: The driving_site hint will not work with CTAS (create table as select) and with create materialized view syntax because they are DDL and these operations must take place on the original instance.




See my notes here on using the driving_site hint:

http://www.dba-oracle.com/t_driving_site_hint.htm




--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 25th April 2017 - 07:15 PM