Re: Selects via DB Links - Special Tuning Considerations

There are always problems with db-links, and in the older versions of Oracle the optimizer does not do very well with optimising.

I can't remember what 8.0 is able to do, but if you are running cost-based, then 8.0 will NOT be able to use any histogram information at the 10.1 end of the link; it may not be able to pull ANY of the statistics across; it may not be able to do ANYTHING sensible with joins at all.

Test very carefully before you commit to the change. Try a simple

    select {columns}
    from t1_at_remote t1, t2_at_remote t2
    where t1.colx = t2.coly
    ;

See if 8.0 resolves this as

1. select from a remote join or
2. a join of two separate remote selects

Then check if the statistics from the execution plan are

1. realistic or
2. complete fabrications

See if putting in a 'driving_site()' hint helps.

If everything looks good, try it with a 3-table join, you may find that Oracle can join two tables well, but can't cope with the third.

Then, if you code requires it, check what happens with a 'create as select', or 'insert into local select from remote'. Things go badly wrong in these cases.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 29th Nov 2005

"Steven Kondolf" <skondolf_at_nospam.rochester.rr.com> wrote in message 
news:ek61p11tplu1ghrt7srtmum7hodrp1pf55_at_4ax.com...


> We have an old 8.0.6.3 database running on HPUX 11 that currently

> contains our Oracle Applications 11.0.3 instance (both database and

> APPL_TOP) as well as a data warehouse schema.  We are beginning to run

> out of physical room and plan on moving the data whse schema over to a

> Linux server running 10g (10.2.0.1) and Redhat AS 4.0.  This will also

> free up processing resources on the old HP box.

>

> As we cannot upgrade our 11.0.3/HP box until next year due to business

> reasons (read MONEY), our 8.0.6 box will still contain the

> jobs/reports/scripts etc.  Our plan is to build a db link from the

> 8.0.6 box to the 10.2 box and change our code to perform our

> processing via this db link.

>

> Are there any special tuning considerations relative to db links,

> sqlnet, etc. that I should be looking at?  I've found notes on

> Metalink commenting on tuning sqlnet by setting the SDU and TDU sizes

> but I've also found notes that state this would be of limited value

> and the "default settings" are adequate in the vast majority of

> situations.

>

> I don't have a lot of Oracle8 documentation available and am currently

> having trouble connecting to tahiti.oracle.com to see if there's any

> docs for 8 there.  Thanks for any help or insights given.

>

> Steve