Re: Data Transfer between two instances

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    source_table_at_dblink;
        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    source_table_at_dblink 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 oracledbasweden_at_yahoo.co.uk 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: tim_at_sagelogix.com

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