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

yeah, trace it - see where the time is taken

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

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.

Why dont u do/try this:

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

Cheers

Do you have a working solution yet? I am having the same issue.
My remote database is 9I and the local database is 8i.

:confused: 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
:)