« Join Elimination(結合の排除)と 参照整合性制約 / FAQ | トップページ | in-memory関連の謎パラメータ 18c »

2019年3月21日 (木)

ハッシュパーティションでのデータの偏り / FAQ

ぼやき漫才みたいな感じですが、Oracleに限らず、ハッシュパーティションでパーティション間のデータを均一にしたいなら、ユニークな値かそれに準ずる列を選ぶべきなわけですが、なにを間違ってしまったのか、稀ではありますが、少々残念なことになっていすることもあります。
とは言え、早めに気づけば影響も小さくて済むわけで:)


というわけで、今回はそんなおはなし。

id_code列の値はユニーク
ORCL@SCOTT> select count(*),count(distinct id_code) from org;

COUNT(*) COUNT(DISTINCTID_CODE)
---------- ----------------------
400000 400000
type列の値は、一意性がなくカーディナリティーも低い、かつ、大きな偏りがある。。
ORCL@SCOTT> select type,count(1) from org group by type;

type COUNT(1)
---------- ----------
1 60000
2 60000
9 60000
0 220000
ハッシュパーティションを選択する主な理由は、パーティションへのデータの均一分散なので、列の値がユニークな列をパーティションキーとしてパーティション化することが多いわけですが、。。
以下は、ハッシュキーに一意な値を持つ列を選択した場合の例
ORCL@SCOTT> r
1 create table hash_p_tab
2 partition by hash(id_code)
3 (
4 partition hash_p_tab_p1
5 ,partition hash_p_tab_p2
6 ,partition hash_p_tab_p3
7 ,partition hash_p_tab_p4
8 )
9 as select
10 id_code
11 ,foo
12 ,type
13 from
14* org

Table created.

ORCL@SCOTT> alter table hash_p_tab add constraint gpk_hash_p_tab primary key(id_code) using index global;

Table altered.

ORCL@SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'hash_p_tab',cascade=>true,no_invalidate=>false,granularity=>'ALL');


ORCL@SCOTT> select table_name,partition_name,num_rows from user_tab_partitions order by 1,2;

TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
HASH_P_TAB HASH_P_TAB_P1 99901
HASH_P_TAB HASH_P_TAB_P2 100194
HASH_P_TAB HASH_P_TAB_P3 100056
HASH_P_TAB HASH_P_TAB_P4 99849


しかし、値の分布に偏りのあるカーディナリティの低い列を選んで残念なことになっているケースも稀にあったりします。

なぜ、ハッシュキーにこの列を選んだんだ! みたいな。。。

そんな時は、設計した人に聞くしかないです。何がやりたかったのかを。。。私に聞かれてもハッシュキーの選択をミスったんですよねーたぶん、としか言えないので。
ORCL@SCOTT> r
1 create table hash_p_tab_skew
2 partition by hash(type)
3 (
4 partition hash_p_tab_skew_p1
5 ,partition hash_p_tab_skew_p2
6 ,partition hash_p_tab_skew_p3
7 ,partition hash_p_tab_skew_p4
8 )
9 as select
10 id_code
11 ,foo
12 ,type
13 from
14* org

Table created.

ORCL@SCOTT> alter table hash_p_tab_skew add constraint gpk_hash_p_tab_skew primary key(id_code) using index global;

Table altered.

ORCL@SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'hash_p_tab_skew',cascade=>true,no_invalidate=>false,granularity=>'ALL');

PL/SQL procedure successfully completed.

ORCL@SCOTT> select table_name,partition_name,num_rows from user_tab_partitions where table_name = upper('hash_p_tab_skew') order by 1,2

TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
HASH_P_TAB_SKEW HASH_P_TAB_SKEW_P1 0
HASH_P_TAB_SKEW HASH_P_TAB_SKEW_P2 280000
HASH_P_TAB_SKEW HASH_P_TAB_SKEW_P3 60000
HASH_P_TAB_SKEW HASH_P_TAB_SKEW_P4 60000
で、データを均一にパーティションに分散させることをなんとなーく、イメージしつつ、上記のようなミスをしてしまうと、パラレルクエリーなどで最もデータの多いパーティションの処理時間に引っ張られて想定より処理時間が長くなるなんてことも。。。あるわけで
例がイケてないけど(気にしないでw)、パラレルサーバーのスレーブ間でbuffer gets部分を見ていただくと偏りはわかりやすいはず。(赤字部分):)
偏りあり
orcl2@SCOTT> @sql_skew
1 select
2 /*+
3 monitor
4 parallel(4)
5 */
6 count(1)
7 from
8 hash_p_tab_skew t01
9 inner join hash_p_tab_skew2 t02
10 on
11 t01.id_code = t02.id_code
12* and t01.type = t02.type


Parallel Execution Details (DOP=4 , Servers Allocated=4)
==========================================================================================
| Name | Type | Server# | Elapsed | Cpu | Other | Buffer | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Gets | (sample #) |
==========================================================================================
| PX Coordinator | QC | | 0.02 | 0.00 | 0.02 | 72 | |
| p000 | Set 1 | 1 | 0.18 | 0.17 | 0.01 | 1940 | |
| p001 | Set 1 | 2 | 0.05 | 0.05 | 0.00 | 448 | |
| p002 | Set 1 | 3 | 0.05 | 0.05 | 0.00 | 448 | |
| p003 | Set 1 | 4 | 0.00 | 0.00 | 0.00 | | |
==========================================================================================

偏りの悪影響のイメージはこんな感じ
20190321-165450
20190321-165622

偏りなし
orcl2@SCOTT> @sql_noskew
1 select
2 /*+
3 monitor
4 parallel(4)
5 */
6 count(1)
7 from
8 hash_p_tab t01
9 inner join hash_p_tab2 t02
10 on
11 t01.id_code = t02.id_code
12* and t01.type = t02.type


Parallel Execution Details (DOP=4 , Servers Allocated=4)
==========================================================================================
| Name | Type | Server# | Elapsed | Cpu | Other | Buffer | Wait Events |
| | | | Time(s) | Time(s) | Waits(s) | Gets | (sample #) |
==========================================================================================
| PX Coordinator | QC | | 0.01 | 0.00 | 0.00 | 96 | |
| p000 | Set 1 | 1 | 0.08 | 0.08 | | 714 | |
| p001 | Set 1 | 2 | 0.08 | 0.08 | 0.00 | 714 | |
| p002 | Set 1 | 3 | 0.08 | 0.07 | 0.01 | 714 | |
| p003 | Set 1 | 4 | 0.08 | 0.08 | | 712 | |
==========================================================================================
パーティション毎にぞれぞれ得手不得手があります。そこんとこを把握したうえで、有効活用したいものですよね。(パーティションもいろいろ進化してきて便利になってきたわけですが、その分わかりにくいところも増えてきて理解するのに大変だったり 18cのも差分は把握しといたほうがいいかw...:)

|

コメント

コメントを書く