Re: oracle sql profile - need help please
From: joel garry <joel-garry_at_home.com>
Date: Tue, 13 Apr 2010 09:53:44 -0700 (PDT)
Message-ID: <df49c7b5-de26-4e38-96bd-9d55cf5c762f_at_e7g2000yqf.googlegroups.com>
On Apr 13, 6:22 am, Andy <andy..._at_gmail.com> wrote:
> On Apr 13, 2:15 pm, gazzag <gar..._at_jamms.org> wrote:
>
> > On 13 Apr, 13:22, Andy <andy..._at_gmail.com> wrote:
>
> > > Hi folks
>
> > > Enterprise Manager suggested a better plan for me (Oracle 10.2.3 /
> > > Solaris64) and I went for it. However, it doesn't look like it's being
>
> > <snip>
>
> > > Any help as to how I can force LIVE to do what TEST is doing? If the
> > > force-match takes effect, I'd like to assume that would have sorted
> > > it ? We have no access to the underlying SQL.
>
> > Are there any differences in INIT.ORA parameters?
>
> Hi - Nothing that would make a different; just larger SGA etc for LIVE
> system. More "sizing" parameters rather than anything of a case more
> likely to inflict a different plan.
>
> If I could get this profile to apply, I'd like to think that would
> sort it....
>
> Any ideas? Thanks for the reply.
>
> A
>
>
>
> > > SELECT /*+ LEADING INDEX(S_ S436_IX1) INDEX(SHAPE F436_UK1)
> > > INDEX(MMLINE A436_IX1) */ HCREF.MMLINE.FEATCODE,
> > > HCREF.MMLINE.SHAPE ,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid,SHAPE.numofpts,SHAPE.entity,SHAPE.points,SHAPE.rowid
> > > FROM (SELECT /*+ INDEX(SP_ S436_IX1) */ DISTINCT sp_fid, eminx, eminy,
> > > emaxx, emaxy FROM HCREF.S436 SP_ WHERE SP_.gx >= :1 AND SP_.gx <= :2
> > > AND SP_.gy >= :3 AND SP_.gy <= :4 AND SP_.eminx <= :5 AND SP_.eminy
> > > <= :6 AND SP_.emaxx >= :7 AND SP_.emaxy >= :8) S_ , HCREF.MMLINE ,
> > > HCREF.F436 SHAPE WHERE S_.sp_fid = SHAPE.fid AND S_.sp_fid =
> > > HCREF.MMLINE.SHAPE
>
> > > Thanks
>
> > > A
>
> > HTH
> > -g
>
>
Date: Tue, 13 Apr 2010 09:53:44 -0700 (PDT)
Message-ID: <df49c7b5-de26-4e38-96bd-9d55cf5c762f_at_e7g2000yqf.googlegroups.com>
On Apr 13, 6:22 am, Andy <andy..._at_gmail.com> wrote:
> On Apr 13, 2:15 pm, gazzag <gar..._at_jamms.org> wrote:
>
> > On 13 Apr, 13:22, Andy <andy..._at_gmail.com> wrote:
>
> > > Hi folks
>
> > > Enterprise Manager suggested a better plan for me (Oracle 10.2.3 /
> > > Solaris64) and I went for it. However, it doesn't look like it's being
>
> > <snip>
>
> > > Any help as to how I can force LIVE to do what TEST is doing? If the
> > > force-match takes effect, I'd like to assume that would have sorted
> > > it ? We have no access to the underlying SQL.
>
> > Are there any differences in INIT.ORA parameters?
>
> Hi - Nothing that would make a different; just larger SGA etc for LIVE
> system. More "sizing" parameters rather than anything of a case more
> likely to inflict a different plan.
>
> If I could get this profile to apply, I'd like to think that would
> sort it....
>
> Any ideas? Thanks for the reply.
>
> A
>
>
>
> > > SELECT /*+ LEADING INDEX(S_ S436_IX1) INDEX(SHAPE F436_UK1)
> > > INDEX(MMLINE A436_IX1) */ HCREF.MMLINE.FEATCODE,
> > > HCREF.MMLINE.SHAPE ,S_.eminx,S_.eminy,S_.emaxx,S_.emaxy ,SHAPE.fid,SHAPE.numofpts,SHAPE.entity,SHAPE.points,SHAPE.rowid
> > > FROM (SELECT /*+ INDEX(SP_ S436_IX1) */ DISTINCT sp_fid, eminx, eminy,
> > > emaxx, emaxy FROM HCREF.S436 SP_ WHERE SP_.gx >= :1 AND SP_.gx <= :2
> > > AND SP_.gy >= :3 AND SP_.gy <= :4 AND SP_.eminx <= :5 AND SP_.eminy
> > > <= :6 AND SP_.emaxx >= :7 AND SP_.emaxy >= :8) S_ , HCREF.MMLINE ,
> > > HCREF.F436 SHAPE WHERE S_.sp_fid = SHAPE.fid AND S_.sp_fid =
> > > HCREF.MMLINE.SHAPE
>
> > > Thanks
>
> > > A
>
> > HTH
> > -g
>
>
This is a bit out of my comfort zone and I'm not licensed to test, but I have some speculative questions:
Is the syntax of the LEADING hint correct? Does it come out exactly the same on both systems? I'm wondering if something there sets off the force_matching exception of combining literals and bind variables or some such thing.
How big exactly is the SGA and the various tables? (I assume they are big tables since they look like topology, but it needs to be asked.)
Do you have any query rewrite?
Can you trace the queries on both systems to see exactly what is going on with the optimizer? (google 10053 trace)
jg
-- _at_home.com is bogus. http://www.signonsandiego.com/news/2010/apr/12/defendants-spawar-kickback-scheme-sentenced/Received on Tue Apr 13 2010 - 11:53:44 CDT