为什么从 Oracle 存储过程中检索 ResultSet 如此缓慢？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2922215/
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
Why is retrieving a ResultSet from Oracle stored procedure so slow?
提问by Pierre Henry
I have to improve some code where an Oracle stored procedure is called from a Java program. Currently the code is really really slow: up to about 8 seconds on my development machine. On the same machine, if I directly call an SQL query that does about the same treatment and returns the same data, it takes under 100 ms...
我必须改进一些从 Java 程序调用 Oracle 存储过程的代码。目前代码真的很慢：在我的开发机器上最多大约 8 秒。在同一台机器上，如果我直接调用执行相同处理并返回相同数据的 SQL 查询，则需要不到 100 毫秒...
The code creates a CallableStatement, registers one of the output parameters to be an Oracle cursor, and then retrieves the cursor using the getObject method of the statement and parse it to ResultSet:
代码创建一个CallableStatement，将输出参数之一注册为Oracle游标，然后使用语句的getObject方法检索游标并将其解析为ResultSet：
cstmt = conn.prepareCall("{ call PKG_ESPECEW.P_ListEspece( ?, ?, ?, ?, ?, ? ) }");
cstmt.registerOutParameter(4, oracle.jdbc.OracleTypes.CURSOR);
[...]
cstmt.executeQuery();
rs = (ResultSet)cstmt.getObject(4);
rs.setFetchSize(1000); //supposed to help ?
options = new HashMap<String, String>(1000);
rs.next() //added that to measure exactly the length of the first call
while(rs.next()) {
[...]
}
I put some timestamps in the code to know which part is taking so long. The result: The first call to rs.next()is taking up to various seconds. The result sets are average, from 10 to a couple thousands rows. As I said before, handling similar result sets coming from a regular PreparedStatement takes 10-100 ms depending the size.
我在代码中添加了一些时间戳以了解哪个部分花费了这么长时间。结果：第一次调用需要rs.next()花费不同的时间。结果集是平均的，从 10 到几千行。正如我之前所说，处理来自常规 PreparedStatement 的类似结果集需要 10-100 毫秒，具体取决于大小。
Is anything wrong with the code? How do I improve it? I'll do direct SQL where critical if I haven't any other solution, but I'd prefer a solution that allows me to not rewrite all the procedures!
代码有什么问题吗？我该如何改进？如果我没有任何其他解决方案，我会在关键的地方直接执行 SQL，但我更喜欢一个允许我不重写所有过程的解决方案！
Here is the definition of the stored procedure:
下面是存储过程的定义：
PROCEDURE P_ListEspece(P_CLT_ID IN ESPECE.ESP_CLT_ID%TYPE, -- Langue de l'utilisateur
P_ESP_GROUP_CODE IN ESPECE.ESP_CODE%TYPE,-- Code du groupe ou NULL
P_Filter IN VARCHAR2, -- Filtre de la requête
P_Cursor OUT L_CURSOR_TYPE, -- Curseur
P_RecordCount OUT NUMBER, -- Nombre d'enregistrement retourne
P_ReturnStatus OUT NUMBER); -- Code d'erreur
采纳答案by Gary Myers
"I thought the procedure was executed, then it's result stored in oracle server's memory, and finally transmitted back to the client (the java app) through the cursor and result set and JDBC"
“我以为程序被执行了，然后它的结果存储在oracle服务器的内存中，最后通过游标和结果集和JDBC传输回客户端（java应用程序）”
That's incorrect. What oracle returns as a cursor is basically a pointer to a query (all ready with any bind variables). It has not materialized the result set in memory. It could be a massive result set of millions/billions of rows.
那是不正确的。oracle作为游标返回的基本上是一个指向查询的指针（所有绑定变量都准备好了）。它没有在内存中实现结果集。它可能是一个包含数百万/数十亿行的庞大结果集。
So it could well be a slow query that takes a long time to deliver results.
所以它很可能是一个缓慢的查询，需要很长时间才能提供结果。
回答by BalusC
Apparently the stored procedure is doing some data conversion/massaging forth and back (e.g. int<--> varchar). This is known to take a lot of time in case of large tables. Ensure that you've declared the right datatypes in the SP arguments and are setting the right datatypes in CallableStatement.
显然，存储过程正在进行一些数据转换/来回传递（例如int<--> varchar）。众所周知，在大表的情况下，这需要花费大量时间。确保您已在 SP 参数中声明了正确的数据类型并在CallableStatement.
回答by Vincent Malgrat
How long does it take to execute the procedure outside of Java? Check with a script like this in SQL*Plus:
在 Java 之外执行该过程需要多长时间？在 SQL*Plus 中使用这样的脚本进行检查：
var ref refcursor
var cnt number
var status number
exec p_listespece (xx, yy, zz, :ref, :cnt, :status);--replace with actual values
print :ref
If it takes more than 10-100 ms, your problem may come from the stored procedure.
如果超过 10-100 毫秒，您的问题可能来自存储过程。
回答by edestrero
I had the same problem, we solved (me and the oracle dedicated guy) by changing the returned parameter from a cursor to a varchar, that was the plain query the stored was executing internally. this was an huge implementation, I don't know if this is applicable for your scenario.
我遇到了同样的问题，我们通过将返回的参数从游标更改为 varchar 来解决（我和 oracle 的专职人员），这是存储在内部执行的普通查询。这是一个巨大的实现，我不知道这是否适用于您的场景。
here's the snippet :
这是片段：
`
`
String sql = "call MyStored(?,?,?,?)";
CallableStatement st = Conn.prepareCall(sql);
st.setInt(1, 10);
st.setInt(2, 20);
st.setInt(3, 30);
st.registerOutParameter(4, OracleTypes.VARCHAR);
st.execute();
String query = (String) st.getObject(4);
Statement stmt = Conn.createStatement();
rs = stmt.executeQuery(query);
[...]
//work with resultset
[...]
stmt.close();
stmt = null;
`
`
