Re: Can anybody explain this strange thing?

From: vic <v.tsien_at_comcast.net>
Date: Sat, 03 Jan 2004 00:57:49 GMT
Message-ID: <hsoJb.719296$HS4.5220148@attbi_s01>

Thanks, Gerard and Daniel,

I used driving_site hint but forgot in my news group message. The explain with or without that hint is identical. However, the explain from server a (hp) is different from the explain on server b (sun). The following is the explain from server b(sun) by using sqlplus to connect to b directly
Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=27 Card=322 Bytes=36064) 1 0 SORT* (GROUP BY) (Cost=27 Card=322 Bytes=36064) :Q612003

2  1     HASH JOIN* (Cost=23 Card=322 Bytes=36064) :Q612003
3  2      PARTITION RANGE* (ALL) :Q612003
4  3        INDEX* (FAST FULL SCAN) OF 'BOOK_DATA_HIST_I1' (NON-UNIQUE)
(Cost=1 Card=924 Bytes=44352):Q612001
5  2      HASH JOIN* (Cost=22 Card=2471 Bytes=158144):Q612002
6  5        TABLE ACCESS* (FULL) OF 'PROD_CLASS_T' (Cost=4 Card=501
Bytes=7515):Q612000
7  5        TABLE ACCESS* (FULL) OF 'PRODUCT_T' (Cost=18 Card=7089

Bytes=347361):Q612002

1 PARALLEL_TO_SERIAL    SELECT A1.C0,A1.C1,A1.C2,A1.C3 FROM (SELECT /*+
ORDERED NO_EXPAND USE_HASH(A3) S
2 PARALLEL_COMBINED_WITH_PARENT
3 PARALLEL_COMBINED_WITH_PARENT
4 PARALLEL_TO_PARALLEL  SELECT /*+ INDEX_RRS(A1 "BOOK_DATA_HIST_I1") */

A1."PROD_UID" C0,A1."ENTRY_DATE"
5 PARALLEL_TO_PARALLEL SELECT /*+ ORDERED NO_EXPAND USE_HASH(A2) */ A2.C2 C0,A1.C0 C1,A2.C0 C2,A1.C1 C3
6 PARALLEL_TO_PARALLEL SELECT /*+ NO_EXPAND ROWID(A1) */ A1."PROD_CLASS_UID" C0,A1.ROWID C1,A1."SESS_CO 7 PARALLEL_COMBINED_WITH_PARENT explain from server a (hp) by using sqlplus connect locally but dblink into server b for query. The explain is:
Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=154 Card=41 Bytes=5740) 1 0 HASH JOIN (Cost=154 Card=41 Bytes=5740)

2  1     REMOTE* (Cost=140 Card=11 Bytes=1122) ACCT_USR03_SVRB.WORLD
3  1      MERGE JOIN (CARTESIAN) (Cost=13 Card=37100 Bytes=1409800)
4  3        REMOTE* (Cost=13 Card=371 Bytes=9275)   ACCT_USR03_SVRB.WORLD
5  3      SORT (JOIN)
6  5       VIEW (Cost=5 Card=100 Bytes=1300)

7 6 REMOTE* ACCT_USR03_SVRB.WORLD
2 SERIAL_FROM_REMOTE
SELECT /*+ PARALLEL("P",8) */
"PROD_UID","PROD_TYPE_CODE","LIST_STAT_CODE","PROD 4 SERIAL_FROM_REMOTE
SELECT /*+ PARALLEL("PC",4) */ "PROD_CLASS_UID","SESS_CODE" FROM "BC_OWNER"."PRO
7 SERIAL_FROM_REMOTE
SELECT /*+ */ "A1"."PROD_UID",MAX("A1"."PROD_STATE_CODE") FROM "AC3_OWNER"."BOOK Take a look at the hint "DRIVING_SITE in the form:

SELECT /*+DRIVING_SITE(dept)*/ e.empno, e.empname FROM emp e, dept_at_RSITE d
WHERE e.deptno = d.deptno;

It may solve the problem.

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
> http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
> damorgan_at_x.washington.edu
> (replace 'x' with a 'u' to reply)
>
Received on Fri Jan 02 2004 - 18:57:49 CST