当应用程序多次调用数据库过程时，性能是否会受到影响？ [英] Whether performance will impact when database procedure is called from application many times?
问题描述
在我的项目中，我们使用
从oracle程序提供的Pro * C / C ++库的帮助下从我们的c ++应用程序调用oracle过程。
In my project, we are calling the oracle procedure from our c++ application with the help of Pro *C/C++ library provided by the oracle.
我们有一个大程序，我的想法是将程序拆分为两个模块化。但他们的建议是一次调用程序，并一次执行所有工作。
We have one big procedure, and my idea is to split the procedure in to two for modularity. But their advice is to call the procedure for one time, and perform all jobs at a shot.
我从他们那里得到的原因是它会导致性能影响，因为应用程序多次与数据库交互。
The reason which i got from them is it will cause performance impacts, since application program is interacting with database for multiple times.
我同意，上面的情况会发生在应用程序连接数据库时，调用过程，最后断开每个过程的数据库呼叫。但是，我们真正做的是在启动时创建一个连接池，并重新使用预连接的数据库连接与数据库进行交互。
I agree, the above scenario will happen when application connects the database, calls the procedure and finally disconnects the database for each procedure call. But, what we really do is we create a pool of connections at startup, and reuse that pre-connected database connections for interacting with the database.
有关信息我的应用程序：
- 它是
多线程应用程序每秒处理大约1000个请求，线程池大小为20.目前对于每个请求，我们与数据库通信4次。
- It is
multi-threaded application, which handles about 1000 request per second with thread pool size as 20. Currently for each request we communicate with database for 4 times.
EDIT：
之间的PLSQL和SQL比其他方式快得多。
Q1。这是什么与我的实际问题有关？我的问题是将程序分为两个相等的部分。假设我有4个查询在过程中执行，我只是把它拆分成两个过程a和过程b，每个过程将有两个查询。
"the switch between PLSQL and SQL is much faster than the other way around". Q1. How this is is related to my actual question? My question is about spiliting a procedure in to two equal parts. Say suppose i have 4 queries executed in procedure, i just split it in to two as procedure a and procedure b, and each procedure will have two queries.
pro * c调用PLSQL是性能命中。
Q2。你是指应用程序（pro * C / C ++）和数据库（oracle）之间的通信吗？
"pro*c calls which call PLSQL is an performance hit". Q2. Do you mean an communication between application(pro *C/C++) and database(oracle) here? If so, is communication was a big performance hit?
在ask tom链接中附加了但是不要害怕从PLSQL调用SQL - 这是PLSQL最好的
Q4。当我们从PLSQL调用SQL时，会发生轮询上下文切换？因为，根据上面的语句，它似乎没有性能的影响。
In the ask tom link you have attached, "But don't be afraid at all to invoke SQL from PLSQL - that is what PLSQL does best" Q4. Wheather context switch will happen while we call SQL from PLSQL? Because, as per above statement, it seems to be no performanace impact.
推荐答案
您的建议是正确的，立即执行所有数据库任务。您的方案中有2个主要的性能影响
Your advice is correct, it would be better to perform all database tasks at once. There are 2 major performance impacts in your scenario
- 在SQL引擎和PL / SQL引擎之间运行pro * c的上下文切换你的线程多次。通常是来自客户端应用程序的许多PL / SQL调用中最大的问题。
- 您的pro * c应用程序和数据库引擎之间的通信中的网络堆栈开销应用程序在不同的物理主机上。
尽管如此，您正在应用程序端创建一个连接池，TNS监听器有一个等待每个网络连接的bequeathed服务器影子进程池（这是在listener.ora设置）。
Having said that, you are creating a connection pool at the application end the TNS listener should also have a pool of bequeathed server shadow processes waiting for each network connection (this is setup at the listener.ora).
当影子进程已经在等待连接时，OCI登录/注销是非常快的，而不是延迟的一个巨大因素 - 我不担心这个，除非一个新的阴影进程在服务器上必须启动 - 那么它可能是一个非常昂贵的调用。因为你在客户端使用连接池，这通常不是一个问题，但只是一些要考虑，因为在呼叫线程。一旦你耗尽了服务器影子进程池，你会发现一个巨大的退化，如果TNS侦听器必须启动更多的服务器影子进程。
The OCI login/logoff when the shadow process is already waiting for connect is very quick and not a huge factor in latency - I don't worry about this unless a new shadow process on the server has to start up - then it can be a very expensive call. As you are using connection pooling on the client side, this is usually not an issue but just something to consider because of the threading in your calls. Once you exhaust the pool of server shadow processes, you will notice a huge degradation if the TNS listener has to start up more server shadow process.
到新问题：
-
这是非常相关的。正如前面指出的，你应该尽量减少plsql和sql在你的C ++应用程序中调用的数量。 C ++应用程序调用中的每个PLSQL调用都调用SQL引擎，然后调用PLSQL引擎进行过程调用。因此，如果你将过程拆分为2 - 你将SQL加倍到PLSQL上下文切换，这是更昂贵的切换，如汤姆Kyte文章和我自己的个人经验。
It is very related. As pointed out previously, you should minimise the amount of plsql and sql calls within your C++ app. Each PLSQL call within your C++ app call invokes the SQL engine which then invokes the PLSQL engine for the procedure call. So if you split your procedure into 2 - you are doubling the SQL to PLSQL context switches which is the more expensive switch as outlined by the Tom Kyte article and my own personal experience.
在1中回答。但是我之前说过，除非您的主机位于不同的物理网络和您要传输的数据类型，否则通信开销是第二。例如，大型C ++对象参数和具有许多调用的大型Oracle结果集显然会影响与往返行程的通信延迟。记住，对于更多的PLSQL调用，您还要为每个连接和结果集的设置添加更多的SQLNET流量。
Is answered in 1. But as I previously said the communications overhead is second unless your hosts are on different physical networks and the types of data you are transferring. For instance large C++ object parameters and large Oracle result sets with many calls will obviously affect communications latency with round trips. Remember that with more PLSQL calls you are also adding more SQLNET traffic for the setup for each connection and result set.
没有问题。
PLSQL引擎中的SQL可以忽略，所以不要挂在它上面。将所有SQL调用放在1个PLSQL调用中，以获得最高的性能吞吐量。
PLSQL to SQL within the PLSQL engine is negligible so don't get hung up on it. Put all your SQL calls within 1 PLSQL call for maximum performance throughput. Don't split the calls just to be more eloquent at the expensive of performance.
这篇关于当应用程序多次调用数据库过程时，性能是否会受到影响？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
