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

jhking wrote:

> Environment Host: Windows 2k, Oracle 9.0.2.5. 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.

My thought would be to fix the "flakey" frame relay problem. do you know what the problem is or does the telco guy just keep guessing? Sometimes you have to pound on the telco provider until they send the tech that really knows how to troubleshoot to the site to correct it. Been there, done that far too often.

While you can't always guarantee that the link will be up, it shouldn't be down very often. And if the data is that important, have a second vendor add a second connection that would be used for "fail-over".

-- 
Michael Austin.
Consultant - Available.
Donations welcomed. http://www.firstdbasource.com/donations.html
:)