SQL*Net message to dblink , SQL*Net message from dblink

This blog is written based on the issue faced due to huge response time of sql query waiting on dblink.

Issue:

Query responding in almost 5 min with session wait event of SQL*Net message to dblink / SQL*Net message from dblink

Analysis:

T3157 was a view calling one more view XXECMS_SM_DTLS_VL_ATS

Observing the DDL of XXECMS_SM_DTLS_VL_ATS it has been identified that it is formed with a complex query having remote references via db link.

Each table being referred over db link was holding more than 50 lac records & sizing over 4 GB each.

View: T3157 

CREATE OR REPLACE FORCE VIEW "ADMIN"."T3157" ("C1", "C536870913", "C536870914", "C536870915",”C536870916", "C536870917", "C536870918") AS SELECT ORG_PARTY_ID,SERVICE_CUSTOMER,SM_EMAIL_ID,SM_FIRST_NAME,SM_LANDLINE_NO,SM_LAST_NAME,SM_MOBILE_NO FROM XXECMS_SM_DTLS_VL_ATS 

View: XXECMS_SM_DTLS_VL_ATS

CREATE OR REPLACE FORCE VIEW "ADMIN"."XXECMS_SM_DTLS_VL_ATS" ("ORG_PARTY_ID", "SERVICE_CUSTOMER", "SM_FIRST_NAME", "SM_LAST_NAME", "SM_EMAIL_ID", "SM_MOBILE_NO", "SM_LANDLINE_NO") AS

SELECT DISTINCT hp1.party_id  Org_Party_Id ,hp1.party_number Service_Customer , hp.person_first_name  SM_First_Name , hp.person_last_name  SM_Last_Name  , max(decode(hc.contact_point_type||'-'||hc.phone_line_type  ,'EMAIL',hc.email_address,NULL)) SM_Email_ID

, max (decode (hc.contact_point_type||'-'||hc.phone_line_type,

'PHONE-MOBILE',(nvl(hc.phone_country_code,'00')||'-'||hc.phone_number),NULL)) SM_Mobile_No, max (decode (hc.contact_point_type||'-'||hc.phone_line_type ,'PHONE-GEN',(nvl(hc.phone_country_code,'00')||'-'||nvl(hc.phone_area_code,'00')||

'-'||hc.phone_number), NULL)) SM_Landline_No

        FROM apps.hz_parties@REM2CRM_DBLINK hp,

             apps.hz_relationships@REM2CRM_DBLINK hr,

             apps.hz_contact_points@REM2CRM_DBLINK hc,

             apps.hz_parties@REM2CRM_DBLINK hp1

         WHERE hr.subject_id = hp.party_id

         AND hr.object_id = hp1.party_id

         AND hr.subject_type = 'PERSON'

         AND hr.object_type= 'ORGANIZATION'

         AND hr.relationship_code ='SERVICE_MANAGER_OF'

         AND hr.directional_flag = 'F'

         AND hr.status = 'A'

         AND hc.owner_table_id = hr.party_id

         AND hc.owner_table_name='HZ_PARTIES'

         AND hc.status = 'A'

 GROUP BY hp1.party_id, hp1.party_number, hp.person_first_name, hp.person_last_name

Execution Plan found to be proper/as expected.

Cause:

The join condition of the view was fetching each distinct row of the table over the DB link/Network at destination DB for processing.

Due to huge number of rows being transferred over the network (from source->destination) delay was observed in processing joins within the destination view

Solution:

As sql joins were processed at destination (local) DB & huge number of traffic flow at local DB over the network caused  the slowness.

A logic has been implemented to process all joins at the source (remote) DB itself & retrieve only processed o/p over the db link

For which an inline query has been extracted from original view using Db link from destination & view has been created on Source Database.

Original view has been modified to use the inline query view created in source via Db link

On Source: A view to process all joins on source

CREATE OR REPLACE VIEW "TEST_123" ("ORG_PARTY_ID", "SERVICE_CUSTOMER", "SM_FIRST_NAME", "SM_LAST_NAME", "SM_EMAIL_ID", "SM_MOBILE_NO", "SM_LANDLINE_NO") AS

SELECT DISTINCT hp1.party_id  Org_Party_Id ,hp1.party_number Service_Customer , hp.person_first_name  SM_First_Name , hp.person_last_name  SM_Last_Name  , max(decode(hc.contact_point_type||'-'||hc.phone_line_type  ,'EMAIL',hc.email_address,NULL)) SM_Email_ID

, max (decode (hc.contact_point_type||'-'||hc.phone_line_type,

'PHONE-MOBILE',(nvl(hc.phone_country_code,'00')||'-'||hc.phone_number),NULL)) SM_Mobile_No, max (decode (hc.contact_point_type||'-'||hc.phone_line_type ,'PHONE-GEN',(nvl(hc.phone_country_code,'00')||'-'||nvl(hc.phone_area_code,'00')||

'-'||hc.phone_number), NULL)) SM_Landline_No

        FROM apps.hz_parties hp,

             apps.hz_relationships hr,

             apps.hz_contact_points hc,

             apps.hz_parties hp1

         WHERE hr.subject_id = hp.party_id

         AND hr.object_id = hp1.party_id

         AND hr.subject_type = 'PERSON'

         AND hr.object_type= 'ORGANIZATION'

         AND hr.relationship_code ='SERVICE_MANAGER_OF'

         AND hr.directional_flag = 'F'

         AND hr.status = 'A'

         AND hc.owner_table_id = hr.party_id

         AND hc.owner_table_name='HZ_PARTIES'

         AND hc.status = 'A' 
GROUP BY hp1.party_id, hp1.party_number, hp.person_first_name, hp.person_last_name;

 

On Destination: Modification in destination view to call source view performing all joins

CREATE OR REPLACE FORCE VIEW "ADMIN"."XXECMS_SM_DTLS_VL_ATS" ("ORG_PARTY_ID", "SERVICE_CUSTOMER", "SM_FIRST_NAME", "SM_LAST_NAME", "SM_EMAIL_ID", "SM_MOBILE_NO", "SM_LANDLINE_NO") AS

SELECT * FROM apps.TEST_123@REM2CRM_DBLINK;

Query started responding in 0.03 seconds

Conclusion

Any complex query involving joins on large table executed over db link will suffer due to n/w delay, so it is best practice to create view of the inline queries on source itself

Alternate Approach:

An alternate approach is to add Hint /*+ DRIVING_SITE */ within the destination view as below .

CREATE OR REPLACE FORCE VIEW "ADMIN"."XXECMS_SM_DTLS_VL_ATS" ("ORG_PARTY_ID", "SERVICE_CUSTOMER", "SM_FIRST_NAME", "SM_LAST_NAME", "SM_EMAIL_ID", "SM_MOBILE_NO", "SM_LANDLINE_NO") AS

SELECT DISTINCT /*+ DRIVING_SITE (hr)*/  /*+ DRIVING_SITE (hc)*/ /*+ DRIVING_SITE (hp1)*/    hp1.party_id  Org_Party_Id ,hp1.party_number Service_Customer , hp.person_first_name  SM_First_Name , hp.person_last_name  SM_Last_Name  , max(decode(hc.contact_point_type||'-'||hc.phone_line_type  ,'EMAIL',hc.email_address,NULL)) SM_Email_ID

, max (decode (hc.contact_point_type||'-'||hc.phone_line_type,

'PHONE-MOBILE',(nvl(hc.phone_country_code,'00')||'-'||hc.phone_number),NULL)) SM_Mobile_No, max (decode (hc.contact_point_type||'-'||hc.phone_line_type ,'PHONE-GEN',(nvl(hc.phone_country_code,'00')||'-'||nvl(hc.phone_area_code,'00')||

'-'||hc.phone_number), NULL)) SM_Landline_No

        FROM apps.hz_parties@REM2CRM_DBLINK hp,

             apps.hz_relationships@REM2CRM_DBLINK hr,

             apps.hz_contact_points@REM2CRM_DBLINK hc,

             apps.hz_parties@REM2CRM_DBLINK hp1

         WHERE hr.subject_id = hp.party_id

         AND hr.object_id = hp1.party_id

         AND hr.subject_type = 'PERSON'

         AND hr.object_type= 'ORGANIZATION'

         AND hr.relationship_code ='SERVICE_MANAGER_OF'

         AND hr.directional_flag = 'F'

         AND hr.status = 'A'

         AND hc.owner_table_id = hr.party_id

         AND hc.owner_table_name='HZ_PARTIES'

         AND hc.status = 'A'

  GROUP BY hp1.party_id, hp1.party_number, hp.person_first_name, hp.person_last_name;

Hint will work similar like earlier approach, but I observed marginal excess in response time. It will reduce the code changes effort