Oracle OLAP 10g表キューブ集計と問合せ操作のベスト・プラクティス

Oracle OLAP 10g
表キューブ集計と問合せ操作のベスト・プラクティス
リリース 1 for Windows and UNIX
部品番号 : B14445-01
原典情報 : A92122-05 Oracle OLAP 10g Best Practices for Tabular Cube Aggregation and Query Operations, Release 1 for
Windows and UNIX
2004 年 10 月
このドキュメントでは、標準的な OLAP 操作および環境の管理性を最適化するための方
法について説明します。この情報は、次の 4 つの内容に分類されます。
1.
スキーマ設計に関する考慮事項
2.
アクセス構造作成のための Oracle 構成パラメータおよびセッションの設定
3.
アクセス構造のスクリプト・パッケージ
4.
問合せ操作の最適化
スキーマ設計に関する考慮事項
■
■
■
■
すべての階層レベル内で一意であるサロゲート・キーを、ディメンション・データに
割り当てます。サロゲート・キーは NUMBER 型として定義し、キーの長さは個々の
ディメンション・メンバーの大きさによって定義します（このリストが大きくなると
予想される場合は、そのことを考慮に入れます）
。これらのサロゲート・キーは、
ディメンション表の主キーおよびレベル・キーとして使用されます。OLAP オブ
ジェクトを定義する前に、新たに定義したサロゲート・キーでファクト表の文字列
キーを補足するルーチンを行う必要があります。このタイプのルーチンのサンプル・
スクリプトについては、次に説明します。
設計したマテリアライズド・ビューおよびビューの索引に対し、個別に表領域を作成
します。これによって、パフォーマンスが大幅に向上することはありませんが、ファ
イルや I/O の管理には効果があります。
アクセス構造（マテリアライズド・ビューおよび索引）の作成スクリプトを実行する
前に、ベース表で統計を計算します。
CWM2 を使用する場合でも、リレーショナル・ディメンション・オブジェクトおよ
び主キー / 外部キーの制約を作成する必要があります。
サロゲート・キーの割当て（例）
この例では、チャネル・メンバーのサロゲート・キーを生成し、そのサロゲート・キーを
使って channels 表を再作成します。その後に、サロゲート・キーを含む sales 表を再作成
します。
PROMPT Generate surrogate keys for CHANNELS
DROP TABLE chan_skey;
CREATE TABLE chan_skey AS
SELECT
lvlid
dimval
dense_rank () OVER
Copyright © 2001, 2004, Oracle.
All rights reserved.
AS lvlid,
AS dimval,
Oracle と Oracle のロゴは Oracle Corporation の登録商標です。SQL*Plus は、Oracle Corporation の商標です。記載されているその他
の製品名および社名はその製品および会社を識別する目的にのみ使用されており、それぞれ該当する所有者の商標です。
(ORDER BY lvlid, dimval)
AS dimkey
FROM
(SELECT DISTINCT TO_CHAR(channel_total) AS dimval, 0 lvlid FROM
channels UNION ALL
SELECT DISTINCT TO_CHAR(channel_class) AS dimval, 1 lvlid FROM
channels UNION ALL
SELECT DISTINCT TO_CHAR(channel_id) AS dimval, 2 lvlid FROM
channels);
COMMIT;
DROP INDEX chan_skey_index;
CREATE INDEX chan_skey_index ON chan_skey(lvlid);
COMMIT;
PROMPT
Create CHANNELS_NEW table with surrogate keys
DROP TABLE channels_new;
CREATE TABLE channels_new
(
channel_id
CHAR(1),
channel_id_key
NUMBER,
channel_desc
VARCHAR2(20),
channel_class
VARCHAR2(20),
channel_class_key
NUMBER,
channel_total
VARCHAR2(13),
channel_total_key
NUMBER) ;
INSERT /*+ APPEND*/ INTO channels_new
SELECT c.channel_id,
(SELECT DISTINCT sk2.dimkey
FROM chan_skey sk2
WHERE sk2.dimval = TO_CHAR(c.channel_id)
AND sk2.lvlid = 2),
c.channel_desc,
c.channel_class,
(SELECT DISTINCT sk1.dimkey
FROM chan_skey sk1
WHERE sk1.dimval = TO_CHAR(c.channel_class)
AND sk1.lvlid = 1),
c.channel_total,
(SELECT DISTINCT sk0.dimkey
FROM chan_skey sk0
WHERE sk0.dimval = TO_CHAR(c.channel_total)
AND sk0.lvlid = 0)
FROM channels c;
COMMIT;
DROP table chan_skey;
prompt Create SALES with references to the surrogate keys
DROP TABLE sales_new;
CREATE TABLE sales_new
PARTITION BY RANGE (time_id_key)
(PARTITION sales_q1_1998 VALUES LESS THAN (91),
PARTITION sales_q2_1998 VALUES LESS THAN (182),
PARTITION sales_q3_1998 VALUES LESS THAN (274),
PARTITION sales_q4_1998 VALUES LESS THAN (366),
PARTITION sales_q1_1999 VALUES LESS THAN (456),
PARTITION sales_q2_1999 VALUES LESS THAN (547),
PARTITION sales_q3_1999 VALUES LESS THAN (639),
PARTITION sales_q4_1999 VALUES LESS THAN (731),
PARTITION sales_q1_2000 VALUES LESS THAN (822),
PARTITION sales_q2_2000 VALUES LESS THAN (913),
PARTITION sales_q3_2000 VALUES LESS THAN (1005),
PARTITION sales_q4_2000 VALUES LESS THAN (MAXVALUE))
2
AS
SELECT products_new.prod_id_key,
customers_new.cust_id_key,
times_new.time_id_key,
channels_new.channel_id_key,
promotions_new.promo_id_key,
sales.quantity_sold,
sales.amount_sold
FROM products_new,
customers_new,
times_new,
channels_new,
promotions_new,
sales
WHERE sales.prod_id = products_new.prod_id AND
sales.cust_id = customers_new.cust_id AND
sales.time_id = times_new.time_id AND
sales.channel_id = channels_new.channel_id AND
sales.promo_id = promotions_new.promo_id;
REM DROP TABLE sales;
REM RENAME table sales_new sales;
アクセス構造作成のための Oracle 構成パラメータお
よびセッションの設定
キューブ集計の最適化
init.ora パラメータ
パラメータ・ファイルに次の変更を行います。
db_file_multiblock_read_count=n
ここで、n はハードウェアによって異なる数値です。推奨値は、8KB ブロックに掛け
るとディスク・ストライプ幅と等しくなる数です。
pga_aggregate_target=n
ここで、n は実メモリーの 45% です。
例 : 512MB の RAM の場合、230MB
sga_aggregate_target=n
ここで、n は実メモリーの 50% です。
例 : 512MB の RAM の場合、256MB
parallel_automatic_tuning=true
このパラメータは削除するか、必ず true に設定してください。
#parallel_max_servers = x
#parallel_min_servers = y
これらの設定は必ずコメントアウトしてください。
workarea_size_policy=AUTO
このパラメータは必ず AUTO に設定してください。
セッション・パラメータ
アクセス構造の作成スクリプトを実行する SQL*Plus のセッションでは、次のようにパラ
メータを設定します。
alter session enable parallel query;
alter session enable parallel dml;
3
alter session set "query_rewrite_enabled"=FALSE;
アクセス構造のスクリプト・パッケージ
表のサマリー・データ構造の作成および管理については、
『Oracle OLAP アプリケーショ
ン開発者ガイド』を参照してください。
問合せ操作の最適化
init.ora パラメータ
RDBMS 10.1.0.3 パッチ・セットの init.ora パラメータ・ファイルに、次の変更を行い
ます。データベース初期化設定の変更に関する詳細は、3 ページの「init.ora パラメータ」
を参照してください。
pga_aggregate_target=n
ここで、n は実メモリーの 25% です。
例 : 512MB の RAM の場合、128MB
ALTER_SESSION パラメータ
SYS.OLAP$ALTER_SESSION 表で、必ず次の設定を行います。
star_transformation_enabled=true
query_rewrite_enabled=true
query_rewrite_integrity=stale_tolerated
optimizer_index_cost_adj=25
_subquery_pruning_enabled=true
_no_or_expansion=true
_query_cost_rewrite=false
_pre_rewrite_push_pred=true
_generalized_pruning_enabled=true
_union_rewrite_for_gs=force
4