Re: copy data between instances

Thanks for bringing that up. I experimented with database links and found that I could not get parallel performance improvements from my queries. My tests used the "create table as select" with the unrecoverable clause. For example

CREATE TABLE NEW_TABLE
PARALLEL ( DEGREE 4 )
UNRECOVERABLE
AS
SELECT *
FROM OLD_TABLE_at_SOURCE; Copying a table inside a single instance, I was able to significantly improve performance by including the parallel clause (from ~90 seconds to ~15 seconds for a 30MB table). But when performing the same statement on a remote table, the parallel clause had no affect on performance. The Oracle Support site confirmed that the parallel clause was ignored when a database link was used (at least in 7.3 and 8.0, which are the only two I'm interested in now). I also tried to simultaneously copy three individual ~30MB tables between databases across a database link to see if I create parallelism that way, but repeatedly found that one table took ~90 seconds, the second took ~180 seconds and the third took ~270 seconds, leading me to believe that I would never be able to get parallel performance through a database link. Perhaps someone can offer suggestions to improve on this situation. Of course this option would still omit the benefits of direct path loading.

Both the source and target databases are currently 7.3.4. Eventually the target database will be 8.0.6.

Brian Gastineau
Gulf Coast Regional Blood Center

In article <971162735.121783_at_zenana.dcz.bekaert.com>,   "Thierry Poels" <Thierry.Poels_at_nospam> wrote:
> Hi,
>
> how about a simple database link ??
> You'll be able to access the tables in the other database (more or
 less)
> like any local table.
>
> regards,
> Thierry Poels
> HP-UX Sysadmin & Oracle DBA.
>
> bdg_at_hotmail.com wrote in message <8rthfj$phj$1_at_nnrp1.deja.com>...
> >Do any utilities exist that would provide SQL*Loader performance, but
> >use another Oracle database (on same host) as the data source instead
 of
> >a text file? Essentially I would like the basic export/import
> >functionality, without having to create an interim dump file. Import
> >also seems to be lacking parallel and direct path performance
 features.
> >
> >Brian Gastineau
> >Gulf Coast Regional Blood Center
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 10 2000 - 10:07:11 CDT