Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Remote query optimization

Remote query optimization

From: Tim Bond <tbond_at_mitre.org>
Date: Tue, 08 Jun 1999 10:40:10 -0400
Message-ID: <375D2B4A.AA43A649@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 - 09:40:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US