help appreciated with db-link CTAS

From: Telemachus <telemachus_at_ulysseswillreturn.net>
Date: Mon, 8 Jul 2002 15:51:50 +0100
Message-ID: <aGhW8.647$zX3.509@news.indigo.ie>

I am having the following prob (on Win/2000) . Managed to get the scott tables to reproduce.

oracle_at_remote is a loopback db-link (for demo only. this is an actual problem on a v. large system)

COMPUTE Schema statistics are fully in place (on scott)

C:\>sqlplus scott/tiger

SQL*Plus: Release 8.1.7.0.0 - Production on Mon Jul 8 15:37:37 2002

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production JServer Release 8.1.7.3.0 - Production

SQL> explain plan for select d.deptno,'X' from emp_at_oracle@remote e,dept_at_oracle@r
emote d where d.deptno=e.deptno;

Explained.

SQL> @ ?/rdbms/admin/utlxpls.sql

Plan Table

| SELECT STATEMENT REMOTE | | 14 | 84 | 1 | |
|

| NESTED LOOPS | | 14 | 84 | 1 | |
|

| TABLE ACCESS FULL |EMP | 14 | 42 | 1 | |
|

| INDEX UNIQUE SCAN |PK_DEPT | 4 | 12 | | |
|

7 rows selected.

(Which is just what is needed and would be expected from a remote only sql . Now look what happens when it goes to a CTAS instead. )

SQL> explain plan for create table foo as select d.deptno,'X' from emp_at_oracle@re
mote e,dept_at_oracle@remote d where d.deptno=e.deptno;

Explained.

SQL> @ ?/rdbms/admin/utlxpls.sql

Plan Table

| CREATE TABLE STATEMENT | | 14 | 364 | 1 | |
|

| LOAD AS SELECT | | | | | |
|

| NESTED LOOPS | | 14 | 364 | 1 | |
|

| REMOTE | | 14 | 182 | 1 | |
|

| REMOTE | | 4 | 52 | 1 | |
|

8 rows selected.

SQL> and now look what happens if we remove the constant .

SQL> explain plan for create table foo as select d.deptno from emp_at_oracle@remote
e,dept_at_oracle@remote d where d.deptno=e.deptno;

Explained.

SQL> @ ?/rdbms/admin/utlxpls.sql

Plan Table

| CREATE TABLE STATEMENT | | | | | |
|

| LOAD AS SELECT | | | | | |
|

| REMOTE | | | | | |
|

and it's now nicer again.

There is quite a nasty degradation in plan 2, since now it's not remoting (not the correct word , I know ) all if it. This caused a production query to blow as the (slightly different) plan selected blew the tempfile since it was merge joining and the ctas made it pull the a lot of data across the link to satisfy the join.
Removal of the constant fixed the issue temporarily but CTAS is meant to be used in this fashion to allow for speedy population between instances.

it is also very difficult to monitor this as the CTAS is noted as a DDL and so is not available in the SQL area.

Any ideas ?

TIA Received on Mon Jul 08 2002 - 09:51:50 CDT