Oracle分布式查询优化案例

  • 写在最前面
  • 一、实验目的：
  • 二、实验内容：
  • 三、实验结果：
  • （1）创建如下应用场景：OraStar公司在**总部**保存供应商的信息，在**生产部门**保存每批进货的零件产品的信息。
  • Step1：在公司总部节点1上，新建供应商表。
  • Step2:在生产部门节点2上，新建零件产品表。
  • Step3:在节点1上，新建到生产部门的数据链。
  • （2）发起查询请求如下：总部用户希望查找产品名字为“CPU”的供货商的信息和对应的供货信息。
  • （3）以不同的优化策略实施查询
  • Step1:以优化器默认的方式发起查询，并对相应的优化策略做出解释
  • Step2:以/*+hint*/的方式提示优化器采取嵌套循环的连接策略
  • Step3:以/*+hint*/的方式提示优化器采取排序合并的连接策略
  • （4）发起相关查询实例，利用Oracle的提示(hint)功能给查询语句指定查询计划。
  • Step1:利用NO_Merge提示
  • Step2:利用DRIVING_SITE提示
  • 相关报错与解决：


写在最前面

这门课没学太明白，若有问题请批评指正(っ•̀ω•́)っ✎⁾⁾ ，鞠躬

一、实验目的：

以分布式数据库管理系统Oracle为例，通过实施于具体分布式应用场景的查询，以案例的方式进一步熟悉Oracle产品的优化机制与方法。

二、实验内容：

（1）创建如下应用场景：OraStar公司在总部保存供应商的信息，在生产部门保存每批进货的零件产品的信息。
Step1:在公司总部节点1上，新建供应商表。
Step2:在生产部门节点2上，新建零件产品表。
Step3:在节点1上，新建到生产部门的数据链。
（2）发起查询请求如下
总部用户希望查找产品名字为“CPU”的供货商的信息和对应的供货信息。
（3）以不同的优化策略实施查询
Step1:以优化器默认的方式发起查询，并对相应的优化策略做出解释
Step2:以/*+hint*/的方式提示优化器采取嵌套循环的连接策略
Step3:以/*+hint*/的方式提示优化器采取排序合并的连接策略
（4）发起相关查询实例，利用Oracle的提示(hint)功能给查询语句指定查询计划。
Step1:利用NO_Merge提示
Step2:利用DRIVING_SITE提示

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

三、实验结果：

应用场景的创建，以及数据的生成与导入。
基于查询实例，采取不同优化策略，并对相应的优化策略做出解释。

附选：
创建应用场景以及发起相关查询实例，解释Oracle的CBO是如何利用并列内联视图（Collocated Inline View）的方式提高分布式查询的性能。

（1）创建如下应用场景：OraStar公司在总部保存供应商的信息，在生产部门保存每批进货的零件产品的信息。

总部在本机，生产部门在虚拟机。

Step1：在公司总部节点1上，新建供应商表。

供应商表S

由供应商代码（SNO）、供应商姓名（SNAME）、供应商状态（STATUS）、供应商所在城市（CITY）组成。

分布式数据库系统实验五_oracle

CREATE TABLE S(
SNO VARCHAR2 (4) NOT NULL PRIMARY KEY,
SNAME VARCHAR2 (20),
STATUS VARCHAR2 (2),
CITY VARCHAR2 (10));

insert into s values('S1','精益',20,'天津');
insert into s values('S2','盛锡',10,'北京');
insert into s values('S3','东方红',30,'北京');
insert into s values('S4','丰泰盛',20,'天津');
insert into s values('S5','为民',30,'上海');

分布式数据库系统实验五_数据库_02

Step2:在生产部门节点2上，新建零件产品表。

零件表P

由零件代码（PNO）、零件名（PNAME）、颜色（COLOR）、重量（WEIGHT）组成。

分布式数据库系统实验五_oracle_03

insert into P values('P1','螺母','红',12);
insert into P values('P2','螺丝','绿',17);
insert into P values('P3','螺丝刀','蓝',14);
insert into P values('P4','螺丝刀','红',14);
insert into P values('P5','凸轮','蓝',40);
insert into P values('P6','齿轮','红',30);
insert into P values('P7','CPU','红',30);

分布式数据库系统实验五_数据库_04

Step3:在节点1上，新建到生产部门的数据链。

基本表SPJ

由供应商代码（SNO）、零件代码（PNO）、工程项目代码（JNO）、供应数量（QTY）组成，表示某供应商供应某种零件的数量为QTY。

分布式数据库系统实验五_外键_05

CREATE TABLE SPJ(
SNO VARCHAR2(4) NOT NULL,
PNO VARCHAR2(20) NOT NULL,
JNO VARCHAR2(10) NOT NULL,
QTY Number(8),
PRIMARY KEY(SNO,PNO,JNO),
FOREIGN KEY (SNO) REFERENCES S(SNO),
FOREIGN KEY (PNO) REFERENCES P(PNO));

insert into SPJ values('S1','P1','J1',200);
insert into SPJ values('S1','P1','J3',100);
insert into SPJ values('S1','P1','J4',700);
insert into SPJ values('S1','P2','J2',100);
insert into SPJ values('S2','P3','J1',400);
insert into SPJ values('S2','P3','J2',200);
insert into SPJ values('S2','P3','J4',500);
insert into SPJ values('S2','P3','J5',400);
insert into SPJ values('S2','P5','J1',400);
insert into SPJ values('S2','P5','J2',100);
insert into SPJ values('S3','P1','J1',200);
insert into SPJ values('S3','P3','J1',200);
insert into SPJ values('S4','P5','J1',100);
insert into SPJ values('S4','P6','J3',300);
insert into SPJ values('S4','P6','J4',200);
insert into SPJ values('S5','P2','J4',100);
insert into SPJ values('S5','P3','J1',200);
insert into SPJ values('S5','P6','J2',200);
insert into SPJ values('S5','P6','J4',500);
insert into SPJ values('S1','P1','J2',5000);
insert into SPJ values('S1','P7','J1',5000);
insert into SPJ values('S2','P7','J2',3000);
insert into SPJ values('S3','P7','J3',2000);
insert into SPJ values('S4','P7','J4',1000);
insert into SPJ values('S5','P7','J5',500);
insert into SPJ values('S3','P7','J6',1);
insert into SPJ values('S1','P7','J7',200);
insert into SPJ values('S2','P7','J8',100);
insert into SPJ values('S2','P7','J9',300);
insert into SPJ values('S1','P7','J10',20);
insert into SPJ values('S2','P7','J11',10);
insert into SPJ values('S3','P7','J12',50);
insert into SPJ values('S3','P7','J13',10);
insert into SPJ values('S1','P7','J14',20);
insert into SPJ values('S2','P7','J15',10);

分布式数据库系统实验五_应用场景_06

（2）发起查询请求如下：总部用户希望查找产品名字为“CPU”的供货商的信息和对应的供货信息。

(内连接)

SELECT P.PNAME,S.SNO,S.SNAME,S.STATUS,S.CITY,SPJ.QTY
FROM P@link1,SPJ,S
WHERE P.PNAME='CPU' AND 
SPJ.PNO = P.PNO AND S.SNO = SPJ.SNO;

分布式数据库系统实验五_外键_07

分布式数据库系统实验五_oracle_08

可以看出，建立外键后和不用/*+hint*/ 提示效果一样，即CBO优化器在建立外键的情况下，默认采用合并排序的方式来进行查询操作。

SELECT P.PNAME,S.SNO,S.SNAME,S.STATUS,S.CITY,SPJ.QTY
FROM P@link1
INNER JOIN SPJ ON SPJ.PNO = P.PNO
INNER JOIN S ON S.SNO = SPJ.SNO
WHERE P.PNAME='CPU';

分布式数据库系统实验五_嵌套循环_09

（3）以不同的优化策略实施查询

Hint是Oracle数据库提供的一种机制用来告诉优化器按照hint告诉它的方式生成执行计划，是很多DBA优化中常用的一个手段。
Oracle引入优化器是因为：基于代价的优化器，在绝大多数情况下会选择正确的优化器，减轻DBA的负担。但是有时候会选择效率很差的执行计划，使某个语句变得很慢，此时就需要DBA认为干预，告诉优化器使用指定的存取路径或者连接类型生成执行计划，从而使语句高效地运行。

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

SELECT P.PNAME,S.SNO,S.SNAME,S.STATUS,S.CITY,SPJ.QTY
FROM P@link1,SPJ,S
WHERE P.PNAME='CPU' AND 
SPJ.PNO = P.PNO AND S.SNO = SPJ.SNO;

分布式数据库系统实验五_应用场景_10


分布式数据库系统实验五_数据库_11

可以看出，建立外键后和不用/*+hint*/提示效果一样，即CBO优化器在建立外键的情况下，默认采用合并排序的方式来进行查询操作。

Step2:以/+hint/的方式提示优化器采取嵌套循环的连接策略

SELECT /*+ use_nl(t1,t2) */PNAME,t1.SNO,t1.SNAME,t1.STATUS,t1.CITY,SPJ.QTY
FROM P@link1 t2,SPJ,S t1
WHERE t2.PNAME='CPU' AND 
SPJ.PNO = t2.PNO AND t1.SNO = SPJ.SNO;

分布式数据库系统实验五_应用场景_12


分布式数据库系统实验五_嵌套循环_13

可以看出和如果没有外键时，不用/+hint/提示效果一样，即CBO优化器默认采用嵌套循环的方式来进行查询操作。

USE_NL：使用该提示引导优化器按照嵌套循环连接方式执行表连接。它只是指出表连接的方式，对于表连接顺序不会有任何影响。

注意：order是根据from表顺序来决定驱动表，而不是use_nl(t1,t2)中表的先后顺序。

使用order来决定先后驱动表。

SELECT /*+ ordered use_nl(t1,t2) */PNAME, t1.SNO, t1.SNAME,t1.STATUS,t1.CITY,SPJ.QTY
FROM P @link1 t2,SPJ,S t1
WHERE t2.PNAME='CPU' AND 
SPJ.PNO = t2.PNO AND t1.SNO = SPJ.SNO;

分布式数据库系统实验五_嵌套循环_14


分布式数据库系统实验五_外键_15

Step3:以/+hint/的方式提示优化器采取排序合并的连接策略

SELECT /*+ use_merge(t1,t2) */PNAME, t1.SNO, t1.SNAME,t1.STATUS,t1.CITY,SPJ.QTY
FROM P @link1 t2,SPJ,S t1
WHERE t2.PNAME='CPU' AND 
SPJ.PNO = t2.PNO AND t1.SNO = SPJ.SNO;

分布式数据库系统实验五_外键_16


分布式数据库系统实验五_嵌套循环_17

可以看出，建立外键后和不用/*+hint*/提示效果一样，即CBO优化器在建立外键的情况下，默认采用合并排序的方式来进行查询操作。
USE_MERGE：引导优化器按照排序合并连接方式执行连接。在有必要的情况下，推荐将该提示与ORDERED提示一起使用。提示通常用于获得查询的最佳吞吐量。假设将两个表连接在一起，从每个表返回的行集将被排序，然后再被合并(也就是合并排序)，从而组成最终的结果集。由于每个行先被排序之后才进行合并，所以在给定查询中检索所有行时，速度将会最快。如果需要以最快速度返回第一行，就应该使用USE_NL提示。

（4）发起相关查询实例，利用Oracle的提示(hint)功能给查询语句指定查询计划。

SELECT P.PNAME,S.SNO,S.SNAME,S.STATUS,S.CITY,SPJ.QTY
FROM (
		SELECT PNAME,PNO,COLOR,WT
		FROM P@link1) P,S,SPJ
WHERE SPJ.PNO = P.PNO AND S.SNO = SPJ.SNO;

分布式数据库系统实验五_oracle_18

内连视图，执行计划为HASH索引。

分布式数据库系统实验五_数据库_19

Step1:利用NO_Merge提示

SELECT /*+ no_merge(P) */PNAME,S.SNO,S.SNAME,S.STATUS,S.CITY,SPJ.QTY
FROM (
		SELECT PNAME,PNO,COLOR,WT
		FROM P@link1) P,S,SPJ
WHERE SPJ.PNO = P.PNO AND S.SNO = SPJ.SNO;

分布式数据库系统实验五_外键_20


分布式数据库系统实验五_oracle_21

Step2:利用DRIVING_SITE提示

SELECT /*+ ordered use_nl(t2) */PNAME, t1.SNO, t1.SNAME,t1.STATUS,t1.CITY,SPJ.QTY
FROM P @link1 t2,SPJ,S t1
WHERE t2.PNAME='CPU' AND 
SPJ.PNO = t2.PNO AND t1.SNO = SPJ.SNO;

分布式数据库系统实验五_外键_22


分布式数据库系统实验五_外键_23

优化器策略算法实现，时间复杂度不会变，但是磁盘I/O降下来了。

  1. 前一种算法种需要访问N+N*M次磁盘，因为每读取一条记录就要访问一次磁盘。
  2. 后一种算法，磁盘的访问次数变成了number_of_bunches_for(outer)+ number_of_ bunches_for(outer)* number_of_ bunches_for(inner)。

相关报错与解决：

报错1：

分布式数据库系统实验五_外键_24

ORA-00900：无效SQL语句
报错原因：Creart拼写错误。之后如果
解决：以后SQL报错可以看变量名及相关操作有没有变色，如这个就是黑色，其他操作都是蓝色。

报错2：

分布式数据库系统实验五_外键_25

解决：设置表别名t1,t2后，所有查询有关表名都需要用别名。