现在的位置: 首页 > 综合 > 正文

提高商业智能环境中 DB2 查询性能

2013年10月09日 ⁄ 综合 ⁄ 共 19583字 ⁄ 字号 小 中 大 ⁄ 评论关闭
本文来自:http://www.ibm.com/developerworks/cn/db2/library/techarticles/dm-0703kapoor/index.html


高效地运行大型查询，是商业智能环境中的顶级性能挑战。学习在这种环境中提高 IBM® DB2® 数据服务器查询性能的技巧。逐步了解各种不同的方法，然后在自己的系统上进行试验。将每种方法应用于一条 SQL 语句，并使用 db2batch 工具评测性能。

简介

本文主要讨论可以使决策支持系统（DSS）中的大型查询高效地执行的一些方法。这些查询通常都是访问较多数据的单纯 select 查询。下面是我们要讨论的一些方法：

  1. 建立适当的参照完整性约束
  2. 使用物化查询表（MQT）将表复制到其它数据库分区，以允许非分区键列上的合并连接
  3. 使用多维集群（MDC）
  4. 使用表分区（DB2® 9 的新功能）
  5. 结合使用表分区和多维集群
  6. 使用 MQT 预先计算聚合结果

本 文中的例子针对 Windows 平台上运行的 DB2 9。但是，其中的概念和信息对于任何平台都是有用的。由于大多数商业智能（BI）环境都使用 DB2 Database Partitioning Feature（DPF，DB2 数据库分区特性），我们的例子也使用 DPF 将数据划分到多个物理和逻辑分区之中。

数据库布局和设置

本节描述用于在我们的系统上执行测试的数据库的物理和逻辑布局。

星型模式布局

本文使用如下所示的星型模式：


清单 1. 星型模式

                		
                                  PRODUCT_DIM             DATE_DIM
                                            /            /
                                             /          /
                                              SALES_FACT
                                                  |
                                                  |
		                             STORE_DIM


其中的表的定义如下：

表名 类型 列名 数据类型 列描述
SALES_FACT FACT TABLE DATE_ID DATE 产品售出日期
    PRODUCT_ID INT 所购买产品的标识符
    STORE_ID INT 出售产品的商店的标识符
    QUANTITY INT 这次交易中售出产品的数量
    PRICE INT 产品购买价格。[为了简单起见，该字段为整型，但是使用小数型更符合实际]
    TRANSACTION_DETAILS CHAR(100) 关于此次交易的描述/详细信息
DATE_DIM DIMENSION TABLE DATE_ID NOT NULL DATE 惟一标识符
    MONTH INT 日期记录所属的月份
    QUARTER INT 日期记录所属的季度（第 1、第 2、第 3 或第 4 季度）
    YEAR INT 日期记录所属的年份
PRODUCT_DIM DIMENSION TABLE PRODUCT_ID NOT NULL INT 产品惟一标识符
    PRODUCT_DESC CHAR(20) 对产品的描述
    MODEL CHAR(200) 产品型号
    MAKE CHAR(50) 产品的质地
STORE_DIM DIMENSION TABLE STORE_ID NOT NULL INT 商店惟一标识符
    LOCATION CHAR(15) 商店位置
    DISTRICT CHAR(15) 商店所属街区
    REGION CHAR(15) 商店所属区域

事 实表 SALES_FACT 包含 2006 年的总体销售信息。它包括产品售出日期、产品 ID、销售该产品的商店的 ID、售出的特定产品的数量，以及产品的价格。事实表中还添加了 TRANSACTION_DETAILS 列，以便在从事实表中访问数据时生成更多的 I/O。

维度表 DATE_DIM 包含商店开放期间的惟一的日期和相应的月份、季度和年份信息。

维度表 PRODUCT_DIM 包含公司所销售的不同产品。每种产品有一个惟一的产品 ID 和一个产品描述、型号以及质地。

维度表 STORE_DIM 包含不同的商店 ID 和商店的位置、所属街区以及所属区域等信息。

数据库分区信息

数据库分区组名 数据库分区数
FACT_GROUP 0,1,2,3
DATE_GROUP 1
PRODUCT_GROUP 2
STORE_GROUP 3

各表都位于它自己的分区组中。3 个维度表都比较小，所以它们位于一个数据库分区上。而事实表则跨 4 个分区。

表空间信息

表空间名 数据库分区组 表
FACT_SMS FACT_GROUP SALES_FACT
DATE_SMS DATE_GROUP DATE_DIM
PRODUCT_SMS PRODUCT_GROUP PRODUCT_DIM
STORE_SMS STORE_GROUP STORE_DIM

各表都位于自己的表空间中。还有一种常见的方法是将这 3 个维度表放在同一个表空间中。

缓冲池信息

本文中的测试所使用的默认缓冲池是 IBMDEFAULTBP，该缓冲池由 1,000 个 4K 的页面组成。在本文的测试中，所有表空间共享这个缓冲池。在通常的 BI 环境中，会创建不同的缓冲池。

主查询

下面的查询用于测试本文中讨论的各种不同的方法。该查询执行一个向外连接，比较二月份和十一月份 10 家商店的销售信息。


清单 2. 主查询 [Query1.sql]

                
WITH TMP1 (MONTH_1,STORE,REGION,DISTRICT,AMOUNT_1) AS
( SELECT
     D.MONTH AS MONTH, 
     S.STORE_ID AS STORE_ID,
     S.DISTRICT AS DISTRICT,
     S.REGION AS REGION,
     SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT

  FROM
     SKAPOOR.SALES_FACT F1,
     SKAPOOR.DATE_DIM D,
     SKAPOOR.PRODUCT_DIM P,
     SKAPOOR.STORE_DIM S

  WHERE
     P.MODEL LIKE '%model%' AND
     F1.DATE_ID=D.DATE_ID AND
     F1.PRODUCT_ID=P.PRODUCT_ID AND
     F1.STORE_ID=S.STORE_ID AND
     F1.DATE_ID BETWEEN '2006-01-01' AND '2006-01-31' AND
     F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND		
     D.MONTH = 1 

  GROUP BY
     S.STORE_ID,S.DISTRICT,S.REGION,D.MONTH) ,

TMP2 (MONTH_11,STORE,REGION,DISTRICT,AMOUNT_11) AS
( SELECT 
     D1.MONTH AS MONTH,
     S1.STORE_ID AS STORE_ID,
     S1.DISTRICT AS DISTRICT,
     S1.REGION AS REGION,
     SUM(F2.QUANTITY * F2.PRICE) AS AMOUNT

  FROM
     SKAPOOR.SALES_FACT F2,
     SKAPOOR.DATE_DIM D1,
     SKAPOOR.PRODUCT_DIM P1,
     SKAPOOR.STORE_DIM S1

  WHERE
     P1.MODEL LIKE '%model%' AND
     F2.DATE_ID=D1.DATE_ID AND
     F2.PRODUCT_ID=P1.PRODUCT_ID AND
     F2.STORE_ID=S1.STORE_ID AND
     F2.DATE_ID BETWEEN '2006-11-01' AND '2006-11-30' AND
     F2.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND		
     D1.MONTH=11

  GROUP BY
     S1.STORE_ID,S1.DISTRICT,S1.REGION,D1.MONTH) 

SELECT 
     A.*, 
     B.*
FROM
     TMP1 A LEFT OUTER JOIN TMP2 B ON
       (A.STORE=B.STORE AND A.REGION=B.REGION AND A.DISTRICT=B.DISTRICT)
ORDER BY A.AMOUNT_1 DESC, B.AMOUNT_11 DESC;


环境设置

本文的测试是使用以下环境执行的：


清单 3. db2level

                
DB2 9 Enterprise Edition:

DB21085I  Instance "DB2" uses "32" bits and DB2 code release "SQL09010" with
level identifier "02010107".
Informational tokens are "DB2 9.1.0.356", "s060629", "NT32", and Fix Pack "0".
Product is installed at "C:/PROGRA~1/IBM/SQLLIB" with DB2 Copy Name "DB2COPY1".

清单 4. 操作系统

                
System: WIN32_NT SKAPOOR Service Pack 2 5.1 x86 Family 15, model 4, stepping 3

清单 5. 硬件

                
CPU: total:2 online:2 Cores per socket:1 Threading degree per core:2
Physical Memory(MB): total:2551 free:1988 available:1949
Virtual  Memory(MB): total:4950 free:6575
Swap     Memory(MB): total:2399 free:4587
1 Physical disk Size 100GB


空间需求

为了重新创建本文中描述的所有测试用例，需要高达 20Gb 的磁盘空间来存放数据和日志文件。其中将近 13Gb 的空间要分配给日志文件。我们要使用循环日志记录，分配 100 个主日志：


清单 6. 用于日志的数据库配置

                
Log file size (4KB)                         (LOGFILSIZ) = 8192
Number of primary log files                (LOGPRIMARY) = 100
Number of secondary log files               (LOGSECOND) = 150


略加修改为事实表填充数据的脚本，即可减少日志文件所需的磁盘空间。本文的后面将对此进行讨论。

设置数据库

第一步是创建一个测试数据库。

在 本文的测试中，创建了 4 个逻辑数据分区。在 etc/services 文件中，应确保有足够的端口用于创建 4 个数据分区。在我们的测试环境中，文件 C:/WINDOWS/system32/drivers/etc/services 中包含关于实例 "DB2" 的以下内容：


清单 7. services 文件的内容

                
DB2_DB2           60000/tcp
DB2_DB2_1         60001/tcp
DB2_DB2_2         60002/tcp
DB2_DB2_END       60003/tcp
DB2c_DB2          50000/tcp


为向实例添加数据库分区，可使用 DB2 CLP 执行以下命令：


清单 8. 使用 db2ncrt 命令创建数据库分区

                
db2stop 
db2ncrt /n:1 /u:username,password /i:DB2 /m:machine /p:1
db2ncrt /n:2 /u:username,password /i:DB2 /m:machine /p:2
db2ncrt /n:3 /u:username,password /i:DB2 /m:machine /p:3


其中 /u 选项所表示的用户名和密码，/m 选项所表示的计算机名，以及 /i 选项所表示的实例名应该根据您自己的环境加以修改。

创建数据库

创建数据库 DSS_DB。这里使用 D: 盘存储该数据库。请根据您自己的环境进行调整。


清单 9. 创建数据库的命令

                
db2 create database dss_db on D:/;


数据库和数据库管理器是使用下面的设置来配置的。db2_all 工具用于设置所有数据库分区上的数据库配置和数据库管理器配置。


清单 10. 更新数据库管理器配置的语句

                
db2_all update dbm cfg /
     using cpuspeed 2.282997e-007 intra_parallel NO comm_bandwidth 100.000000

db2_all update db cfg for DSS_DB /
     using locklist 2450 dft_degree 1 maxlocks 60 /
           avg_appls 1 stmtheap 16384 dft_queryopt 5


创建数据库分区组和表空间

使用以下语句创建数据库分区组和表空间。可以将这些语句复制到一个名为 STORAGE.ddl 的文件中，然后使用下面的命令执行它们：

db2 -tvf STORAGE.ddl -z storage.log

清单 11. 创建数据库分区组和表空间的语句

                
CONNECT TO DSS_DB;

--------------------------------------------------
-- DDL Statements for DATABASE PARTITION GROUPS --
--------------------------------------------------

CREATE DATABASE PARTITION GROUP "FACT_GROUP" ON DBPARTITIONNUMS 
		(0,
		 1,
		 2,
		 3);

CREATE DATABASE PARTITION GROUP "DATE_GROUP" ON DBPARTITIONNUMS 
		(1);

CREATE DATABASE PARTITION GROUP "PRODUCT_GROUP" ON DBPARTITIONNUMS 
		(2);

CREATE DATABASE PARTITION GROUP "STORE_GROUP" ON DBPARTITIONNUMS 
		(3);

COMMIT WORK;

------------------------------------
-- DDL Statements for TABLESPACES --
------------------------------------

CREATE REGULAR TABLESPACE FACT_SMS IN DATABASE PARTITION GROUP FACT_GROUP
    PAGESIZE 4096 MANAGED BY SYSTEM 
	 USING ('d:/database/fact_tbsp0') ON DBPARTITIONNUMS (0)
	 USING ('d:/database/fact_tbsp1') ON DBPARTITIONNUMS (1)
	 USING ('d:/database/fact_tbsp2') ON DBPARTITIONNUMS (2)
	 USING ('d:/database/fact_tbsp3') ON DBPARTITIONNUMS (3)
	 EXTENTSIZE 32
	 PREFETCHSIZE AUTOMATIC
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING  
	 DROPPED TABLE RECOVERY ON;

CREATE REGULAR TABLESPACE DATE_SMS IN DATABASE PARTITION GROUP
DATE_GROUP PAGESIZE 4096 MANAGED BY SYSTEM 
	USING ('d:/database/date_group') ON DBPARTITIONNUMS (1)
	EXTENTSIZE 32
	 PREFETCHSIZE AUTOMATIC
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING  
	 DROPPED TABLE RECOVERY ON;

CREATE REGULAR TABLESPACE PRODUCT_SMS IN DATABASE PARTITION GROUP
PRODUCT_GROUP PAGESIZE 4096 MANAGED BY SYSTEM 
	USING ('d:/database/product_group') ON DBPARTITIONNUMS (2)
	EXTENTSIZE 32
	 PREFETCHSIZE AUTOMATIC
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING  
	 DROPPED TABLE RECOVERY ON;

CREATE REGULAR TABLESPACE STORE_SMS IN DATABASE PARTITION GROUP
STORE_GROUP PAGESIZE 4096 MANAGED BY SYSTEM 
	USING ('d:/database/store_group') ON DBPARTITIONNUMS (3)
	EXTENTSIZE 32
	 PREFETCHSIZE AUTOMATIC
	 BUFFERPOOL IBMDEFAULTBP
	 OVERHEAD 7.500000
	 TRANSFERRATE 0.060000 
	 NO FILE SYSTEM CACHING  
	 DROPPED TABLE RECOVERY ON;

COMMIT WORK;

-- Mimic tablespace

ALTER TABLESPACE SYSCATSPACE
      PREFETCHSIZE AUTOMATIC
      OVERHEAD 7.500000
      TRANSFERRATE 0.060000;


ALTER TABLESPACE TEMPSPACE1
      PREFETCHSIZE AUTOMATIC
      OVERHEAD 7.500000
      TRANSFERRATE 0.060000;


ALTER TABLESPACE USERSPACE1
      PREFETCHSIZE AUTOMATIC
      OVERHEAD 7.500000
      TRANSFERRATE 0.060000;

COMMIT WORK;

------------------------------------------------
-- Update the bufferpool to use 1000 4K pages --
------------------------------------------------

ALTER BUFFERPOOL IBMDEFAULTBP SIZE 1000;

COMMIT WORK;
CONNECT RESET;


注意：表空间被定义为 "NO FILE SYSTEM CACHING"，以避免文件系统缓存歪曲测试各种方法时得到的评测结果。

使用 db2batch 工具评测性能

db2batch 程序用于运行 清单 2 中的主查询。为了使用 db2batch 命令运行该查询，需要将查询保存在一个以分号结尾的文件中，并使用以下选项，使 db2batch 工具查看计时情况：


清单 12. 使用 db2batch 评测查询的性能

                
				db2batch -d <dbname> -f <input_file> -i <elapsed_time> -iso <isolation level>
				-o p <perf_detail> o <optlevel> r <rows_out> -r <result_file>


其中 <dbname> 是数据库名称，<input_file> 是以分号结尾、包含查询的文件。

  • -iso <isolation level>:
    在 我们的测试中，默认隔离级别是 CS，但是默认情况下 db2batch 工具使用隔离级别 RR。如果使用隔离级别 RR 执行一个查询，那么使用隔离级别 CS 创建的 MQT 不会被考虑。为了解决这个问题，可以在 db2batch 命令中使用 -iso 选项和隔离级别 CS，以便查询选择 MQT。而且，应用程序可使用默认的 CS 隔离级别，不带 -iso 选项运行 db2batch 会导致它使用 RR 隔离级别，并可能导致锁争用。
  • -o - options options:
    • p <perf_detail>: 性能详细信息。返回数据库管理器、数据库、应用程序和语句的快照（只有在自动提交关闭，且处理的是单个语句，而非语句块时，才返回语句快照）。另外还返回 缓冲池、表空间和 FCM的快照（只有在多数据库分区环境中才会返回 FCM 快照）。 对于例子 p 5，我们使用最详细的输出，但是也可以使用不同级别的性能输出。
    • o <optlevel>: 查询优化级别。（本文使用优化级别 5，这里不需要显式地指定这个优化级别，因为它是数据库的默认优化级别，如 清单 10 所示。）
    • r <rows_out>: 所获取且将发送到输出的行数。我们的例子 r 0 不发送行。
  • -r <result_file>: 结果文件。在我们的例子中，results.txt 是输出文件名，db2batch 将结果输出到该文件中。


在本文中，我们使用：

db2batch -d DSS_DB -f QUERY1.SQL -i complete -iso CS -o p 5 o 5 r 0 -r <output filename>


提高查询性能的方法

在本节中，让我们逐步了解用于提高 清单 2 中描述的查询的性能的各种不同方法。在讨论任何方法之前，必须创建基本的事实表和维度表。

步骤 A：创建好表空间之后，就要创建事实表和维度表。可以将 SKAPOOR 改为符合您自己环境的模式名。这样做时，务必更新 清单 2 中的查询，以反映适当的模式名。可以将下面的语句复制到一个名为 TEST1.ddl 的文件中，然后使用以下命令来执行该文件：

			
db2 -tvf TEST1.ddl -z test1.log

清单 13. TEST1.ddl 的内容

                
CONNECT TO DSS_DB;

---------------------------------------------------
-- DDL Statements for table "SKAPOOR "."SALES_FACT"
---------------------------------------------------


CREATE TABLE "SKAPOOR "."SALES_FACT"  (
		  "DATE_ID" DATE , 
		  "PRODUCT_ID" INTEGER , 
		  "STORE_ID" INTEGER , 
		  "QUANTITY" INTEGER , 
		  "PRICE" INTEGER , 
		  "TRANSACTION_DETAILS" CHAR(100) )   
		 DISTRIBUTE BY HASH("DATE_ID")   
		   IN "FACT_SMS" ;

-------------------------------------------------
-- DDL Statements for table "SKAPOOR "."DATE_DIM"
-------------------------------------------------


CREATE TABLE "SKAPOOR "."DATE_DIM"  (
		  "DATE_ID" DATE NOT NULL , 
		  "MONTH" INTEGER , 
		  "QUARTER" INTEGER , 
		  "YEAR" INTEGER )   
		 IN "DATE_SMS" ; 


-- DDL Statements for primary key on Table "SKAPOOR "."DATE_DIM"
-- DATE_ID is the unique identifier
ALTER TABLE "SKAPOOR "."DATE_DIM" 
	ADD PRIMARY KEY
		("DATE_ID");



----------------------------------------------------
-- DDL Statements for table "SKAPOOR "."PRODUCT_DIM"
----------------------------------------------------


CREATE TABLE "SKAPOOR "."PRODUCT_DIM"  (
		  "PRODUCT_ID" INTEGER NOT NULL , 
		  "PRODUCT_DESC" CHAR(20) , 
		  "MODEL" CHAR(10) , 
		  "MAKE" CHAR(10) )   
		 IN "PRODUCT_SMS" ; 


-- DDL Statements for primary key on Table "SKAPOOR "."PRODUCT_DIM"
-- PRODUCT_ID is the unique identifier
ALTER TABLE "SKAPOOR "."PRODUCT_DIM" 
	ADD PRIMARY KEY
		("PRODUCT_ID");



--------------------------------------------------
-- DDL Statements for table "SKAPOOR "."STORE_DIM"
--------------------------------------------------


CREATE TABLE "SKAPOOR "."STORE_DIM"  (
		  "STORE_ID" INTEGER NOT NULL , 
		  "LOCATION" CHAR(15) , 
		  "DISTRICT" CHAR(15) , 
		  "REGION" CHAR(15) )   
		 IN "STORE_SMS" ; 


-- DDL Statements for primary key on Table "SKAPOOR "."STORE_DIM"
-- STORE_ID is the unique identifier
ALTER TABLE "SKAPOOR "."STORE_DIM" 
	ADD PRIMARY KEY
		("STORE_ID");


COMMIT WORK;

CONNECT RESET;


步骤 B：创建好表后，将数据插入到三个维度表中，并根据您自己的环境调整模式：


清单 14. 填充 DATE_DIM 表

                
db2 -td@ -vf date_insert.txt -z date_insert.log

清单 15. 填充 PRODUCT_DIM 表

                
db2 -td@ -vf product_insert.txt -z product_insert.log

清单 16. 填充 STORE_DIM 表

                
db2 -td@ -vf store_insert.txt -z store_insert.log


这三个文件的内容是：

DATE_DIM 表被填入 2006 年所有 365 天的值。


清单 17. date_insert.txt 的内容

                
connect to dss_db@

begin atomic
  declare cnt INT default 1;
  declare dat DATE default '01/01/2006';
  declare yer INT default 2006;
  declare quart INT default 1;

while (cnt <= 365) do    
    if (int(dat + cnt DAYS)/100) between 200601 and 200603 then
           set quart=1;
    elseif (int(dat + cnt DAYS)/100) between 200604 and 200606 then
           set quart=2;
    elseif (int(dat + cnt DAYS)/100) between 200607 and 200609 then
           set quart=3;
    elseif (int(dat + cnt DAYS)/100) between 200610 and 200612 then
           set quart=4;    
    end if;

    insert into SKAPOOR.DATE_DIM values (
	dat + cnt DAYS,
	(int(dat + cnt DAYS)/100) - 200600,
	quart,
	yer
    );

    set cnt=cnt+1;
end while;

end@

connect reset@


PRODUCT_DIM 表被填入 60,000 种产品。


清单 18. product_insert.txt 的内容

                
connect to dss_db@

drop sequence seq1@
drop sequence seq2@

create sequence seq1 as integer start with 1 increment by 1@
create sequence seq2 as integer start with 1 increment by 1@

begin atomic
   declare cnt INT default 1;

   while (cnt < 60001) do
	insert into SKAPOOR.PRODUCT_DIM values (
	    nextval for SEQ2,
	    'product desc' concat char(nextval for SEQ1),
            'model ' concat char(integer(rand()*1000)),
	    'maker ' concat char(integer(rand()*500))
	);
 	set cnt=cnt+1;
    	end while;
end@

drop sequence seq1@
drop sequence seq2@

connect reset@


STORE_DIM 表被填入 201 家商店。


清单 19. store_insert.txt 的内容

                
connect to dss_db@

drop sequence seq2@

create sequence seq2 as integer start with 0 increment by 1@

begin atomic
    declare cnt INT default 1;

    while (cnt < 202) do
       insert into SKAPOOR.STORE_DIM values (
	    nextval for SEQ2,
	    'location' concat char(integer(rand()*500)),
	    'district' concat char(integer(rand()*10)),
	    'region' concat char(integer(rand()*5))
       );
       set cnt=cnt+1;

    end while;
end@

drop sequence seq2@

connect reset@


步骤 C：将数据插入到 SALES_FACT 表中。根据您自己的环境调整模式。在我们的测试环境中，将数据插入到事实表花了约一个半小时的时间。


清单 20. 填充 SALES_FACT 表

                
db2 -td@ -vf sales_fact_insert.ddl -z sales_fact_insert.log

清单 21. sales_fact_insert.ddl 的内容

                
connect to dss_db@

VALUES (CURRENT TIMESTAMP)@

begin atomic

   declare cnt INT default 1;
   declare cnt1 INT default 1;
   declare dat DATE default '01/01/2006';

   while (cnt <= 365) do    

    INSERT INTO SKAPOOR.SALES_FACT
    with v(DATE_ID, PRODUCT_ID, STORE_ID, QUANTITY, PRICE, TRANSACTION_DETAILS, U_ID) as
         (values(dat + CNT1 DAYS, 1, 1, 1, 1, '', 1) union all
          select DATE_ID, int(rand()*59999) + 1, int(rand()*200),  int(rand()*50) + 1,
          INT(RAND()*200 + 1), RESERVE, U_ID + 1
          from   v
          where  U_ID < 60000)
     select date_id, product_id, store_id, quantity, price, transaction_details from v;

     set cnt1 = cnt1 + 1;
     set cnt  = cnt + 1;
   end while;

end@

VALUES (CURRENT TIMESTAMP)@

connect reset@


注意：在 清单 21 中，SALES_FACT 表是在一次事务处理中填充的，这需要大量的磁盘空间来作日志记录。为了降低日志记录的影响，可以创建一个存储过程，并分步提交插入内容：


清单 22. 填充 SALES_FACT 表的另一种方法

                
connect to dss_db@

VALUES (CURRENT TIMESTAMP)@

-- Create a procedure to populate the SALES_FACT table
-- committing the inserts in stages to reduce the impact
-- of logging

create procedure salesFactPopulate()
specific salesFactPopulate
language sql

begin

   declare cnt INT default 1;
   declare cnt1 INT default 1;
   declare dat DATE default '01/01/2006';

   while (cnt <= 365) do    

    INSERT INTO SKAPOOR.SALES_FACT
    with v(DATE_ID, PRODUCT_ID, STORE_ID, QUANTITY, PRICE, TRANSACTION_DETAILS, U_ID) as
    (
       values(dat + CNT1 DAYS, 1, 1, 1, 1, '', 1)
      union all
       select DATE_ID, int(rand()*59999) + 1, int(rand()*200),  int(rand()*50) + 1,
              INT(RAND()*200 + 1), RESERVE, U_ID + 1
         from v
        where U_ID < 60000
    )
    select date_id, product_id, store_id, quantity, price, TRANSACTION_DETAILS from v;

    commit work;

    set cnt1 = cnt1 + 1;
    set cnt=cnt+1;

   end while;

end@

-- populate the SALES_FACT table
invoke salesFactPopulate@

VALUES (CURRENT TIMESTAMP)@

connect reset@


步骤 D：为了理解各种不同的方法对所选查询访问计划的有怎样的影响，我们需要解释（Explain）查询，以查看 DB2 查询优化器选择的访问计划。为此，可使用 EXPLAIN 工具，这要求存在 EXPLAIN 表。为了创建 EXPLAIN 表，执行以下步骤：

  1. 进入 sqllib/misc 目录所在的位置。
    在我们的测试环境中，这个位置为 "C:/Program Files/IBM/SQLLIB/MISC"。
  2. 执行 db2 connect to dss_db。
  3. 执行 db2 -tvf EXPLAIN .DDL。

方法 1：在事实表与三个维度表之间定义适当的参照完整性约束

在 DB2 中，可以定义主键和外键约束，以允许数据库管理器对数据实施参照完整性约束。外键等参照约束还有助于提高性能。例如，如果修改 清单 2 中的查询中的子表达式 TMP1，去掉 PRODUCT_DIM 表上的本地谓词，那么，如果在 SALES_FACT.PRODUCT_ID 上创建一个外键约束，则优化器会消除 SALES_FACT 和 PRODUCT_DIM 之间的连接。如果创建了外键约束，则那样的连接被认为是无损的（lossless）， 可以从查询中移除，因为查询需要从 PRODUCT_DIM 中读取的数据在 SALES_FACT 表中都有，在 PRODUCT_DIM 与 SALES_FACT 的连接中，只引用到 PRODUCT_DIM 的主键，而没有引用 PRODUCT_DIM 的其它列。

在 星型模式布局 小节中描述的星型模式中，维度中存在的每个 DATE_ID、PRODUCT_ID 和 STORE_ID 在事实表中也必须存在。每个 ID 在维度表中都是惟一的，由为每个维度表创建的主键约束标识。因此，事实表保存产品被售出时的历史数据（定量）。下面的表描述了在这种模式中应该创建的主键 和外键。维度中的每个惟一性 ID 在事实表中都有一个相应的外键约束。

表 列 PK/FK 目标表（列）
DATE_DIM DATE_ID PK 无
PRODUCT_DIM PRODUCT_ID PK 无
STORE_DIM STORE_ID PK 无
SALES_FACT DATE_ID FK DATE_DIM (DATE_ID)
SALES_FACT PRODUCT_ID FK PRODUCT_DIM (PRODUCT_ID)
SALES_FACT STORE_ID FK STORE_DIM (STORE_ID)

步骤 1A：对事实表执行 ALTER 操作，创建它与维度表之间的适当的 FK 关系。通过上面的表查看事实表与维度表之间的关系。再创建 SALES_FACT 列（DATE_ID,STORE_ID）上的一个索引，以便与 方法 3 中描述的 MDC 方法进行比较，方法 3 使用 (DATE_ID,STORE_ID) 上的一个块索引。


清单 23. 在 SALES_FACT 表中创建外键约束和索引

                
db2 -tvf alter_sales_fact.txt -z alter_sales_fact.log

清单 24.alter_sales_fact.txt 文件的内容

                
CONNECT TO DSS_DB;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY
(DATE_ID) REFERENCES DATE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT STORE_DIM_FK FOREIGN KEY
(STORE_ID) REFERENCES STORE_DIM;
ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT PRODUCT_DIM_FK FOREIGN KEY
(PRODUCT_ID) REFERENCES PRODUCT_DIM;
CREATE INDEX SKAPOOR.IND_DATE_STORE ON SKAPOOR.SALES_FACT (DATE_ID, STORE_ID);
CONNECT RESET;


步骤 1B：收集关于所有表的统计信息：

优化器根据统计信息适当地计算备选查询执行计划（QEP）的成本，并选择最佳计划。在继续下一步骤之前，我们需要收集一些统计信息。


清单 25. 收集关于所有表的统计信息

                
db2 -tvf runstats.ddl -z runstats.log

清单 26. runstats.ddl 的内容

                
CONNECT TO DSS_DB;
RUNSTATS ON TABLE SKAPOOR.DATE_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.STORE_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.PRODUCT_DIM WITH DISTRIBUTION AND INDEXES ALL;
RUNSTATS ON TABLE SKAPOOR.SALES_FACT WITH DISTRIBUTION AND INDEXES ALL;
CONNECT RESET;


创建了外键之后，可以看看 DB2 优化器如何利用参照完整性来消除连接。

步骤 1C：解释查询：


清单 27. 含无损连接的查询

                
SELECT
		D.MONTH AS MONTH, 
		S.STORE_ID AS STORE_ID,
		S.DISTRICT AS DISTRICT,
		S.REGION AS REGION,
		SUM(F1.QUANTITY * F1.PRICE) AS AMOUNT

  FROM
		SKAPOOR.SALES_FACT F1,
		SKAPOOR.DATE_DIM D,
		SKAPOOR.PRODUCT_DIM P,
		SKAPOOR.STORE_DIM S
  WHERE
		F1.DATE_ID=D.DATE_ID AND
		F1.PRODUCT_ID=P.PRODUCT_ID AND
		F1.STORE_ID=S.STORE_ID AND
		F1.DATE_ID BETWEEN '2006-01-01' AND '2006-01-31' AND
		F1.STORE_ID IN (29, 30, 42, 55, 67, 72, 82, 99, 123, 199) AND		
		D.MONTH = 1 
  GROUP BY
		S.STORE_ID,S.DISTRICT,S.REGION,D.MONTH)


下面显示了解释此查询的方法之一：

db2 connect to dss_db
db2 set current explain mode explain
db2 -tvf JOIN_ELIM_QUERY.SQL -z JOIN_ELIM_QUERY.log
db2 set current explain mode no
db2 connect reset


其中 JOIN_ELIM_QUERY.SQL 的内容只包括 清单 27 中的查询，以分号结尾。

可以使用 db2exfmt 工具查看查询执行计划：

 
db2exfmt -d dss_db -g TIC -w -1 -n % -s % -# 0 -o join_elim.txt


输出在 join_elim.txt 中。要获得关于 db2exfmt 工具的详细信息，可以使用 -h 选项。

请打开 下载 小节中的 JOIN_ELIM 文件，看看查询优化器生成的一个访问计划，其中与 PRODUCT_DIM 的连接已经被消除。

可以查看 db2exfmt 输出中的 "Optimized Statement" 部分，注意 PRODUCT_DIM 表已从查询中移除。

注意：使用外键之类的参照约束时，插入、删除和更新操作可能无法正常执行。如果性能对于这些操作来说非常关键，但是连接排除优化在查询中也比较有用，那么可以将外键约束定义为纯信息型（informational） 的。这个方法后面的练习就是针对这一选项的。

步骤 1D：解释和运行整个查询。

为了解释查询，采用与步骤 1C 中相同的步骤：

db2 connect to dss_db
db2 set current explain mode explain
db2 -tvf QUERY1.SQL -z QUERY1.log
db2 set current explain mode no
db2 connect reset


其中，QUERY1.SQL 的内容只包括 清单 2 中的查询，以分号结尾。

可以使用 db2exfmt 工具查看查询执行计划：

db2exfmt -d dss_db -g TIC -w -1 -n % -s % -# 0 -o test1.txt


查询执行计划应该类似于 下载 小节中的 Test 1 所提供的查询执行计划。

为了运行查询，要使用 db2batch 工具来评测性能。在此之前，应该让 db2 实例经过一个再循环过程，以便对每种方法进行公平比较，避免其它因素影响性能（例如，后面测试的方法可能受益于之前留下的缓冲池，从而歪曲了评测结果）。

注意：在运行这些测试时，我们的测试系统是空闲的，没有其他活动在运行。

使用 db2stop force 停止 db2，再使用 db2start 重新启动它。使用 db2batch 获得所用时间的信息，如下所示：

db2batch -d DSS_DB -f QUERY1.SQL -i complete -iso CS -o p 5 o 5 r 0 -r test1.results.txt


文件 test1.results.txt 将包含编译和运行查询所用的时间，如下所示：

* Prepare Time is:       7.278206 seconds
* Execute Time is:     107.729436 seconds
* Fetch Time is:         0.000102 seconds
* Elapsed Time is:     115.007744 seconds (complete)


练习：

  1. 在 步骤 1A 中，在 SALES_FACT 表上创建了外键约束，但是，它们可能会影响插入、更新和删除操作，因为数据库管理器必须实施参照完整性。如果这些操作的性能很关键，并且参照完整性可由其 它方法来实施，那么可以创建信息型约束，以继续利用连接排除。否则，提供信息型约束会导致不正确的结果。

    信息型约束与参照约束的定义类似，只是最后加上了 not enforced 关键字，例如：

    ALTER TABLE SKAPOOR.SALES_FACT ADD CONSTRAINT DATE_DIM_FK FOREIGN KEY (DATE_ID) REFERENCES DATE_DIM NOT ENFORCED;

    接下来，为了完成该练习，还需撤销在 SALES_FACT 表上创建的外键约束，并使用信息约束重复步骤 1A 至 1D。

方法 2：复制维度表上的物化查询表

这里的测试使用的查询和表与方法 1 相同，但是该方法还重复创建维度表上的 MQT。

在方法 1 中，维度表在不同的分区中，必须在分区之间传送数据。可以使用 MQT 将维度表复制到其它分区，以支持合并连接，避免在分区之间发送数据，从而提高查询执行性能。

步骤 2A：创建重复的 MQT：

db2 -tvf replicated.ddl

清单 28. replicated.ddl 文件的内容

                
connect to dss_db;

drop table skapoor.store_dim_rep;
drop table skapoor.product_dim_rep;
drop table skapoor.date_dim_rep;

create table skapoor.store_dim_rep as (select * from skapoor.store_dim)
data initially deferred refresh deferred in FACT_SMS replicated;
create table skapoor.product_dim_rep as (select * from skapoor.product_dim)
data initially deferred refresh deferred in FACT_SMS replicated;
create table skapoor.date_dim_rep as (select * from skapoor.date_dim)
data initially deferred refresh deferred in FACT_SMS replicated;

refresh table skapoor.store_dim_rep;
refresh table skapoor.product_dim_rep;
refresh table skapoor.date_dim_rep;

create index skapoor.store_dim_id_rep on skapoor.store_dim_rep (store_id);
create index skapoor.product_dim_id_rep on skapoor.product_dim_rep (product_id);
create index skapoor.date_dim_id_rep on skapoor.date_dim_rep (date_id);

runstats on table skapoor.store_dim_rep with distribution and indexes all;
runstats on table skapoor.product_dim_rep with distribution and indexes all;
runstats on table skapoor.date_dim_rep with distribution and indexes all;

connect reset;


为 了确保可以实现这种合并，重复的维度必须与事实表位于同一数据库分区组中。为简单起见，我们使用和事实表一样的表空间，但是，只要是共用相同的数据库分区 组，也可以使用不同的表空间。而且，为了使优化器在计算不同备选访问计划的成本时，重复的表与底层表一致，重复的表应该有与底层表一样的索引，并且应该收 集相同的统计信息。由于不能在 MQT 上创建惟一的索引，所以在底层表的主键上创建常规索引。

复制维度表会 产生该表的一个额外的副本。在 DB2 9 中，新增了行压缩功能，以节省存储空间。为了减少维度表的额外副本的开销，可以对其进行压缩。当决定使用那样的技术时，建议也压缩重复的 MQT。否则，优化器可能会决定执行与底层维度表的非合并连接，因为它们被压缩过，在规模上小于重复的 MQT。

步骤 2B：更新数据库 DSS_DB 的数据库配置，将 dft_refresh_age 设置为 "ANY"，以便优化器选择重复的 MQT：


清单 29. 更新数据库配置

                
db2_all db2 update db cfg for DSS_DB using dft_refresh_age any
db2 terminate


步骤 2C：和 方法 1 中的步骤 1C 一样，生成主查询的 db2exfmt 输出。查看访问计划，看重复的 MQT 是否被访问（也就是说，是否选择了 date_dim_rep、product_dim_rep 和 store_dim_rep）。打开 下载 小节中的 Test 2，看看这个访问计划的一个例子。

在上述访问计划中，不存在方法 1 中那样的连接之间的表队列（TQ）操作符，因为优化器选择使用重复的维度表，从而允许合并连接。

步骤 2D：确认访问计划中会访问 MQT 之后，像 方法 1 中的步骤 1D 那样，使用 db2batch 工具评测性能。在运行 db2batch 之前，应确保 db2 实例经过再循环过程。然后，记录下结果。

注意：对 于该方法，要将数据库配置参数 DFT_REFRESH_AGE 设置为 ANY on all Database Partitions。如果想再次运行方法 1 中的测试，则需

返回
【上篇】
【下篇】

作者:

抱歉!评论已关闭.

返回首页

Copyright © 2013-2018 学步园  保留所有权利.
软文销售 QQ客服：2265327166 点击这里给我发消息（其他合作也可洽谈）
必威体育
必威电竞