Home » RDBMS Server » Performance Tuning » ORA-16951: Too many bind variables supplied for this SQL statement (merged) (oracle 11gr2 linux)
ORA-16951: Too many bind variables supplied for this SQL statement (merged) [message #676596] Fri, 21 June 2019 06:02 Go to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member


Hi Guys,

I am running dbms_sqltune for tuning sqlid,most of sqlid i am able to execute with this ,few times i get error ORA-16951 in some of sqlid

below is syntax i used

DECLARE
stmt_task VARCHAR2(64);
BEGIN
stmt_task:=dbms_sqltune.create_tuning_task(sql_id => '1XXXXXXXXXX',
time_limit => 3600,
task_name => 'Tune_name1',
description => 'Task to tune Tune_name1 sql_id');
END;
/


its getting executed and report also coming,but its not giving suggestion but error as below

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

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


I checked query only 2 bind variables are used,i successfully run many sqlid with 6 or more bind var also so i rule out this is issue
and i gone through oracle docs,they mentioned to remove bind_list => sql_binds(anydata.convertnumber(100))
but i am not using this parameter in my syntax so this also is ruled out (if it try to use it giving syntax error)

please suggest what could be issue

Thanks
Re: dbms_sqltune error ORA-16951 [message #676597 is a reply to message #676596] Fri, 21 June 2019 07:28 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Post the text of the statement.

Re: dbms_sqltune error ORA-16951 [message #676598 is a reply to message #676597] Fri, 21 June 2019 07:33 Go to previous messageGo to next message
BlackSwan
Messages: 26643
Registered: January 2009
Location: SoCal
Senior Member
>I checked query only 2 bind variables are used,i successfully run many sqlid with 6 or more bind var also so i rule out this is issue
so how do you convince Oracle to use bind variables & which bind variables to use?
ORA-16951: Too many bind variables supplied for this SQL statemen [message #677118 is a reply to message #676596] Tue, 20 August 2019 04:23 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Hi Guys,

In some of sql queries tuning advisor gives error as below:
ORA-16951: Too many bind variables supplied for this SQL statemen

even though no bind variable is used
or nieither bind_list => sql_binds(anydata.ConvertNumber(100)) is used in the syntax

What could be issue

Thanks
Re: dbms_sqltune error ORA-16951 [message #677119 is a reply to message #676597] Tue, 20 August 2019 04:34 Go to previous messageGo to next message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
Its your common reply to all Razz
Re: dbms_sqltune error ORA-16951 [message #677124 is a reply to message #677119] Tue, 20 August 2019 07:28 Go to previous messageGo to next message
Bill B
Messages: 1919
Registered: December 2004
Senior Member
How are we supposed to trouble shoot your tuning task without knowing what it is tuning? The statement being tuned has binds, you just don't see them. If you do a statement like

my_test := 'XYZ';
select * from my_table where col1 = my_test;

the optimizer will change that to
select * from my_table where col1 = :b1;
and will pass a bind.
Re: dbms_sqltune error ORA-16951 [message #677126 is a reply to message #677124] Tue, 20 August 2019 10:01 Go to previous message
dba4oracle
Messages: 100
Registered: June 2010
Senior Member
its large query may be hundreds of line,problem is in query or sqltune syntax or some db para required to set
Previous Topic: Slow query with hash clustering (latch free - multiblock read objects)
Next Topic: queue management system
Goto Forum:
  


Current Time: Sat Dec 14 07:58:10 CST 2019