2016年9月22日 (木)

メモ:SIMDあれこれ

SIMDあれこれ自分メモ

primitive: blog / introdunction to SIMD programming
http://i-saint.hatenablog.com/entry/2015/05/26/212441

wikipedia / Streaming SIMD Extensions
https://ja.wikipedia.org/wiki/Streaming_SIMD_Extensions

IA Software User Society / コンパイラー最適化入門: 第1回 SIMD 命令とプロセッサーの関係
http://www.isus.jp/products/c-compilers/compiler_part1/

IA Software User Society / コンパイラー最適化入門: 第2回 SIMD 命令と伝統的な IA 命令
http://www.isus.jp/products/c-compilers/compiler_part2/

IA Software User Society / コンパイラー最適化入門: 第3回 インテル® コンパイラーのベクトル化レポートを活用する
http://www.isus.jp/products/c-compilers/compiler_part3/

IA Software User Society / コンパイラー最適化入門: 第4回 自動ベクトル化はどんな時に行われるか
http://www.isus.jp/products/c-compilers/compiler_part4/

IA Software User Society / コンパイラー最適化入門: 第5回 明示的にベクトル化されたコードを記述する
http://www.isus.jp/products/c-compilers/compiler_part5/

IA Software User Society / コンパイラー最適化入門: 第6回 ベクトル化の裏技集
http://www.isus.jp/products/c-compilers/compiler_part6/

Oracle In-Memory Column Store Internals – Part 1 – Which SIMD extensions are getting used?
http://blog.tanelpoder.com/2014/10/05/oracle-in-memory-column-store-internals-part-1-which-simd-extensions-are-getting-used/

SIMD-Scan: Ultra Fast in-Memory Table Scan using on- Chip Vector Processing Units
http://www.vldb.org/pvldb/2/vldb09-327.pdf

Rethinking SIMD Vectorization for In-Memory Databases
http://www.cs.columbia.edu/~orestis/sigmod15.pdf

Oracle … as usual / SIMD Extensions in and out Oracle 12.1.0.2
https://laurent-leturgez.com/2015/04/22/simd-extensions-in-and-out-oracle-12-1-0-2/

Laurent Leturgez, / Ukoug15 SIMD outside and inside Oracle 12c (12.1.0.2)
http://www.slideshare.net/lolo115/ukoug15-simd-outside-and-inside-oracle-12c-12102

Cellプログラミングチュートリアル Version 0.2c
http://cell.fixstars.com/ps3linux/tutorial/index.html

| | コメント (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)

2015年5月17日 (日)

Meetup! JPOUG @ Oracle CloudWorld 2015

意外に心?w 時間? に余裕がないのですが、途切れ途切れでも更新していこうかなと50%いや、60%
ぐらい思ってます。

久々の投稿ですが、一ヶ月前のネタです m(_ _)m

Meetup! JPOUG 開催報告

ショートセッション

スペシャル企画 「な〜んでだ?」


ショートセッションのSQLチューニング総合診療Oracle CloudWorld出張所は、db tech showcase 2014 Tokyoの続編のようなw (ような、じゃなくて、続編ですね、キッパリ

いろいろな性能病は無駄な物理I/Oや論理I/Oを削減していくことが、治療の第一歩!。

な〜んでだ? #3は、時間が押してしまい残り5分程度で駆け足で終わらせてしまったので、「な〜んでだ?」は?な雰囲気にできなかったのが心残りでした (笑

AWRを眺めていると、あるような、ないような時間が見えたりしてモヤモヤすることもあると思うんですよね。
そんなときの参考にならないかな? と思ったネタでした。


Oracle Databaseが雲の向こうに行っちゃっても、人工知能型オプティマイザでも登場してこないかぎり、まだ、まだ、人間のサポートが必要だと思うんだよねw いつまで必要なのかは、わからないけど。:)

賢い人工知能型オプティマイザがうまれたとして、各インスタンスごとに性格の違うオプティマイザが成長しちゃったら面白い世界になるかもしれない。
と妄想していると、眠れなくなるので、今日はこのへんで。

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

2015年1月 2日 (金)

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

JPOUG Advent Calendar 2014 17日目のエントリーで公開したクイズを実解答編です。
実際にOracle Database 12c 12.1.0.2.0を使ってどの表を駆動表(外部表)にするか確認してみます。


予想はあたるか、外れるか...どのような結果が待っているのか..ワクワクします。 :-)
Oracle® Database SQLチューニング・ガイド 12cリリース1(12.1) B71277-02 - 7 結合


前回の予想も参考に....机上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
/


私の答え(予想)
Nested Loop結合かつ、INNER JOINですから駆動表(外部表)はデータセットの小さい方。
(Nested Loop結合でOUTER JOINだと結合順は固定されるので駆動表は見つけやすいですが...INNER JOINの場合はそうはいかないですよね。ちなみに、最近のOracleだと、OUTER JOINでHash結合だと外部表を入れれかえて最適化するケースもあります。いずれどこかでそのネタをやると思います。)

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

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

Oracle Database 12c 12.1.0.2.0の動き
以下、SQLモニタリングレポートをみると、駆動表は、 a2表でした。 :)
ちなみに、a1を駆動表にした場合は、若干Buffer Getsが増加していました。誤差の範囲ですが...

SQL Monitoring Report

Global Stats
=======================================
| Elapsed | Other | Fetch | Buffer |
| Time(s) | Waits(s) | Calls | Gets |
=======================================
| 0.00 | 0.00 | 3 | 37 |
=======================================

SQL Plan Monitoring Details (Plan Hash Value=3695212685)
=====================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
=====================================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 20 | | |
| 1 | NESTED LOOPS | | 20 | 23 | 1 | +0 | 1 | 20 | | |
| 2 | NESTED LOOPS | | 20 | 23 | 1 | +0 | 1 | 20 | | |
| 3 | TABLE ACCESS BY INDEX ROWID BATCHED | A2 | 20 | 3 | 1 | +0 | 1 | 20 | | |
| 4 | INDEX RANGE SCAN | SYS_C0010378 | 20 | 2 | 1 | +0 | 1 | 20 | | |
| 5 | INDEX UNIQUE SCAN | SYS_C0010377 | 1 | | 1 | +0 | 20 | 20 | | |
| 6 | TABLE ACCESS BY INDEX ROWID | A1 | 1 | 1 | 1 | +0 | 20 | 20 | | |
=====================================================================================================================================================

問題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が外部表になるはず。

Oracle Database 12c 12.1.0.2.0の動き
以下、SQLモニタリングレポートから....外部表は、予想通りのa2

SQL Monitoring Report

Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.01 | 0.01 | 0.00 | 135 | 497 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=1713954154)
==================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
==================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 2000 | | | |
| 1 | HASH JOIN | | 2000 | 121 | 1 | +0 | 1 | 2000 | 1M | | |
| 2 | TABLE ACCESS FULL | A2 | 2000 | 19 | 1 | +0 | 1 | 2000 | | | |
| 3 | TABLE ACCESS FULL | A1 | 10000 | 102 | 1 | +0 | 1 | 10000 | | | |
==================================================================================================================================


問題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結合になる可能性が高そう)
ヒットするデータ件数から考えると、どちらも無駄なデータアクセスが多くなる可能性は高いのではないでしょうか。


Oracle Database 12c 12.1.0.2.0の動き
おおお〜。予想通り、相関副問合せは、unnestされて結合に書き換えられています。 さらに 12cの新機能であるAdaptive Joinになっています。興味深い。

で本題である、外部表はどうなっているかというと....pk_d1をindex only scanしてNested Loop結合になっています。つまり、駆動表は、予想通り、d1になっています。Yahoo!
id=1でHash結合がありますが、試行はしたようですが、実際にはNested Loop結合だったようです。

SQL Monitoring Report

Global Stats
=======================================
| Elapsed | Other | Fetch | Buffer |
| Time(s) | Waits(s) | Calls | Gets |
=======================================
| 0.01 | 0.01 | 15 | 44 |
=======================================

SQL Plan Monitoring Details (Plan Hash Value=804853015)
==============================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) |
==============================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 0 | | |
| 1 | HASH JOIN | | 200 | 5 | 1 | +0 | 1 | 0 | | |
| 2 | NESTED LOOPS | | 200 | 5 | 1 | +0 | 1 | 200 | | |
| 3 | NESTED LOOPS | | 200 | 5 | 1 | +0 | 1 | 200 | | |
| 4 | STATISTICS COLLECTOR | | | | 1 | +0 | 1 | 1 | | |
| 5 | INLIST ITERATOR | | | | 1 | +0 | 1 | 2 | | |
| 6 | INDEX UNIQUE SCAN | PK_D1 | 2 | 1 | 1 | +0 | 2 | 2 | | |
| 7 | INDEX RANGE SCAN | PK_D2 | 1 | 1 | 1 | +0 | 2 | 200 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | D2 | 100 | 2 | 1 | +0 | 200 | 200 | | |
| 9 | INLIST ITERATOR | | | | | | | | | |
| 10 | TABLE ACCESS BY INDEX ROWID BATCHED | D2 | 100 | 2 | | | | | | |
| 11 | INDEX RANGE SCAN | PK_D2 | 1 | 1 | | | | | | |
==============================================================================================================================================


問題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


Oracle Database 12c 12.1.0.2.0の動き
良かったw。 予想どおりソートを回避したようで、 外部表は、 d2ですね。

SQL Monitoring Report

Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.02 | 0.01 | 0.01 | 198 | 48 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=705618498)
=============================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
=============================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 2955 | | | |
| 1 | MERGE JOIN | | 3966 | 10 | 1 | +0 | 1 | 2955 | | | |
| 2 | TABLE ACCESS BY INDEX ROWID | D2 | 20 | 4 | 1 | +0 | 1 | 15 | | | |
| 3 | INDEX RANGE SCAN | PK_D2 | 20 | 3 | 1 | +0 | 1 | 15 | | | |
| 4 | SORT JOIN | | 199 | 6 | 1 | +0 | 15 | 2955 | 145K | | |
| 5 | TABLE ACCESS FULL | D1 | 199 | 5 | 1 | +0 | 1 | 199 | | | |
=============================================================================================================================================


問題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 ですよね。


Oracle Database 12c 12.1.0.2.0の動き
結果は、予想通りでした〜。これは簡単だから!。

SQL Monitoring Report

Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.06 | 0.04 | 0.02 | 335 | 1476 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=3711653655)
===================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
===================================================================================================================================
| 0 | SELECT STATEMENT | | | | 1 | +0 | 1 | 5000 | | | |
| 1 | HASH JOIN | | 5000 | 315 | 1 | +0 | 1 | 5000 | 1M | | |
| 2 | HASH JOIN | | 500 | 247 | 1 | +0 | 1 | 500 | 1M | | |
| 3 | TABLE ACCESS FULL | B3 | 500 | 9 | 1 | +0 | 1 | 500 | | | |
| 4 | TABLE ACCESS FULL | B1 | 20000 | 238 | 1 | +0 | 1 | 20000 | | | |
| 5 | TABLE ACCESS FULL | B2 | 5000 | 68 | 1 | +0 | 1 | 5000 | | | |
===================================================================================================================================


問題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.

Oracle Database 12c 12.1.0.2.0の動き
結果を見てみると〜〜〜〜〜っ。恐れていたw 外部表と内部表の置き換え操作(LEFT OUTERがRIGHT OUTER)になってますね〜っ。あはは、見事に、外れました....orz.

SQL Monitoring Report

Global Stats
=================================================
| Elapsed | Cpu | Other | Fetch | Buffer |
| Time(s) | Time(s) | Waits(s) | Calls | Gets |
=================================================
| 0.34 | 0.21 | 0.13 | 1651 | 2648 |
=================================================

SQL Plan Monitoring Details (Plan Hash Value=1453650298)
======================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | (Max) | (%) | (# samples) |
======================================================================================================================================
| 0 | SELECT STATEMENT | | | | 4 | +0 | 1 | 24750 | | | |
| 1 | HASH JOIN RIGHT OUTER | | 203K | 316 | 4 | +0 | 1 | 24750 | 3M | | |
| 2 | TABLE ACCESS FULL | B2 | 5000 | 68 | 1 | +0 | 1 | 5000 | | | |
| 3 | HASH JOIN RIGHT OUTER | | 20250 | 247 | 4 | +0 | 1 | 20250 | 1M | | |
| 4 | TABLE ACCESS FULL | B3 | 500 | 9 | 1 | +0 | 1 | 500 | | | |
| 5 | TABLE ACCESS FULL | B1 | 20000 | 238 | 4 | +0 | 1 | 20000 | | | |
======================================================================================================================================

問題ごとの解説をおまけで何回かに分けて、書いた方が良さそうだなw

ということで、おまけエントリーを書くかも...し、れ、な、い。


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

| | コメント (1) | トラックバック (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年8月23日 (土)

IO Resource Manager 関係のメモ

黒い画面の好きなyskwkzhrさんも気に入るよね、きっと :)


PART 1 of 4 "Test interdatabase consolidation with Oracle Exadata IORM"

PART 2 of 4 "Test interdatabase consolidation with Oracle Exadata IORM"

PART 3 of 4 "Test intradatabase consolidation with Oracle Exadata IORM"

PART 4 of 4 "Test intradatabase consolidation with Oracle Exadata IORM"


ここで使ってるモニターリングツールはSwingbenchでも有名なDominic Gilesさんのサイトね。ステキです:)
Database Time Monitor
CPU Monitor
Monitor DB

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

2014年8月19日 (火)

pivotとSQL*PlusとSETコマンドと #2

昨日の続きですw

v$sys_time_modelの列データを行データへpivotで変換し、かつシェルで定期的に取得してみたものの、出力形式は今ひとつ。 iostatやvmstatのように出力したい。

前回のエントリーの出力結果は以下の通りでした。

[oracle ˜]$ ./sample.sh

SQL*Plus: Release 12.1.0.2.0 Production on 日 8月 17 21:10:47 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
に接続されました。
21:10:48 SCOTT>
DB_TIME DB_CPU BG_TIME BG_CPU
------------------------------- ------------------------------- ------------------------------- -------------------------------
66172667 7714783 127809578 27114874

経過: 00:00:00.02
21:10:48 SCOTT>
DB_TIME DB_CPU BG_TIME BG_CPU
------------------------------- ------------------------------- ------------------------------- -------------------------------
66179178 7720782 128085853 27160868

経過: 00:00:00.01
21:10:57 SCOTT>
DB_TIME DB_CPU BG_TIME BG_CPU
------------------------------- ------------------------------- ------------------------------- -------------------------------
66180864 7723782 128249102 27197862

経過: 00:00:00.00

じゃまな出力は、以下の通り

  • SQL*Plusのプロンプト
  • ヘッダー行
  • 経過時間
  • 出力の状態から見て、余分な改行

そして、不足している出力はメトリックのログ取得時のタイムスタンプ


以下のSQL*Plusシステム変数を調整追加すればなんとかなりそうな感じ。。。。

  • SQL*Plusのプロンプトは、 set sqlp "" で抑止。
  • ヘッダー行は、 set head off で抑止
  • 経過時間は、 set timi off で抑止
  • 余分な改行は、 たぶん、 set newp none で抑止
  • 直接関係ないけど、Excelにコピペするときにじゃまになるので set tab offでタブの混入抑止


不足しているログ取得時のタイムスタンプは、シェルのdateコマンドで取得した日時をSQL文に埋め込むことでなんとかなりそうな気がします。

と頭に浮かんだら忘れないうちに試してみますよ〜

★横に長くてごめんなさい。時間取れたらSyntaxHighlighterとか入れます詐欺 m(_ _)m

#!/bin/bash
#
(
echo "conn scott/tiger"
echo "set timi off time off tab off sqlp \"\" head off newp none"
echo "col db_time for 999999999999999999999999999999"
echo "col db_cpu for 999999999999999999999999999999"
echo "col bg_time for 999999999999999999999999999999"
echo "col bg_cpu for 999999999999999999999999999999"
while [ 1 ]
do
echo "SELECT db_time,db_cpu,bg_time,bg_cpu FROM (SELECT stat_name,value FROM v\$sys_time_model) PIVOT (MAX(value) FOR stat_name IN ('DB time' AS db_time,'DB CPU' AS db_cpu,'background elapsed time' AS bg_time,'background cpu time' AS bg_cpu));"
sleep 10
done
) | sqlplus /nolog


ん〜〜〜〜〜、なんか、惜しい!!!!  いい感じにななったのに。... しばし考える。。。。

[oracle ˜]$ ./sample.sh

SQL*Plus: Release 12.1.0.2.0 Production on 日 8月 17 21:45:43 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.

21:45:43 > 接続されました。
21:45:44 SCOTT> 67126005 8476651 182158980 37408311

67129138 8479651 182310474 37445305


きた〜〜〜、神が降りてきたのでちょっと書き換えた

#!/bin/bash
#
(
echo "conn scott/tiger"
echo "set timi off time off sqlp \"\""
echo "col db_time for 999999999999999999999999999999"
echo "col db_cpu for 999999999999999999999999999999"
echo "col bg_time for 999999999999999999999999999999"
echo "col bg_cpu for 999999999999999999999999999999"
while [ 1 ]
do
t=`date +'%DT%T'`
echo "SELECT SUBSTR('${t}',1,INSTR('${t}','T')-1) as logged_date,SUBSTR('${t}',INSTR('${t}','T')+1) as logged_time,db_time,db_cpu,bg_time,bg_cpu FROM (SELECT stat_name,value FROM v\$sys_time_model) PIVOT (MAX(value) FOR stat_name IN ('DB time' AS db_time,'DB CPU' AS db_cpu,'background elapsed time' AS bg_time,'background cpu time' AS bg_cpu));"
echo "set head off newp none"
sleep 10
done
) | sqlplus /nolog


ん〜〜〜、まだ余計な改行というか空行がある。。。なんだこれ。。。。再び、考え中........ あ、あれだ! 出力行数を返すやつ!

SQL*Plus: Release 12.1.0.2.0 Production on 日 8月 17 21:56:21 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.

21:56:21 > 接続されました。
21:56:22 SCOTT>
LOGGED_D LOGGED_T DB_TIME DB_CPU BG_TIME BG_CPU
-------- -------- ------------------------------- ------------------------------- ------------------------------- -------------------------------
08/17/14 21:56:21 67688748 8937570 198751230 40650823

08/17/14 21:56:31 67695983 8945569 199012673 40699815


で、できたのがこれ。

#!/bin/bash
#
(
echo "conn scott/tiger"
echo "set timi off time off sqlp \"\" feed off"
echo "col db_time for 999999999999999999999999999999"
echo "col db_cpu for 999999999999999999999999999999"
echo "col bg_time for 999999999999999999999999999999"
echo "col bg_cpu for 999999999999999999999999999999"
while [ 1 ]
do
t=`date +'%DT%T'`
echo "SELECT SUBSTR('${t}',1,INSTR('${t}','T')-1) as logged_date,SUBSTR('${t}',INSTR('${t}','T')+1) as logged_time,db_time,db_cpu,bg_time,bg_cpu FROM (SELECT stat_name,value FROM v\$sys_time_model) PIVOT (MAX(value) FOR stat_name IN ('DB time' AS db_time,'DB CPU' AS db_cpu,'background elapsed time' AS bg_time,'background cpu time' AS bg_cpu));"
echo "set head off newp none"
sleep 10
done

) | sqlplus /nolog

出力結果は以下のようになり、 iostatやvmstat風にヘッダー行は一度だけ、その後、定期的に取得されるメトリックが出力されていくイメージに! :)

[oracle ˜]$ ./sample.sh

SQL*Plus: Release 12.1.0.2.0 Production on 日 8月 17 22:02:59 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.

22:02:59 > 接続されました。
22:02:59 SCOTT>
LOGGED_D LOGGED_T DB_TIME DB_CPU BG_TIME BG_CPU
-------- -------- ------------------------------- ------------------------------- ------------------------------- -------------------------------
08/17/14 22:02:59 68026469 9252517 211057107 42641516
08/17/14 22:03:09 68034074 9261515 211217278 42686507
08/17/14 22:03:19 68039874 9267514 211414489 42730501
08/17/14 22:03:29 68045221 9272514 211576132 42769498
08/17/14 22:03:39 68051396 9276513 211844118 42862480
^C
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optionsとの接続が切断されました。


Enjoy!


pivotとSQL*PlusとSETコマンドと

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

2014年8月18日 (月)

pivotとSQL*PlusとSETコマンドと

Oracle Databaseの性能試験で以下のようなメトリックを定期取得して、分析やビジュアライズに利用している方も多いと思います。(思ってます。)
でも、ですねぇ。
以下のv$sys_time_model動的パフォーマンスビューも典型例なのですが、列持ちのメトリックが多いので集計にはかなり苦労します。というか、してます。

v$sys_time_model動的パフォーマンスビューの出力例)

SCOTT> SELECT stat_name, value from v$sys_time_model;

STAT_NAME VALUE
---------------------------------------------------------------- ----------
DB time 64073868
DB CPU 6549986
background elapsed time 42988476
background cpu time 11080311

・・・以下略・・・

列持ちなんですよね、 列持ち!(しつこいw

行持ちにしたいですよね。 どう料理しましょう。 まさか、手作業ではやってないですよね。

SQL文でやってますよね! 私もそうです。
ちなみに、UNION連打はしてませんからね!(キリっ!

昔はほかに手がなかったのですが、Oracle11gから便利で比較的読みやすい構文がサポートされています。

列持ちを行持ちにするといえば....そうです、あれです。 pivot

ということで、

pivotを使って、v$sys_time_modelを例にオレオレv$sys_time_modelを作り出してみます。
(これができれば、数あるオラクルの動的パフォーマンスビューをもっと好きになれるんじゃないかなぁ。と思います。)

では、早速

v$sys_time_modelの stat_name列の列値が、'DB time'、'DB CPU'、 'background elapsed time'、'background cpu time'の4つのメトリックを列持ちから行持ちに変え、オレオレv$sys_time_model作り出すSQL文です。
ビューは作りませんけど (^^;;;

SELECT 
db_time
,db_cpu
,bg_time
,bg_cpu
FROM
(
SELECT
stat_name
,value
FROM
v$sys_time_model
)
PIVOT
(
MAX(value)
FOR stat_name IN
(
'DB time' AS db_time
,'DB CPU' AS db_cpu
,'background elapsed time' AS bg_time
,'background cpu time' AS bg_cpu
)
)
;


これを実行すると以下のような結果になります。本来4行なのですが、1行にできるんです! 便利ですね。 pivot (pivotの逆の操作をする unpivotもあります)

   DB_TIME     DB_CPU    BG_TIME     BG_CPU
---------- ---------- ---------- ----------
63895360 6364016 32965031 8718671

いい感じになってきました。


しかし、まだ物足りないですよね。 そう!
取得時のタイムスタンプとか、例えば iostat や vmstatのように定期的に取得したくなってきます!!!!

「門外不出のOracle現場ワザ」 第5章 DBアクセスの空白地帯 コネクションプーリングを極めるの定期的にSQLを発行するシェルを作成するには? でも解説されているのでこの方法で取得されている方も多いと思います。:)

ただ、そのまんまだと以下のような出力になってしまいます。 iostatやvmstatの出力をイメージしちゃうと余分な表示が多いわけです。

[oracle ˜]$ ./sample.sh

SQL*Plus: Release 12.1.0.2.0 Production on 日 8月 17 21:10:47 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.


Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
に接続されました。
21:10:48 SCOTT>
DB_TIME DB_CPU BG_TIME BG_CPU
------------------------------- ------------------------------- ------------------------------- -------------------------------
66172667 7714783 127809578 27114874

経過: 00:00:00.02
21:10:48 SCOTT>
DB_TIME DB_CPU BG_TIME BG_CPU
------------------------------- ------------------------------- ------------------------------- -------------------------------
66179178 7720782 128085853 27160868

経過: 00:00:00.01
21:10:57 SCOTT>
DB_TIME DB_CPU BG_TIME BG_CPU
------------------------------- ------------------------------- ------------------------------- -------------------------------
66180864 7723782 128249102 27197862

経過: 00:00:00.00

前述の出力は以下のコードで取得したのですが、実はそんなに手を加えなくても vmstatやiostatのような出力形式で、みなさんの大好きなExcelで集計しやすくすることができるんですよ。
どこを変更すればよいか分かった人、手を挙げて〜〜〜〜っ!

注)scottユーザにselect any dictionaryシステム権限付けてます。

#!/bin/bash
#
(
echo "conn scott/tiger"
echo "set timi on time on"
echo "col db_time for 999999999999999999999999999999"
echo "col db_cpu for 999999999999999999999999999999"
echo "col bg_time for 999999999999999999999999999999"
echo "col bg_cpu for 999999999999999999999999999999"
while [ 1 ]
do
echo "SELECT db_time,db_cpu,bg_time,bg_cpu FROM (SELECT stat_name,value FROM v\$sys_time_model) PIVOT (MAX(value) FOR stat_name IN ('DB time' AS db_time,'DB CPU' AS db_cpu,'background elapsed time' AS bg_time,'background cpu time' AS bg_cpu));"
sleep 10
done
) | sqlplus /nolog


つづきは、次のエントリーで :)

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

2014年7月26日 (土)

TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #3

つづきです。
バッファキャッシュにヒットした場合はどうかというと、物理I/Oは発生しないので、そのまんまの結果ですよね。(^^;;;

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 195 0.11 0.18 0 6287 0 2907
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 197 0.11 0.18 0 6287 0 2907

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110 (SCOTT)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
2907 2907 2907 NESTED LOOPS (cr=6287 pr=0 pw=0 time=206651 us)
2907 2907 2907 NESTED LOOPS (cr=3380 pr=0 pw=0 time=116870 us cost=5362 size=1744812 card=2851)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID BATCHED HIGH_CLUSTERING_FACTOR (cr=3108 pr=0 pw=0 time=42395 us cost=2861 size=872406 card=2851)
2907 2907 2907 INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=201 pr=0 pw=0 time=12292 us cost=8 size=0 card=2851)(object id 93727)
2907 2907 2907 INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=272 pr=0 pw=0 time=36902 us cost=0 size=0 card=1)(object id 93725)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=2907 pr=0 pw=0 time=37280 us cost=1 size=306 card=1)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
2907 NESTED LOOPS
2907 NESTED LOOPS
2907 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID BATCHED) OF 'HIGH_CLUSTERING_FACTOR' (TABLE)
2907 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_HIGH_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_LOW_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'LOW_CLUSTERING_FACTOR' (TABLE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 195 0.00 0.00
SQL*Net message from client 195 0.00 0.15
SQL*Net more data to client 193 0.00 0.01
********************************************************************************

12cで、optimizer_features_enable='11.2.0.1'にしてバッファキャッシュをクリアすれば、11gと同じ実行計画(TABLE ACCESS BY INDEX ROWID)になって、db file parallel readになるよね!

という確認もしておいた!

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.42 1.59 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 195 18.34 61.02 2450 6287 0 2907
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 197 18.77 62.63 2450 6287 0 2907

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110 (SCOTT)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
2907 2907 2907 NESTED LOOPS (cr=6287 pr=2450 pw=0 time=92881090 us)
2907 2907 2907 NESTED LOOPS (cr=3380 pr=2232 pw=0 time=59488238 us cost=5362 size=1744812 card=2851)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID HIGH_CLUSTERING_FACTOR (cr=3108 pr=2221 pw=0 time=31142410 us cost=2861 size=872406 card=2851)
2907 2907 2907 INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=201 pr=7 pw=0 time=3493710 us cost=8 size=0 card=2851)(object id 93727)
2907 2907 2907 INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=272 pr=11 pw=0 time=10377721 us cost=0 size=0 card=1)(object id 93725)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=2907 pr=218 pw=0 time=13380036 us cost=1 size=306 card=1)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
2907 NESTED LOOPS
2907 NESTED LOOPS
2907 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID BATCHED) OF 'HIGH_CLUSTERING_FACTOR' (TABLE)
2907 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_HIGH_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_LOW_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'LOW_CLUSTERING_FACTOR' (TABLE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 195 0.00 0.21
Disk file operations I/O 1 0.00 0.00
db file sequential read 452 0.21 1.54
SQL*Net message from client 195 15.35 16.71
SQL*Net more data to client 193 0.00 0.47
db file parallel read 190 0.05 5.91
********************************************************************************


整形前SQLトレースより抜粋 (db file parallel readが索引で現れている

WAIT #140513423347200: nam='db file parallel read' ela= 16615 files=1 blocks=13 requests=13 obj#=93726 tim=6258017207

straceより抜粋...io_submit()、だよね〜。:)

io_submit(140513465466880, 13, ...中略... ) = 13


最後に、11g R2 11.2.0.1で同じことを確認(確認するまでもないんだけどね)

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 169 0.58 6.88 2148 5439 0 2509
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 171 0.58 6.88 2148 5439 0 2509

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84 (SCOTT)

Rows Row Source Operation
------- ---------------------------------------------------
2509 NESTED LOOPS (cr=5439 pr=2148 pw=0 time=19292592 us)
2509 NESTED LOOPS (cr=2930 pr=1931 pw=0 time=24524478 us cost=5012 size=1530612 card=2501)
2509 TABLE ACCESS BY INDEX ROWID HIGH_CLUSTERING_FACTOR (cr=2683 pr=1922 pw=0 time=24392808 us cost=2510 size=765612 card=2502)
2509 INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=174 pr=5 pw=0 time=11616 us cost=8 size=0 card=2502)(object id 113222)
2509 INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=247 pr=9 pw=0 time=0 us cost=0 size=0 card=1)(object id 113220)
2509 TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=2509 pr=217 pw=0 time=0 us cost=1 size=306 card=1)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
2509 NESTED LOOPS
2509 NESTED LOOPS
2509 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'HIGH_CLUSTERING_FACTOR' (TABLE)
2509 INDEX MODE: ANALYZED (RANGE SCAN) OF 'PK_HIGH_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2509 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'PK_LOW_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2509 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF 'LOW_CLUSTERING_FACTOR' (TABLE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 169 0.00 0.00
SQL*Net message from client 169 24.57 25.54
Disk file operations I/O 1 0.00 0.00
db file parallel read 167 0.09 5.66
db file sequential read 357 0.01 0.92
SQL*Net more data to client 167 0.00 0.01
********************************************************************************

11gまでは、実行計画上には現れず実行時に内部的に行っていたI/O最適化の動きが、12cからは実行計画上でも確認できるようになったというのは分かり易くていい。
時間あったら、v$sesstatも載せるかも。 :)

さて、12c 12.1.0.2.0のダウンロードも終わったので、別の楽しみが増えましたよね、みなさん!

ダウンロード! しましたか〜〜〜〜〜っ! :)

みなさんの、12c ネタ祭りを楽しみにしております。 (^^


TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #1
TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #2

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

2014年7月25日 (金)

TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #2

Oracle Database 12c R1 12.1.0.2.0 EEが公開されましたが、12.1.0.1.0で試してあります。

注)バッファキャッシュをクリア後にSQL文を実行してあります。

SELECT
/*+
leading(t2 t1)
use_nl(t2 t1)
index(t2 pk_high_clustering_factor)
*/
t2.id
,t2.name
,t1.name
FROM
low_clustering_factor t1
INNER JOIN high_clustering_factor t2
ON
t1.id = t2.id
WHERE
t2.id BETWEEN 30001 AND 35000

上記のSQL文でTABLE ACCESS BY INDEX ROWID BATCHED操作を行わせてみた。

以下、SQLトレースの結果です。予想通り db file parallel readが発生しています。
高いクラスタリングファクタを持つ索引をアクセス、不連続なROWDをかき集めかき集めたROWIDをある程度まとめて1度のI/Oリクエストでバッファキャッシュへ読み込む動作ですよね!

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.07 6 6 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 195 0.48 4.92 2288 6338 0 2907
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 197 0.50 4.99 2294 6344 0 2907

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 110 (SCOTT)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
2907 2907 2907 NESTED LOOPS (cr=6338 pr=2288 pw=0 time=2150320 us)
2907 2907 2907 NESTED LOOPS (cr=3431 pr=2087 pw=0 time=6036546 us cost=3368 size=1159620 card=3514)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID BATCHED HIGH_CLUSTERING_FACTOR (cr=3107 pr=2082 pw=0 time=5948676 us cost=1012 size=579810 card=3514)
2907 2907 2907 INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=201 pr=0 pw=0 time=15435 us cost=11 size=0 card=3514)(object id 93727)
2907 2907 2907 INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=324 pr=5 pw=0 time=56981 us cost=0 size=0 card=1)(object id 93725)
2907 2907 2907 TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=2907 pr=201 pw=0 time=312848 us cost=1 size=165 card=1)


Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
2907 NESTED LOOPS
2907 NESTED LOOPS
2907 TABLE ACCESS (BY INDEX ROWID BATCHED) OF 'HIGH_CLUSTERING_FACTOR' (TABLE)
2907 INDEX (RANGE SCAN) OF 'PK_HIGH_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 INDEX (UNIQUE SCAN) OF 'PK_LOW_CLUSTERING_FACTOR' (INDEX (UNIQUE))
2907 TABLE ACCESS (BY INDEX ROWID) OF 'LOW_CLUSTERING_FACTOR' (TABLE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 195 0.00 0.00
db file sequential read 209 0.03 0.32
SQL*Net message from client 195 19.95 21.21
db file parallel read 194 0.09 4.33
SQL*Net more data to client 193 0.00 0.01
********************************************************************************

SQLトレース(整形前)のトレースログより : db file parallel read (この時は、最小 3blocks〜15blocksの範囲で行われていた。

WAIT #139657829206792: nam='db file parallel read' ela= 64710 files=1 blocks=12 requests=12 obj#=93726 tim=4037078687

ちなみにstraceでみるとio_submitが呼び出されていて11gの頃と変わりはなかった :)

つまり、実行計画上、db file parallel readやるからね!! 物理I/Oを行うときは! という意思が明確に表示されるようになった。 
分かり易くなっていい!!!!
これだと物理I/Oがあれば、db file parallel readやってるな〜と実行計画を見ただけで想像できますな:)




TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #1

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

2014年7月23日 (水)

TABLE ACCESS BY INDEX ROWID BATCHED (Oracle Database 12c R1) ってなに! #1

Oracle® Database SQLチューニング・ガイド 12cリリース1(12.1) - ROWIDによる表アクセス: 例にも記載され、昨年試していたときにも現れていた、table access by index rowid batched という操作って、実際のところdb file parallel readをやるよ! と実行計画に表すようになったということだよね! それだけだよね。

ということを確認してみた。(TODOと書いてから1年経過してしまった orz )

詳細はあした以降に書きまする。 :)

20140723_220538

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

2014年6月11日 (水)

html5j パフォーマンス部 @ DeNA へ参加してきた

html5j パフォーマンス部 第二回勉強会へ参加してきた。

勉強会2連チャンでした :)

html5jとか勉強会(そんな名称になる前から参加してました :))には何度か参加していますが、パフォーマンスという単語に脊髄反応して当日のセッション内容を確認もぜず参加してしまったw のは内緒。


データベースが遅い! と呼ばれ、調査すると本当に遅いのはブラウザの上、なんてことも意外に多いんですよね。

10301204_712086918857590_3502558762

講義を受けてるみたいで脳に汗かいた。 

1.品質管理と統計学「IT業界へどうつなぐ?」 - 成蹊大学 / 中西寛子
2.ソフトウェア工学の基礎を学ぶ - 大槻肇


ところで、
今回で2回目のようですが、16回先までテーマが決まっているようでおどろきました。
他の勉強会を運営している方がってどうしてるんだろ?。  

そして、DeNAさんのセミナー会場いいですよね〜〜〜。
都合がつけば次回も参加すると思います。 :)

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