OraFAQ Forum: Performance Tuning » Driving site hint on a single remote table

Home » RDBMS Server » Performance Tuning » Driving site hint on a single remote table (OEL 6.5,Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Driving site hint on a single remote table [message #655503]

Thu, 01 September 2016 01:40

swas_recall
Messages: 12
Registered: May 2012
Location: Bangalore

Junior Member

Hi All

I am trying to pull data from remote table via DBLINK and using bulk collect method for the same.

The table extractions are based on one to one basis without any joins.

Wanted to understand can driving site hint be useful?

If i run a explain plan ,it does not uses the driving siite Hint,so i am wondering why the optimiser is not using it

If i use it for a table extraction which has 12 MILLION RECS ,Will the optimiser use it.

Is there any restriction on single table extraction?Does driving site hint only work on remote table extractions javing joins only?

Please advise.

Report message to a moderator

Re: Driving site hint on a single remote table [message #655509 is a reply to message #655503]

Thu, 01 September 2016 02:22

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

It is hard to tune SQL that one cannot see Smile

Please user [code] tags when you post the code and the execution plan.

Report message to a moderator

Re: Driving site hint on a single remote table [message #655510 is a reply to message #655509]

Thu, 01 September 2016 02:29

swas_recall
Messages: 12
Registered: May 2012
Location: Bangalore

Junior Member

My procedure goes as below which is without driving_site hint.

PROCEDURE PRC_C2B_USERS 
Is
VSTR VARCHAR2(10000);
BEGIN
VSTR:=q'{
DECLARE
TYPE RT IS RECORD
(
ID        C2B_USERS.ID%TYPE,         
LOGINID    C2B_USERS.LOGINID%TYPE,       
ORGID      C2B_USERS.ORGID%TYPE,        
MBOXID      C2B_USERS.MBOXID%TYPE,           
NAME        C2B_USERS.NAME%TYPE,           
TITLE       C2B_USERS.TITLE%TYPE,           
FORENAMES   C2B_USERS.FORENAMES%TYPE,           
JOB         C2B_USERS.JOB%TYPE,           
TEL          C2B_USERS.TEL%TYPE,          
TEL2         C2B_USERS.TEL2%TYPE,          
FAX         C2B_USERS.FAX%TYPE,           
ADDRESS       C2B_USERS.ADDRESS%TYPE,         
ADDRESS2     C2B_USERS.ADDRESS2%TYPE,          
TOWN         C2B_USERS.TOWN%TYPE,          
COUNTY        C2B_USERS.COUNTY%TYPE,         
POSTCODE      C2B_USERS.POSTCODE%TYPE,         
COUNTRY           C2B_USERS.COUNTRY%TYPE,     
PASSWD_DATE_CHANGE C2B_USERS.PASSWD_DATE_CHANGE%TYPE,    
RADIUS_ACC_ID     C2B_USERS.RADIUS_ACC_ID%TYPE,     
CREATED       C2B_USERS.CREATED%TYPE,         
FIRSTLOGON   C2B_USERS.FIRSTLOGON%TYPE,          
LASTLOGON    C2B_USERS.LASTLOGON%TYPE,          
STATUS      C2B_USERS.STATUS%TYPE,            
DIRECTORY   C2B_USERS.DIRECTORY%TYPE,           
INFO       C2B_USERS.INFO%TYPE,            
OPSYSTEM   C2B_USERS.OPSYSTEM%TYPE,            
MAXTIME     C2B_USERS.MAXTIME%TYPE,           
USEDTIME    C2B_USERS.USEDTIME%TYPE,           
MAXCONN     C2B_USERS.MAXCONN%TYPE,           
UNIX_UID    C2B_USERS.UNIX_UID%TYPE,           
CONSENT      C2B_USERS.CONSENT%TYPE,          
LAST_NET_LOGON    C2B_USERS.LAST_NET_LOGON%TYPE,     
DGID         C2B_USERS.DGID%TYPE,           
PARTNERS_CONSENT  C2B_USERS.PARTNERS_CONSENT%TYPE,     
E2E_ADDRESS     C2B_USERS.E2E_ADDRESS%TYPE,       
UCG_DATA   C2B_USERS.UCG_DATA%TYPE            
);

TYPE TAB_NT IS TABLE OF RT;
V_NT TAB_NT;

CURSOR C_C2B_USERS IS
SELECT 
ID,LOGINID,ORGID,MBOXID,NAME,TITLE ,FORENAMES ,JOB ,TEL,TEL2,FAX ,ADDRESS,ADDRESS2 ,TOWN ,COUNTY ,POSTCODE,COUNTRY ,PASSWD_DATE_CHANGE,RADIUS_ACC_ID ,CREATED  ,         
FIRSTLOGON,LASTLOGON ,STATUS ,DIRECTORY,INFO,OPSYSTEM ,MAXTIME,USEDTIME,MAXCONN,UNIX_UID,CONSENT ,          
LAST_NET_LOGON ,DGID,PARTNERS_CONSENT , E2E_ADDRESS,UCG_DATA   
FROM  C2B_USERS_SYN;

BEGIN
OPEN C_C2B_USERS;
LOOP
   FETCH C_C2B_USERS BULK COLLECT INTO V_NT LIMIT  5000;
   EXIT WHEN V_NT.COUNT=0;
   FORALL I IN V_NT.FIRST..V_NT.LAST
   INSERT INTO C2B_USERS VALUES V_NT(I);
   COMMIT;
 END LOOP;
 END;
}';

Explain Plan for the cursor query as below

EXPLAIN PLAN FOR
SELECT  /*driving_site(a) */
ID,LOGINID,ORGID,MBOXID,NAME,TITLE ,FORENAMES ,JOB ,TEL,TEL2,FAX ,ADDRESS,ADDRESS2 ,TOWN ,COUNTY ,POSTCODE,COUNTRY ,PASSWD_DATE_CHANGE,RADIUS_ACC_ID ,CREATED  ,         
FIRSTLOGON,LASTLOGON ,STATUS ,DIRECTORY,INFO,OPSYSTEM ,MAXTIME,USEDTIME,MAXCONN,UNIX_UID,CONSENT ,          
LAST_NET_LOGON ,DGID,PARTNERS_CONSENT , E2E_ADDRESS,UCG_DATA   
FROM  C2B_USERS_SYN a;

Plan table output as below.

Plan hash value: 3461732445
 
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|       |  4683K|   920M| 46931   (1)| 00:09:24 |        |
|   1 |  TABLE ACCESS FULL     | USERS |  4683K|   920M| 46931   (1)| 00:09:24 |  CDBPR |
-----------------------------------------------------------------------------------------
 
Note
-----
   - fully remote statement

Hence i was trying to understnad why the process does not uses the driving site hint if its a single remote table without any local table join.

thanks

Report message to a moderator

Re: Driving site hint on a single remote table [message #655512 is a reply to message #655510]

Thu, 01 September 2016 02:40

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Is this
/*driving_site(a) */
meant to be a hint? It isn't, you need the + symbol or it is interpreted as a comment. However, I don not see how it can be relevant for a statements with neither joins or filters. Can you explain further?

Report message to a moderator

Re: Driving site hint on a single remote table [message #655514 is a reply to message #655512]

Thu, 01 September 2016 02:44

swas_recall
Messages: 12
Registered: May 2012
Location: Bangalore

Junior Member

Hi John

Thanks,i missed the + symbol actually.

Even with the + Symbol i go not see the hint being used.i had thought even without a join or a filter it will still use the driving_site hint,does this mean the local site does all the work when a full table is pulled from a remote site without any other table joins or filters?

Plan hash value: 3461732445
 
-----------------------------------------------------------------------------------------
| Id  | Operation              | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Inst   |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT REMOTE|       |  4683K|   920M| 46931   (1)| 00:09:24 |        |
|   1 |  TABLE ACCESS FULL     | USERS |  4683K|   920M| 46931   (1)| 00:09:24 |  CDBPR |
-----------------------------------------------------------------------------------------
 
Note
-----
   - fully remote statement

Report message to a moderator

Re: Driving site hint on a single remote table [message #655516 is a reply to message #655514]

Thu, 01 September 2016 02:48

John Watson
Messages: 8964
Registered: January 2010
Location: Global Village

Senior Member

Hints control, or influence, optimization. For a query such as yours, there is no optimization possible. So I don't see what effect the hint could have.

Report message to a moderator