Re: Is explain plan lying to me?

without the structure, size, indexes and statistist of the tables and definition of the view there is too much for guessing.

If you're so puzzle for this I suggest you to trace your statement and tkprof it. Then you will know for sure the exact plan the optimizer used and how many rows it got for each step, also you will know how many blocks did you get from disk and from the SGA

Regards
Alfonso

On Tue, 15 Feb 2005 20:35:03 -0500, Aragon, Gabriel (GE Commercial Finance) <gabriel.aragon_at_ge.com> wrote:
> 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
>

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