Forum Stats

  • 3,780,468 Users
  • 2,254,398 Discussions
  • 7,879,340 Comments

Discussions

Creating a SQL Profile with dbms_sqltune

Laury
Laury Member Posts: 1,657 Silver Badge

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

Tagged:

Answers