PS : occurs on 9.2 as well. Just checked.
"Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message
news:aGhW8.647$zX3.509_at_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
>
> (c) Copyright 2000 Oracle Corporation. All rights reserved.
>
>
> 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
> --------------------------------------------------------------------------
--
> ----
>
> | Operation | Name | Rows | Bytes| Cost | Pstart|
> Pstop |
>
> --------------------------------------------------------------------------
--
> ----
>
> | 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
> --------------------------------------------------------------------------
--
> ----
>
> | Operation | Name | Rows | Bytes| Cost | Pstart|
> Pstop |
>
> --------------------------------------------------------------------------
--
> ----
>
> | 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
> --------------------------------------------------------------------------
--
> ----
>
> | Operation | Name | Rows | Bytes| Cost | Pstart|
> Pstop |
>
> --------------------------------------------------------------------------
--
> ----
>
> | 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 - 10:14:44 CDT
