We hear a lot about bind variables. Tom Kyte says bind variables are good and we should always use them.
Bind variables are good for library cache management. They have the same SQL text so they hard parse just one time and that particular explain plan is reused until the SQL either flushes out of the library cache (this doesn’t happen that much anymore…) or stats are run invalidating SQL with calls to those objects.
There are a couple of ways to see the bind variables associated with the SQL and explain plans. IF you have the SQL ID, you can look up the bind variable data using V$SQL_BIND_CAPTURE. The important fields are: NAME, POSITION, DATATYPE_STRING, and VALUE_STRING. This is useful when you are reviewing problem SQL either from the library cache or stored in AWR using the view DBA_HIST_SQLTEXT.
***Note*** The DBA_HIST_* views are part of the AWR system (automated workload repository). Queries to these tables requires valid license to OEM diagnostics and tuning pack - you cannot assume because they exist that you are allowed to use them.
Another way is to use the DBMS_XPLAN.Display_Cursor procedure using this syntax after executing the SQL:
Select * from table(DBMS_XPLAN.Display_Cursor (format => typical + peeked_binds));
Problem: Bind variables can be a challenge for the SQL itself. Bind variables are designed to allow for SQL to not need to be re-parsed when the where predicate changes. Oracle11 with its Adaptive Cursor Sharing has automated, to a certain degree, to catch these SQL with bind variables that are accessing skewed data…ie: sometimes the SQL runs well, sometimes it doesn’t. I often use the example of a name field where the first look up application. The scenario is this table has skewed data. Lots of SMITH names, lots of JONES names but few HOTKA names. Lets say there is a nice index on this column as well. Lets also assume stats are run on Sundays and the first request for this look up looks up a HOTKA name. Oracle CBO hard parses the SQL and comes up with an explain plan using the index. Next execution of this SQL passes SMITH in via the bind variable. Oracle will use the index from the explain plan instead of doing a full table scan that would be much better…because…the plan associated with the SQL has an index access in it.
Solution 1: Oracle11+ has a solution for this skewed data dilemma. The upside is if there is a histogram on the database column associated with the bind variable, AND, the SQL performs poorly as compared to the prior execution (thank you AWR!)…Oracle11 will check the bind variable with the histogram and if there is a significant change in row counts…will produce another child cursor (hard parse) with an explain plan better suited for this different volume of data. The serious downside here is that the SQL has to perform poorly once for Oracle to make a change.
Solution 2: Some SQL just shouldn’t have bind variables because they really do need to be hard parsed with each submission. Applications that cannot tolerate the wrong execution even once are candidates. SQL that is using data that are beyond the last collected stats (lets say date fields…stats are run nightly on date sensitive data but tens of thousands of rows are entered hourly so by lunch time, Oracle is simply guessing on what data is actually in the table. These SQL statements could benefit from a DYNAMIC SAMPLE hint…causing Oracle to sample some of the data on a hard parse. The downside here is this takes time and you don’t want to do this if the data is with in range of the stats. You DO want to try this if the data is beyond the last stats collection. The above-mentioned name field is another candidate SQL that is not well suited for bind variables.
Problem: Another bind variable issue is if the bind variable is associated with a between clause. The optimizer would need quite a different explain plan if the range of data was say for a day’s worth of data, a weeks worth of data, or a months worth of data. Combine this with the fact that the above-mentioned Adaptive Cursor Sharing was not designed for this kind of SQL can present quite the parsing challenge for Oracle. Basically…the first execution of this SQL with the original bind variable contents is the explain plan that will be used for all subsequent executions of this SQL.
Solution: Have your application check the bind variable values and submit the SQL but with a clever use of the hint technology. Remember, Oracle uses the SQL Text to find submitted SQL in the library cache (there is a bit more to it than this…ie: check sum/hashing/etc…another discussion on how the library cache works perhaps). The optimizer strips off comments in SQL but if the comments look like a hint…they are not stripped out. SO…if your application can check the differences in the bind variable data…you can include a comment that would cause the different possible data volumes to each have their own SQL hard parsed by simply including the text that looks like a hint /*+ LOW_VOLUME */, /*+ MEDIUM_VOLUME /, and/or /+ HIGH_VOLUME */. Have your application check the differences in the bind variable and submit the SQL to Oracle with these kinds of comments will also allow for better first-time execution of the SQL. This is easily accomplished if your SQL is being fired from PL/SQL. Not so easy if your SQL is coming in from a Form perhaps.
Problem: Yet another challenge is bind variables that work with partitioned objects and contain partitioning key values. Oracle generally leaves this decision for run-time execution and even though Oracle Corp believes this can be a good decision (you can tell when this happens because you get the data ‘KEY’ in both the P_START and P_STOP columns of a partitioned SQL explain Plan), it has been my experience with the SQL that I have seen…causes a full scan across all the partitions.
Solution: Use either DMBS_SQL or my favorite, Execute Immediate, to dynamically submit the SQL for execution. What I do here is to resolve the bind variable back to its data item and submit the SQL for execution to Oracle without bind variables. This technique works well for SQL where the bind variable is imbedded in functions and the functions are causing parsing issues. I have used this technique to resolve the partitioned object access problem described here. Of course, this only works if the SQL is being submitted via PL/SQL.
The Oracle RDBMS is progressing. The library cache likes bind variables as this cuts down on the need to hard parse…a time-consuming process that takes a lot of CPU time. Bind variables can cause issues with subsequent execution of the SQL based on data volumes of the related data being requested. Oracle11 has addressed the bind variable issue with some degree of success. Oracle12, with its adaptive plan management can make changes to the execution plans on the fly if different data volumes are detected at execution time. These technologies don’t address all the issues. Understand your application and the various data volumes a bind variable might request and code accordingly if you can.
Dan HotkaAuthor/Instructor/CEOOracle ACE Director
There's still, in my reasonably humble opinion, a major problem with using bind variables - the developers *still* parse every statement for every execution. They think it's efficient because they use binds while we DBAs bang our heads on the desk in despair!
I had a mini-rant about it years ago at www.toadworld.com/.../it-must-be-efficient-i-m-using-bind-variables.aspx - and it is still valid today judging by some of the code I've been exposed to!