关于 Oracle 并行插入性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10421806/
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
about Oracle parallel insert performance
提问by Gary
I have an sql like this:
我有一个这样的sql：
Insert into A
Select * from B;
Now I want it to run in parallel. My question is to parallelize the insert or select or both? See the following sqls, can you tell me which one is correct or which one has best performance. I don't have dba permission, so I cann't check its execute plan.
现在我希望它并行运行。我的问题是并行插入还是选择或两者兼而有之？看下面的sql，你能告诉我哪个是正确的，或者哪个性能最好。我没有 dba 权限，所以我无法检查它的执行计划。
1) Insert /*+ parallel(A 6) */ into A select * from B;
1) Insert /*+ parallel(A 6) */ into A select * from B;
2) Insert into A select/*+ parallel(B 6) */ * from B;
2) Insert into A select/*+ parallel(B 6) */ * from B;
3) Insert /*+ parallel(A 6) */ into A select /*+ parallel(B 6) */ * from B;
3) Insert /*+ parallel(A 6) */ into A select /*+ parallel(B 6) */ * from B;
Thank you!
谢谢！
回答by Jon Heller
Parallelizing both the INSERTand the SELECTis the fastest.
并行化 theINSERT和 theSELECT是最快的。
(If you have a large enough amount of data, you have a decent server, everything is configured sanely, etc.)
（如果你有足够多的数据，你有一个不错的服务器，一切都配置合理，等等。）
You'll definitely want to test it yourself, especially to find the optimal degree of parallelism. There are a lot of myths surrounding Oracle parallel execution, and even the manual is sometimes horribly wrong.
您肯定想自己测试一下，尤其是要找到最佳的并行度。有很多关于 Oracle 并行执行的神话，甚至手册有时也大错特错。
On 11gR2, I would recommend you run your statement like this:
在 11gR2 上，我建议你像这样运行你的语句：
alter session enable parallel dml;
insert /*+ append parallel(6) */ into A select * from B;
- You always want to enable parallel dml first.
parallel(6)uses statement-level parallelism, instead of object-level parallelism. This is an 11gR2 feature that allows you to easily run everything in parallel witout having to worry about object aliases or access methods. For 10G you'll have to use multiple hints.- Normally the
appendhint isn't necessary. If your DML runs in parallel, it will automatically use direct-path inserts. However, if your statement gets downgraded to serial, for example if there are no parallel servers available, then theappendhint can make a big difference.
- 您总是希望首先启用并行 dml。
parallel(6)使用语句级并行，而不是对象级并行。这是 11gR2 的一项功能，可让您轻松并行运行所有内容，而不必担心对象别名或访问方法。对于 10G，您必须使用多个提示。- 通常
append不需要提示。如果您的 DML 并行运行，它将自动使用直接路径插入。但是，如果您的语句降级为串行，例如如果没有可用的并行服务器，则append提示可能会产生很大的不同。
回答by David Aldridge
You do not need DBA privileges to run an explain plan. I believe that SELECT_CATALOG is the correct privilege.
您不需要 DBA 特权来运行解释计划。我相信 SELECT_CATALOG 是正确的特权。
