Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: suggestions for poor-man's datawarehouse performance problem

Re: suggestions for poor-man's datawarehouse performance problem

From: Mark Townsend <>
Date: Fri, 16 Jul 2004 04:37:01 GMT
Message-ID: <MXIJc.89952$IQ4.82464@attbi_s02>

Try the COPY command from SQL*Plus ? It will probably be slower (you don't sya how much data you have) but may be better able to cope with your network outages. See and note the SET COPYCOMMIT command

Jason King wrote:

> Ana,
> I appreciate the thought, but Change Data Capture needs set up on the 
> remote sites which I can't change and are on 8i.
> Ana C. Dent wrote:

>> jhking <> wrote in
>> news:cd7ald$
>>> Environment Host: Windows 2k, Oracle Remote Sites Windows 2k
>>> Oracle 8.1.7.
>>> Connection over somewhat flakey frame relay system.
>>> My objective is to gather new/updated data from each remote site and
>>> aggregate it on the host. The first issue is that for political
>>> reasons I can't change anything on the remote sites (which kills
>>> replication or triggers + queues). So I create dblinks to the remote
>>> sites and drive the updates from the host. My first answer, do a
>>> insert into .. select from for each table I need into staging tables
>>> and then move from the staging tables into aggregate tables. For the
>>> sites where the connection stays up this is fine, but if the
>>> connection dies during the insert into .. select from the session
>>> hangs. I've done google and metalink research to find a solution to
>>> that but AFAICT that's not solvable. My second answer is to use
>>> cursors and fetch a row at a time. Disconnects result in raised
>>> errors (which I can deal with) but the performance has become
>>> abysmal. I did some tests with bulk fetch and it seems to behave
>>> like insert into .. select from hanging on disconnects.
>>> I can do virtually anything to the host box and virtually nothing to
>>> the remote boxes. Any suggestions on how to make insert into ..
>>> select from work in this fragile environment or alternative
>>> approaches I could take to getting the data aggregated would be welcome.
>> Change Data Capture feature in 9i
Received on Thu Jul 15 2004 - 23:37:01 CDT

Original text of this message