Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Remote Joins cause poor response times

Re: Remote Joins cause poor response times

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 28 Apr 2001 15:11:38 +0100
Message-ID: <988467013.16597.1.nnrp-08.9e984b29@news.demon.co.uk>

This is probably an unlucky bit of costing from the CBO, you don't mention the version of Oracle - each version gets a little better about using statistics from remote databases.

Have you tried putting in a USE_NL hint into the query you want to run to see how efficient it is ?

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases
Publishers:  Addison-Wesley

Reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



Scott Watson wrote in message ...

>
>Mark, thanks for the answer....
>
>However attached is two statements with plans. I would like to know why
>when I don't select any columns from the remote table the other column
(from
>explain plan) shows the query I would have hoped was sent to the remote
>server, but when I include columns it does not.
>
>Thanks,
>Scott.
>
>
>
>
>
>explain plan
>set statement_id = 'SCOTT1' for
>Select /*+ ordered */ DE.SFID, DE.VERSION_TOKEN, N.NAME_DESCRIPTION,
>Get_Translated_Text(1, N.SYN_TOKEN,'SYNONYM_TYPES.SYN_TYPE')
> from QUERY_OL_DATA_ELEMENTS DE, OL_SUBST_NAMES N
>where DE.QUERY_SUMMARY_NO = 8476 and N.USID = DE.USID AND N.LANG_TOKEN =
1;
>
>
>
>Execution Plan
>OTHER
>---------------------------------------------------------------------------
-
>---------------------------------------------
>SELECT STATEMENT CHOOSE Cost=235 Rows=7264 Bytes=849888
> HASH JOIN Cost=235 Rows=7264 Bytes=849888
> TABLE ACCESS (BY INDEX ROWID) DATACHEST.QUERY_OL_DATA_ELEMENTS(1)
>ANALYZED Cost=20 Rows=522 Byte
>s=27144
>
> INDEX (RANGE SCAN) DATACHEST.QRY_OL_DATA_ELEMENTS_PK ANALYZED Cost=3
>Rows=522
> PROD SERIAL_FROM_REMOTE Cost=206 Rows=2018033 Bytes=131172145
>SELECT "USID","LANG_T
>
>OKEN","SYN_TOKEN","NA
>
>ME_DESCRIPTION" FROM
>
>"OL_SUBST_NAMES" "N"
>
>WHERE "LANG_TOKEN"=1
>
>explain plan
>set statement_id = 'SCOTT2' for
>Select /*+ ordered */ DE.SFID, DE.VERSION_TOKEN
> from QUERY_OL_DATA_ELEMENTS DE, OL_SUBST_NAMES N
>where DE.QUERY_SUMMARY_NO = 8476 and N.USID = DE.USID AND N.LANG_TOKEN =
1;
>
>
>Execution Plan
>OTHER
>---------------------------------------------------------------------------
-
>---------------------------------------------
>SELECT STATEMENT CHOOSE Cost=20 Rows=7264 Bytes=566592
> NESTED LOOPS Cost=20 Rows=7264 Bytes=566592
> TABLE ACCESS (BY INDEX ROWID) DATACHEST.QUERY_OL_DATA_ELEMENTS(1)
>ANALYZED Cost=20 Rows=522 Byte
>s=27144
>
> INDEX (RANGE SCAN) DATACHEST.QRY_OL_DATA_ELEMENTS_PK ANALYZED Cost=3
>Rows=522
> PROD SERIAL_FROM_REMOTE Rows=2018033 Bytes=52468858
>SELECT "USID","LANG_T
>
>OKEN" FROM "OL_SUBST_
>
>NAMES" "N" WHERE "USI
>
>D"=:1 AND "LANG_TOKEN
>
>"=1
Received on Sat Apr 28 2001 - 09:11:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US