1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Improve perfomance for Remote DB LINK

Discussion in 'SQL PL/SQL' started by pradeep84, Nov 13, 2008.

  1. pradeep84

    pradeep84 Active Member

    Messages:
    17
    Likes Received:
    1
    Trophy Points:
    80
    Hi,

    Another issue i want some help with is that when i am doing a select query in my local database to fetch using a remote db link:

    Code (Text):

    SELECT T1.csy AS POLICY_NO,
    T2.cmn AS CLIENT_ID,
    T2.GIVNAME AS GIVEN_NAME,
    T2.SURNAME AS SURNAME,
    TO_CHAR(T2.CLTDOB,'DD/MM/YYYY') AS DATEOFBIRTH,
    DECODE(T2.CLTTYPE,'P','Personal','C','Corporate') CLIENTTYPE,
    T2.sap AS ,
    T2.PHONE02 AS PHONE,
    T2.PCODE AS PINCODE
    FROM chman@ODS_UAT_PS T1, emp@ODS_UAT_PS T2
    WHERE T1.man = T2.cman
     
    this query and other querries using dblink are taking an awful lot of time.

    how can i improve its performance and whether i should use hints or any other option is there?
     
  2. simply_dba

    simply_dba Forum Advisor

    Messages:
    95
    Likes Received:
    5
    Trophy Points:
    140
    Location:
    Kolkata, India
    Execute the query in the remote db locally take out the explain plan and post it here. Also post the table statistics (size, no. of rows etc) and whether the statistics are updated or not.

    One more thing, Execute the query both locally and using dblink and post time taken in both cases.