Re: Poor Sql Perf via DBLINK

Bruce Pihlamae <pihlab_at_cbr.hhcs.gov.au> wrote:
>lparsons_at_world.std.com (Lee E Parsons) writes:
>> Problem:

 [blah blah blah]
>>
>> | 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^}.

Mea Culpa! :-}

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

Tried this. Apparently the optimizer really wants to process the dblink first. I think he was looking at the indexes on USERS and seeing that accessing SOC_NUM would require a full tablescan. So USERS was the inner table. He failed, however, to realize that SOC_NUM has one row.

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

This was what we finally did. We had been avoiding COST because of its reported (and observed) problems. I finally decided to turn it on but not to analyze any tables. This give us access to hints but keeps the RULE based for everything else.

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

Nope sorry. SOC_NUM contains 1 row and USERS is a view that returns 200k rows.

Clearly the right way to go here is HINTS. I hadn't viewed that as a option because of the OPTIMIZER problems. But turning it on and not analyzing the tables works suprisingly well. Suprising for me anyway.

Thanx

-- 
Regards, 

Lee E. Parsons                  		
Systems Oracle DBA	 			lparsons_at_world.std.com