Still, there have been several occasions where I've gone looking for bind variables for a given SQL that had performance issues, only to find that some of the bind variables were not captured.That is not a good thing.
I always just wrote it off to some annoying bug, something special that the optimizer did when parsing the SQL statement or some other undocumented functionality that would someday surface.
Lo and behold - the answer seemed to surface today in the documentation. I happened to be looking at the documentation for v$sql_bind_capture and there it was - in black and white - the seeming reason some of my bind variables had been missing:
Bind data
One of the bind values used for the bind variable during a past execution of its associated SQL statement. Bind values are not always captured for this view. Bind values are displayed by this view only when the type of the bind variable is simple (this excludes LONG, LOB, and ADT datatypes) and when the bind variable is used in the
WHERE or HAVING clauses of the SQL statement.There you go! It appears that bind variable capture does not happen in several cases. In the one very repeatable case I had, there are a number of bind variables used before the where clause appears - and none of those is captured. There are scalar sub-queries in the select clause, there are some case statements that have bind variables - all of these are not captured. The first bind variable captured is the first one that shows up in the where clause.
Yet - being a skeptic - after my discovery, I went and did some testing.
I ran the contrived test SQL statement below (tables have been renamed to protect the innocent), trying to validate my assumptions - thinking I would not see the bind variable captured:
select dummy,
(select count(*) from schema.testtable where test_num>2000000) as total_tables from dual;
Yet, it was indeed captured for this execution as you can see here:
select dbms_sqltune.extract_binds(bind_data) from v$sql where sql_id='01pnj1r5r1n90';
DBMS_SQLTUNE.EXTRACT_BINDS(BIND_DATA)(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED, VALUE_STRING, VALUE_ANYDATA())
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 2, 'NUMBER', NULL, NULL, NULL, 22, '29-AUG-17', '2000000', ANYDATA()))
The obvious answer might seem to be: Well, maybe it works differently when cursor_sharing is set to FORCE - as is the case on our databases. Perhaps it's the conversion of the literal to a bind variable that causes it to get captured (which is my guess). So, what happens if I define the bind variable from the outset?
alter system flush shared_pool;
var my_var number
exec :my_var:=2000000
select dummy, (select count(*) from schema.testtable where test_num>:my_var) as total_tables from dual;
SQL> select dbms_sqltune.extract_binds(bind_data) from v$sql where sql_id='0gp8g0rd67w9w';
DBMS_SQLTUNE.EXTRACT_BINDS(BIND_DATA)(NAME, POSITION, DUP_POSITION, DATATYPE, DATATYPE_STRING, CHARACTER_SID, PRECISION, SCALE, MAX_LENGTH, LAST_CAPTURED, VALUE_STRING, VALUE_ANYDATA())
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_BIND_SET(SQL_BIND(NULL, 1, NULL, 2, 'NUMBER', NULL, NULL, NULL, 22, '29-AUG-17', '2000000', ANYDATA()))
So... now I'm sitting back at square 1 again.
Based on my initial discovery and theories, I assumed when the description of bind data indicated that it would not capture anything before the where clause of the main body of the query. However, this test seems to refute that belief. Perhaps the complexity of the SQL statement itself has some impact on bind capture. There is a good lesson here - assume nothing until you have tested it out in 64 different ways. (ok, that might be over kill...)
Here I thought I'd found the answer and instead, I just find more questions!
Wooo Hooo!
It's my anniversary today, so I won't be researching this anymore for now... But, it is an interesting problem.
