STAR TRANSFORMATION ENABLED - 日本エクセム株式会社 Oracleパラメータ
SQLチューニングのためには、そのSQLがどのように動いていて、データベースにどのように影響しているのか、を把握する必要があります。『MaxGauge』があれば簡単に状況が把握でき、適切なSQLチューニングができるようになります。
『MaxGauge』の資料はこちらから。
基本情報
Parameter情報
Syntax：STAR_TRANSFORMATION_ENABLED = TRUE|TEMP_DISABLE|FALSE
設定方法：・Parameter File
・ALTER SYSTEM SET STAR_TRANSFORMATION_ENABLED =
・ALTER SESSION SET STAR_TRANSFORMATION_ENABLED =バージョンとデフォルト
9iR2 : FALSE
10gR1 : FALSE
10gR2 : FALSE
11g : FALSE説明
Star Transformationを使用するかどうかを指定します。
・TRUE：Star Transformationを使用します。
・TEMP_DISABLE：Star Transformationを使用しますが、Temp Table Transformation作業（Operation）は、使用していません。
Temp Table TransformationはDimension Tableを二度アクセスする負担を軽減するために、アクセス結果を
Temp Tableに保存することを意味します。
一般的に、性能に有利ですので、性能的に問題のある動作をしている場合は、この値を用いて制御します。
・FALSE：Star Transformationを使用しません。注意
Star Transformation
Star Transformationを使用するには、次のような条件を満たさなければなりません。
・FactテーブルとDimensionテーブルで構成されStar Schemaを使用します。
・FactテーブルとDimensionテーブルはParent-Child Relationshipを明示的に持たなければなりません。
つまり、Primary key、Foreign Key Constraintを与えなければならないのです。
・FactテーブルのDimension Keyはすべて単一の列からなるBitmap Indexを持たなければなりません。例
Star Transformationを使用するには、次のようにParent/ Child Relationshipを持つStar Schemaを設定する必要があります。
-- create objects
-- this is star schema
drop table t_fact purge;
drop table t_dim1 purge;
drop table t_dim2 purge;
create table t_fact(dim1_key int, dim2_key int, dim3_key int, value1 int, value2 int);
alter table t_fact add constraint pk_fact primary key(dim1_key, dim2_key, dim3_key);
create table t_dim1(dim1_key int primary key, value varchar2(20));
create table t_dim2(dim2_key int primary key, value varchar2(20));
alter table t_fact add constraint fk_dim1
foreign key (dim1_key)
references t_dim1(dim1_key);
alter table t_fact add constraint fk_dim2
foreign key (dim2_key)
references t_dim2(dim2_key);
-- generate data
-- generate dimension data
insert into t_dim1
select
level as dim1_key,
'area'||level as value
from
dual
connect by level <= 100;
insert into t_dim2
select
level as dim2_key,
'code'||level as value
from
dual
connect by level <= 100;
-- generate fact data
insert /*+ append */ into t_fact
select
t_dim1.dim1_key,
t_dim2.dim2_key,
dummy.dim3_key,
dbms_random.random as value1,
dbms_random.random as value2
from
t_dim1, t_dim2,
(select level as dim3_key from dual connect by level <= 10) dummy
;
commit;また、Factテーブルの各Dimension KeyはBitmap Indexを持たなければなりません。
-- create bitmap index
create bitmap index t_fact_bidx1 on t_fact(dim1_key);
create bitmap index t_fact_bidx2 on t_fact(dim2_key);
-- gather statistics
exec dbms_stats.gather_table_stats(user, 't_dim1', cascade=>true, no_invalidate=>false);
exec dbms_stats.gather_table_stats(user, 't_dim2', cascade=>true, no_invalidate=>false);
exec dbms_stats.gather_table_stats(user, 't_fact', cascade=>true, no_invalidate=>false);Star Transformationを使用していない場合は、次のような実行計画に続きます。
-- case 1: no star transformation
-- disable start transformation
alter session set star_transformation_enabled = false;
select /*+ gather_plan_statistics */
d1.value as area,
d2.value as code,
sum(f.value1) as sum_value1,
max(f.value2) as max_value2,
count(*) as counts
from
t_fact f,
t_dim1 d1,
t_dim2 d2
where
f.dim1_key = d1.dim1_key and
f.dim2_key = d2.dim2_key and
d1.value like 'area1%' and
d2.value = 'code1'
group by
d1.value, d2.value
;
select * from table(dbms_xplan.display_cursor(null, null, 'iostats last'));
-------------------------------------------------------------------------------------------------
| Id | Operation | Name |Starts| E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 1 |HASH GROUP BY | | 1 | 9 | 12 |00:00:00.05 | 415 |
|* 2 |HASH JOIN | | 1 | 119 | 120 |00:00:00.05 | 415 |
|* 3 |TABLE ACCESS FULL |T_DIM1 | 1 | 12 | 12 |00:00:00.01 | 7 |
| 4 |TABLE ACCESS BY INDEX ROWID |T_FACT | 1 | 986 | 1000 |00:00:00.03 | 408 |
| 5 |NESTED LOOPS | | 1 | 986 | 1002 |00:00:00.02 | 9 |
|* 6 |TABLE ACCESS FULL |T_DIM2 | 1 | 1 | 1 |00:00:00.01 | 7 |
| 7 |BITMAP CONVERSION TO ROWIDS | | 1 | | 1000 |00:00:00.01 | 2 |
|* 8 |BITMAP INDEX SINGLE VALUE |T_FACT_BIDX2| 1 | | 1 |00:00:00.01 | 2 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("F"."DIM1_KEY"="D1"."DIM1_KEY")
3 - filter("D1"."VALUE" LIKE 'area1%')
6 - filter("D2"."VALUE"='code1')
8 - access("F"."DIM2_KEY"="D2"."DIM2_KEY")Star Transformationを使用する場合には、次のような実行計画に続きます。Bitmap演算を利用してDimension値をまず抽出し、再びDimension値とFact値を抽出する変換手法を注意深く観察する必要があります。
-- case 2: star transformation
alter session set star_transformation_enabled = true;
select /*+ gather_plan_statistics */
d1.value as area,
d2.value as code,
sum(f.value1) as sum_value1,
max(f.value2) as max_value2,
count(*) as counts
from
t_fact f,
t_dim1 d1,
t_dim2 d2
where
f.dim1_key = d1.dim1_key and
f.dim2_key = d2.dim2_key and
d1.value like 'area1%' and
d2.value = 'code1'
group by
d1.value, d2.value
;
select * from table(dbms_xplan.display_cursor(null, null, 'iostats last')); ----------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
| 1 |HASH GROUP BY | | 1 | 1 | 12 |00:00:00.01 | 106 |
|* 2 |HASH JOIN | | 1 | 1 | 120 |00:00:00.01 | 106 |
|* 3 |HASH JOIN | | 1 | 1 | 120 |00:00:00.01 | 99 |
|* 4 |TABLE ACCESS FULL | T_DIM2 | 1 | 1 | 1 |00:00:00.01 | 7 |
| 5 |TABLE ACCESS BY INDEX ROWID| T_FACT | 1 | 119 | 120 |00:00:00.01 | 92 |
| 6 |BITMAP CONVERSION TO ROWIDS| | 1 | | 120 |00:00:00.01 | 32 |
| 7 |BITMAP AND | | 1 | | 1 |00:00:00.01 | 32 |
| 8 |BITMAP MERGE | | 1 | | 1 |00:00:00.01 | 9 |
| 9 |BITMAP KEY ITERATION | | 1 | | 1 |00:00:00.01 | 9 |
|* 10 |TABLE ACCESS FULL |T_DIM2 | 1 | 1 | 1 |00:00:00.01 | 7 |
|* 11 |BITMAP INDEX RANGE SCAN |T_FACT_BIDX2| 1 | | 1 |00:00:00.01 | 2 |
| 12 |BITMAP MERGE | | 1 | | 1 |00:00:00.01 | 23 |
| 13 |BITMAP KEY ITERATION | | 1 | | 12 |00:00:00.01 | 23 |
|* 14 |TABLE ACCESS FULL |T_DIM1 | 1 | 12 | 12 |00:00:00.01 | 7 |
|* 15 |BITMAP INDEX RANGE SCAN |T_FACT_BIDX1| 12 | | 12 |00:00:00.01 | 16 |
|* 16 |TABLE ACCESS FULL |T_DIM1 | 1 | 12 | 12 |00:00:00.01 | 7 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("F"."DIM1_KEY"="D1"."DIM1_KEY")
3 - access("F"."DIM2_KEY"="D2"."DIM2_KEY")
4 - filter("D2"."VALUE"='code1')
10 - filter("D2"."VALUE"='code1')
11 - access("F"."DIM2_KEY"="D2"."DIM2_KEY")
14 - filter("D1"."VALUE" LIKE 'area1%')
15 - access("F"."DIM1_KEY"="D1"."DIM1_KEY")
16 - filter("D1"."VALUE" LIKE 'area1%')関連情報
1.Bitmap Join IndexSQLチューニングのためには、そのSQLがどのように動いていて、データベースにどのように影響しているのか、を把握する必要があります。『MaxGauge』があれば簡単に状況が把握でき、適切なSQLチューニングができるようになります。
『MaxGauge』の資料はこちらから。
