RE: Slow performance of code using dblinks ver 8.1.6.2

Hi Andreas,

Thanks for your suggestion for improvement. I shall pass on this to development team for their evaluation.

Right now we are testing our migration to 8.1.6.2 and observed that slowness. We are also working with our UNIX/Hardware team to check network issue and confirm that the configuration is same as other boxes i.e. the new box is in same network loop as other production boxes as this testing is between 2 test boxes which are out of that loop. They tried to fix this problem today which resulted in some other network issue.

We are not suspecting any code problem at this stage as same code is working in 15 minutes with production box....

Thanks once again for your interest and guidance.... Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Tue, 27 Feb 2001 10:26:50 -0800

Hi Rafiq!

Thanks for sending me your PL/SQL code.
You do a lot of aggregations over the dblink, also remote joins can be slow.

Suggestion:

   Reverse the situation, do the selects local and insert remote.    Run the code on the 7.3.4.5 instance without @OLAP_APPS_LINK in selects    and insert into the same table (now with a new database link for the 8.1.6.2 instance):

   INSERT INTO HAR_ATB_ITEMS_at_8_1_6_2_instance_dblink ...    You can still invoke the code from the 8.1.6.2 instance, if you create a procedure

   on the 7.3.4.5 instance:
   replace the 'DECLARE' by
  'CREATE OR REPLACE PROCEDURE MYPROC IS'    and invoke it remotely from the 8.1.6.2 instance:

BEGIN
    MYPROC_at_8_1_6_2_instance_dblink
END;
/

This should execute the queries in the local instance only and thus consume only the local resources.
I'd like to know , if this affects performance.

Good Luck!

Andreas

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Haunschmidt Andreas VASL/FAS
   INET: Andreas.Haunschmidt_at_voest.co.at

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mohammad Rafiq
  INET: rafiq9857_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L


(or the name of mailing list you want to be removed from).  You may

also send the HELP command for other information (like subscribing).