Oracle分布式查询优化案例——以案例的方式进一步熟悉Oracle产品的优化机制与方法（内附生成5万条模拟数据的代码）

---

前言

以分布式数据库管理系统Oracle为例，通过实施于具体分布式应用场景的查询，以案例的方式进一步熟悉Oracle产品的优化机制与方法。
在分布式数据库环境下，实施不同的优化策略，创建应用场景，并导入一定量的模拟数据，学会使用Oracle的提示(hint)功能以指定不同的查询优化计划，并对相应策略做出解释。

---

0 软件版本

OS：一台Windows10的电脑和在其上搭建一台CentOS7的虚拟机
数据库：Oracle19c
图形化工具：Navicat for Oracle
（建议直接从官网下载，也可以从我之前的博客中获取，里面讲述如何安装和使用这些软件）

---

1 应用场景的创建，以及数据的生成与导入

1.1 创建应用场景

OraStar公司在总部保存供应商的信息，在生产部门保存每批进货的零件产品的信息。
（1）在公司总部节点1上，新建供应商表

CREATE TABLE supplier (
	supNo NUMBER (2) PRIMARY KEY,
	supName VARCHAR2 (14),
	loc VARCHAR2 (13)
);

插入五条供应商数据到总部的供应商信息表中：

INSERT INTO SUPPLIER VALUES (1,'supplier01','中国上海');
INSERT INTO SUPPLIER VALUES (2,'supplier02','中国北京');
INSERT INTO SUPPLIER VALUES (3,'supplier03','中国武汉');
INSERT INTO SUPPLIER VALUES (4,'supplier04','日本东京');
INSERT INTO SUPPLIER VALUES (5,'supplier05','美国纽约');

再次查看供应商表：

（2）在生产部门节点2上，新建零件产品表

CREATE TABLE product (
	pNo VARCHAR2(50) NOT NULL,
	pName VARCHAR2 (15),
	buyDate DATE,
	price NUMBER (7, 2),
	supNo NUMBER (2)
);

此处由于需生产大量零件产品数据，故采用了利用flinkStream随机生成了五万条数据，程序代码如下：

import org.apache.flink.core.fs.FileSystem.WriteMode
import org.apache.flink.streaming.api.functions.source.{RichSourceFunction, SourceFunction}
import org.apache.flink.streaming.api.scala.{DataStream, StreamExecutionEnvironment}
import org.apache.flink.streaming.api.scala._

import java.util.concurrent.TimeUnit
import java.util.{Date, UUID}
import scala.util.Random

object ProductRandom {

  //样例类
  case class
  Product(pNo: String, pName: String, buyDate: Date, price: Number, supNo: Number)

  def main(args: Array[String]): Unit = {
    //TODO:1.environment
    val env: StreamExecutionEnvironment = StreamExecutionEnvironment.getExecutionEnvironment

    //TODO:2.source,生产随机订单数据
    val sourceDStream: DataStream[Product] = env.addSource(new ProductSourceFunction)

    //TODO:3.Transformation

    //TODO:4.Sink,将数据写入excel表
    sourceDStream.writeAsCsv("src\\main\\resources\\Product.csv", WriteMode.OVERWRITE)

    //TODO:5.Execution
    env.execute("ProductRandom Job")

  }

  class ProductSourceFunction extends RichSourceFunction[Product] {
    //定义变量
    private var count: Long = 0L
    private var isRunning: Boolean = true

    override def run(sourceContext: SourceFunction.SourceContext[Product]): Unit = {
      // 使用while循环生成50000个订单(零件产品编号pNo、零件名称pName、购买时间buyDate、零件价格price、供应商编号supNo )
      while (isRunning && count < 50000) {
        // 随机生成零件产品编号pNo（UUID）
        val pNo: String = UUID.randomUUID().toString
        // 随机生成零件名称pName（product1-product100）
        val pName: String = "product" + (Random.nextInt(10) + 1)
        // 购买时间buyDate为当前系统时间
        val buyDate: Date = new Date(System.currentTimeMillis())
        // 随机生成零件价格price（100~1000）
        val price: Double = Random.nextInt(900) + 101
        // 随机生成供应商编号supNo（1-5）
        val supNo: Double = Random.nextInt(5) + 1

        // 收集数据
        sourceContext.collect(Product(pNo, pName, buyDate, price, supNo))
        // 统计生成的订单数
        count += 1

        // 每隔1毫秒生成一个订单k
        TimeUnit.MILLISECONDS.sleep(1)
        //        TimeUnit.MILLISECONDS.sleep(1)
      }
    }

    override def cancel(): Unit = {
      isRunning = false
    }
  }
}

可以看到生成了五万条模拟数据：


然后将生成的product.csv文件导入生产部门的零件信息表中：

（3）在节点1上，新建到生产部门的数据链

create database link link1 connect to scott identified by tiger using '网络服务名';

注：若数据库链此前建立过，可不需再重复建立。

1.2 发起查询请求

总部用户希望查找产品名字为“product5”的供货商的信息和对应的供货信息。

SELECT
	*
FROM
	SUPPLIER t1,
	PRODUCT @link1 t2
WHERE
	T2.supNo = T1.SUPNO AND T2.pName = 'product5';

---

2 基于查询实例，采取不同优化策略，并对相应的优化策略做出解释

2.1 以不同的优化策略实施查询

（1）以优化器默认的方式发起查询，并对相应的优化策略做出解释

explain plan for
SELECT
	*
FROM
	SUPPLIER t1,
	PRODUCT @link1 t2
WHERE
	T2.supNo = T1.SUPNO
AND T2.pName = 'product5';

SELECT * FROM TABLE(dbms_xplan.display);

上述查询语句的执行计划如下：
可以看到CBO优化器默认采用了嵌套循环的连接方式来进行查询操作。
嵌套循环有驱动表和被驱动表的概念，驱动顺序不同执行计划差异非常大，驱动表只被访问一次，被驱动表被访问多次。嵌套循环访问表的次数直接受驱动表的返回记录数的影响。因此应当让实际返回记录数（A-Rows）小的表作为驱动表，返回记录数大的表作为被驱动表。

（2）以/*+hint*/的方式提示优化器采取哈希连接的连接策略

explain plan for
SELECT
	/*+USE_HASH(t1,t2)*/
	*
FROM
	SUPPLIER t1,
	PRODUCT @link1 t2
WHERE
	T2.supNo = T1.SUPNO
AND T2.pName = 'product5';

SELECT * FROM TABLE(dbms_xplan.display);

可以看出，CBO为这个查询选择了**散列连接（Hash Join）**的连接策略。
散列连接选择两个表中数据量较小的一个(SUPPLIER)做全表扫描，并在内存中形成一个散列表保存SUPPLIER表的数据。然后Oracle 扫描数据量较大的表( PRODUCT)，对于其每一行的SUPNO，查找散列表中相匹配的行，返回连接映像。
由于散列表保存在一块Oracle 的私有内存中，可以避免某些不必要的锁操作，所以访问速度非常快。散列连接非常适合表的数据量一大一小，而且小表的数据量完全可以保存到内存中的连接案例。

（3）以/*+hint*/的方式提示优化器采取嵌套循环的连接策略

explain plan for
SELECT
	/*+USE_NL(t1,t2)*/
	*
FROM
	SUPPLIER t1,
	PRODUCT @link1 t2
WHERE
	T2.supNo = T1.SUPNO
AND T2.pName = 'product5';

SELECT * FROM TABLE(dbms_xplan.display);

可以看出，CBO为这个查询选择了嵌套循环（Nested Loop）连接策略。
嵌套循环连接策略，外表驱动内表，外表的每一行都会在内表中进行匹配。与Hash Join不同的是，没有使用内表来生成HashTable，因此内表最好有索引

（4）以/*+hint*/的方式提示优化器采取排序合并的连接策略

explain plan for
SELECT
	/*+USE_MERGE(T1,T2)*/
	*
FROM
	SUPPLIER t1,
	PRODUCT @link1 t2
WHERE
	T2.supNo = T1.SUPNO
AND T2.pName = 'product5';

SELECT * FROM TABLE(dbms_xplan.display);

可以看出，CBO为这个查询选择了排序合并（Merge Join）连接策略。
排序合并连接可以联接来自两个独立源的行。哈希联接通常比排序合并联接性能更好。但是，如果同时存在以下两种情况，则排序合并联接的性能可能优于哈希联接：

1. 行源已排序。
2. 不必执行排序操作。

---

3 解释Oracle的CBO是如何利用并列内联视图的方式提高分布式查询的性能

查询语句：

-- EXPLAIN PLAN FOR 
SELECT
	V.PNO,
	V.PNAME,
	E .SUPNO,
	E .SUPNAME
FROM
	(
		SELECT
			PNO,PNAME,PRICE,SUPNO
		FROM
			PRODUCT @link1
	) V,
	SUPPLIER E
WHERE
	V.SUPNO = E .SUPNO;

-- SELECT * FROM TABLE (dbms_xplan.display);

查看执行计划：

内联视图指的是一种嵌入式的Select语句，用于替换主Select语句中的表。
并列内联视图是指从同一个数据库中的多个表上执行Select语句所获得的内联视图。
对于分布式查询，Oracle数据库采用并列内联视图（Collocated Inline View）来降低网络间传输的数据量，并减少对远程数据库的访问。

发起相关查询实例，利用Oracle的提示(hint)功能给查询语句指定查询计划:
（1）利用NO_Merge提示
添加如下NO_MERGE提示，避免将一个内联视图合并到一个潜在的非并列SQL语句：

/*+NO_MERGE(V)*/

使用NO_MERGE 提示，可以避免将一个内联视图合并到一个潜在的非并列SQL语句中。

（2）利用DRIVING_SITE提示
添加如下DRIVING_SITE提示，指定SQL语句的执行地点：

/*+DRIVING_SITE(E)*/

---

总结

在分布式数据库环境下，实施不同的优化策略，创建应用场景，并导入一定量的模拟数据，学会使用Oracle的提示(hint)功能以指定不同的查询优化计划，并对相应策略做出解释。

---

后续会继续更新有关Oracle分布式数据库的内容!
（注：第19次发文，如有错误和疑问，欢迎在评论区指出!）
——2021.11.12