2014年11月26日水曜日

12.1.0.2 Zone Maps 入門 Part I

この記事はOracle Ace DirectorのRichard Foote氏のご厚意で氏のブログを翻訳しています。
https://richardfoote.wordpress.com/2014/09/03/12-1-0-2-introduction-to-zone-maps-part-i-map-of-the-problematique/

この記事に関する指摘は、sakitaまでご連絡お願い致します。
また、翻訳は自動翻訳を若干意訳した程度のレベルとなっております。

日本語訳:sakita


==========

Zone Mapは、選択した列の最小値と最大値を表の各「ゾーン」に格納することでテーブルのアクセス時にディスクブロックの「プルーニング」を可能にする、インデックスのような構造の新しい機能である。
ゾーンは、単にテーブル内の連続したブロックの範囲です。Zone MapはExadata Storage Indexの概念と似ていますが、明示的に制御し、維持することができる物理的なオブジェクトです。
また、 Bitmap-Join indexesと同様の方法で、Zone Mapで定義されたテーブルの列の属性をキーとして外部結合をすることができます。
Zone Mapは同じく新機能として導入されたAttribute Clusteringの一部として作成することもできます。(前回の記事で書いています)。
興奮してしまいますが、一つ注意が必要です。Zone Mapは残念ながらExadataまたはSuperCluster限定の機能で、Partitioning Optionが必要です。もしそれでもまだご興味がおありでしたら、続きをお読みください。


まずは、BIG_BOWIEテーブルを作成することから始めましょう。この記事ではALBUM_ID列が非常に良くクラスタ化されていることに注目してください。


SQL> create table big_bowie (id number not null, album_id number not null, artist_id number not null, format_id number,
release_date date, total_sales number, description varchar2(100));

Table created.

SQL> create sequence bowie_seq order;

Sequence created.

SQL> create or replace procedure pop_big_bowie as
  begin
    for v_album_id in 1..100 loop
        for v_artist_id in 1..100000 loop
            insert into big_bowie values (bowie_seq.nextval, v_album_id, v_artist_id, ceil(dbms_random.value(0,5)) * 2,
     trunc(sysdate-ceil(dbms_random.value(0,10000))), ceil(dbms_random.value(0,500000)), 'THE RISE AND FALL OF ZIGGY STARDUST AND THE SPIDERS FROM MARS');
        end loop;
          commit;
    end loop;
    commit;
end;
/

Procedure created.

SQL> exec pop_big_bowie

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.gather_table_stats(ownname=>user, tabname=>'BIG_BOWIE', method_opt=>'FOR ALL COLUMNS SIZE 1');




次にALBUM_ID列に通常のB-Treeインデックスを作成します。


SQL> create index dwh_bowie_album_id_i on dwh_bowie(album_id);

Index created.


ALBUM_ID列のなかから、データのちょうど1%を占めている特定の値を探して、次のクエリを実行します。


SQL> select * from big_bowie where album_id=42;

100000 rows selected.

Elapsed: 00:00:00.29

Execution Plan
----------------------------------------------------------
Plan hash value: 1830705794

------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                      |   100K|  8984K|  1554   (1)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| BIG_BOWIE            |   100K|  8984K|  1554   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | BIG_BOWIE_ALBUM_ID_I |   100K|       |   201   (2)| 00:00:01 |
------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ALBUM_ID"=42)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1590  consistent gets
          0  physical reads
          0  redo size
    9689464  bytes sent via SQL*Net to client
        760  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed



CBOは優れたクラスタリングのデータがあるのでインデックスを使用し、consistent getsは1590と比較的低いです(arraysizeは5000で設定されています)。

インデックスをinvisibleにして先ほどのクエリを何度か行います。


SQL> alter index big_bowie_album_id_i invisible;

Index altered.

SQL> select * from big_bowie where album_id=42;

100000 rows selected.

Elapsed: 00:00:00.29

Execution Plan
----------------------------------------------------------
Plan hash value: 469213804

---------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |   100K|  8984K|  3269  (12)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL| BIG_BOWIE |   100K|  8984K|  3269  (12)| 00:00:01 |
---------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   1 - storage("ALBUM_ID"=42)
       filter("ALBUM_ID"=42)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     135085  consistent gets
     135068  physical reads
          0  redo size
    3130019  bytes sent via SQL*Net to client
        760  bytes received via SQL*Net from client
         21  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     100000  rows processed


実行時間はExadata Storage Indexes (SI) と同様、全表スキャン(FTS)であるにもかかわらず、ほとんど同一であることにお気づきでしょう。
データは非常によくクラスタ化された状態で、SIは非常に効果的にアクセスする必要のある物理ブロックを「プルーニング」することができました。
明らかにこれらのクエリのいずれかを実行したセッションのセッション統計を見て確認できます。

SQL> select name , value/1024/1024 MB
from v$statname n,  v$mystat s
where n.statistic# = s.statistic# and
n.name in ('cell physical IO interconnect bytes returned by smart scan', 'cell physical IO bytes saved by storage index');

NAME                                                                     MB
---------------------------------------------------------------- ----------
cell physical IO bytes saved by storage index                    1038.32813
cell physical IO interconnect bytes returned by smart scan       9.56008911


テーブルの大部分は、実際のStorage Indexesの結果として読み飛ばされています。


これから、ALBUM_ID列に基づく基本的なバージョンのZone Mapを作成していきます。


SQL> create materialized zonemap big_bowie_album_id_zm on big_bowie(album_id);

Materialized zonemap created.

Zone Mapはマテリアライズド・ビューと同様に実装され、マテリアライズド・ビューと同様にリフレッシュすることができる独立したテーブル構造です。
これは、Zone Mapのリフレッシュ属性に応じて、Zone Mapと従来のインデックスとの重要な違いであり、DML操作中に更新されない場合があります。
デフォルトのリフレッシュ属性は、DML操作後に「古く」なることを意味する「REFRESH ON LOAD DATA MOVEMENT」です。
Zone Mapは基本的には、表の各「ゾーン」内の対応する列(複数可)の最小値と最大値を格納します。
Zone Mapの内容を見てみると…(下にあるリストの一部をご覧ください)


SQL> select zone_id$, min_1_album_id, max_1_album_id, zone_rows$

from big_bowie_album_id_zm;

       ZONE_ID$ MIN_1_ALBUM_ID MAX_1_ALBUM_ID ZONE_ROWS$
--------------- -------------- -------------- ----------
   385855025152              1              2      66234
   385855029250              5              6      56715
   385855029251              7              7      76562
   385855025155              7              8      76632
   385855004675              8              9      76633
   385855025161             21             22      75615
   385855004684             29             29      75582
   385855004685             31             32      75545
   385855004687             35             36      75617
   385855029267             43             44      75615
   385855029270             50             50      75481
   385855029275             61             62      75616
   385855025179             62             63      75530
   385855029284             81             82      75615
   385855029285             84             84      75480
   385855004710             87             88      75616
   385855004711             90             91      75484
   385855029293            100            100      75799
   385855029254             13             14      75615
   385855029255             16             16      75481
   385855004681             22             22      75480
   385855004682             24             25      75616


...

   385855025184             73             74      75615
   385855004705             76             77      75615
   385855029283             79             80      75615
   385855029286             86             87      75616
   385855029287             88             89      75618
   385855004714             97             97      75771
   385855029295            100            100      15871



134 rows selected.


いくつかの注意点を書きます。
まず、Zone Mapのために保存されるデータの量はごくわずかです。各Zoneには8Mのストレージが対応し、今回はおおよそ75500行です。これはZone Mapと従来のB-Treeインデックスとの重要な違いです。
インデックスのエントリは参照される行ごとに有しますが、Zone MapはテーブルのみのZoneごとに1つのエントリを有します。
また、ALBUM_ID行は非常によくクラスタ化されているので、同じZone内ではALBUM_IDの値が非常に狭い範囲の最小値/最大値になっています。これは、Zone MapによってOracleは非常に簡単に興味のあるデータを含む大多数のZoneからのストレージのアクセスを「プルーニング」することにおいて極めて有効であることを意味します。

このZone Mapがいかに小さいか確認しましょう。


SQL> select segment_name, segment_type, bytes from dba_segments where segment_name like 'BIG_BOWIE%';

SEGMENT_NAME              SEGMENT_TYPE     BLOCKS      BYTES
------------------------- ------------ ---------- ----------
BIG_BOWIE                 TABLE            139264 1140850688
BIG_BOWIE_ALBUM_ID_I      INDEX             20480  167772160
BIG_BOWIE_ALBUM_ID_ZM     TABLE                 8      65536


テーブルやインデックスがはるかに大きいのに対し、Zone Mapは64Kのエクステント1つしか必要ではありません。事実、実際に134行はすべて8Kのデータブロックに収めることができます!
2つの新しいデータ・ディクショナリビューはZone Mapに詳細を示します。


SQL> select zonemap_name, pruning, refresh_mode, invalid, stale, unusable
from dba_zonemaps where zonemap_name='BIG_BOWIE_ALBUM_ID_ZM';

ZONEMAP_NAME              PRUNING  REFRESH_MODE      INVALID STALE   UNUSABLE
------------------------- -------- ----------------- ------- ------- --------
BIG_BOWIE_ALBUM_ID_ZM     ENABLED  LOAD DATAMOVEMENT NO      NO      NO


従って、Zone Mapの現在の状態がDBA_ZONEMAPSで見ることができます。


SQL> select measure, position_in_select, agg_function, agg_column_name
from dba_zonemap_measures where zonemap_name='BIG_BOWIE_ALBUM_ID_ZM';

MEASURE                        POSITION_IN_SELECT AGG_FUNCTION  AGG_COLUMN_NAME
------------------------------ ------------------ ------------- ---------------
"BOWIE"."BIG_BOWIE"."ALBUM_ID"                  3 MAX           MAX_1_ALBUM_ID
"BOWIE"."BIG_BOWIE"."ALBUM_ID"                  2 MIN           MIN_1_ALBUM_ID


さらに、Zone Map内の列の詳細はDBA_ZONEMAP_MEASURESで見ることができます。

BIG_BOWIEテーブルで同じクエリを再実行してみましょう。


SQL> select * from big_bowie where album_id=42;

100000 rows selected.

Elapsed: 00:00:00.28

Execution Plan
----------------------------------------------------------
Plan hash value: 1980960934

----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |           |   100K|  8984K|  3269  (12)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL WITH ZONEMAP| BIG_BOWIE |   100K|  8984K|  3269  (12)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("ALBUM_ID"=42)
filter(SYS_ZMAP_FILTER('/* ZM_PRUNING */ SELECT "ZONE_ID$", CASE WHEN
BITAND(zm."ZONE_STATE$",1)=1 THEN 1 ELSE CASE WHEN (zm."MIN_1_ALBUM_ID" > :1 OR
zm."MAX_1_ALBUM_ID" < :2) THEN 3 ELSE 2 END END FROM "BOWIE"."BIG_BOWIE_ALBUM_ID_ZM" zm
WHERE zm."ZONE_LEVEL$"=0 ORDER BY zm."ZONE_ID$"',SYS_OP_ZONE_ID(ROWID),42,42)<3 AND
"ALBUM_ID"=42)
Statistics
----------------------------------------------------------
141  recursive calls
0  db block gets
2364  consistent gets
0  physical reads
0  redo size
3130019  bytes sent via SQL*Net to client
760  bytes received via SQL*Net from client
21  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
100000  rows processed

CBOはFTS(TABLE ACCESS STORAGE FULL WITH ZONEMAPとfilter predicate informationを見てください)中にZone Mapを使用しています。
consistent getsではデータベースのインデックスを介した場合と比較して増加(2364 vs 1590)しますが、139000近くの奇数のブロックがどこにも無いので前回に匹敵する性能であり、そんなに悪くはありません。
増えたconsistent getsはZone全体が読み取られる必要があるため必要であり、一方、B-Treeインデックスは対象のデータが含まれている特定のZoneのみ指定することができます。

従ってZone Mapは「十分に良い」結果をもたらしました。
一方、データベースまたはExadata Storage Serverのいずれかをバウンスしたあとであっても、
Zone Mapを維持する方法を制御することができ(後述します)、Zone Mapオブジェクトが常に存在することを保証する非常に最小限のストレージを必要としています。

PartIIでは、ベーステーブル上のいくつかのDMLを実行するときに何が起こるか見てみましょう。

0 件のコメント:

コメントを投稿