2014年12月25日木曜日

12.1.0.2 Zone Maps 入門 Part III

今回の記事は、弊社バルテックのアドベントカレンダーにエントリーしています。
http://www.adventar.org/calendars/414


この記事はOracle Ace DirectorのRichard Foote氏のご厚意で氏のブログを翻訳しています。
https://richardfoote.wordpress.com/2014/11/24/12-1-0-2-introduction-to-zone-maps-part-iii-little-by-little/

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

日本語訳:sakita

==========

私は以前、新しいデータベース機能 Zone Mapが、どのようにExadata Storage Indexと同様の動きをしているのか説明をしてきました。
データが十分にZone Mapまたはインデックスと連携して良くクラスタ化されたときだけ、Storage Index(及び従来のインデックス)のように効果的に動くことを確認しました。
また、Zone Mapと同じデータがテーブルにあることにより、テーブル内の各8MBのZoneの最小値と最大値の範囲は可能な限り狭くすることができ、よりZoneへのアクセスを減らすことができます。
一方で、テーブル内のデータが十分にクラスタ化されていないときには、最小値と最大値のZone Mapの有効性は限定的となり、範囲は非常に広くなってしまいます。

このテーマの最初の記事で私が例に挙げたALBUM_ID列では、データが非常によくクラスタ化されていたため、関連したZone Mapは非常に有効でした。しかし、データのクラスタ化が不十分であった場合はどうでしたでしょうか?
例にあげるために、私は値がテーブル全体にランダムに分散している、不十分にクラスタ化されたARTIST_ID列にZone Mapを作成します。


SQL> create materialized zonemap big_bowie_artist_id_zm on big_bowie(artist_id);
 create materialized zonemap big_bowie_artist_id_zm on big_bowie(artist_id)
 *
 ERROR at line 1:
 ORA-31958: fact table "BOWIE"."BIG_BOWIE" already has a zonemap
 "BOWIE"."BIG_BOWIE_ALBUM_ID_ZM" on it


インデックスとZone Mapのもう一つの違いは、1つのZone Mapは、テーブルごとに定義されていますが、Zone Mapは複数の列を含めることができます。しかし、私は既にZone MapはALBUM_ID列のみに定義されてきたため、別のものを作成することはできません。

従いまして、私は現在のZone Mapを削除し、ARTIST_IDとALBUM_ID列の両方に基づいた新しいものを作成します:


SQL> drop materialized zonemap big_bowie_album_id_zm;

Materialized zonemap dropped.

SQL> create materialized zonemap big_bowie_zm on big_bowie(album_id, artist_id);

Materialized zonemap created.
   
 SQL> select measure, position_in_select, agg_function, agg_column_name
 from dba_zonemap_measures where zonemap_name='BIG_BOWIE_ZM';

MEASURE              POSITION_IN_SELECT AGG_FUNCTION  AGG_COLUMN_NAME
 -------------------- ------------------ ------------- --------------------
 "BOWIE"."BIG_BOWIE".                  5 MAX           MAX_2_ARTIST_ID
 "ARTIST_ID"

"BOWIE"."BIG_BOWIE".                  4 MIN           MIN_2_ARTIST_ID
 "ARTIST_ID"

"BOWIE"."BIG_BOWIE".                  3 MAX           MAX_1_ALBUM_ID
 "ALBUM_ID"

"BOWIE"."BIG_BOWIE".                  2 MIN           MIN_1_ALBUM_ID
 "ALBUM_ID"


この新しいゾーンマップはARTIST_IDとALBUM_ID列の両方にテーブル内の各ゾーンごとに最小値と最大値を持っています。

最小値/最大値のZone Mapの範囲は、データのクラスタリングの優れた視覚的な表現を提供しています。私はALBUM_ID列のZone Mapの詳細を選択した場合は(下のリストの一部を参照してください)。


SQL> select zone_id$, min_1_album_id, max_1_album_id, zone_rows$ from big_bowie_zm;

ZONE_ID$ MIN_1_ALBUM_ID MAX_1_ALBUM_ID ZONE_ROWS$
 ---------- -------------- -------------- ----------
 3.8586E+11              1              2      66234
 3.8586E+11              5              6      56715
 3.8586E+11              7              7      76562
 3.8586E+11              7              8      76632
 3.8586E+11              8              9      76633
 3.8586E+11             21             22      75615
 3.8586E+11             29             29      75582
 3.8586E+11             31             32      75545
 3.8586E+11             35             36      75617
 3.8586E+11             43             44      75615
 ...

3.8586E+11             76             77      75615
 3.8586E+11             79             80      75615
 3.8586E+11             86             87      75616
 3.8586E+11             88             89      75618
 3.8586E+11             97             97      75771
 3.8586E+11            100            100      15871

134 rows selected.


テーブル内のデータを効果的にALBUM_ID列に基づいて順序付けされます(非常に良くこの列に関連してクラスタ化されている)。各Zoneの最小値/最大値の範囲は極めて狭くなっています。各Zoneには基本的にALBUM_IDの1つまたは2つの異なる値が含まれ、もし特定のALBUM_IDの値だけであれば、Zone Mapは不要なZoneを排除するのに非常に有効です。我々が欲しいものだけです。
しかしながら、クラスタ化が不十分なARTIST_IDカラム(同じ一部分のリスト)のゾーンマップの詳細を見てみると:

SQL> select zone_id$, min_2_artist_id, max_2_artist_id, zone_rows$ from big_bowie_zm;

ZONE_ID$ MIN_2_ARTIST_ID MAX_2_ARTIST_ID ZONE_ROWS$
 ---------- --------------- --------------- ----------
 3.8586E+11            3661           98244      66234
 3.8586E+11               1          100000      56715
 3.8586E+11            5273           81834      76562
 3.8586E+11               1          100000      76632
 3.8586E+11               1          100000      76633
 3.8586E+11               1          100000      75615
 3.8586E+11            2383           77964      75582
 3.8586E+11               1          100000      75545
 3.8586E+11               1          100000      75617
 3.8586E+11               1          100000      75615
 ...

3.8586E+11               1          100000      75615
 3.8586E+11               1          100000      75615
 3.8586E+11               1          100000      75615
 3.8586E+11               1          100000      75615
 3.8586E+11               1          100000      75616
 3.8586E+11               1          100000      75618
 3.8586E+11            4848           80618      75771
 3.8586E+11           84130          100000      15871

134 rows selected.


Zoneのほとんどは範囲が非常に大きく、多くは実際に最小値が1(実際の最小値)で、最大値が100000(実際の最大)となっています。特定の要求値はZoneのほとんどに存在することとなり、最悪のシナリオです。それによってOracleに対してほとんどのZoneを確認するよう強制し、Zone Mapは完全に効果が無いことをとなります。
もし、特定のARTIST_IDに対して検索クエリを実行したとすると:

SQL> select * from big_bowie where artist_id=42;

100 rows selected.

Elapsed: 00:00:00.69

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

----------------------------------------------------------------------------------------------------
 | Id  | Operation                              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
 ----------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT                       |           |    99 |  9108 |  3291  (13)| 00:00:01 |
 |*  1 |  TABLE ACCESS STORAGE FULL WITH ZONEMAP| BIG_BOWIE |    99 |  9108 |  3291  (13)| 00:00:01 |
 ----------------------------------------------------------------------------------------------------
 Predicate Information (identified by operation id):
 ---------------------------------------------------

1 - storage("ARTIST_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_2_ARTIST_ID" > :1 OR
 zm."MAX_2_ARTIST_ID" < :2) THEN 3 ELSE 2 END END FROM "BOWIE"."BIG_BOWIE_ZM" zm WHERE
 zm."ZONE_LEVEL$"=0 ORDER BY zm."ZONE_ID$"',SYS_OP_ZONE_ID(ROWID),42,42)<3 AND
 "ARTIST_ID"=42)

     
 Statistics
 ----------------------------------------------------------
 141  recursive calls
 0  db block gets
 101614  consistent gets
 0  physical reads
 0  redo size
 5190  bytes sent via SQL*Net to client
 618  bytes received via SQL*Net from client
 8  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)
 100  rows processed


わずか100行の結果を返すために、101,614もの非常に高いconsistent getsを行うことを余儀なくされているのが確認できます。consistent getsが2364(訳注:効果的なZone Mapのときの値)よりもはるかに高く、100000行の結果をすべて返す際のconsistents gets 135,085と比較して大差ありません。
ARTIST_ID列に基づいてこれらのクエリのパフォーマンスを改善する必要があります...

このZone Mapを削除しましょう:


SQL> drop materialized zonemap big_bowie_zm;

Materialized zonemap dropped.

そして、ARTIST_ID行のデータ順にクラスタ化されるように物理的なクラスタリングを変更しましょう。


SQL> alter table big_bowie add clustering by linear order(artist_id, album_id) with materialized zonemap;

Table altered.


さて、このテーブルに新しいZone Mapを基にしたクラスタリング属性を追加します(以前の記事で説明しました)。


SQL> select zonemap_name from dba_zonemaps where fact_table='BIG_BOWIE';

ZONEMAP_NAME
---------------
ZMAP$_BIG_BOWIE

SQL> select zonemap_name, pruning, with_clustering, invalid, stale, unusable
from dba_zonemaps where zonemap_name = 'ZMAP$_BIG_BOWIE';

ZONEMAP_NAME    PRUNING  WITH_CLUSTERING INVALID STALE   UNUSABLE
--------------- -------- --------------- ------- ------- --------
ZMAP$_BIG_BOWIE ENABLED  YES             NO      NO      NO


実際にはテーブルを再編成していませんが、テーブル行は以前と同じようにクラスタ化されています。:

SQL> select zone_id$, min_2_album_id, max_2_album_id, zone_rows$ from zmap$_big_bowie;

ZONE_ID$ MIN_2_ALBUM_ID MAX_2_ALBUM_ID ZONE_ROWS$
---------- -------------- -------------- ----------
3.8586E+11             43             44      75615
3.8586E+11              1              2      66234
3.8586E+11             81             82      75615
3.8586E+11             29             29      75582
3.8586E+11             50             50      75481
3.8586E+11             90             91      75484
3.8586E+11              5              6      56715
3.8586E+11              7              8      76632
3.8586E+11              8              9      76633
3.8586E+11             16             16      75481
...

3.8586E+11             44             44      75480
3.8586E+11             82             83      75616
3.8586E+11            100            100      15871
3.8586E+11             34             35      75576
3.8586E+11             14             15      75615
3.8586E+11             33             34      75616
3.8586E+11              3              5      75707

134 rows selected.

SQL> select zone_id$, min_1_artist_id, max_1_artist_id, zone_rows$ from zmap$_big_bowie;

ZONE_ID$ MIN_1_ARTIST_ID MAX_1_ARTIST_ID ZONE_ROWS$
---------- --------------- --------------- ----------
3.8586E+11               1          100000      75545
3.8586E+11               1          100000      75616
3.8586E+11               1          100000      75617
3.8586E+11               1          100000      75911
3.8586E+11               1          100000      75616
3.8586E+11               1          100000      75616
3.8586E+11               1          100000      75615
3.8586E+11               1          100000      75616
3.8586E+11             132           75743      75612
3.8586E+11               1          100000      75615
...

3.8586E+11               1          100000      66296
3.8586E+11               1          100000      75615
3.8586E+11            2360           96960      75701
3.8586E+11               1          100000      75615
3.8586E+11               1          100000      75616
3.8586E+11           23432           98911      75480
3.8586E+11               1          100000      75791
3.8586E+11           21104           96583      75480

134 rows selected.

テーブルを再編成した場合でも、クラスタリング属性を有効にできるようにします:


SQL> alter table big_bowie move;

Table altered.

Zone Mapの特性が劇的に変化しているに気づくでしょう。以前に良くクラスタ化されたALBUM_ID列無効となり、すべての範囲を効果的に完全な最小値/最大値からなるZone Mapができています。



SQL> select zone_id$, min_2_album_id, max_2_album_id, zone_rows$ from zmap$_big_bowie;

ZONE_ID$ MIN_2_ALBUM_ID MAX_2_ALBUM_ID ZONE_ROWS$
---------- -------------- -------------- ----------
3.9704E+11              1            142      21185
3.9704E+11              1            100       9452
3.9704E+11              1            100      76516
3.9704E+11              1            100      75501
3.9704E+11              1            100      75497
3.9704E+11              1            100      75501
3.9704E+11              1            100      75499
3.9704E+11              1            100      75504
3.9704E+11              1            100      75500
3.9704E+11              1            100      75501
...

3.9704E+11              1            100      75503
3.9704E+11              1            100      75498
3.9704E+11              1            100      75501
3.9704E+11              1            100      75501
3.9704E+11              1            100      75501
3.9704E+11              1            100      75501
3.9704E+11              1            100      75794

144 rows selected.

ARTIST_ID列に以前にあった効果的でないZone Mapは、はるかに効果的な小さい範囲の最小値/最大値となっているZoneとなっています:

SQL> select zone_id$, min_1_artist_id, max_1_artist_id, zone_rows$ from zmap$_big_bowie;

ZONE_ID$ MIN_1_ARTIST_ID MAX_1_ARTIST_ID ZONE_ROWS$
---------- --------------- --------------- ----------
3.9704E+11              67            1036      21185
3.9704E+11            2359            2453       9452
3.9704E+11            8341            9106      76516
3.9704E+11           18933           19688      75501
3.9704E+11           22708           23463      75497
3.9704E+11           26483           27238      75501
3.9704E+11           27238           27993      75499
3.9704E+11           33278           34033      75504
3.9704E+11           36674           40449      75500
3.9704E+11           38563           39318      75501
...

3.9704E+11           49888           50643      75503
3.9704E+11           62723           63478      75498
3.9704E+11           77824           78579      75501
3.9704E+11           82354           83109      75501
3.9704E+11           88394           89149      75501
3.9704E+11           93679           94434      75501
3.9704E+11           98211           98969      75794

144 rows selected.


先ほどと同じクエリは、Zone Mapがアクセスされてから、ほぼすべてのZoneを排除することができるようになり、はるかに速く実行されます。



SQL> select * from big_bowie where artist_id=42;

100 rows selected.

Elapsed: 00:00:00.02

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

----------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |           |    99 |  9108 |  3291  (13)| 00:00:01 |
|*  1 |  TABLE ACCESS STORAGE FULL WITH ZONEMAP| BIG_BOWIE |    99 |  9108 |  3291  (13)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

1 - storage("ARTIST_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_ARTIST_ID" > :1 OR
zm."MAX_1_ARTIST_ID" < :2) THEN 3 ELSE 2 END END FROM "BOWIE"."ZMAP$_BIG_BOWIE" zm WHERE
zm."ZONE_LEVEL$"=0 ORDER BY zm."ZONE_ID$"',SYS_OP_ZONE_ID(ROWID),42,42)<3 AND
"ARTIST_ID"=42)
Statistics
----------------------------------------------------------
187  recursive calls
0  db block gets
175  consistent gets
0  physical reads
0  redo size
5190  bytes sent via SQL*Net to client
618  bytes received via SQL*Net from client
8  SQL*Net roundtrips to/from client
9  sorts (memory)
0  sorts (disk)
100  rows processed


consistent getsは以前の101,614から175に劇的に減少しています。
データのクラスタリングを変更すると、一つのことが改善される代わりにかなり悪い何かが共通して起きます。以前のALBUM_ID列に基づいた効率的なアクセスは以前と比較してどうでしょうか。


SQL> select * from big_bowie where album_id = 42;

100000 rows selected.

Elapsed: 00:00:01.27

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_2_ALBUM_ID" > :1 OR
zm."MAX_2_ALBUM_ID" < :2) THEN 3 ELSE 2 END END FROM "BOWIE"."ZMAP$_BIG_BOWIE" zm WHERE
zm."ZONE_LEVEL$"=0 ORDER BY zm."ZONE_ID$"',SYS_OP_ZONE_ID(ROWID),42,42)<3 AND "ALBUM_ID"=42)

Statistics
----------------------------------------------------------
187  recursive calls
0  db block gets
141568  consistent gets
0  physical reads
0  redo size
4399566  bytes sent via SQL*Net to client
73878  bytes received via SQL*Net from client
6668  SQL*Net roundtrips to/from client
9  sorts (memory)
0  sorts (disk)
100000  rows processed

今、consistent getsが以前の2,364からなんと、141,568に上昇しています。
従って、Zone Mapは、データベースのインデックスとのExadata Storage Indexのように、I/Oを減らすのに非常に有益でありますが、その有効性はデータのクラスタリングに非常に大きく依存をします。

2014年12月20日土曜日

sedとawkで一行シェルプログラミング

今回の記事は、弊社バルテックのアドベントカレンダーにエントリーしています。
http://www.adventar.org/calendars/414

今回のエントリでは趣向を変えてプログラミングの話をしてみようと思います。
忘年会帰りのsakitaです。

プログラムと言いましても、javaのような高級な言語は使えませんので、主に運用などで使われるシェルプログラムで良く使われるsedやawkを紹介したいと思います。

まずはsed。これは主に文字列変換で使います。

sed -e "s/脂肪/エネルギー/g"

という指定をすると、文字列中の「脂肪」という文言が「エネルギー」という文言に変わります。
ファイルの中身を返還するような場合は、

cat fatboy.txt | sed  -e "s/脂肪/エネルギー/g" > smartboy.txt

とすると、fatboy.txtにある脂肪という文言をエネルギーに変えたテキストがsmartboy.txtに出力されます。

他にも行削除など用途があります。


次にawkです。これ結構いろいろつかえて便利です。
たとえば、こんなファイル。

1      9     18     1
2      9     19     1
....

いわゆる勤怠ファイルです。2列目が出勤時間で3列目が退勤時間で4列目が休憩時間です。
退勤時間だけ出力したい場合、

kintai.txt | awk '{print $3}'

こんなかんじで指定ができます。
計算もできちゃったりします。

たとえば、勤務時間を計算する場合、

kintai,.txt | awk '{print $1 $3-$2-$4}'

こんな感じでうまくいきます。

いろいろ便利そうなのですが、なかなかとっつきづらいかもしれません。
こういうときどうするの? といった疑問があったとき、私が良く見るサイトはこちら。
https://www.usptomo.com/
USP友の会の勉強会スライド。
表計算をしたりソートしたりなどなど、自由自在に1行プログラムで文字列操作をしています。

私もさらに勉強をしてawk使い見習いくらいになりたいものです。

2014年12月18日木曜日

オラクルマスターあれこれ

今回の記事は、弊社バルテックのアドベントカレンダーにエントリーしています。
http://www.adventar.org/calendars/414

オラクルマスターというと、大抵はORACLE MASTER Oracle Databaseを指していると思います。
ただ、Oracle社は買収によってMySQLやJavaなどを手に入れていますので、オラクルマスターは様々な種類がありますので、
今日はオラクルマスターについて紹介をしていきたいと思います。

まずはORACLE MASTER Oracle Databaseから。
資格の種類はBronze、Silver、Gold、Plutinumとありますが、この呼称は日本のみの呼び名だというのはご存知でしょうか?
日本以外では、Oracle Certification Associate(OCA)、Oracle Certification Professional(OCP)、Oracle Certification Master(OCM)となっています。
一つ足りないと思われるかも知れませんが、Bronzeは日本のみの資格となっています。Silverを取るとOCA、Goldを取るとOCPに自動認定されます。
ただし、Silver、Goldなどが認定されるのは日本語で受験した場合となります。英語で受験をした場合はOCA、OCPなどとなりますのでご注意ください(敢えて英語で受ける人は少ないと思いますが)。
続いて、Expertについてですが、これはGoldなどの枠とは別となります。
現在、Database関連ではパフォーマン2014年12月15日~ 2015年5月31スチューニング、RAC、Linuxは参考書が売られているので比較的取得者が多いのではと思います。Linuxは前提資格としてSilver、パフォーマンスチューニングとRACはGoldが前提資格となっています。
中には前提資格なしで認定されるExpertもあります。Implemention、Data Warehouseがありますが、いずれも英語のみの試験です。
毛色は違いますが、PL/SQLもSilverとGoldがあります。ExpertはSQLとSQL Tuningがありますが、こちらも英語のみです。

ORACLE MASTERで最近注目と言えば、Javaではないでしょうか。
Java ProgrammingもBronze、Silver、Goldがあります。参考書も出てきましたね。
他にはOracle LinuxやOracle SolarisといったOSの資格、Exadataなどのエンジニアドシステム、Oracle VMなどの仮想化、E-Business suiteのようなアプリケーションに関するものなど、資格の数は100はあるのではないでしょうか。
これらの資格は参考書が無いものがほとんどなので、取得は大変だと思いますが、そのぶん希少価値はありますので取得すると評価が高まるかもしれません。


最後に、最近のORACLE MASTERの動きについて2つ。
・Recertification(再認定)ポリシーの導入
11月1日より、Oracleのデータベース資格に関して再認定ポリシーが導入されました。
これは他の多くのIT資格と同様、新バージョンに向けて資格を取得していかないと認定者として認めないというものです。
今後は資格終了日から1年以内にアップグレードをしないと、資格認定者とならなくなります。
現在、既にOracle Database 9i以下の資格は終了しており、2015年10月までにアップグレードしないと有効な認定者として認められなくなりました。10gについても2015年2月に終了し、2016年2月を過ぎると有効な認定者となりません。
アップグレードをするのは大変ですが、一方で朗報でもあると思っています。
といいますのは、9iまでは現在のSilver相当をGold、現在のGold相当をPlutinumと呼んでいたため、9i Plutinum取得者と聞くと優秀な技術者に聞こえてしまうので、こういったことが排除されるのは良い傾向かなと思っています。
会社には受験料で迷惑かけることになってしまいますが・・・。

参考資料はこちら
https://blogs.oracle.com/oraclemaster/entry/recertification_policy

・一回目が不合格でももう一度挑戦できるチャンス!
2014年12月15日から2015年5月31日の間、Oracle Database 12c試験、Java試験の一部で1回目の試験が不合格となった場合、同じ試験を期間中に受けた場合2回目が無料になるキャンペーンをやっています。
これを機にOracle Database 12cへのアップグレードに挑戦してみては如何でしょうか?

参考資料はこちら
http://www.oracle.com/jp/education/index-172250-ja.html

2014年12月14日日曜日

ダイレクトロードでFORCE LOGGINGしたときのREDO量見積もり

今回の記事は、弊社バルテックのアドベントカレンダーにエントリーしています。
http://www.adventar.org/calendars/414


ダイレクトパスインサートとは、バッファキャッシュを介さずにデータを落とし込む機能ですので、効率的にデータのインサートができます。
この機能を使用する場合、REDO情報の生成を最小限に抑えるNOLOGGINGモードを指定できます。
ただ、DataGuardやGolden Gateなどで遠方のサーバーへのRedo転送をする際、NOLOGGINGのままだとログ適用エラーやデータアクセスエラーが発生してしまうので、
FORCE LOGGINGにする必要があります。

通常の処理で発生したREDOログ量をそのまま転送REDOログ量と見積もって良いのですが、ダイレクトパスインサートの処理が大量にある場合は、FORCE LOGGINGにした分の転送REDOログ量を見積もる必要が出てきます。


このログ量はAWRの表示で見ることができます。

ダイレクトパスインサート分のREDO量 = block_size * redo size for direct writes - redo size for lost write detection
REDOログ量総計 = 通常のREDO量 + ダイレクト分のREDO量

となります。

ダイレクトパスインサート分のREDO量を見積もっていなかったことで転送ラグが出てしまった、ということが以前あったので、事前の見積もりは大事だなと思いました。

2014年12月11日木曜日

機械学習で遊んでみる

今回の記事は、弊社バルテックのアドベントカレンダーにエントリーしています。
http://www.adventar.org/calendars/414


当Blogはデータベース関連ですが、今日はちょっと毛色の違う話をしてみようと思います。
先々月、代休を取ってマイクロソフトカンファレンスに遊びに行ったとき、機械学習についてセッションを聞きました。
クラウドベースのMicrosoft Azure Machine Learning(ML)が6月に発表され、今はプレビュー版が無料で試せます。
http://azure.microsoft.com/en-us/services/machine-learning/

機械学習について、簡単に説明します。
Wikipediaによると、「センサやデータベースなどからある程度の数のサンプルデータ集合を入力して解析を行い、そのデータから有用な規則、ルール、知識表現、判断基準などを抽出し、アルゴリズムを発展させる。」とあります。
以前、米大手スーパーマーケットチェーン・ウォールマートの分析で「おむつを買った人はビールを買う傾向がある」という分析結果が出たという話を聞いたことがある方は多いのではないでしょうか。
現在ではネットショッピングなどでこれを買った人はこんなものも買っていますという表示が出てくるのを見ることができると思います。
(これ、結構当たっていてつい一緒に購入してしまいそうになります)

しかしながら、こうしたアルゴリズムを構築することは難しいですが、それをクラウドサービスとして提供しようというのが、Microsoft Azure Machine Learning(ML)です。
というわけで、実際に試してみました。

まず、ログインするとビデオによる概要説明が始まります。
それを終えると、こんな画面が出てきます。













さて、データを投入しましょう。
今回は過去半年間の飲み記録(平日のみ)を入力し、いつ飲みに行く傾向があるか調べてみようと思います。
どうでもいいデータですね。。。












このCSVデータを入れていきます。入力ファイルはCSVファイルなどのほか、R言語のデータも入れることができるようです。
そして、データ解析が始まります。まずはインプットにさっきのデータをインプット。
次にデータをSplitします。今回は75%とします。なぜデータを分割するかと言いますと、75%のデータの頻出度などを分析し機械学習を実施し、残り25%の結果と合わせて精度を高めるという流れて進みます。
その後、分析対象の列選択(Project Columns)を行い、トレーニング=学習を実施(Train Model)し、残りのSplitした結果と比較していく流れになります。












結果が画面右。会社のラウンジでお酒を飲むのは金曜日という傾向が発見できました。
当人にしてみたら、ごくごく当然の傾向が導き出されました。












もっと多角的に検証できると思うので、徐々に勉強していきたいと思います。

2014年12月1日月曜日

12.1.0.2 Zone Maps 入門 Part II

今回の記事は、JPOUGアドベントカレンダーと弊社バルテックのアドベントカレンダーにダブルエントリーしました。
http://jpoug.doorkeeper.jp/events/17313
http://www.adventar.org/calendars/414


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

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

日本語訳:sakita

==========

Part Iでは、Zone MapはExadataのStorage Indexに類似した構造のような新しいインデックスであることを議論しました。
Zone Mapは、表の各「ゾーン」を選択した列の最小値と最大値を格納することによって、テーブルのアクセス時にディスクブロックの「プルーニング」を可能にします。
各ゾーンは連続した8Mのブロックの範囲です。
Zone Mapは比較的小さいですが、よくクラスタ化された列(ALBUM_ID)のconsistent getsを減らすのに非常に有効であることを紹介しました。
この記事では引き続きデモを行い、テーブルにデータがアップデートされたとき、Zone Mapの場所に何が起きるのかを見て行きます。

では、いくつかの行(IDの値が1から100のもの)のALBUM_ID列(現在Zone Mapが定義されている)を更新していきましょう。
ALBUM_ID列の値はこれらの行ではすべて1であったのを142に更新します。


SQL> update big_bowie set album_id=142 where id between 1 and 100;

100 rows updated.

SQL> commit;

Commit complete.


さて、ALBUM_IDの最大の値は今142であり、100ではありません。現在のZone Mapにリストされている最大値を見てみましょう。


SQL> select max(max_1_album_id) from  big_bowie_album_id_zm;

MAX(MAX_1_ALBUM_ID)
-------------------
100


最大値はまだ100のままです。従いまして、実際にはZone Mapの内容は更新されていません。Zone Mapと従来のインデックスの間には大きな違いがあり、インデックスはDML操作中に自動的に更新されるのに対し、Zone Mapは更新されません(REFRESH ON COMMIT設定が指定されない限り)。


最小値が1(更新前のALBUM_ID列の値)のZone Mapエントリの状態を見ましょう。


SQL> select * from big_bowie_album_id_zm where min_1_album_id = 1;

ZONE_ID$ MIN_1_ALBUM_ID MAX_1_ALBUM_ID ZONE_LEVEL$ ZONE_STATE$ ZONE_ROWS$
---------- -------------- -------------- ----------- ----------- ----------
3.8586E+11              1              2           0           0      66234
3.8586E+11              1              2           0           1      65787
3.8586E+11              1              2           0           0      66223


ZONE_STATE$が失効したという意味の「1」になっていることが確認できます。しかし、他のゾーンはすべてまだOKです。

全体の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は更新後も"すべてOK"のままです。(注釈:変更されていないと言っていると思われます)

Part Iのクエリをリランします。


SQL> select * from big_bowie where album_id = 42;

100000 rows selected.

Elapsed: 00:00:00.29

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
3238  consistent gets
0  physical reads
0  redo size
3130019  bytes sent via SQL*Net to client
761  bytes received via SQL*Net from client
21  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
100000  rows processed

Zone MapはまだCBOを使用しています。古くなったゾーンに関連付けられたすべてのブロックにより追加のアクセスが発生し、consistent getsの数は増えて(2364から3238)いますが、テーブル全体からすべてのブロックを読むよりは効率的です。
古いゾーンのエントリを削除したい場合は、Zone Mapを更新したり、再構築(ON DEMAND設定によるリフレッシュ)します。

SQL> alter materialized zonemap big_bowie_album_id_zm rebuild;

Materialized zonemap altered.

Zone Mapのエントリを見てみましょう。


SQL> select * from big_bowie_album_id_zm where min_1_album_id = 1;

ZONE_ID$ MIN_1_ALBUM_ID MAX_1_ALBUM_ID ZONE_LEVEL$ ZONE_STATE$ ZONE_ROWS$
---------- -------------- -------------- ----------- ----------- ----------
3.8586E+11              1              2           0           0      66234
3.8586E+11              1            142           0           0      65787
3.8586E+11              1              2           0           0      66223

エントリが古くないことと実際の最大値を反映した正しいゾーン(142)が確認できます。

クエリをリランしましょう。

SQL> select * from big_bowie where album_id = 42;

100000 rows selected.

Elapsed: 00:00:00.30

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
3238  consistent gets
0  physical reads
0  redo size
3130019  bytes sent via SQL*Net to client
761  bytes received via SQL*Net from client
21  SQL*Net roundtrips to/from client
0  sorts (memory)
0  sorts (disk)
100000  rows processed

Zone Mapはまだ使用されていますが、consistent getsは前と変わらない値であり、何も変わっていないことに気がつきますでしょうか。なぜ以前の2364に戻っていないのでしょうか。
現在、更新されたゾーンのなかで取りうる値の範囲は1~142であり、42の要求値は潜在的にこのゾーン内に見つけられそうですので、念のためにアクセスする必要がまだあります。
私たちは42の値はこのゾーン内には存在しないことはわかっていますが、Oracleはそれを知ることはできず、1~142の範囲に基づいて処理されます。
従って、Zone Mapはデータが十分にクラスタ化され、ゾーンの最小値/最大値の範囲がアクセスする必要のあるゾーンを制限できるときに最適に動作します。もしデータが十分にクラスタ化されておらず、各ゾーンが(テーブル全体の)最小値と最大値をほとんど持っていた場合は、Oracleは効果的に多くまたはすべてのゾーンをプルーニングすることができませんし、Zone Mapは役に立たないでしょう。

また PART IIIで。