Gunnar,
Please do not discard dblinks so readily.
As in many situations, I suspect that they are blamed for �slowness� when it
is really the application code that is at fault. All too often, people will
write PL/SQL code that hasn�t a hope in hell of performing well, then put a
database link into the mix and blame the poor performance on that. A good
example of this would be:
declare
cursor get_rows
is
select col1, col2, col3, ..., coln
from [EMAIL PROTECTED];
begin
for x in get_rows loop
insert into dest_table (col1, col2, col3, ..., coln)
values (x.col1, x.col2, x.col3, ..., x.coln);
end loop;
end;
/
Straight row-by-row processing, no BULK operations, no direct-path -- none
of the things you'd want to do when moving bulk volumes of data! It's not
the dblink that is slow, although the impact of latency is accentuated by
the repeated and unnecessarily frequent dblink traffic. But code like this
will perform poorly even if database links were not involved at all.
Try using parallel direct-path INSERT /*+ APPEND PARALLEL NOLOGGING */
operations to �pull� the data across the database link. I am pretty certain
that you�ll like the results and it�ll be a heck of a lot easier to program
than reverse-engineering the two-phase commit protocol that you get for
free. Data movement using "flat files" is an unnecessarily painful way to
die; one thing goes wrong and you're never sure if you've ever gotten
things right.
In straight SQL*Plus code it might look something like this:
whenever sqlerror exit failure rollback
set echo on feedback on timing on
spool pull_data
alter session enable parallel dml;
insert /*+ append parallel(y, 4) nologging */
into dest_table y
(col1, col2, col3, ..., coln)
select /*+ full(x) parallel(x, 4) */
col1, col2, col3, ..., coln
from [EMAIL PROTECTED] x;
exit success commit
If for some reason you are not permitted to perform direct-path inserts on
the "dest_table", you can still make use of the BULK operations introduced
to PL/SQL in Oracle8i. Not the best, but still quite good. If you are not
yet running Oracle8i or above, you could still simulate the effect
(somewhat) by making use of PL/SQL arrays (a.k.a. table types) to select
data into, transfer across the dblink, and insert from. There are plenty of
alternatives to speed bulk operations across dblinks, regardless of what
version of the RDBMS you are using...
Also, it might help (very slightly) if the database link used for this
purpose is defined using a TNS-string that raises the SDU and TDU parameters
in SQL*Net, but don't expect a great deal of difference from this measure.
As with anything else, writing appropriate application code has the best
impact on performance.
Hope this helps...
-Tim
on 10/19/03 10:39 PM, Gunnar Berglund at [EMAIL PROTECTED] wrote:
> Hi all,
>
> we have an application which needs data from other environment (which is
> actually SAP db). Currently we have implemented it the way we create flat
> files and put them in using pl/sql -procedures but I don't like this because
> the data in the flat files are "visible" and it is somehow "secret".
>
> What other options we might have if we do not want to use db links (because of
> its slowness.
>
> I very much appreciate all your suggestions...
>
> TIA
> gb
>
> Want to chat instantly with your online friends?�Get the FREE Yahoo! Messenger
> <http://uk.rd.yahoo.com/mail/tagline_messenger/*http://uk.messenger.yahoo.com/
> >
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Tim Gorman
INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com
San Diego, California -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (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).
