Re: db-link 7.3.4 to 8.0.5

Without seeing the explain plan output this is all I can think of: Distributed queries often result in full table scans and since you have RULE hints I really question what Oracle is doing to solve this.

One thing you might consider is taking the join on the remote tables and replacing it will a view at the remote instance that does the join. Also if the local table is small and the remote tables are not, you might consider pushing the local table to the remote instance and letting the query be ran there using the driving_site hint [check syntax on hint].

In article <Cj9y4.373$QY4.29951_at_news0.telusplanet.net>,   "Terry Dykstra" <tdykstra_at_cfol.ab.ca> wrote:
> Yes, I use hints. Here is the whole query. The final result is 2
 rows.
>
> select /*+ RULE */ atgas.contract_type, atgas.contract_id, ipl.uwi,
> 'Unknown',
> atgas.working_interest, ipl.form_name
> from
> contract_lands atgas,
> (select /*+ RULE */ well.uwi, formation.form_name,
> dls_range, meridian, meridian_dir, dls_township, dls_section
> FROM
> IPL_CODE_at_PETRODATA2 IPL_CODE,
> FORMATION_at_PETRODATA2 FORMATION,
> WELL_at_PETRODATA2 WELL,
> WELL_PROD_ZONE_at_PETRODATA2 WELL_PROD_ZONE,
> NODE_at_PETRODATA2 NODE,
> NODE_DLS_NTS_at_PETRODATA2 DLS
> WHERE WELL_PROD_ZONE.SOURCE(+) = 'IPL'
> AND WELL.SOURCE = 'IPL'
> AND FORMATION.SOURCE(+) = 'IPL'
> AND IPL_CODE.CODE_TYPE(+) = 'STATUS'
> AND DLS.SOURCE = 'IPL'
> AND DLS.LOC_QUAL = 'A'
> AND NODE.SOURCE = 'IPL'
> AND NODE.LOC_QUAL = 'A'
> AND WELL_PROD_ZONE.UWI(+)=WELL.UWI
> AND WELL_PROD_ZONE.SOURCE(+)=WELL.SOURCE
> AND FORMATION.SOURCE(+)=WELL_PROD_ZONE.SOURCE
> AND FORMATION.FORM_ID(+)=WELL_PROD_ZONE.ZONE_ID
> AND IPL_CODE.SOURCE(+)=WELL.SOURCE
> AND IPL_CODE.CODE(+)=WELL.IPL_CRSTATUS
> AND DLS.NODE_ID=NODE.NODE_ID
> AND DLS.SOURCE=NODE.SOURCE
> AND NODE.SOURCE=WELL.SOURCE
> AND NODE.NODE_ID=WELL.BASE_NODE_ID
> ) ipl
> where ipl.dls_range = atgas.rge
> and ipl.meridian = atgas.mer and ipl.meridian_dir='W'
> and ipl.dls_township = atgas.twp
> and ipl.dls_section = atgas.sec
> and (atgas.contract_type, atgas.contract_id, ipl.uwi) not in
> (select contract_type, contract_id, uwi from contract_wells)
> and ipl.meridian_dir = 'W' and ipl.meridian = 5 and ipl.dls_range=25
 and
> ipl.dls_section=13 and ipl.dls_township=92
> and atgas.mer = 5 and atgas.rge =25 and atgas.sec =13 and atgas.twp=92
> /
>
> --
> Terry Dykstra (TeamSybase)
> Canadian Forest Oil Ltd.
> Check out Sybase Developer's Network: http://www.sybase.com/sdn
>
> Brian Peasland <peasland_at_edcmail.cr.usgs.gov> wrote in message
> news:38C91009.2A644F65_at_edcmail.cr.usgs.gov...
> > Terry,
> >
> > I'm not sure why your database is hanging, but I have seen
 problems
> > with dblinks between Oracle 7 & 8. Query hints have problems in this
> > environment. Are you using any hints? Maybe you should include a
 copy of
> > your query. That might help diagnose the problem.
> >
> > HTH,
> > Brian
> >
> > Terry Dykstra wrote:
> > >
> > > I run a query from a 7.3.4 database using db-links to a 8.0.5
 database
 and
> > > it just hangs my 734 database. When I copy the 734 tables to the
 805
> > > database and run the same query without dblinks, it returns the
 answer
 in
> > > less than 1 second. Anybody have any insights into this?
> > >
> > > --
> > > Terry Dykstra
> > > Canadian Forest Oil Ltd.
> > > Check out Sybase Developer's Network: http://www.sybase.com/sdn
> >
> > --
> > ========================================
> > Brian Peasland
> > Raytheons Systems at
> > USGS EROS Data Center
> > These opinions are my own and do not
> > necessarily reflect the opinions of my
> > company!
> > ========================================
>
>

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.