oracle P&T

Thursday, March 29, 2012

The idea behind ACS is to improve the execution plans for statements with bind variables. CBO has been enhanced to allow multiple execution plans to be used for a single statement with bind variables, without hard parsing the SQL every time. The magic is done in the soft parse phase of the statement with the help of three new views. This feature is very useful on columns with skewed data, where there are histograms created onto these columns to help optimizer determine the cost of the execution plan. Unlike previous Oracle versions where CBO was peeking values for bind variables, it was not guaranteed that generated plan was “good” enough for all possible values of the bind variables.In 10g, first hard parsed plan with bind variable peeked was used for all other values of bind variable. Obviously this approach was not good.

What is bind variable peeking?

Bind variable peeking is when Oracle’s CBO peeks (waits until he gets the ) value of the bind variable and then optimizes the SQL. But, this is very important: this is done in the hard parsing phase of the SQL. The idea with bind variables is to hard parse it once, and then to reuse N times same SQL statement (soft parsing).

Note that I’ve used method_opt to collect histograms for the table. Histograms are important to determine data distribution in column. They are important to CBO to determine selectivity on bind variable.

LM@ORCL112> -- EXECUTE STATEMENTS FOR THE FIRST TIME USING BIND VARIABLE

lm@ORCL112>variable id NUMBER;

lm@ORCL112>EXEC :id:=50000;

PL/SQL procedure successfully completed.

lm@ORCL112>SELECT /* BNDW */ count(*) FROM test1 WHERE id=:id;

COUNT(*)

----------

49999

lm@ORCL112>exec DBMS_LOCK.SLEEP(15);

PL/SQL procedure successfully completed.

We have two new columns in V$SQL view (IS_BIND_SENSITIVE and IS_BIND_AWARE) and three new views (V$SQL_CS_HISTOGRAM, V$SQL_CS_SELECTIVITY, V$SQL_CS_STATISTICS).

IS_BIND_SENSITIVE - If this column contains a value of (Y)es, it means that column on bind variable has histogram and that the optimizer peeked at the values of the statement’s bind variables so that it can calculate each predicate’s selectivity.

IS_BIND_AWARE – Provide CBO with ability to determine the selectivity of any where clause condition that uses bind variables.If set to YES CBO realized that current plan might not be the optimal for all provided bind variable.

V$SQL_CS_HISTOGRAM – Distributes the frequency (three-bucket histogram) at which Oracle used to decide if a SQL statement was bind-sensitive, including how many times a particular child cursor has been executed.

V$SQL_CS_SELECTIVITY – Contains information about the relative selectivity of a SQL statement’s predicates, including the predicates themselves, and their high and low value ranges.

V$SQL_CS_STATISTICS – Lists the statistics of whether and/or how often an Adaptive Cursor has been shared. The column PEEKED = Y, then the bind set has been used to build the adaptive cursor.

CBO executed second time with soft parse using cursor that is in memory. CBO obviously made mistake as selecting 49999 record and 1 record using the same execution plan is not good. But; we will see that CBO will adapt in the future run.

For the given bind value (ID=2500) oracle returned zero records but also produced new child cursor. Oracle mainly uses CPU and buffer gets to determine whether given bind variable is good candidate for new cursor. We also see that IS_BIND_AWARE for the second cursor is set Y(es).

Below are presented both plans in cursor cache for the given SQL. One is using fast full index scan and the other index range scan.

This time CBO created new cursor child no 3 that is identical as cursor child 1. This time CBO used right plan index range scan for bind variable value id=1. So CBO adapts with all the info that collected during run.Cursor child 0 and 1 have IS_SHAREABLE column set to N and will be first to age out.

When we flush shared pool we lose valuable information that could affect performances. Instead flushing whole shared pool one should use oracle package to delete particular cursor and all child cursors.

dbms_shared_pool.purge('<address,hash_value>','C');

Sometime we can't afford optimizer to make mistakes and learn from them. In this situation after scheduled server reboot we can pin particular cursor in cursor cache from data dictionary.