Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.server -> Remote query optimization
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 processedReceived on Tue Jun 08 1999 - 09:40:10 CDT
![]() |
![]() |