文章/答案/技术大牛

发布

社区首页 >问答首页 >使用IDE工具作为SQL Developer或蟾蜍的Oracle并行查询行为

问使用IDE工具作为SQL Developer或蟾蜍的Oracle并行查询行为
EN

Stack Overflow用户

提问于 2020-07-24 15:30:12

回答 1查看 768关注 0票数 2

有一段时间，我一直在努力争取时间来写这个问题，并尽我所能最好地解释这个问题，所以请提前原谅我的长文。

我的环境：

Oracle数据库12.2运行在Red 7(R.A.C2节点)-16 per和每个节点64 on上。
设置为TRUE，强制并行服务器进程只能在启动SQL语句的同一节点上执行。

我们有一个非常大的数据库，其中包含了许多模式，这些模式为多个应用程序提供服务。实际上，大多数应用程序都是PL/SQL中的批处理引擎，处理数百万条记录，因此由于性能原因，大多数大型表都配置了并行度默认值。对表进行分区，并进行高级压缩。

除了一些用于利用的报告BI工具之外，许多最终用户还可以使用SQL Developer访问系统(仅在读取模式下)进行QA检查。我从来不喜欢，但有时你必须接受事情的现状。

为了控制一些事情，我设计了一个特定的登录触发器，它不仅包括审计功能，还包括传入会话的一些方面：

使用进行访问的最终用户可能只打开两个会话。
使用进行访问的最终用户运行执行立即ALTER禁用并行查询。不幸的是，我知道一些用户正在自己激活它。ALTER启用/禁用并行查询是由CREATE特权或CONNECT角色继承的，因此我不能对此做任何事情。
使用SQL进行访问的最终用户被分配到具有CPU、磁盘读取等限制条件的特定配置文件。

登录触发器允许或不允许基于一组附加规则的访问，但就问题而言，它们并不重要。

让我们看看在上运行的查询如何针对具有并行启用功能的表进行操作：

场景

我有一个包含80亿条记录的表，这些记录被不同的分区分割。使用sql developer登录并运行此查询的用户。

SELECT COUNT(*) FROM MY_SCHEMA.MY_TABLE PARTITION ( MY_PARTITION ) ;

183.940.801 rows

由于该表没有索引，CBO将使用它所考虑的尽可能多的从表并行运行一个完整的表扫描。它需要6秒才能完成。到目前为止没出什么问题。

同时，我正在监视会话(在运行查询时，可以看到所有会话都处于活动状态)

  INSTANCE     SID USERNAME             PROGRAM                                            SQL_ID               STATUS
---------- ------- -------------------- -------------------------------------------------- -------------------- --------
         2       6 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00N)       dtf8d89xg7muq        ACTIVE
         2     128 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P004)       dtf8d89xg7muq        ACTIVE
         2     140 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P007)       dtf8d89xg7muq        ACTIVE
         2     256 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00O)       dtf8d89xg7muq        ACTIVE
         2     284 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00D)       dtf8d89xg7muq        ACTIVE
         2     388 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00C)       dtf8d89xg7muq        ACTIVE
         2     400 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00P)       dtf8d89xg7muq        ACTIVE
         2     510 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00E)       dtf8d89xg7muq        ACTIVE
         2     621 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00F)       dtf8d89xg7muq        ACTIVE
         2     641 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00Q)       dtf8d89xg7muq        ACTIVE
         2     739 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P008)       dtf8d89xg7muq        ACTIVE
         2     771 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P005)       dtf8d89xg7muq        ACTIVE
         2     888 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00R)       dtf8d89xg7muq        ACTIVE
         2     893 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00G)       dtf8d89xg7muq        ACTIVE
         2     996 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00H)       dtf8d89xg7muq        ACTIVE
         2    1010 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00S)       dtf8d89xg7muq        ACTIVE
         2    1015 FDM_ADM_GRID         SQL Developer                                      dtf8d89xg7muq        ACTIVE
         2    1109 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00T)       dtf8d89xg7muq        ACTIVE
         2    1116 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00I)       dtf8d89xg7muq        ACTIVE
         2    1230 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00J)       dtf8d89xg7muq        ACTIVE
         2    1254 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00U)       dtf8d89xg7muq        ACTIVE
         2    1352 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P009)       dtf8d89xg7muq        ACTIVE
         2    1376 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P001)       dtf8d89xg7muq        ACTIVE
         2    1383 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P006)       dtf8d89xg7muq        ACTIVE
         2    1477 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00V)       dtf8d89xg7muq        ACTIVE
         2    1488 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P000)       dtf8d89xg7muq        ACTIVE
         2    1506 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00K)       dtf8d89xg7muq        ACTIVE
         2    1604 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P002)       dtf8d89xg7muq        ACTIVE
         2    1617 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00L)       dtf8d89xg7muq        ACTIVE
         2    1620 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00A)       dtf8d89xg7muq        ACTIVE
         2    1740 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P003)       dtf8d89xg7muq        ACTIVE
         2    1743 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00M)       dtf8d89xg7muq        ACTIVE
         2    1851 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00B)       dtf8d89xg7muq        ACTIVE

查询完成后

SQL> r
  1* select inst_id as instance , sid, username, program, sql_id, status from gv$session where username = 'FDM_ADM_GRID'

  INSTANCE     SID USERNAME             PROGRAM                                            SQL_ID               STATUS
---------- ------- -------------------- -------------------------------------------------- -------------------- --------
         2    1015 FDM_ADM_GRID         SQL Developer                                                           INACTIVE

到目前一切尚好。现在让我们在中运行另一个查询

SELECT * FROM MY_SCHEMA.MY_TABLE partition ( MY_PARTITION ) fetch first 1000 rows only;

查询几乎立即检索前1000行。但是让我们看看数据库中的区别

当它运行的时候

SQL> r
  1* select inst_id as instance , sid, username, program, sql_id, status from gv$session where username = 'FDM_ADM_GRID'

  INSTANCE     SID USERNAME             PROGRAM                                            SQL_ID               STATUS
---------- ------- -------------------- -------------------------------------------------- -------------------- --------
         2       6 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00N)       9jyvj64ag15mv        ACTIVE
         2     128 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P007)       9jyvj64ag15mv        ACTIVE
         2     140 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P004)       9jyvj64ag15mv        ACTIVE
         2     256 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00D)       9jyvj64ag15mv        ACTIVE
         2     284 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00O)       9jyvj64ag15mv        ACTIVE
         2     388 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00P)       9jyvj64ag15mv        ACTIVE
         2     400 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00C)       9jyvj64ag15mv        ACTIVE
         2     510 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00E)       9jyvj64ag15mv        ACTIVE
         2     621 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00F)       9jyvj64ag15mv        ACTIVE
         2     641 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00Q)       9jyvj64ag15mv        ACTIVE
         2     739 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P008)       9jyvj64ag15mv        ACTIVE
         2     771 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P005)       9jyvj64ag15mv        ACTIVE
         2     888 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00G)       9jyvj64ag15mv        ACTIVE
         2     893 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00R)       9jyvj64ag15mv        ACTIVE
         2     996 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00H)       9jyvj64ag15mv        ACTIVE
         2    1010 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00S)       9jyvj64ag15mv        ACTIVE
         2    1015 FDM_ADM_GRID         SQL Developer                                                           ACTIVE
         2    1109 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00I)       9jyvj64ag15mv        ACTIVE
         2    1116 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00T)       9jyvj64ag15mv        ACTIVE
         2    1230 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00J)       9jyvj64ag15mv        ACTIVE
         2    1254 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00U)       9jyvj64ag15mv        ACTIVE
         2    1352 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P006)       9jyvj64ag15mv        ACTIVE
         2    1376 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P009)       9jyvj64ag15mv        ACTIVE
         2    1383 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P001)       9jyvj64ag15mv        ACTIVE
         2    1477 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P000)       9jyvj64ag15mv        ACTIVE
         2    1488 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00V)       9jyvj64ag15mv        ACTIVE
         2    1506 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00K)       9jyvj64ag15mv        ACTIVE
         2    1604 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P002)       9jyvj64ag15mv        ACTIVE
         2    1617 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00A)       9jyvj64ag15mv        ACTIVE
         2    1620 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00L)       9jyvj64ag15mv        ACTIVE
         2    1740 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P003)       9jyvj64ag15mv        ACTIVE
         2    1743 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00M)       9jyvj64ag15mv        ACTIVE
         2    1851 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00B)       9jyvj64ag15mv        ACTIVE

查询一完成，我就会再次检查，但是现在所有的奴隶仍然在那里并且处于活动状态。不过，当QC完成并标记为非活动时，Oracle就会关闭所有从进程。但事实并非如此

  `INSTANCE     SID USERNAME             PROGRAM`                                            SQL_ID               STATUS
---------- ------- -------------------- -------------------------------------------------- -------------------- --------
     2       6 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00N)       9jyvj64ag15mv        ACTIVE
     2     128 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P007)       9jyvj64ag15mv        ACTIVE
     2     140 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P004)       9jyvj64ag15mv        ACTIVE
     2     256 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00D)       9jyvj64ag15mv        ACTIVE
     2     284 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00O)       9jyvj64ag15mv        ACTIVE
     2     388 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00P)       9jyvj64ag15mv        ACTIVE
     2     400 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00C)       9jyvj64ag15mv        ACTIVE
     2     510 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00E)       9jyvj64ag15mv        ACTIVE
     2     621 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00F)       9jyvj64ag15mv        ACTIVE
     2     641 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00Q)       9jyvj64ag15mv        ACTIVE
     2     739 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P008)       9jyvj64ag15mv        ACTIVE
     2     771 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P005)       9jyvj64ag15mv        ACTIVE
     2     888 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00G)       9jyvj64ag15mv        ACTIVE
     2     893 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00R)       9jyvj64ag15mv        ACTIVE
     2     996 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00H)       9jyvj64ag15mv        ACTIVE
     2    1010 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00S)       9jyvj64ag15mv        ACTIVE
     2    1015 FDM_ADM_GRID         SQL Developer                                                           INACTIVE
     2    1109 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00I)       9jyvj64ag15mv        ACTIVE
     2    1116 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00T)       9jyvj64ag15mv        ACTIVE
     2    1230 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00J)       9jyvj64ag15mv        ACTIVE
     2    1254 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00U)       9jyvj64ag15mv        ACTIVE
     2    1352 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P006)       9jyvj64ag15mv        ACTIVE
     2    1376 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P009)       9jyvj64ag15mv        ACTIVE
     2    1383 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P001)       9jyvj64ag15mv        ACTIVE
     2    1477 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P000)       9jyvj64ag15mv        ACTIVE
     2    1488 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00V)       9jyvj64ag15mv        ACTIVE
     2    1506 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00K)       9jyvj64ag15mv        ACTIVE
     2    1604 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P002)       9jyvj64ag15mv        ACTIVE
     2    1617 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00A)       9jyvj64ag15mv        ACTIVE
     2    1620 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00L)       9jyvj64ag15mv        ACTIVE
     2    1740 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P003)       9jyvj64ag15mv        ACTIVE
     2    1743 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00M)       9jyvj64ag15mv        ACTIVE
     2    1851 FDM_ADM_GRID         oracle@scglvdoraci0010.scger.pre.corp (P00B)       9jyvj64ag15mv        ACTIVE

只要会话保持开放，QC将保持不活动状态，奴隶将处于活动状态，因此尽管他们什么也不做，但他们仍然被视为并行服务器。或者会话关闭，或者用户运行另一个查询，以便我注意并行使用中的更改。但是如果用户去喝咖啡，或者去启动或者正在做其他的事情，就不会有任何问题了。在100多个用户同时工作的情况下，您可能会觉得头痛。我不得不设计一些解决方案：

我必须在触发器内创建一个新的控件，以确定QC与处于活动状态的从属程序之间的非活动时间，以确定用户已经打开了多少会话。
在窗口时间为1小时后，我必须创建一个清理过程，以断开处于此状态的会话。
我不能在Profiles中使用限制会话，因为它们在QC和奴隶之间没有区别。
无论我配置了多少东西，有时我都会耗尽并行进程，如果一个批处理过程在工作时间内被执行(这种情况经常发生)，我有时会面临这些重要进程的并行可用性不足，因为不活动的会话占用了大量的从进程。

我的问题如下：

当QC已经完成时，为什么奴隶仍然处于活跃状态？当QC发布结果后，奴隶们不应该立即被终止吗？
为什么当SQLPLUS或Java解决方案(作为solutions对象)运行非常相似的查询时，没有发生这种行为？
是否有一种方法可以禁用最终用户的并行功能，无论他们试图通过启用并行查询还是通过提示来激活它们？

我为这个冗长的问题道歉，但我不想留下任何东西。我非常感谢你对这件事的任何见解。

谢谢你们所有人。

oracle-rac

parallel-query

oracle

回答 1

Stack Overflow用户

回答已采纳

发布于 2020-07-24 23:18:55

您的查询还没有真正完成。虽然查询只获取前1000行，但只获取这1000行中的前50行。在滚动到最后一行之前，IDE不会关闭游标。一旦检索到所有数据，这些并行进程就会消失。确保您看到的是“在X秒内获取的所有行: 1000”，而不是“在Y秒中获取50行”。(我希望SQL Developer能够在视觉上更清楚地看到有更多的行在等待。)您不会在SQL*Plus中看到这个问题，因为SQL*Plus总是抓取所有的行。

当只获取第一个N行时，这些并行进程是“活动的”，但没有执行任何操作。您应该能够忽略这些会话，因为它们没有使用任何重要的资源。

如果您担心并行会话的数量，您可能需要调整您的期望。我以前和你一样，经常告诉用户他们(不完整)的查询占用了所有的并行会话。最后，我发现这只是一个问题，因为我创造了一种人为的稀缺资源。Oracle并行进程通常是轻量级的，而且数据库可以支持比大多数人认为的更多的并行进程。

PARALLEL_MAX_SERVERS、PARALLEL_THREADS_PER_CPU和CPU_COUNT的参数值是多少？查看服务器的默认值。根据手册，默认编号是：PARALLEL_MAX_SERVERS = PARALLEL_THREADS_PER_CPU * CPU_COUNT * concurrent_parallel_users * 5。

大多数DBA看到的最大并行线程数在数百个，恐慌，然后减少了这个数目。然后我们开始对开发人员大喊大叫，因为他们使用了一种被人为限制的不重要的资源。相反，我们应该将数字调回到缺省值，忽略随机并行会话。如果用户没有超过IO或CPU限制，那么它们使用多少个并行线程就不重要了。

(可能的例外是防止大量并行查询会话使用。将您的用户放到不同的配置文件中，并将他们的SESSIONS_PER_USER设置为几十个。不要将其限制为1或2。IDE需要为多个选项卡、获取元数据的后台进程和调试会话提供额外的会话。如果将限制设置为2，则开发人员将无法正确使用IDE。)

编辑(对注释的响应)

我不确定你是否能读到查询协调器的状态。QC做了几件事，但理想情况下，它大部分时间都是空闲的，而并行会话则处理大部分工作。

使用生产者/消费者模型，一半的并行会话可能正在接收数据，但实际上没有做任何事情--就像它们只是某些操作中的内存结构一样。并行会话可以在活动会话和非活动会话之间切换，因为并非所有步骤都需要相同数量的会话。但是我们不希望Oracle在中间关闭会话，因为以后可能需要它们，我们也不想浪费时间来打开和结束会话。

有许多因素影响并行度，但据我所知，增加PARALLEL_MAX_SERVERS不会影响为单个语句请求的并行服务器的数量。(但如果语句要求的服务器已经超过最大值，则增加参数可能会影响分配的会话数量)。

可能感觉SQL语句只是随机地获取所有并行会话，但最终DOP计算几乎总是遵循确定性规则。只是规则太复杂了，很难说它是怎么工作的。例如，一个常见的混淆点是，每当查询添加排序或分组时，并行会话的数量就会翻倍。

票数 2

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/63076728

复制

相似问题

问使用IDE工具作为SQL Developer或蟾蜍的Oracle并行查询行为
EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用IDE工具作为SQL Developer或蟾蜍的Oracle并行查询行为EN

回答 1

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用IDE工具作为SQL Developer或蟾蜍的Oracle并行查询行为
EN