Inserting over database links

[commatom]

I need some fuel for my fire here.

Im at a site that is keeping their staging tables in a separate instance from their reporting tables on the same server. In their eyes, they are doing it for organizational purposes and don't think there are any performance impacts. The nightly processes insert data from staging to the reporting tables over DB Links. They arent in production yet so Im hoping I can get them to change their minds now.

Im going to create some test cases to prove that this is a bad design but am interested in any other feedback you folks may have.

 

[SantaMufasa]

One test is worth 100 expert opinions.

I recommend building generic synonym references into your application, then you can assign the synonym to a db link, then to a schema reference (without the db link), testing the response time for each method without changing application code.


This way, your design team will have facts for your environment for them to make an informed design decision.

Be sure to let us know the results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Thargy]

You might also consider eliminating the staging tables completely and using external tables.

Obviously there may be reasons why this isn't being done, but removing an entire stage of a process is almost certain to speed things up.

Regards

T

Grinding away at things Oracular

 

[commatom]

Good point thargtheslayer however the the stage tables are loading by another group with a different process.

By the time I start, my source tables are already in Oracle.

 

[commatom]

Interesting, it didnt seem to matter on development but once we got to production we saw a big difference.


USING THE DB LINK TO MOVE DATA IN

  1  select v_step, action, to_char(timestamp, 'hh24:mi')
  2  from
  3  process_control_detail
  4* where timestamp > trunc(sysdate)

    V_STEP ACTION                         TO_CH
---------- ------------------------------ -----
        10 START_PROC                     01:00
        20 FINDING MISSING FILEIDs        01:00
        30 20 FILEIDs WILL BE PROCESSED   01:01
        40 GATHERING DATA                 01:01
        50 INSERTED 43764 ROWS            02:36

As you can see, the SQL and insert took over an hour and a half.

Running the exact same SQL but moving the tables out of the DB Link to the same instance.

        10 START_PROC                     09:36
        20 FINDING MISSING FILEIDs        09:36
        30 20 FILEIDs WILL BE PROCESSED   09:38
        40 GATHERING DATA                 09:38
        50 INSERTED 43764 ROWS            09:48

It only took 10 minutes this time.

So here are my questions.

1) There was no performance hit when using the DB Link on DEV. Any suggestion on a parameter to look at that could cause a slowdown?

2) If we are simply copying the table over using the DB link, it is very fast. What is it about making a couple joins that slows us down so much?

3) Are there CBO issues with db links i.e. The explain plan generated by Oracle when using db links are inconsistent?

Additional info-

We have both instances on the same server using the same store.

 

[commatom]

OK so really Im not trying to bump this thread, I just have more information.

Now when I run the SQL without the insert on DEV (where the problem doesnt exist) the explain plan is almost exactly the same as PROD. They both return the data quickly. It only when I add the insert piece of the SQL does this thing go "whacky"

So to recap

I am operating from database instance A and gathering data from both database instance A and database instance B and inserting into database instance B. Both DEV and PROD have the same data and DEV runs in about 10 minutes and PROD runs in about 90 minutes.

I have gone in and deleted statistics on all the tables and regathered stats and the explain plans have not changed.

When I look at the insert statement there is a big difference between dev and prod and the biggest thing I see is that the driving table which is on db instance A is deep in a nested loop on PROD and the opposite on DEV. (grr that didnt make sense did it)

Anyway, im stumped so any suggestion will be tried at this point.

 

[Thargy]

tom,

is there a difference between the number of records in the DEV and PROD tables? The reason I ask is that if there is, the behind the scenes overhead of updating large indexes might be slowing down the insert in PROD.

T

Grinding away at things Oracular

 

[commatom]

Good point but no they are the same.

So I think its working now. I deleted stats and then regathered using dbms_stats on the table level but it didnt seem to work. (according the the explain plan created by TOAD)

We decided to re-run the whole procedure and a little while later and bam! it worked!

So I reran the explain and it looked better. YAY!

So I think my dbms stats run worked. I wonder if TOAD had the explain plan in cache and it didn't completely refresh.

Regardless, its working now so thanks for the suggestions.

 

[dbtoo2001]

So here are my questions.

1) There was no performance hit when using the DB Link on DEV. Any suggestion on a parameter to look at that could cause a slowdown?

2) If we are simply copying the table over using the DB link, it is very fast. What is it about making a couple joins that slows us down so much?

3) Are there CBO issues with db links i.e. The explain plan generated by Oracle when using db links are inconsistent?

Your questions are geared towards how oracle handles distributed query...

http://download.oracle.com/docs/cd/B10501_01/server.920/a96521/ds_concepts.htm#13003