Is explain plan lying to me?

From: Aragon, Gabriel (GE Commercial Finance) <gabriel.aragon_at_ge.com>
Date: Tue, 15 Feb 2005 20:35:03 -0500
Message-ID: <8CABE4F0A4D9B54BBF735922A381BD0E019FA0C5@CINMLVEM05.e2k.ad.ge.com>

Hi guys,

Oracle 9.2.0.5 win2k

I have one view that makes a join between another view and a table through a dblink, when I generate the explain plan I see the following:

Operation Object Name Rows Bytes Cost=09

SELECT STATEMENT Hint=3DCHOOSE 3 G 6 G

  HASH JOIN 3 G 7916G 6 G     VIEW VIW_BBC_USER_LEVEL 80 960 33       SORT UNIQUE 80 8 K 33         UNION-ALL

          TABLE ACCESS FULL	TBL_BBCX_RM_INFO	78  	8 K
7  	 	      	             	=20
          FILTER

            TABLE ACCESS FULL	TBL_BBCX_PA_INFO	2  	106
7  	 	      	             	=20
            TABLE ACCESS FULL	TBL_BBCX_RM_INFO	4  	76
7  	 	      	             	=20
    VIEW	VIW_HFS_ALL_USER_INFO	4 G	9847G	6 G

      SORT UNIQUE		4 G	11851G	6 G

        REMOTE		4 G	11851G	52  	DBL_SQLSERVER	SERIAL

AFAIU I can notice the query is accesing a lot of info (several gig's) on the remote dblink, but the query only takes 1 second to return the data! So, what is the explanation? am I misunderstanding the plan? Is this explain plan for real?=20

SELECT

    xxx.USER_SSO_ID,
    xxx.USER_FIRST_NAME,
    xxx.USER_LAST_NAME,
    xxx.USER_MIDDLE_NAME,
    xxx.USER_SUFFIX,
    xxx.USER_TITLE,
    xxx.USER_ADDRESS,
    xxx.USER_CITY,
    xxx.USER_STATE,
    xxx.USER_COUNTRY,
    xxx.USER_ZIP,
    xxx.USER_DESIGNATION,
    xxx.USER_WORK_PHONE,
    xxx.USER_DIAL_COM,
    xxx.USER_MOBILE,
    xxx.USER_HOME_PHONE,
    xxx.USER_EMAIL,
    xxx.IS_ACTIVE,
    xxx.LAST_UPDATED_DATE

FROM VIW_HFS_ALL_USER_INFO xxx, VIW_BBC_USER_LEVEL yyy WHERE xxx.USER_SSO_ID =3D yyy.USER_SSO_ID;=09

DBlink is inside VIW_BBC_USER_LEVEL pointing to a SQL server db, using transparent gateway.

TIA
Gabriel

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 15 2005 - 20:38:02 CST