« db file scattered read と db file parallel read と db file sequential read (その1) | トップページ | db file scattered read と db file parallel read と db file sequential read (その3) »

2013年4月 1日 (月)

db file scattered read と db file parallel read と db file sequential read (その2)

答え分かりましたかねぇ?

環境情報など再現方法などは次回にしますが、どうなるかやってみましょう。(事前にSQLトレースを有効にしてあります。)


※各SQLはヒントで実行計画を固定してあります。
最初は参考資料3の実行計画になるクエリから実行します。

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2501 | 1494K| 5012 (1)| 00:01:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2501 | 1494K| 5012 (1)| 00:01:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| HIGH_CLUSTERING_FACTOR | 2502 | 747K| 2510 (1)| 00:00:31 |
|* 4 | INDEX RANGE SCAN | PK_HIGH_CLUSTERING_FACTOR | 2502 | | 8 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_LOW_CLUSTERING_FACTOR | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | LOW_CLUSTERING_FACTOR | 1 | 306 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

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
/

次は参考資料2の実行計画になるクエリを実行します。

-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2500 | 1494K| 5231 (1)| 00:01:03 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 2500 | 1494K| 5231 (1)| 00:01:03 |
| 3 | TABLE ACCESS BY INDEX ROWID| LOW_CLUSTERING_FACTOR | 5000 | 1494K| 229 (0)| 00:00:03 |
|* 4 | INDEX RANGE SCAN | PK_LOW_CLUSTERING_FACTOR | 5000 | | 11 (0)| 00:00:01 |
|* 5 | INDEX UNIQUE SCAN | PK_HIGH_CLUSTERING_FACTOR | 1 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | HIGH_CLUSTERING_FACTOR | 1 | 306 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

SELECT
/*+
leading(t1 t2)
use_nl(t1 t2)
index(t1 pk_low_clustering_factor)
*/
t1.id
,t1.name
,t2.name
FROM
low_clustering_factor t1
INNER JOIN high_clustering_factor t2
ON
t1.id = t2.id
WHERE
t1.id BETWEEM 1 AND 5000
/

どのような待機イベントが発生しているかSQLトレース結果(tkprofで整形済み)を見てみると...

最初のクエリのトレース結果

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 163 0.30 1.06 2841 5252 0 2421
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 165 0.30 1.06 2841 5252 0 2421

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84

Rows Row Source Operation
------- ---------------------------------------------------
2421 NESTED LOOPS (cr=5252 pr=2841 pw=0 time=2771706 us)
2421 NESTED LOOPS (cr=2831 pr=2522 pw=0 time=4204941 us cost=5012 size=1530612 card=2501)
2421 TABLE ACCESS BY INDEX ROWID HIGH_CLUSTERING_FACTOR (cr=2589 pr=2458 pw=0 time=4188001 us cost=2510 size=765612 card=2502)
2421 INDEX RANGE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=168 pr=121 pw=0 time=7663 us cost=8 size=0 card=2502)(object id 82774)
2421 INDEX UNIQUE SCAN PK_LOW_CLUSTERING_FACTOR (cr=242 pr=64 pw=0 time=0 us cost=0 size=0 card=1)(object id 82772)
2421 TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=2421 pr=319 pw=0 time=0 us cost=1 size=306 card=1)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 163 0.00 0.00
db file scattered read 107 0.01 0.30
SQL*Net message from client 163 0.96 3.36
db file parallel read 161 0.01 0.63
SQL*Net more data to client 161 0.00 0.00
db file sequential read 14 0.00 0.00

2つ目のクエリのトレース結果

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 173 0.28 0.72 1874 3367 0 2566
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 175 0.28 0.73 1874 3367 0 2566

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84

Rows Row Source Operation
------- ---------------------------------------------------
2566 NESTED LOOPS (cr=3367 pr=1874 pw=0 time=802588 us)
2566 NESTED LOOPS (cr=801 pr=381 pw=0 time=115791 us cost=5231 size=1530000 card=2500)
5000 TABLE ACCESS BY INDEX ROWID LOW_CLUSTERING_FACTOR (cr=564 pr=286 pw=0 time=42241 us cost=229 size=1530000 card=5000)
5000 INDEX RANGE SCAN PK_LOW_CLUSTERING_FACTOR (cr=181 pr=32 pw=0 time=26744 us cost=11 size=0 card=5000)(object id 82772)
2566 INDEX UNIQUE SCAN PK_HIGH_CLUSTERING_FACTOR (cr=237 pr=95 pw=0 time=0 us cost=0 size=0 card=1)(object id 82774)
2566 TABLE ACCESS BY INDEX ROWID HIGH_CLUSTERING_FACTOR (cr=2566 pr=1493 pw=0 time=0 us cost=1 size=306 card=1)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 173 0.00 0.00
db file scattered read 381 0.01 0.42
db file sequential read 287 0.00 0.14
SQL*Net message from client 172 0.04 2.83
SQL*Net more data to client 171 0.00 0.00


ということで、

A) db file scattered read
B) db file parallel read
C) db file sequential read

A)、B)、C)すべての待機イベント発生しました!!! :)  

実に興味深い。。。。 実行計画見てても、分からないですよね、こればかりは。


次回へ続く。




バックナンバー
db file scattered read と db file parallel read と db file sequential read (その1)

|

トラックバック

この記事のトラックバックURL:
http://app.cocolog-nifty.com/t/trackback/106341/57078591

この記事へのトラックバック一覧です: db file scattered read と db file parallel read と db file sequential read (その2):

コメント

コメントを書く