表锁会加速 Oracle 10g 企业版中的更新语句吗？
我们有一个相当宽的表 BaseData，其中约有 3300 万行。然后我们有一个更新查询，将其连接到其他几个包含各种参数的表，应用一些函数，按原始 Id 进行分组，然后将结果写回到 BaseData 表的几列中。
这个过程非常慢，所以我正在寻找加快速度的方法。我在 SQLServer 方面拥有大部分经验，因此我还不知道 Oracle 的所有此类内部结构。
我怀疑的一件事是，在更新期间，Oracle 会创建每一行的版本，以便任何其他读者都可以读取未受影响的行。然而，这占用了大量资源。有没有什么方法可以让更新在表上获取写锁，这样就不会创建每一行的版本？
对于大型更新，你们还有其他建议吗？我们已经把它分成了几批。每个批次都位于表的单独分区中，然后并行运行多个更新。但仍然太慢了。
We have a fairly wide table BaseData with some 33 millions rows in it. Then we have an update query that joins it to several other tables containing all kinds of parameters, some functions are applied, there is a group by original Id and then the results are written back to the BaseData table in a few columns.
This process is very slow so I'm looking into ways of speeding it up. I have most of my experience in SQLServer so all this type of internals of Oracle I don't know yet.
One thing I suspect is that during the update Oracle creates versions of every row so any oher readers can read that unaffected row. This however takes up considerable resources. Is there any way to have the update take a write lock on the table so it wouldn't create versions of every row?
Any other tips you guys have for large updates? We already broke it down into batches. Each batch is in a seperate partition of the table and then several updates are run in parallel. But still its all much too slow.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
简短的回答是，不会，在 Oracle 中，对表采取排他锁不会阻止其他会话读取该表，也不会导致生成数据的读取一致视图的工作。同样，在 Oracle 中，您无法告诉会话启用“脏读”。
好吧，第一个问题是什么慢——是连接和应用函数的所有工作，还是写回？与您的更新语句相比，
SELECT my_updated_resultset FROM BASEDATA JOIN...的执行情况如何？您是否已验证 BaseData 的读者和更新过程之间存在争用？另外，对于业务来说它太慢了，或者只是比你想象的慢？另一个需要考虑的选项是使用分区交换来执行更新。高级概念是：
ALTER TABLE BASEDATA EXCHANGE PARTITION (ONLY_ONE_PARTITION) WITH BASEDATA_XCHG如果您要更新 BASEDATA 表分区中的大部分行，请不要更新它们 - 创建一个新表并将其交换出来。 Tim Gorman 有一篇出色的论文，名为 “Scaling to Infinity”，其中涵盖了这一概念更大的深度；你不妨检查一下。
The short answer is that no, in Oracle, taking an exclusive lock on a table won't prevent other sessions from reading it, or having to incur the work of generating a read-consistent view of the data. Similarly, in Oracle, you can't tell a session to enable "dirty reads."
Well, the first question is what's slow - is it all the work of joining and applying functions, or is it the writing back? How does a
SELECT my_updated_resultset FROM BASEDATA JOIN...perform compared to your update statement? Have you verified that there's contention between the readers of BaseData and the update process? Also, it's it too slow for the business, or just slower than you think it should be?Another option to consider is to use partition exchange to perform your updates. The high level concept would be:
CREATE TABLE BASEDATA_XCHG as SELECT * FROM BASEDATA WHERE 1 = 0;INSERT /*+ append */ INTO BASEDATA_XCHG SELECT my_updated_resultset FROM BASEDATA PARTITION (ONLY_ONE_PARTITION) JOIN...ALTER TABLE BASEDATA EXCHANGE PARTITION (ONLY_ONE_PARTITION) WITH BASEDATA_XCHGIf you're updating most of the rows in a partition of BASEDATA table, don't update them - create a new table and exchange it out. Tim Gorman has an excellent paper called "Scaling to Infinity" that covers this concept in greater depth; you may wish to check it out.
除了 Adam 的回答之外：
在更新语句上运行 EXPLAIN PLAN 并检查执行计划。
添加索引来支持连接和 WHERE 条件可能会加快查询速度。
In addition to Adam's answer:
Run an EXPLAIN PLAN on your update statement and check the execution plan.
Chances are that adding indexes to support your joins and WHERE conditions can speed up the query.
Oracle 使用撤消段来实现读取一致性（与 SCN 一起，了解更多信息 此处）
我假设这些大型批处理进程正在暂存区域上运行，而不是在许多不同进程正在使用的“prod”实例上运行。如果您要更新某个大表的 25% 或更多（粗略数字），则执行 CTAS（创建表作为选择...）可能比尝试更新更好。您的 CTAS 将包含新表的更新逻辑。完成后，在新表上添加索引/授权/等，并将新表重命名为旧表。您还可以在 CTAS 上添加并行提示和无日志记录，以进一步加快速度。
Oracle uses undo segments for read consistency (along with SCNs, read more here)
I'm assuming these large batch processes are running on a staging area and not a "prod" instance that is being used by a lot of various processes. If you are updating 25% or more (rough figures) of some big table, it may be better to do a CTAS (create table as select...) than attempting updates. Your CTAS would contain the update logic for the new table. Once done, add indexes/grants/etc on new table and rename new to old. You can also add a parallel hint and nologging on the CTAS to potentially speed things up even more.