动态SQL与存储过程在Oracle上的性能 [英] Performance of dynamic SQL vs stored procedures on Oracle

问题描述

关于SQL Server中的缓存执行计划如何消除(即使不是大多数)存储过程相对于动态sql的性能优势，有很多信息.但是，对于Oracle数据库是否同样适用，我还没有发现太多.是否有人拥有有关Oracle中动态SQL处理的信息或指向信息的指针?最好是真实的性能数字，而不是一般的"sp很好"/"sp不好"的讨论.

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.

推荐答案

Oracle还具有缓存的执行工具.如果查询击中哈希表，则对查询进行哈希处理并与计划匹配.您还可以使用此机制为特定查询强制执行计划.与SQL Server一样，您需要使用参数化查询来执行此操作，而不是将值替换为字符串-后者将生成不同的哈希值.

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.

这篇关于动态SQL与存储过程在Oracle上的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！