Adaptive Cursor Sharing – An Overview

Continuing my previous posts about cursor sharing and bind peeking, in this post will discuss about Adaptive Cursor Sharing (ACS), which is a mechanism introduced in 11g to overcome bind peeking disadvantages.

Bind Aware cursor sharing is another name of ACS. It allows optimizer to compare the effectiveness of execution plans between executions with different bind variable values. There is few columns in v$sql view for check cursor sharing properties.

• is_bind_sensitive: Y or YES indicates bind peeking is used for generating plan, also adaptive cursor sharing may/may not used.
• is_bind_aware: Y or YES means adaptive cursor sharing is used.
• is_shareable: Y or Yes says cursor can be shared.

Example:

SQL> CREATE TABLE t AS SELECT rownum AS id, rpad('*',100,'*') AS pad FROM dual CONNECT BY level <= 10000;
SQL> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);
SQL> BEGIN
	2	dbms_stats.gather_table_stats(
	3	owname	=>SYS,
	4	tabname	=>'t',
	5	estimate_percent	=>100,
	6 	method_opt =>'for all column size 1'
	7	);
	8	END;
	9 /
SQL>
SQL> SELECT count(pad), count(DISTINCT id), min(id), max(id) from t;

COUNT(ID)	COUNT(DISTINCT ID)	MIN(ID)		MAX(ID)
----------	-----------------	--------	--------
	10000		10000		1		10000

By default with bind variables the child cursor can be shared. Depending on the peeked value (10 or 10000), a full table scan or an index range scan is used.

Executing different SQL.

Result: Second sql should favor full table scan but due to bind peeking used first SQL’s plan. Also you can see the SQL ID is bind sensitive but not bind aware yet. When we execute above sql with different values multiple times, will find that optimizer start picking optimized plans.

Now with smaller value again to check if that still using Index:

As seen below new child has been created and value is ‘Y’ for IS_BIND_SENSITIVE and IS_BIND_AWARE means it is using Adaptive Cursor Sharing.

New child cursor 1 has been generated and that is BIND AWARE. CHILD_NUMBER 0 becomes is_shareable=N which means it will aged out from memory if library cache has less space. When cursor is bind IS_BIND_SENSITIVE all executions will be monitored for different values of bind variables, cursor made IS_BIND_AWARE and plan generated on basis of selectivity.

Next will check how it decides to make cursor bind aware and new plan. Below views comes in action

• v$sql_cs_histogram: Stores number of execution’s per bucket and used to decides when cursor should marked as IS_BIND_AWARE.
• v$sql_cs_selectivity: Used by oracle when cursor is marked as IS_BIND_AWARE. It contains selectivity of bind variable with low and high range of each child cursor.

Example: Flush shared pool and execute the query couple of time.

SQL> execute :id :=10;
SQL> SELECT count(pad) FROM t WHERE id < :id;

COUNT(PAD)
---------
	9

CHILD_NUMBER 0, BUCKET_ID is 0 and having count 5. Then, the cursor is executed several times with different range of values(10 to 990)
The number of executions to invalidate it depends on the magnitude of the variation.

Finally got 1 more new child cursor with different buckets, and after doing some more executions with greater values (999999999). Now in v$sql we can see new child cursor 1 and 2 has been created and its using ACS.

v$sql_cs_selectivity view helps deciding bucket_id by calculating Selectivity. Selectivity is as value between 0 and 1 representing fraction of rows filtered by an operation. e.g. if operation reads 120 rows from table and return 18 from that selectivity will be 0.15(18/120).

When v$sql_cs_selectivity value occurred, it indicates:

• Cursor child_number is marked as IS_BIND_AWARE and from now on for every execution optimizer will peek at bind variable and check selectivity.
• If selectivity is of bind variable is found between LOW and HIGH columns values of any of CHILD_NUMBER it will share plan with that
  or else new Plan will be generated on basis of new LOW and HIGH values.

Points to remember:

1. Adaptive cursor sharing is enabled by default on database.
2. Adaptive cursor sharing will not work if stats are not updated.
3. Adaptive cursor sharing will not work if sql is using more than 14 bind variables.
4. We need at least 1 inefficient execution to take Adaptive cursor sharing advantage but from 11.1.0.7 version we can use bind_aware hint in sql to avoid that limitation.

Thanks for reading…

subscribe to get notified for latest post.