Intelligent Cursor Sharing in 11.1.0.7 More testing

I’m continuing my research on Intelligent Cursor Sharing (Adaptive Cursor Sharing). At the symposium this year one thing I pointed out was the range in the view v$sql_cs_selectivity view. There is a high and low to each range. Looking at these values for a set of queries there is a pattern to what is going on but exactly what it means is a bit less then clear to me.

It starts out pretty simple. When I run my simple query:

select /* RVD */ count(*) from big_tab where object_type = :obj_typ

With a couple of values is seems pretty simple. I run it with the values “RULE”, then “SYNONYM” twice, this kicks in Cursor Sharing. And I see this in the v$sql_cs_selectivity view:

LOW = 0.351151
HIGH = 0.429185

Doing a bit of arithmetic and it is easy to find that the midpoint of this range is .390168. The selectivity for SYNONYM in this table is 0.390168 so it’s very clear that this is the midpoint of this range. The span of the range is .078034 from the low to the high values.

I run another query with the value set to INDEX, and a new range is created with:

LOW = 0.044807
HIGH = 0.054764

Doing the arithmetic again the selectivity of INEX (.049785470) falls on the mid point, .0497855. But the span of the range from low to high is different: .009957.

If I run with several other values I end up with 6 child cursors and the low high look like this:



A couple of interesting points about this, the span between high and low generally fluctuates, child number 5 and 4 are nearly the same, but the others differ by quite a bit. Only cursor number 5 and 6 are sharable from V$SQL which means they are the only ones that are useable.

Cursor 5 is uses and INDEX RANGE SCAN and cursor 6 uses a INDEX FAST FULL SCAN. Cursor 5 has a Span from low to high of 0.000013 to 0.077194
The selectivity for the queries that use cursor 5 are:

0.000015 (RULE)
0.049785 (INDEX)
0.000073 (LOB)
0.039517 (TABLE)
0.070177 (VIEW)
0.000102 (CONTEXT)
0.000160 (JOB)
0.000145 (CLUSTER)
0.000015 (EDITION)
0.018602 (PACKAGE)

Cursor 6 has a span from low to high of 0.290623 to 0.429185. The selectivity for the queries that use cursor 6 are:

0.390168 (SYNONYM)
0.322915 (JAVA CLASS)

So there seems to be some correlation between the selectivity and the range. It appears that after a few runs the Cursor Sharing is able to figure out it doesn’t need more cursors. Somehow it also seems to come up with a decent ranger for each, how the range is calculated is still a bit of a mystery. Also there is a gap between the ranges used for 5 and 6. So far this is just with one BIND in the statement. Next to move on to multiple BINDs and see what happens there.

Note: If you are looking at doing some test like this on your own, the high and low values are stored as varchar2 data. And at least on my windows box were stored in a multi-byte char set. To do any arithmetic with them I had to do this to get the values as a number:

(TO_NUMBER(TO_SINGLE_BYTE(HIGH))