oracle sql profile - need help please

From: Andy <andyjgw_at_gmail.com>
Date: Tue, 13 Apr 2010 05:22:09 -0700 (PDT)
Message-ID: <74bec163-0ee1-4087-be18-69ccf36c2136_at_r1g2000yqb.googlegroups.com>

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

Received on Tue Apr 13 2010 - 07:22:09 CDT