oracle JDBC 的性能问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4078712/
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
Performance issues with JDBC
提问by Sebastian Ganslandt
I'm currently facing an issue where a specific SQL-query is taking about 30 seconds to issue from within my Java application but <1 sec in a SQL-client (SQL Developer).
我目前面临一个问题，即从我的 Java 应用程序中发出特定 SQL 查询需要大约 30 秒，但在 SQL 客户端（SQL 开发人员）中发出 <1 秒。
In the the question,
Slow query in Java by JDBC but not in other systems (TOAD), it is suggested that using a PreparedStatement bound to java variables could make the query execute far slower than in the SQL-client (TOAD in that case) because Oracle is getting confused about which indexes to use. Could this be an issue with a PreparedStatement without parameters as well?
在问题中，
JDBC 在 Java 中的慢查询，但在其他系统 (TOAD) 中没有，建议使用绑定到 Java 变量的 PreparedStatement 会使查询执行速度远慢于 SQL 客户端（在这种情况下为 TOAD）因为 Oracle 对使用哪些索引感到困惑。这是否也是没有参数的 PreparedStatement 的问题？
What could otherwise be the issue?
否则会是什么问题？
The query looks something like
查询看起来像
select
sum(col1),
sum(col2),
max(select ...)
from view_
where time_id = get_time_id(to_date('2010-10-10','yyyy-mm-dd'))
where view_ is a complex view containing aggregations of tables and other complex views. The query is executed as a PreparedStatement but without any parameters. It doesn't seem to make a difference whether we use prepared statement or just plain statements.
其中 view_ 是包含表和其他复杂视图聚合的复杂视图。查询作为 PreparedStatement 执行，但没有任何参数。我们使用准备好的语句还是只使用简单的语句似乎没有区别。
Since the execution plan is quite huge I can't post all if it here, but the relevant difference seems to be:
由于执行计划非常庞大，我无法在这里发布所有内容，但相关差异似乎是：
UNION-ALL TABLE ACCESS FULL GVC_WH.PLAYER_FACT_DAILY TABLE 37 6717151 596,934.317 19940 240 7621178231 19502
UNION-ALL TABLE ACCESS BY INDEX ROWID GVC_WH.PLAYER_FACT_DAILY TABLE 38 2657 236.120 2429 30 20544658 2428 INDEX RANGE SCAN GVC_WH.PK_AGG_PLAYER INDEX (UNIQUE) 37 2657 16 1 638743 16
Where the first snippet is from when running it with the JDBC Thin Client and the second from when running it inside SQL Developer. It's not picking up the correct index when running as a statement (makes no difference whether I use a prepared statement or not) with the JDBC Thin Client. The time difference i 30 seconds for the first and 0.5 seconds for the second.
第一个片段在使用 JDBC 瘦客户端运行时来自哪里，第二个在 SQL Developer 中运行时来自哪里。使用 JDBC 瘦客户端作为语句运行时（无论我是否使用准备好的语句都没有区别），它没有选择正确的索引。第一次的时间差为 30 秒，第二次的时间差为 0.5 秒。
Could it be that using the function get_time_id prohibits the use of the index when used though JDBC, even though it is not function on the column and even though it seems to be working in SQL Developer?
使用函数 get_time_id 是否会在通过 JDBC 使用时禁止使用索引，即使它不是列上的函数，即使它似乎在 SQL Developer 中工作？
回答by LauraB
I would try running a traceon the database whilst using the application.
我会尝试在使用应用程序时在数据库上运行跟踪。
Then you should be able to see the query being run, and the actual execution plan. This will show you exactly what is going on, ie whether it is picking up the indexes or not.
然后您应该能够看到正在运行的查询以及实际的执行计划。这将准确地向您显示正在发生的事情，即它是否正在获取索引。
回答by erbsock
It is very possible you might be hitting problems with bind variable peaking due to the predicates being passed in. Try running the query with the following to confirm (i.e. consistent run times)
由于传入的谓词，您很有可能会遇到绑定变量峰值的问题。尝试使用以下命令运行查询以确认（即一致的运行时间）
alter session set “_optim_peek_user_binds”=false;
Are the stats up-to-date on all the objects?
所有对象的统计数据都是最新的吗？
As justin posted, as well, ensure you're measuring correctly as well. Without the full query, it will be difficult to provide additional insight.
正如贾斯汀发布的那样，请确保您的测量也正确。如果没有完整的查询，就很难提供额外的洞察力。
回答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 必须使用函数来转换字符编码。
