调优技巧 - 超大表与超大表关联优化方法 - 《SQL优化入门》

现有如下 SQL。

select * from a,b where a.object_id=b.object_id;

表 a 有 4GB，表 b 有 6GB，两表关联后返回大量数据，应该走 HASH 连接。因为 a 比 b 小，所以 a 表应该作为 HASH JOIN 的驱动表。驱动表 a 有 4GB，需要放入 PGA 中。因为 PGA 中 work area 不能超过 2G，所以 PGA 不能完全容纳下驱动表，这时有部分数据会溢出到磁盘（TEMP）进行 on-disk hash join。我们可以开启并行查询加快查询速度。

超大表与超大表在进行并行 HASH 连接的时候，需要将两个表根据连接列进行 HASH 运算，然后将运算结果放到 PGA 中，再进行 HASH 连接，这种并行 HASH 连接就叫作并行 HASH HASH 连接。假设对上面 SQL 启用 6 个并行查询，a 表会根据连接列进行 HASH 运算然后拆分为 6 份，记为 a1，a2，a3，a4，a5，a6，b 表也会根据连接列进行 HASH 运算然后拆分为 6 份，记为 b1，b2，b3，b4，b5，b6。那么以上 SQL 开启并行就相当于被改写成如下 SQL。

select * from a1，b1 where a1.object_id=b1.object_id  ---并行进行 
union all
select * from a2，b2 where a2.object_id=b2.object_id  ---并行进行 
union all
select * from a3，b3 where a3.object_id=b3.object_id  ---并行进行 
union all
select * from a4，b4 where a4.object_id=b4.object_id  ---并行进行 
union all
select * from a5，b5 where a5.object_id=b5.object_id  ---并行进行 
union all
select * from a6，b6 where a6.object_id=b6.object_id； ---并行进行

对于上面 SQL，开启并行查询就能避免 on-disk hash join，因为表不是特别大，而且被拆分到内存中了。怎么写 HINT 实现并行 HASH HASH 呢？我们需要添 hint：**pq_distribute（被驱动表 hash，hash）**。

现在我们来查看并行 HASH HASH 的执行计划（为了方便排版，执行计划中省略了部分数据）。

explain plan for select 
/*+ parallel(6) use_hash(a,b) pq_distribute(b hash,hash) */
   *
    from a, b
   where a.object_id = b.object_id;

Explained.

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------
Plan hash value: 728916813
-------------------------------------------------------------------------------------
| Id | Operation               | Name     | Rows  | Bytes |TempSpc|IN-OUT|PQ Distrib|
-------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT        |          |  3046M|  1174G|       |      |          |
|  1 |  PX COORDINATOR         |          |       |       |       |      |          |
|  2 |   PX SEND QC (RANDOM)   | :TQ10002 |  3046M|  1174G|       | P->S |QC (RAND) |
|* 3 |    HASH JOIN BUFFERED   |          |  3046M|  1174G|   324M| PCWP |          |
|  4 |     PX RECEIVE          |          |  9323K|  1840M|       | PCWP |          |
|  5 |      PX SEND HASH       | :TQ10000 |  9323K|  1840M|       | P->P |HASH      |
|  6 |       PX BLOCK ITERATOR |          |  9323K|  1840M|       | PCWC |          |
|  7 |        TABLE ACCESS FULL| A        |  9323K|  1840M|       | PCWP |          |
|  8 |     PX RECEIVE          |          |    20M|  4045M|       | PCWP |          |
|  9 |      PX SEND HASH       | :TQ10001 |    20M|  4045M|       | P->P |HASH      |
| 10 |       PX BLOCK ITERATOR |          |    20M|  4045M|       | PCWC |          |
| 11 |        TABLE ACCESS FULL| B        |    20M|  4045M|       | PCWP |          |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."OBJECT_ID"="B"."OBJECT_ID")

两表如果进行的是并行 HASH HASH 关联，执行计划 Operation 会出现 PX SEND HASH 关键字，PQ Distrib 会出现 HASH 关键字。

如果表 a 有 20G，表 b 有 30G，即使采用并行 HASH HASH 连接也很难跑出结果，因为要把两个表先映射到 PGA 中，这需要耗费一部分 PGA，之后在进行 HASH JOIN 的时候也需要部分 PGA，此时 PGA 根本就不够用，如果我们查看等待事件，会发现进程一直在做 DIRECT PATH READ/WRITE TEMP。

如何解决超级大表（几十 GB）与超级大表（几十 GB）关联的性能问题呢？我们可以根据并行 HASH HASH 关联的思路，人工实现并行 HASH HASH。下面就是人工实现并行 HASH HASH 的过程。

现在我们创建新表 p1，在表 a 的结构上添加一个字段 HASH_VALUE，同时根据 HASH_VALUE 进行 LIST 分区。

 CREATE TABLE P1(
  HASH_VALUE NUMBER,
   OWNER VARCHAR2(30),
  OBJECT_NAME VARCHAR2(128),
  SUBOBJECT_NAME VARCHAR2(30),
  OBJECT_ID NUMBER,
  DATA_OBJECT_ID NUMBER,
  OBJECT_TYPE VARCHAR2(19),
 CREATED DATE,
   LAST_DDL_TIME DATE,
  TIMESTAMP VARCHAR2(19),
  STATUS VARCHAR2(7),
  TEMPORARY VARCHAR2(1),
  GENERATED VARCHAR2(1),
  SECONDARY VARCHAR2(1),
 NAMESPACE NUMBER,
  EDITION_NAME VARCHAR2(30)
  )
      PARTITION BY  list(HASH_VALUE)
 (
  partition p0 values (0),
   partition p1 values (1),
  partition p2 values (2),
  partition p3 values (3),
  partition p4 values (4)
  );

Table created.

然后我们创建新表 p2，在表 b 的结构上添加一个字段 HASH_VALUE，同时根据 HASH_VALUE 进行 LIST 分区。

CREATE TABLE P2(
  HASH_VALUE NUMBER,
   OWNER VARCHAR2(30),
  OBJECT_NAME VARCHAR2(128),
  SUBOBJECT_NAME VARCHAR2(30),
  OBJECT_ID NUMBER,
  DATA_OBJECT_ID NUMBER,
 OBJECT_TYPE VARCHAR2(19),
  CREATED DATE,
  LAST_DDL_TIME DATE,
  TIMESTAMP VARCHAR2(19),
  STATUS VARCHAR2(7),
  TEMPORARY VARCHAR2(1),
  GENERATED VARCHAR2(1),
  SECONDARY VARCHAR2(1),
  NAMESPACE NUMBER,
  EDITION_NAME VARCHAR2(30)
  )
     PARTITION BY  list(HASH_VALUE)
  (
  partition p0 values (0),
  partition p1 values (1),
  partition p2 values (2),
  partition p3 values (3),
  partition p4 values (4)
  );

Table created.

请注意，两个表分区必须一模一样，如果分区不一样，就有数据无法关联上。

我们将 a 表的数据迁移到新表 p1 中。

insert into p1
  select ora_hash（object_id, 4）， a.* from a； ---注意排除 object_id 为 null 的数据 
commit；

然后我们将 b 表的数据迁移到新表 p2 中。

insert into p2
  select ora_hash（object_id, 4）， b.* from b； ---注意排除 object_id 为 null 的数据 
commit；

下面 SQL 就是并行 HASH HASH 关联的人工实现。

select *
  from p1, p2
 where p1.object_id = p2.object_id
   and p1.hash_value = 0
   and p2.hash_value = 0;
select *
  from p1, p2
 where p1.object_id = p2.object_id
   and p1.hash_value = 1
   and p2.hash_value = 1;
select *
  from p1, p2
 where p1.object_id = p2.object_id
   and p1.hash_value = 2
   and p2.hash_value = 2;
select *
  from p1, p2
 where p1.object_id = p2.object_id
   and p1.hash_value = 3
   and p2.hash_value = 3;
select *
  from p1, p2
 where p1.object_id = p2.object_id
   and p1.hash_value = 4
   and p2.hash_value = 4;

此方法运用了 ora_hash 函数。Oracle 中的 HASH 分区就是利用的 ora_hash 函数。

ora_hash 使用方法如下。

ora_hash(列，HASH 桶)，HASH 桶默认是 4 294 967 295，可以设置 0～4 294 967 295。

ora_hash(object_id,4)会把 object_id 的值进行 HASH 运算，然后放到 0、1、2、3、4 这些桶里面，也就是说ora_hash(object_id,4)只会产生 0、1、2、3、4 这几个值。

将大表（a,b）拆分为分区表（p1，p2）之后，我们只需要依次关联对应的分区，这样就不会出现 PGA 不足的问题，从而解决了超级大表关联查询的效率问题。在实际生产环境中，需要添加多少分区，请自己判断