Oracle 插入执行时间过长
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6330551/
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
Oracle insert performs too long
提问by Viktor Stolbin
I'm confused about time Oracle 10g XE performs insert. I implemented bulk insert from xml file into several tables with programmatical transaction managment. Why one insert performs in a moment and another more than 10 minutes! I can't wait more and stop it. I think there's something more complex I have not payed attention yet.
我对 Oracle 10g XE 执行插入的时间感到困惑。我通过编程事务管理实现了从 xml 文件到多个表的批量插入。为什么一个插入在一分钟内执行，另一个插入超过 10 分钟！我迫不及待地想阻止它。我认为还有一些更复杂的东西我还没有注意到。
Update:
更新：
I found lock using Monitor.
我使用 Monitor 找到了锁。
Waits
Event enq: TX - row lock contention
name|mode 1415053316
usnusnusnusn<<16 | slot 327711
sequence 162
SQL
INSERT INTO ESKD$SERVICESET (ID, TOUR_ID, CURRENCY_ID) VALUES (9, 9, 1)
What does it mean and how should I resolve it?
这是什么意思，我应该如何解决？
采纳答案by Stephanie Page
TX- Enqueues are well known and a quick google will give you a clear answer.
TX- Enqueues 是众所周知的，一个快速的谷歌会给你一个明确的答案。
From that article:
从那篇文章：
1) Waits for TX in mode 6 occurs when a session is waiting for a row level lock that is already held by another session. This occurs when one user is updating or deleting a row, which another session wishes to update or delete. This type of TX enqueue wait corresponds to the wait event enq: TX - row lock contention.
1) Waits for TX in mode 6 当一个会话正在等待另一个会话已经持有的行级锁时发生。当一个用户正在更新或删除另一会话希望更新或删除的行时，就会发生这种情况。这种类型的 TX enqueue 等待对应于等待事件 enq：TX - 行锁争用。
If you have lots of simultaneous inserts and updates to a table you want each transaction to be a short as possible. Get in, get out... the longer things sit in between, the longer the delays for OTHER transactions.
如果您有大量同时插入和更新表，您希望每个事务尽可能短。进来，出去……事情之间的时间越长，其他交易的延迟时间就越长。
PURE GUESS:
纯粹的猜测：
I have a feeling that your mention of "programmatical transaction managment" is that you're trying to use a table like a QUEUE. Inserting a start record, updating it frequently to change the status and then deleting the 'finished' ones. That is always trouble.
我有一种感觉，您提到“程序化事务管理”是您正在尝试使用像 QUEUE 这样的表。插入开始记录，经常更新它以更改状态，然后删除“已完成”的记录。那总是麻烦。
回答by Kyle Hailey
see sites.google.com/site/embtdbo/wait-event-documentation/oracle-enqueues
见sites.google.com/site/embtdbo/wait-event-documentation/oracle-enqueues
The locking wait indicates a conflict that could easily be the cause of your performance issues. On the surface it looks likely that the problem is inserting a duplicate key value while the first insert of that key value had not yet committed. The lock you see "enq: TX - row lock contention" happens because one session is trying to modify uncommited data from another session. There are 4 common reasons for this particular lock wait event:
锁定等待表示可能很容易导致性能问题的冲突。从表面上看，问题很可能是插入了重复的键值，而该键值的第一次插入尚未提交。您看到“enq：TX - 行锁争用”的锁是因为一个会话试图修改另一个会话中未提交的数据。此特定锁定等待事件有 4 个常见原因：
- update/delete of the same row
- inserting the same uniq key
- modifying the same bitmap index chunk
- deleting/updating a parent value to a foreign key
- 更新/删除同一行
- 插入相同的 uniq 键
- 修改相同的位图索引块
- 删除/更新父值到外键
We can eliminate the first and last case are you are doing an insert. You should be able to identify the 2nd if you have no bitmap indexes involved. If you have bitmap indexes involved and you have uniq keys involved then you could investigate easily if you had Active Session History (ASH) data, but unfortunately Oracle XE doesn't. On the other hand you can collected it yourself with S-ASH, see : http://ashmasters.com/ash-simulation/ . With ASH or S-ASH you can run a query like
我们可以消除第一种和最后一种情况，即您正在执行插入操作。如果您没有涉及位图索引，您应该能够识别第二个。如果您有位图索引并且您有 uniq 键，那么您可以轻松调查是否有活动会话历史 (ASH) 数据，但不幸的是 Oracle XE 没有。另一方面，您可以使用 S-ASH 自己收集它，请参阅：http://ashmasters.com/ash-simulation/。使用 ASH 或 S-ASH，您可以运行如下查询
col event for a22
col block_type for a18
col objn for a18
col otype for a10
col fn for 99
col sid for 9999
col bsid for 9999
col lm for 99
col p3 for 99999
col blockn for 99999
select
to_char(sample_time,'HH:MI') st,
substr(event,0,20) event,
ash.session_id sid,
mod(ash.p1,16) lm,
ash.p2,
ash.p3,
nvl(o.object_name,ash.current_obj#) objn,
substr(o.object_type,0,10) otype,
CURRENT_FILE# fn,
CURRENT_BLOCK# blockn,
ash.SQL_ID,
BLOCKING_SESSION bsid
--,ash.xid
from v$active_session_history ash,
all_objects o
where event like 'enq: TX %'
and o.object_id (+)= ash.CURRENT_OBJ#
Order by sample_time
/
这将输出如下内容：
ST EVENT SID LM P2 P3 OBJ OTYPE FN BLOCKN SQL_ID BSID 10:41 enq: TX - row lock c 143 4 966081 4598 I1 INDEX 0 0 azav296xxqcjx 144 10:41 enq: TX - row lock c 143 4 966081 4598 I1 INDEX 0 0 azav296xxqcjx 144 10:41 enq: TX - row lock c 143 4 966081 4598 I1 INDEX 0 0 azav296xxqcjx 144 10:41 enq: TX - row lock c 143 4 966081 4598 I1 INDEX 0 0 azav296xxqcjx 144显示对象名称“OBJ”和对象类型“OTYPE”存在争用，并且类型是 INDEX。从那里您可以查找 INDEX 的类型以验证它是否是位图。如果问题是位图索引，那么您可能应该重新评估使用位图索引或重新访问数据加载和/或修改的方式以减少冲突。
If the problem isn't BITMAP indexes, then it's trying to insert a duplicate key. Some other process had inserted the same key value and not yet committed. Then your process tries to insert the same key value and has to wait for the first session to commit or rollback.
如果问题不是 BITMAP 索引，则它正在尝试插入重复键。其他一些进程插入了相同的键值但尚未提交。然后您的进程尝试插入相同的键值并且必须等待第一个会话提交或回滚。
For more information see this link: lock waits
有关更多信息，请参阅此链接：锁定等待
回答by Bora Yuret
It means, your sequence cache is to small. Increase it.
这意味着，您的序列缓存太小了。增加它。
回答by maple_shaft
This question will be really hard to answer with so little specific information. All that I can tell you is why this could be.
用这么少的具体信息，这个问题真的很难回答。我所能告诉你的就是为什么会这样。
If you are doing an INSERT ... SELECT ...bulk insert then perhaps your SELECTquery is performing poorly. There may be a large number of table joins, innefficient use of inline views and other resources that may be negatively impacting the performance of your INSERT.
如果您正在执行INSERT ... SELECT ...批量插入，那么您的SELECT查询可能表现不佳。可能存在大量表连接、内联视图的低效使用以及其他可能对INSERT.
Try executing your SELECTquery in an Explain Plan to see how the Optimizer is deriving the plan and to evaluation the COST of the query.
尝试SELECT在解释计划中执行您的查询以查看优化器如何派生计划并评估查询的成本。
The other thing that you mentioned was a possible lock. This could be the case however you will need to analyze this with the OEM tool to tell for sure.
您提到的另一件事是可能的锁定。可能是这种情况，但是您需要使用 OEM 工具对其进行分析才能确定。
Another thing to consider may be that you do not have indexes on your tables OR the statistics on these tables may be out of date. Out of date statistics can GREATLY impact the performance of queries on large tables.
要考虑的另一件事可能是您的表上没有索引，或者这些表上的统计信息可能已过时。过时的统计数据会极大地影响大型表上的查询性能。
