Geek Answers Handbook

Dynamic SQL and stored procedure performance in Oracle

There's a lot of information on how cached execution plans in SQL Server greatly reduce, if not most, the performance benefits of stored procedures over dynamic sql. However, I did not find much about whether the same is true for Oracle databases. Does anyone have info or pointers to information about dynamic sql processing in Oracle? Preferably, real performance metrics rather than general “sp” are good / “sp” are bad discussions.

+4
source share
3 answers

Oracle also has a caching tool. The request is hashed and matched against the plan if it falls into the hash table. You can also use this mechanism to force the planning of a specific request. As with SQL Server, you need to use a parameterized query instead of substituting values ​​in a string, since the latter will generate a different value for the hash function.

+7
source

Oracle never needed stored procedures for cached plans, so why not talk about it. The main performance benefit of stored procedures is cursor caching. It is assumed that if the session performed the PL / SQL fragment once, there is a good chance that the session will start it again. Therefore, when the saved PL / SQL code tells the session to close the cursor, it usually does not close it immediately. Instead, it will select it until “I need to open this cursor (again)” appears in the code or it needs memory.

The main advantage of this is OLTP processing, but the actual numbers will vary significantly between sites. But take a look at the article “Analysis - This Analysis - This Analysis”: http://www.oracle.com/technology/oramag/oracle/07-jul/o47asktom.html

+3
source

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

Remember that client side caching and client side caching are different animals.

0
source

More articles:


All Articles