2017年4月12日 (水)

Oracle Database Connect 2017 : 参加報告とお礼

Oracle Database Connect 2017の資料とセッション映像(全てではないようですが)が公開されました。

このような機会を与えてくれた日本オラクルのしばちょうさん、関係者の皆様ありがとうございました。
ドクターG的な感じで伝えられるか不安でしたが、評判は良かったと聞きホッとしているところです:)

Oracle Database Connect 2017 / jpoug.org

Oracle Database Connect 2017資料
http://www.oracle.com/technetwork/jp/ondemand/odc2017-3627481-ja.html


■基調講演

■異なるデータベース間のSQL比較と Oracle Database 12cの新機能
 日本ヒューレット・パッカード株式会社 篠田典良さん

■クラウド運用で省力化! 最新版 Oracle Database を活用した基盤の魅力
 日本オラクル 伊藤 勝一さん

■進化したのはサーバだけじゃない! DBA の毎日をもっと豊かにするユーティリティのすすめ
 NTTデータ先端技術株式会社 吉田成利さん


Oracle Database Connect 2017 つぶやきまとめ #jpoug #oradbc17
https://togetter.com/li/1088687

■エキスパートはどう考えるか? 体感!パフォーマンスチューニング 
 日本オラクル しばちょうさん、津島さん、畔勝さん
 JPOUG 諸橋さん、渡部さん、そして私


ps.
開催前のミーティングで、「イケる、イケる」と自己暗示のように呟いていた、しばちょうさんが印象的でした:)

| | コメント (0) | トラックバック (0)

2017年3月 7日 (火)

Oracle Database Connect 2017

Oracle Database Connect 2017 ~ 最新のデータベース技術がここにある ~

が明日開催されます。


昨年は、LTでしたが、今年は好評のJPOUG in 15minitesを行います。
セッション・オーガナイザーはJPOUGのサイトにてご確認ください。
http://www.jpoug.org/2017/02/14/odc2017


アジェンダには記載されていない登壇者は諸橋さんのブログで公開されていますが

ablog - 畔勝さん
wmo6hash::blog - 諸橋さん
コーソル DatabaseエンジニアのBlog - 渡部さん

そして、最近、ひな壇エンジニアリングに目覚めつつあるw 私


会場でお会いできることを楽しみにしています。



あ、そうそう、

昔、奥さんが書いてた「今日のゴハン」ってブログで、ダーリンと書かれてたのですが、
おら!オラ!Oracleのペンネーム:ダーリンが誰なのか知ったのは、2011年か2012年ごろ。

ネタを探してググっていたら、そのダーリンのエントリが!!! 

待ちイベントに関する検証 その7 - ペンネーム: ダーリン
http://www.insight-tec.com/mailmagazine/ora3/vol327.html


| | コメント (0) | トラックバック (0)

2016年12月24日 (土)

わすれられない言葉 - Unforgettable word

Jonathan LewisがOracle ACEDを辞されたというニュースはショックだった。
So Long ACED - Oracle Scratchpad / Jonathan Lewis

Oracle ACEDだけでなく、Oracle ACE全体で同様の手続きが必要になったということについて、日本在住のOracle ACEには最近、しかも一部の方のみに連絡されたのみという不手際が影響して混乱している状況となっている。2016/12/24現在


Jonathan LewisはOracle ACEDではなくても、ワクワクする記事を書いてくれると思います。


そして、

彼のサインに添えられた、忘れられない一言。

Img_3179_1









追伸

私がこのような壇上に上がるようになれたのも、ユーザーグループのみんな、ユーザーグループイベントを盛り上げてくれるみんな、そして、Oracle ACEに推薦してくれた友人たちのおかげです。 

今年一年、ありがとうございました。

多分、今年最後のエントリになると思うので。。。。


みなさん、メリークリスマス、よいお年を。


| | コメント (0) | トラックバック (0)

2016年12月17日 (土)

スタースキーマを扱う実行計画の特徴

JPOUG Advent Calendar 2016の17日目のエントリです。
昨日は、id:kenken08さんのMySQLのsql_modeにあるORACLEとは - kenken0807_DBメモでした。

第三の柴田さんのネタを見て、急遽内容を変更しました。:)
SQLチューニングと対戦格闘ゲームの類似性について語る。- JPOUG Advent Calendar 2016 Day 15 - - ねら~ITエンジニア雑記


DWH系のスタースキーマを扱う実行計画の特徴を簡単にまとめておきたいと思います。(個人的には、in-memory aggregationが今年のハイライトだったのでw)

※サンプルスキーマ:SHスキーマを利用しています。
Installing Sample Schemas

まず、ハッシュ結合とBloom Filterを利用した実行計画です。面倒な準備もなく、癖も少ないので力技でなんとかする系ではよく見かける実行計画です。
Right-Deep Join + Bloom Filter
Right-Deep Joinが可能なのはHash Joinのみです。 意図的に行う場合は、LEADING/USE_HASH/SWAP_JOIN_INPUTSを利用します。
Right-Deep Join Trees and Star Schema Queries
津島博士のパフォーマンス講座 - 第46回 パーティション・プルーニングとハッシュ結合について

スタースキーマでない結合や、NLJではLeft-Deep Joinとなるのが一般的なので見慣れない実行計画だと思う方もいると思いますが、巨大なファクト表よりサイズの小さいディメンジョン表が常にハッシュ結合のビルド表(外部表)になるように結合順序が入れ替えられています。

ハッシュ結合の実行計画としては理にかなっているのですが、超巨大なファクト表との結合がある場合、Exadataをもってしても倒すことができない敵に出会うこともありますw
弱点といえば弱点ですが、方式上難しいところでもあります。
パラレル度を増加させたとしても太刀打ちできないケースもね。。。。とほほ。

Execution Plan
----------------------------------------------------------
Plan hash value: 2503647845

------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1546 | 137K| 3879 (1)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10003 | 1546 | 137K| 3879 (1)| 00:00:01 | | | Q1,03 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 1546 | 137K| 3879 (1)| 00:00:01 | | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 1546 | 137K| 3879 (1)| 00:00:01 | | | Q1,03 | PCWP | |
| 5 | PX SEND RANGE | :TQ10002 | 1546 | 137K| 3879 (1)| 00:00:01 | | | Q1,02 | P->P | RANGE |
| 6 | HASH GROUP BY | | 1546 | 137K| 3879 (1)| 00:00:01 | | | Q1,02 | PCWP | |
|* 7 | HASH JOIN | | 580K| 50M| 3875 (1)| 00:00:01 | | | Q1,02 | PCWP | |
| 8 | PX RECEIVE | | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,00 | PCWC | |
| 11 | TABLE ACCESS INMEMORY FULL | COUNTRIES | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 12 | HASH JOIN | | 580K| 35M| 3872 (1)| 00:00:01 | | | Q1,02 | PCWP | |
| 13 | PX RECEIVE | | 55500 | 541K| 8 (13)| 00:00:01 | | | Q1,02 | PCWP | |
| 14 | PX SEND BROADCAST | :TQ10001 | 55500 | 541K| 8 (13)| 00:00:01 | | | Q1,01 | P->P | BROADCAST |
| 15 | PX BLOCK ITERATOR | | 55500 | 541K| 8 (13)| 00:00:01 | | | Q1,01 | PCWC | |
| 16 | TABLE ACCESS INMEMORY FULL| CUSTOMERS | 55500 | 541K| 8 (13)| 00:00:01 | | | Q1,01 | PCWP | |
|* 17 | HASH JOIN | | 580K| 29M| 3864 (1)| 00:00:01 | | | Q1,02 | PCWP | |
|* 18 | TABLE ACCESS INMEMORY FULL | CHANNELS | 2 | 42 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
|* 19 | HASH JOIN | | 1161K| 36M| 3862 (1)| 00:00:01 | | | Q1,02 | PCWP | |
| 20 | PART JOIN FILTER CREATE | :BF0000 | 1845 | 22140 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
|* 21 | TABLE ACCESS INMEMORY FULL| TIMES | 1845 | 22140 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 22 | PX BLOCK ITERATOR | | 3673K| 73M| 3857 (1)| 00:00:01 |:BF0000|:BF0000| Q1,02 | PCWC | |
| 23 | TABLE ACCESS FULL | SALES | 3673K| 73M| 3857 (1)| 00:00:01 |:BF0000|:BF0000| Q1,02 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 4 because of session
- 1 Sql Plan Directive used for this statement

Star Transformation
巨大なファクト表とディメンジョン表の結合が辛いなら、昔からあるスター変換だ! 
とも思うのですが、スター変換は頑固ものかつ、曲者なのが難点。

頑固さ
ディメンジョン表やファクト表にビットマップ索引や参照整合性制約作成等、利用できるようにするお膳立てができてないと、ピクリとも動きませんw
巨大なファクト表の外部キー列にビットマップ索引を1つ作成するのに、数時間w 複数作成して、さらに、ディメンジョン表との参照整合性制約まで必要なのでまともにやっていると、1日では終わらないことも><
(俺を信じろ、 RELYが利用できるデータの状態であれば楽ではありますが

スター変換の最大の弱点は、ビットマップ索引を利用したROWIDアクセス(以下の実行計画ではId=42の部分)による読み込み件数が多すぎるケースです。性能が伸びなかったり、または、悪化することもあります。
スター変換を利用するかどうかは、ディメンジョン表との結合でファクト表が十分に絞り込めるかにかかっています。

ディメンジョン表との結合キーがビットマップ索引中にあるため、ディメンジョン表とファクト表を結合することなく、ROWIDでファクト表をアクセスして集計することができます。
ファクト表のアクセス量が少ない場合はROWIDアクセスがメリットとなるわけですが、その逆のケースでは、ファクト表はROWIDで1行ごとにアクセスされることになるため、アクセスするファクト表の行数が多くなればなるほど不利になります。

ROWIDによるシングルブロックリードが数十億回繰り返されるとしたら、待機イベントのほとんどが、db file sequential readやcell single block physical read(Exadata)になってしまうことになります。

使いどころを見誤らないようにしたいものです。

Execution Plan
----------------------------------------------------------
Plan hash value: 2513598833

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6490 | 627K| | 5584 (1)| 00:00:01 | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q1,00 | P->S | QC (RAND) |
| 4 | LOAD AS SELECT (TEMP SEGMENT MERGE) | SYS_TEMP_0FD9D662C_336236 | | | | | | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS INMEMORY FULL | TIMES | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 7 | PX COORDINATOR | | | | | | | | | | | |
| 8 | PX SEND QC (ORDER) | :TQ20003 | 6490 | 627K| | 5582 (1)| 00:00:01 | | | Q2,03 | P->S | QC (ORDER) |
| 9 | SORT GROUP BY | | 6490 | 627K| 60M| 5582 (1)| 00:00:01 | | | Q2,03 | PCWP | |
| 10 | PX RECEIVE | | 6490 | 627K| | 5582 (1)| 00:00:01 | | | Q2,03 | PCWP | |
| 11 | PX SEND RANGE | :TQ20002 | 6490 | 627K| | 5582 (1)| 00:00:01 | | | Q2,02 | P->P | RANGE |
| 12 | HASH GROUP BY | | 6490 | 627K| 60M| 5582 (1)| 00:00:01 | | | Q2,02 | PCWP | |
|* 13 | HASH JOIN | | 580K| 54M| | 4334 (1)| 00:00:01 | | | Q2,02 | PCWP | |
| 14 | PX RECEIVE | | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | |
| 15 | PX SEND BROADCAST | :TQ20000 | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q2,00 | P->P | BROADCAST |
| 16 | PX BLOCK ITERATOR | | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q2,00 | PCWC | |
| 17 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662C_336236 | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q2,00 | PCWP | |
|* 18 | HASH JOIN | | 580K| 48M| | 4332 (1)| 00:00:01 | | | Q2,02 | PCWP | |
|* 19 | TABLE ACCESS INMEMORY FULL | CHANNELS | 2 | 42 | | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | |
|* 20 | HASH JOIN | | 580K| 36M| | 4329 (1)| 00:00:01 | | | Q2,02 | PCWP | |
| 21 | PX RECEIVE | | 55500 | 2005K| | 10 (10)| 00:00:01 | | | Q2,02 | PCWP | |
| 22 | PX SEND BROADCAST | :TQ20001 | 55500 | 2005K| | 10 (10)| 00:00:01 | | | Q2,01 | P->P | BROADCAST |
|* 23 | HASH JOIN | | 55500 | 2005K| | 10 (10)| 00:00:01 | | | Q2,01 | PCWP | |
| 24 | TABLE ACCESS INMEMORY FULL | COUNTRIES | 23 | 621 | | 2 (0)| 00:00:01 | | | Q2,01 | PCWP | |
| 25 | PX BLOCK ITERATOR | | 55500 | 541K| | 8 (13)| 00:00:01 | | | Q2,01 | PCWC | |
| 26 | TABLE ACCESS INMEMORY FULL | CUSTOMERS | 55500 | 541K| | 8 (13)| 00:00:01 | | | Q2,01 | PCWP | |
| 27 | VIEW | VW_ST_A44449E3 | 580K| 16M| | 4319 (1)| 00:00:01 | | | Q2,02 | PCWP | |
| 28 | NESTED LOOPS | | 580K| 28M| | 4315 (1)| 00:00:01 | | | Q2,02 | PCWP | |
| 29 | PX PARTITION RANGE SUBQUERY | | 580K| 12M| | 15 (14)| 00:00:01 |KEY(SQ)|KEY(SQ)| Q2,02 | PCWC | |
| 30 | BITMAP CONVERSION TO ROWIDS | | 580K| 12M| | 15 (14)| 00:00:01 | | | Q2,02 | PCWP | |
| 31 | BITMAP AND | | | | | | | | | Q2,02 | PCWP | |
| 32 | BITMAP MERGE | | | | | | | | | Q2,02 | PCWP | |
| 33 | BITMAP KEY ITERATION | | | | | | | | | Q2,02 | PCWP | |
| 34 | BUFFER SORT | | | | | | | | | Q2,02 | PCWP | |
|* 35 | TABLE ACCESS INMEMORY FULL| CHANNELS | 2 | 26 | | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | |
|* 36 | BITMAP INDEX RANGE SCAN | SALES_CHANNEL_BIX | | | | | |KEY(SQ)|KEY(SQ)| Q2,02 | PCWP | |
| 37 | BITMAP MERGE | | | | | | | | | Q2,02 | PCWP | |
| 38 | BITMAP KEY ITERATION | | | | | | | | | Q2,02 | PCWP | |
| 39 | BUFFER SORT | | | | | | | | | Q2,02 | PCWP | |
| 40 | TABLE ACCESS FULL | SYS_TEMP_0FD9D662C_336236 | 1845 | 14760 | | 2 (0)| 00:00:01 | | | Q2,02 | PCWP | |
|* 41 | BITMAP INDEX RANGE SCAN | SALES_TIME_BIX | | | | | |KEY(SQ)|KEY(SQ)| Q2,02 | PCWP | |
| 42 | TABLE ACCESS BY USER ROWID | SALES | 1 | 29 | | 4304 (1)| 00:00:01 | ROWID | ROWID | Q2,02 | PCWP | |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 4 because of session
- star transformation used for this statement
- 1 Sql Plan Directive used for this statement

長い前置きでしたが、やっと真打の登場です!
in-memory aggregationとして解説されていることが多いのですが、実行計画を眺めている人間からすると機能名よりvector transformationの方がイメージしやすいので、以下、Vector Tranformation/ベクター変換とします。

Vector Transformation
スター変換同様に、巨大なファクト表とディメンジョン表を直接結合しない点や、パラレル実行時も他の実行計画ではみられない(誤解をおそれずにいうと、全力投球に近いかもw)特徴があります。
索引や参照整合性制約などの作成は不要。
in-memory database関連の機能ではあるのですが、全ての表がinmemory化されていなくても発動させることができます。(inmemory_sizeパラメータの設定は必要となる模様。後述)
最強の力を発揮するのは、全てがinmemoryで動作した場合であることは間違いないわけですが、巨大過ぎるファクト表がinmemory化できるほどメモリが潤沢にあるかというと、そうじゃなかっりしますし。


ベクター変換の動きを簡単に説明すると以下のような感じです。
ディメンジョン表を元に集計結果相当の構造体(in-memory accumulatorと呼ばれる多次元構造体)をメモリ上に構築後、ファクト表を読みながらin-memory accumulator上で集計します!!!(画期的!)
ハッシュ結合が全くなくなるわけではないですが、集計終了後に読み替え目的で少量(この部分が少量じゃないと辛くなるはずなのでよーく確認しておくことをおすすめします)はのハッシュ結合が行われるだけなので、冒頭で紹介した巨大なファクト表とディメンジョン表の結合によるCPUネック部分を華麗に回避していることがわかります。
Right-Deep Join+Bloom Filterで苦しい状況になったら、in-memory aggregationのことを思い出してあげてください。

助けてくれるかもしれません。



Oracle Database In-Memory: In-Memory Aggregation - Oracle White Paper JANUARY 2015


Execution Plan
----------------------------------------------------------
Plan hash value: 3211261687

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 483 | 60858 | | 3840 (1)| 00:00:01 | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6630_336236 | | | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10001 | 21 | 336 | | 3 (34)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 5 | BUFFER SORT | | 21 | 336 | | 3 (34)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | VECTOR GROUP BY | | 21 | 336 | | 3 (34)| 00:00:01 | | | Q1,01 | PCWP | |
| 7 | KEY VECTOR CREATE BUFFERED | :KV0000 | 1845 | 29520 | | 3 (34)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX RECEIVE | | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 9 | PX SEND HASH | :TQ10000 | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q1,00 | PCWC | |
|* 11 | TABLE ACCESS INMEMORY FULL | TIMES | 1845 | 22140 | | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 12 | LOAD AS SELECT | SYS_TEMP_0FD9D6631_336236 | | | | | | | | | | |
| 13 | PX COORDINATOR | | | | | | | | | | | |
| 14 | PX SEND QC (RANDOM) | :TQ20001 | 23 | 851 | | 12 (25)| 00:00:01 | | | Q2,01 | P->S | QC (RAND) |
| 15 | HASH GROUP BY | | 23 | 851 | 2624K| 12 (25)| 00:00:01 | | | Q2,01 | PCWP | |
| 16 | KEY VECTOR CREATE BUFFERED | :KV0001 | | | | | | | | Q2,01 | PCWP | |
| 17 | PX RECEIVE | | 55500 | 2005K| | 10 (10)| 00:00:01 | | | Q2,01 | PCWP | |
| 18 | PX SEND HASH | :TQ20000 | 55500 | 2005K| | 10 (10)| 00:00:01 | | | Q2,00 | P->P | HASH |
|* 19 | HASH JOIN | | 55500 | 2005K| | 10 (10)| 00:00:01 | | | Q2,00 | PCWP | |
| 20 | TABLE ACCESS INMEMORY FULL | COUNTRIES | 23 | 621 | | 2 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 21 | PX BLOCK ITERATOR | | 55500 | 541K| | 8 (13)| 00:00:01 | | | Q2,00 | PCWC | |
| 22 | TABLE ACCESS INMEMORY FULL | CUSTOMERS | 55500 | 541K| | 8 (13)| 00:00:01 | | | Q2,00 | PCWP | |
| 23 | LOAD AS SELECT | SYS_TEMP_0FD9D6632_336236 | | | | | | | | | | |
| 24 | PX COORDINATOR | | | | | | | | | | | |
| 25 | PX SEND QC (RANDOM) | :TQ30001 | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | P->S | QC (RAND) |
| 26 | BUFFER SORT | | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | PCWP | |
| 27 | VECTOR GROUP BY | | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | PCWP | |
| 28 | KEY VECTOR CREATE BUFFERED | :KV0002 | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | PCWP | |
| 29 | PX RECEIVE | | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,01 | PCWP | |
| 30 | PX SEND HASH | :TQ30000 | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,00 | P->P | HASH |
| 31 | PX BLOCK ITERATOR | | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,00 | PCWC | |
|* 32 | TABLE ACCESS INMEMORY FULL | CHANNELS | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,00 | PCWP | |
| 33 | PX COORDINATOR | | | | | | | | | | | |
| 34 | PX SEND QC (ORDER) | :TQ40003 | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,03 | P->S | QC (ORDER) |
| 35 | SORT GROUP BY | | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,03 | PCWP | |
| 36 | PX RECEIVE | | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,03 | PCWP | |
| 37 | PX SEND RANGE | :TQ40002 | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,02 | P->P | RANGE |
| 38 | HASH GROUP BY | | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,02 | PCWP | |
|* 39 | HASH JOIN | | 483 | 60858 | | 3820 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 40 | PX RECEIVE | | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,02 | PCWP | |
| 41 | PX SEND BROADCAST | :TQ40000 | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,00 | P->P | BROADCAST |
| 42 | PX BLOCK ITERATOR | | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,00 | PCWC | |
| 43 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6631_336236 | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,00 | PCWP | |
|* 44 | HASH JOIN | | 483 | 42987 | | 3818 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 45 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6630_336236 | 21 | 252 | | 2 (0)| 00:00:01 | | | Q4,02 | PCWP | |
|* 46 | HASH JOIN | | 483 | 37191 | | 3816 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 47 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6632_336236 | 2 | 42 | | 2 (0)| 00:00:01 | | | Q4,02 | PCWP | |
| 48 | VIEW | VW_VT_AF0F4755 | 483 | 27048 | | 3814 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 49 | HASH GROUP BY | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 50 | PX RECEIVE | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 51 | PX SEND HASH | :TQ40001 | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,01 | P->P | HASH |
| 52 | VECTOR GROUP BY | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,01 | PCWP | |
| 53 | HASH GROUP BY | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,01 | PCWP | |
| 54 | KEY VECTOR USE | :KV0001 | 580K| 18M| | 3811 (1)| 00:00:01 | | | Q4,01 | PCWC | |
| 55 | KEY VECTOR USE | :KV0002 | 580K| 16M| | 3811 (1)| 00:00:01 | | | Q4,01 | PCWC | |
| 56 | KEY VECTOR USE | :KV0000 | 1161K| 27M| | 3811 (1)| 00:00:01 | | | Q4,01 | PCWC | |
| 57 | PX BLOCK ITERATOR | | 3673K| 73M| | 3811 (1)| 00:00:01 |KEY(SQ)|KEY(SQ)| Q4,01 | PCWC | |
|* 58 | TABLE ACCESS FULL| SALES | 3673K| 73M| | 3811 (1)| 00:00:01 |KEY(SQ)|KEY(SQ)| Q4,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 4 because of session
- 2 Sql Plan Directives used for this statement
- vector transformation used for this statement

超絶必殺技にも思えるベクター変換ですが、癖がないわけではありません。(大技につきものの反動というか、なんというかw)

癖 その1)
パラレルクエリー時に割り当てられるサーバープロセスが多く、割り当てられるサーバー数は、KEY VECTORの作成数により大きく変化します。
以下、SQL MONITORのParallel Execution Detailsセクション(抜粋)の比較

並列度は同じでも割り当てるサーバー数はこんなに違う!
VECTOR TRANSFORMATION
KEY VECTORが3つ作成されるベクター変換の場合
(SQL MONITORのParallel Execution Detailsセクションより抜粋)
Parallel Execution Details (DOP=4 , Servers Allocated=32)

KEY VECTORが2つ作成されるベクター変換の場合
Parallel Execution Details (DOP=4 , Servers Allocated=24)

STAR TRANSFORMATION
Parallel Execution Details (DOP=4 , Servers Allocated=12)

Hash Joinのみ
Parallel Execution Details (DOP=4 , Servers Allocated=8)


癖 その2)
スター変換を発動させるための索引作成や、制約作成の煩雑さは無く、全ての表がinmemoryになっていなくても発動させることはできるのですが、発動させるためは、最低限設定しなればならない(ほんと? 不具合?)パラメータが存在します。(

inmemory化する表は無くとも、inmemory_size=100m(設定可能な最小サイズ)に設定しないとVECTOR_TRANSFORMヒントが無視されるという点です。
この制限?を記載しているマニュアルなどは探し出せていないのですが、どこかに記載されているのでしょうか?(いまのところ見つけることができず。。。教えていただけるとうれしいです)


では、最後に、inmemory_size初期化パラメータの設定有無による変化をみてみましょう。

inmemory_size初期化パラメータが設定されている場合にはVECTOR_TRANSFORMヒントでベクター変換を強制できています。

10:36:23 orcl12c@SYSTEM> show parameter inmemory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 3
inmemory_query string ENABLE
inmemory_size big integer 512M
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE


10:37:04 ORCL@SH> set autot trace exp stat
10:56:18 ORCL@SH> @sample3_2

135 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3211261687

------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 483 | 60858 | | 3967 (1)| 00:00:01 | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | | | | |
| 2 | LOAD AS SELECT | SYS_TEMP_0FD9D6609_33B383 | | | | | | | | | | |
| 3 | PX COORDINATOR | | | | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ10001 | 21 | 336 | | 20 (5)| 00:00:01 | | | Q1,01 | P->S | QC (RAND) |
| 5 | BUFFER SORT | | 21 | 336 | | 20 (5)| 00:00:01 | | | Q1,01 | PCWP | |
| 6 | VECTOR GROUP BY | | 21 | 336 | | 20 (5)| 00:00:01 | | | Q1,01 | PCWP | |
| 7 | KEY VECTOR CREATE BUFFERED | :KV0000 | 1845 | 29520 | | 20 (5)| 00:00:01 | | | Q1,01 | PCWP | |
| 8 | PX RECEIVE | | 1845 | 22140 | | 19 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 9 | PX SEND HASH | :TQ10000 | 1845 | 22140 | | 19 (0)| 00:00:01 | | | Q1,00 | P->P | HASH |
| 10 | PX BLOCK ITERATOR | | 1845 | 22140 | | 19 (0)| 00:00:01 | | | Q1,00 | PCWC | |
|* 11 | TABLE ACCESS FULL | TIMES | 1845 | 22140 | | 19 (0)| 00:00:01 | | | Q1,00 | PCWP | |
| 12 | LOAD AS SELECT | SYS_TEMP_0FD9D660A_33B383 | | | | | | | | | | |
| 13 | PX COORDINATOR | | | | | | | | | | | |
| 14 | PX SEND QC (RANDOM) | :TQ20001 | 23 | 851 | | 122 (3)| 00:00:01 | | | Q2,01 | P->S | QC (RAND) |
| 15 | HASH GROUP BY | | 23 | 851 | 2624K| 122 (3)| 00:00:01 | | | Q2,01 | PCWP | |
| 16 | KEY VECTOR CREATE BUFFERED | :KV0001 | | | | | | | | Q2,01 | PCWP | |
| 17 | PX RECEIVE | | 55500 | 2005K| | 119 (0)| 00:00:01 | | | Q2,01 | PCWP | |
| 18 | PX SEND HASH | :TQ20000 | 55500 | 2005K| | 119 (0)| 00:00:01 | | | Q2,00 | P->P | HASH |
|* 19 | HASH JOIN | | 55500 | 2005K| | 119 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 20 | TABLE ACCESS FULL | COUNTRIES | 23 | 621 | | 2 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 21 | PX BLOCK ITERATOR | | 55500 | 541K| | 117 (0)| 00:00:01 | | | Q2,00 | PCWC | |
| 22 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 541K| | 117 (0)| 00:00:01 | | | Q2,00 | PCWP | |
| 23 | LOAD AS SELECT | SYS_TEMP_0FD9D660B_33B383 | | | | | | | | | | |
| 24 | PX COORDINATOR | | | | | | | | | | | |
| 25 | PX SEND QC (RANDOM) | :TQ30001 | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | P->S | QC (RAND) |
| 26 | BUFFER SORT | | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | PCWP | |
| 27 | VECTOR GROUP BY | | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | PCWP | |
| 28 | KEY VECTOR CREATE BUFFERED | :KV0002 | 2 | 50 | | 3 (34)| 00:00:01 | | | Q3,01 | PCWP | |
| 29 | PX RECEIVE | | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,01 | PCWP | |
| 30 | PX SEND HASH | :TQ30000 | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,00 | P->P | HASH |
| 31 | PX BLOCK ITERATOR | | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,00 | PCWC | |
|* 32 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | | 2 (0)| 00:00:01 | | | Q3,00 | PCWP | |
| 33 | PX COORDINATOR | | | | | | | | | | | |
| 34 | PX SEND QC (ORDER) | :TQ40003 | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,03 | P->S | QC (ORDER) |
| 35 | SORT GROUP BY | | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,03 | PCWP | |
| 36 | PX RECEIVE | | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,03 | PCWP | |
| 37 | PX SEND RANGE | :TQ40002 | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,02 | P->P | RANGE |
| 38 | HASH GROUP BY | | 483 | 60858 | | 3821 (1)| 00:00:01 | | | Q4,02 | PCWP | |
|* 39 | HASH JOIN | | 483 | 60858 | | 3820 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 40 | PX RECEIVE | | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,02 | PCWP | |
| 41 | PX SEND BROADCAST | :TQ40000 | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,00 | P->P | BROADCAST |
| 42 | PX BLOCK ITERATOR | | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,00 | PCWC | |
| 43 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660A_33B383 | 23 | 851 | | 2 (0)| 00:00:01 | | | Q4,00 | PCWP | |
|* 44 | HASH JOIN | | 483 | 42987 | | 3818 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 45 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6609_33B383 | 21 | 252 | | 2 (0)| 00:00:01 | | | Q4,02 | PCWP | |
|* 46 | HASH JOIN | | 483 | 37191 | | 3816 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 47 | TABLE ACCESS FULL | SYS_TEMP_0FD9D660B_33B383 | 2 | 42 | | 2 (0)| 00:00:01 | | | Q4,02 | PCWP | |
| 48 | VIEW | VW_VT_AF0F4755 | 483 | 27048 | | 3814 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 49 | HASH GROUP BY | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 50 | PX RECEIVE | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,02 | PCWP | |
| 51 | PX SEND HASH | :TQ40001 | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,01 | P->P | HASH |
| 52 | VECTOR GROUP BY | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,01 | PCWP | |
| 53 | HASH GROUP BY | | 483 | 15939 | | 3814 (1)| 00:00:01 | | | Q4,01 | PCWP | |
| 54 | KEY VECTOR USE | :KV0001 | 580K| 18M| | 3811 (1)| 00:00:01 | | | Q4,01 | PCWC | |
| 55 | KEY VECTOR USE | :KV0002 | 580K| 16M| | 3811 (1)| 00:00:01 | | | Q4,01 | PCWC | |
| 56 | KEY VECTOR USE | :KV0000 | 1161K| 27M| | 3811 (1)| 00:00:01 | | | Q4,01 | PCWC | |
| 57 | PX BLOCK ITERATOR | | 3673K| 73M| | 3811 (1)| 00:00:01 |KEY(SQ)|KEY(SQ)| Q4,01 | PCWC | |
|* 58 | TABLE ACCESS FULL| SALES | 3673K| 73M| | 3811 (1)| 00:00:01 |KEY(SQ)|KEY(SQ)| Q4,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------------------------------------------------------------

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

11 - filter("T"."FISCAL_YEAR"=2000 OR "T"."FISCAL_YEAR"=2005 OR "T"."FISCAL_YEAR"=2010 OR "T"."FISCAL_YEAR"=2015 OR "T"."FISCAL_YEAR"=2016)
19 - access("C"."COUNTRY_ID"="R"."COUNTRY_ID")
32 - filter("CH"."CHANNEL_DESC"='Internet' OR "CH"."CHANNEL_DESC"='Partners')
39 - access("ITEM_13"=INTERNAL_FUNCTION("C0") AND "ITEM_14"="C4")
44 - access("ITEM_17"=INTERNAL_FUNCTION("C0") AND "ITEM_18"="C2")
46 - access("ITEM_15"=INTERNAL_FUNCTION("C0") AND "ITEM_16"="C2")
58 - filter(SYS_OP_KEY_VECTOR_FILTER("S"."TIME_ID",:KV0000) AND SYS_OP_KEY_VECTOR_FILTER("S"."CHANNEL_ID",:KV0002))

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 4 because of session
- 2 Sql Plan Directives used for this statement
- vector transformation used for this statement

inmemory_size初期化パラメータを0にし、同一SQL文を実行すると。。。。なんということでしょう。ベクター変換は発動しません!

10:46:22 orcl12c@SYSTEM> show parameter inmemory

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default string
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_query string ENABLE
inmemory_size big integer 0
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE


10:47:56 ORCL@SH> @sample3_2

135 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 2503647845

--------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1546 | 137K| 4006 (1)| 00:00:01 | | | | | |
| 1 | PX COORDINATOR | | | | | | | | | | |
| 2 | PX SEND QC (ORDER) | :TQ10003 | 1546 | 137K| 4006 (1)| 00:00:01 | | | Q1,03 | P->S | QC (ORDER) |
| 3 | SORT GROUP BY | | 1546 | 137K| 4006 (1)| 00:00:01 | | | Q1,03 | PCWP | |
| 4 | PX RECEIVE | | 1546 | 137K| 4006 (1)| 00:00:01 | | | Q1,03 | PCWP | |
| 5 | PX SEND RANGE | :TQ10002 | 1546 | 137K| 4006 (1)| 00:00:01 | | | Q1,02 | P->P | RANGE |
| 6 | HASH GROUP BY | | 1546 | 137K| 4006 (1)| 00:00:01 | | | Q1,02 | PCWP | |
|* 7 | HASH JOIN | | 580K| 50M| 4002 (1)| 00:00:01 | | | Q1,02 | PCWP | |
| 8 | PX RECEIVE | | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,00 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,00 | PCWC | |
| 11 | TABLE ACCESS FULL | COUNTRIES | 23 | 621 | 2 (0)| 00:00:01 | | | Q1,00 | PCWP | |
|* 12 | HASH JOIN | | 580K| 35M| 3999 (1)| 00:00:01 | | | Q1,02 | PCWP | |
| 13 | PX RECEIVE | | 55500 | 541K| 117 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 14 | PX SEND BROADCAST | :TQ10001 | 55500 | 541K| 117 (0)| 00:00:01 | | | Q1,01 | P->P | BROADCAST |
| 15 | PX BLOCK ITERATOR | | 55500 | 541K| 117 (0)| 00:00:01 | | | Q1,01 | PCWC | |
| 16 | TABLE ACCESS FULL | CUSTOMERS | 55500 | 541K| 117 (0)| 00:00:01 | | | Q1,01 | PCWP | |
|* 17 | HASH JOIN | | 580K| 29M| 3882 (1)| 00:00:01 | | | Q1,02 | PCWP | |
|* 18 | TABLE ACCESS FULL | CHANNELS | 2 | 42 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
|* 19 | HASH JOIN | | 1161K| 36M| 3879 (1)| 00:00:01 | | | Q1,02 | PCWP | |
| 20 | PART JOIN FILTER CREATE| :BF0000 | 1845 | 22140 | 19 (0)| 00:00:01 | | | Q1,02 | PCWP | |
|* 21 | TABLE ACCESS FULL | TIMES | 1845 | 22140 | 19 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 22 | PX BLOCK ITERATOR | | 3673K| 73M| 3857 (1)| 00:00:01 |:BF0000|:BF0000| Q1,02 | PCWC | |
| 23 | TABLE ACCESS FULL | SALES | 3673K| 73M| 3857 (1)| 00:00:01 |:BF0000|:BF0000| Q1,02 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------

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

7 - access("C"."COUNTRY_ID"="R"."COUNTRY_ID")
12 - access("S"."CUST_ID"="C"."CUST_ID")
17 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
18 - filter("CH"."CHANNEL_DESC"='Internet' OR "CH"."CHANNEL_DESC"='Partners')
19 - access("S"."TIME_ID"="T"."TIME_ID")
21 - filter("T"."FISCAL_YEAR"=2000 OR "T"."FISCAL_YEAR"=2005 OR "T"."FISCAL_YEAR"=2010 OR "T"."FISCAL_YEAR"=2015 OR
"T"."FISCAL_YEAR"=2016)

Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- Degree of Parallelism is 4 because of session
- 1 Sql Plan Directive used for this statement

参考
Database Virtual Box Appliance / Virtual Machine
Installing Sample Schemas
Getting started with Oracle Database In-Memory Part V - Aggregation
津島博士のパフォーマンス講座 - 第54回 Oracle Database In-Memoryについて(2)

利用したSQL
sample1_2.sql
Right-Deep Join (Right-Deep Treeにならない場合には SWAP_JOIN_INPUTSで制御する必要がありますが、SHスキーマでオプティマイザ統計が取得されているのであれば不要.今回の例ではSWAP_JOIN_INPUTSは利用していませんが、オプティマイザは判断を誤るようであれば利用したほうがよいと思います。)

SELECT	
/*+
MONITOR
LEADING(r sum)
USE_HASH(r sum)
*/
sum.fiscal_year
, r.country_name
, sum.channel_class
, sum.sales_amount
FROM
(
SELECT
t.fiscal_year
, c.country_id
, ch.channel_class
, SUM(s.amount_sold) sales_amount
FROM
sales s
, times t
, customers c
, channels ch
WHERE
s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND ch.channel_desc in ('Internet','Partners')
AND t.fiscal_year IN (
2000, 2005, 2010, 2015, 2016
)
GROUP BY
ch.channel_class
, c.country_id
, t.fiscal_year
) sum
, countries r
WHERE
sum.country_id = r.country_id
ORDER BY
sum.fiscal_year
, r.country_name
, sum.channel_class
/

sample2_2.sql
スター変換ヒントが必要です。スター変換はデフォルトでOFFに設定されています。
SHスキーマはスタースキーマかつ、スター変換をすぐに試せる環境(ファクト表の外部キーのビットマップ索引やデョメンジョン表への参照整合性制約等)になっています。

SELECT	
/*+
MONITOR
LEADING(r sum)
USE_HASH(r sum)
*/
sum.fiscal_year
, r.country_name
, sum.channel_class
, sum.sales_amount
FROM
(
SELECT
/*+
STAR_TRANSFORMATION
*/
t.fiscal_year
, c.country_id
, ch.channel_class
, SUM(s.amount_sold) sales_amount
FROM
sales s
, times t
, customers c
, channels ch
WHERE
s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND ch.channel_desc in ('Internet','Partners')
AND t.fiscal_year IN (
2000, 2005, 2010, 2015, 2016
)
GROUP BY
ch.channel_class
, c.country_id
, t.fiscal_year
) sum
, countries r
WHERE
sum.country_id = r.country_id
ORDER BY
sum.fiscal_year
, r.country_name
, sum.channel_class
/

sample3_2.sql
ベクター変換の例です。ベクター変換のヒントは数種類(ファクト表を記述場合、ディメンジョン表を記述場合)あります。(詳細はv$sql_hintを参照のこと)

SELECT	
/*+
MONITOR
LEADING(r sum)
USE_HASH(r sum)
*/
sum.fiscal_year
, r.country_name
, sum.channel_class
, sum.sales_amount
FROM
(
SELECT
/*+
VECTOR_TRANSFORM
*/
t.fiscal_year
, c.country_id
, ch.channel_class
, SUM(s.amount_sold) sales_amount
FROM
sales s
, times t
, customers c
, channels ch
WHERE
s.time_id = t.time_id
AND s.cust_id = c.cust_id
AND s.channel_id = ch.channel_id
AND ch.channel_desc in ('Internet','Partners')
AND t.fiscal_year IN (
2000, 2005, 2010, 2015, 2016
)
GROUP BY
ch.channel_class
, c.country_id
, t.fiscal_year
) sum
, countries r
WHERE
sum.country_id = r.country_id
ORDER BY
sum.fiscal_year
, r.country_name
, sum.channel_class
/


明日は、@yoshikawさんです! お楽しみに!


俺のターンおわたー!:)

| | コメント (0) | トラックバック (0)

2016年10月25日 (火)

JPOUG in 15 minutes #2 @ Yahoo! Japan 紀尾井町

随分前に放置していた、Youtubeで公開しているOracle SQL Developer Data Modeler : reverse engineeringがいつのまにやら10万アクセスを超えていた
(祝!

最近のOracle SQL Developer意識しなくてもリバースしてくれたりして。。。ね。 新しいネタ乗せないと><


ってことは置いといて、




JPOUG (Japan Oracle User Group)のイベントのお知らせです。

JPOUG in 15 minutes #2


を2016年11月17日(木)19:00~21:00(開場および受付開始: 18時30分)に開催しま〜す。

(コワーキングスペースが気になる!)Yahoo! Japanさんの新オフィスです!。
昔は日本オラクルさんのオフィスビルだったところの向かいです:)

広いお部屋をお借りできたので、まだ、お席に余裕があります!!(10/25日現在)


参加登録は以下よりお願いします。
なお、セキュリティーの厳しいビルであるため、参加登録ページの後半に入館に関する注意点を記載してあります。
スムーズな入館のため、ご一読くださいますようお願いいたします。

https://jpoug.doorkeeper.jp/events/51429
20161025_230728

| | コメント (0) | トラックバック (0)

2016年7月19日 (火)

DB Tech Showcase 2016 Tokyo - E35 - SQLチューニング総合診療所的予防医学のセッション資料

DB Tech Showcase 2016 Tokyo - E35 - SQLチューニング総合診療所的予防医学のセッション資料を公開しました。

ぼくとつと、性能試験データの質などのことを言い続ける感じになっていたでしょうか?w

ところで、次回のJPOUG主催のイベントの開催が決まりました。セッション内容は現在準備中ですが、確定次第随時更新します。
お申し込みは以下のイベントベージよりお願いします。

JPOUG in 15 minutes #1

| | コメント (0) | トラックバック (0)

2016年2月14日 (日)

JPOUG Tech Talk Night #6 @ 21cafe


2016/3/5追記

JPOUG Tech Talk Night #6 - Togetterまとめ
http://togetter.com/li/943287



2/23(火) - 19:00 - 20:30 に今年最初のJPOUG Tech Talk Night #6 (すでに満席ですが....) を開催しま〜す。

詳細は以下⇩⇩⇩⇩⇩
JPOUG Tech Talk Night #6
doorkeeper : JPOUG Tech Talk Night #6
Oracle Database の オプティマイザ統計運用 について語ります



たん、たん、たぬきの....か〜〜ぜもないのに、ぶ〜らぶら

5452836527_d7794263f7_o
https://www.flickr.com/photos/small-life/albums/72157625948037655
(CC BY-NC 2.0) Attribution-NonCommercial 2.0 Generic

的なところが話題になるんでしょうかね?


でも、
風もないのにぶ〜らぶらしないのが実行計画

ぶ〜らぶらする理由の代表的なものはなんだろう....しばし考え中....


そうだ!いろいろなぶ〜らぶら、つまり揺れ、振れ、偏り。

ぶ〜らぶら、その1

オプティマイザ統計のぶ〜らぶら、とか実データとの乖離状況のぶ〜らぶら。

ぶ〜らぶら、その2

特定データの偏り、その偏り自体が時間と共に変化するぶ〜らぶら。

ぶ〜らぶら、その3

検索範囲とヒットする行セットが、大きく変化する、揺れ、振れるぶ〜らぶら。

ぶ〜らぶら、その4

その1〜その3が混じって、ぶ〜らぶら


どう考えても、か〜ぜもないのにぶ〜らぶらするわけじゃない....ですよね。

なにかが、ぶ〜らぶらしてるから、実行計画もぶ〜らぶらするんじゃないかなぁ。

ぶ〜らぶらするのって普通でしょ? オプティマイザへインプットされる情報がぶ〜らぶらしてるんだから...、

それにしても、オプティマイザ、peakyすぎるだろう。。とか。

いろいろな方々の、いろいろな思いが交錯して、さらに事態はややこしいことにw。

どんなに、ぶ〜らぶらしても性能要件さえ満たしていればいいんですが、性能要件を満たせない(データモデルの影響も少なくないと思いますが)ほどぶ〜らぶらしちゃうと問題になってきます。

安定させろ、

オプティマイザ統計固めろ

隠れてない隠しパラメータオフれ!

いろいろ止めろ!とか....


でも最近思うんです、結局のところ、やることの多さと労力には大きな差ないのかもしれない....とか....ぼそっ。

ぶ〜らぶらしてる方が健康的かも、 

いやっ! ちがう、ぶ〜らぶらしない方がいい!!!!

ボクサーパンツ vs. トランクス どっがいいんだ的なw オチのない永遠のテーマなのかもね。




追伸
実行計画を固定したとしても、データ量が増加してくれば、自動的に処理時間は伸びる傾向を示すわけで、ある時までは良かった実行計画が、時を経て悪者扱いされることもなくはない...


| | コメント (0) | トラックバック (0)

2015年12月 4日 (金)

OTHER_XMLの中身

JPOUG Advent Calendar 2015 - 4日目のエントリーです。

役に立たないから、絶対最後まで読まないでね!!! (^^)

さて、
みなさん、マニュアル読んでますか? 
最近は量が多いので、必要になってから、なりそうだから読むことが多いです。
Oracle® Databaseリファレンス 12cリリース1 (12.1) なんて特にそうです。
ただ、一度読み始めると、いろいろと気づくところもあるわけです...こんな情報が取れるのか! とか。


あ〜、これは、禁断のw マニュアルに記載されてないパラメータを指定すると見れるやつだ!!! とか気づくこともあったり。

例えば、V$SQL_PLANDBA_HIST_SQL_PLANのOTHER_XML列。

この列の説明には実に興味深いことが書かれています。以下の説明、読んでてワクワクしますw 
何が格納されているのでしょう???(もう、みんな知ってるくせに〜)

”アウトライン・データ(同じ計画の再作成に使用できる一連のオプティマイザ・ヒント)”

見たくないですか? 中身。

見たいですよね。私もそうです!


ただ、OTHER_XML列、列名の通り、CLOB型で中身はXMLです!!!
SQL*Plusで普通に表示しようとすると長すぎて読みにくいってのが難点w


ならばXMLにも対応しているSQL文で何とかしてみたいと思います。

ゴニョゴニョ、パタパタ。

できました。

dba_hist_sql_plan向けですが。(v$sql_planビューでも同様のことができます!)
(注:dba_hist_*ビューを参照するにはOracle Diagnostics Packが必要でっす。ですが、v$sql_planを参照するのならオプションはいらないですよね.)

こんな感じです

SYSTEM> !cat report_outline_hints.sql
col outline for a200
set linesize 300
set pagesize 10000
set veri off

SELECT '/*+' AS outline FROM DUAL
UNION ALL
SELECT ' '||'BEGIN_OUTLINE_DATA' FROM DUAL
UNION ALL
SELECT
' '||o.outlinehint
FROM
(
SELECT
EXTRACTVALUE(
VALUE(x)
, '/hint/text()'
) AS outlinehint
FROM
XMLTABLE(
'/*/outline_data/hint'
PASSING(
SELECT
XMLTYPE(other_xml)
FROM
dba_hist_sql_plan
WHERE
other_xml IS NOT NULL
AND sql_id = '&1'
AND plan_hash_value = &2
)
) x
) o
UNION ALL
SELECT ' '||'END_OUTLINE_DATA' FROM DUAL
UNION ALL
SELECT '*/' FROM DUAL
/
set veri on

実行すると以下のような、:) 情報を取り出すことができます!!

SYSTEM> @report_outline_hints gdtyuqcyk8x1c 2236229349

OUTLINE
------------------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "B"@"SEL$1" ("ILM_EXECUTION$"."EXECUTION_ID"))
NO_ACCESS(@"SEL$1" "A"@"SEL$1")
LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
USE_MERGE(@"SEL$1" "A"@"SEL$1")
PQ_FILTER(@"SEL$1" SERIAL)
INDEX(@"SEL$2" "A"@"SEL$2" ("ILM_EXECUTION$"."EXECUTION_ID"))
FULL(@"SEL$2" "B"@"SEL$2")
LEADING(@"SEL$2" "A"@"SEL$2" "B"@"SEL$2")
USE_MERGE(@"SEL$2" "B"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$2")
END_OUTLINE_DATA
*/

表示された内容に、身に覚え、
いや、見覚えのある方も多いことと思います :) あれでね。そう、あれです。
マニュアルに記載されいないパラメータを利用しなくても取り出せるんです。

ついでなので、マニュアルに記載のないパラメータを使ったDBMS_XPLAN.DISPLAY_AWRで同じ情報をリストしてみました。DBMS_XPLAN.DISPLAY_AWR以外のDISPLAY*関数のformatパラメータでもできます:)
(注: DISPLAY_AWRでAWRを参照するのでOracle Diagnostics Packが必要でっす。 ですが、DISPLAY_CURSOR(),DISPLAY()とDISPLAY_PLAN()ならAWRは参照しないのでオプションはいらないですよね。)

PROMPT
PROMPT ****** display_awr with outline option *********
SELECT
plan_table_output as outline
FROM
TABLE(DBMS_XPLAN.DISPLAY_AWR(sql_id=>'&1',plan_hash_value=>&2,format=>'OUTLINE'))
/


****** DBMS_XPLAN.DISPLAY_AWR with OUTLINE option *******

OUTLINE
------------------------------------------------------------------------------------------
SQL_ID gdtyuqcyk8x1c
--------------------
SELECT B.EXECUTION_ID, NVL(A.N_COUNT,0), A.COMP_TIME FROM ( SELECT
A.EXECUTION_ID, COUNT(*) N_COUNT, NVL(MAX(B.COMPLETION_TIME), SYSDATE)
COMP_TIME FROM SYS.ILM_EXECUTION$ A, SYS.ILM_RESULTS$ B WHERE
EXECUTION_STATE = :B7 AND A.EXECUTION_ID = B.EXECUTION_ID AND
B.JOB_STATUS NOT IN (:B6 , :B5 , :B4 , :B3 , :B2 , :B1 ) GROUP BY
A.EXECUTION_ID )A, ILM_EXECUTION$ B WHERE B.EXECUTION_ID =
A.EXECUTION_ID (+) AND EXECUTION_STATE = :B7 AND (ROWNUM <= :B9 OR :B9
= :B8 )

Plan hash value: 2236229349

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| |
| 1 | COUNT | | | | | |
| 2 | FILTER | | | | | |
| 3 | MERGE JOIN OUTER | | 1 | 65 | 4 (50)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID | ILM_EXECUTION$ | 1 | 26 | 0 (0)| |
| 5 | INDEX FULL SCAN | PK_TASKID | 1 | | 0 (0)| |
| 6 | SORT JOIN | | 1 | 39 | 4 (50)| 00:00:01 |
| 7 | VIEW | | 1 | 39 | 3 (34)| 00:00:01 |
| 8 | HASH GROUP BY | | 1 | 65 | 3 (34)| 00:00:01 |
| 9 | MERGE JOIN | | 1 | 65 | 3 (34)| 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID| ILM_EXECUTION$ | 1 | 26 | 0 (0)| |
| 11 | INDEX FULL SCAN | PK_TASKID | 1 | | 0 (0)| |
| 12 | SORT JOIN | | 1 | 39 | 3 (34)| 00:00:01 |
| 13 | TABLE ACCESS FULL | ILM_RESULTS$ | 1 | 39 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

Outline Data
-------------

/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "B"@"SEL$1" ("ILM_EXECUTION$"."EXECUTION_ID"))
NO_ACCESS(@"SEL$1" "A"@"SEL$1")
LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
USE_MERGE(@"SEL$1" "A"@"SEL$1")
PQ_FILTER(@"SEL$1" SERIAL)
INDEX(@"SEL$2" "A"@"SEL$2" ("ILM_EXECUTION$"."EXECUTION_ID"))
FULL(@"SEL$2" "B"@"SEL$2")
LEADING(@"SEL$2" "A"@"SEL$2" "B"@"SEL$2")
USE_MERGE(@"SEL$2" "B"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$2")
END_OUTLINE_DATA
*/

ちなみに、11g R2でも動作します!


どうでしたか?役に立たなかったですよね?
以上、OUTLINE HINTSより愛を込めてお送りしました!


参考: (日本語で書かれたエントリーは見当たらない。初か、もしかして!w)

Oracle SQL Plan Stability
http://blog.tanelpoder.com/oracle/performance/sql/oracle-sql-plan-stability/


Plan stability in 10g - using existing cursors to create Stored Outlines and SQL profiles
http://oracle-randolf.blogspot.jp/2009/03/plan-stability-in-10g-using-existing.html


FORCE_MATCH for Stored Outlines and/or SQL Baselines????? – follow up
https://tonyhasler.wordpress.com/2011/12/

How to hint – 1
https://jonathanlewis.wordpress.com/2011/06/08/how-to-hint-1/

dbms_xplan(3)
https://jonathanlewis.wordpress.com/2008/03/06/dbms_xplan3/



追記:

@yoshikawさんに指摘され、EXTRACTVALUE()が11.2で非推奨になっていたとことに気づく orz.

@yoshikawさん、指摘ありがとうございます。 

XMLTABLE().... COLUMNSを使えばEXTRACTVALUE()はいらなかった!!!! ということでEXTRACTVALUEなし版も作りました!!

ただ、CON_IDも見ないとマルチテナントに対応できず、エラーになると気づいたが、
11gにも対応しようとすると、もう一工夫必要なことに気づき、再び、orz.

つづきは、...いずれ...

変更したSQL文は以下のとおり。

SYSTEM> !cat report_outline_hints.sql
col outline for a200
set linesize 300
set pagesize 10000
set veri off

SELECT '/*+' AS outline FROM dual
UNION ALL
SELECT ' '||'BEGIN_OUTLINE_DATA' FROM DUAL
UNION ALL
SELECT
' '||x.outline_hint
FROM
XMLTABLE(
'/*/outline_data/hint/text()'
PASSING(
SELECT
XMLTYPE(other_xml)
FROM
dba_hist_sql_plan
WHERE
other_xml IS NOT NULL
AND sql_id = '&1'
AND plan_hash_value = &2
)
COLUMNS outline_hint PATH '/text()'
) x
UNION ALL
SELECT ' '||'END_OUTLINE_DATA' FROM DUAL
UNION ALL
SELECT '*/' FROM DUAL
/

SYSTEM> @report_outline_hints gdtyuqcyk8x1c 2236229349

OUTLINE
--------------------------------------------------------------------------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
DB_VERSION('12.1.0.2')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "B"@"SEL$1" ("ILM_EXECUTION$"."EXECUTION_ID"))
NO_ACCESS(@"SEL$1" "A"@"SEL$1")
LEADING(@"SEL$1" "B"@"SEL$1" "A"@"SEL$1")
USE_MERGE(@"SEL$1" "A"@"SEL$1")
PQ_FILTER(@"SEL$1" SERIAL)
INDEX(@"SEL$2" "A"@"SEL$2" ("ILM_EXECUTION$"."EXECUTION_ID"))
FULL(@"SEL$2" "B"@"SEL$2")
LEADING(@"SEL$2" "A"@"SEL$2" "B"@"SEL$2")
USE_MERGE(@"SEL$2" "B"@"SEL$2")
USE_HASH_AGGREGATION(@"SEL$2")
END_OUTLINE_DATA
*/

JPOUG Advent Calendar 2015、12/5は、Yohei Azekatsu さんです。どんな変態ネタが飛び出すのか、乞うご期待!!

| | コメント (0) | トラックバック (0)

2015年10月 1日 (木)

JPOUG> SET EVENTS 20151017 を開催します!

JPOUG主催

JPOUG> SET EVENTS 20151017

を日本オラクル青山センター 13階セミナールームで
2015年10月17日(土)13:00~17:30(開場および受付開始: 12:30)に開催します。

ハッシュタグ #JPOUG

今回は、
開発設計、チューニング関連の力セッション、インフラ関連の技セッション、入門者向けの知セッションと3本立てです。

JPOUGボードメンバーでもある渡部さん、通しで3枠!!  知セッションを担当します!
Oracle Database 入学式のような感じになるのでしょうか? Oracle Database初心者におすすめです。


そして、ミックさんをはじめ、JPOUG主催イベント初登場のスピーカーが多いのも今回の特徴です。


セッションの詳細は以下サイトをご覧ください。
http://www.jpoug.org/2015/09/01/setevents20151017
Logo20151017_851x478

私は裏方ですので、うろうろしているか、受付とか、やってると思います :)




ところで、皆さん。 Oracle Database 7.3とOracle Database 12.1のSELECT文がどれだけ進化、複雑化したかイメージできてますか?

SELECT文があれば更新削除以外は、なんでもできるんじゃないかと、錯覚するほと変わってきています。
これから先も変化すると思います。(基本は同じですが。)

付いていけてますか?  

まさか、Oracle Database 7.3とか、8とか8iぐらいのSELECT文で止まったままになってないでしょうか?
使いこなせてますか?

たまに、自問自答してます......

Oracle Database 7.3とOracle Database 12.1のSELECT文の文法の差をこうやってみると.....お・ど・ろ・き・ま・す・よ!!!!

20150926_223247




7.3と12.1のSELECT文の差の詳細はマニュアルで!

Oracle Database 7.3 SQL Reference
Oracle Database 12.1 SQL Reference - SELECT

| | コメント (0) | トラックバック (0)

2015年5月17日 (日)

Oracle Database 入学式 保護者の方はご遠慮ください 3/13, 4/15

Oracle Database 入学式 保護者の方はご遠慮ください
と題して、3月13日、4月15日に入門者向け勉強会を株式会社コーソルさんの会場をお借りして開催しました。
(私は受付たり、tweetしていただけですが :)

サブタイトルにあるように、マサカリをなげそうな保護者の方が紛れ込まないよう、
金属探知機で入念にチェック(嘘ですw

Oracle Database Entrance Ceremony – Touchdown / 諸橋さん

入学式を担当してくださった、先生方の紹介

Introduction of Oracle Database Architecture / 渡部 亮太
新・門外不出のOracle現場ワザ エキスパートが明かす運用・管理の極意 /小田 圭二

以前関わったことのあるプロジェクトの方から、今回は参加できなかったが次回は是非参加したいとのメッセージが届いているんですが、タイトルが”入学式”なので、次回って来年かも。
(タイトル変えればなんとかなる?か?w  

| | コメント (0) | トラックバック (0)

2015年1月 1日 (木)

机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!! (予想解答編)

JPOUG Advent Calendar 2014 17日目のエントリーで公開したクイズの予想解答編です。
机上ですし、限られた情報しか提示していないので、実際にやってみたら違う...ということも十分ありえます。 (^^;;;;


外部表はどれがいいか予想できると、いろいろな制限のあるチューニング現場では役立つこともあるんです。LEADINGヒント等で結合順を考える場合にも役立ちますyo!
机上の予想なので間違うこともあります。オプティマイザも統計情報から予想しているわけで(Adaptiveな機能を除く)ハズすことも多いですから... (^^

Oracle® Database SQLチューニング・ガイド 12cリリース1(12.1) B71277-02 - 7 結合


問題1
次に示すSQL文の駆動表(外部表)はどれでしょうか? 

表と索引
create table a1 (
id number primary key
, data varchar2(1000)
) nologging
/
create table a2 (
id number primary key
, data varchar2(1000)
) nologging
/

統計情報
TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- -------------- ---------- ------------- -----------------
A1 SYS_C0010377 10000 10000 295
A2 SYS_C0010378 2000 2000 59

SELECT
/* SQL01 */
/*+
MONITOR
USE_NL(a1 a2)
*/
*
FROM
a1
INNER JOIN a2
ON
a1.id = a2.id
WHERE
a1.id BETWEEN 1 AND 100
/

私の答え(予想)
Nested Loop結合かつ、INNER JOINですから駆動表(外部表)はデータセットの小さい方。
(Nested Loop結合でOUTER JOINだと結合順は固定されるので駆動表は見つけやすいですが...INNER JOINの場合はそうはいかないですよね)

ただ、結合条件キーはどちらも主キーですし、WHERE a1.id BETWEEN 1 AND 100は、a2表にも適用されることになるでしょうから、a1、a2どちらも最大で100行ヒットする可能性はあります。
どちらでも正解になる可能性はありますが...w (最初から引っ掛けかよw

細かい条件は提示していないので、提示した情報だけで机上で駆動表(外部表)を決めるとすれば、全体のサイズが小さい、a2でいいんじゃないでしょうか?(私ならそうします。机上ですから)
(当初、もう少し詳細な情報を提示しようとしていたのですが忘れてました...なのでa1だと思った方も可能性は五分五分ですね。前提条件不足でした。...ごめんなさい。ごめんなさい。)


問題2
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
(表と索引、および、統計情報は問題1と同じです。)

SELECT
/* SQL02 */
/*+
MONITOR
USE_HASH(a1 a2)
*/
*
FROM
a1
INNER JOIN a2
ON
a1.id = a2.id
/

私の答え(予想)
Hash結合でINNER JOINかつ、WHERE句はないのでこの問題は簡単ですよね! (これは迷わないはず!!!)

Hash結合も外部表はデータセットの小さい方ですから...この場合だと、a2が外部表になるはず。

問題3
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
なお、D1とD2の多重度は、D1:D2 = 1:100
(個人的にUMLの多重度表記のほうが好きなので、UML表記の多重度で記述します。

表と索引
create table d1 (
id number
, data varchar2(1000)
) nologging
/
alter table d1 add constraint pk_d1 primary key (id) using index nologging
/
create table d2 (
id number not null
, seq# number not null
, data varchar2(1000)
) nologging
/
alter table d2 add constraint pk_d2 primary key (id, seq#) using index nologging
/

統計情報
TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- -------------- ---------- ------------- -----------------
D1 PK_D1 200 200 9
D2 PK_D2 20000 20000 870

SELECT
/* SQL03 */
/*+
MONITOR
*/
*
FROM
d2
WHERE
EXISTS (
SELECT
1
FROM
d1
WHERE
d1.id = d2.id
AND d1.id IN (1,5)
)
/


私の答え(予想)
これはちょっと意地悪な問題ですが、わかる人ならわかるはず。だと思って(信じて)作った問題です。 :)

最近のオプティマイザは、相関副問合せを可能であれば結合に書き換える(unnest)傾向が強いのをご存知でしょうか? 
となれば、方向はだいだい見えてきます。

d1.id IN (1,5)から最大で2件ヒットすると予想できますよね。
さらに、Nested Loop結合に書き換え、d2を内部表として結合できれば無駄がない。
つまり、駆動表は d1が理想的なはず。

d2を外部表にしてしまうと、WHERE条件がないので結合に置き換えてしまうとHash結合または、d2を全表走査して相関副問合せを都度実行のいづれかになってしまうでしょうね。(unnestの傾向が強いのでHash結合になる可能性が高そう)
ヒットするデータ件数から考えると、どちらも無駄なデータアクセスが多くなる可能性は高いのではないでしょうか。

問題4
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
(表と索引、および、統計情報は問題3と同じです。)

SELECT
/* SQL04 */
/*+
MONITOR
*/
*
FROM
d1
INNER JOIN d2
ON
d1.id > d2.id
AND d2.id BETWEEN 1 AND 5
AND d2.seq# BETWEEN 2 AND 4
/

私の答え(予想)
結合条件が ">" であることに気付きましたか? ;)


これは、Nested Loop結合にも、Hash結合にもなりません。 Merge(sort/merge)結合が選択されます。

内部表は索引が利用できないのでソートが発生します。(ソートは避けられない)
外部表では索引を利用してソート処理が回避可能であれば、回避する。

つまりソート処理の影響を最小にしようとするはず、なので...

d1:d2=1:100という比率からd2のソート処理を重いと判断し回避するために外部表にするだろうなぁ〜〜。
私は、d2が外部表になるだろうと予想しています。
(直積じゃないMerge結合なんて最近お目にかかったことないのですが...)

余談)
Hash結合のない時代のOracleで、Merge結合の実行計画を見せられて、なんでソートしてるんですかね〜と、某ベンダーの方に質問されて一瞬固まったことを思い出したw


問題5
次に示すSQL文の駆動表(外部表)はどれでしょうか? 

表と索引定義
create table b1 (
id number
, data varchar2(1000)
) nologging
/
alter table b1 add constraint pk_b1 primary key(id) using index nologging
/
create table b3 (
id number
, seq# number
, data varchar2(1000)
) nologging
/
alter table b3 add constraint pk_b3 primary key (id, seq#) using index nologging
/
create table b2 (
id number
, seq# number
, subseq# number
, data varchar2(1000)
) nologging
/
alter table b2 add constraint pk_b2 primary key (id ,seq#, subseq#) using index nologging
/

統計情報
TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- -------------- ---------- ------------- -----------------
B1 PK_B1 20000 20000 870
B2 PK_B2 5000 5000 228
B3 PK_B3 500 500 23

ERDと多重度
b1 : b3 = 1 : 0..2
b3 : b2 = 1 : 0..10
(UML表記の多重度で記述しています。
20141221_120733

SELECT
/* SQL05 */
/*+
MONITOR
USE_HASH(b1 b3 b2)
*/
*
FROM
b1
INNER JOIN b3
ON
b1.id = b3.id
INNER JOIN b2
ON
b3.id = b2.id
AND b3.seq# = b2.seq#
/

私の答え(予想)
INNER JOINでHash結合かつ、WHERE句もないので簡単な問題ですよね。

外部表は、b3

ついでに、続けると、 b3とb2を結合すると最大で5000件、b3とb1を結合した場合、最大で500件なので、 結合順として理想的なのは、 b3, b1, b2 ですよね。


問題6
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
(表と索引、および、統計情報は問題5と同じです。)

SELECT
/* SQL06 */
/*+
MONITOR
USE_HASH(b1 b3 b2)
*/
*
FROM
b1
LEFT OUTER JOIN b3
ON
b1.id = b3.id
LEFT OUTER JOIN b2
ON
b3.id = b2.id
AND b3.seq# = b2.seq#
/

私の答え(予想)
OUTER JOINなのですが、Hash結合なので、多分、予想通りにはならないだろうな〜〜と。

最近のオプティマイザは外部結合かつHash結合の場合、外部表と内部表をデータセットサイズに応じて入れ替える傾向が強いんですよね。PGAの使用サイズが小さくなるように....

とは言っても、机上の話なので、入れ替えないとしたらどうなるだろう..と、外部結合なので、内部表は、そのまま内部表として結合したほうがわかりやすいといえばわかりやすい。
(統計情報に乖離がある場合には入れ替えないほうが良い結果だったりすることもあります。)


注)()内を先に結合するものとします。
入れ替えなかった場合は、記述したままなので、(外部表、内部表)=外部表、内部表ということで、 (b1, b3), b2
データセットの小さい順に従えば、外部表と、内部表をいれかえて、b3, b1。
この結果セットの最大件数は元の外部表がb1ですから20,250件、b2が5,000件なので、また外部表と内部表を入れ替えて、結果として、b2, (b3, b1)ってことに...
(内部的に結合順が入れ替えられ、Right Joinに置き換えられることが多いんですよね〜いつ頃からだろう...10gあたりか。)

で、机上ならどちらを取るかということですが、机上では、ひとまず、SQL文の通りとしておくケースが多いです。あくまで私の場合ですよ。(難しいんですよ、これ。外れる可能性大w)
オプティマイザが内部的に外部表と内部表を入れ替えて問題がなければよし、問題があれば、内部的な入れ替えをヒントで止めるというのが私の基本方針なので、この辺りは、チューナーさんのさじ加減次第じゃないかなぁ、と思っています。

あはは、むずかし過ぎたか... 机上だと orz.

結合順がほぼ想定できる場合、状況次第で変わりそうな結合順、いろいろありますよね。
オプティマイザの気持ちになって考えてみると、いろいろ気づくことも多いんじゃないかなぁ。と思います。

オプティマイザってソートが嫌いなんだ〜、とか、PGA少ない方が好きなんだ〜とか....


役に立ったか、どうなのか不明なオチになりましたが.....

本年もよろしくお願いいたします。 m(_ _)m


あ、そうそう、一つ忘れてました。

次回は、Oracle Database 12c 12.1.0.2.0を使って試したオプティマイザが選択した駆動表(外部表)がどれだったかのか公開する予定です。


机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!!

| | コメント (0) | トラックバック (0)

2014年12月17日 (水)

机上SQLチューニング、クイズ! 駆動表(外部表)はどれだ!!!!

予備プラン発動中 :)

ということで、諸事情により開け忘れた窓を開けるためJPOUG Advent Calendar 2014 2回目の登場となりました。

JPOUG Advent Calendar 2014 17日目のエントリーです。


突然ですが、

「机上SQLチューーーーーニング、クイズ〜〜〜っ!!!!!!
 駆動表(外部表)はどれだ!!!!」


なお、このクイズには次の制限があります。

別途、本ブログで解答エントリーが公開(年明けを予定)されるまで、Oracle Databaseを利用して答え求めるのは禁止。 :-)
Oracle Databaseを利用せず、机上で、どの表を駆動表(外部表)にすれば理想的な実行計画になりそうか考えてみてね。

また、解答はOracle Database 12c Release 1 12.1.0.2.0 のオプティマイザをインストールしたまま(初期化パラメータはデフォルトのまま)の環境を使って行います。
(11gでも違わないと思いますが)

というクリスマスプレゼント :)

次に示されるSQL文の駆動表(外部表)はどれでしょうか? 

前提

統計情報と実データとの間に乖離はありません。
リテラル値で指定した検索条件に該当するデータは必ず存在します。


※引っ掛け問題もあるよ! :)


問題1
次に示すSQL文の駆動表(外部表)はどれでしょうか? 

表と索引
create table a1 (
id number primary key
, data varchar2(1000)
) nologging
/
create table a2 (
id number primary key
, data varchar2(1000)
) nologging
/

統計情報
TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- -------------- ---------- ------------- -----------------
A1 SYS_C0010377 10000 10000 295
A2 SYS_C0010378 2000 2000 59

SELECT
/* SQL01 */
/*+
MONITOR
USE_NL(a1 a2)
*/
*
FROM
a1
INNER JOIN a2
ON
a1.id = a2.id
WHERE
a1.id BETWEEN 1 AND 100
/

問題2
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
(表と索引、および、統計情報は問題1と同じです。)

SELECT
/* SQL02 */
/*+
MONITOR
USE_HASH(a1 a2)
*/
*
FROM
a1
INNER JOIN a2
ON
a1.id = a2.id
/

問題3
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
なお、D1とD2の多重度は、D1:D2 = 1:100
(個人的にUMLの多重度表記のほうが好きなので、UML表記の多重度で記述します。

表と索引
create table d1 (
id number
, data varchar2(1000)
) nologging
/
alter table d1 add constraint pk_d1 primary key (id) using index nologging
/
create table d2 (
id number not null
, seq# number not null
, data varchar2(1000)
) nologging
/
alter table d2 add constraint pk_d2 primary key (id, seq#) using index nologging
/

統計情報
TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- -------------- ---------- ------------- -----------------
D1 PK_D1 200 200 9
D2 PK_D2 20000 20000 870

SELECT
/* SQL03 */
/*+
MONITOR
*/
*
FROM
d2
WHERE
EXISTS (
SELECT
1
FROM
d1
WHERE
d1.id = d2.id
AND d1.id IN (1,5)
)
/


問題4
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
(表と索引、および、統計情報は問題3と同じです。)

SELECT
/* SQL04 */
/*+
MONITOR
*/
*
FROM
d1
INNER JOIN d2
ON
d1.id > d2.id
AND d2.id BETWEEN 1 AND 5
AND d2.seq# BETWEEN 2 AND 4
/


問題5
次に示すSQL文の駆動表(外部表)はどれでしょうか? 

表と索引定義
create table b1 (
id number
, data varchar2(1000)
) nologging
/
alter table b1 add constraint pk_b1 primary key(id) using index nologging
/
create table b3 (
id number
, seq# number
, data varchar2(1000)
) nologging
/
alter table b3 add constraint pk_b3 primary key (id, seq#) using index nologging
/
create table b2 (
id number
, seq# number
, subseq# number
, data varchar2(1000)
) nologging
/
alter table b2 add constraint pk_b2 primary key (id ,seq#, subseq#) using index nologging
/

統計情報
TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
-------------- -------------- ---------- ------------- -----------------
B1 PK_B1 20000 20000 870
B2 PK_B2 5000 5000 228
B3 PK_B3 500 500 23

ERDと多重度
b1 : b3 = 1 : 0..2
b3 : b2 = 1 : 0..10
(UML表記の多重度で記述しています。
20141221_120733

SELECT
/* SQL05 */
/*+
MONITOR
USE_HASH(b1 b3 b2)
*/
*
FROM
b1
INNER JOIN b3
ON
b1.id = b3.id
INNER JOIN b2
ON
b3.id = b2.id
AND b3.seq# = b2.seq#
/


問題6
次に示すSQL文の駆動表(外部表)はどれでしょうか? 
(表と索引、および、統計情報は問題5と同じです。)

SELECT
/* SQL06 */
/*+
MONITOR
USE_HASH(b1 b3 b2)
*/
*
FROM
b1
LEFT OUTER JOIN b3
ON
b1.id = b3.id
LEFT OUTER JOIN b2
ON
b3.id = b2.id
AND b3.seq# = b2.seq#
/


冬休みの宿題〜〜〜〜っ。暇つぶしにトライしてみてくださいね。(ニヤニヤ 

解答エントリーの公開は年明けを予定していま〜す。

We wish your merry christmas and a happy new year!

次の扉は、Tamie Yamamotoさんです。

| | コメント (0) | トラックバック (0)

2014年12月 9日 (火)

私のチューニング、アダプティブなオプティマイザにも、今のところ勝てそうな気がするぞ〜〜〜っ。と思った師走のある日。

このエントリは JPOUG Advent Calendar 2014 9日目のエントリです。 昨日のエントリは wmo6hashさんの Goodbye, Patch Set. さよなら、PSR。でした。

アダプティブなオプティマイザ関連のアドベントカレンダーネタに、ついカッとなったところで、アダプティブな結合を試してみようと、一杯の珈琲を飲み、気持ちを落ち着けたアカウントがこちらになりますw


環境は以下の通り。

Oracle Linux Server release 6.6
Linux 3.8.13-44.1.3.el6uek.x86_64 #2 SMP Wed Oct 15 19:53:10 PDT 2014 GNU/Linux


BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0
PL/SQL Release 12.1.0.2.0 - Production 0
CORE 12.1.0.2.0 Production 0
TNS for Linux: Version 12.1.0.2.0 - Production 0
NLSRTL Version 12.1.0.2.0 - Production 0


以下のような2表を用意しました。他のネタに作った表なのですごーく適当ですが、ID列は単一列の主キー制約があります。その他に索引はありません。

SCOTT> desc maybe_driving_tab
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
ALTID NUMBER
DATA VARCHAR2(2000)
STATUS NOT NULL NUMBER(2)

SCOTT> desc maybe_inner_tab
名前 NULL? 型
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
ALTID NUMBER
DATA VARCHAR2(2000)
STATUS NOT NULL NUMBER(2)


そして、これまた適当にデータを登録して統計を取っちゃいます。

SCOTT> truncate table maybe_driving_tab;
SCOTT> truncate table maybe_inner_tab;
SCOTT> insert into maybe_driving_tab values(1,1,1,1);
SCOTT> insert into maybe_driving_tab values(2,1,1,1);
SCOTT> insert into maybe_inner_tab select * from maybe_driving_tab;
SCOTT> commit;
SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'MAYBE_DRIVING_TAB',cascade=>true,no_invalidate=>false,);
SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'MAYBE_INNER_TAB',cascade=>true,no_invalidate=>false);

この時点で代表的な統計情報を見てみると、こんな感じです。

SCOTT> select table_name,index_name,num_rows,distinct_keys,clustering_factor from user_indexes where table_name like 'MAYBE%'

TABLE_NAME INDEX_NAME NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ------------- -----------------
MAYBE_DRIVING_TAB PK_MAYBE_DRIVING_TAB 2 2 1
MAYBE_INNER_TAB PK_MAYBE_INNER_TAB 2 2 1

今回、主役となるid列は、連番で登録します。このタイプはクラスタリングファクタも低くなりindex range scanの有効範囲が広めになる傾向があります。しかし、このデータを登録する前に統計情報を取得(2件の時に)したため、実データと統計情報は大きく乖離しています。Oracle Database 12c 12.1.0.2.0 のオプティマイザにちょっとした意地悪をしています。

BEGIN
FOR i IN 3..200000 LOOP
INSERT INTO maybe_driving_tab VALUES(
i
,CEIL(DBMS_RANDOM.VALUE(1,400001))
,LPAD(i,500,'*')
,MOD(i,2))
;
INSERT INTO maybe_inner_tab VALUES(
i
,CEIL(DBMS_RANDOM.VALUE(1,400001))
,LPAD(i,500,'*')
,MOD(i,2))
;
IF MOD(i,100) = 0 THEN
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/


統計取得後に、これだけ大量のデータを登録していれば....統計情報は失効しています

SCOTT> select table_name,num_rows,stale_stats from user_tab_statistics where table_name like 'MAYBE%';

TABLE_NAME NUM_ROWS STA
------------------------------ ---------- ---
MAYBE_DRIVING_TAB 2 YES
MAYBE_INNER_TAB 2 YES


統計情報上は2行ですが...実際は20万行あります (^^;; かなり意地の悪い状況にしてありますが、状況的になくもないと思います。

SCOTT> select count(1) from maybe_driving_tab;

COUNT(1)
----------
200000

SCOTT> select count(1) from maybe_inner_tab;

COUNT(1)
----------
200000


はい、準備はできました。ここからが本題ですよ〜〜っ。

次のようなSQL文を実行してみます。
AUTOTRACEのNote部分に、12cから登場したadaptive planが適用されていることが示されてます。(見逃さないでくださいよ。
adaptiveな機能が多くなり、リテラル値を使っていても実際の実行計画とは異なる実行計画が示されることが多くなってきてるんです。
dbms_xplan.display_cursor(format=>'ALLSTATS LAST')、SQLトレース、または、SQL監視機能を使って実際に選択されている実行計画の確認が重要になった、ということですからね!! これ大事ですよ。

SCOTT> r
1 SELECT
2 /* SQL101 */
3 /*+
4 MONITOR
5 */
6 *
7 FROM
8 maybe_driving_tab t1
9 INNER JOIN maybe_inner_tab t2
10 ON
11 t1.id = t2.id
12 WHERE
13* t1.id BETWEEN 1 and 30000

30000行が選択されました。

実行計画
----------------------------------------------------------
Plan hash value: 3943187719

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| maybe_driving_tab | 2 | 22 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 11 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

4 - access("T1"."ID">=1 AND "T1"."ID"<=30000)
5 - access("T1"."ID"="T2"."ID")
filter("T2"."ID">=1 AND "T2"."ID"<=30000)

Note
-----
- this is an adaptive plan


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
8573 consistent gets
2159 physical reads
0 redo size
31686000 bytes sent via SQL*Net to client
22541 bytes received via SQL*Net from client
2001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
30000 rows processed

では、実際に実行された実行計画をSQL監視機能(オプションが必要な機能です)を使って見てみます。

11gまでは見たこともない不思議な光景になっていますよね。これがAdaptive Joinの実行計画。ほうほうって感じです。

この実行計画の中には2つの実行計画が混在しています。実際に実行された操作もあれば、実行されない操作も含まれています。

どの操作が実行されたかみるには、Execs列をみます。

ね、ね、ね。 
Nested Loop結合じゃなくて、Hash結合が実行されています。
はじめは、Nested Loop結合をやろうとしてるのわかりますか?

ID=4のStatistics Collectorとう操作で実行時の統計(このケースでは行数でしょうかね??? 想像ですが)をみて、Nested Loop結合を寸止めして(妥当な表現かあやしいが)、ハッシュ結合に切り替えた様子が見えますよね。。。。ちょいと感動したw

ただし、index range scanするには行数的に微妙な状況になりつつありそうなところだと思います。

======================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
======================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 5 | +0 | 1 | 30000 | | | | | |
| 1 | HASH JOIN | | 2 | 4 | 5 | +0 | 1 | 30000 | | | 20M | | |
| 2 | NESTED LOOPS | | 2 | 4 | | | 1 | | | | | | |
| 3 | NESTED LOOPS | | 2 | 4 | | | 1 | | | | | | |
| 4 | STATISTICS COLLECTOR | | | | 1 | +0 | 1 | 0 | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | maybe_driving_tab | 2 | 2 | 1 | +0 | 1 | 30000 | | | | | |
| 6 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | 1 | 1 | +0 | 1 | 30000 | | | | | |
| 7 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | | | | | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 1 | | | | | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED | maybe_inner_tab | 1 | 1 | 5 | +0 | 1 | 30000 | 2108 | 16MB | | | |
| 10 | INDEX RANGE SCAN | PK_maybe_inner_tab | 1 | | 5 | +0 | 1 | 30000 | 51 | 408KB | | | |
======================================================================================================================================================================================


さらに、取得件数を倍にしてみました。

どうなるでしょう。

割合的には、30%の範囲検索です。(統計情報次第では全表走査+ハッシュ結合に切り替わることも無くはない状況ですが.....)

SELECT
/* SQL102 */
/*+
MONITOR
*/
*
FROM
maybe_driving_tab t1
INNER JOIN maybe_inner_tab t2
ON
t1.id = t2.id
WHERE
t1.id BETWEEN 1 and 60000
/

60000行が選択されました。

実行計画
----------------------------------------------------------
Plan hash value: 3943187719

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| maybe_driving_tab | 2 | 22 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 11 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

4 - access("T1"."ID">=1 AND "T1"."ID"<=60000)
5 - access("T1"."ID"="T2"."ID")
filter("T2"."ID">=1 AND "T2"."ID"<=60000)

Note
-----
- this is an adaptive plan


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
17142 consistent gets
9230 physical reads
0 redo size
63391324 bytes sent via SQL*Net to client
44541 bytes received via SQL*Net from client
4001 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
60000 rows processed


全体の30%の範囲検索でもギリギリ、Index range scanしているようです。 index rowid batchedとう操作が効いているためでしょうか。そのあとに、ハッシュ結合をしています。
index rowid batchedという不連続ブロックの一括読み込みを繰り返して、全体の30%程度のindex range scanを 1度のオペレーションで行っている箇所(ID=5や9)は興味深いですよね。
(クラスタリングファクタが低いと、実はtable full scanと大差なかったりして〜〜〜と思ったり、思わなかったり)

======================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
======================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 9 | +0 | 1 | 60000 | | | | | |
| 1 | HASH JOIN | | 2 | 4 | 9 | +0 | 1 | 60000 | | | 36M | | |
| 2 | NESTED LOOPS | | 2 | 4 | 1 | +0 | 1 | 1 | | | | | |
| 3 | NESTED LOOPS | | 2 | 4 | 1 | +0 | 1 | 1 | | | | | |
| 4 | STATISTICS COLLECTOR | | | | 1 | +0 | 1 | 60000 | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | maybe_driving_tab | 2 | 2 | 1 | +0 | 1 | 60000 | 4615 | 36MB | | | |
| 6 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | 1 | 1 | +0 | 1 | 60000 | | | | | |
| 7 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | | | | | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 1 | | | | | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED | maybe_inner_tab | 1 | 1 | 9 | +0 | 1 | 60000 | 4615 | 36MB | | | |
| 10 | INDEX RANGE SCAN | PK_maybe_inner_tab | 1 | | 9 | +0 | 1 | 60000 | | | | | |
======================================================================================================================================================================================


次に、全体の40%程度の範囲検索にしてみます。
全表走査+ハッシュ結合が理想だと思うのですが、adaptive joinでhash結合になっているので、全表走査になりそうな予感はしますが...

SELECT
/* SQL103 */
/*+
MONITOR
*/
*
FROM
maybe_driving_tab t1
INNER JOIN maybe_inner_tab t2
ON
t1.id = t2.id
WHERE
t1.id BETWEEN 1 and 80000
/

80000行が選択されました。

実行計画
----------------------------------------------------------
Plan hash value: 3943187719

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| maybe_driving_tab | 2 | 22 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 11 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

4 - access("T1"."ID">=1 AND "T1"."ID"<=80000)
5 - access("T1"."ID"="T2"."ID")
filter("T2"."ID">=1 AND "T2"."ID"<=80000)

Note
-----
- this is an adaptive plan


統計
----------------------------------------------------------
4 recursive calls
0 db block gets
22856 consistent gets
3393 physical reads
0 redo size
84528237 bytes sent via SQL*Net to client
59215 bytes received via SQL*Net from client
5335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
80000 rows processed


ん〜〜〜〜〜っ。adaptive joinでhash結合にはなるもの全表走査にはなかなかなってくれません。むむむ。

==============================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (Max) | (%) | (# samples) |
==============================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 12 | +0 | 1 | 80000 | | | | | | | | |
| 1 | HASH JOIN | | 2 | 4 | 12 | +0 | 1 | 80000 | 21 | 2MB | 21 | 2MB | 48M | 3M | | |
| 2 | NESTED LOOPS | | 2 | 4 | 1 | +0 | 1 | 1 | | | | | | | | |
| 3 | NESTED LOOPS | | 2 | 4 | 1 | +0 | 1 | 1 | | | | | | | | |
| 4 | STATISTICS COLLECTOR | | | | 1 | +0 | 1 | 80000 | | | | | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | maybe_driving_tab | 2 | 2 | 1 | +0 | 1 | 80000 | 1539 | 12MB | | | | | | |
| 6 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | 1 | 1 | +0 | 1 | 80000 | | | | | | | | |
| 7 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | | | | | | | | | | | | |
| 8 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 1 | | | | | | | | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED | maybe_inner_tab | 1 | 1 | 12 | +0 | 1 | 80000 | 1539 | 12MB | | | | | | |
| 10 | INDEX RANGE SCAN | PK_maybe_inner_tab | 1 | | 12 | +0 | 1 | 80000 | | | | | | | | |
==============================================================================================================================================================================================================


ちょっと、ここで寄り道してみます。
adaptive joinを無効にしたら、多分、一番だめな感じのNested Loop結合になるはずなので確認しておきましょう。
隠しパラメータを %adaptive%で検索して見つけたのがそのものズバリの隠しパラメータそ使つかいます。 :)

AUTOTRACEから adaptive planというNoteが消えましたねぇ。(^^

SELECT
/* SQL104 */
/*+
MONITOR
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
*/
*
FROM
maybe_driving_tab t1
INNER JOIN maybe_inner_tab t2
ON
t1.id = t2.id
WHERE
t1.id BETWEEN 1 and 80000
/


80000行が選択されました。

実行計画
----------------------------------------------------------
Plan hash value: 3943187719

--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 44 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED| maybe_driving_tab | 2 | 22 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | | 1 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 11 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

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

4 - access("T1"."ID">=1 AND "T1"."ID"<=80000)
5 - access("T1"."ID"="T2"."ID")
filter("T2"."ID">=1 AND "T2"."ID"<=80000)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
102603 consistent gets
12608 physical reads
0 redo size
84528237 bytes sent via SQL*Net to client
59215 bytes received via SQL*Net from client
5335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
80000 rows processed


統計情報との乖離が大きいので、Nested Loop結合でindex range scanしてますねぇ〜。駆動表はtable access index rowid batchedになっていますが....
内部表は8万回もindex indexunique scanされてますね。

=============================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
=============================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 11 | +0 | 1 | 80000 | | |
| 1 | NESTED LOOPS | | 2 | 4 | 11 | +0 | 1 | 80000 | | |
| 2 | NESTED LOOPS | | 2 | 4 | 11 | +0 | 1 | 80000 | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | maybe_driving_tab | 2 | 2 | 11 | +0 | 1 | 80000 | | |
| 4 | INDEX RANGE SCAN | PK_maybe_driving_tab | 2 | 1 | 11 | +0 | 1 | 80000 | | |
| 5 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | 1 | | 11 | +0 | 80000 | 80000 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 1 | 11 | +0 | 80000 | 80000 | | |
=============================================================================================================================================================

寄り道はここまでにして、
オプティマイザがやってくれないなら、ヒントでチューニングしちゃいましょ。
hash結合+全表走査になるように....

SELECT
/* SQL105 */
/*+
MONITOR
OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
FULL(t1)
FULL(t2)
USE_HASH(t1 t2)
*/
*
FROM
maybe_driving_tab t1
INNER JOIN maybe_inner_tab t2
ON
t1.id = t2.id
WHERE
t1.id BETWEEN 1 and 80000
/

80000行が選択されました。


いい感じになったような気がしますが、consistent getsは、index range scan + table access bby index rowid batchedの方が少ないんですねぇ〜。
実データのクラスタリングファクタが高かったら違う結果になるような気がします。
今回は試しませんが。

実行計画
----------------------------------------------------------
Plan hash value: 1719838364

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 44 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 2 | 44 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| maybe_driving_tab | 2 | 22 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| maybe_inner_tab | 2 | 22 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

1 - access("T1"."ID"="T2"."ID")
2 - filter("T1"."ID">=1 AND "T1"."ID"<=80000)
3 - filter("T2"."ID">=1 AND "T2"."ID"<=80000)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
33390 consistent gets
30906 physical reads
0 redo size
84528237 bytes sent via SQL*Net to client
59215 bytes received via SQL*Net from client
5335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
80000 rows processed


==============================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
==============================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 12 | +0 | 1 | 80000 | | | | | |
| 1 | HASH JOIN | | 2 | 6 | 11 | +0 | 1 | 80000 | | | 50M | | |
| 2 | TABLE ACCESS FULL | maybe_driving_tab | 2 | 3 | 1 | +0 | 1 | 80000 | 252 | 121MB | | | |
| 3 | TABLE ACCESS FULL | maybe_inner_tab | 2 | 3 | 11 | +0 | 1 | 80000 | 251 | 121MB | | | |
==============================================================================================================================================================


統計との乖離があるのは確かなので統計情報を取り直してみます。(オプティマイザはどういった計画を導きだすか....)

SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'MAYBE_DRIVING_TAB',no_invalidate=>false,cascade=>true);
SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'MAYBE_INNER_TAB',no_invalidate=>false,cascade=>true);


実行!

SELECT
/* SQL108 */
/*+
MONITOR
*/
*
FROM
maybe_driving_tab t1
INNER JOIN maybe_inner_tab t2
ON
t1.id = t2.id
WHERE
t1.id BETWEEN 1 and 80000
/

80000行が選択されました。


実行計画
----------------------------------------------------------
Plan hash value: 1719838364

------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 80000 | 78M| | 12776 (1)| 00:00:01 |
|* 1 | HASH JOIN | | 80000 | 78M| 40M| 12776 (1)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| maybe_driving_tab | 80000 | 39M| | 4398 (1)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| maybe_inner_tab | 80000 | 39M| | 4398 (1)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

1 - access("T1"."ID"="T2"."ID")
2 - filter("T1"."ID"<=80000 AND "T1"."ID">=1)
3 - filter("T2"."ID"<=80000 AND "T2"."ID">=1)

Note
-----
- this is an adaptive plan


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
35859 consistent gets
15453 physical reads
0 redo size
84528237 bytes sent via SQL*Net to client
59215 bytes received via SQL*Net from client
5335 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
80000 rows processed


===========================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (Max) | (%) | (# samples) |
===========================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 12 | +0 | 1 | 80000 | | | | | |
| 1 | HASH JOIN | | 80000 | 12776 | 11 | +0 | 1 | 80000 | | | 51M | | |
| 2 | NESTED LOOPS | | 80000 | 12776 | | | 1 | | | | | | |
| 3 | NESTED LOOPS | | | | | | 1 | | | | | | |
| 4 | STATISTICS COLLECTOR | | | | 1 | +0 | 1 | 0 | | | | | |
| 5 | TABLE ACCESS FULL | maybe_driving_tab | 80000 | 4398 | 1 | +0 | 1 | 80000 | 252 | 121MB | | | |
| 6 | INDEX UNIQUE SCAN | PK_maybe_inner_tab | | | | | | | | | | | |
| 7 | TABLE ACCESS BY INDEX ROWID | maybe_inner_tab | 1 | 4398 | | | | | | | | | |
| 8 | TABLE ACCESS FULL | maybe_inner_tab | 80000 | 4398 | 11 | +0 | 1 | 80000 | | | | | |
===========================================================================================================================================================================

うん、やっと、オプティマイザと私の意見が一致したようだw
おら、オプティマイザより先にこの結果想定してたんだ! アダプティブなオプティマイザにちょっとだけ勝ったw ぞ〜〜〜〜っ. (クレヨン シンちゃん風に...)

そして....
table access by index rowid batched って状況次第ではあるものの、意外にいいかも。と思ったのであった。 (^^/
....今後の為に、さらにネタを集めたほうがいいかもしれない...


最後に、adaptiveな機能が多くなっていますが、その基礎になるのはやはり、統計情報と、その向こうにあるデータです。
機能に目が移りがちですが忘れちゃいけないのが、これらです。


以上、役に立つような、立たないようなネタでした。

あ、一つ忘れてました〜〜っ。
optimizer_dynamic_samplingが効きそうな気もしますが、今回はデフォルト(2)だったので結果は未確認です。だれかやってみて〜〜:)


JPOUG Advent Calendar 2014
明日は、yoku0825さんです。お楽しみに〜。

| | コメント (4) | トラックバック (0)

2014年11月19日 (水)

SQLチューニング総合診療所 ケースファイルX / db tech showcase tokyo 2014

恒例となってきたデータベースのお祭り db tech showcase tokyo

今年も、JPOUGのSQLチューニング総合診療医w として治療話をしてきました :)

20141119_222101


貴重な機会を提供いただいたインサイトテクノロジーの皆様、そして、お忙しい中、セッションに参加してくださった皆様、ありがとうございました。

当日は気合で乗り切ったものの翌日は力尽きて発熱で資料アップが遅れていましたが
L35 「SQLチューニング総合診療所 ケースファイルX」の資料をslideshareに公開しました。

来年のお祭りを楽しみにしています:)


あ、そうそう、
Craig Shallahamer さんのセッションを聴講したときに感じたのですが、「本人が楽しそうに話す」の重要!!
ですね。 :)


2013年以前のセッション資料は以下から。
- db tech showcase tokyo 2013 - A35 - JPOUG特濃:潮溜まりでジャブジャブ、SQLチューニング Tweet
- Unconference at db tech showcase 2012の資料公開 :)

| | コメント (0) | トラックバック (0)

2014年8月26日 (火)

JPOUG> SET EVENTS 20140907 開催を開催します! 

2014年9月7日(日)13:00~17:00(開場および受付開始: 12:30)に、
IIJさんに会場をお借りし、JPOUG> SET EVNETS 20140907を開催します。

参加方法やプログラムの詳細は、JPOUG> SET EVENTS 20140907をご覧ください。

では、会場でお会いしましょう。 (^^)/  <<<恐らく受け付けをやっていると思います :)

Jpougsetevents20140907w902

| | コメント (0) | トラックバック (0)

2013年12月31日 (火)

SELECT ~ FOR UPDATE SKIP LOCKED その1 - @sh2ndさんのエントリの復習など

JPOUT Advent Calender 2013の@sh2ndさんのエントリーが面白かったので大晦日に酒飲みながら... :)
まずは twitterでのやり取りなど....


20131231_113305


20131231_163205

イケテナイとか、良いとか、自由に言えるのはユーザーだからこそだと思うんだ。
セールストークじゃない生の情報って大切だ。


ということで、skip locked へ行く前に、Oracle11g R1 11.1.0.7.0 および、Oracle12c R1 12.1.0.1.0 で @sh2ndさんのエントリーの復習から :)

※セッション1
SESSION1> select * from q order by id;

ID DATA
---------- ----------
1 a
2 b
3 c

※セッション1
SESSION1> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;

ID DATA
---------- ----------
1 a

※セッション2
SESSION2> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;
(ここで待たされる。待つことは正しい動き)

※セッション1
SESSION1> DELETE FROM q WHERE id = 1;

1行が削除されました。

※セッション1
SESSION1> commit;

コミットが完了しました。

SESSION1>

※セッション2はここでロックを獲得できる。
ID DATA
---------- ----------
2 b

SESSION2>


Oracle11g R2 11.2.0.1.0とOracle11g R2 11.2.0.2.0は、Wrong Resultだったんだと思うが...

※セッション1
SESSION1> select * from q order by id;

ID DATA
---------- ----------
1 a
2 b
3 c

※セッション1
SESSION1> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;

ID DATA
---------- ----------
1 a

※セッション2
SESSION2> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;
(ここで待たされる。というところまでは同じ)


※セッション1
SESSION1> DELETE FROM q WHERE id = 1;

1行が削除されました。

※セッション1
SESSION1> COMMIT;

コミットが完了しました。

SESSION1>

※セッション2 (注1
(しか〜〜〜し、なんとレコードが選択されませんでした!!!!!)
レコードが選択されませんでした。

SESSION2>

結果
OracleREAD COMMITTED
11.1.0.7.0ID=2を取得
11.2.0.1.0空振り
11.2.0.2.0空振り
12.1.0.1.0ID=2を取得


注1)
Oracle11g 11.2.0.3.0以降では修正されているとのこと...(私は手持ちのが無かったので未確認...だれか書いてw)

SELECT ~ FOR UPDATE SKIP LOCKEDへつづく...

| | コメント (0) | トラックバック (0)

2013年12月 9日 (月)

no ocijdbc11 in java.library.path on OS X Mavericks

JPOUG Advent Calendar 2013、9日目のエントリー、かつ、チューニングネタではなく Java on OS X の話という変化球 :) です。

OS X版Oracle SQL DeveloperやOracle JDeveloperで、OCI/Thick JDBCを利用しようとして以下のようなメッセージに遭遇したら...みなさんどうしてるのだろう? 

no ocijdbc11 in java.library.path

と考えだしたら眠れなくなったので、役に立つのか、たたないのかわからないけど書いておきます

私は、Oracle SQL DeveloperやOracle JDeveloperの起動シェルでDYLD_LIBRARY_PATHなどの環境変数をセットすることで対処しています。
(今となってはこれが楽だと思います。 environment.plistでなんとかできた時代もありましたけど...いまは使えないしね)

no ocijdbc11 in java.library.pathとなっている状態から解決するまでの操作をYouTubeで....


映像では、ユーザーのホームディレクトリーにOracle向け環境変数設定ファイル (この例では、oracleenvというファイルを作成してあります) を作成しておき、その設定ファイルをOracle SQL Developer等の起動シェルで読み込ませて問題を解決するまでの操作を行っています。

主演
MacBook Air (mid2013)

ホストOS、その他

  • OS X 10.9 Maveriks
  • Oracle Instant Client 11g 11.2.0.3.0 for OS X (64bit)
  • Terminal 2.4
  • VirtualBox 4.3.4 for OS X
  • Oracle SQL Developer 4.0.0 for OS X
  • Oracle JDeveloper12c 12.1.2.0.0 Studio Edition Generic

ゲストOS、その他

  • Oracle Linux Server 6.4 x86_64
  • Oracle Database 12c EE R1 for Linux x86_64

映像では見づらい方向けの解説。

事前にOracle向け設定ファイルを該当ユーザーのホームディレクトリーに作成しておきます。
この例では、oracleenvとして作成しました。(不過視ファイルとしてもよいかもしれません。)
Oracle_environment_variables


Oracle SQL Developer 4.0 for OS X

「Oracle SQL Developerメニュー」→「Preference...」を選択
002_sdev_004

「データベース」→「拡張」→「OCI/Thickドライバの使用」チェックボックス」をチェック→「テスト」ボタンをクリック
002_sdev_005

no ocijdbc11 in java.library.pathエラーとなる(ライブラリーへのパスが通ってないので当然ですよね)
002_sdev_006

「Finder」→「アプリケーション」→「SQL Developer」→「右クリック」→ポップアップメニューの「パッケージの内容表示」
002_sdev_009

「Contents」→「MacOS」→「sqldeveloper.sh」を選択
002_sdev_010

ポップアップメニュー「このアプリケーションで開く」でお好きなテキストエティタを選択
002_sdev_012

oracle向け環境設定ファイルを読み込ませるように編集。この例ではユーザーホームディレクトリにある oracleenvというファイルを読み込むように変更。
002_sdev_013

Oracle SQL Developer 4.0を再起動しOCI/Thick JDBCドライバーで接続可能か再確認
002_sdev_014

002_sdev_015


Oracle JDeveloper12c 12.1.2.0.0 Studio Edition Generic

「データベース接続編集」ダイアログの「接続のテスト」ボタンをクリックするとno ocijdbc11 in java.library.pathエラー(これもパスが通ってないのが原因なので...)
003_jdev_002

003_jdev_003

「Finder」でOracle JDeveloper12cのインストールディレクトリーからアプリケーション「JDeveloper」を右クリック
→ポップアップメニューの「パッケージの内容を表示」を選択

003_jdev_004

「Contents」→「MacOS」→「JDeveloper」を選択して右クリック→「このアプリケーションを開く」でお好きなテキストエディタを選択
003_jdev_005

oracle向け環境設定ファイルを読み込ませるように編集。この例ではユーザーホームディレクトリにある oracleenvというファイルを読み込むように変更。
003_jdev_006

Oracle JDeveloper12cを再起動し、OCI/Thick JDBCドライバーで接続可能か再確認
003_jdev_007

003_jdev_008


明日は、@dekasasaki さんの担当です。引き続き、JPOUG Advent Calendar 2013をお楽しみください。:)


| | コメント (0) | トラックバック (0)

2013年11月24日 (日)

今年もやりますよ〜、JPOUG Advent Calendar 2013

今年もやりますよ〜 JPOUG Advent Calendar 2013

20131124_230725

Oracleに絡んでいればどんなネタでもOK。

例えば、こんなASCII ARTをOracleを絡めてやってみるとか.... 役に立つ立たない、そんなこ事、気にしな〜〜いw

なんてもいいんですw

お気軽にご参加ください〜。 :)

| | コメント (0) | トラックバック (0)

2013年11月17日 (日)

db tech showcase tokyo 2013 - A35 - JPOUG特濃:潮溜まりでジャブジャブ、SQLチューニング

11/13〜15に開催されたdb tech showcase tokyo 2013 の最終日、午後の4枠で特濃JPOUGとてセッションを行いました。

貴重な機会を提供いただいたインサイトテクノロジーの皆様ありがとうございました。
また、お忙しい中、セッションに参加してくださった皆様、ありがとうございました。

A35
15:00-15:45 / 「JPOUG特濃:潮溜まりでジャブジャブ、SQLチューニング」 のセッション資料を公開しました。

塩分濃いめの潮溜まりで釣り上げたSQLは治療できるかどうかもわからない病になっていました….
治療できたか、できなかったのか……

曲者すぎる難病もありますが、何かの機会に思い出していただければと思います。

みなさん、楽しい時間をありがとう。

| | コメント (0) | トラックバック (0)

2013年10月30日 (水)

潮溜まりでジャブジャブします :)

db tech showcase 東京 2013


潮溜まりで泳いでいる一癖ありそうなSQL文が見えませんか?

Rockpool
Rock Pool : Michael Coghlan (CC BY-NC-SA 2.0)

| | コメント (0) | トラックバック (0)

2013年5月15日 (水)

db tech showcase 大阪 2013 へ“技術者魂”を届けます。

インサイトテクノロジーさん主催

db tech showcase 大阪 2013 へ“技術者魂”を届けます。


当日、私は、大阪へ”酒”飲み(だけ)に行けるでしょうかw (謎

| | コメント (0) | トラックバック (0)

2012年12月25日 (火)

Indexがあれば、いいってもんじゃない。中身がたいせつ

吉田さんが、なぜ24日を選んだのか理由がわかった...


さて、JPOUG Advent Calendar、25番目の扉を開けます。

ネタは、そう、やはり、索引です。:)


以下のようなバッチ処理があったと思ってくださいな。

リリース直後、データが少ない状態で統計情報をロックしたまま数年が経過したバッチ処理、リリース当初は特に目立った遅延問題も発生せず安定していた(らしい)。

ところが、ところがです。
最近は、少しでも処理量が増加しようものなら100%遅延するという状況となっていた。

関係者へのヒアリングから、どうやら、グルグル系のバッチであることが見えてきました。(またか!w)

AWRレポートから問題となるSQL文(グルグル回るところが一番怪しいんですけどね、大抵は)を特定しました。(以下参照)

Execution Plan
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 843 (100)| |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| HOGE | 1 | 19 | 843 (1)| 00:00:11 |
| 3 | INDEX RANGE SCAN | HOGE_I01 | 1 | | 842 (1)| 00:00:11 |
-----------------------------------------------------------------------------------------

Full SQL Text

SQL ID SQL Text
------------ -----------------------------------------------------------------
43a7z10a4ckx SELECT COUNT(1) FROM HOGE WHERE
CATEGORY = :B4 AND CLASS = :B3 AND SUBCLASS = :B2 AND ID = :B5
AND SUBID = :B1 AND IS_DELETED = 0

実行計画だけを見ていると、Index Range Scan?、なにが問題なの?(キリっ という感じがしますが、
それに騙されてはいけいけないんです。

次のセクションを見てください。
(本来10万回実行されるのですが終わらないので途中でキャンセルしましたw、約1時間で5000回も回ってません。)

Plan 1(PHV: 468154321)
----------------------

Plan Statistics DB/Inst: DISCUS/discus Snaps: 129-130
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100

Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 3,185,031 640.9 99.8
CPU Time (ms) 3,092,467 622.2 99.8
Executions 4,970 N/A N/A
Buffer Gets 7.7276E+07 15,548.5 99.9
Disk Reads 13,999 2.8 93.4
Parse Calls 1 0.0 0.0
Rows 4,969 1.0 N/A
User I/O Wait Time (ms) 92,540 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version Count 1 N/A N/A
Sharable Mem(KB) 22 N/A N/A
-------------------------------------------------------------

Index Range Scanで平均Rows=1の割にBuffer Getsが異常に多い、処理時間のほとんどがCPU処理時間。

これらがなにを意味するのか............

確認のためexplain planで取得した実行計画のPredicate Informationを見てみると..

Plan hash value: 468154321

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 795 (1)| 00:00:10 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| HOGE | 1 | 19 | 795 (1)| 00:00:10 |
|* 3 | INDEX RANGE SCAN | HOGE_I01 | 1 | | 794 (1)| 00:00:10 |
-----------------------------------------------------------------------------------------

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

2 - filter("IS_DELETED"=0)
3 - access("CATEGORY"=TO_NUMBER(:V1) AND "CLASS"=TO_NUMBER(:V2) AND
"SUBCLASS"=TO_NUMBER(:V3) AND "ID"=TO_NUMBER(:V4) AND "SUBID"=TO_NUMBER(:V5))
filter("ID"=TO_NUMBER(:V4) AND "SUBID"=TO_NUMBER(:V5))

索引アクセス時にID列とSUBID列でフィルタ処理が行われています。:) この部分の比率が非常に大きい。
ようするに大量のデータを読み込み、フィルタ処理でそのほとんどを捨てている!!! 
(もったいない、もったいないです。もったいないお化けが出てきそうです)

索引を見てみます。

INDEX_NAME                     COLUMN_NAME
------------------------------ --------------------
HOGE_I01 CATEGORY
HOGE_I01 CLASS
HOGE_I01 SUBCLASS
HOGE_I01 STATUS
HOGE_I01 ID
HOGE_I01 SUBID

索引の先頭3列(CATEGORY,CLASS,SUBCLASS)でデータ数をカウントしてみると、400,000行ありました...
40万行から、たった1行を取り出すフィルタリングを10万回繰り返している...グルグル系恐るべし(@@)


解決方法は....

索引の列順を入れ替えれば解決するはず!!!
status列はカーディナリティが非常に低い列であることは確認ずみであつたため最後尾に移動しました。
先頭5列(CATEGORY,CLASS,SUBCLASS,ID,SUBID)でほぼデータが絞りこめるようになるはずです。
(AWRレポートのRowsを見ると最終的には1行or0行となることが確認できます)

作り直した索引は以下のようになります。
STATUS列が最後になっているところがポイントです。違いはたったそれだけです。
(Index Only Accessではありませんよ、今回は。Index Only Accessでグルグルをギリギリまでチューニングしなくても十分な効果が得られたのでこれ以上なにもしません。)

INDEX_NAME                     COLUMN_NAME
------------------------------ --------------------
HOGE_I02 CATEGORY
HOGE_I02 CLASS
HOGE_I02 SUBCLASS
HOGE_I02 ID
HOGE_I02 SUBID
HOGE_I02 STATUS


索引を作り直した後はご覧の通り、じゃじゃ〜〜〜〜〜〜ん!
想定17時間程度のダメバッチが30分ほどで終了しました。ニッコリ。CPUにも随分優しくなってますね。

Plan 1(PHV: 2833120447)
-----------------------

Plan Statistics DB/Inst: DISCUS/discus Snaps: 131-132
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100

Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 1,846,702 18.5 99.2
CPU Time (ms) 43,208 0.4 89.1
Executions 100,000 N/A N/A
Buffer Gets 3,971,771 39.7 99.1
Disk Reads 290,822 2.9 99.1
Parse Calls 1 0.0 0.0
Rows 100,000 1.0 N/A
User I/O Wait Time (ms) 1,830,618 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version Count 1 N/A N/A
Sharable Mem(KB) 22 N/A N/A
-------------------------------------------------------------

Execution Plan
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| HOGE | 1 | 19 | 5 (0)| 00:00:01 |
| 3 | INDEX RANGE SCAN | HOGE_I02 | 1 | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Full SQL Text

SQL ID SQL Text
------------ -----------------------------------------------------------------
43a7z10a4ckx SELECT COUNT(1) FROM HOGE WHERE CATEGORY = :B4 AND CLASS = :B3 AN
D SUBCLASS = :B2 AND ID = :B5 AND SUBID = :B1 AND IS_DELETED = 0


念のためexplain plan でも確認してみます。ご覧の通りフィルタリングはis_deleted列のみでindex range scan時のフィルタリングは全く発生していません。

もったいないお化けを退治できましたv

Plan hash value: 2833120447

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 19 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 19 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| HOGE | 1 | 19 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | HOGE_I02 | 1 | | 4 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

2 - filter("IS_DELETED"=0)
3 - access("CATEGORY"=TO_NUMBER(:B4) AND "CLASS"=TO_NUMBER(:B3) AND
"SUBCLASS"=TO_NUMBER(:B2) AND "ID"=TO_NUMBER(:B5) AND "SUBID"=TO_NUMBER(:B1))

複合索引の列順って、ほんとうに大切ですよね :)

NOTE:
今回のような遅延ケースでは索引を適切に再構成するほかに、ヒントでIndex skip scanを強制する方法もありますが、索引を適切な列の並びに再構成する方法と比べ劣るのも事実です。
大人の事情に金縛りに合うこともあるでしょうし.....。状況に合わせて使い分けるとよいと思います。

これで、JPOUG Advent Calendarの全ての小窓は開かれました。

メリークリスマス、そして、良いお年をお迎えください。


最後に、Pipelined Table Function で Christmas Tree ☆☆☆☆

 

| | コメント (0) | トラックバック (0)

2012年10月21日 (日)

Unconference at db tech showcase 2012の資料公開 :)

db tech showcase 2012
Unconference at db tech showcase 2012

db tech showcaseの一角をJPOUGが占拠してUnconferenceを開催しました。 db tech showcase関係者の皆様、このような機会を与えて頂き大変感謝します。
そして、お疲れさまでした。



Index Only Access 3部作の最終回?! として 「Index Only Accessが実装されるたった一つの理由」と題したセッションを行いました。
実行計画を取得するために操作したデータベースの中には人生二度目のデータベース複数もあり、かなりの時間を裂いて調べた割にはセッション時間が少々短めになってしまいました。m(_ _)m

なぜ、このテーマを選んだか.

PostgreSQLがリリースされてから9.1まで実装されなかったIndex Only Accessでしたが、9.2でついに実装されました。

そして..db tech showcase 2012は...

SQL> select dbms_name from all_dbms where dbms_category like "%";

DBMS_NAME
------------------
Oracle
DB2
MySQL
PostgreSQL
SQL Server
Vecterwise
MongoDB
Symfoware
Clustrix
InfiniDB
.
.
.
.

的な雰囲気となっていることもあり、Index Only Accessの魚拓をあつめて比較、Index Only Accessが実装される理由について今一度、考えてみたいな..と。
タイトル見ただけで理由が想像できた方は、資料見なくても大丈夫だと思いますよ。:) 
 

H/Wの性能が急速に伸びてきている影響もあるように感じますが、無駄に広範囲な検索や、無駄にビッグなデータとなっていること気にしていないのではないか? というケースが多くなっていると感じています。
DBMSはアクセスするデータをより少なくするための工夫をしているのに...エンジニアがそれをうまく使っていない、使えていない、設計できてない...そんな"感じ"がするんです。

セッション資料を公開しました。
S1a


じゃ、like "%てない" 状況をどうすればいいか....答えは、小田さんのセッションの中にあった。。。。:)


#不慣れなDBMSもあり、こんなメトリックみたほうが分かりやすいよ〜、などのツッコミ歓迎します.


| | コメント (0) | トラックバック (0)

2012年9月24日 (月)

Unconferenceあります。

JPOUGが、再び、Unconference をやっちゃいます!  (タイムスケジュールなどはJPOUG(Japa Oracle User Group)のサイトをご覧ください

Unconference_dbts2012


今回はインサイトテクノロジー社主催のdb tech showcase 2012で...

Dts_2012e1346810339148300x54



db tech showcase2012 ハッシュタグ : #dbts2012
JPOUG(Japan Oracle User Group) ハッシュタグ : #jpoug

| | コメント (0) | トラックバック (0)

2012年8月21日 (火)

Index Only Accessネタのおまけ

2012/10/13追記
MySQL(InnoDB)の主キーはClustered Indexなので主キーアクセスである場合はCovering indexは不要ですね。(^^;; 
PostgreSQL9.2のIndex-only scanですが、vacuumさえしっかりやっていればcovering indexを利用するようになることを確認。

前述の2点を後日追加予定です。



随分間があいてしまいました、m(_ _)m

Oracle以外でも多数の商用/OSS RDBMSでIndex Only Accessできるんですよね。
ということで、MySQL/PostgreSQLの実行計画ではどのように表示されるのか、Oracleのように索引だけ作れば勝ってにIndex Only Accessやってくれるのか?、などなど簡単に確かめてみました。(備忘録)

OracleのIndex Only Accessは以下のエントリを参考にしてください。
いん!、イン!、Index どっぷり Inde Only Access生活w - Oracle OpenWorld Unconference presented by JPOUG
JPOUG SET EVENTS 20120721 - 「(続)いん!、イン!、Index 大人の事情縛りのSQLチューニング」資料公開

今回使ったのは、MySQL 5.5.27/PostgreSQL 9.1.4そしてBeta阪ですが9.2 Beta4です。
環境はOracleが乗ってる環境と同じです。

MacBook Air late 2010 13inch 2GB (MacOS X Lion)
VirtualBox4.1.18 for MacOS X
GuestOS:CentOS5.8 x86

ちなみに、データ作りに時間を割けなかったので、クラスタリングファクターはどちらのも低めとなっています。実行計画上どのように見えるのか知りたかっただけなので. (^^;;;

MySQL 5.5.27

Oracleのデモでも使っていたスカラー副問合せで試してみました。最初は、Index Rancge Scanをグルグル。
この場合は索引、表をそれぞれアクセスします。

mysql> explain 
-> select
-> t1.unique_id,
-> t1.item_code,
-> (
-> select
-> max(t3.unique_id)
-> from
-> tab31 t2 join tab311 t3
-> on
-> t2.sub_item_code = t3.sub_item_code
-> and t3.is_delete = 0
-> where
-> t2.item_code = t1.item_code
-> and t2.is_delete = 0
-> ) current_sub_item
-> from
-> tab3 t1
-> where
-> t1.unique_id between 1 and 10000
-> and t1.is_delete = 0
-> and t1.status_code = '00'
-> ;
+----+--------------------+-------+--------+---------------+-----------+---------+------------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+--------+---------------+-----------+---------+------------------------+-------+-------------+
| 1 | PRIMARY | t1 | range | PRIMARY | PRIMARY | 5 | NULL | 20108 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | eq_ref | PRIMARY | PRIMARY | 45 | scott.t1.item_code | 1 | Using where |
| 2 | DEPENDENT SUBQUERY | t3 | ref | tab311_ix | tab311_ix | 31 | scott.t2.sub_item_code | 1 | Using where |
+----+--------------------+-------+--------+---------------+-----------+---------+------------------------+-------+-------------+
3 rows in set (0.00 sec)

mysql>


Index Only Accessさせた場合の実行計画です。
Extra列に Using indexと出ていればIndex Only Accessになっています。
ちなみに、Oracleとちがって勝ってにCovering Indexを使ってくれなかったのでSQLヒントを使っています。Oracleのヒントと随分書き方違うので戸惑うよ(^^;;;

mysql> explain
-> select
-> t1.unique_id,
-> t1.item_code,
-> (
-> select
-> max(t3.unique_id)
-> from
-> tab31 t2 ignore index(primary) join tab311 t3 ignore index(tab311_ix)
-> on
-> t2.sub_item_code = t3.sub_item_code
-> and t3.is_delete = 0
-> where
-> t2.item_code = t1.item_code
-> and t2.is_delete = 0
-> ) current_sub_item
-> from
-> tab3 t1
-> where
-> t1.unique_id between 1 and 10000
-> and t1.is_delete = 0
-> and t1.status_code = '00'
-> ;
+----+--------------------+-------+-------+----------------+----------------+---------+------------------------------+-------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-------+----------------+----------------+---------+------------------------------+-------+--------------------------+
| 1 | PRIMARY | t1 | range | PRIMARY | PRIMARY | 5 | NULL | 20108 | Using where |
| 2 | DEPENDENT SUBQUERY | t2 | ref | tab31_demo_ix | tab31_demo_ix | 47 | scott.t1.item_code,const | 10000 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t3 | ref | tab311_demo_ix | tab311_demo_ix | 33 | scott.t2.sub_item_code,const | 10000 | Using where; Using index |
+----+--------------------+-------+-------+----------------+----------------+---------+------------------------------+-------+--------------------------+
3 rows in set (0.00 sec)

mysql>


PostgreSQL 9.1

PostgreSQL9.1まではIndex Only Accessが実装されていないとのこと。(実は今年になってはじめて知ったことなのですが、その時は、「え?! そうなの?」って感じでした)

※実行時間のバラツキがあるため実行統計情報から表や索引ブロックアクセス状況を確認しています。

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
16401 | public | tab311 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
16395 | public | tab31 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit
-------+------------+------------+---------+----------------+---------------+--------------
16401 | 16411 | public | tab311 | tab311_pk | 0 | 0
16395 | 16454 | public | tab31 | tab31_demo_ix | 0 | 0
16401 | 16455 | public | tab311 | tab311_demo_ix | 0 | 0

scott=> explain analyze verbose
scott-> select
scott-> t1.unique_id,
scott-> t1.item_code,
scott-> (
scott(> select
scott(> max(t3.unique_id)
scott(> from
scott(> tab31 t2 join tab311 t3
scott(> on
scott(> t2.sub_item_code = t3.sub_item_code
scott(> and t3.is_delete = 0
scott(> where
scott(> t2.item_code = t1.item_code
scott(> and t2.is_delete = 0
scott(> ) current_sub_item
scott-> from
scott-> tab3 t1
scott-> where
scott-> t1.unique_id between 1 and 10000
scott-> and t1.is_delete = 0
scott-> and t1.status_code = '00'
scott-> ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tab3_pk on public.tab3 t1 (cost=0.00..2050286.39 rows=10326 width=22) (actual time=45.613..6560.751 rows=10000 loops=1)
Output: t1.unique_id, t1.item_code, (SubPlan 1)
Index Cond: ((t1.unique_id >= 1::numeric) AND (t1.unique_id <= 10000::numeric))
Filter: ((t1.is_delete = 0::numeric) AND (t1.status_code = '00'::bpchar))
SubPlan 1
-> Aggregate (cost=198.47..198.48 rows=1 width=6) (actual time=0.650..0.651 rows=1 loops=10000)
Output: max(t3.unique_id)
-> Nested Loop (cost=0.00..198.46 rows=4 width=6) (actual time=0.143..0.621 rows=40 loops=10000)
Output: t3.unique_id
-> Index Scan using tab31_pk on public.tab31 t2 (cost=0.00..8.58 rows=1 width=11) (actual time=0.010..0.011 rows=1 loops=10000)
Output: t2.item_code, t2.sub_item_code, t2.data, t2.is_delete
Index Cond: (t2.item_code = t1.item_code)
Filter: (t2.is_delete = 0::numeric)
-> Index Scan using tab311_ix on public.tab311 t3 (cost=0.00..189.27 rows=49 width=17) (actual time=0.130..0.565 rows=40 loops=10000)
Output: t3.unique_id, t3.sub_item_code, t3.data, t3.is_delete
Index Cond: (t3.sub_item_code = t2.sub_item_code)
Filter: (t3.is_delete = 0::numeric)
Total runtime: 6566.636 ms
(18 行)

時間: 6618.795 ms
scott=>

適切な索引を利用しているのでIndex Unique/Range Scanとテーブルブロックへのアクセスが確認できます。(当然といえば当然ですよね)

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
16401 | public | tab311 | 15389 | 36920 | 1983 | 30033 | 0 | 0 | 0 | 0
16395 | public | tab31 | 401 | 9599 | 64 | 30057 | 0 | 0 | 0 | 0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit
-------+------------+------------+---------+----------------+---------------+--------------
16401 | 16411 | public | tab311 | tab311_pk | 0 | 0
16395 | 16454 | public | tab31 | tab31_demo_ix | 64 | 30057
16401 | 16455 | public | tab311 | tab311_demo_ix | 1983 | 30033

Index Only AccessさせるためのCovering Index(FAT index)を作ってみましたが、Index Range/Unique Scanのままですね。
ちなみに、PostgreSQLってOracleやMySQLのようなSQLヒントがありません。(あったらごめんなさい。調べきれてないだけです。m(_ _)m
Covering Indexだけを残して邪魔な索引を削除することでCovering indexをアクセスさせています。

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
16401 | public | tab311 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0
16395 | public | tab31 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit
-------+------------+------------+---------+----------------+---------------+--------------
16401 | 16411 | public | tab311 | tab311_pk | 0 | 0
16395 | 16454 | public | tab31 | tab31_demo_ix | 0 | 0
16401 | 16455 | public | tab311 | tab311_demo_ix | 0 | 0


scott=> explain analyze verbose
scott-> select
scott-> t1.unique_id,
scott-> t1.item_code,
scott-> (
scott(> select
scott(> max(t3.unique_id)
scott(> from
scott(> tab31 t2 join tab311 t3
scott(> on
scott(> t2.sub_item_code = t3.sub_item_code
scott(> and t3.is_delete = 0
scott(> where
scott(> t2.item_code = t1.item_code
scott(> and t2.is_delete = 0
scott(> ) current_sub_item
scott-> from
scott-> tab3 t1
scott-> where
scott-> t1.unique_id between 1 and 10000
scott-> and t1.is_delete = 0
scott-> and t1.status_code = '00'
scott-> ;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tab3_pk on public.tab3 t1 (cost=0.00..2109597.62 rows=10326 width=22) (actual time=0.677..1808.056 rows=10000 loops=1)
Output: t1.unique_id, t1.item_code, (SubPlan 1)
Index Cond: ((t1.unique_id >= 1::numeric) AND (t1.unique_id <= 10000::numeric))
Filter: ((t1.is_delete = 0::numeric) AND (t1.status_code = '00'::bpchar))
SubPlan 1
-> Aggregate (cost=204.21..204.22 rows=1 width=6) (actual time=0.176..0.176 rows=1 loops=10000)
Output: max(t3.unique_id)
-> Nested Loop (cost=0.00..204.20 rows=4 width=6) (actual time=0.024..0.137 rows=40 loops=10000)
Output: t3.unique_id
-> Index Scan using tab31_demo_ix on public.tab31 t2 (cost=0.00..8.76 rows=1 width=11) (actual time=0.007..0.009 rows=1 loops=10000)
Output: t2.item_code, t2.sub_item_code, t2.data, t2.is_delete
Index Cond: ((t2.item_code = t1.item_code) AND (t2.is_delete = 0::numeric))
-> Index Scan using tab311_demo_ix on public.tab311 t3 (cost=0.00..194.84 rows=49 width=17) (actual time=0.013..0.068 rows=40 loops=10000)
Output: t3.unique_id, t3.sub_item_code, t3.data, t3.is_delete
Index Cond: ((t3.sub_item_code = t2.sub_item_code) AND (t3.is_delete = 0::numeric))
Total runtime: 4762.819 ms
(16 行)

時間: 4855.100 ms
scott=>

実行計画上、Filterはなくなりましたが、表ブロックもアクセスしているのでIndex Only Accessにはなっていません。(ほんとにIndex Only AccessというかIndex-only Scanは9.1までは実装されていないんですね。)

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
16401 | public | tab311 | 15390 | 36919 | 1982 | 30034 | 0 | 0 | 0 | 0
16395 | public | tab31 | 401 | 9599 | 62 | 30059 | 0 | 0 | 0 | 0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit
-------+------------+------------+---------+----------------+---------------+--------------
16401 | 16411 | public | tab311 | tab311_pk | 0 | 0
16395 | 16454 | public | tab31 | tab31_demo_ix | 62 | 30059
16401 | 16455 | public | tab311 | tab311_demo_ix | 1982 | 30034

PostgreSQL 9.2 Beta4

最後に、PostgreSQL 9.2 Beta4 です。このリリースではPostgreSQLでは初めて、Index Only Access(マニュアルでは Index-only Scanと記載されています)
PostgreSQL方面の方がIndex-only Scanと書くかたが多いのもこの影響でしょうね。日本人からするとIndex Only AccessよりIndex Only Scanの方が発音しやすい?(私だけか?)気がしますw

scott=> explain analyze verbose
scott-> select
scott-> t1.unique_id,
scott-> t1.item_code,
scott-> (
scott(> select
scott(> max(t3.unique_id)
scott(> from
scott(> tab31 t2 join tab311 t3
scott(> on
scott(> t2.sub_item_code = t3.sub_item_code
scott(> and t3.is_delete = 0
scott(> where
scott(> t2.item_code = t1.item_code
scott(> and t2.is_delete = 0
scott(> ) current_sub_item
scott-> from
scott-> tab3 t1
scott-> where
scott-> t1.unique_id between 1 and 10000
scott-> and t1.is_delete = 0
scott-> and t1.status_code = '00'
scott-> ;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tab3_pk on public.tab3 t1 (cost=0.00..1925706.85 rows=9421 width=22) (actual time=41.205..6202.096 rows=10000 loops=1)
Output: t1.unique_id, t1.item_code, (SubPlan 1)
Index Cond: ((t1.unique_id >= 1::numeric) AND (t1.unique_id <= 10000::numeric))
Filter: ((t1.is_delete = 0::numeric) AND (t1.status_code = '00'::bpchar))
SubPlan 1
-> Aggregate (cost=204.32..204.33 rows=1 width=6) (actual time=0.613..0.614 rows=1 loops=10000)
Output: max(t3.unique_id)
-> Nested Loop (cost=0.00..204.31 rows=4 width=6) (actual time=0.110..0.584 rows=40 loops=10000)
Output: t3.unique_id
-> Index Scan using tab31_pk on public.tab31 t2 (cost=0.00..11.35 rows=1 width=11) (actual time=0.012..0.013 rows=1 loops=10000)
Output: t2.item_code, t2.sub_item_code, t2.data, t2.is_delete
Index Cond: (t2.item_code = t1.item_code)
Filter: (t2.is_delete = 0::numeric)
-> Index Scan using tab311_ix on public.tab311 t3 (cost=0.00..192.47 rows=49 width=17) (actual time=0.095..0.525 rows=40 loops=10000)
Output: t3.unique_id, t3.sub_item_code, t3.data, t3.is_delete
Index Cond: (t3.sub_item_code = t2.sub_item_code)
Filter: (t3.is_delete = 0::numeric)
Total runtime: 6207.935 ms
(18 行)

時間: 6313.789 ms
scott=>

9.2でもIndex Only Accessでなければ表ブロックもアクセスしますよね〜。そりゃそうだ。:)

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
16404 | public | tab311 | 15390 | 36919 | 1649 | 29996 | 0 | 0 | 0 | 0
16398 | public | tab31 | 401 | 9599 | 39 | 30037 | 0 | 0 | 0 | 0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit
-------+------------+------------+---------+--------------+---------------+--------------
16404 | 16414 | public | tab311 | tab311_pk | 0 | 0
16398 | 16432 | public | tab31 | tab31_pk | 39 | 30037
16404 | 16434 | public | tab311 | tab311_ix | 1649 | 29996

いよいよ、PostgreSQL9.2 Beta4のIndex-only Scanの番です。:)
お〜〜〜っ、 Index Scan using xxxxという部分が、Index Only Scan using xxxxとなっています! が、 Heap Fetches 400000とある? どゆこと?

scott=> explain analyze verbose
scott-> select
scott-> t1.unique_id,
scott-> t1.item_code,
scott-> (
scott(> select
scott(> max(t3.unique_id)
scott(> from
scott(> tab31 t2 join tab311 t3
scott(> on
scott(> t2.sub_item_code = t3.sub_item_code
scott(> and t3.is_delete = 0
scott(> where
scott(> t2.item_code = t1.item_code
scott(> and t2.is_delete = 0
scott(> ) current_sub_item
scott-> from
scott-> tab3 t1
scott-> where
scott-> t1.unique_id between 1 and 10000
scott-> and t1.is_delete = 0
scott-> and t1.status_code = '00'
scott-> ;

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tab3_pk on public.tab3 t1 (cost=0.00..2000896.05 rows=9421 width=22) (actual time=0.410..1728.699 rows=10000 loops=1)
Output: t1.unique_id, t1.item_code, (SubPlan 1)
Index Cond: ((t1.unique_id >= 1::numeric) AND (t1.unique_id <= 10000::numeric))
Filter: ((t1.is_delete = 0::numeric) AND (t1.status_code = '00'::bpchar))
SubPlan 1
-> Aggregate (cost=212.30..212.31 rows=1 width=6) (actual time=0.168..0.168 rows=1 loops=10000)
Output: max(t3.unique_id)
-> Nested Loop (cost=0.00..212.29 rows=4 width=6) (actual time=0.023..0.131 rows=40 loops=10000)
Output: t3.unique_id
-> Index Only Scan using tab31_demo_ix on public.tab31 t2 (cost=0.00..13.13 rows=1 width=11) (actual time=0.006..0.007 rows=1 loops=10000)
Output: t2.item_code, t2.is_delete, t2.sub_item_code
Index Cond: ((t2.item_code = t1.item_code) AND (t2.is_delete = 0::numeric))
Heap Fetches: 10000
-> Index Only Scan using tab311_demo_ix on public.tab311 t3 (cost=0.00..198.67 rows=49 width=17) (actual time=0.013..0.066 rows=40 loops=10000)
Output: t3.sub_item_code, t3.is_delete, t3.unique_id
Index Cond: ((t3.sub_item_code = t2.sub_item_code) AND (t3.is_delete = 0::numeric))
Heap Fetches: 400000
Total runtime: 5412.612 ms
(18 行)

時間: 5465.400 ms
scott=>

やはり! Heap Fetchesとあるのでおかしいと思っていたら...orz. なんで表ブロックアクセスしてんの〜〜〜っ。実行計画は、Index Only Scan。謎。

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
16404 | public | tab311 | 15389 | 36920 | 1983 | 30033 | 0 | 0 | 0 | 0
16398 | public | tab31 | 401 | 9599 | 64 | 30057 | 0 | 0 | 0 | 0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit
-------+------------+------------+---------+----------------+---------------+--------------
16404 | 16414 | public | tab311 | tab311_pk | 0 | 0
16398 | 16435 | public | tab31 | tab31_demo_ix | 64 | 30057
16404 | 16436 | public | tab311 | tab311_demo_ix | 1983 | 30033


何故、Inde Only Scanなのに表ブロックをアクセスしてしまうのか、わかった〜〜〜っ、と思う。(vacuum analyze が必要らしい。ちなみに前述の結果はデータをINSERTし、analyzeコマンドだけを実施した状態だった)

気持ちを落ち着けて〜〜〜!

[oracle@leaffish ˜]$ psql -U oracle scott
タイミングは on です。
psql (9.2beta4)
"help" でヘルプを表示します.

scott=# vacuum analyze tab311;
scott=# vacuum analyze tab31;


こんどこそ! できた〜〜〜〜っ!と思う。
Heap Fetchesも0だし、表ブロックへのアクセスもほぼない。ほぼない。大切なので2度書きました。表ブロックへのアクセスは0にはならないようです。今のところ。こまけーことはいいんだよ的で、ワイルド。 :) 数ブロックアクセスしているのはどのようなブロックなのでしょうね

scott=> explain analyze verbose
scott-> select
scott-> t1.unique_id,
scott-> t1.item_code,
scott-> (
scott(> select
scott(> max(t3.unique_id)
scott(> from
scott(> tab31 t2 join tab311 t3
scott(> on
scott(> t2.sub_item_code = t3.sub_item_code
scott(> and t3.is_delete = 0
scott(> where
scott(> t2.item_code = t1.item_code
scott(> and t2.is_delete = 0
scott(> ) current_sub_item
scott-> from
scott-> tab3 t1
scott-> where
scott-> t1.unique_id between 1 and 10000
scott-> and t1.is_delete = 0
scott-> and t1.status_code = '00'
scott-> ;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using tab3_pk on public.tab3 t1 (cost=0.00..188801.90 rows=9921 width=22) (actual time=0.255..1116.525 rows=10000 loops=1)
Output: t1.unique_id, t1.item_code, (SubPlan 1)
Index Cond: ((t1.unique_id >= 1::numeric) AND (t1.unique_id <= 10000::numeric))
Filter: ((t1.is_delete = 0::numeric) AND (t1.status_code = '00'::bpchar))
SubPlan 1
-> Aggregate (cost=18.95..18.96 rows=1 width=6) (actual time=0.108..0.109 rows=1 loops=10000)
Output: max(t3.unique_id)
-> Nested Loop (cost=0.00..18.94 rows=4 width=6) (actual time=0.019..0.081 rows=40 loops=10000)
Output: t3.unique_id
-> Index Only Scan using tab31_demo_ix on public.tab31 t2 (cost=0.00..9.13 rows=1 width=11) (actual time=0.005..0.005 rows=1 loops=10000)
Output: t2.item_code, t2.is_delete, t2.sub_item_code
Index Cond: ((t2.item_code = t1.item_code) AND (t2.is_delete = 0::numeric))
Heap Fetches: 0
-> Index Only Scan using tab311_demo_ix on public.tab311 t3 (cost=0.00..9.32 rows=49 width=17) (actual time=0.012..0.036 rows=40 loops=10000)
Output: t3.sub_item_code, t3.is_delete, t3.unique_id
Index Cond: ((t3.sub_item_code = t2.sub_item_code) AND (t3.is_delete = 0::numeric))
Heap Fetches: 0
Total runtime: 1121.140 ms
(18 行)

時間: 1123.194 ms

scott=> select * from pg_statio_user_tables where relname in ('tab31','tab311');
relid | schemaname | relname | heap_blks_read | heap_blks_hit | idx_blks_read | idx_blks_hit | toast_blks_read | toast_blks_hit | tidx_blks_read | tidx_blks_hit
-------+------------+---------+----------------+---------------+---------------+--------------+-----------------+----------------+----------------+---------------
16404 | public | tab311 | 0 | 3 | 0 | 32015 | 0 | 0 | 0 | 0
16398 | public | tab31 | 0 | 1 | 0 | 30120 | 0 | 0 | 0 | 0

scott=> select * from pg_statio_user_indexes where relname in ('tab31','tab311');
relid | indexrelid | schemaname | relname | indexrelname | idx_blks_read | idx_blks_hit
-------+------------+------------+---------+----------------+---------------+--------------
16404 | 16414 | public | tab311 | tab311_pk | 0 | 0
16398 | 16421 | public | tab31 | tab31_demo_ix | 0 | 30120
16404 | 16422 | public | tab311 | tab311_demo_ix | 0 | 32015




参考:
INDEX FULL SCANを狙う - MySQL Casual Advent Calendar 2011
Covering Index と self-join と MySQL
How are index-only scans implemented in InnoDB
PostgreSQL 9.2 highlight: Index-only scans
PostgreSQLアーキテクチャ入門(INSIGHT OUT 2011)

| | コメント (0) | トラックバック (0)

2012年7月22日 (日)

JPOUG SET EVENTS 20120721 - 「(続)いん!、イン!、Index 大人の事情縛りのSQLチューニング」資料公開

当日は、予想を大きく上回るご参加、ありがとうございました。m(_ _)m  エンジニアの笑顔っていいですよね。

※数日前まで風邪で体調を崩していたこもあり名刺切れしていた申し訳ありませんでした。(名刺印刷をアウトソースしないこだわり名刺というのもその理由なのですが。)

JPOUG> SET EVENTS 20120721 @ 日本オラクル青山センター
でのセッション「(続)いん!、イン!、Index 大人の事情縛りのSQLチューニング」の資料を公開します。

Safari以外のブラウザではアニメーション効果はありませんが、Safari (Mac/iPad/iPhone)ではKeynote風(但し、ページ間のトランジッションなし)に表示されます。

デモ環境は前回と同じです。
デモ内容は別途追加予定です。2012/7/24:デモを追加しました。)

いん!、イン!、Index どっぷり Inde Only Access生活w - Oracle OpenWorld Unconference presented by JPOUGのセッション資料はこちら

おまけの資料(OracleとNULL)

Oracle8i SQL Reference Release 8.1.5 - Nulls


Oracle® Database SQL言語リファレンス 11gリリース2(11.2) - NULL


20120722_92335


DEMO : 1回で2万行参照するバッチ処理

ほんとのバッチはこんなもんじゃないのは知ってますよねw.

SCOTT> @demo1_2
1 declare
2 type t_unique_id is table of tab1.unique_id%type index by pls_integer;
3 type t_non_unique_id is table of tab1.non_unique_id%type index by pls_integer;
4 unique_ids t_unique_id;
5 non_unique_ids t_non_unique_id;
6 begin
7 select
8 unique_id
9 ,non_unique_id
10 bulk collect into
11 unique_ids
12 ,non_unique_ids
13 from
14 tab1
15 where
16 unique_id between 1 and 20000
17 and is_delete = 0
18 and status_code = '00'
19 ;
20 dbms_output.put_line('rows:'||unique_ids.last);
21* end;
rows:20000

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.75

・・・中略・・・

Plan Statistics DB/Inst: DISCUS/discus Snaps: 208-209
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100

Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 699 699.0 22.4
CPU Time (ms) 310 310.0 16.3
Executions 1 N/A N/A
Buffer Gets 1,648 1,648.0 7.9
Disk Reads 1,584 1,584.0 65.0
Parse Calls 1 1.0 0.1
Rows 20,000 20,000.0 N/A
User I/O Wait Time (ms) 585 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version Count 1 N/A N/A
Sharable Mem(KB) 14 N/A N/A
-------------------------------------------------------------

Execution Plan
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1579 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | 20000 | 273K| 1579 (1)| 00:00:19 |
| 2 | INDEX RANGE SCAN | TAB1_PK | 20000 | | 39 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Full SQL Text

SQL ID SQL Text
------------ -----------------------------------------------------------------
cbmpgdpxr6vy SELECT UNIQUE_ID , NON_UNIQUE_ID FROM TAB1 WHERE UNIQUE_ID BETWEE
EN 1 AND 20000 AND IS_DELETE = 0 AND STATUS_CODE = '00'

Report written to demo1_2_awrsqrpt.txt
SCOTT>


DEMO : 1行の参照を2万回グルグル回すバッチ処理

単純な比較だからね。1回 vs. 2万回グルグル系の。
グルグル回す処理方式もいろいろな大人の事情で利用する必要があるのもこともわかっていますが、基本的に性能はでないので、それを想定した対処は必要ですよね :)

SCOTT> @demo1_3
1 declare
2 type t_unique_id is table of tab1.unique_id%type index by pls_integer;
3 type t_non_unique_id is table of tab1.non_unique_id%type index by pls_integer;
4 unique_ids t_unique_id;
5 non_unique_ids t_non_unique_id;
6 cursor c1(p_unique_id tab1.unique_id%TYPE) is
7 select
8 unique_id
9 ,non_unique_id
10 from
11 tab1
12 where
13 unique_id = p_unique_id
14 and is_delete = 0
15 and status_code = '00'
16 ;
17 begin
18 for i in 1..20000 loop
19 for c1_rec in c1(i) loop
20 unique_ids(i) := c1_rec.unique_id;
21 non_unique_ids(i) := c1_rec.non_unique_id;
22 end loop;
23 end loop;
24 dbms_output.put_line('rows:'||unique_ids.last);
25* end;
rows:20000

PL/SQLプロシージャが正常に完了しました。

・・・中略・・・

Plan Statistics DB/Inst: DISCUS/discus Snaps: 210-211
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100

Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 1,456 0.1 37.4
CPU Time (ms) 1,112 0.1 42.2
Executions 20,000 N/A N/A
Buffer Gets 60,047 3.0 95.3
Disk Reads 1,587 0.1 75.6
Parse Calls 1 0.0 0.7
Rows 20,000 1.0 N/A
User I/O Wait Time (ms) 866 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version Count 1 N/A N/A
Sharable Mem(KB) 18 N/A N/A
-------------------------------------------------------------

Execution Plan
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | 1 | 14 | 2 (0)| 00:00:01 |
| 2 | INDEX UNIQUE SCAN | TAB1_PK | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Full SQL Text

SQL ID SQL Text
------------ -----------------------------------------------------------------
dvpjay9p4csj SELECT UNIQUE_ID , NON_UNIQUE_ID FROM TAB1 WHERE UNIQUE_ID = :B1
AND IS_DELETE = 0 AND STATUS_CODE = '00'


Report written to demo1_3_awrsqrpt.txt
SCOTT>

DEMO : DEMO : 1行の参照を2万回グルグル回すバッチ処理をIndex Only Accessでチューニングしたみたよ:)

アクセスブロック数も減ったし、処理時間も短くなった。 グルグル系のはね、SQL文レベルのチューニング限界でもある。
Index Only Accessでチューニングしても、グルグル回ってる回数は同じだから。

SCOTT> @demo1_3_ix
create index tab1_ix_demo1_3 on tab1(unique_id,is_delete,status_code,non_unique_id) nologging;

索引が作成されました。

SCOTT> @demo1_3
1 declare
2 type t_unique_id is table of tab1.unique_id%type index by pls_integer;
3 type t_non_unique_id is table of tab1.non_unique_id%type index by pls_integer;
4 unique_ids t_unique_id;
5 non_unique_ids t_non_unique_id;
6 cursor c1(p_unique_id tab1.unique_id%TYPE) is
7 select
8 unique_id
9 ,non_unique_id
10 from
11 tab1
12 where
13 unique_id = p_unique_id
14 and is_delete = 0
15 and status_code = '00'
16 ;
17 begin
18 for i in 1..20000 loop
19 for c1_rec in c1(i) loop
20 unique_ids(i) := c1_rec.unique_id;
21 non_unique_ids(i) := c1_rec.non_unique_id;
22 end loop;
23 end loop;
24 dbms_output.put_line('rows:'||unique_ids.last);
25* end;
rows:20000

PL/SQLプロシージャが正常に完了しました。

・・・中略・・・

Plan Statistics DB/Inst: DISCUS/discus Snaps: 212-213
-> % Total DB Time is the Elapsed Time of the SQL statement divided
into the Total Database Time multiplied by 100

Stat Name Statement Per Execution % Snap
---------------------------------------- ---------- -------------- -------
Elapsed Time (ms) 498 0.0 11.1
CPU Time (ms) 453 0.0 13.0
Executions 20,000 N/A N/A
Buffer Gets 40,088 2.0 74.9
Disk Reads 72 0.0 8.1
Parse Calls 1 0.0 0.2
Rows 20,000 1.0 N/A
User I/O Wait Time (ms) 37 N/A N/A
Cluster Wait Time (ms) 0 N/A N/A
Application Wait Time (ms) 0 N/A N/A
Concurrency Wait Time (ms) 0 N/A N/A
Invalidations 0 N/A N/A
Version Count 1 N/A N/A
Sharable Mem(KB) 18 N/A N/A
-------------------------------------------------------------

Execution Plan
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | INDEX RANGE SCAN| TAB1_IX_DEMO1_3 | 1 | 14 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------

Full SQL Text

SQL ID SQL Text
------------ -----------------------------------------------------------------
dvpjay9p4csj SELECT UNIQUE_ID , NON_UNIQUE_ID FROM TAB1 WHERE UNIQUE_ID = :B1
AND IS_DELETE = 0 AND STATUS_CODE = '00'


Report written to demo1_3_awrsqrpt.txt
SCOTT>
SCOTT> @drop_demo1_3_ix
drop index tab1_ix_demo1_3;

索引が削除されました。

DEMO : 暴走するスカラー副問合せ

SELECTリストにあるスカラー副問合せって、クエリ本体でヒットしたデータ件数分グルグル実行されるんだお。

SCOTT> @demo5
alter system flush buffer_cache;

システムが変更されました。

経過: 00:00:00.26
1 select
2 t1.unique_id,
3 t1.item_code,
4 (
5 select
6 max(t3.unique_id)
7 from
8 tab31 t2 join tab311 t3
9 on
10 t3.sub_item_code = t2.sub_item_code
11 and t3.is_delete = 0
12 where
13 t2.item_code = t1.item_code
14 and t2.is_delete = 0
15 ) current_item
16 from
17 tab3 t1
18 where
19 t1.unique_id between 1 and 10000
20 and t1.is_delete = 0
21* and t1.status_code = '00'

10000行が選択されました。

経過: 00:00:17.54

実行計画
----------------------------------------------------------
Plan hash value: 2850625377

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9998 | 253K| 424 (0)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 47 | | |
| 2 | NESTED LOOPS | | 2 | 94 | 8 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| TAB31 | 1 | 29 | 3 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | TAB31_PK | 1 | | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| TAB311 | 2 | 36 | 5 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | TAB311_IX | 2 | | 2 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | TAB3 | 9998 | 253K| 424 (0)| 00:00:06 |
|* 8 | INDEX RANGE SCAN | TAB3_PK | 10000 | | 23 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

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

3 - filter("T2"."IS_DELETE"=0)
4 - access("T2"."ITEM_CODE"=:B1)
5 - filter("T3"."IS_DELETE"=0)
6 - access("T3"."SUB_ITEM_CODE"="T2"."SUB_ITEM_CODE")
7 - filter("T1"."IS_DELETE"=0 AND "T1"."STATUS_CODE"='00')
8 - access("T1"."UNIQUE_ID">=1 AND "T1"."UNIQUE_ID"<=10000)


統計
----------------------------------------------------------
1256 recursive calls
0 db block gets
52747 consistent gets
22557 physical reads
116 redo size
326816 bytes sent via SQL*Net to client
7742 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
35 sorts (memory)
0 sorts (disk)
10000 rows processed

SCOTT>

DEMO : 暴走するスカラー副問合せをIndex Only Accessでチューニング!

アクセスブロック数が明らかに減った :) 索引だけ参照させることでね :)

SCOTT> @demo5_ix
create index tab31_demo_ix on tab31(item_code, is_delete, sub_item_code) nologging;

索引が作成されました。

経過: 00:00:01.18
create index tab311_demo_ix on tab311(sub_item_code, is_delete, unique_id) nologging;

索引が作成されました。

SCOTT> @demo5
alter systen flush buffer_cache;

システムが変更されました。

経過: 00:00:00.18
1 select
2 t1.unique_id,
3 t1.item_code,
4 (
5 select
6 max(t3.unique_id)
7 from
8 tab31 t2 join tab311 t3
9 on
10 t3.sub_item_code = t2.sub_item_code
11 and t3.is_delete = 0
12 where
13 t2.item_code = t1.item_code
14 and t2.is_delete = 0
15 ) current_item
16 from
17 tab3 t1
18 where
19 t1.unique_id between 1 and 10000
20 and t1.is_delete = 0
21* and t1.status_code = '00'

10000行が選択されました。

経過: 00:00:03.81

実行計画
----------------------------------------------------------
Plan hash value: 2957188266

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9998 | 253K| 424 (0)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 47 | | |
| 2 | NESTED LOOPS | | 2 | 94 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TAB31_DEMO_IX | 1 | 29 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TAB311_DEMO_IX | 2 | 36 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| TAB3 | 9998 | 253K| 424 (0)| 00:00:06 |
|* 6 | INDEX RANGE SCAN | TAB3_PK | 10000 | | 23 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

3 - access("T2"."ITEM_CODE"=:B1 AND "T2"."IS_DELETE"=0)
4 - access("T3"."SUB_ITEM_CODE"="T2"."SUB_ITEM_CODE" AND "T3"."IS_DELETE"=0)
5 - filter("T1"."IS_DELETE"=0 AND "T1"."STATUS_CODE"='00')
6 - access("T1"."UNIQUE_ID">=1 AND "T1"."UNIQUE_ID"<=10000)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
32839 consistent gets
4655 physical reads
0 redo size
326816 bytes sent via SQL*Net to client
7742 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed

SCOTT>
SCOTT> @drop_demo5_ix
drop index tab31_demo_ix;

索引が削除されました。

経過: 00:00:00.42
drop index tab311_demo_ix;

索引が削除されました。

DEMO : NULLとハサミは使いよう。(Index Only Accessがその効果を失う日?!)

※セッション中のデモ時間短縮のため”暴走するスカラー副問合せ”のチューニング後の状態を同一条件の別テーブルで再現してあります。

Index Only Accessでチューニングしたはずの、スカラー副問合せが...再び暴れだした。どゆこと?
処理時間も以前より遅くなってるし、かつ特定の部分のROWSが異常に増加していて、Buffer Getsがすごい事になってます。


Index Only Accessのための索引を作ったものの
SCOTT> @demo5_2_ix
create index tab31_bk_demo_ix on tab31_bk(item_code, is_delete, sub_item_code) nologging;

索引が作成されました。

経過: 00:00:01.25
create index tab311_bk_demo_ix on tab311_bk(sub_item_code, is_delete, unique_id) nologging;

索引が作成されました。

SCOTT> @demo5_2
alter system flush buffer_cache;

システムが変更されました。

経過: 00:00:00.09
1 select
2 t1.unique_id,
3 t1.item_code,
4 (
5 select
6 max(t3.unique_id)
7 from
8 tab31_bk t2 join tab311_bk t3
9 on
10 t3.sub_item_code = t2.sub_item_code
11 and t3.is_delete = 0
12 where
13 t2.item_code = t1.item_code
14 and t2.is_delete = 0
15 ) current_item
16 from
17 tab3 t1
18 where
19 t1.unique_id between 1 and 10000
20 and t1.is_delete = 0
21* and t1.status_code = '00'

10000行が選択されました。

経過: 00:00:34.59

実行計画
----------------------------------------------------------
Plan hash value: 3069420010

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9998 | 253K| 424 (0)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 47 | | |
| 2 | NESTED LOOPS | | 35974 | 1651K| 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TAB31_BK_DEMO_IX | 1 | 29 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TAB311_BK_DEMO_IX | 35978 | 632K| 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| TAB3 | 9998 | 253K| 424 (0)| 00:00:06 |
|* 6 | INDEX RANGE SCAN | TAB3_PK | 10000 | | 23 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

3 - access("T2"."ITEM_CODE"=:B1 AND "T2"."IS_DELETE"=0)
4 - access("T3"."SUB_ITEM_CODE"="T2"."SUB_ITEM_CODE" AND "T3"."IS_DELETE"=0)
5 - filter("T1"."IS_DELETE"=0 AND "T1"."STATUS_CODE"='00')
6 - access("T1"."UNIQUE_ID">=1 AND "T1"."UNIQUE_ID"<=10000)


統計
----------------------------------------------------------
1 recursive calls
0 db block gets
896697 consistent gets
5190 physical reads
0 redo size
326829 bytes sent via SQL*Net to client
7742 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed

SCOTT>

DEMO : NULLとハサミは使いよう。(Index Only Accessがその効果を失う日?!)

なぜ、Index Only Accessが効果を失ってしまったのか。。。
スカラー副問合せのチューニングでは、Index Range ScanやIndex Unique ScanをNested Loop結合かつIndex Only Access化したのだが、
特定の値に大きな偏りがあり、広範囲のIndex Range ScanがNested Loop結合で繰り返されたのがその理由。

特定の値の意味を調査していくと、実はNULLでも問題ないという意味合いしかないことが発覚。

しか〜〜〜し、大人の事情で、該当列をNULLに更新してしまうことは許されない。さて、どのように対処するか!

閃いた!。 Oracle11g で登場した新機能を使え! (恐る恐るw でも事前にKROWNなど調べまくりましたよv)

特定の値をNULLに置換する仮想列を追加して、その列でIndex Only Accesssを実現する索引を作る。さらに、SQL文の結合条件だけは変更してもらう。
(影響範囲を最小にした対処だと思います. 仮想列が無かったら大変だったと思います)

以下の結果の通り、処理時間も以前チューニングした時間まで改善し、広範囲のIndex Range Scanも消えていることが実行計画からも確認できます。めでたしめでたし。

SCOTT> @demo5_2_virtual
alter
table tab311_bk add (sub_item_code_virtual CHAR(10) as (replace(sub_item_code,' ',null)) virtual);

表が変更されました。

経過: 00:00:00.57

SCOTT> @demo5_2_ix_2
create index tab311_bk_demo_vix on tab311_bk(sub_item_code_virtual, is_delete, unique_id) nologging;

索引が作成されました。

経過: 00:00:05.38

SCOTT> @demo5_2_2
alter system flush buffer_cache;

システムが変更されました。

経過: 00:00:00.07
1 select
2 t1.unique_id,
3 t1.item_code,
4 (
5 select
6 max(t3.unique_id)
7 from
8 tab31_bk t2 join tab311_bk t3
9 on
10 t3.sub_item_code_virtual = t2.sub_item_code
11 and t3.is_delete = 0
12 where
13 t2.item_code = t1.item_code
14 and t2.is_delete = 0
15 ) current_item
16 from
17 tab3 t1
18 where
19 t1.unique_id between 1 and 10000
20 and t1.is_delete = 0
21* and t1.status_code = '00'

10000行が選択されました。

経過: 00:00:03.45

実行計画
----------------------------------------------------------
Plan hash value: 2681694377

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9998 | 253K| 424 (0)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 48 | | |
| 2 | NESTED LOOPS | | 7 | 336 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | TAB31_BK_DEMO_IX | 1 | 29 | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | TAB311_BK_DEMO_VIX | 7 | 133 | 2 (0)| 00:00:01 |
|* 5 | TABLE ACCESS BY INDEX ROWID| TAB3 | 9998 | 253K| 424 (0)| 00:00:06 |
|* 6 | INDEX RANGE SCAN | TAB3_PK | 10000 | | 23 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

3 - access("T2"."ITEM_CODE"=:B1 AND "T2"."IS_DELETE"=0)
4 - access("T3"."SUB_ITEM_CODE_VIRTUAL"="T2"."SUB_ITEM_CODE" AND "T3"."IS_DELETE"=0)
5 - filter("T1"."IS_DELETE"=0 AND "T1"."STATUS_CODE"='00')
6 - access("T1"."UNIQUE_ID">=1 AND "T1"."UNIQUE_ID"<=10000)


統計
----------------------------------------------------------
22 recursive calls
0 db block gets
32843 consistent gets
4072 physical reads
0 redo size
322687 bytes sent via SQL*Net to client
7742 bytes received via SQL*Net from client
668 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10000 rows processed

SCOTT>
SCOTT> @drop_demo5_2_ix_2
drop index tab311_bk_demo_vix;

索引が削除されました。

経過: 00:00:00.29
SCOTT> @drop_demo5_2_ix
drop index tab31_bk_demo_ix;

索引が削除されました。

経過: 00:00:00.37
drop index tab311_bk_demo_ix;

索引が削除されました。

経過: 00:00:00.05

SCOTT> @drop_demo5_2_virtual
alter table tab311_bk drop (sub_item_code_virtual);

表が変更されました。

経過: 00:00:00.48

| | コメント (0) | トラックバック (0)

2012年6月 7日 (木)

7/21(土)に Japan Oracle User Group主催のイベント開催!

Japan Oracle User Groupが2012年7月21日(土)に日本オラクル青山センターにてイベントを開催します!

詳細は以下
↓↓↓↓↓
http://www.jpoug.org/2012/06/06/jpoug-set-events-20120721

なお、参加の申し込みはzussarからどうぞ :)
http://www.zusaar.com/event/311004


現在開催に先立ちオーガナイザー募集中
募集は終了しました。ご応募ありがとうございました。

5分だけのライトニングトーク、20分のアンカンファレンス、45分のセミナーで「我こそは ぜひ思いを共有したい」という方を募集中!



オーガナイザー募集の詳細も以下のURLからどうぞ
↓↓↓↓↓

http://www.jpoug.org/2012/06/06/jpoug-set-events-20120721

Logo20120721w1000


| | コメント (0) | トラックバック (0)

2012年4月 8日 (日)

いん!、イン!、Index どっぷり Inde Only Access生活w - Oracle OpenWorld Unconference presented by JPOUG

Oracle OpenWorld Unconference presented by JPOUG
とこかで聞いたようなタイトルを"インデックス"に置換えた「いん!、イン!、Index どっぷり Inde Only Scan生活w」のセッション資料を公開します。
(相変わらず文才ないな〜〜と落ち込む....作文嫌いは死ぬまで治らないと思ってるが、がんばって書くw)

デモ内容とデモ環境情報を追加予定です。 2012/4/15デモとデモ環境情報追加しました)

直前にデモ環境のOracle11gR2が起動しないという想定外のトラブルを乗り越えw かなりイッパイ、イッパイの状態でしたが楽しい時間を過ごさせていただきました。

関係者の皆様、ご来場の皆様、ありがとうございました。

Safari以外のブラウザではアニメーション効果はありませんが、Safari (Mac/iPad/iPhone)ではKeynote風(但し、ページ間のトランジッションなし)に表示されます。

いん!、イン!、IndexどっぷりIndes Only Scan生活w


(2012/4/10追加)
セッションで利用したデモ環境の情報を載せておきます。

MacBook Air late 2010 13inch 2GB (MacOS X Lion)
VirtualBox4.1.10 for MacOS X
GuestOS:CentOS5.8 x86 
DB : Oracle11g R2 11.2.0.1.0 EE for Linux x86

VMの設定は以下のようになってます。
仮想ディスクはほかにもいろいろやるので合計で21GBほどにしてあります :)

20120410_13346_2

(2012/4/15)host i/o cacheはオフにしてあります。
Vbox_no_hostio_cache

late2010のMBAはメモリが2GBなので、VMには1GBのメモリを割り当ててます。
(オラクルのmemory_targetなどの値も書いたほうがいいのかな〜)

(2012/4/15追記)
メモリサイズがギリギリなのでOracleのメモリ関連初期化パラメータも以下の通りで少なめです。

memory_max_target                    big integer 404M
memory_target big integer 404M
sga_max_size big integer 404M
sga_target big integer 0
pga_aggregate_target big integer 0

(2012/4/15追記)
・デモで使った表これ。

13:56:52 SCOTT> desc tab1
名前 NULL? 型
----------------------------- -------- --------------------
UNIQUE_ID NOT NULL NUMBER
NON_UNIQUE_ID NUMBER(10)
NON_UNIQUE_ID2 NUMBER(5)
DATA VARCHAR2(500)
IS_DELETE NUMBER(1)
STATUS_CODE CHAR(2)

13:56:55 SCOTT> desc tab2
名前 NULL? 型
----------------------------- -------- --------------------
UNIQUE_ID2 NOT NULL NUMBER(5)
DATA VARCHAR2(500)
IS_DELETE NUMBER(1)


こんなデータを登録して...

13:56:58 SCOTT> 
15:01:15 SCOTT> begin for i in 1..100000 loop insert into tab1 values(i,ora_hash(i,999),ora_hash(i,99),lpad(i,500,'*'),0,'00'); end loop; end;
15:01:17 SCOTT> /

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:25.46
15:01:44 SCOTT> commit;

コミットが完了しました。

経過: 00:00:00.01
15:01:59 SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TAB1',no_invalidate=>false,cascade=>true);

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:08.50
15:09:43 SCOTT> begin for i in 1..200 loop insert into tab2 values(i,lpad(i,500,'*'),0); end loop; end;
15:10:12 2 /

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.05
15:10:13 SCOTT> commit;

コミットが完了しました。

経過: 00:00:00.08
15:10:16 SCOTT> exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TAB2',no_invalidate=>false,cascade=>true);

PL/SQLプロシージャが正常に完了しました。

経過: 00:00:00.99
15:10:50 SCOTT>


データ件数、データのカーディナリティは以下のようになってます。

15:10:50 SCOTT> @show_tab_info
tabnameに値を入力してください: tab1

SEGMENT_NAME SEGMENT_TYPE SEGMENT_SU MB NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR VISIBLE
------------------------------ ------------------ ---------- ---------- ---------- ------------- ----------------- -------
TAB1 TABLE ASSM 62 100000 YES
TAB1_IX1 INDEX ASSM 3 100000 1000 99400 YES
TAB1_IX2 INDEX ASSM 3 100000 100 94181 YES
TAB1_PK INDEX ASSM 2 100000 100000 7693 YES

経過: 00:00:00.78

TABLE_NAME INDEX_NAME COLUMN_NAME DESC DATA_TYPE NUM_NULLS DENSITY NUM_DISTINCT HI_VALUE LO_VALUE HISTOGRAM
------------------------------ ------------------------------ -------------------- ---- ------------ ---------- ---------- ------------ ------------- ------------- ---------------
TAB1 TAB1_IX1 NON_UNIQUE_ID ASC NUMBER 0 .001 1000 999 0 NONE

TAB1_IX2 NON_UNIQUE_ID2 ASC NUMBER 0 5.0182E-06 100 99 0 FREQUENCY

TAB1_PK UNIQUE_ID ASC NUMBER 0 .00001 100000 100000 1 NONE


経過: 00:00:02.51
15:17:47 SCOTT> @show_tab_info
tabnameに値を入力してください: tab2

SEGMENT_NAME SEGMENT_TYPE SEGMENT_SU MB NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR VISIBLE
------------------------------ ------------------ ---------- ---------- ---------- ------------- ----------------- -------
TAB2 TABLE ASSM 1 200 YES
TAB2_PK INDEX ASSM 1 200 200 15 YES

経過: 00:00:00.78

TABLE_NAME INDEX_NAME COLUMN_NAME DESC DATA_TYPE NUM_NULLS DENSITY NUM_DISTINCT HI_VALUE LO_VALUE HISTOGRAM
------------------------------ ------------------------------ -------------------- ---- ------------ ---------- ---------- ------------ ------------- ------------- ---------------
TAB2 TAB2_PK UNIQUE_ID2 ASC NUMBER 0 .005 200 200 1 NONE


経過: 00:00:02.55
15:18:03 SCOTT>


注)毎回buffer cacheをクリア、かつ、ソフトパース時の結果を載せています。

・demo1

通常ならこの実行計画であればなにもしないですよね...

こんな良さげな実行計画でもSQL文と実行計画それに表定義をじ〜〜〜〜〜〜っと見ていると解決策が見えてきますよね。そうindex only accessならね。

14:45:34 SCOTT> 
14:45:34 SCOTT> set autot trace exp stat
14:45:34 SCOTT> @demo1

システムが変更されました。

経過: 00:00:00.03
1 select
2 unique_id
3 ,status_code
4 from
5 tab1
6 where
7 unique_id between 1 and 1000
8* and is_delete = 0

998行が選択されました。

経過: 00:00:00.07

実行計画
----------------------------------------------------------
Plan hash value: 1911646434

---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 10989 | 81 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| TAB1 | 999 | 10989 | 81 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TAB1_PK | 1000 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

1 - filter("IS_DELETE"=0)
2 - access("UNIQUE_ID">=1 AND "UNIQUE_ID"<=1000)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
210 consistent gets
80 physical reads
0 redo size
19586 bytes sent via SQL*Net to client
1142 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
998 rows processed


is_deleteなんて列を参照するためにだけに行長の長い行を持つ表を、しかも、レンジスキャンしているのでデータアクセス量が多くなっているんですよね。
そこで、index only access

index only accessするための索引を作成してしまえばそれを回避できますよね.

14:45:36 SCOTT> @demo1_ix
create index tab1_ix_demo1 on tab1(unique_id,is_delete,status_code) nologging invisible

索引が作成されました。

経過: 00:00:00.80
alter session set optimizer_use_invisible_indexes=true

セッションが変更されました。

経過: 00:00:00.01


TABLE ACCESSが消えました!
consistent getsは、1/3に減少しましたよね :) 時間も 70msから10ms程度に改善してます。

14:46:05 SCOTT> @demo1

システムが変更されました。

経過: 00:00:00.04
1 select
2 unique_id
3 ,status_code
4 from
5 tab1
6 where
7 unique_id between 1 and 1000
8* and is_delete = 0

998行が選択されました。

経過: 00:00:00.01

実行計画
----------------------------------------------------------
Plan hash value: 327556384

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 999 | 10989 | 4 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| TAB1_IX_DEMO1 | 999 | 10989 | 4 (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

1 - access("UNIQUE_ID">=1 AND "IS_DELETE"=0 AND "UNIQUE_ID"<=1000)
filter("IS_DELETE"=0)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
70 consistent gets
4 physical reads
0 redo size
19586 bytes sent via SQL*Net to client
1142 bytes received via SQL*Net from client
68 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
998 rows processed


次のデモのために作成した索引を削除しておきます。

14:46:13 SCOTT> @drop_demo1_ix
drop index tab1_ix_demo1

索引が削除されました。

経過: 00:00:00.61
alter session set optimizer_use_invisible_indexes=false

セッションが変更されました。

経過: 00:00:00.00

・demo2

demo1同様に行長の長い行をある程度の範囲でレンジスキャンかつ、group/order by句があるのでソート処理が行われています。

これも通常ならチューニングなんてしないのですが、大人の事情で何かできないか? 

と聞かれればindex only accessにして表をアクセスしない+ソート処理も省略しましょうか! と返してあげることが多いです。

14:47:05 SCOTT> @demo2

システムが変更されました。

経過: 00:00:00.02
1 select
2 non_unique_id2
3 ,count(1)
4 from
5 tab1
6 where
7 non_unique_id = 70
8 and is_delete = 0
9 group by
10 non_unique_id2
11 order by
12* non_unique_id2

経過: 00:00:00.06

実行計画
----------------------------------------------------------
Plan hash value: 1524069294

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 63 | 630 | 102 (1)| 00:00:02 |
| 1 | SORT GROUP BY | | 63 | 630 | 102 (1)| 00:00:02 |
|* 2 | TABLE ACCESS BY INDEX ROWID| TAB1 | 100 | 1000 | 101 (0)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | TAB1_IX1 | 100 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

2 - filter("IS_DELETE"=0)
3 - access("NON_UNIQUE_ID"=70)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
103 consistent gets
103 physical reads
0 redo size
517 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed


index only scanにしてソート処理も省略するための索引を作成します。

ではどのような索引を作成すればよいのでしょう?

実行計画のPredicate Informationセクションにはどのような索引を作成するかを決定するための重要な情報がリストされています。

気づきましたか?

以下の2行が重要。

2 - filter("IS_DELETE"=0)
3 - access("NON_UNIQUE_ID"=70)

NON_UNIQUE_ID列に作成されている索引を利用して行を取得し、IS_DELETE=0 でフィルタ処理を実施していることがわかります。

ということは、 NON_UNIQUE_ID,IS_DELETEの2列(つまりWHERE句に記述されている検索条件)がアクセスパスですよね。

次に、選択リスト/GROUP BY句/ORDER BY句を見ると NON_UNIQUE_ID2毎のCOUNT()を取りNON_UNIQUE_ID2を昇順にソートしていることがわかります。

NON_UNIQUE_ID,IS_DELETE列アクセスして、NON_UNIQUE_ID2が昇順にソート済みとなっている索引があればindex only accessになるはず!

14:47:14 SCOTT> @demo2_ix
create index tab1_ix_demo2 on tab1(non_unique_id,is_delete,non_unique_id2) logging invisible

索引が作成されました。

経過: 00:00:00.63
alter session set optimizer_use_invisible_indexes=true

セッションが変更されました。

経過: 00:00:00.01


結果は...

index range scanのみになり、SORT GROUP BY NOSORTというオペレーションからもわかるように、ソート処理が省略されています。;)

consistent getsは約1/50、処理時間は60msから10msまで改善しました。

14:47:28 SCOTT> @demo2

システムが変更されました。

経過: 00:00:00.03
1 select
2 non_unique_id2
3 ,count(1)
4 from
5 tab1
6 where
7 non_unique_id = 70
8 and is_delete = 0
9 group by
10 non_unique_id2
11 order by
12* non_unique_id2

経過: 00:00:00.01

実行計画
----------------------------------------------------------
Plan hash value: 2668135026

--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 63 | 630 | 1 (0)| 00:00:01 |
| 1 | SORT GROUP BY NOSORT| | 63 | 630 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | TAB1_IX_DEMO2 | 100 | 1000 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access("NON_UNIQUE_ID"=70 AND "IS_DELETE"=0)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
2 physical reads
0 redo size
517 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

作成した索引を削除して次のデモへ。

14:47:33 SCOTT> @drop_demo2_ix
drop index tab1_ix_demo2

索引が削除されました。

経過: 00:00:00.10
alter session set optimizer_use_invisible_indexes=false

セッションが変更されました。

経過: 00:00:00.00

・demo3

これdemo2の応用編です. 行長の長い行をある程度の範囲でレンジ検索し、最小値(min)を取得するものです。最大値(max)でも考え方は同じ。

14:47:49 SCOTT> @demo3

システムが変更されました。

経過: 00:00:00.04
1 select
2 min(non_unique_id2)
3 from
4 tab1
5 where
6 non_unique_id = 70
7* and is_delete = 0

経過: 00:00:00.05

実行計画
----------------------------------------------------------
Plan hash value: 255634452

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 101 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
|* 2 | TABLE ACCESS BY INDEX ROWID| TAB1 | 100 | 1000 | 101 (0)| 00:00:02 |
|* 3 | INDEX RANGE SCAN | TAB1_IX1 | 100 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

2 - filter("IS_DELETE"=0)
3 - access("NON_UNIQUE_ID"=70)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
103 consistent gets
103 physical reads
0 redo size
456 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed


demo2と同様に、WHERE句で参照されている検索条件列がアクセスパスになるような索引に、選択リスト中のmin()関数で参照されている列を追加した索引でindex only accessになりそうですよね。

14:47:51 SCOTT> @demo3_ix
create index tab1_ix_demo3 on tab1(non_unique_id, is_delete, non_unique_id2) nologging invisible

索引が作成されました。

経過: 00:00:00.65
alter session set optimizer_use_invisible_indexes=true

セッションが変更されました。

経過: 00:00:00.00


結果は、狙い通りになりました!

処理時間は40msから10msへ。consistent getsは約1/50に改善しました。

実行計画のINDEX RANGE SCAN (MIN/MAX) 〜 FIRST ROWSという素敵なオペレーションが現れています。

Rowsを見ると索引作成前は 100行選択して1行返すという無駄の多い(99行は捨てている)操作から1行とってきて1行返すというエコな動きをしていることが確認できます! 素敵ですね。

14:48:05 SCOTT> @demo3

システムが変更されました。

経過: 00:00:00.04
1 select
2 min(non_unique_id2)
3 from
4 tab1
5 where
6 non_unique_id = 70
7* and is_delete = 0

経過: 00:00:00.01

実行計画
----------------------------------------------------------
Plan hash value: 3012701563

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 10 | | |
| 2 | FIRST ROW | | 1 | 10 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN (MIN/MAX)| TAB1_IX_DEMO3 | 1 | 10 | 2 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

3 - access("NON_UNIQUE_ID"=70 AND "IS_DELETE"=0)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
2 physical reads
0 redo size
456 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

作詞絵作成した索引を削除して次のデモへ...

14:48:09 SCOTT> @drop_demo3_ix
drop index tab1_ix_demo3

索引が削除されました。

経過: 00:00:00.09
alter session set optimizer_use_invisible_indexes=false

セッションが変更されました。

経過: 00:00:00.01


・demo4

大人の事情で実行計画だけみてるとチューニングなんて不要でしょ! という感じのクエリをチューニングできないか?

と依頼されたら・・・

2つの表を結合した結果でgorup/order byしているのでソート処理のバイパスはできせんが、index only accessを使ってNested Loop結合でアクセスされるブロック数を最小化することはできますよね。

14:48:26 SCOTT> @demo4

システムが変更されました。

経過: 00:00:00.12
1 select
2 d.non_unique_id
3 ,m.unique_id2
4 ,count(1)
5 from
6 tab2 m join tab1 d
7 on m.unique_id2 = d.non_unique_id2
8 and m.is_delete = 0
9 and d.is_delete = 0
10 where
11 m.unique_id2 between 1 and 10
12 group by
13 d.non_unique_id
14 ,m.unique_id2
15 order by
16 d.non_unique_id
17* ,m.unique_id2

100行が選択されました。

経過: 00:00:01.71

実行計画
----------------------------------------------------------
Plan hash value: 1194523033

-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1034 | 16544 | 539 (1)| 00:00:07 |
| 1 | SORT GROUP BY | | 1034 | 16544 | 539 (1)| 00:00:07 |
| 2 | NESTED LOOPS | | | | | |
| 3 | NESTED LOOPS | | 1034 | 16544 | 537 (0)| 00:00:07 |
|* 4 | TABLE ACCESS BY INDEX ROWID| TAB2 | 10 | 60 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | TAB2_PK | 10 | | 1 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | TAB1_IX2 | 111 | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID | TAB1 | 103 | 1030 | 106 (0)| 00:00:02 |
-------------------------------------------------------------------------------------------

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

4 - filter("M"."IS_DELETE"=0)
5 - access("M"."UNIQUE_ID2">=1 AND "M"."UNIQUE_ID2"<=10)
6 - access("M"."UNIQUE_ID2"="D"."NON_UNIQUE_ID2")
filter("D"."NON_UNIQUE_ID2"<=10 AND "D"."NON_UNIQUE_ID2">=1)
7 - filter("D"."IS_DELETE"=0)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
9604 consistent gets
5793 physical reads
0 redo size
2588 bytes sent via SQL*Net to client
482 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
100 rows processed


WHERE句やPredicate Informationからも駆動表(TAB2)と内部表(TAB1)は判断できると思います。

駆動表(TAB2)はWHERE句の検索条件および結合条件でアクセスパスとなる列が参照されています。
内部表(TAB1)はWHERE句では参照されておらず、結合条件でのみアクセスパスとなる列が参照されていることがわかります。

選択リストやGROUP/ORDER BY句から、内部表(TAB1)のNON_UNIQUE_ID列も索引に含める必要があるとわかるはずです。

既に気づいた方も多いと思うのですが、index only access化に必要な索引列候補はSQL文だけでも見つけることができるんですよ。

ただ、複雑な結合などが含まれる場合は、実行計画計画やPredicate Informationに有益な情報が載っているので、できれば合わせて見ておくと吉ですね。  

Truth is out there! :) って場合もあるってことで。

14:48:31 SCOTT> @demo4_ix
create index tab2_ix_demo4 on tab2(unique_id2,is_delete) nologging invisible

索引が作成されました。

経過: 00:00:00.06
create index tab1_ix_demo4 on tab1(non_unique_id2, is_delete, non_unique_id) nologging invisible

索引が作成されました。

経過: 00:00:01.21
alter session set optimizer_use_invisible_indexes=true

セッションが変更されました。

経過: 00:00:00.00


大きなクエリほど効果は絶大ということで。

1710msから50msに改善してます。consistents getsは、1/291に :)

14:49:04 SCOTT> @demo4

システムが変更されました。

経過: 00:00:00.05
1 select
2 d.non_unique_id
3 ,m.unique_id2
4 ,count(1)
5 from
6 tab2 m join tab1 d
7 on m.unique_id2 = d.non_unique_id2
8 and m.is_delete = 0
9 and d.is_delete = 0
10 where
11 m.unique_id2 between 1 and 10
12 group by
13 d.non_unique_id
14 ,m.unique_id2
15 order by
16 d.non_unique_id
17* ,m.unique_id2

100行が選択されました。

経過: 00:00:00.04

実行計画
----------------------------------------------------------
Plan hash value: 1672990879

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1034 | 16544 | 31 (4)| 00:00:01 |
| 1 | SORT GROUP BY | | 1034 | 16544 | 31 (4)| 00:00:01 |
| 2 | NESTED LOOPS | | 1034 | 16544 | 30 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN| TAB1_IX_DEMO4 | 10314 | 100K| 30 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN| TAB2_IX_DEMO4 | 1 | 6 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

3 - access("D"."NON_UNIQUE_ID2">=1 AND "D"."IS_DELETE"=0 AND
"D"."NON_UNIQUE_ID2"<=10)
filter("D"."IS_DELETE"=0)
4 - access("M"."UNIQUE_ID2"="D"."NON_UNIQUE_ID2" AND "M"."IS_DELETE"=0)
filter("M"."UNIQUE_ID2"<=10 AND "M"."UNIQUE_ID2">=1)


統計
----------------------------------------------------------
0 recursive calls
0 db block gets
33 consistent gets
31 physical reads
0 redo size
2588 bytes sent via SQL*Net to client
482 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
100 rows processed

14:49:09 SCOTT> @drop_demo4_ix
drop index tab1_ix_demo4

索引が削除されました。

経過: 00:00:00.11
drop index tab2_ix_demo4

索引が削除されました。

経過: 00:00:00.04
alter session set optimizer_use_invisible_indexes=false

セッションが変更されました。

経過: 00:00:00.00


Enjoy!



Index Only Access (Index Only Scan) っていいよね(デメリットもあるけどさ) #1
Index Only Access (Index Only Scan) っていいよね(デメリットもあるけどさ) #2

Index Only Scan, JPOUG, OOW, Oracle Database 11g, SQL, VirtualBox, チューニング | | コメント (1) | トラックバック (0)