调用另一个 Oracle 包中的过程/函数的开销
我们正在讨论将通用函数/过程放在单独的包中或在每个包中使用本地副本对性能的影响。
我的想法是，将公共代码放在一个包中会更干净，但其他人担心性能开销。
想法/经历？
We're discussing the performance impact of putting a common function/procedure in a separate package or using a local copy in each package.
My thinking is that it would be cleaner to have the common code in a package, but others worry about the performance overhead.
Thoughts/experiences?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
将其放在一个位置并从多个位置调用它 - 这是基本的代码重用。从一个包调用另一个包的任何开销都是微不足道的。如果他们仍然怀疑，请让他们展示性能差异。
Put it in one place and call it from many - that's basic code re-use. Any overhead in calling one package from another will be minuscule. If they still doubt it, get them to demonstrate the performance difference.
担心者完全可以通过展示性能开销来证明他们的担忧的有效性。这应该是微不足道的。
同时他们应该考虑在多个地方重复代码的内存使用和维护开销。
通用代码放在一个包中。
The worriers are perfectly at liberty to prove the validity of their concerns by demonstrating a performance overhead. that ought to be trivial.
Meanwhile they should consider the memory usage and maintenance overhead in repeating code in multiple places.
Common code goes in one package.
除非您通过数据库链接调用位于不同数据库的包中的过程，否则调用另一个包中的过程的开销可以忽略不计。
存在一些性能问题以及内存问题，但它们很少见且相距甚远。此外，它们属于“甲骨文黑魔法”类别。例如，检查此链接。如果您能清楚地理解这是什么意思，那么您就可以认为自己是一位出色的 Oracle 专业人士。如果没有 - 别担心，因为这确实是硬核的东西。
然而，您应该考虑的是依赖性问题。
Oracle 包由 2 部分组成：spec 和 body：
Spec 是一个标头，其中声明 public 过程和函数（即在包外部可见）。
Body 是它们的实现。
尽管紧密相连，但它们是 2 个独立数据库对象。
Oracle 使用包状态来指示包是有效还是无效。如果某个包失效，则所有其他包都无效
依赖它的也变得无效。
例如，如果您的程序调用包 A 中的过程，而该过程又调用包 B 中的过程，则意味着
您的程序依赖于包A，而包A又依赖于包B。在Oracle中，这种关系是传递性的，这意味着
你的程序依赖于包 B。因此，如果包 B 损坏，你的程序也会停止（错误终止）。
这应该是显而易见的。但不太明显的是，Oracle 还通过包规范在编译时跟踪依赖关系。
假设您的包 A 和包 B 的规范和主体均已成功编译且有效。
然后你去修改包B的包体。因为你只改变了包体，但没有改变spec，
Oracle 假定包 B 的调用方式没有改变并且不执行任何操作。
但是，如果您在更改包 B 的主体的同时更改了包 B 的规范，则 Oracle 怀疑您可能更改了某些内容
过程的参数或类似的东西，并将整个链标记为无效（即包 B 和 A 以及您的程序）。
请注意，Oracle 不会检查规范是否确实发生更改，它只是检查时间。因此，只需重新遵守规范即可使所有内容无效。
如果发生失效，下次运行程序时将会失败。
但如果此后您再运行一次，Oracle 将自动重新编译所有内容并成功执行。
我知道这很令人困惑。那是甲骨文。不要试图过多地思考它。
您只需要记住几件事：
如果可能，请避免复杂的包间依赖关系。如果一件事依赖于另一件事，而另一件事又依赖于另一件事，依此类推，
那么通过重新编译一个数据库对象而使所有内容无效的可能性非常高。
最糟糕的情况之一是“循环”依赖关系，即包 A 调用包 B 中的过程，而包 B 调用包 A 中的过程。
在这种情况下，几乎不可能在不中断另一个的情况下编译一个。
将包规范和包主体保存在单独的源文件中。如果您只需要更改主体，请不要触及规范！
Unless you are calling a procedure in a package situated on a different data base over a DB link, the overhead of calling a procedure in another package is negligible.
There are some performance concerns, as well as memory concerns, but they are rare and far between. Besides, they fall into "Oracle black magic" category. For example, check this link. If you can clearly understand what that is about, consider yourself an accomplished Oracle professional. If not - don't worry, because it's really hardcore stuff.
What you should consider, however, is the question of dependencies.
Oracle package consists of 2 parts: spec and body:
Spec is a header, where public procedures and functions (that is, visible outside the package) are declared.
Body is their implementation.
Although closely connected, they are 2 separate database objects.
Oracle uses package status to indicate if the package is VALID or INVALID. If a package becomes invalid, then all the other packages
that depend on it become invalid too.
For example, If you programme calls a procedure in package A, which calls a procedure in package B, that means that
you programme depends on package A, and package A depends on package B. In Oracle this relation is transitive and that means that
your programme depends on package B. Hence, if package B is broken, your programme also brakes (terminates with error).
That should be obvious. But less obvious is that Oracle also tracks dependencies during the compile time via package specs.
Let's assume that the specs and bodies for both of your package A and package B are successfully compiled and valid.
Then you go and make a change to the package body of package B. Because you only changed the body, but not the spec,
Oracle assumes that the way package B is called have not changed and doesn't do anything.
But if along with the body you change the package B's spec, then Oracle suspects that you might have changed some
procedure's parameters or something like that, and marks the whole chain as invalid (that is, package B and A and your programme).
Please note that Oracle doesn't check if the spec is really changed, it just checks the timestemp. So, it's enough just to recomplie the spec to invalidate everything.
If invalidation happens, next time you run you programme it will fail.
But if you run it one more time after that, Oracle will recompile everything automatically and execute it successfully.
I know it's confusing. That's Oracle. Don't try to wrap your brains too much around it.
You only need to remember a couple of things:
Avoid complex inter-package dependencies if possible. If one thing depends on the other thing, which depends on one more thing and so on,
then the probability of invalidating everything by recompiling just one database object is extremely high.
One of the worst cases is "circular" dependencies, when package A calls a procedure in package B, and package B calls procedure in package A.
It that case it is almost impossible to compile one without braking another.
Keep package spec and package body in separate source files. And if you need to change the body only, don't touch the spec!