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