Re: Poor Sql Perf via DBLINK

In article <CsyCFz.14u_at_world.std.com>, lparsons_at_world.std.com (Lee E Parsons) writes:
> Problem:
>
> I have two systems on the network. Table SOC_NUM on system A contains a short
> list of social security numbers. I want to select the name of those
> individuals from the master employees list on system B. This list is called
> USERS and is naturally rather large. USERS is accessed from A by a database
> link.
>
> -----------------------------------------------------------------------
> | |
> | SYSTEM A SYSTEM B |
> | |
> | DB LINK |
> | TABLE SOC_NUM (SSN)------------------------>TABLE USERS (NAME, SSN) |
> | |
> | select USERS.name, SOC_NUM.ssn |
> | from SOC_NUM.a, USERS_at_B b |
> | where a.SSN = b.SSN; |

If you define aliases (ie a & b) then don'y prefix fields with the table name. Bad style 8^}.

If you want SOC_NUM to drive then place it last in the FROM clause (for RULE based optimiser).

If you're using the COST based optimiser then put in some HINTs on how you really want it to work.

If you want Oracle7 to use COST based optimiser then make sure you have ANALYZEd all objects involved.

> -----------------------------------------------------------------------
>
> The problem is that when the above staement is executed from sys A
> the execution plan mandates that all of USERS be shipped from B to A. Only
> after this ton of data is shipped back does is the SOC_NUM table evaluated.
> The prefered method would be to scan SOC_NUM and do a index lookup on
> USERS.
SET ARRAY size large (100-500) to reduce unnecessary network fetches.

If SOC_NUM is large'ish then Oracle may be doing the query in the most cost effective manner.  

>
> FYI - USERS is actually a pretty ugly view of 5 tables
>
> Question:
>
> When a database link is involved how do we control the execution plan?

Oracle7 provides 'HINTS'.

You could consider using stored procedures that accept the ssn and return the user name but then you would have to put a loop in pl/sql to do your query.

> Should I lose the dblink and go with a snap shot? (Of course since USERS
> is a view and not all of the data in the underlying tables can be viewed
> by sys A, I not sure what I would snapshot)

You could keep the SSN <-> USER mapping as a separate table on sys A and then manage reloads of that every night or whatever.

>
> Is all of life this hard?

YES !

-- 

Bruce...        pihlab_at_cbr.hhcs.gov.au

*******************************************************************
* Bruce Pihlamae  --  Database Administration                     *
* Commonwealth Department of Human Services and Health            *
* Canberra, ACT, Australia                        (W) 06-289-7056 *
*=================================================================*
* These are my own thoughts and opinions, few that I have.        *
*******************************************************************

"The more complex the argument gets, the easier it is to refute."
"Killing is wrong!"  -- Trent 'The Uncatchable' Castanaveras