oracle 慢更新声明
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/296097/
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 Update Statement
提问by Thorsten
we're dealing with a very slow update statement in an Oracle project.
我们正在处理 Oracle 项目中非常缓慢的更新语句。
Here's a little script to replciate the issue:
这里有一个小脚本来复制这个问题：
drop table j_test;
CREATE TABLE J_TEST
(
ID NUMBER(10) PRIMARY KEY,
C1 VARCHAR2(50 BYTE),
C2 VARCHAR2(250 BYTE),
C3 NUMBER(5),
C4 NUMBER(10)
);
-- just insert a bunch of rows
insert into j_test (id)
select rownum
from <dummy_table>
where rownum < 100000;
-- this is the statement that runs forever (longer than my patience allows)
update j_test
set C3 = 1,
C1 = 'NEU';
There are some environments where the Update-Statement takes just about 20 seconds, some where the statement runs for a few minutes. When using more rows, the problem gets even worse.
在某些环境中，Update-Statement 只需大约 20 秒，有些则语句运行几分钟。当使用更多行时，问题会变得更糟。
We have no idea what is causing this behavior, and would like to have an understanding of what is going on before proposing a solution.
我们不知道是什么导致了这种行为，并希望在提出解决方案之前了解正在发生的事情。
Any ideas and suggestions? Thanks Thorsten
有什么想法和建议吗？谢谢托尔斯滕
回答by Tony Andrews
One possible cause of poor performance is row chaining. All your rows initially have columns C3 and C4 null, and then you update them all to have a value. The new data won't fit into the existing blocks, so Oracle has to chain the rows to new blocks.
性能不佳的一个可能原因是行链接。您的所有行最初都有 C3 和 C4 列为空，然后您将它们全部更新为具有值。新数据不适合现有块，因此 Oracle 必须将行链接到新块。
If you know in advance that you will be doing this you can pre-allocate sufficient free space like this:
如果你事先知道你会这样做，你可以像这样预先分配足够的可用空间：
CREATE TABLE J_TEST
(
ID NUMBER(10) PRIMARY KEY,
C1 VARCHAR2(50 BYTE),
C2 VARCHAR2(250 BYTE),
C3 NUMBER(5),
C4 NUMBER(10)
) PCTFREE 40;
... where PCTFREE specifies a percentage of space to keep free for updates. The default is 10, which isn't enough for this example, where the rows are more or less doubling in size (from an average length of 8 to 16 bytes according to my db).
... 其中 PCTFREE 指定保留用于更新的可用空间百分比。默认值为 10，这对于此示例来说还不够，其中行的大小或多或少加倍（根据我的数据库，平均长度为 8 到 16 个字节）。
This test shows the difference it makes:
此测试显示了它的不同之处：
SQL> CREATE TABLE J_TEST
2 (
3 ID NUMBER(10) PRIMARY KEY,
4 C1 VARCHAR2(50 BYTE),
5 C2 VARCHAR2(250 BYTE),
6 C3 NUMBER(5),
7 C4 NUMBER(10)
8 );
Table created.
SQL> insert into j_test (id)
2 select rownum
3 from transactions
4 where rownum < 100000;
99999 rows created.
SQL> update j_test
2 set C3 = 1,
3 C2 = 'NEU'
4 /
99999 rows updated.
Elapsed: 00:01:41.60
SQL> analyze table j_test compute statistics;
Table analyzed.
SQL> select blocks, chain_cnt from user_tables where table_name='J_TEST';
BLOCKS CHAIN_CNT
---------- ----------
694 82034
SQL> drop table j_test;
Table dropped.
SQL> CREATE TABLE J_TEST
2 (
3 ID NUMBER(10) PRIMARY KEY,
4 C1 VARCHAR2(50 BYTE),
5 C2 VARCHAR2(250 BYTE),
6 C3 NUMBER(5),
7 C4 NUMBER(10)
8 ) PCTFREE 40;
Table created.
SQL> insert into j_test (id)
2 select rownum
3 from transactions
4 where rownum < 100000;
99999 rows created.
SQL> update j_test
2 set C3 = 1,
3 C2 = 'NEU'
4 /
99999 rows updated.
Elapsed: 00:00:27.74
SQL> analyze table j_test compute statistics;
Table analyzed.
SQL> select blocks, chain_cnt from user_tables where table_name='J_TEST';
BLOCKS CHAIN_CNT
---------- ----------
232 0
As you can see, with PCTFREE 40 the update takes 27 seconds instead of 81 seconds, and the resulting table consumes 232 blocks with no chained rows instead of 694 blocks with 82034 chained rows!
如您所见，使用 PCTFREE 40 更新需要 27 秒而不是 81 秒，结果表消耗 232 个没有链接行的块，而不是 694 个具有 82034 个链接行的块！
回答by Gordon Bell
Try this:
尝试这个：
insert into j_test (id, C3, C4)
select rownum, 1, 'NEU'
from <dummy_table>
where rownum < 100000;
回答by user34850
Are you really trying to update a numeric field C4 NUMBER(10) with 'NEU' character value?
您是否真的想用“NEU”字符值更新数字字段 C4 NUMBER(10)？
Assuming you're trying to do the following:
假设您正在尝试执行以下操作：
UPDATE j_test
SET c3 = 3
WHERE c1 = 'NEU'
You may need to create an index on the search field and analyze the table to speed up the update process. If you really trying to update the entire table then update speed may vary. It depends on memory, disk access speed, redo-logs creation, etc.
您可能需要在搜索字段上创建索引并分析表以加快更新过程。如果您真的尝试更新整个表，则更新速度可能会有所不同。这取决于内存、磁盘访问速度、重做日志创建等。
Also, as it was mentioned in another answer, you need to reserve some space for updates using PCTFREE, otherwise your are going to get a lot of chained rows in the table which affect the update speed.
此外，正如在另一个答案中提到的，您需要为使用 PCTFREE 的更新保留一些空间，否则您将在表中获得大量影响更新速度的链接行。
回答by Kieveli
Are you sure the problem isn't coming from the fact that you're insertting 'NEU' into a Number(10) field? It's doing an on-the-fly conversion from 'NEU' to a Number (??), before insertting.
您确定问题不是来自您将“NEU”插入 Number(10) 字段的事实吗？在插入之前，它正在执行从“NEU”到数字 (??) 的即时转换。
I mean seriously, the other answers are nice and useful information, but 100k rows on a full update should be fast.
我的意思是说真的，其他答案是很好且有用的信息，但完整更新的 100k 行应该很快。
Remember - indexes tend to speed up selects, and slow down inserts / updates.
请记住 - 索引往往会加快选择速度，并减慢插入/更新速度。
回答by hamishmcn
回答by hamishmcn
This is very similar to the question and my answer here.
这与这里的问题和我的回答非常相似。
Never update 100% of the rows in a table. Just follow the procedure in that link. build the "right answer" as a new table and then swap that new table for the old one. Same with deleting a large percentage of the rows. It's just far more effective to use the scenario I've outlined.
永远不要更新表中 100% 的行。只需按照该链接中的程序进行操作即可。将“正确答案”构建为新表，然后将该新表替换为旧表。与删除大部分行相同。使用我概述的场景要有效得多。
EDIT: If this seems like a bad idea to some of you, just know that this is the technique recommended by Tom Kyte.
编辑：如果这对你们中的一些人来说似乎是个坏主意，请知道这是 Tom Kyte 推荐的技术。
