Performance of dynamic SQL vs stored procedures on Oracle

Question

There's lots of information about how cached execution plans in SQL Server eliminate much if not most of the performance advantage of stored procedures over dynamic sql. However, I have not found much about whether the same is true for Oracle databases. Does anyone have information or pointers to information about dynamic sql handling in Oracle? Preferably real performance numbers, not general "sp's are good"/ "sp's are bad" discussions.

Answer 1

Oracle also has a cached execution facility. The Query is hashed and matched to a plan if it hits on the hash table. You can also use this mechanism to force a plan for a particular query. As with SQL Server, you need to use a parameterised query to do this, rather than substituting the values into the string - as the latter will generate a different hash value.

Answer 2

Oracle never needed stored procedures for cached plans, so that's why there's not much said about it. The primary performance benefit for stored procedures is cursor caching. The assumption is that, if a session has performed a piece of PL/SQL once, there's a good chance that session will run it again. So when the code of stored PL/SQL tells the session to close a cursor, it generally won't close it immediately. Instead it will keep it allocated until either the code says "I need to open this cursor (again)" or it needs the memory.

The major benefit of this is in OLTP processing, but the actual numbers would vary significantly between sites. But have a look at the "A Parse Is a Parse Is a Parse" article here: http://www.oracle.com/technology/oramag/oracle/07-jul/o47asktom.html

Answer 3

There is something called client side statement caching: http://www.oracle.com/technology/oramag/oracle/06-jul/o46odp.html

Be aware, client side result caching and client side statement caching are different beasts.