This discussion is archived
2 Replies Latest reply: Oct 3, 2007 10:15 PM by 530453 RSS

ORA-16951 from SQL Tuning Advisor

user302675 Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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