对于相同的对象，什么可能导致 Oracle 选择在一个数据库上而不是在另一个数据库上并行执行？
我有一个测试和开发 Oracle 19c 数据库，该数据库在旧的现有查询上耗尽了临时表空间。解释计划显示，在数据库空间不足的情况下，解释计划使用大量并行执行步骤（PX SEND BROADCAST、PX RECEIVE、PX BLOCK ITERATOR）。数据库还缓冲大量扫描数据，我认为这就是导致所有空间被耗尽的原因。
据我检查，在开发数据库上，相同的查询，相同的对象，相同的索引，其他所有内容都相同，它运行时不会耗尽空间。解释计划使用了大约一半的步骤，并且根本不使用并行执行。
我正在尝试与我们的一位 DBA 合作找出造成差异的原因。我应该注意哪些事情可以解释解释计划中的这种差异？我已经检查过确保索引相同，数据大小相同，最近运行了收集统计信息，并且我还检查了这些设置：
select PDML_ENABLED、PDML_STATUS、PDDL_STATUS、PQ_STATUS FROM V$ session where sid = (select sid from v$mystat where rownum = 1);
是否有我可以在两个数据库之间进行比较的全局或会话参数？
I have a test and development Oracle 19c database that is running out of temp tablespace on an older pre-existing query. The explain plan shows that, on the database running out of space, the explain plan is using a lot of parallel execution steps (PX SEND BROADCAST, PX RECEIVE, PX BLOCK ITERATOR). The database is also buffering a lot of the scanned data, which I assume is what is causing all the space to get eaten up.
On the dev database, the same query, on the same objects, same indexes, same everything else as far as I have checked, it runs without running out of space. The explain plan uses about half the steps and does not use parallel execution at all.
I am trying to work with one of our DBAs to find what is causing the difference. What are some things I should look at that might explain such a difference in explain plan? I have looked at making sure the indexes are the same, the data size is the same, there have been recent gather stats run, and I have also looked at these settings:
select PDML_ENABLED, PDML_STATUS, PDDL_STATUS, PQ_STATUS FROM V$session where sid = (select sid from v$mystat where rownum = 1);
Are there any global or session parameters I might compare between the two databases?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
当您说表/索引相同时，请务必检查它们的“并行”属性。
在系统级别，检查parallel_ Degree_policy。
此外，解释计划应该告诉您为什么选择特定的程度或并行性；这可能提供一个线索。
When you say the tables/indexes are the same, make sure to check their "parallel" attribute.
At the system level, check parallel_degree_policy.
Also, an explain plan should tell you why a specific degree or parallelism was chosen; that might provide a clue.