Re: Can anybody explain this strange thing?

"vic" <v.tsien_at_comcast.net> wrote in message news:<VsoJb.719303$HS4.5220062_at_attbi_s01>...

> 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
I'm not an expert in interpreting distributed plans, but since I see two REMOTE lines i would assume that the query is not resolved completely at the remote site but partially at the local site. I would bet that e.g. the HASH JOIN is performed locally.

Have you put a space in the hint after the "+" (i.e. /*+ driving_site ...*/ instead of /*+driving_site ...*/) ? In some versions i've noticed that the first char is frequently chopped away, so Oracle sees something like /*+riving_site*/ and it ignores the hint.

I had some problems myself while mixing db-links and parallel operations; i solved them perfectly by creating a view at the remote site with the same text as the query, and then performing a "select *" from the view. That worked perfectly for me so i stopped investigating; i don't know if it may work for you.

HTH
Alberto Received on Mon Jan 05 2004 - 08:44:59 CST