Matching Algorithms
The default behavior for cursor reuse in the database is referred to as exact matching . Outside of PL/SQL this exactness is quite unforgiving—the ASCII values must match precisely. There is only black and white, no gray area at all. A cursor either matches or it does not. Within PL/SQL, the compiler does what it can by reformatting cursors to promote reuse, but it can only do so much. This limitation is especially maddening when cursors differ only in the text contained within literals. Consider these two cursors:
SELECT order_date FROM orders WHERE order_number = '1';
SELECT order_date FROM orders WHERE order_number = '4';They both perform the same function of getting the order_date for a specific order, and their total ASCII values differ by a measly four digits. But under the exact matching algorithm, they both merit their own hard parse and space in the shared pool—even when embedded in PL/SQL.
SQL_TEXT PARSE_CALLS EXECUTIONS
------------------------------ ----------- ----------
SELECT order_date FROM orders 1 1
WHERE order_number = '4'
SELECT order_date FROM orders 1 1
WHERE order_number = '1'Eventually, the shared pool may end up flooded with cursors like this that are so close to being reused—and yet so far. Some commercial off-the-shelf applications exhibit this behavior of using literals in cursors, and some older versions of ODBC rely on it, as well. To allow for this behavior, Oracle introduced a second algorithm for cursor sharing known as similar ...