Re: Remote query optimization

From: Matthew McPeak <mcpeakm_at_email.msn.com>
Date: Tue, 8 Jun 1999 18:57:29 -0400
Message-ID: <ehFnmLgs#GA.115@cpmsnbbsa02>

Tim,

I'm not sure, but you might try modifying your localview view so that it passes the where clause directly to the remote table. Like this, maybe:

SELECT bunchofcolumns

FROM     local_tab_1,
                local_tab_2,
                (select remotecolumns
                  from   remote_tab_1_at_remotedb
                  where name like 'SMITH%'
                )

 WHERE other_where_conditions

I know that isn't exactly the way you have things structured in your question, but if it works it might provide you with enough insight to solve the problem.

Good luck,
Matt

Tim Bond wrote in message <375D2B4A.AA43A649_at_mitre.org>...
>I am having difficulty with the optimization of a query running over a
>somewhat slow database link. The SQL is trying to grab a set of rows
>from a group of tables on a remote Oracle server and insert them into a
>table on the local server. The tables are accessed by a view defined on
>the local system. If the query is run without inserting the results
>locally, it only takes only a few seconds (acceptable). With the
>insert, it is taking anywhere from 60-120 seconds (which is not
>acceptable). The explain plan shows that multiple "SERIAL_FROM_REMOTE"
>loops are being performed. We defined a view on the remote system and
>then performance improved to acceptable levels (see second explain
>output below). Only a single "SERIAL_FROM_REMOTE" was executed.
>Unfortunately, the remote view is not a desirable option for the
>production system since we probably won't be allowed to create schema
>objects. Any ideas on what might be happening or options on how we can
>force the second access path without the remote view?
>
>All tables/indexes have been analyzed. idseq is a local sequence we use
>to generate unique row identifiers. localview is a view which accesses
>remote tables via a database link.
>
>This transcript is from a test system but reflects the explain output
>from the system that is performing badly:
>
>SQL> insert into TEMPTAB
> 2 select bunchofcolumns, idseq.nextval
> 4 from localview
> 5 where name like 'SMITH%';
>
>4 rows created.
>
>Commit complete.
>
>Execution Plan
>----------------------------------------------------------
> 0 INSERT STATEMENT Optimizer=CHOOSE (Cost=16 Card=2 Bytes=178)
> 1 0 SEQUENCE OF 'IDSEQ'
> 2 1 NESTED LOOPS (OUTER) (Cost=16 Card=2 Bytes=178)
> 3 2 NESTED LOOPS (OUTER) (Cost=12 Card=2 Bytes=144)
> 4 3 REMOTE* (Cost=8 Card=2 Bytes=58)
> 5 3 REMOTE*
> 6 2 REMOTE*
>
> 4 SERIAL_FROM_REMOTE
> 5 SERIAL_FROM_REMOTE
> 6 SERIAL_FROM_REMOTE
>
>Statistics
>----------------------------------------------------------
> 669 recursive calls
> 242 db block gets
> 167 consistent gets
> 0 physical reads
> 15280 redo size
> 408 bytes sent via SQL*Net to client
> 733 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 3 sorts (memory)
> 1 sorts (disk)
> 4 rows processed
>
>SQL>
>
>SQL> insert into TEMPTAB
> 2 select bunchofcolumns, idseq.nextval
> 4 from remoteview_at_db2
> 5 where name like 'SMITH%';
>
>4 rows created.
>
>Commit complete.
>
>Execution Plan
>----------------------------------------------------------
> 0 INSERT STATEMENT Optimizer=CHOOSE (Cost=5 Card=116 Bytes=197
> 2)
>
> 1 0 SEQUENCE OF 'IDSEQ'
> 2 1 REMOTE* (Cost=5 Card=116 Bytes=1972)
>
> 2 SERIAL_FROM_REMOTE SELECT
>
>
>Statistics
>----------------------------------------------------------
> 14 recursive calls
> 41 db block gets
> 4 consistent gets
> 0 physical reads
> 4548 redo size
> 407 bytes sent via SQL*Net to client
> 740 bytes received via SQL*Net from client
> 4 SQL*Net roundtrips to/from client
> 1 sorts (memory)
> 0 sorts (disk)
> 4 rows processed
>
>
>
Received on Tue Jun 08 1999 - 17:57:29 CDT