oracle JDBC 在 Java 中的慢查询，但在其他系统中没有 (TOAD)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1339549/
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
Slow query in Java by JDBC but not in other systems (TOAD)
提问by Telcontar
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.
您好，我有一个对 Oracle 系统的查询，该系统涉及一个视图，该视图通过将 TO_NUMBER() 应用到表主键来连接其他表。
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.
如果我使用 TOAD 进行查询，则查询速度非常快（800 regs 为 1 秒）。如果我使用字符串文字（不是参数化查询）在 JDBC 的 Java 程序中执行相同的查询，那么时间也很好。
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.
但是，如果我通过 PreparedStatement 使用参数化查询，则查询需要 1 分钟才能获取相同的寄存器。我知道使用常量值比使用参数产生不同的执行计划......但是如果我删除视图连接中的 TO_NUMBER 函数，参数化查询也很快。
- Is the parameters / TO_NUMBER() union preventing to use the PK index of the joined tables?
- is there a workaround to solve this (i need the parameters on the query and also the TO_NUMBER function)?
- 参数 / TO_NUMBER() 联合是否阻止使用连接表的 PK 索引？
- 是否有解决此问题的解决方法（我需要查询中的参数以及 TO_NUMBER 函数）？
P.D. sry for my bad english
PD sry 我的英语不好
回答by Vincent Malgrat
without additional information, we can only assume that an index isn't being used with the to_number() function is applied to the column. As shown in this SO question, a type conversion can prevent the optimizer from using an index.
没有额外的信息，我们只能假设没有使用索引，to_number() 函数应用于列。如这个 SO question所示，类型转换可以阻止优化器使用索引。
In general:
一般来说：
- when you add a function to a column (i-e:
to_number(id)) the optimizer won't be able to use the regular indexes on that column, - if it is possible, you should use the column raw. For exemple: instead of
WHERE trunc(col) = DATE '2009-08-27'you should use:WHERE col >= DATE '2009-08-27' AND col < DATE '2009-08-28' - if you really have to apply a function to a column, you can use a function-based index
- 当您向列添加函数时（即：）
to_number(id)优化器将无法使用该列上的常规索引， - 如果可能，您应该使用原始列。例如：而不是
WHERE trunc(col) = DATE '2009-08-27'你应该使用：WHERE col >= DATE '2009-08-27' AND col < DATE '2009-08-28' - 如果确实必须将函数应用于列，则可以使用基于函数的索引
回答by dpbradley
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 变量的数据类型是否与 Oracle 数据类型兼容。在通过与 Oracle DATE 列进行比较的绑定变量传递 Java TIMESTAMP 时，我看到了与您类似的症状 - 文字字符串查询正常，PL/SQL 中的测试用例（日期）绑定正常，Java 代码不匹配不正常。
[Edit] 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.
[编辑] 我认为自最初发布以来您已经提供了一些附加信息。了解来自不同环境（Java 与 Toad）的查询的稍微不同的形式（绑定与文字）发生了什么的最佳方法是在执行期间启用跟踪并比较来自生成的跟踪文件的执行路径。这将要求您有权访问数据库主机以检索文件。
- 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.
- 在 Toad 中，打开一个交互式 SQL 窗口（我不使用 Toad，但我相信您会理解我的意思）并发出 SQL 命令“alter session set sql_trace=true”
- 运行您的查询 - 向查询添加注释是个好主意，例如“/* Toad with literals */”
- 对于 Java 测试，构建一个测试用例，该用例发出“alter session...”语句，然后运行查询。再次向查询添加注释以将其标识为来自 Java 测试。
- 不要担心关闭跟踪——这会在会话断开时发生，在某些情况下，停止跟踪的断开连接方法是首选。
- 通过“select value from v$parameter where name like 'user_dump_dest'”找出数据库主机上的跟踪文件的位置
- 通过搜索查询注释字符串来查找 .trc 文件
- 从操作系统命令行使用 TKPROF 实用程序来处理跟踪文件 - “ tkprof filename.trc tkprof filename.out ”
- 检查/发布您看到的执行路径和时间。
回答by nevster
Check to make sure someone hasn't set the property oracle.jdbc.defaultNChar=true
检查以确保有人没有设置属性 oracle.jdbc.defaultNChar=true
This is sometimes done to resolve unicode problems but it means all columns are treated as nvarchars. If you have an index on a varchar column, it won't be used because oracle has to use a function to convert the character encoding.
有时这样做是为了解决 unicode 问题，但这意味着所有列都被视为 nvarchars。如果在 varchar 列上有索引，则不会使用它，因为 oracle 必须使用函数来转换字符编码。
