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.