Skip to Main Content
Oracle PL/SQL for DBAs
book

Oracle PL/SQL for DBAs

by Arup Nanda, Steven Feuerstein
October 2005
Intermediate to advanced content levelIntermediate to advanced
454 pages
14h 44m
English
O'Reilly Media, Inc.
Content preview from Oracle PL/SQL for DBAs

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 ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Oracle PL/SQL Best Practices

Oracle PL/SQL Best Practices

Steven Feuerstein
Expert Oracle PL/SQL

Expert Oracle PL/SQL

Ron Hardman, Michael McLaughlin
Oracle PL/SQL For Dummies

Oracle PL/SQL For Dummies

Michael Rosenblum, Paul Dorsey

Publisher Resources

ISBN: 0596005873Supplemental ContentErrata Page