Intelligent Cursor Sharing in 11.1.0.7

I've installed 11.1.0.7 and it does seem that Intelligent Cursor Sharing is smarter in this version. When I did the testing in 11.1.0.6, I had a SQL statement that generated 10 child cursors, with 11.1.0.7 the same test only generates 6 child cursors. So it's better.

The test case is this SQL:

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

The object type column has some good skew to it, some values only appear tens of times and others many thousands. The bind (:obj_typ) is then set to 12 different values.

With 11.1.0.6 I got the following when I looked at V$SQL:




With 11.1.0.7 I got the following when I looked at V$SQL:



I know the code here is hard to read, an important column is the first one which is the "IS_SHARABLE" column. This more of less says will this cursor be used in the future. With it set to N it wouldn't be. So in both these versions only the last two cursors are sharable (usable).

There are only two different plan for each version. An index range scan, and an index fast full scan.

For the 11.1.0.6 version the index range scan is use for child cursor 0, 2, 3, 4, 5, 6, 7, 8, and 9. Only child cursor 1 and 10 have the index fast full scan.

For the 11.1.0.7 version the index range scan is used for child cursors 0, 2, 3, 4, and 5. Again only the second and last (child number 1 and 6) have the index fast full scan.

This is definitely an improvement. I hope to see that we get to a point with a simple query like this that we only have 3 plans, cursor 0 and then the two plans that are used. Because of the nature of how Intelligent Cursor Sharing works, cursor 0 will always be an unused cursor once Intelligent Cursor Sharing kicks in.