db file scattered read と db file parallel read と db file sequential read (その２） Tweet

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

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

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

-----------------------------------------------------------------------------------------------------------
| 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
/

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

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

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

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 (その１）