Re: New Sql profiles

Hi Pap,

DBA_AUTOTASK_OPERATION displays all automated maintenance task operations:

SQL> select operation_name, status from dba_autotask_operation;

OPERATION_NAME                                                   STATUS
---------------------------------------------------------------- --------
auto optimizer stats job                                         ENABLED
auto space advisor job                                           DISABLED
automatic sql tuning task                                        DISABLED

There are several historical tables:

dba_autotask_client_history - displays per-window history of job execution counts for each automated maintenance task; dba_autotask_job_history - displays the history of automated maintenance task job runs;
dba_autotask_window_history displays historical information for automated maintenance task windows.

 --
Anton Spitsyn
Database Administrator
http://aspitsyn.wordpress.com

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

> 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
>>>
>>>

-- 

Anton Spitsyn

Database Administrator

http://aspitsyn.wordpress.com

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