remote table dblink

Symptoms

Sql plan management is not used for remote query.

How can  plan stability with queries with remote tables be applied?

Changes

No Changes.

Cause

The main purpose of the SPM is for plan stability of any SQL.

The issue with remote tables is that it has 2 components which need to be stabilized

a. the local query and

b. the remote query running on remote database.

The, execution plan for remote queries may change during run time.

Currently there is no functionality added which will include SPM / Outline / Profiles for remote tables to provide plan stability. 

So this is an expected behavior with SPM , SQL Profiles and Stored Outlines that  have a restriction on  Baselines, profiles or Stored Outlines on queries involving Remote objects.

Solution

The restriction of Plan Stability for Remote tables exists for all the methods used for stabilizing the execution plans (SQL PROFILES, STORED OUTLINES, SPM).

So this could be difficult for ORACLE to suggest any way to stabilize the complex query execution plan.

You can try using the /*+ DRIVING_SITE(<remote_table_alias)*/ hint in the complex query which will try to do most of the data processing involving remote table on the remote site. This will reduce the amount of data needed to be fetched over DB Link and will work in advancing the execution time of the queries.

Along with this you can also specify the order in which the remote and local tables should be joined in the query which will help in reproducing the good execution plan using the /*+ ORDERED */ hint.

The Good plan can be reproduced using the hints:

EXAMPLE:

=========

INSERT INTO TABLE_NAME 

SELECT /*+ ORDERED USE_NL(T1,T2,T3) */ DISTINCT T3.USERNUMBER,SUBSTR(T2.DOMAINCODE, 1, INSTR(T2.DOMAINCODE, '_', 1, 2) - 1),T4.USERNUMBER, T3.USERLOGIN, T1.DOMAINID, 'BINI' 

FROM TABLE1 T1, TABLE2 T2, TABLE3 T3, TABLE3 T4 ,TABLE4 T5 ,TABLE5 T6 

WHERE T1.DOMAINID = T2.DOMAINID AND T1.USERID = T3.USERID 

AND SUBSTR(DOMAINCODE, INSTR(T2.DOMAINCODE, '_', 1, 2) + 1) = T4.USERLOGIN 

AND T2.DOMAINCODE = :B2 || 'i_' || T4.USERLOGIN 

AND T1.USERTYPEID = 'ESTUDIANT' 

AND T2.DOMAINTYPEID = 'TUTORIA' 

AND T5.IDP = T3.USERNUMBER 

AND (T5.COD_CENTRO = :B1 OR :B1 IS NULL) 

AND T6.COD_PLAN = T5.COD_PLAN AND T6.IDP_TUTOR = T4.USERNUMBER;