2 Replies Latest reply: Oct 4, 2007 12:15 AM by 530453 RSS

    ORA-16951 from SQL Tuning Advisor

    user598253
      Hello,
      Sorry for the long post. I'm trying to run the SQl Tuning Advisor from a shell script. The script run 2 pl/sql scripts. the first creates a tuning task, runs the task, and checks for completion. The 2nd script displays the report. Here are my 2 pl/sql scripts
      === 1st script
      set verify off
      set long 10000
      set longchunksize 10000
      set linesize 132
      set pagesize 100
      set serveroutput on
      --
      declare
      v_tune_task varchar2(30);
      v_task_name varchar2(30);
      v_status varchar2(11);
      v_sqltext clob;
      v_sqlid varchar2(13);
      v_dbname varchar2(9);
      v_timestamp varchar2(15);
      err_cde number;
      err_msg varchar2(85);

      CURSOR c1 IS
      select status
      from user_advisor_tasks
      where task_name = v_task_name;
      --
      begin
      --
      v_sqlid := '&1';
      v_task_name := '&2';
      --
      dbms_output.put_line('v_sqlid => ' || v_sqlid);
      dbms_output.put_line('v_task_name => ' || v_task_name);
      --
      select sql_fulltext into v_sqltext
      from v$sqlarea
      where sql_id = v_sqlid;
      --
      v_tune_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(
      sql_text => v_sqltext,
      bind_list => sql_binds(anydata.ConvertVarchar2(100)),
      user_name => 'SYSTEM',
      scope => 'COMPREHENSIVE',
      time_limit => 1800,
      task_name => v_task_name,
      description => 'SQL Tuning pl/sql');
      --
      DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => v_task_name);
      --
      open c1;
      loop
      fetch c1 into v_status;
      dbms_output.put_line('...' || v_status);
      exit when v_status = 'COMPLETED';
      end loop;
      close c1;
      --
      EXCEPTION
      when others then
      begin
      err_msg := SUBSTR(SQLERRM,1,100);
      err_cde := SQLCODE;
      dbms_output.put_line(' '||err_msg||' '||err_cde);
      end;
      END;
      /
      === 2nd script
      set verify off
      set long 50000
      set longchunksize 50000
      set linesize 132
      set pagesize 100
      --
      select dbms_sqltune.report_tuning_task('&1') from dual
      /

      The report show 1 error, ORA-16951. Here is the report.
      =====
      v_sqlid => 8u38165phpqmv
      v_task_name => PPRD7_8u38165phpqmv_1003071426
      ...COMPLETED

      PL/SQL procedure successfully completed.


      DBMS_SQLTUNE.REPORT_TUNING_TASK('PPRD7_8U38165PHPQMV_1003071426')
      --------------------------------------------------------------------------------
      ----------------------------------------------------
      GENERAL INFORMATION SECTION
      -------------------------------------------------------------------------------
      Tuning Task Name : PPRD7_8u38165phpqmv_1003071426
      Tuning Task Owner : SYSTEM
      Scope : COMPREHENSIVE
      Time Limit(seconds) : 1800
      Completion Status : COMPLETED
      Started at : 10/03/2007 14:26:49
      Completed at : 10/03/2007 14:26:49
      Number of Errors : 1

      -------------------------------------------------------------------------------
      Schema Name: SYSTEM
      SQL ID : 2t6630rp7g317
      SQL Text : SELECT DISTINCT(RCRAPP1_PIDM),SYSDATE FROM FAISMGR.RCRAPP1
      ,FAISMGR.RORSTAT WHERE RCRAPP1_PIDM = RORSTAT_PIDM AND
      RCRAPP1_AIDY_CODE = '0708' AND RCRAPP1_AIDY_CODE =
      RORSTAT_AIDY_CODE AND ((RCRAPP1_ACTIVITY_DATE >=
      TO_DATE('21-SEP-2007','DD-MON-YYYY') AND RCRAPP1_CURR_REC_IND =
      'Y' )OR (RORSTAT_TGRP_CODE IN ('INACTR','INACTS','INACTV','INSANA
      ','NOADMT','REJISR','REVIEW') OR RORSTAT_TGRP_CODE IN
      ('NNVERN','NNVERR','DEPVRR') OR RORSTAT_TGRP_CODE IN
      ('CNFLCT','CNFLCF','RAPGRP','NOAID','MAYGRD') ))

      -------------------------------------------------------------------------------
      ERRORS SECTION
      -------------------------------------------------------------------------------
      - ORA-16951: Too many bind variables supplied for this SQL statement.

      DBMS_SQLTUNE.REPORT_TUNING_TASK('PPRD7_8U38165PHPQMV_1003071426')
      --------------------------------------------------------------------------------
      =====
      Any help will greatly appreciated.

      Thanks, Mike Lamar
        • 1. Re: ORA-16951 from SQL Tuning Advisor
          damorgan
          1. Get rid of the cursor. There is nothing in your code that requires one. Why is it everyone wants to create and open a cursor? Please do not use things without reading about their proper usage.

          2. The error message is quite self explanatory. What don't you understand about it?
          Start with a simple SQL statement and add elements to it one at a time.
          • 2. Re: ORA-16951 from SQL Tuning Advisor
            530453
            16951, 00000, "Too many bind variables supplied for this SQL statement."
            // *Cause:  Binding this SQL statement failed because too many bind variables were supplied.
            // *Action: Pass the correct number of bind variables.

            Regards