PDF《<在此处插入图片>》

应用 Oracle 数据仓库最佳实践 会议日程 ? 硬件 ? 构建平衡配置 ? 磁盘/ASM 配置 ? 逻辑模型 ? 第三范式 ? 星型模式 ? 物理模型 ? 实施逻辑模型 ? 数据加载 ? 系统管理 ? 并行查询 ? 统计管理 ? 初始化参数 ? 负载监视 硬件 磁盘 阵列

1 磁盘 阵列

2 磁盘 阵列

3 磁盘 阵列

4 磁盘 阵列

5 磁盘 阵列

6 磁盘 阵列

7 磁盘 阵列

8 FC 交换机

1 FC 交换机

2 HBA1 HBA2 HBA1 HBA2 HBA1 HBA2 HBA1 HBA2 平衡配置 最薄弱环节 限定吞吐量 CPU 数量和速度决定 HBA 的数量 互连的容量 HBA 数量和速度决定 磁盘控制器的数量 交换机的速度和数量 控制器数量和速度决定 磁盘的数量 交换机的速度和数量 磁盘数量和速度 典型的、非均衡的客户配置示例 FC 交换机

1 FC 交换机

2 FC 交换机

1 FC 交换机

2 4x P690

32 CPU 24x 2Gb HBA 4x EMC DMX

2000 6x 最大 IO 吞吐量:25GB/秒 最大 IO 吞吐量:18GB/秒72% 容量,28% 性能降低 最大 IO 吞吐量:5 C 6GB/秒 容量的

20 -24%,76 C 80% 性能降低 数据仓库硬件配置最佳实践 ? 构建平衡硬件配置 ? 总吞吐量 = 内核数 X 100-200 MB(取决于芯片集) ? HBA 总吞吐量 = 内核总吞吐量 ? 如果内核总吞吐量 = 1.

6GB,将需要

4 个4Gb HBA ? 每个 HBA 端口使用

1 个磁盘控制器(吞吐量必须相同) ? 交换机的容量必须与 HBA 和磁盘控制器的容量相同 ? 每个控制器最多

10 个物理磁盘(使用较小的驱动器,146 或300 GB) ? 每个内核至少 4GB 内存(如果使用压缩,则8GB) ? 互连带宽应该等于 IO 带宽 (Infiniband) ? 结合使用 ASM 和RAID

1 镜像实现冗余 ? 创建两个 ASM 磁盘组(1 个用于数据,1 个用于闪回恢复区) ? 使用属性 au_size 增大分配单元(AU) 平衡系统 Sun Oracle Database Machine 数据库服务器网格

8 台服务器,每台服务器包括: ? 一个 Sun Fire? X4170,其中包括 ?2 个Intel

4 核处理器 ?72 GB RAM ?4 个146GB SAS 磁盘 ?双端口 InfiniBand 主机通道适配器 (HCA) Exadata 存储服务器网格

14 台服务器,每台服务器包括: ? 一个 Sun Fire? X4275,其中包括 ?

2 个Intel

4 核处理器 ?24GB RAM ?12 个600GB SAS 或2TB SATA 磁盘 ?双端口 InfiniBand 主机通道适配器 (HCA) ?4 x

96 GB Sun Flash PCIe Cards (384 GB total)

4 4 台台InfiniBand InfiniBand 交换机 交换机 每台交换机具有 每台交换机具有

24 24 个端口 个端口 ?IB 带宽高达 40GB/秒 逻辑模型 业务概念和关系的直观表示形式 为什么模式建模至关重要 ? 根据业务需求建模 ? 不要迷失在学术领域中 ? 忽略最终使用的物理数据库、硬件或最终用户工具 ? 两个主要数据仓库模型 ? 第三范式 (3NF) ? 通过标准化将数据冗余降至最低 ? 通常具有大量表 ? 星型模式 ? 最简单的模型 ? 多个维度表围绕一个事实表 ? 完成后,模型应该 ? 易于映射到物理数据库中的事实表和维度表 ? 清楚地显示运营系统中的信息将如何存储到数据仓库中 有关何时使用每个模型的行业最佳实践 ? 第三范式 ? 保留每个事务的详细记录而没有任何数据冗余 ? 允许对属性以及数据元素之间的所有关系进行丰富的编码 ? 用户通常需要充分了解数据才能导航 ? 星型模式 ? 简化数据模型以便于访问的维度方法 ? 下钻路径、层次结构和查询配置文件嵌入数据模型本身而不是数据中 ? 便于没有经验的用户进行导航 ? Forrester: ? 第三范式是无私的模型(中立) ? 星型模式是自私的模型(面向主题) 优化第三范式 需要

3 个P―Power, Partitioning, Parallelism ? 应该对较大的表或事实表进行分区 ? 使用组合的范围-散列分区 ? 进行范围分区以便加载和删除数据 ? 对联接列进行散列分区以便使用partition wise join ? 散列分区的数量应该是Power的 2倍(CPU 数X2) ? 应该使用并行执行 ? 不是通过一个进程完成所有工作,而是使用多个进程在较小的单元 上并发运行 ? 并行度应该是Power的 2倍 目标是并行执行的partition wise join Sales Sales 表表2008

2008 年年55月月22

22 日日2008

2008 年年55月月23

23 日日2008

2008 年年55月月24

24 日日2008

2008 年年55月月18

18 日日2008

2008 年年55月月19

19 日日2008

2008 年年55月月20

20 日日2008

2008 年年55月月21

21 日日Select sum(sales_amount) From SALES Where sales_date between to_date('

05/20/2008'

,'

MM/DD/YYYY'

) And to_date('

05/23/2008'

,'

MM/DD/YYYY'

);

问:2008 年5月20 -

22 日这个周末的总销售额是 多少? 仅访问

3 个相 关的分区 分区调整 Select sum(sales_amount) From SALES s, CUSTOMER c Where s.cust_id = c.cust_id;

两个表具有相同的并行度,并在 联接列 (cust_id) 上以相同的方式 进行分区 Sales Sales 对对2008

2008 年年55月月18

18 日进行 日进行 范围分区 范围分区 子分区 子分区

2 2 子分区 子分区

3 3 子分区 子分区

4 4 子分区 子分区

1 1 Customer Customer 对对2008

2008 年年55月月18

18 日进行 日进行 范围分区 范围分区 子分区 子分区

2 2 子分区 子分区

3 3 子分区 子分区

4 4 子分区 子分区

1 1 子分区 子分区

2 2 子分区 子分区

3 3 子分区 子分区

4 4 子分区 子分区

1 1 子分区 子分区

2 2 子分区 子分区

3 3 子分区 子分区

4 4 子分区 子分区

1 1 将大型联接分为多个较小的 联接,每个联接并行联接一 对分区 Partition Wise join Partition Wise join的执行计划 ID ID Operation Operation Name Name Pstart Pstart Pstop Pstop TQ PQ Distrib QC (RAND) HASH

128 128

128 0 SELECT STATEMENT

1 PX COORDINATOR

2 PX SEND QC (RANDOM) :TQ10001 Q1,01

3 SORT GROUP BY Q1,01

4 PX RECEIVE Q1,01

5 PX SEND HASH :TQ10000 Q1,00

6 SORT GROUP BY Q1,00

7 PX PARTITION HASH ALL

1 Q1,00

8 HASH JOIN Q1,00

9 TABLE ACCESS FULL Customers

1 Q1,00

10 TABLE ACCESS FULL Sales

1 Q1,00 联接和单一 联接和单一 PQ PQ 集上的 集上的 Partition Hash All Partition Hash All 表示 表示Partition Wise join Partition Wise join 星型模式是什么样子? ? 之所以称为星型模式是因为

图表像星星 ? 星型的中心由一个或多个事实表构成 ? 星型的各点是维度表 Select SUM(quanity_sold) total_umbrellas_sold_in_Boston From Sales s, Customers c, Products p, Times t Where s.cust_id = c.cust_id And s.prod_id = p.prod_id And s.time_id = t.time_id And c.cust_city = '

BOSTON'

And p.product = '

UMBRELLA'

And t.month = '

MAY'

And t.year = 2008;

问:2008 年5月份在波士顿 销售的雨伞总数是多少? 星型查询 优化星型模式 ? 在事实表的外键列上创建位图索引 ? 将STAR_TRANSFORMATION_ENABLED 设置为 TRUE 目标是星型转换 ? 重写或转换 SQL 的强大优化技巧 ? 分两个阶段执行查询 ? 第一阶段从事实表中检索所需的行(行集) ? 位图联接所有外键列上的位图索引 ? 第二阶段将此行集联接到维度表 ? 返回维度表的联接通过散列联接实现 星型转换细节 Select SUM(quanity_sold) From Sales s, Customers c, Products p, Times t Where s.cust_id = c.cust_id And s.prod_id = p.prod_id And s.time_id = t.time_id And c.cust_city = '

BOSTON'

And p.product = '

UMBRELLA'

And t.month = '

MAY'

And t.year = 2008;

Select SUM(quanity_sold) From Sales s Where s.cust_id IN (Select c.cust_id From Customers c Where c.cust_city = '

BOSTON'

) And s.prod_id IN (Select p.prod_id From Products p where p.product = '

UMBRELLA'

) And s.time_id IN (Select t.time_id From Times t Where t.month ='

MAY'

And t.year =2008);

第1步: Oracle 使用外键列上的位图索引重写/ 转换查询,以便仅从事实表中检索所需的行 第2步: Oracle 将事 实表中的行联接到维度表 第第11阶段 阶段 第第22阶段 阶段 星型查询的执行计划 ID ID Operation Operation Name Name Rows Rows Pstart Pstart Pstop Pstop

0 SELECT STATEMENT

1 1 SORT GROUP BY NOSORT

1 2 HASH JOIN

3 3 TABLE ACCESS FULL PRODUCTS

2 4 HASH JOIN

1 5 TABLE ACCESS FULL TIMES

1 1

1 1

1 1

22 BUFFER SORT

23 TABLE ACCESS FULL PRODUCTS

2 6 PARTITION RANGE SUBQUERY

44144 16

7 TABLE ACCESS BY LOCAL INDEX ROWID SALES

44144 16

8 BITMAP CONVERSION TO ROWIDS

9 BITMAP AND

10 BITMAP MERGE

11 BITMAP KEY ITERATION

12 BUFFER SORT

13 TABLE ACCESS FULL TIMES

1 14 BITMAP INDEX RANGE SCAN SALES_TIME_BIX

16 15 BITMAP MERGE

16 BITMAP KEY ITERATION

17 BUFFER SORT

18 TABLE ACCESS FULL CUSTOMERS

1 19 BITMAP INDEX RANGE SCAN SALES_CUST_BIX

16 20 BITMAP MERGE

21 BITMAP KEY ITERATION

24 BITMAP INDEX RANGE SCAN SALES_PROD_BIX

16 物理模型 逻辑模型的物理实现 数据仓库中的物理层蓝图 数据加载 临时存储层和更高层 数据加载最佳实践 ? 外部表 ? 允许通过 SQL、PL/SQL 将平面文件作为表访问 ? 能够 动态 进行复杂的数据转换和数据清理 ? 避免空间浪费 ? 预处理 ? 能够指定访问驱动程序将为读取数据而执行的程序 ? 指定 gunzip 以便在加载 .gzip 文件时对其进行 动态 解压缩 ? 并行直接路径 ? 绕过缓冲区缓存,通过多块异步 IO 将数据直接写入磁盘 ? 使用并行加速加载 ? 记得使用 Alter session enable parallel DML ? 范围分区 ? 实现分区交换加载 Sales Sales 表表2008

2008 年年55月月22

22 日日2008

2008 年年55月月23

23 日日2008

2008 年年55月月24

24 日日2008

2008 年年55月月18

18 日日2008

2008 年年55月月19

19 日日2008

2008 年年55月月20

20 日日2008

2008 年年55月月21

21 日日DBA 1. 为平面文件创建外部表 5. 使用表 tmp_sales 更改表 Sales 的交换分区 May_24_2008 2. 使用 CTAS 命令创建 未分区的表 TMP_SALES Tmp_sales Tmp_sales 表表Sales Sales 表表2008

2008 年年55月月22

22 日日2008

2008 年年55月月23

23 日日2008

2008 年年55月月24

24 日日2008

2008 年年55月月18

18 日日2008

2008 年年55月月19

19 日日2008

2008 年年55月月20

20 日日2008

2008 年年55月月21

21 日日Sales 表现 在包含所有 数据 3. 创建索引 4. 收集统计信息 Tmp_sales Tmp_sales 表表分区交换加载 系统管理 充分利用资源并避免 调整死亡螺旋 系统管理 保持数据仓库正常运行 ? 并行执行 ? 使用常识,仅在对性能有所帮助而非阻碍时应用并行 ? 资源管理器 ? 如........