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を実行するときに何が起こるか見てみましょう。

2014年11月15日土曜日

db tech showcase 2014 東京に参加しました。

年に2回のデータベースの祭典、db tech showcaseに参加してきました。

毎年春に大阪、秋に東京で開催され、セールストーク一切無し、Oracle Aceをはじめとした多くのトップ技術者のセッションを聞ける貴重な機会です。初回開催から欠かさず参加し、今回は初日と二日目に参加しました。
今回、Oracleのセッションでは多くの海外からのスピーカーが来ました。
OracleのパフォーマンスチューニングではCraig Shallahamarさん、AskTomで有名なTom Kyteさん、
新たにPythian Asia Pasificの代表に就任されたバックアップリカバリのプロフェッショナルFrancisco Alvarezさんなど
蒼蒼たるメンバーです。

聴講したセッションは以下の通りです(敬称略)。

11/11
Memory Structure Control: How it works (latches & mutexes) Craig Shallahamer
About server sizing and implementing Hard Partitioning technologies to stop over-licensing Bjoern Rost
Everyone talks about DR / But why so few implement it Francisco Munos Alvarez
Improving the performance of PL/SQL function calls from SQL Tim Hall
Scalability with MariaDB and MaxScale Colin Charles
[7-eleven] DR with Physical Replication Technology (+DEMO) Anton Els/大橋美幸
[DoBoken] 月間10億PVから学んだMongoDBアンチパターン 磯部有司

11/12
Oracle Optimizer: What’s new in Oracle Database 12c? Tom Kyte
Hitchhiker’s Guide to Oracle Database Upgrades Mike Dietrich
Why Engineered System? Tom Kyte
Multitenant in the Real World Mike Dietrich
Introduction to Time-Based Performance Analysis: Stop the guessing! Craig Shallahamer
PostgreSQLを拡張してみよう 高塚遥
Oracle 12c Backup & Recovery / What is new? Francisco Munos Alvarez

Craigさんのセッションは初日の一コマ目。LatchとMutexの内部構造についての講義でした。
こちらがボディランゲンジを交えてわかりやすく、大変勉強になりました。
2日目のセッションも、AWRの分析手法について丁寧に解説してくださり、大収穫でした。

また、TomasKyteさんのWhy Engineered SystemではExadataの機能紹介があり、
よくわかっていなかったHybrid Columnar Compressionについて勉強になりました。

12.1.0.2から、Exadataの新機能としてZone Mapというのができたと、Kyteさんから紹介がありました。
こちらを併用することで、SmartScanによるストレージでの絞り込み率を上げることに効果があるのだそうです。
また、Twitterを介して別の方からAttribute Clusteringという機能も有用であるとの紹介がありました。

簡単に調べた内容を紹介します。
・Attribute Clustering
ユーザが指定したカラムの順に物理的にソートされることで、隣接したカラムに内容の近いデータが並び、
ストレージでの絞り込みに効果を発揮できる機能。複数表でもOKとのこと。
データをAPPENDヒントで挿入するか、もしくは表のmove操作も可能。
こちらは非Exadataでも使用できるので、また機会を見て検証したいところです。
・Zone Map
StorageIndexでは1Mごとの単位で、しかも頭8桁での値の最大値/最小値のデータが抽出されますが、
ZoneMapではcreate materialized zonemapを作成することで、カラムの特性に合った単位で抽出ができ、
操作できるようになるというものです。
これは期待大です。検証したいですが、環境を整えるのが難しそうですが…。

これらの内容について、以下のサイトで概要が書かれていますので、興味のある方は見てみてください。
http://www.oracle.com/webfolder/technetwork/jp/ondemand/od12c-aug2014/14-DB12102-coretech-DWH-v1.pdf


様々な学びを得られ、懇親会ではOracleAceの方々ともお話ができ、有意義な参加となりました。