4. Adaptive Cursor Sharing
When a new query is issued, the database server makes a syntactic check to determine the legality of the SQL statement. If this new query is found to be semantically equivalent to an existing one already hashed and currently available in the library cache, it is executed using the execution plan of the earlier query.
Such a sharing mechanism is possible when using bind variables (or literals with CURSOR_SHARING set to FORCE). However, cursor sharing and SQL optimization might be diametrically opposed. Whereas bind variables avoid reoptimization by sharing the existing child cursor, they are not necessarily going to do the same amount of work and henceforth they might create a performance issue.
Oracle Database 11g
Get Oracle Database Problem Solving and Troubleshooting Handbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
