Re: New Sql profiles

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