| ORA-16951: Too many bind variables supplied for this SQL statement (merged) [message #676596] |
Fri, 21 June 2019 06:02  |
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 #677124 is a reply to message #677119] |
Tue, 20 August 2019 07:28  |
Bill B
Messages: 1971
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
