Hi,
I am working with Oracle 19c.
I am creating a tuning task like:
set serveroutput on
declare
l_sql_tune_task_id varchar2(100);
begin
l_sql_tune_task_id := dbms_sqltune.create_tuning_task (
sql_id => '27tryus09m4bd',
scope => dbms_sqltune.scope_comprehensive,
time_limit => 60,
task_name => '27tryus09m4bd_tuning_task',
description => 'tuning task for statement 27tryus09m4bd.'
);
dbms_output.put_line('l_sql_tune_task_id (task name): ' || l_sql_tune_task_id);
end;
/
l_sql_tune_task_id (task name): 27tryus09m4bd_tuning_task
Then I view the recommendation with:
set long 10000
set pagesize 1000
set linesize 200
select dbms_sqltune.report_tuning_task('27tryus09m4bd_tuning_task') as recommendations from dual;
The recommendations shows up that the execution plan will benefit from an index.
Suppose I am happy with the initial plan (thus with no index) and that I want to create a profile for this SQL IS and for the initial plan. I run then this procedure:
set serveroutput on
declare
l_sql_tune_task_id varchar2(20);
begin
l_sql_tune_task_id := dbms_sqltune.accept_sql_profile (
task_name => '27tryus09m4bd_tuning_task',
name => '27tryus09m4bd__tuning_task_PROFILE'
);
dbms_output.put_line('l_sql_tune_task_id (task name): ' || l_sql_tune_task_id);
end;
/
But I get this errors:
ERROR at line 1:
ORA-13831: SQL profile or patch name specified is invalid
ORA-06512: at "SYS.DBMS_SQLTUNE", line 3996
ORA-06512: at "SYS.DBMS_SQLTUNE", line 10854
ORA-06512: at line 4
Also the following query for that SQL ID returns no Plan base line:
select s.sql_id, s.sql_plan_baseline
from v$sql s
where s.sql_id = '27tryus09m4bd';
Can someone explain me why I get this errors and why I cannot create a profile?
Thanks by advance.
Kind Regards