oracle sql profile - need help please
in [Oracle]
|
Prev: importing DBMS_SCHEDULER jobs - date formatting problem...
Next: Copying Backup of 32-bit Win2000 10g Database into a 64 Bit Win2003 Server
From: Andy on 13 Apr 2010 08:22 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 used at all. EM still shows the SQL using the inefficient plan. SQL> l 1 begin 2 dbms_output.put_line( 3 dbms_sqltune.accept_sql_profile( 4 task_name => 'SQL_TUNING_1271147514497', 5 name => 'SYS_SQLPROF_0149fdae48460004', 6 replace => true, 7 force_match => true) 8 ); 9* end; SQL> / SYS_SQLPROF_0149fdae48460004 PL/SQL procedure successfully completed. SQL> select force_matching from dba_sql_profiles where name = 'SYS_SQLPROF_0149fdae48460004'; FOR --- NO Can anyone advise why this might be please ? The Tuning ID in EM is correct (that's where I got it from). Basically, I need Operation Object Object Type Order Rows Size (KB) Cost Time (sec) CPU Cost I/O Cost SELECT STATEMENT 11 743 NESTED LOOPS 10 4 0.496 743 9 39626522 733 NESTED LOOPS 7 4 0.453 735 9 39563064 725 VIEW 4 4 0.254 730 9 39499102 720 HASH UNIQUE 3 4 0.156 730 9 39499102 720 FILTER 2 INDEX RANGE SCAN HCREF.S436_IX1 INDEX (UNIQUE) 1 4 0.156 729 9 35552327 720 TABLE ACCESS BY INDEX ROWID HCREF.F436 TABLE 6 1 0.050 2 1 16553 2 INDEX UNIQUE SCAN HCREF.F436_UK1 INDEX (UNIQUE) 5 1 1 1 9021 1 TABLE ACCESS BY INDEX ROWID HCREF.MMLINE TABLE 9 1 0.011 2 1 16673 2 INDEX UNIQUE SCAN HCREF.A436_IX1 INDEX (UNIQUE) 8 1 1 1 9021 1 MMLINE should be above F436 in the plan for optimum performance. All stats are up to date, and the TEST system does it the right way. I can't find anything to account for the different plan (sga, pga, IO, parameters, anything!). 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. 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
From: gazzag on 13 Apr 2010 09:15 On 13 Apr, 13:22, Andy <andy...(a)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? > 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
From: Andy on 13 Apr 2010 09:22 On Apr 13, 2:15 pm, gazzag <gar...(a)jamms.org> wrote: > On 13 Apr, 13:22, Andy <andy...(a)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
From: joel garry on 13 Apr 2010 12:53
On Apr 13, 6:22 am, Andy <andy...(a)gmail.com> wrote: > On Apr 13, 2:15 pm, gazzag <gar...(a)jamms.org> wrote: > > > On 13 Apr, 13:22, Andy <andy...(a)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 -- @home.com is bogus. http://www.signonsandiego.com/news/2010/apr/12/defendants-spawar-kickback-scheme-sentenced/ |