Re: New Sql profiles

Missed adding the group.

Is there a way to see when this parameter(accept_sql_profiles) changed in the past? In dba_hist_parameter it only shows history of change of system parameters i.e. from v$parameters. So is there any such way to see exactly the value of parameter 'accept_sql_profiles' at a certain point in time or say when its value got changed in dba_advisor_parameters?

On Mon, Sep 13, 2021 at 9:23 PM Pap <oracle.developer35_at_gmail.com> wrote:

> Thank you so much. That helped.
>
> Is there any way to see what the value of these parameters were before
> upgrading? Want to see if this happened as part of the upgrade or was it
> in the same state even before upgrade?
>
>
>
> On Mon, Sep 13, 2021 at 4:25 PM Anton Spitsyn <antonio.spitsyn_at_gmail.com>
> wrote:
>
>> 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