Re: Is explain plan lying to me?
- From: Alfonso León <aleon68@xxxxxxxxx>
- To: gabriel.aragon@xxxxxx
- Date: Tue, 15 Feb 2005 21:45:19 -0500
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@xxxxxx> 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
>
> --
> //www.freelists.org/webpage/oracle-l
>
--
Alfonso Leon
--
//www.freelists.org/webpage/oracle-l
Other related posts:
