|
使用面向数据仓库的基本数据库功能
使用面向数据仓库的基本数据库功能
在本教程中，您将使用 Oracle 数据库 10g 的基本功能查询数据仓库并改善其性能。
大约 1 个小时
本教程包括下列主题：
 将光标置于此图标上可以加载和查看本教程的所有屏幕截图。（警告：因为此操作会同时加载所有屏幕截图，所以在网速较慢时，响应时间可能会比较长。）
注：此外，您还可以在下列步骤中将光标放在每个单独的图标上，从而仅加载和查看与该步骤相关的屏幕截图。单击屏幕截图即可将其隐藏。
Oracle 数据库 10g 是面向数据仓库的领先关系数据库，也是第一个完整的商务智能平台。它不但满足了性能、可伸缩性和可管理性等基本核心要求，而且还满足了围绕 ETL 处理、数据分析 (OLAP) 和数据挖掘的其他数据相关的后端功能。
Oracle 数据库 10g Release2 是一个功能丰富并具有性能增强的后续版本，它在 Oracle 关系功能的每个层面都提供了显著的性能增强，并在提供最完整的商务智能平台方面充分发挥了 Oracle 的领先性。
返回主题列表
在开始本 OBE 的任务之前，您需要对现有销售历史模式进行一些更改。另外，您还需要一些附加对象，并为用户 SH 授予附加的系统权限。应用这些更改的 SQL 文件为 modifySH_10gR2.sql。此文件位于 /home/oracle/wkdir 文件夹中。要利用数据仓库教程的设置文件，执行下列步骤：
| 1. |
启动一个终端窗口。通过在终端会话中执行下列命令，将工作目录更改为 /home/oracle/wkdir 文件夹：
（注：本教程假设您拥有 /home/oracle/wkdir 文件夹。如果没有，则需要创建此文件夹。） cd wkdir
|
| 2. |
启动一个 SQL*Plus 会话。使用用户名和口令 sh/sh。
在该 SQL*Plus 会话中运行 modifySH_10gR2.sql 脚本。
@modifySH_10gR2.sql 输出最后的部分应与下图相同。
|
返回主题列表
在本主题中，您将了解 Oracle 面向数据仓库的高级查询执行功能，它被称为 Oracle 的星形转换，在 Oracle8.0 中首次引入。您将比较星形/雪花形环境中典型查询的各种执行计划，并讨论星形转换与其他计划相比的优势。除星型转换外的其他计划不太适合大量数据的处理，在本文中出现的顺序以性能的降序排列。
注：因为在本上机操作中使用的数据较少，所以星型转换的优势并不明显。这是由于数据基本不会超出数据库或文件系统缓存的容量，因此在很大程度上消除了其他机制的 I/O 劣势。另外，由于 init.ora 设置可能与统计数据有所不同，您计划输出中的执行计划和开销也许会与示例有一些差别。
了解 Oracle 星形查询转换的基本机制
| 1. |
在登录 SH 模式的 SQL*Plus 会话中运行 show_star1.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中。
下面显示的 ALTER SESSION 命令能够实现 Oracle 的基本星形查询转换功能，而无需使用临时表。从 Oracle8i 开始，优化器可能会在一些情况下使用临时表来进一步改善星形转换的性能。下面的示例演示了一个真正的星形转换（在 Oracle8i 中引入）与此处所显示行为间的区别。
注：NOREWRITE 提示用于避免与可能存在的现有物化视图间的冲突。另外，您也可以禁用此特定会话或整个实例的 query_rewrite。这种情况适用于所有后续语句。
STAR_TRANSFORMATION 提示用于强制在 Oracle 中使用星形转换的功能，即使对于更适用于使用其他计划的小型数据集也是如此。
@show_star1.sql
PROMPT let's disable the usage of TEMP TABLES to show simple star first
ALTER SESSION SET star_transformation_enabled=TEMP_DISABLE;
show parameters star_transformation
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT /*+ norewrite */
t.calendar_month_desc
, p.prod_subcategory
, c.cust_city
, sum(s.amount_sold) AS dollars
FROM sales s
, times t
, products p
, customers c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.cust_id = c.cust_id
AND c.cust_city='Ravensburg'
AND p.prod_category in ('Hardware','Photo')
AND t.fiscal_year in (2000,1999)
GROUP BY p.prod_subcategory, calendar_month_desc
, c.cust_city;
PROMPT show plan
set linesize 150
set pagesize 100
select * from table(dbms_xplan.display);
注：此时，您可以不考虑输出中的 PSTART 和 PSTOP 列。（它们位于 Time 列的右侧。）我们将在后面讨论这两列。
上面的计划显示了一个典型的星形查询转换。您通过扫描位图索引结构即可找到事实表中满足查询 WHERE 条件的记录，而不用扫描整个大型的 sales 事实表。
在第一个内部递归步骤中，Oracle 数据库会选择三个维表（products、times 和 channels）中满足 WHERE 条件的所有记录。您可以在 "Operation" 列中 BITMAP KEY ITERATION 这行源代码下方看到此表对这些维表的访问。
然后，Oracle 数据库将这些记录的键值作为谓词使用，它们将探测 sales 事实表本身的位图索引结构。您会看到查询（针对 customers、products 和 times 维表）的这些谓词利用位图索引结构 sales_prod_bix、sales_cust_bix 和 sales_time_bix 对销售额表相关记录进行高选择性预选。
在第二步中，为获取最终的查询结果，结果集将与所有维表重新连接。这些是在 "Operation" 列中显示的 PARTITION RANGE SUBQUERY 之后的所有操作。
|
| 2. |
在此步骤中，您会看到使用临时表转换的 Oracle 星形查询转换的基本机制。
在登录 SH 模式的 SQL*Plus 会话中运行 show_star2.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中：
@show_star2.sql
ALTER SESSION SET star_transformation_enabled=TRUE;
show parameters star_transformation
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT /*+ norewrite */
t.calendar_month_desc
, p.prod_subcategory
, c.cust_city
, sum(s.amount_sold) AS dollars
FROM sales s
, times t
, products p
, customers c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.cust_id = c.cust_id
AND c.cust_city='Ravensburg'
AND p.prod_category in ('Hardware','Photo')
--AND p.prod_category in ('Hardware')
AND t.fiscal_year in (2000,1999)
GROUP BY p.prod_subcategory, calendar_month_desc
, c.cust_city;
set linesize 140
select * from table(dbms_xplan.display);
此计划与前面显示的计划类似，但并不完全相同。这两个计划之间的区别在于，Oracle 数据库现在使用了一个名为 sys_temp_xxx（不属于该查询）的表来满足该 SQL 语句。请注意，由于临时表名称是系统生成的，各个临时表的名称可能会有所不同。
优化器评估了 WHERE 条件的选择性以及维表的大小。通过检测，它发现 customers 表的选择性较高，并且因为此表足够“大”，创建临时表（其中包括 customers 表中谓词的结果集）的开销小于第一个计划。由于第一个计划没有使用临时表，它访问了两次 customers 表。因此，系统使用该临时表代替了 customers 表本身。
如该计划所示，此临时表的创建和数据插入都是自动进行的。
|
| 3. |
接下来，您将使用 STAR 提示来强制 Oracle 7 行为。
在登录 SH 模式的 SQL*Plus 会话中运行 show_star3.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中。
@show_star3.sql
PROMPT STAR JOIN TRANSFORMATION - 7.3 BEHAVIOR
Rem show plan with star join transformation and discuss it
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT /*+ norewrite STAR */
t.calendar_month_desc
, p.prod_subcategory
, c.cust_city
, sum(s.amount_sold) AS dollars
FROM sales s
, times t
, products p
, customers c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.cust_id = c.cust_id
AND c.cust_city='Ravensburg'
--AND p.prod_category in ('Hardware')
AND p.prod_category in ('Hardware','Photo')
AND t.fiscal_year in (2000,1999)
GROUP BY p.prod_subcategory, calendar_month_desc
, c.cust_city;
Rem show plan
set linesize 140
select * from table(dbms_xplan.display);
上面的计划输出显示了所谓的星形查询优化，该功能是在 Oracle7 中引入的。为避免与大型事实表之间进行多次连接，优化器构建了 times、products 和 customers 维表的笛卡尔积，并对该笛卡尔结果与 sales 事实表进行一次连接操作。
注：此星形优化技术与当前的星形查询转换无关，并且它的使用与 star_transformation_enabled 参数的设置无关。
|
| 4. |
接下来，您将执行一个不带任何 SQL 处理优化的典型星形查询。
在登录 SH 模式的 SQL*Plus 会话中运行 show_star4.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中：
@show_star4.sql
PROMPT NO STAR TRANSFORMATION - WORST CASE
Rem show plan without star transformation and discuss it
alter session set star_transformation_enabled=false;
set sqlprompt "no STAR - SQL> "
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT /*+ norewrite */
t.calendar_month_desc
, p.prod_subcategory
, c.cust_city
, sum(s.amount_sold) AS dollars
FROM sales s
, times t
, products p
, customers c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.cust_id = c.cust_id
AND c.cust_city='Ravensburg'
--AND p.prod_category in ('Hardware')
AND p.prod_category in ('Hardware','Photo')
AND t.fiscal_year in (2000,1999)
GROUP BY p.prod_subcategory, calendar_month_desc
, c.cust_city;
PROMPT show plan
set linesize 140
select * from table(dbms_xplan.display);
从连接的角度来看，此计划中不存在面向数据仓库的任何优化。因此，它连接了四个表。请注意，因为表中没有定义谓词，所以系统需要处理整个 sales 事实表。
|
| 5. |
在登录 SH 模式的 SQL*Plus 会话中运行 reset_star_test.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中。
此脚本会将会话环境重置为启用了星形查询转换的初始设置，这是数据仓库环境的推荐设置。
@reset_star_test.sql
PROMPT BACK TO NORMALITY
alter session set star_transformation_enabled=TRUE;
set sqlprompt "SQL> "
|
返回主题列表
位图连接索引是在 Oracle9i 中引入的。连接索引是一个表上的索引，这个表会通过连接使用一个或多个不同表中的列。
如果您使用了连接索引，则必须连接的数据量可能会减少，这是因为连接已经进行了预计算。另外，包含多个维表的连接索引可以消除位操作，而该操作在使用当前位图索引的星形转换中是必需的。
有关位图连接索引的详细信息，请参阅 Oracle 数据仓库指南。
| 1. |
在事实表 sales 上为 products 维表中的连接属性创建一个位图连接索引。在登录 SH 模式的 SQL*Plus 会话中运行 cr_bj_idx.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中：
（注：运行此脚本可能需要几分钟。）
@cr_bj_idx.sql
DROP INDEX bji_sales_cust_city;
CREATE BITMAP INDEX bji_sales_cust_city on sales(c.cust_city)
FROM sales s, customers c
WHERE s.cust_id = c.cust_id
LOCAL NOLOGGING COMPUTE STATISTICS;
|
| 2. |
再次查看查询的执行计划，并将其与上一部分中最初的星形转换计划相比较。为了简便起见，我们禁用了星形转换临时表的使用。
@plan_bj_idx.sql
ALTER SESSION SET star_transformation_enabled=TEMP_DISABLE;
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT /*+ norewrite */
t.calendar_month_desc
, p.prod_subcategory
, c.cust_city
, sum(s.amount_sold) AS dollars
FROM sales s
, times t
, products p
, customers c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.cust_id = c.cust_id
AND c.cust_city='Ravensburg'
AND p.prod_category in ('Hardware','Photo')
--AND p.prod_category in ('Hardware')
AND t.fiscal_year in (2000,1999)
GROUP BY p.prod_subcategory, calendar_month_desc
, c.cust_city;
Rem show plan
set linesize 140
select * from table(dbms_xplan.display);
ALTER SESSION SET star_transformation_enabled=TRUE;
将此计划与相对应的星形转换计划进行比较时，您会注意到，在探测 sales 事实表上的位图索引时不一定要查询 products 维表。查询中的谓词位于 prod_category 列，而该列存储在位图连接索引中，从而为星形转换的内部部分创建了连接。
位图连接索引的另一个优点在于：由于它消除了位图键迭代工作（必须将所有位图合并在一起），从而节省了 CPU（和 IO）。
|
返回主题列表
真正的（永久性）位图索引是在 Oracle 7.3 中引入的，它是 Oracle 星形查询转换的核心基础，并针对集操作进行了优化。B 树索引存储着每个键的行 ID 列表，这些键与具有键值的行相对应；位图索引则用于存储每个键值的位图，而不是行 ID 列表。
位图索引以压缩格式存储；如果不同键值的数量（基数）很少，那么位图索引则具有很高的空间效率，其占用空间仅为相对应的 B 树索引结构的二十到三十分之一。
位图索引为数据仓库提供了下列优点：
 |
缩短了处理大量不同即席查询的响应时间 |
|
与其他索引技术相比，该技术显著减少了对空间的使用 |
|
即使在价格低廉的硬件上也可以获得良好的性能 |
|
非常高效的并行 DML 和加载 |
在本节中，您将在数据仓库环境中创建一个具有典型事实表结构的新表，用于表示我们的一部分销售交易事实数据。为体验位图索引的优点，您将测量位图索引与 B 树索引的索引创建时间和占用空间。另外，您还需要分别为 B 树索引和位图索引的每个维连接列创建一个索引。
| 1. |
在登录 SH 模式的 SQL*Plus 会话中运行 create_stage_table.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中：
@create_stage_table.sql
DROP TABLE sales_delta;
CREATE TABLE sales_delta
NOLOGGING AS
SELECT *
FROM sales
WHERE 1=0;
|
| 2. |
如果您尚未完成高速数据加载和滚动窗口操作 教程，则需要创建一个能够加载一些测试数据的外部表。在登录 SH 模式的 SQL*Plus 会话中运行 create_ext_tab_for_bdf.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中：
@create_ext_tab_for_bdf.sql
CREATE OR REPLACE DIRECTORY log_dir AS '/tmp';
CREATE TABLE sales_delta_XT
(
PROD_ID NUMBER,
CUST_ID NUMBER,
TIME_ID DATE,
CHANNEL_ID CHAR(2),
PROMO_ID NUMBER,
QUANTITY_SOLD NUMBER(3),
AMOUNT_SOLD NUMBER(10,2)
)
ORGANIZATION external
(
TYPE oracle_loader
DEFAULT DIRECTORY data_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
BADFILE log_dir:'sh_sales.bad'
LOGFILE log_dir:'sh_sales.log_xt'
FIELDS TERMINATED BY "|" LDRTRIM
(prod_id, cust_id,
time_id CHAR(11) DATE_FORMAT DATE MASK "DD-MON-YYYY",
channel_id, promo_id, quantity_sold, amount_sold
)
)
location
(
'salesDec01.dat'
)
)REJECT LIMIT UNLIMITED NOPARALLEL;
ALTER TABLE sales_delta_xt location ( 'salesQ1.dat' );
|
| 3. |
使用 sales 事实表中的一些数据填充该表。
@load_stage_table3.sql
INSERT /*+ APPEND */ INTO sales_delta
SELECT
PROD_ID,
CUST_ID,
TIME_ID,
case CHANNEL_ID
when 'S' then 3
when 'T' then 9
when 'C' then 5
when 'I' then 4
when 'P' then 2
else 99
end,
PROMO_ID,
sum(QUANTITY_SOLD) quantity_sold,
sum(AMOUNT_SOLD) amount_sold
FROM SALES_DELTA_XT
GROUP BY prod_id,time_id,cust_id,channel_id,promo_id;
|
| 4. |
基于所有的维连接列创建 B 树索引。记下创建所需的时间。
@cr_btree_idx.sql
set timing on
CREATE INDEX sales_prod_local_bix
ON sales_delta (prod_id)
NOLOGGING COMPUTE STATISTICS ;
CREATE INDEX sales_cust_local_bix
ON sales_delta (cust_id)
NOLOGGING COMPUTE STATISTICS ;
CREATE INDEX sales_time_local_bix
ON sales_delta (time_id)
NOLOGGING COMPUTE STATISTICS ;
CREATE INDEX sales_channel_local_bix
ON sales_delta (channel_id)
NOLOGGING COMPUTE STATISTICS ;
CREATE INDEX sales_promo_local_bix
ON sales_delta (promo_id)
NOLOGGING COMPUTE STATISTICS ;
您会发现，在您的系统上创建 B 树索引需要 3 到 7 秒的时间。
|
| 5. |
使用 SQL 将 SIZES 存储在一个表中，用于进行直接比较。为此，从数据词典中选择 B 树索引的实际分段大小。
@cr_compare_tab.sql
DROP TABLE compare_idx_size;
CREATE TABLE compare_idx_size
AS
SELECT segment_name index_name,'STANDARD BTREE' index_type,
sum(bytes)/(1024*1024) index_size
FROM user_segments us, user_indexes ui
WHERE us.segment_name=ui.index_name
AND ui.table_name='SALES_DELTA'
GROUP BY segment_name, index_type;
|
| 6. |
如果您使用了静态位图索引，则确定这些索引的大小。首先，您必须运行 cleanup_idx_comp.sql 清理环境。
@cleanup_idx_comp.sql
DROP INDEX sales_prod_local_bix;
DROP INDEX sales_cust_local_bix;
DROP INDEX sales_time_local_bix;
DROP INDEX sales_channel_local_bix;
DROP INDEX sales_promo_local_bix;
|
| 7. |
基于所有的维连接列创建静态位图索引。您会发现，创建该索引所需的时间明显少于 B 树索引的创建时间。
@cr_bitmap_idx.sql
Set timing on
CREATE BITMAP INDEX sales_prod_local_bix
ON sales_delta (prod_id)
NOLOGGING COMPUTE STATISTICS;
CREATE BITMAP INDEX sales_cust_local_bix
ON sales_delta (cust_id)
NOLOGGING COMPUTE STATISTICS;
CREATE BITMAP INDEX sales_time_local_bix
ON sales_delta (time_id)
NOLOGGING COMPUTE STATISTICS;
CREATE BITMAP INDEX sales_channel_local_bix
ON sales_delta (channel_id)
NOLOGGING COMPUTE STATISTICS;
CREATE BITMAP INDEX sales_promo_local_bix
ON sales_delta (promo_id)
NOLOGGING COMPUTE STATISTICS ;
注：位图索引的创建速度远远快于相应 B 树索引的创建速度。
|
| 8. |
填写前面创建的比较表，并使用 SQL 来研究 B 树索引与位图索引之间的区别。
@fill_comp_table.sql
INSERT INTO compare_idx_size
SELECT segment_name index_name, 'BITMAP',sum(bytes)/(1024*1024)
FROM user_segments us, user_indexes ui
WHERE us.segment_name=ui.index_name
AND ui.table_name='SALES_DELTA'
GROUP BY segment_name;
COMMIT;
|
| 9. |
比较这些索引大小的不同。为显示所有的数据，在 SQL*Plus 会话中运行脚本 comp_idx1.sql。
@comp_idx1.sql
COLUMN "Index Name" format a23
COLUMN "Index Type" format a14
COLUMN "btree X times larger" format a36
SELECT substr(a.index_name,1,23) "Index Name",
a.index_type "Index Type",
a.index_size "Size [MB]",
b.index_type "Index Type",
b.index_size "Size [MB]",
'btree ' || trunc((b.index_size/a.index_size),2) ||
' times bigger than bitmap' "btree X times larger"
FROM compare_idx_size a, compare_idx_size b
WHERE a.index_name=b.index_name
AND a.index_type='BITMAP'
AND b.index_type='STANDARD BTREE'
ORDER BY 6 asc;
要获得一个较短的版本，运行下列脚本：
@comp_idx2.sql
SELECT substr(a.index_name,1,23) "Index Name",
'btree ' || trunc((b.index_size/a.index_size),2) ||
' times bigger than bitmap' "btree X times larger"
FROM compare_idx_size a, compare_idx_size b
WHERE a.index_name=b.index_name
AND a.index_type='BITMAP'
AND b.index_type='STANDARD BTREE'
ORDER BY 2 asc;
根据索引列基数的不同，位图索引通常是相应 B 树索引的三十分之一；有时甚至可达到 B 树索引的五十到六十分之一。以一个几 TB 的数据仓库系统为例，即使平均缩减比例只达到原来的五分之一，位图索引也能为系统节省大量的磁盘空间和可能的工作负荷。请考虑一下扫描一个 250 GB 的索引和一个 50 GB 的索引会产生多大的差别。
另外，位图索引的创建时间也比 B 树索引短很多。
因此我们认为，真正的位图索引是具有星形或雪花形模式数据模型的任何数据仓库环境的重要组件。
|
| 10. |
清理环境。
@cleanup_idx_test.sql
DROP TABLE compare_idx_size;
DROP TABLE sales_delta;
|
返回主题列表
Oracle 分区不但在表的维护方面具有优势，它的很多机制还能够透明地加快查询性能，并使查询的速度产生数量级的提高。
查询性能通常无法代表在系统中设计分区对象时的主要决策标准。Oracle 建议，设计的分区对象不但要满足系统的维护要求（如加载窗口、备份和恢复时间以及容量约束），还要执行常见的数据仓库任务（如滚动窗口操作）。在很多情况下，这样的分区方式与针对性能驱动策略选择的方式非常接近，甚至完全相同。
注：有关数据仓库中常见滚动窗口操作的详细信息，请参阅“高速数据加载和滚动窗口操作”教程。
分区修剪是用于大型数据仓库项目的一个非常重要的优化方式。在将大型表划分为一些称为分区的更小的部分后，Oracle 确保了用户只能随时访问和处理与其语句相关的分区。
分区修剪是一个过程，用于仅识别出可满足查询的分区对象的必要分区。该过程可以在查询编译时执行（静态修剪），也可以在运行时动态执行。
|
静态分区修剪在这种情况下发生：优化器可以在分析时消除特定的分区（例如分区键列上的查询谓词）时。
|
|
动态分区修剪可能非常简单（如运行时的绑定变量替换），也可能非常复杂（如生成附加的递归 SQL 以识别合适的分区）。动态分区修剪在查询运行时进行。
|
通常情况下，如果满足了下列条件，则会发生高级修剪：
|
表上的谓词条件连接到分区键列上的分区对象 |
|
谓词的选择性足够高 - 因此分区修剪的选择性足够高 - 这使附加递归 SQL 的开销低于处理不带动态分区修剪的查询。
|
|
当连接列上的谓词无法在分析时确定并且需要简单的附加递归 SQL 时，动态分区修剪也会发生。
例如，当您正在使用一个“不完整”的 DATE 值，如 TO_DATE ('01-JAN-00', 'DD-MON-RR') 时，或者当您对 DATE 数据类型使用隐式数据类型转换时。在这种情况下，您需要一个递归语句来确定世纪值，从而完善此 DATE 值。 |
静态分区修剪
下面的示例演示了在分区键列上具有谓词条件的静态分区修剪。
| 1. |
在登录 SH 模式的 SQL*Plus 会话中运行 plan_static_pruning.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中：
@plan_static_pruning.sql
truncate table plan_table;
DELETE FROM plan_table;
COMMIT;
explain plan for
SELECT c.channel_desc, sum(amount_sold)
FROM sales s, channels c
WHERE s.channel_id = c.channel_id
AND s.time_id >= to_date('04-JAN-2000','DD-MON-YYYY')
AND s.time_id <= to_date('22-FEB-2000','DD-MON-YYYY')
GROUP BY channel_desc;
Rem show plan
set linesize 140
select * from table(dbms_xplan.display);
查看 PSTART 和 PSTOP 列，了解分区修剪的相关信息。您会发现，当且仅当查询访问分区 partition # 13 时，它才能获取查询结果。
|
| 2. |
有关必须访问分区的更多信息，您可以使用相应的分区号查询数据词典。
@select_part_name.sql
SELECT partition_name
FROM user_tab_partitions
WHERE table_name='SALES'
AND partition_position=13;
|
返回主题
动态分区修剪
该示例模式的 sales 事实表根据 time_id 列进行了分区。将 sales 事实表与 times 维表基于 time_id 进行连接会满足第一个条件，因此将发生动态分区修剪。
| 1. |
在登录 SH 模式的 SQL*Plus 会话中运行 plan_dyn_pruning.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中：
@plan_dyn_pruning.sql
DELETE FROM plan_table;
COMMIT;
EXPLAIN PLAN FOR
SELECT /*+ norewrite */
t.calendar_month_desc
, p.prod_subcategory
, c.cust_city
, sum(s.amount_sold) AS dollars
FROM sales s
, times t
, products p
, customers c
WHERE s.time_id = t.time_id
AND s.prod_id = p.prod_id
AND s.cust_id = c.cust_id
AND c.cust_city='Ravensburg'
AND p.prod_category in ('Hardware')
AND t.fiscal_year in (2000,1999)
GROUP BY p.prod_subcategory, calendar_month_desc
, c.cust_city;
set linesize 140
Rem show the plan with dynamic partition pruning
select * from table(dbms_xplan.display);
与静态分区修剪不同，您不会在 PSTART 和 PSTOP 列中看到任何绝对数字，而是会看到 KEY (SQ) 一词。这就表示此查询将在运行时发生动态分区修剪。
这些结果不但显示了动态修剪的种类，而且还显示了动态修剪的发生方式。
|
| 2. |
要显示针对动态分区修剪发出的递归 SQL 语句，为 plan_table 运行下列查询：
@select_other
set long 400
SELECT other
FROM plan_table
WHERE other IS NOT NULL;
您会看到 Oracle 数据库如何将原始的谓词条件转换为可获得相应分区信息的内嵌式视图。
您看到的结果不但显示了动态修剪的种类，而且显示了动态修剪的发生方式。此示例中的动态修剪基于 SUBQUERY，该 SUBQUERY 可能标识在计划 (Id 7) 中也可能标识在 PSTART/PSTOP 列中；KEY(SQ) 是 KEY(SUBQUERY) 的缩写。
|
返回主题
返回主题列表
智能化分区连接可以缩短查询响应时间，这是通过最小化并行执行连接时并行执行服务器之间交换的数据量来实现的。由于响应时间明显缩短，CPU 和内存资源的使用得到改善。在 Oracle Real Application Cluster 环境中，智能化分区连接还能消除或者限制互连上的数据通讯量，这对于获得大量连接操作的良好可伸缩性是非常关键的。智能化分区连接可以是完整连接，也可以是部分连接。Oracle 服务器会决定使用哪种类型的连接。
注：本教程并没有详细讲述智能化分区操作的所有可能性。它会让您对智能化分区操作有一个基本的了解，并掌握读取执行计划的相关信息。有关智能化分区连接的详细信息，请参阅数据仓库指南。
步骤
智能化分区连接的优点
智能化分区连接具有以下优点：
降低通信开销
在并行执行时，智能化分区连接会降低通信开销。这是因为，在默认情况下，由一系列并行执行服务器并行执行的连接操作需要基于连接列将每个表重新分配到断开连接的行子集中。然后，这些断开的行子集将由一个并行执行服务器进行智能化对连接。
因为这两个表已经基于该连接列进行了分区，Oracle 数据库可以避免对这些分区的重新分布。这使每个并行执行服务器都能与一对相匹配的分区连接。
在使用节点间并行执行的 Oracle Real Application Cluster 配置中，由于使用并行执行而得到的性能改善甚至会更加明显。智能化分区连接极大地减少了互连通讯量。因而，此功能对于使用 Oracle Real Application Clusters 的大型 DSS 配置很有帮助。
降低了内存要求
与所连接表的完整数据集的相应连接操作相比，智能化分区连接需要较少的内存。
在串行连接情况下，连接在相配分区对上同时执行。如果数据在分区之间平均分配，则内存需求为需求总数除以分区的数量。这是显而易见的。
在并行情况下，内存需求取决于并行连接的分区对的数量。例如，如果并行程度为 20，分区数量为 100，则所需的内存会减少五倍，这是因为系统只能同时执行两个分区的 20 个连接。由于智能化分区连接所需内存较少，所以会对性能产生直接的影响。例如，连接可能不需要在构建散列连接期间向磁盘写入数据块。
返回主题列表
1. 创建两个附加的散列分区表和索引
Oracle 数据库用于决定是否执行智能化分区连接的一个驱动因素（除了并行执行之外）是要连接对象的物理结构。为演示智能化分区连接，您还需要两个附加的表。
| 1. |
创建一个散列分区表，使其结构与 customers 维表的结构相同。在登录 SH 模式的 SQL*Plus 会话中，运行下列 SQL 语句创建该表：
@create_cust_hash.sql
DROP TABLE customers_hash;
CREATE TABLE customers_hash
( cust_id NUMBER
, cust_first_name VARCHAR2(20)
, cust_last_name VARCHAR2(40)
, cust_gender CHAR(1)
, cust_year_of_birth NUMBER(4)
, cust_marital_status VARCHAR2(20)
, cust_street_address VARCHAR2(40)
, cust_postal_code VARCHAR2(10)
, cust_city VARCHAR2(30)
, cust_city_id number
, cust_state_province VARCHAR2(40)
, cust_state_province_id number
, country_id number
, cust_main_phone_number VARCHAR2(25)
, cust_income_level VARCHAR2(30)
, cust_credit_limit NUMBER
, cust_email VARCHAR2(30)
, cust_total varchar2(14)
, cust_total_id number
, cust_src_id number
, cust_eff_from date
, cust_eff_to date
, cust_valid varchar2(1)
)
PCTFREE 5
PARTITION BY HASH (cust_id)
(PARTITION h1_cust, PARTITION h2_cust,PARTITION h3_cust,PARTITION h4_cust,
PARTITION h5_cust,PARTITION h6_cust,PARTITION h7_cust,PARTITION h8_cust,
PARTITION h9_cust,PARTITION h10_cust,PARTITION h11_cust,PARTITION h12_cust,
PARTITION h13_cust,PARTITION h14_cust,PARTITION h15_cust,PARTITION h16_cust);
CREATE UNIQUE INDEX customers_hash_pk
ON customers_hash (cust_id) ;
ALTER TABLE customers_hash
ADD ( CONSTRAINT customers_hash_pk
PRIMARY KEY (cust_id) RELY ENABLE VALIDATE
) ;
|
| 2. |
创建一个组合的范围散列分区表，使其结构与 sales 事实表结构相同。
@create_sales_hash.sql
DROP TABLE sales_hash;
CREATE TABLE sales_hash
( prod_id NUMBER
, cust_id NUMBER
, time_id DATE
, channel_id NUMBER
, promo_id NUMBER
, quantity_sold NUMBER(10,2)
, amount_sold NUMBER(10,2)
)PCTFREE 5 NOLOGGING
PARTITION BY RANGE (time_id)
SUBPARTITION BY HASH (cust_id)
SUBPARTITIONS 16
(PARTITION SALES_HASH_1995 VALUES LESS THAN (TO_DATE('01-JAN-1996','DD-MON-YYYY')),
PARTITION SALES_HASH_1996 VALUES LESS THAN (TO_DATE('01-JAN-1997','DD-MON-YYYY')),
PARTITION SALES_HASH_1_1997 VALUES LESS THAN (TO_DATE('01-JUL-1997','DD-MON-YYYY')),
PARTITION SALES_HASH_2_1997 VALUES LESS THAN (TO_DATE('01-JAN-1998','DD-MON-YYYY')),
PARTITION SALES_HASH_Q1_1998 VALUES LESS THAN (TO_DATE('01-APR-1998','DD-MON-YYYY')),
PARTITION SALES_HASH_Q2_1998 VALUES LESS THAN (TO_DATE('01-JUL-1998','DD-MON-YYYY')),
PARTITION SALES_HASH_Q3_1998 VALUES LESS THAN (TO_DATE('01-OCT-1998','DD-MON-YYYY')),
PARTITION SALES_HASH_Q4_1998 VALUES LESS THAN (TO_DATE('01-JAN-1999','DD-MON-YYYY')),
PARTITION SALES_HASH_Q1_1999 VALUES LESS THAN (TO_DATE('01-APR-1999','DD-MON-YYYY')),
PARTITION SALES_HASH_Q2_1999 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')),
PARTITION SALES_HASH_Q3_1999 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')),
PARTITION SALES_HASH_Q4_1999 VALUES LESS THAN (TO_DATE('01-JAN-2000','DD-MON-YYYY')),
PARTITION SALES_HASH_Q1_2000 VALUES LESS THAN (TO_DATE('01-APR-2000','DD-MON-YYYY')),
PARTITION SALES_HASH_Q2_2000 VALUES LESS THAN (TO_DATE('01-JUL-2000','DD-MON-YYYY')),
PARTITION SALES_HASH_Q3_2000 VALUES LESS THAN (TO_DATE('01-OCT-2000','DD-MON-YYYY')),
PARTITION SALES_HASH_Q4_2000 VALUES LESS THAN (TO_DATE('01-JAN-2001','DD-MON-YYYY')),
PARTITION SALES_HASH_Q1_2001 VALUES LESS THAN (TO_DATE('01-APR-2001','DD-MON-YYYY')),
PARTITION SALES_HASH_Q2_2001 VALUES LESS THAN (TO_DATE('01-JUL-2001','DD-MON-YYYY')),
PARTITION SALES_HASH_Q3_2001 VALUES LESS THAN (TO_DATE('01-OCT-2001','DD-MON-YYYY')),
PARTITION SALES_HASH_Q4_2001 VALUES LESS THAN (TO_DATE('01-JAN-2002','DD-MON-YYYY')))
;
CREATE BITMAP INDEX sales_cust_hash_bix
ON sales_hash (cust_id)
LOCAL NOLOGGING;
|
返回主题
2. 导入这两个表的统计信息
您已经设置了两个附加表 customers_hash 和 sales_hash，但是这两个表中不包含任何数据。您不需要复制源表 customers 和 sales 中的数据，而是使用导出和导入创建不包含任何数据的表统计信息。此功能是在 Oracle8i 中引入的。
Oracle 优化器使用对象统计信息评估执行计划。在没有相应数据的情况下导出和导入统计信息的能力允许您为测试或开发环境获得与大型生产系统相同的优化器行为，而不必具有相同的规模和数据。
注：获取相同优化器行为的另一种方式是使用 Oracle 的计划稳定性功能。请参阅性能指南和参考。
| 1. |
目前，您尚未收集两个新对象的任何统计信息：
@show_tab_stats.sql
SELECT table_name, num_rows
FROM user_tables
WHERE table_name in ('SALES_HASH','CUSTOMERS_HASH');
|
| 2. |
因为这两个对象中不包含任何数据，并且也没有插入数据的计划，所以您需要导入这两个对象的现有统计信息。
@imp_tab_stats.sql
Rem import those statistics from STAT_TABLE
Rem file stat_table.dmp, must be imported at the beginning
exec dbms_stats.import_table_stats('sh','sales_hash', -
stattab =>'stat_table', statid =>'HANDS_ON');
exec dbms_stats.import_table_stats('sh','customers_hash', -
stattab =>'stat_table', statid =>'HANDS_ON')
|
| 3. |
查看这两个表的统计信息，就好像表中包含很多数据一样。
@show_tab_stats.sql
SELECT table_name, num_rows
FROM user_tables
WHERE table_name IN ('SALES_HASH','CUSTOMERS_HASH');
|
返回主题
3. 查看串行智能化分区连接
使用串行智能化分区连接有两种方法。这些方法包括：
注：NOREWRITE 提示用于避免与可能存在的物化视图之间的冲突。另外，您也可以禁用此特定会话或整个实例的 query_rewrite。这种情况适用于所有后续语句。
| |
在登录 SH 模式的 SQL*Plus 会话中运行 set_noparallel.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中以确保获得串行执行计划：
@set_noparallel
ALTER TABLE sales NOPARALLEL;
ALTER TABLE sales_hash NOPARALLEL;
ALTER TABLE customers NOPARALLEL;
ALTER TABLE customers_hash NOPARALLEL;
|
串行非智能化分区连接
现在，请将范围分区表 sales 与非分区表 customers 连接。
| 1. |
在登录 SH 模式的 SQL*Plus 会话中运行 serial_nopwj.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中：
@serial_nopwj.sql
DELETE FROM plan_table;
COMMIT;
explain plan for
select /*+ use_hash(c,s) norewrite */ cust_last_name, sum(amount_sold)
from sales s, customers c
where s.cust_id = c.cust_id
-- and s.cust_id in (10005,10004,10003)
group by cust_last_name;
set linesize 140
Rem show the plan with non partition-wise join
select * from table(dbms_xplan.display);
该计划显示了对两个表 sales 和 customers 以及一个散列连接的完整表访问。PARTITION RANGE ALL 这行源代码位于该 HASH JOIN 中，这表示 customers 表要与所有分区进行连接。
|
返回子主题
串行完全智能化分区连接
完全智能化分区连接会将一个较大的连接分为两个连接表中分区对之间的一些更小的连接。要使用此功能，您必须将组合（范围散列）分区表 sales_hash 与散列分区表 customers_hash 进行连接，并基于它们的连接键对两个表进行均分。请注意，这两个表是基于 cust_id 连接列进行均分的。
| 1. |
在登录 SH 模式的 SQL*Plus 会话中运行 serial_pwj.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中：
@serial_pwj.sql
DELETE FROM plan_table;
COMMIT;
explain plan for
select /*+ norewrite */ cust_last_name, sum(amount_sold)
from sales_hash s, customers_hash c
where s.cust_id = c.cust_id
-- and s.cust_id in (10005,10004,10003)
group by cust_last_name;
set linesize 140
Rem show the plan with full partition-wise join
select * from table(dbms_xplan.display);
该计划与非分区计划稍有不同。 You see an additional operation, ?PARTITION HASH ALL? in the plan.该行源代码位于 HASH JOIN 之外。您可以将此操作视为指定处理散列连接的方式，如下方的伪代码所示：
FOR LOOP over all partitions of customers_hash
DO
hash-join equivalent partitions
DONE
此完全智能化分区连接是以串行方式执行的。
|
返回子主题
返回主题
4. 查看并行智能化分区连接
使用并行智能化分区连接有三种方法。这些方法包括：
NOREWRITE 提示用于避免与可能存在的现有物化视图间的冲突。另外，您也可以禁用此特定会话或整个实例的 query_rewrite。这种情况适用于所有后续语句。
| |
在登录 SH 模式的 SQL*Plus 会话中运行 set_parallel4.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中以确保获得并行执行计划：
@set_parallel4.sql
ALTER TABLE sales PARALLEL 4;
ALTER TABLE sales_hash PARALLEL 4;
ALTER TABLE customers PARALLEL 4;
ALTER TABLE customers_hash PARALLEL 4
|
非智能化分区连接
现在，将范围分区表 "sales" 与非分区表 "customers" 相连。
| 1. |
在登录 SH 模式的 SQL*Plus 会话中运行 par_nopwj.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中：
@par_nopwj.sql
explain plan for
select /*+ norewrite */ cust_last_name, sum(amount_sold)
from sales s, customers c
where s.cust_id = c.cust_id
-- and s.cust_id in (10005,10004,10003)
group by cust_last_name;
set linesize 140
Rem show the plan with non partition-wise join
select * from table(dbms_xplan.display);
您会看到该语句是以并行方式执行的；因为其中一个表的任何现有物理分区都无法用于此查询，所以没有发生智能化分区连接。
两个表都以并行方式扫描。较小的表 customers 广播到基于该并行计划 (Id 9) 的下一部分运行的所有从属内容。该并行计划的下一部分执行第一个 HASH GROUP BY 后面的 HASH JOIN。因为所有从属内容均基于完整结果集运行，所以您必须为最后一个 HASH GROUP BY 操作 (Id 3) 重新分配完整结果集（基于 HASH，Id 5）。
如果查询包含一个附加的 ORDER BY，您就会看到一个基于 RANGE 的重新分布，它可以满足 SORT GROUP BY（ID 5 的 PQ Distribution 方法）的需要。因为消除了最后的排序操作，重新分布基于 RANGE 的数据优化了该计划 - 系统按照特定的顺序 (Id 2) 连接并行从属内容的结果，从而保证了该排序。
|
返回子主题
完全智能化分区连接
完全智能化分区连接的并行执行是串行执行的简单并行化。不是一次连接一个分区对，而是由 n 个查询服务器以并行方式连接分区对。
上图显示了一个散列-散列智能化分区连接的并行智能化分区连接。在本教程中，您要将散列分区表 customers_hash 与组合分区表 sales_hash 相连。Sales_hash 表的散列分区由 16 个子分区组成，每个子分区来自一个范围分区。
| 1. |
在登录 SH 模式的 SQL*Plus 会话中运行 par_fullpwj.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中：
@par_fullpwj.sql
alter session set "_parallel_broadcast_enabled"=FALSE;
DELETE FROM plan_table;
COMMIT;
explain plan for
select /*+ norewrite */ cust_last_name, sum(amount_sold)
from sales_hash s, customers_hash c
where s.cust_id = c.cust_id
-- and s.cust_id in (10005,10004,10003)
group by cust_last_name;
set linesize 160
Rem show the plan with full partition-wise join
select * from table(dbms_xplan.display);
由于对象较小，您必须禁用小型对象的并行执行优化，即结果的广播。对于小型对象，到所有从属内容的广播可能是开销更少的执行计划；而避免重新分布比使用较大结果集进行后续并行操作更具优势。
您会看到已在串行完全智能化分区连接中了解的 'PARTITION HASH ALL' 操作。
另外，此计划还显示了智能化分区连接的一个优点：在并行执行时，智能化分区连接会降低通信开销。因为这两个表已经基于该连接列进行了分区，Oracle 数据库可以避免对这些分区的重新分布。这使每个并行执行服务器都能与一对相匹配的分区连接。
您会看到执行第一个 HASH GROUP BY (ID 6) 操作的同一个从属集 (Q 1,00) 也在为分区对执行表扫描和散列连接操作（PQ Distribution 方法，Parallel Combined With Parent/Child，即 PCWP 或 PCWC）。到目前为止，该执行计划中没有进行数据重新分布。
|
返回子主题
部分智能化分区连接
与完全智能化分区连接不同，部分智能化分区连接只需要您基于连接键对一个表进行分区，而不是对两个表都进行分区。分区的表称为参考表。另一个表可以分区也可以不分区。部分智能化分区连接比完全智能化分区连接更为常见，不需要对表进行均分。
要执行部分智能化分区连接，Oracle 数据库会基于参考表的分区对另一个表进行动态重新分区。在另一个表重新分区之后，该计划的执行与完全智能化分区连接相似。
部分智能化分区连接只以并行方式执行。该连接的主要优势是连接之前的分布，它可以改善连接操作的效率、加快连接的速度。 Because you only have one process executing the join in serial, there is no need—and no benefit—to redistribute the nonpartitioned table dynamically before the join.
| 1. |
在登录 SH 模式的 SQL*Plus 会话中运行 par_partpwj.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中：
@par_partpwj.sql
explain plan for
select /*+ norewrite */ cust_last_name, sum(amount_sold)
from sales_hash s, customers c
where s.cust_id = c.cust_id
-- and s.cust_id in (10005,10004,10003)
group by cust_last_name;
set linesize 140
Rem show the plan with partial partition-wise join
select * from table(dbms_xplan.display);
您会看到，customers 表基于连接键列 cust_id 进行了动态重新分布，并且分布方式与 sales_hash 表基于此列进行再次分区的方式相同。
以下的事实表明了这一点，即，系统向 HASH JOIN 操作 (Q 1,01 - ID 7) 提供了 customers 表并行表扫描的结果集，这与执行组合分区表 sales_hash 的并行（智能化分区）扫描的从属集相同。
Sales_hash 表的散列分区由 16 个子分区组成，每个子分区来自一个范围分区。
|
返回子主题
返回主题
5. 清理
在继续下一主题之前，您需要清理环境。执行以下步骤：
| |
在登录 SH 模式的 SQL*Plus 会话中运行 cleanup_mod3.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中：
@cleanup_mod3.sql
ALTER TABLE sales NOPARALLEL;
DROP TABLE sales_hash;
ALTER TABLE customers NOPARALLEL;
DROP TABLE customers_hash;
|
返回主题列表
DBMS_STATS 包是在 Oracle8i 中引入的。此程序包简化了完整数据库、模式或特定对象的统计数据收集；其中大多数操作都可以并行执行。DBMS_STATS 是用于收集统计数据的推荐方式，与旧的 ANALYZE 命令相比更具有优势。
您不但可以为完整的数据库、模式或表收集统计数据，通过 Oracle8i 引入的 STALE 和 EMPTY 对象，您还可以为需要获取新统计数据的对象指定附加的粒度。
|
EMPTY 表是不包含任何统计数据的表。您可以使用 options=’GATHER EMPTY’ 为所有这些表及其相关索引收集统计数据。 |
|
只要总数据量的 10% 发生了变化，系统就会认为表及其相关索引结构处于 STALE 状态。使用 option=’GATHER STALE’ 调用 DBMS_STATS 就会收集变化超过 10% 的所有对象的统计数据。
|
从 Oracle 数据库 10g 开始，Oracle 自动监视表的陈旧程度。在较早的版本中，您必须主动启用表监视功能。方法是将表指定为 CREATE_TABLE 命令的一部分，或者在稍后使用 ALTER TABLE 命令启用。
认为表处于陈旧状态的阈值 10% 不可更改；这是从内部测试获取的经验值，适用于大多数客户的情况。有时，该阈值可能比较保守的，这意味着 Oracle 数据库很可能在必要的时间之前将对象视为陈旧对象。如果一个特定的应用环境需要更多的适用统计数据，您可以利用有关对象变化的内部跟踪信息，然后实施收集所需统计信息的过程。
除了希望自己控制所有内容的非常复杂的客户环境之外，很多客户关注操作的简便性。使用 Oracle 数据库 10g 时，您使用一个命令即可更新系统中的所有统计数据，从而大大减少了统计数据的维护工作。为优化器提供相应的统计数据也不困难。在默认情况下，系统会为每个 Oracle 数据库 10g 安装设置一个计划作业，定期在预定义的维护窗口 (WEEKNIGH_WINDOW, WEEKEND_WINDOW) 中收集统计数据。
一个用于使用 DBMS_STATS 包的选项设置在 Oracle9i 中引入。该选项为 ‘GATHER AUTO’。除了模式名称之外，这是唯一一个必须传递给 Oracle 的参数，它会自动为您决定所有其他设置（如 estimate_percentage）或您是否需要直方图。该参数将收集不包含统计数据的所有表以及被认为处于陈旧状态的所有对象的统计数据。
要使用此选项，执行下列步骤：
| 1. |
在登录 SH 模式的 SQL*Plus 会话中运行 tab_status_mon.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中：
@tab_status_mon.sql
Rem actual tables and monitoring status
SELECT table_name,
to_char(last_analyzed,'dd.mm.yy hh24:mi:ss') la,
num_rows, monitoring
FROM user_tables;
在默认情况下，系统会监视所有的表，但您也可能发现一些例外（如外部表）。这是 Oracle 数据库 10g 的新行为。监视过程完全在内存中进行，不会对表产生任何开销；并且您不能关闭表的监视。
|
| 2. |
在启用监视的情况下，user_tab_modifications 数据词典视图将列出表的所有更改。
@show_tab_modifications.sql
COLUMN table_name FORMAT a20
COLUMN subpartion_name FORMAT a20
SELECT * FROM user_tab_modifications;
您看到条目的数量取决于系统之前所发生的情况。现在您可以使用一个命令来更新统计数据，对于收集统计数据的对象，您还会看到其计时和数量是如何发生变化的。
|
| 3. |
在登录 SH 模式的 SQL*Plus 会话中运行 gather_auto.sql，或将下列 SQL 语句复制到 SQL*Plus 会话中：
@gather_auto.sql
set serveroutput on
declare
list_of_objects dbms_stats.objectTab := dbms_stats.objectTab();
begin
dbms_output.enable(200000);
dbms_stats.gather_schema_stats('SH',options=>'GATHER AUTO',
objlist=>list_of_objects);
for i in 1 ..list_of_objects.count loop
dbms_output.put_line('updated:'||list_of_objects(i).objtype||' '||
list_of_objects(i).objname||' '||list_of_objects(i).partname);
end loop;
end;
/
根据上一教程中使用的不包含任何统计数据的对象数量的不同，这种方式可能需要几分钟的时间。
注：模式名称以及自动收集统计数据的请求会传递给 Oracle。所有其他参数都是在系统内部产生的。
|
| 4. |
在其中一个表上执行 DML。
@stat_dml1.sql
Rem 3833 rows
UPDATE customers
SET cust_credit_limit=cust_credit_limit+1
WHERE country_id =
(SELECT country_id
FROM countries
WHERE country_name='France');
COMMIT;
您修改的数据少于 10%，因此尚未达到收集新统计数据的阈值。
|
| 5. |
现在，查看 user_tab_modifications 中的内容。除非您进行了手动请求，否则有关表的修改的相关信息会在 SGA 中定期刷新。 You won?t see any changes yet (unless you did the last operations shortly before a periodic flush).
@show_tab_modifications.sql
COLUMN table_name FORMAT a20
COLUMN subpartion_name FORMAT a20
SELECT * FROM user_tab_modifications;
|
| 6. |
Although the database periodically flushes the information about modified objects from the SGA (and also internally every time when DBMS_STATS is invoked), there?s a customer requirement to get the most actual information for customer-specific usage.您可以通过发出以下命令来手动地“按需”刷新这些信息：
@flush_monitoring.sql
Rem enforce flush of the modification info
Rem is called internally every time before GATHER statistics
exec dbms_stats.flush_database_monitoring_info
|
| 7. |
现在，您手动刷新了表修改的相关信息。您会看到相应的更改。
@show_tab_modifications.sql
COLUMN table_name FORMAT a20
COLUMN subpartion_name FORMAT a20
SELECT * FROM user_tab_modifications;
|
| 8. |
再次收集统计数据。
@gather_auto.sql
set serveroutput on
declare
list_of_objects dbms_stats.objectTab := dbms_stats.objectTab();
begin
dbms_output.enable(200000);
dbms_stats.gather_schema_stats('SH',options=>'GATHER AUTO',
objlist=>list_of_objects);
for i in 1 ..list_of_objects.count loop
dbms_output.put_line('updated:'||list_of_objects(i).objtype||
' '||list_of_objects(i).objname||' '||list_of_objects(i).partname);
end loop;
end;
/
|
| 9. |
因为更改行的比例低于阈值 10%，所以它的运行速度很快，不会调用统计数据收集。您不会看到更新的修改信息。
@show_tab_modifications.sql
COLUMN table_name FORMAT a20
COLUMN subpartion_name FORMAT a20
SELECT * FROM user_tab_modifications;
如果您希望根据特定的应用规则设置自己的统计数据收集机制，则可以执行此操作，并使用 user_tab_modifications 中的信息。显示的对象获得新的统计数据之后，它们的条目将立即从可能处于陈旧状态的表的列表中删除。
|
| 10. |
再执行一些 DML，使更改超过阈值 10%。
@stat_dml2.sql
Rem note that the monitoring mechanism is agnostic about the content
- the following statement reverts the subsequent on
update customers
set cust_credit_limit=cust_credit_limit-1
where country_id =
(select country_id from countries where country_name='France');
commit;
|
| 11. |
在 SGA 中刷新有关监视表的信息。
@flush_monitoring.sql
exec dbms_stats.flush_database_monitoring_info
请注意，最后一个 DML 回复了上一个命令。
|
| 12. |
现在，您手动刷新了表修改的相关信息。您会看到相应的更改。
@show_tab_modifications.sql
COLUMN table_name FORMAT a20
COLUMN subpartion_name FORMAT a20
SELECT * FROM user_tab_modifications;
现在，更改的数据超过了阈值 10%。再次运行统计数据的自动收集之后，Oracle 将自动收集关于表 customers 的新统计数据集。
|
收集统计数据之前，您要再次更新 customers 表，并对非分区表使用 Oracle 的并行 DML 功能。对于 10g Release 2，当存在位图索引时，每个分段上最大 DOP 的限制会得到提升。
| 1. |
创建一个临时表。从 SQL*Plus 会话中执行以下脚本：
@cr_cust_dml.sql
DROP TABLE cust_dml;
CREATE TABLE cust_dml PARALLEL AS SELECT /*+ PARALLEL(c) */ * FROM customers c;
|
| 2. |
现在为非分区表 products 发出一个并行 UPDATE 命令。
@xpdml_on_cust.sql
ALTER SESSION ENABLE PARALLEL DML;
COMMIT;
EXPLAIN PLAN FOR update cust_dml set cust_credit_limit=cust_credit_limit-1;
SELECT * FROM TABLE(dbms_xplan.display);
请注意，UPDATE 命令是该并行操作 (Id 3) 的一部分。该计划还表明，索引维护是作为 DML 操作的一部分以并行方式进行的。
|
| 3. |
执行并行 DML，并使用 V$PO_SESSTAT 对其进行控制。
@pdml_on_cust.sql
PROMPT Parallel DML against new table
update cust_dml set cust_credit_limit=cust_credit_limit-1;
COMMIT;
SELECT * FROM v$pq_sesstat
WHERE statistic in ('DML Parallelized','Allocation Height');
|
| 4. |
最后，使用 GATHER AUTO 功能。
再次调用统计数据的自动收集。您会看到这次的操作时间比较长，系统收集了表 customers 的新统计数据。
@gather_auto.sql
set serveroutput on
declare
list_of_objects dbms_stats.objectTab := dbms_stats.objectTab();
begin
dbms_output.enable(200000);
dbms_stats.gather_schema_stats('SH',options=>'GATHER
AUTO',objlist=>list_of_objects);
for i in 1 ..list_of_objects.count loop
dbms_output.put_line('updated:'||list_of_objects(i).objtype||'
'||list_of_objects(i).objname||' '||list_of_objects(i).partname);
end loop;
end;
/
|
| 5. |
现在，您可以显示对 customers 表进行的所有修改。
@show_tab_modifications.sql
COLUMN table_name FORMAT a20
COLUMN subpartion_name FORMAT a20
SELECT * FROM user_tab_modifications
|
返回主题列表
要清理您的环境，您需要执行下列步骤：
|
|
在登录 SH 模式的 SQL*Plus 会话中执行下列命令：
SET SERVEROUTPUT ON
EXEC dw_handsOn.cleanup_modules
|
返回主题列表
在本教程中，您学习了如何：
|
将 Oracle 的星形查询转换与其他不同的访问计划进行比较 |
|
使用位图连接索引 |
|
了解数据仓库 B 树索引与位图索引之间的区别 |
|
熟悉 Oracle 用于改善查询性能的高级分区机制 |
|
使用统计数据自动收集 |
返回主题列表
|
