DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Poor Performance Across DBLINK

  1. #1
    Join Date
    Jul 2006
    Location
    Midwest
    Posts
    4

    Poor Performance Across DBLINK

    See SQL statement #1 below. When I run only the SELECT portion of the statement, the run time is acceptable (1 mins., 57 secs.).

    However, when I add the INSERT, the run time is absolutely unacceptable (38 mins., 5 secs.).

    Adding the DRIVING_SITE hint had absolutely no effect. I also tried adding a "COLLOCATED INLINE VIEW" which had no effect, see SQL statement #2 below.

    Changing the order of the optimizer hints also has little, if any, effect.

    Adding temp tables or views on the remote database is not an option as it is a production database and I do not have CREATE privilege.

    Does anybody have any suggestions?????

    -- SQL Statement #1
    INSERT INTO TEMP.T_LIAR_ADDRESS
    *SELECT /*+ INDEX(AR,PK_T_LIAR_ADDRESS) *
    DRIVING_SITE(AR) */
    ******* AR.*,
    ******* TO_DATE('2006-07-15', 'yyyy-mm-dd'),
    ******* SYSDATE
    *FROM DBO.T_LIAR_ADDRESS@LIFECAD.SBL.COM AR
    *WHERE AR.ar_address_id IN (SELECT DISTINCT
    ********************************** PR.pr_address_id
    *************************** FROM DBO.T_LIPR_POLICY_ROLE@LIFECAD.SBL.COM PR
    *************************** WHERE PR.tstamp > (SELECT DT.CMP_DATE
    ********************************************** FROM CORE.DT_COMPDATE@LIFECAD.SBL.COM DT)) OR
    ****** AR.tstamp > (SELECT DT.CMP_DATE
    ******************* FROM CORE.DT_COMPDATE@LIFECAD.SBL.COM DT)

    -- SQL Statement #2
    INSERT INTO TEMP.T_LIAR_ADDRESS
    SELECT *
    FROM (SELECT /*+ INDEX(AR,PK_T_LIAR_ADDRESS)
    DRIVING_SITE(AR) */
    AR.*,
    TO_DATE('2006-07-15', 'yyyy-mm-dd'),
    SYSDATE
    FROM DBO.T_LIAR_ADDRESS@LIFECAD.SBL.COM AR
    WHERE AR.ar_address_id IN (SELECT DISTINCT
    PR.pr_address_id
    FROM DBO.T_LIPR_POLICY_ROLE@LIFECAD.SBL.COM PR
    WHERE PR.tstamp > (SELECT DT.CMP_DATE
    FROM CORE.DT_COMPDATE@LIFECAD.SBL.COM DT)) OR
    AR.tstamp > (SELECT DT.CMP_DATE
    FROM CORE.DT_COMPDATE@LIFECAD.SBL.COM DT)) V

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    yeah, trace it - see where the time is taken

  3. #3
    Join Date
    Jul 2006
    Location
    Midwest
    Posts
    4
    I restructured my load process to load a Temp table on the REMOTE database, then load the table on the LOCAL database from the temp table on the REMOTE, see below.

    As you can see the load of the temp table from REMOTE to LOCAL took less than 1 second.

    It seems obvious that Oracle is NOT following the DRIVING_SITE hint directive.

    I am not the DBA and do not have the authority to run a trace.

    --------- SAMPLE SQL ---------
    ---- To load this table across the DBLINK took 39 min., 9 sec
    -- Run on COREPRD
    INSERT INTO HOAR.T_LIAR_ADDRESS
    SELECT /*+ INDEX(AR,PK_T_LIAR_ADDRESS)
    DRIVING_SITE(AR) */
    AR.*,
    TO_DATE('2006-07-15', 'yyyy-mm-dd'),
    SYSDATE
    FROM DBO.T_LIAR_ADDRESS@LIFECAD.SBL.COM AR,
    DT_COMPDATE@LIFECAD.SBL.COM
    WHERE AR.ar_address_id IN (SELECT DISTINCT
    PR.pr_address_id
    FROM DBO.T_LIPR_POLICY_ROLE@LIFECAD.SBL.COM PR,
    CORE.DT_COMPDATE@LIFECAD.SBL.COM DT
    WHERE PR.tstamp > DT.CMP_DATE) OR
    AR.tstamp > CMP_DATE

    --------------------------------------------------------

    ---- Load Address table -- Load Time approx. 2 min., 50 sec. ; 1,213 rows
    -- Run on LIFECAD
    INSERT INTO CORE.TEMP_T_LIAR_ADDRESS
    SELECT /*+ INDEX(AR,PK_T_LIAR_ADDRESS) */
    AR.*,
    SYSDATE - 1,
    SYSDATE
    FROM DBO.T_LIAR_ADDRESS AR
    WHERE AR.ar_address_id IN (SELECT DISTINCT
    PR.pr_address_id
    FROM DBO.T_LIPR_POLICY_ROLE PR
    WHERE PR.tstamp > (SELECT DT.CMP_DATE
    FROM CORE.DT_COMPDATE DT)) OR
    AR.tstamp > (SELECT DT.CMP_DATE
    FROM CORE.DT_COMPDATE DT)

    ---- Load Address table from Temp table on LIFECAD - Load time less that 1 sec.
    -- Run on COREPRD
    INSERT INTO HOAR.T_LIAR_ADDRESS
    SELECT *
    FROM CORE.TEMP_T_LIAR_ADDRESS@LIFECAD.SBL.COM

  4. #4
    Join Date
    Jul 2002
    Location
    Northampton, England
    Posts
    612
    Quote Originally Posted by khoar
    It seems obvious that Oracle is NOT following the DRIVING_SITE hint directive.
    "hint" and "directive" are a slight contradiction in terms:

    Main Entry: directive
    Part of Speech: noun
    Definition: command
    Synonyms: charge, communication, decree, dictate, edict, injunction, mandate, memo, memorandum, message, notice, order, ordinance, regulation, ruling, ukase, word

    Main Entry: hint
    Part of Speech: noun
    Definition: indication
    Synonyms: adumbration, advice, allusion, announcement, clue, communication, connotation, denotation, evidence, glimmering, help, idea, implication, impression, inference, information, inkling, innuendo, insinuation, intimation, iota, lead, mention, notice, notion, observation, omen, pointer, print, reference, reminder, scent, sign, signification, smattering, suggestion, suspicion, symptom, taste, telltale, tinge, tip, tip-off*, token, trace, warning, whiff*, whisper, wink*, wrinkle*

    A hint is just what it sounds like. A suggestion to the optimizer to point it in the right direction. If Oracle decides that your hint isn't suitableit will ignore it.

    Get your DBA to trace it for you to see what event(s) you are waiting on.
    Assistance is Futile...

  5. #5
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Why dont u do/try this:

    1) CURSOR SELECT
    2) INSERT USING BULK COLLECT OPTION with LIMIT option

    Cheers
    Cheers!
    OraKid.

  6. #6
    Join Date
    Oct 2006
    Posts
    1
    Do you have a working solution yet? I am having the same issue.
    My remote database is 9I and the local database is 8i.

  7. #7
    Join Date
    Oct 2006
    Posts
    1

    Smile

    How did it come about?It is covering me now.
    Thank you for your post in advance and wish everyone coming here have a nice weeekend!
    RGDS,
    Algogo.com
    Attached Images Attached Images

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width