Is explain plan lying to me?
- From: "Aragon, Gabriel (GE Commercial Finance)" <gabriel.aragon@xxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 15 Feb 2005 20:35:03 -0500
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
Other related posts:
