Re: New Sql profiles

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

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