Forum Stats

  • 3,872,132 Users
  • 2,266,392 Discussions
  • 7,911,060 Comments

Discussions

ORA-13786: missing SQL text of statement object - Unable to profile a sql_id in oracle 11g -

IamHariKrishna
IamHariKrishna Member Posts: 192
edited Feb 17, 2015 6:28PM in General Database Discussions

11.2.0.3.5 / 2 node rac on rhel-6 / 64-bit

I would like to a profile a sql_id since its following 4 plans and one of them is the optimal.

But im unable to do it as it throwing ORA-13786.

Steps I followed :

{code}

SET SERVEROUTPUT ON

-- Tuning task created for specific a statement from the AWR.

DECLARE

  l_sql_tune_task_id  VARCHAR2(100);

BEGIN

  l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (

                          begin_snap  => 29676,

                          end_snap    => 29707,

                          sql_id      => 'fjndcnvzkjkb5',

                          scope       => DBMS_SQLTUNE.scope_comprehensive,

                          time_limit  => 60,

                          task_name   => '420tavt57dxkx_tuning_task',

                          description => 'Tuning task for statement 420tavt57dxkx in AWR.');

  DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);

END;

/

EXEC DBMS_SQLTUNE.execute_tuning_task(task_name => 'fjndcnvzkjkb5_CFO_tuning_task');

SET LONG 10000;

SET PAGESIZE 1000

SET LINESIZE 200

SELECT DBMS_SQLTUNE.report_tuning_task('420tavt57dxkx_tuning_task') AS recommendations FROM dual;

SET PAGESIZE 24

SQL>  execute dbms_sqltune.accept_sql_profile(task_name =>'420tavt57dxkx_tuning_task_task', replace => TRUE,force_match  => TRUE);

BEGIN dbms_sqltune.accept_sql_profile(task_name =>420tavt57dxkx_tuning_task', replace => TRUE,force_match  => TRUE); END;

*

ERROR at line 1:

ORA-13786: missing SQL text of statement object "1" for tuning task "420tavt57dxkx_tuning_task"

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95

ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 16442

ORA-06512: at "SYS.PRVT_SQLPROF_INFRA", line 31

ORA-06512: at "SYS.DBMS_SQLTUNE", line 7544

ORA-06512: at "SYS.DBMS_SQLTUNE", line 7568

ORA-06512: at line 1

{code}

Can somebody help me out on this?

Tagged:
This discussion has been closed.