We all know ... Using bind variables --> reduce time for parsing (by doing soft-parsing instead of hard parsing) --> improve the query performance.
BUT there is a problem. Using bind variable is not always the best option. It could result in a sub-optimal query execution. When our query is supposed to return more than a certain % of the table rows, FTS(Full Table Scan) is more efficient than index scan.** When we use a bind variable, we remove the ability for the optimizer to choose the option of a full table scan because the optimizer doesn't get a chance to compare the value of the bind variable with the data distribution in the column(Histogram). In other words, using bind variable is very economic b/c we need one shared cursor in the library cache to execute more than on SQL statement. However, it could sometimes results in a poor query performance.
** more than 3%~10% of the table rows to be returned as per Oracle while some others argue differently.
Solutions:
(1) Oracle 9i - there was "Bind Variable Peeking" with 9i. When a query with bind variable is parsed first time, the optimizer takes a look at the value of the bind variables and then comes up with a best execution plan based(on the value of the bind variables). --> This is good. HOWEVER, there was another problem. The same execution plan(from the first parsing) will be REUSED for all subsequent executions of the query. The same plan will be used even if the query gets different value in its bind variable.
(2) Oracle 11g - To address the issue of Bind Variable Peeking, Oracle introduced something called, Adaptive Cursor Sharing with 11g --> a compromising solution between 'reducing time spent for hard parsing' and 'keeping the good level of query performance'. We first need to set a parameter as following.
SQL> alter session set optimizer_features_enable="11.1.0.6" ; (11.1.0.6 or later version #)
What happens internally is...
> at the 1st run of a query with bind variable : Bind peeking takes place. The cursor for the query execution starts its life with a hard parse. The cursor is marked as a bind-sensitive cursor. In addition, some more information are stored in the cursor: the predicate selectivity, the bind values, the execution statistics, and an initial selectivity cube(a range of selectivity that allows a soft parsing of a subsequent execution of the query).
> at the 2nd run of the query with a new set of bind values : the system finds the predicate selectivity for the new set of bind values and see if it falls within the initial selectivity cube. If it falls within the initial cube, the system uses the existing execution plan. If not, the system does a hard parsing and creates a new execution plan. And the system also creates a new selectivity cube based on the selectivity of the new set of bind values. (By now, the cursor has two different selectivity cubes.)
> at the next subsequent runs : the sql parsing and the execution plan are determined in the same fashion. However, when the hard parse generates the same execution plan as any of existing plans, the system merges the two selectivity cubes(the existing one + the new one), which will result in a new bigger selectivity cubes. The selectivity cubes are merges and get bigger as the query runs more and more.
------------------------------------------------------------
*** For more information about Adaptive Cursor Sharing, refer to following links
http://www.dba-oracle.com/t_11g_adaptive_cursor_sharing.htm
*** We probably don't want to use bind variables that often in OLAP systems(Data warehousing) where the parsing of the query is a very small percentage of the work done when submitting a query.
> reference : http://www.akadia.com/services/ora_bind_variables.html
No comments:
Post a Comment