Some Oracle databases with high ad-hoc query
activity (Crystal Reports, Business Objects) cannot avoid in-line
literals inside the SQL, and that's why Oracle introduced the
cursor_sharing parameter. This use of cursor_sharing=force
has been shown to provide a huge benefit for database plagued with literals (i.e.
non-reentrant SQL) in their library cache.
The dynamic shop
often has SQL that is generated by ad-hoc query tools with
hard-coded literal values embedded within the SQL. As we know,
hard-coded literal values make the SQL statements non-reusable
unless cursor_sharing=force is set in the Oracle
initialization file.
Shops that are
plagued with non-reusable SQL can adopt either the persistent or the
dynamic execution plan philosophy. To use optimizer plan stability
with non-reusable SQL, the DBA will set cursor_sharing=force
and then extract the transformed SQL from the library cache and use
optimizer plan stability to make the execution plan persistent.
My tips for using cursor_sharing include:
