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@xxxxxxxxx> 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@xxxxxxxxx>
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@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
--
Anton Spitsyn
Database Administrator
http://aspitsyn.wordpress.com
