linux - Oracle 11.2 在随机时间对简单 SQL 有 2 秒的延迟

Question

一个简单的表连接通常在 0.0XX 秒内完成，有时在 2.0XX 秒内完成(根据 PL/SQL Developer SQL 执行)。从 SQL Plus 运行时，它仍然会发生。

如果我运行 SQL 10 次，其中 8 次运行良好，2 次在 2+ 秒内运行。

这是在 Centos 7 上全新安装适用于 Linux x86_64 的 Oracle 11.2.0.4。我已经安装了 Oracle 推荐的补丁:

• 补丁 19769489 - 数据库补丁集更新 11.2.0.4.5(包括 CPUJan2015)
• 补丁 19877440 - Oracle JavaVM 组件 11.2.0.4.2 数据库 PSU(2015 年 1 月)

打补丁后没有变化。

2个表有:LNK_PACK_REP:13 行包装:6 行

在 SQL Plus 中，我启用了所有统计信息并多次运行 SQL。只有时间不时从0.1变为2.1。如果我将 0.1 秒内的运行与 2.1 秒内的运行进行比较，则没有其他统计数据发生变化。服务器有 16 Gb RAM 和 8 个 CPU 内核。服务器负载低于 0.1(目前没有用户使用服务器)。

输出:

SQL> select PACKAGE_ID, id, package_name from LNK_PACK_REP LNKPR INNER JOIN PACKAGES P ON LNKPR.PACKAGE_ID = P.ID;

PACKAGE_ID ID PACKAGE_NAME

---

     3          3 RAPOARTE
     3          3 RAPOARTE
   121        121 VANZARI
   121        121 VANZARI
   121        121 VANZARI
     2          2 PACHETE
     2          2 PACHETE
     1          1 DEPARTAMENTE
     1          1 DEPARTAMENTE
    81         81 ROLURI
    81         81 ROLURI

PACKAGE_ID ID PACKAGE_NAME

---

   101        101 UTILIZATORI
   101        101 UTILIZATORI

已选择 13 行。

经过:00:00:02.01

执行计划

计划哈希值:2671988802

--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name              | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                   |    13 |   351 |     3   (0)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR         |                   |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (RANDOM)   | :TQ10002          |    13 |   351 |     3   (0)| 00:00:01 |  Q1,02 | P->S | QC (RAND)  |
|*  3 |    HASH JOIN            |                   |    13 |   351 |     3   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE          |                   |     6 |    84 |     2   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX SEND HASH       | :TQ10001          |     6 |    84 |     2   (0)| 00:00:01 |  Q1,01 | P->P | HASH       |
|   6 |       PX BLOCK ITERATOR |                   |     6 |    84 |     2   (0)| 00:00:01 |  Q1,01 | PCWC |            |
|   7 |        TABLE ACCESS FULL| PACKAGES          |     6 |    84 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
|   8 |     BUFFER SORT         |                   |       |       |            |          |  Q1,02 | PCWC |            |
|   9 |      PX RECEIVE         |                   |    13 |   169 |     1   (0)| 00:00:01 |  Q1,02 | PCWP |            |
|  10 |       PX SEND HASH      | :TQ10000          |    13 |   169 |     1   (0)| 00:00:01 |        | S->P | HASH       |
|  11 |        INDEX FULL SCAN  | UNQ_PACK_REP      |    13 |   169 |     1   (0)| 00:00:01 |        |      |            |
--------------------------------------------------------------------------------------------------------------------------

谓词信息(由操作 id 标识):

3 - 访问("LNKPR"."PACKAGE_ID"="P"."ID")

注意事项

• 用于此语句的动态采样(level=2)

统计

     24  recursive calls
      0  db block gets
     10  consistent gets
      0  physical reads
      0  redo size
    923  bytes sent via SQL*Net to client
    524  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      4  sorts (memory)
      0  sorts (disk)
     13  rows processed

表1结构:

-- Create table
create table PACKAGES
(
  id           NUMBER(3) not null,
  package_name VARCHAR2(150),
  position     NUMBER(3),
  activ        NUMBER(1)
)
tablespace UM
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table PACKAGES
  add constraint PACKAGES_ID primary key (ID)
  using index 
  tablespace UM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index PACKAGES_ACTIV on PACKAGES (ID, ACTIV)
  tablespace UM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

表2结构:

-- Create table
create table LNK_PACK_REP
(
  package_id NUMBER(3) not null,
  report_id  NUMBER(3) not null
)
tablespace UM
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate primary, unique and foreign key constraints 
alter table LNK_PACK_REP
  add constraint UNQ_PACK_REP primary key (PACKAGE_ID, REPORT_ID)
  using index 
  tablespace UM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
-- Create/Recreate indexes 
create index LNK_PACK_REP_REPORT_ID on LNK_PACK_REP (REPORT_ID)
  tablespace UM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );

在 SQL Monitor 的 Oracle Enterprise Manager 中，我可以看到多次运行的 SQL。所有运行的“数据库时间”为 0.0 秒(如果我将鼠标悬停在列表上，则在 10 微秒以下)和“持续时间”正常运行为 0.0 秒，延迟运行为 2.0 秒。如果我为那次运行的 2.0 转到受监控的 SQL 执行，我有:

• 时长:2.0s
• 数据库时间:0.0s
• PL/SQL 和 Java:0.0
• 等待事件:%(这里没有数字)
• 缓冲区获取:10
• IO 请求:0
• IO 字节:0
• 接听电话:2
• 并行:4

这些数字与快速运行相一致，但持续时间甚至小于数据库时间(10,163 微秒数据库时间和 3,748 微秒持续时间)，如果没有鼠标悬停，两者都显示为 0.0 秒。

我不知道还要检查什么。

Answer 1

并行查询无法在几秒钟内进行有意义的调整。它们专为长时间处理大量数据的查询而设计。

用小数据集优化并行语句的最好方法是暂时禁用它:

alter system set parallel_max_servers=0;

(这是一个很好的例子，说明了在工作站而不是服务器上进行开发的优势。在服务器上，这种变化会影响到每个人，你甚至可能没有运行命令的权限。)

查询可能很简单，但并行性在后台增加了很多复杂性。

很难确切地说出它变慢的原因。如果您有 SQL 监控报告，等待事件可能会有所帮助。但即使是这些数字也可能只是像“CPU”这样的通用等待。并行查询有很多开销，预计资源密集型、长时间运行的查询。以下是一些类型的开销，可以解释这 2 秒的来源:

1. 动态采样 - 并行可能会自动导致动态采样，从表中读取数据。尽管 此语句使用了动态采样 (level=2)可能只是暗示缺少优化器统计信息。
2. OS Thread startup - SQL 语句可能需要启动 8 个额外的 OS 线程，并准备大量内存来保存所有中间数据。也许参数 PARALLEL_MIN_SERVERS 可以帮助避免花费一些时间来创建这些线程。
3. 额外监控 - 自动监控并行语句，这需要递归 SELECT 和 INSERT。
4. 缓存 - 并行查询通常直接从磁盘读取并跳过读取和写入缓冲区缓存。它何时缓存数据的规则很复杂且没有记录。
5. 降级 - 找到正确的并行度很复杂。例如，我编制了一个列表 39 factors that influence the DOP .其中之一可能会导致降级，使某些查询变快而另一些变慢。

而且可能还有几十种我想不到的其他类型的开销。并行性非常适合大幅改进大型操作的运行时间。但它不适用于微小的查询。