Re: alter sql profile to force matching
- From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- To: joshuasingham@xxxxxxxxx
- Date: Tue, 27 Apr 2010 09:13:10 -0500
As Dion said, the altering SQL Profiles doesn't allow the force_match to be
changed. However, you can create the Profile with force_match set to true to
begin with.
The proposed syntax by the tuning advisor report says something like this:
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'SYS_xxx',
replace => TRUE);
You can accept the profile in a more explicit way like so:
exec dbms_sqltune.accept_sql_profile(task_name => 'SYS_xxx',category =>
'DEFAULT', force_match => true);
I'd recommend creating in an alternate category for testing and to look at the
hints before turning it loose via the DEFAULT category.
Also, I'd consider whether the hints are fixing an optimizer deficiency (i.e.
where the calculations are always going to be wrong and by more or less the
same factor) or whether it's just fixing inaccurate stats which are liable to
get corrected in fairly short order (thus making the fudge factors in the
opt_estimate hints invalid).
So you may want to drop the existing profile and re-accept it.
Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com
On Apr 27, 2010, at 3:19 AM, joshuasingham wrote:
> Hi Dion,
>
> thanks for the reply seem to be a confict in the documents any does anybody
> know how to alter the profile to force match with other methods
>
> Thanks
>
> joshua
>
> On Tue, Apr 27, 2010 at 3:52 PM, Dion Cho <ukja.dion@xxxxxxxxx> wrote:
> http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sqltun.htm#i1008294
>
> Seems that you can only alter following attributes - STATUS, NAME,
> DESCRIPTION, CATEGORY.
>
> ================================
> Dion Cho - Oracle Performance Storyteller
>
> http://dioncho.wordpress.com (english)
> http://ukja.tistory.com (korean)
> http://dioncho.blogspot.com (japanese)
> http://ask.ex-em.com (q&a)
> ================================
>
>
> 2010/4/27 joshuasingham <joshuasingham@xxxxxxxxx>
>
> Hi all,
>
> I have been trying to alter my sql profile to force match but was unable to
> get it to work according to the document link below it say it is possible
> but I keep getting the error below any help is appreciated
> http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_tune.htm#CHDDABDE
>
> SQL> BEGIN
> 2 DBMS_SQLTUNE.ALTER_SQL_PROFILE(
> 3 name => 'SYS_SQLPROF_0146a31c146a31c5',
> 4 attribute_name => 'FORCE_MATCH',
> 5 value => 'YES');
> 6 END;
> 7 /
> BEGIN
> *
> ERROR at line 1:
> ORA-13835: invalid attribute name specified
> ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 7132
> ORA-06512: at "SYS.DBMS_SQLTUNE", line 5603
> ORA-06512: at line 2
>
>
Other related posts:
