Oracle 锁定表需要更多时间执行更新语句
我们有一个批处理过程，它读取基表并执行一些聚合，然后使用修改后的标志更新表。
我们有一个更新语句，它更新了大约 300 万行。作为业务需求的一部分，我们需要对正在更新的表进行表级锁定。
UPDATE TABLE1 t1 SET PARAMETER1=(SELECT p1 from TABLE2 t2 where t1.ROW_ID=ROWIDTOCHAR(t2.ROW_ID)
今天我们观察到，带有表级锁的更新语句需要 35 分钟，而没有表级锁则需要 20 分钟。
我无法确定这一观察结果。请帮忙！
干杯， 德瓦拉克
We have a batch process which reads the base tables and performs some aggregation and then update the tables with an modified flag.
We have an update statement which updates around 3million rows.As a part of the business requirement we need to have table-level lock on the table which we are updating.
UPDATE TABLE1 t1 SET PARAMETER1=(SELECT p1 from TABLE2 t2 where t1.ROW_ID=ROWIDTOCHAR(t2.ROW_ID)
The observation today we made is that, update statement with table level lock is taking 35 mins while without table level lock is taking 20 mins.
I am not able to ascertain this observation. Please help!
Cheers,
Dwarak
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
除了您的数据库之外，没有人可以告诉您观察的原因。你必须做一份 AWR 报告。
但是，不太可能
UPDATE会运行更长时间，因为表之前已被锁定。Nobody but your database could tell you the reason of your observation. You'll have to do an AWR report.
However, it's not quite possible that the
UPDATEwould run longer because the table had been locked before.您在测试中是否考虑了缓存（在数据库和文件系统中）？根据您执行的操作，由于数据已在内存中，一条语句可能会运行得更快。
Did you account for caching (both in the database and the filesystem) in your testing? Depending on what you did when, one statement might have run faster due to data already being in memory.