如何通过 Java 使我的 Oracle 更新/插入操作更快？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4936882/
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
How to make my Oracle update/insert action through Java faster?
提问by lamwaiman1988
I am facing a problem in my company that is - our program's speed is not fast enough. To be more specific, we are telecommunication company and this program handle call/internet serfing transaction made by every mobile phone users in our city. Because the amount of download content made by the iphone users is just too much, our program cannot handle them fast enough.
我在我的公司面临一个问题 - 我们的程序速度不够快。更具体地说，我们是电信公司，该程序处理我市每个手机用户的电话/互联网服务交易。由于 iphone 用户下载的内容太多，我们的程序处理速度不够快。
The situation is, the amount of transaction made by users are double of the transaction processed by our program. Most of the running time of the program are dominated by DB transactions.
情况是，用户进行的交易量是我们程序处理的交易量的两倍。程序的大部分运行时间都以DB事务为主。
I've search through the internet and browsed some sites ( for example: http://www.javaperformancetuning.com/tips/rawtips.shtml) talking about Java performace in DB, but I cannot find a suggestion suitable for us.
我已经通过互联网搜索并浏览了一些网站（例如：http: //www.javaperformancetuning.com/tips/rawtips.shtml）谈论 DB 中的 Java 性能，但我找不到适合我们的建议。
These advices are not applicable/already used, for instance:
这些建议不适用/已经使用，例如：
1. Use prepared statements. Use parameterized SQL
1. 使用准备好的语句。使用参数化 SQL
Already used prepared statement. Each time will use different parameter by clear parameters and set parameters.
已经使用了准备好的语句。通过清除参数和设置参数，每次将使用不同的参数。
2. Tune the SQL to minimize the data returned (e.g. not 'SELECT *').
2. 调整SQL 以最小化返回的数据（例如不是'SELECT *'）。
Sure, already used.
当然，已经使用了。
3. Use connection pooling.
3. 使用连接池。
We hold a single connection during the program's execution. And I doubt that pooling cannot solve the problem because our program act as 1 user, so there are no problem for concurrent access to DB. If anyone of you think pooling is good, please tell me why. Thanks.
我们在程序执行期间保持一个连接。而且我怀疑池化不能解决问题，因为我们的程序充当1个用户，所以对DB的并发访问没有问题。如果你们中有人认为池化很好，请告诉我为什么。谢谢。
4. Try to combine queries and batch updates.
4. 尝试结合查询和批量更新。
Cannot do it. Every query/insert/update is depend on the database's information. For example, we look up the DB for the client's information, if we cannot find his usage, we insert the usage into DB, otherwise we do update.
做不到。每个查询/插入/更新都取决于数据库的信息。例如，我们在数据库中查找客户的信息，如果找不到他的用法，则将用法插入数据库，否则我们进行更新。
5. Close resources (Connections, Statements, ResultSets) when finished
5. 完成后关闭资源（Connections、Statements、ResultSets）
Sure.
当然。
6. Select the fastest JDBC driver.
6. 选择最快的JDBC 驱动程序。
I don't know. I've search on the internet about the type of driver available and I am very confused. We use oracle.jdbc.driver.OracleDriverand we use thin instead of oci, that's all I know. In addition, our program is a two-tier way ( java <-> oracle )
我不知道。我在互联网上搜索了可用的驱动程序类型，但我很困惑。我们使用oracle.jdbc.driver.OracleDriver并且我们使用薄而不是oci，这就是我所知道的。另外，我们的程序是两层方式（ java <-> oracle ）
7. Turn off auto-commit
7.关闭自动提交
already done that.
已经这样做了。
Looking forwards to any help.
期待任何帮助。
采纳答案by Gary Rowe
Get hold of a copy of Professional Oracle Programming.
获取专业 Oracle 编程的副本。
It seems a bit on the old side at 2005, but Oracle doesn't change drastically when it comes to optimising performance. I've got this book myself and have used it's advice to speed up seemingly intractable performance issues for many applications. Get it. Read it. Do it.
在 2005 年，这似乎有点过时了，但是在优化性能方面，Oracle 并没有发生太大的变化。我自己也拿到了这本书，并使用它的建议来加速许多应用程序看似棘手的性能问题。得到它。阅读。做吧。
So what can you do while you wait for express delivery?
那么在等待快递的同时还能做什么呢？
- Get the DBA on your side, - you'll need their help and their tools
- Get hold of TOAD and pay for the extra query analysis tools if necessary
- Check your indexes for every query that you run - you need to examine the execution plans carefully (AUTOTRACE and EXPLAIN PLAN are your friends here)
- Consider the type of index you're using (could a functional index do the trick?)
- Consider using transportable tablespaces
- Use the built-in Optimizer to gather information
- Obtain statistics so you can measure performance gains (irrespective of the pre-caching and suchlike)
- Consider stored outlines
- Consider materialized views to allow splitting your data to that which is needed immediately and that which can suffer a delay
- Consider table truncation to reduce the size of the overall tables as older data is farmed off
- 让 DBA 站在你这边——你需要他们的帮助和他们的工具
- 掌握 TOAD 并在必要时支付额外的查询分析工具
- 检查您运行的每个查询的索引 - 您需要仔细检查执行计划（AUTOTRACE 和 EXPLAIN PLAN 是您的朋友）
- 考虑您使用的索引类型（功能索引可以解决问题吗？）
- 考虑使用可传输的表空间
- 使用内置优化器收集信息
- 获取统计信息，以便您可以衡量性能提升（不考虑预缓存等）
- 考虑存储的轮廓
- 考虑物化视图以允许将数据拆分为立即需要的数据和可能会延迟的数据
- 考虑截断表以减少整个表的大小，因为旧数据被淘汰
That should be enough to give you a firm grasp on what is failing and how to fix it.
这应该足以让您牢牢掌握失败的原因以及如何修复它。
回答by Tony Andrews
4. Try to combine queries and batch updates.
Cannot do it. Every query/insert/update is depend on the database's information. For example, we look up the DB for the client's information, if we cannot find his usage, we insert the usage into DB, otherwise we do update.
4. 尝试结合查询和批量更新。
做不到。每个查询/插入/更新都取决于数据库的信息。例如，我们在数据库中查找客户的信息，如果找不到他的用法，则将用法插入数据库，否则我们进行更新。
If you are doing that from the Java application you can improve performance by doing it in the database in one round-trip instead. There are a couple of ways:
如果您是从 Java 应用程序执行此操作，则可以通过在数据库中执行一次往返来提高性能。有几种方法：
1) Use a SQL MERGEstatement
1) 使用 SQL MERGE语句
2) Write a stored procedure to do the insert or update logic and just call that from Java.
2) 编写一个存储过程来执行插入或更新逻辑，然后从 Java 中调用它。
Further explanation
进一步说明
I assume from what you said that at the moment you have Java logic that works like this:
我假设根据您所说的，目前您的 Java 逻辑是这样工作的：
// Pseudocode
execute SQL 'select count(*) from mytable where id=?'
if result = 0 then
execute SQL 'insert into mytable (id,a,b,c) values (?,?,?,?)';
else
execute SQL 'update mytable set a=?, b=?, c=? where id=?';
end if;
That means 2 separate round-trips to the database: one to check whether the record exists, and another to either insert or update as appropriate.
这意味着对数据库进行两次单独的往返：一次检查记录是否存在，另一次根据需要插入或更新。
Alternatives are:
替代方案是：
1) Use a SQL MERGE statement:
1) 使用 SQL MERGE 语句：
// Pseudocode
execute SQL 'merge into mytable t using (select ? id, ? a, ? b, ? c from dual) s
on (t.id = s.id)
when matched then update set t.a = s.a, t.b = s.b, t.c = s.c
when not matched then insert (id, a, b, c)
values (s.id, s.a, s.b, s.c)';
The MERGE statement is a bit daunting at first, especially when like this you have to use Oracle's "dual" table.
MERGE 语句起初有点令人生畏，尤其是当您必须使用 Oracle 的“双”表时。
2) Use a stored procedure:
2）使用存储过程：
// Pseudocode
execute SQL 'begin mytable_package.insert_or_update
(p_id => ?, p_a => ?, p_b => ?, p_c => ?); end;'
The stored procedure, in a package called mytable_package, would look something like
存储过程位于名为 mytable_package 的包中，类似于
procedure insert_or_update (p_id mytable.id%type
,p_a mytable.a%type
,p_b mytable.a%type
,p_c mytable.a%type
)
is
begin
update mytable
set a = p_a, b = p_b, c = p_c
where id = p_id;
if sql%rowcount = 0 then
insert into mytable (id, a, b, c) values (p_id, p_a, p_b, p_c);
end if;
end;
回答by Erich Kitzmueller
Check your indexes!!!Bad update performance can be the result of a foreign key constraint where the index on the foreign key is missing on the referencing table.
检查你的索引！！！糟糕的更新性能可能是外键约束的结果，其中外键上的索引在引用表上丢失。
4)Try to combine queries and batch updates.
Cannot do it. Every query/insert/update is depend on the database's information. For example, we look up the DB for the client's information, if we cannot find his usage, we insert the usage into DB, otherwise we do update.
4）尝试结合查询和批量更新。
做不到。每个查询/插入/更新都取决于数据库的信息。例如，我们在数据库中查找客户的信息，如果找不到他的用法，则将用法插入数据库，否则我们进行更新。
Two things come to my mind:
我想到了两件事：
Do the UPDATE statement and check the result of ExecuteUpdate(); only if it is zero, do the INSERT. Saves you one SELECT statement.
Always (possibly batch-)insert into an intermediary table, later use the MERGEstatement to update your usage table.
执行 UPDATE 语句并检查 ExecuteUpdate() 的结果；只有当它为零时，才执行 INSERT。为您节省一个 SELECT 语句。
始终（可能批量）插入中间表，稍后使用MERGE语句更新您的使用表。
5)Close resources (Connections, Statements, ResultSets) when finished
5）完成后关闭资源（连接、语句、结果集）
Keep the connection open as long as possible (i.e. permanently till shutdown of the server), prepare a PreparedStatement once and use it repeatedly.
尽可能长时间保持连接打开（即永久直到服务器关闭），准备一次 PreparedStatement 并重复使用它。
Do a bit of aggregation before you write to the database. A cell phone user who generates a transaction now probably will generate another one within a few seconds. Use a hashtable to aggregate current usage and write it to the database after a minute or so.
在写入数据库之前进行一些聚合。现在生成交易的手机用户可能会在几秒钟内生成另一笔交易。使用哈希表聚合当前使用情况，并在一分钟左右后将其写入数据库。
回答by Thorbj?rn Ravn Andersen
First of all you need the DBA's on your sideto tell you where the time is actually spent. You can be fast as lightning in your client and still have long transaction times until a crucial index is set up.
首先，您需要 DBA 在您身边告诉您时间实际花在哪里。您可以像闪电一样快速地访问客户端，并且在设置关键索引之前仍有很长的交易时间。
It's been eight years since I worked with Java+Oracle but I did then not find the oci driver (using a native driver in a DLL) to be significantly faster than then thin driver (all written in Java).
我使用 Java+Oracle 已经八年了，但我没有发现 oci 驱动程序（在 DLL 中使用本机驱动程序）比瘦驱动程序（全部用 Java 编写）快得多。
A quick work around to give you breathing space could be to generate a text file with 1000 or 10000 transactions at a time and let "SQLLDR" inject the batch into the database. Perhaps even more. Properly invoked SQLLDR is the fastest thing there is, and it will buy you time to do it properly.
为您提供喘息空间的快速解决方法可能是一次生成一个包含 1000 或 10000 个事务的文本文件，然后让“SQLLDR”将批处理注入数据库。也许更多。正确调用 SQLLDR 是最快的事情，它会为您争取时间正确执行。
回答by ik_zelf
Can you divide your workload to use multiple sessions to the database? There are lots of round trips to the database causing latency. If your tables are structured correctly they can handle multiple concurrent inserts/updates without problems (check INITRANS property of the heavy inserted tables to be the same as the number of concurrent sessions doing the insert). I think this would be the easiest way to win performance for your app. What version of the database is in use? Can you get ADDM reports? They can tell you in no time what - if any - the problem in the database is. Has the app server enough cpu resources ? If not, it's an extra reason to split the load over multiple sessions, in that case divided over multiple app servers. Without a statspack report or - preferably - a ADDM report it is hard to tell where the problem is.
您可以划分工作负载以对数据库使用多个会话吗？有很多往返数据库导致延迟。如果您的表结构正确，它们可以毫无问题地处理多个并发插入/更新（检查重插入表的 INITRANS 属性是否与执行插入的并发会话数相同）。我认为这将是为您的应用赢得性能的最简单方法。正在使用什么版本的数据库？你能得到 ADDM 报告吗？他们可以立即告诉您数据库中的问题是什么（如果有的话）。应用服务器是否有足够的 CPU 资源？如果不是，则将负载分配到多个会话是一个额外的原因，在这种情况下，将负载分配到多个应用程序服务器。如果没有 statspack 报告或 - 最好是 - ADDM 报告，很难判断问题出在哪里。
I hope this helps, Ronald.
我希望这会有所帮助，罗纳德。
回答by David Hosier
If you want to keep the DBA's involvement at a minimum, you could at least ask for a login to access the Oracle Enterprise Manager running on a test server. In OEM, you can actually see what operations are taking a long time. OEM will also try to help you by suggesting ways to improve performance, like adding indexes or changing the structures of your queries. Hopefully OEM could at least give you solid reasons for asking for further involvement by the DBAs.
如果您希望 DBA 的参与最少，您至少可以要求登录以访问在测试服务器上运行的 Oracle Enterprise Manager。在 OEM 中，您实际上可以看到哪些操作需要很长时间。OEM 还将尝试通过建议提高性能的方法来帮助您，例如添加索引或更改查询的结构。希望 OEM 至少可以给您充分的理由要求 DBA 进一步参与。
回答by David Hosier
Are you launching new instances of the JVM for each execution? Can you elaborate on the nature of your application (i.e. how it is invoked, what triggers invocation, etc.). From your description, it doesn't really sound like a Java and DB issue. It sounds like maybe some kind of database index problem or other design problem. What is the nature of the SQL commands you are using? Have you timed or profiled these calls to see if some are taking longer than others?
您是否为每次执行启动新的 JVM 实例？您能否详细说明您的应用程序的性质（即它是如何调用的，什么触发了调用等）。根据您的描述，这听起来不像是 Java 和 DB 问题。听起来可能是某种数据库索引问题或其他设计问题。您使用的 SQL 命令的性质是什么？您是否对这些调用进行了计时或分析，以查看某些调用是否比其他调用花费的时间更长？
