2 Replies Latest reply: Jul 2, 2013 7:29 AM by 932784 RSS

    Sql Profile Error ORA-13786: missing SQL text of statement object "1"

    1000565
      Hi,

      I am testing a Sql Profile sample executiion with the hint result_cache for faster execution of a query. I have created a tuning task but got no recommendations. But when i tried to create sql profile with the pkg dbms_sqltune.accept_sql_profile i am getting an error. The db version is 11.1.0.7. Given below is the sql and the output.
      Please let me know if you have any suggestions.

      -- Accept SQL Profiles
      SET SERVEROUTPUT ON
      DECLARE
      l_sql_tune_task_id VARCHAR2(2000);
      BEGIN
      l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
      task_name => 'Permissionstab_tuning_task',
      name => 'Permissionstab_profile',
      description => 'sql profile for Permissionstab_profile',
      force_match => TRUE);
      --profile_type => DBMS_SQLTUNE.PX_PROFILE);
      DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
      END;
      /

      with profile_type commented out output
      DECLARE
      *
      ERROR at line 1:
      ORA-13786: missing SQL text of statement object "1" for tuning task "Permissionstab_tuning_task"
      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
      ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 11049
      ORA-06512: at "SYS.PRVT_SQLPROF_INFRA", line 30
      ORA-06512: at "SYS.DBMS_SQLTUNE", line 5836
      ORA-06512: at line 4

      including profile_type
      profile_type => DBMS_SQLTUNE.PX_PROFILE);
      *
      ERROR at line 9:
      ORA-06550: line 9, column 35:
      PLS-00302: component 'PX_PROFILE' must be declared
      ORA-06550: line 4, column 5:
      PL/SQL: Statement ignored




      Given below is the complete script and output
      CONN / AS SYSDBA
      GRANT CREATE ANY SQL PROFILE TO ariba;
      GRANT DROP ANY SQL PROFILE TO ariba;
      GRANT ALTER ANY SQL PROFILE TO ariba;

      conn ariba/****;
      EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('Permissionstab_tuning_task');

      -- Tuning task created for a manually specified statement.
      DECLARE
      l_sql VARCHAR2(4000);
      l_sql_tune_task_id VARCHAR2(1000);
      BEGIN

      l_sql := 'SELECT /*+ result_cache */ rootId, pe_DefaultingSetBits, pe_Version, pe_Creator, pe_Active, pe_AdapterSource, pe_AdapterFlag, pe_PartitionN
      umber, '||
      'pe_PurgeState, pe_TimeCreated, pe_TimeUpdated, pe_CommonId, pe_ServerVersion, pe_UniqueName, pe_Name, mls_DefaultingSetBits, '||
      'mls_PrimaryString, mls_MaxTranslationID, mls_Translations, mls_PrimaryStringLanguageID, mls_LocalizationKey, pe_Description, '||
      'mls0_DefaultingSetBits, mls0_PrimaryString, mls0_MaxTranslationID, mls0_Translations, mls0_PrimaryStringLanguageID, '||
      'mls0_LocalizationKey, pe_Created, pe_Modified '||
      'FROM PermissionTab '||
      'where pe_UniqueName = '||''''||'PurchasingAgent'||''''||' '||
      'and ( pe_PurgeState = :1 ) AND pe_PartitionNumber = :2';

      l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
      sql_text => l_sql,
      user_name => 'ARIBA',
      scope => DBMS_SQLTUNE.scope_comprehensive,
      time_limit => 60,
      task_name => 'Permissionstab_tuning_task',
      description => 'Tuning task for an Permissionstab query.');
      DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
      END;
      /

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

      SELECT task_name, status FROM dba_advisor_log WHERE owner = 'ARIBA';

      SET LONG 10000;
      SET PAGESIZE 1000
      SET LINESIZE 200
      SELECT DBMS_SQLTUNE.report_tuning_task('Permissionstab_tuning_task') AS recommendations FROM dual;
      SET PAGESIZE 24
      -- Accept SQL Profiles
      SET SERVEROUTPUT ON
      DECLARE
      l_sql_tune_task_id VARCHAR2(2000);
      BEGIN
      l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile (
      task_name => 'Permissionstab_tuning_task',
      name => 'Permissionstab_profile',
      description => 'sql profile for Permissionstab_profile',
      force_match => TRUE,
      profile_type => DBMS_SQLTUNE.PX_PROFILE);
      DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
      END;
      /



      11:17:17 SYS@FLBUYPM 02-APR-13> @sql_profile_result_cache.sql
      Connected.

      Grant succeeded.


      Grant succeeded.


      Grant succeeded.

      Connected.

      PL/SQL procedure successfully completed.


      PL/SQL procedure successfully completed.


      PL/SQL procedure successfully completed.


      TASK_NAME STATUS
      ------------------------------ -----------
      Permissionstab_tuning_task COMPLETED


      RECOMMENDATIONS
      --------------------------------------------------------------------------------
      GENERAL INFORMATION SECTION
      -------------------------------------------------------------------------------
      Tuning Task Name : Permissionstab_tuning_task
      Tuning Task Owner : ARIBA
      Workload Type : Single SQL Statement
      Scope : COMPREHENSIVE
      Time Limit(seconds): 60
      Completion Status : COMPLETED
      Started at : 04/02/2013 11:17:20
      Completed at : 04/02/2013 11:17:20

      -------------------------------------------------------------------------------
      Schema Name: ARIBA
      SQL ID : 2y6yar7r71tj1
      SQL Text : SELECT /*+ result_cache */ rootId, pe_DefaultingSetBits,
      pe_Version, pe_Creator, pe_Active, pe_AdapterSource,
      pe_AdapterFlag, pe_PartitionNumber, pe_PurgeState,
      pe_TimeCreated, pe_TimeUpdated, pe_CommonId, pe_ServerVersion,
      pe_UniqueName, pe_Name, mls_DefaultingSetBits,
      mls_PrimaryString, mls_MaxTranslationID, mls_Translations,
      mls_PrimaryStringLanguageID, mls_LocalizationKey,
      pe_Description, mls0_DefaultingSetBits, mls0_PrimaryString,
      mls0_MaxTranslationID, mls0_Translations,
      mls0_PrimaryStringLanguageID, mls0_LocalizationKey, pe_Created,
      pe_Modified FROM PermissionTab where pe_UniqueName =
      'PurchasingAgent' and ( pe_PurgeState = :1 ) AND
      pe_PartitionNumber = :2

      -------------------------------------------------------------------------------
      ADDITIONAL INFORMATION SECTION
      -------------------------------------------------------------------------------
      - At least one important bind value was missing for this sql statement. The
      accuracy of the advisor's analysis may depend on all important bind values
      being supplied.

      -------------------------------------------------------------------------------
      EXPLAIN PLANS SECTION
      -------------------------------------------------------------------------------

      1- Original
      -----------
      Plan hash value: 778402129

      --------------------------------------------------------------------------------
      ---------------------------
      | Id | Operation | Name | Rows | Byte
      s | Cost (%CPU)| Time |
      --------------------------------------------------------------------------------
      ---------------------------
      | 0 | SELECT STATEMENT | | 1 | 29
      8 | 1 (0)| 00:00:01 |
      | 1 | RESULT CACHE | 9qsg9vxnpk5r13h9gb3wcgujg1 | |
      | | |
      |* 2 | TABLE ACCESS BY INDEX ROWID| PERMISSIONTAB | 1 | 29
      8 | 1 (0)| 00:00:01 |
      |* 3 | INDEX UNIQUE SCAN | ARIBAINDEX341 | 1 |
      | 1 (0)| 00:00:01 |
      --------------------------------------------------------------------------------
      ---------------------------

      Predicate Information (identified by operation id):
      ---------------------------------------------------

      2 - filter("PE_PURGESTATE"=TO_NUMBER(:1))
      3 - access("PE_UNIQUENAME"='PurchasingAgent' AND "PE_PARTITIONNUMBER"=TO_NUMB
      ER(:2))

      Result Cache Information (identified by operation id):
      ------------------------------------------------------

      1 - column-count=30; dependencies=(ARIBA.PERMISSIONTAB); attributes=(single-r
      ow); parameters=(nls, :1, :2); name="SELECT /*+ result_cache */ rootId, pe_Defau
      ltingSetBits, pe_Version, pe_Creator, pe_Active, pe_AdapterSource, pe_AdapterFla
      g, pe"

      -------------------------------------------------------------------------------


      DECLARE
      *
      ERROR at line 1:
      ORA-13786: missing SQL text of statement object "1" for tuning task "Permissionstab_tuning_task"
      ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
      ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 11049
      ORA-06512: at "SYS.PRVT_SQLPROF_INFRA", line 30
      ORA-06512: at "SYS.DBMS_SQLTUNE", line 5836
      ORA-06512: at line 4


      11:17:20 ARIBA@FLBUYPM 02-APR-13>



      profile_type => DBMS_SQLTUNE.PX_PROFILE);
      *
      ERROR at line 9:
      ORA-06550: line 9, column 35:
      PLS-00302: component 'PX_PROFILE' must be declared
      ORA-06550: line 4, column 5:
      PL/SQL: Statement ignored

      Edited by: 997562 on Apr 2, 2013 8:34 AM
        • 1. Re: Sql Profile Error ORA-13786: missing SQL text of statement object "1"
          1000565
          Can someone help me out? I cannot create sql profile. When i run dbms_sqltune.accept_sql_profile it fails with the above error.
          • 2. Re: Sql Profile Error ORA-13786: missing SQL text of statement object "1"
            932784

            Hi Experts,

             

            i too getting this error while attaching the tuning task to the sql profiler, below are the details, appreciate any help on this.

             

            SQL> SQL> VAR profile_name VARCHAR2(30);

            SQL> BEGIN

               :profile_name := DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(task_name => 'sql_tuning_task');

            END;

            /  2    3    4

            BEGIN

            *

            ERROR at line 1:

            ORA-13786: missing SQL text of statement object "1" for tuning task "sql_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 line 2

             

             

             

             

            SQL> select banner from v$version;

             

             

            BANNER

            --------------------------------------------------------------------------------

            Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - Production

            PL/SQL Release 11.2.0.3.0 - Production

            CORE    11.2.0.3.0      Production

            TNS for Linux: Version 11.2.0.3.0 - Production

            NLSRTL Version 11.2.0.3.0 - Production