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))

BINDs and Selectivity Calculations

One of the issues with doing an explain plan vs an execution plan is that if at explain time you use a bind the optimizer will "assume" a selectivity for the bind. If it's an equality operator it will use the density value from the stats, which is 1/NDV (NDV - Number of district values).

(Note: These test runs on a 11.1.0.7 database, but I have seen the same values on a 10.2.0.1 database as well.)

Given this SQL:
select /* RVD */ count(*) from big_tab where object_type = :obj_typ1
/

An explain plan shows:

INDEX RANGE SCAN on BIG_OBJTYPE_IDX with estimated rows of 59464

The table has 2,200,160 rows in it, the desity is 1/37 = .02702702702703

Doing the arithmetic: 2200160*.02702702702703 = 59463.78378379 so it sure looks like that is the calculation going on.

Interesting note is that when there is a histogram on the column, the stored density in the stats shows a .00000, not the .02070207 number. The optimizer must just do the calculation during the parse of the statement.

OK, how about when we use other operators? What selectivity does it use for them?

With <, <=, >,>=, LIKE, and BETWEEN I got this:

INDEX RANGE SCAN on BIG_OBJTYPE_IDX with estimated rows of 110K

Doing a bit of arithmetic, this is 5% (2200160*.05=110008).

This seems to prove that with a BIND it will go with 5% selectivity, except for the equality operator where it will use the 1/NDV calculation.

BBQ in Vancouver BC Canada

The other night I went to Dix BBQ and Brewery here in Vancouver. The food was good, but the beer is really their trade. I had ordered a Margareta, mostly because I really like tequila. Beer and I haven't been getting along as well these days... I'll head back there again and try some more of the beer, I did have a small sample the other night and it was very good.