Oracle 上动态 SQL 与存储过程的性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/372401/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Performance of dynamic SQL vs stored procedures on Oracle
提问by Kevin Dente
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.
有很多关于 SQL Server 中的缓存执行计划如何消除存储过程相对于动态 sql 的大部分性能优势的信息。但是，我还没有发现很多关于 Oracle 数据库是否也是如此。有没有人有关于 Oracle 中动态 sql 处理的信息或指针？最好是真实的性能数字，而不是一般的“sp 好”/“sp 不好”的讨论。
回答by ConcernedOfTunbridgeWells
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.
Oracle 还具有缓存的执行工具。如果查询命中哈希表，则该查询被散列并匹配到一个计划。您还可以使用此机制为特定查询强制执行计划。与 SQL Server 一样，您需要使用参数化查询来执行此操作，而不是将值替换到字符串中 - 因为后者会生成不同的哈希值。
回答by Gary Myers
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.
Oracle 从来不需要缓存计划的存储过程，所以这就是为什么没有太多关于它的说法。存储过程的主要性能优势是游标缓存。假设是，如果一个会话执行了一次 PL/SQL，则该会话很有可能再次运行它。所以当存储的 PL/SQL 的代码告诉会话关闭一个游标时，它通常不会立即关闭它。相反，它会一直分配它，直到代码说“我需要（再次）打开这个游标”或者它需要内存。
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
这样做的主要好处是在 OLTP 处理中，但实际数字会因站点而异。但是在这里看看“A Parse Is a Parse Is a Parse”文章：http: //www.oracle.com/technology/oramag/oracle/07-jul/o47asktom.html
回答by tuinstoel
There is something called client side statement caching: http://www.oracle.com/technology/oramag/oracle/06-jul/o46odp.html
有一种叫做客户端语句缓存的东西：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.
请注意，客户端结果缓存和客户端语句缓存是不同的野兽。
