Oracle 中的性能独立过程与打包过程
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1812049/
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 Standalone Procedure vs Packaged Procedure in Oracle
提问by P Sharma
What is the difference of performance between standalone procedure and packaged procedure? Which will be good performance wise and why? Is there any difference in execution of both?
独立程序和打包程序在性能上有什么区别？哪个将是良好的性能明智的，为什么？两者的执行有什么不同吗？
回答by Marius Burz
Tom says:
汤姆说：
Always use a package.
Never use a standalone procedure except for demos, tests and standalone utilities (that call nothing and are called by nothing)
总是使用一个包。
除了演示、测试和独立实用程序（它们什么都不调用，什么也不调用）之外，切勿使用独立过程
There you can also find a very good discussion about their performance. Just search for "performance" on that page.
If still seriously in doubt, you can always test yourself which one is faster. You'll certainly learn something new by doing so.
在那里您还可以找到关于他们表现的很好的讨论。只需在该页面上搜索“性能”即可。
如果仍然有严重的疑问，您可以随时测试自己哪个更快。这样做你肯定会学到新的东西。
My take on your question: while it's true that calling package procedures/functions seems to be slower in certain situations than calling standalone procedures/functions, the advantages offered by the additional features available when using packages far outweigh the performance loss. So, just like Tom puts it, use packages.
我对你的问题的看法：虽然在某些情况下调用包过程/函数似乎比调用独立过程/函数慢，但使用包时可用的附加功能提供的优势远远超过性能损失。所以，就像 Tom 所说的，使用包。
The link: http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:7452431376537
链接：http: //asktom.oracle.com/pls/asktom/f?p=100:11:0 ::::P11_QUESTION_ID: 7452431376537
Test code(20 million calls, runstats_pkg is a package I wrote based on the runstats package by Tom Kyte):
测试代码（2000 万次调用，runstats_pkg 是我基于Tom Kyte的runstats 包编写的包）：
CREATE OR REPLACE PACKAGE testperf AS
FUNCTION pow(i INT) RETURN INT;
END;
/
CREATE OR REPLACE PACKAGE BODY testperf AS
FUNCTION pow(i int) RETURN INT AS
BEGIN
RETURN i * i;
END;
END;
/
CREATE OR REPLACE FUNCTION powperf(i INT) RETURN INT AS
BEGIN
RETURN i * i;
END;
/
DECLARE
I INT;
S INT DEFAULT 0;
BEGIN
runstats_pkg.start1;
FOR I IN 1 .. 20000000 LOOP
s := s + (powperf(i) / i);
END LOOP;
runstats_pkg.stop1;
dbms_output.put_line(s);
s := 0;
runstats_pkg.start2;
FOR I IN 1 .. 20000000 LOOP
s := s + (testperf.pow(i) / i);
END LOOP;
runstats_pkg.stop2;
dbms_output.put_line(s);
runstats_pkg.show;
END;
Results(Oracle XE):
结果（Oracle XE）：
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
2,491 2,439 -52 102.13%
Run1 ran in 2304 hsecs
Run2 ran in 2364 hsecs
run 1 ran in 97.46% of the time
Results(Oracle 11g R1, different machine):
结果（Oracle 11g R1，不同机器）：
Run1 latches total versus runs -- difference and pct
Run1 Run2 Diff Pct
2,990 3,056 66 97.84%
Run1 ran in 2071 hsecs
Run2 ran in 2069 hsecs
run 1 ran in 100.1% of the time
So, there you go. Really not much of a difference. Want data for something more complex that also involves SQL DML? You gotta test it yourself.
所以，你去了。真的差别不大。想要数据用于也涉及 SQL DML 的更复杂的东西？你得自己测试一下。
回答by tuinstoel
There isn't a performance difference except that packages can have state and standalone procedures and functions not.
除了包可以具有状态而独立过程和函数没有之外，没有性能差异。
The use of package is more about ordening and grouping of code. You could see them as an alternative of namespaces.
包的使用更多是关于代码的排序和分组。您可以将它们视为命名空间的替代品。
回答by David
The primary reason to use packages is they break the dependency chain. For instance if you have two stand-alone procedures, procedure A which calls procedure B and you recompile procedure B you will also need to recompile procedure A. This gets quite complicated as you increase the number of procedures and functions.
使用包的主要原因是它们打破了依赖链。例如，如果您有两个独立的过程，过程 A 调用过程 B 并且您重新编译过程 B，您还需要重新编译过程 A。随着过程和函数的数量增加，这变得相当复杂。
If you move these to two to different packages you will not need to recompile them as long as the specification does not change.
如果将它们移到两个不同的包中，只要规范不更改，就不需要重新编译它们。
回答by cagcowboy
There should be no difference between the two.
两者应该没有区别。
A major use of packages is to group a set of similar/associeted functions+procedures
包的一个主要用途是将一组相似/关联的函数+过程分组
回答by Jeffrey Kemp
The other answers here are all good (e.g. packages have state, they separate interface from implementation, etc).
这里的其他答案都很好（例如，包具有状态，它们将接口与实现分开，等等）。
Another difference is when procedures or packages are wrapped - it is simple to unwrap a procedure, but not a package body.
另一个区别是在包装过程或包时 - 解包过程很简单，但不是包体。
