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

ぼやき漫才みたいな感じですが、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 |        |             |
==========================================================================================

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

偏りなし

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...:)

津島博士のパフォーマンス講座 第10回 パーティションについて