Re: New Sql profiles

How to Turn On/Off Automatically Accepting Sql Profile in Sql Tuning Advisor Autotask (Doc ID 2345319.1)

1. To disable SYS_AUTO_SQL_TUNING_TASK (SQL Profiles not enabled by default):

exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);

2. If SYS_AUTO_SQL_TUNING_TASK was enabled and someone enabled the accept sql profiles, then it could be disabled:

exec
dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK','ACCEPT_SQL_PROFILES','FALSE');

On Mon, Sep 13, 2021 at 11:24 AM Pap <oracle.developer35_at_gmail.com> wrote:

> Thank You Anton. Not sure what exact patch you fetch the output from but I
> do see in this current version(which is 19.9.0.0.0) at least we have
> "ACCEPT_SQL_PROFILES" set ar TRUE while IS_DEFAULT is 'N', so it means
> somebody must have turned this to the non default value manually on this
> database post migration. Correct me if I'm wrong.
> And to revert it back to default i.e. 'FALSE', can it be done online
> without any issue?
>
> On Mon, Sep 13, 2021 at 12:22 PM Anton Spitsyn <antonio.spitsyn_at_gmail.com>
> wrote:
>
>> Hi Pap,
>>
>> See SYS_AUTO_SQL_TUNING_TASK parameters.
>>
>> In Oracle 19c, the defaults are:
>>
>> SQL> select parameter_name, parameter_value, is_default from
>> dba_advisor_paramet
>> ers where task_name ='SYS_AUTO_SQL_TUNING_TASK' and parameter_name in
>> ('ACCEPT_S
>> QL_PROFILES','MAX_SQL_PROFILES_PER_EXEC','MAX_AUTO_SQL_PROFILES');
>>
>> PARAMETER_NAME PARAMETER_VALUE IS_DEFAULT
>> ------------------------------ --------------- ----------
>> ACCEPT_SQL_PROFILES FALSE Y
>> MAX_AUTO_SQL_PROFILES 10000 Y
>> MAX_SQL_PROFILES_PER_EXEC 20 Y
>>
>> --
>> Anton Spitsyn
>> Database Administrator
>> http://aspitsyn.wordpress.com
>>
>>
>> On Mon, Sep 13, 2021 at 9:22 AM Pap <oracle.developer35_at_gmail.com> wrote:
>>
>>> Thank you Lok. Actually that is what I was trying to find out and
>>> confirm. Is there any way I can get the exact auto job name/schedule which
>>> has created these profiles and also made them enabled?
>>>
>>> On Mon, Sep 13, 2021 at 11:13 AM Lok P <loknath.73_at_gmail.com> wrote:
>>>
>>>> As you mentioned nobody has created it manually and its having TYPE as
>>>> AUTO so it must have been created by some auto task.
>>>>
>>>> On Mon, 13 Sep 2021, 2:05 am Pap, <oracle.developer35_at_gmail.com> wrote:
>>>>
>>>>> Hello Listers, We are seeing a few sqls running longer post 19c
>>>>> migration and when looking into the details , we found the new plan which
>>>>> it opting are having some sql profiles created/attached(having name
>>>>> SYS_SQLPROF****) and forcing the plan to go for a suboptimal ones. Want To
>>>>> understand how those are created for so many(around ~10) sqls. In
>>>>> DBa_sql_profiles they are having TYPE as 'AUTO' and STATUs as ENABLED
>>>>> having a specific TASK_EXEC_NAME. So wanted to understand if any automatic
>>>>> job caused this which should have been turned off during this migration or
>>>>> somebody must have created those by manually running some task , which
>>>>> seems unlikely?
>>>>>
>>>>> Regards
>>>>> Pap
>>>>>
>>>>
>>
>> --
>>
>> Anton Spitsyn
>>
>> Database Administrator
>>
>> http://aspitsyn.wordpress.com
>>
>>

-- 

Anton Spitsyn

Database Administrator

http://aspitsyn.wordpress.com

--
http://www.freelists.org/webpage/oracle-l