Recently, there was trouble in the air. I migrated a database into 11g to enjoy adaptive cursor sharing, but it did not adapt cursors. In the trouble shooting process, I discovered the following:
Bug 8357294: ADAPTIVE CURSOR SHARING DOESN’T WORK FOR SQL FROM PL/SQL
Nice one. A short quotation from the bug text:
PL/SQL has famously enjoyed the so-called “soft-parse avoidance” optimization
for embedded SQL ever since its inception. Then, in 10.1, the benefit was
brough to native dynamic SQL, but only for “execute immediate”. Notice that
queries executed using a ref cursor (whether this is opened statically or
dynamically) do not enjoy the “soft-parse avoidance” optimization.It seems that this was overlooked when adaptive cursor sharing was
introduced.
Summary: 11g PL/SQL does adaptive cursor sharing when we are using EXECUTE IMMEDIATE, but does not if you are using embedded SQL (for example in a(n implicit) cursor, like a for loop).
The bug says, that setting SESSION_CACHED_CURSORS=0 fixes this problem, but I will have to test it by myself to classify this information.
Regards
Usn