Re: tough question (distributed queries)...

From: Heiko Welter <Heiko.Welter_at_t-mobil.de>
Date: Fri, 15 Dec 2000 08:38:52 +0100
Message-ID: <3A39CA8C.2DC4A994@t-mobil.de>

Hi Jonathan!
Thanks for your suggestion. It seems, as if your perfectly right with your assuption.
Following you can find the execution-plans for both statements. Maybe its important
to say that the SQL-Statements are generated by an application. So it is important to know if there is a hint or a global database-option to force Oracle to behave in the 'right' manner. I already tried the hint "push_subq" but it doesn't work. Ahhh - one more thing:
The "or"-query is on the remote site translated to an "ANY"-Statement -why? I'm really desperate, because I didn't thougt, that this would be such a big issue...

Cheers
Heiko

Here is the "fast" execution-plan.
SELECT STATEMENT (REMOTE) Optimizer=HINT: ALL_ROWS (Cost=45550 Card=1 Bytes=34)
  SORT (GROUP BY) (Cost=45550 Card=1 Bytes=34)     HASH JOIN (Cost=45545 Card=126 Bytes=4284)

      VIEW OF VW_NSO_1 (Cost=98 Card=25619 Bytes=333047)
        SORT (UNIQUE) (Cost=98 Card=25619 Bytes=333047)
          UNION-ALL
            REMOTE* (Cost=4 Card=21441 Bytes=278733)
            REMOTE* (Cost=1 Card=4177 Bytes=54301)
            REMOTE* (Cost=1 Card=1 Bytes=13)
      TABLE ACCESS (FULL) OF DWH$TA_C_VERTRAG (Cost=45438 Card=106402

Bytes=2234442)

And this is the slow one (doesnt show a nested loop, but it seems to do one - without indices btw.)
SELECT STATEMENT (REMOTE) Optimizer=HINT: ALL_ROWS (Cost=45506 Card=15175 Bytes=318675)
  SORT (GROUP BY) (Cost=45506 Card=15175 Bytes=318675)     FILTER
      TABLE ACCESS (FULL) OF DWH$TA_C_VERTRAG (Cost=45438 Card=15176 Bytes=318696)

      REMOTE* (Cost=1 Card=1 Bytes=13)
      REMOTE* (Cost=1 Card=1 Bytes=13)
      REMOTE* (Cost=1 Card=1 Bytes=13)

Jonathan Lewis schrieb:

> Have you tried to run 'explain plan' against
> the query to see what Oracle is actually
> doing.
>
> In one case Oracle may have decided that the best route
> is to fold your IN clause into an existence test, and therefore
> do a nested loop from B for each row fetching data from
> the two small tables. Whereas the other case Oracle
> may have decided to do the 'remote' union and pull it
> across the link to drive a local hash join into the large
> table.
>
> --
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Practical Oracle 8i: Building Efficient Databases
>
> Publishers: Addison-Wesley
> See a first review at:
> http://www.ixora.com.au/resources/index.htm#practical_8i
> More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html
>
> Heiko Welter wrote in message <3A390266.C4979E63_at_t-mobil.de>...
> >We have observed in our distributed environment a strange behavour when
> >using some sql-queries. Our env. is the following:
> >
> >local instance (A) - only small tables
> >remote instance (B) - one big table
> >All instances are running Oracle8.1.6 under Solaris2.7. On instance A we
> >installed a DB-Link to instance B and created a
> >view on A pointing to the big table on instance B. Instance A and B are
> >on different machines.
> >
> >When we execute our select-statement
> >select /*+ driving_site(b) all_rows */ b.column1
> >from big_table b
> >where b.key1 in (select key1 from small_table1) -- small_table is local,
> >but because of driving_site then remote
> >or b.key1 in (select key1 from small_table2) -- same
> >
> >We expected the small tables to be transported to the specified driving
> >site and the
> >query to be executed on the driving site. In the end, the result-set
> >should be sent back
> >to our local instance. The problem is, that it doesn't work as we
> >expected it to do!
> >It produced a very very large amount of network-traffic...
> >
> >On the other hand the statement:
> >
> >select /*+ driving_site(b) all_rows */ b.column1
> >from big_table b
> >where b.key1 in (select key1 from small_table1 union select key1 from
> >small_table2)
> >
> >works as expected very quick.
> >
> >Maybe it is important that the statements produce a different
> >execution-plan.
> >We really don't know what to do, so any suggestions would be welcome.
> >
> >Thanks for your help
> >cheers
> >Heiko
> >
> >
> >
Received on Fri Dec 15 2000 - 01:38:52 CST