JDBC中的Java查询速度慢，但其他系统（TOAD）则不然 [英] Slow query in Java by JDBC but not in other systems (TOAD)

问题描述

你好我有一个Oracle系统查询，它涉及一个视图，它通过将TO_NUMBER（）与表主键相关联来连接其他表。

Hello i have a query to an Oracle System which involves a view which joins other tables by apliying an TO_NUMBER() to the tables primary key.

如果我这样做使用TOAD的查询查询速度非常快（800 regs为1秒）。如果我在JDBC程序中使用String文字（不是参数化查询）在java程序中执行相同的查询，那么时间也很好。

If i do the query using TOAD the query is very fast (1 sec for 800 regs). If i do the same query in a java program by JDBC with a String literal (not a parametrized query), the time is good too.

但是如果我使用参数化的通过PreparedStatement查询，查询需要1分钟来获取相同的寄存器。我知道使用常量值会产生与使用参数不同的执行计划...但如果我删除视图连接中的TO_NUMBER函数，参数化查询也会很快。

But if i use a parametriced query by an PreparedStatement the query takes 1 min to fetch the same registers. I know that using constant values produce distinct execution plan than using parameters... but if i remove the TO_NUMBER funcions in the joins of the view, the parametriced query is fast too.

• 参数/ TO_NUMBER（）union是否阻止使用连接表的PK索引？


• 有解决方法吗（我需要查询参数以及TO_NUMBER函数）？

PD sry for my bad english

P.D. sry for my bad english

推荐答案

检查参数中传递的Java变量的数据类型是否与Oracle数据类型兼容。通过与Oracle DATE列进行比较的绑定变量传递Java TIMESTAMP时，我看到了类似于你的症状 - 文字字符串查询OK，PL / SQL中的测试用例（日期）绑定OK，Java代码w /不匹配不正常。

Check that the data type of the Java variable passed in the parameter is compatible with the Oracle data type. I have seen symptoms similar to yours when passing Java TIMESTAMP's through a bind variable that was being compared to Oracle DATE columns - literal string query OK, test case in PL/SQL with (date) bind OK, Java code w/ mismatch not OK.

我认为自原始发布以来，您提供了一些其他信息。了解来自不同环境（Java与Toad）的查询略有不同的形式（绑定与文字）发生的事情的最佳方法是在执行期间启用跟踪并比较生成的跟踪文件中的执行路径。这将要求您有权访问数据库主机以检索文件。

I think you've provided some additional information since the original posting. The best way to understand what is happening with the slightly different forms (binds vs. literals) of the query from different environments (Java vs. Toad) is to enable tracing during the execution and compare the execution paths from the resulting trace files. This will require that you have access to the database host to retrieve the files.

• 在Toad中，打开一个交互式SQL
  窗口（我不使用Toad，但我确定
  你会明白我的意思）和
  发出SQL命令alter session
  set sql_trace = true


• 运行您的查询 - 在查询
  中添加评论，例如/ * Toad with literals * /

，这将是一个很好的
想法。
• 对于Java测试，构建一个测试用例
  ，它发出alter session ...
  语句，然后运行查询。
  再次，在查询中添加一条评论，以
  将其标识为来自Java
  测试。


• 不要担心转向跟踪
  off - 当
  会话断开连接时会发生这种情况，而在某些
  的情况下，首选
  停止跟踪的断开连接方法。


• 找出数据库主机
  上的跟踪文件的位置从v $ parameter中选择
  值，其中name
  类似'user_dump_dest'


• 查找.trc文件通过搜索
  查询注释字符串


• 使用OS
  命令行中的TKPROF实用程序来处理跟踪
  文件 - tkprof filename.trc tkprof
  filename.out


• 检查/发布执行路径，并查看
  次。

• In Toad, open an interactive SQL window (I don't use Toad but I'm sure you'll understand what I mean) and issue the SQL command "alter session set sql_trace=true"
• Run your query - it would be a good idea to add a comment to the query such as "/* Toad with literals */"
• For the Java test, build a test case that issues the "alter session..." statement and then runs the query. Again, add a comment to the query to identify it as coming from the Java test.
• Don't worry about turning the tracing off - this will happen when the sessions are disconnected and in some cases the disconnection method of stopping the trace is preferred.
• Find out where your trace files on the database host are by "select value from v$parameter where name like 'user_dump_dest' "
• Find the .trc files by searching for the query comment strings
• Use the TKPROF utility from the OS command line to process the trace file - " tkprof filename.trc tkprof filename.out "
• Examine/post the execution paths and times that you see.

这篇关于JDBC中的Java查询速度慢，但其他系统（TOAD）则不然的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！