DB Tech Showcase 2016 - E35 - SQLチューニング総合診療所的予防医学

310 Views

July 16, 16

#Database Operations #Hash Join #Memory Use #Temp Use #Table Access

スライド概要

Slideshare移行 2023-08-10時点View : 8777 views

profile-image
スライド一覧

個人事業主/Love SQL/Tuning/DBエンジニア/JPOUG/Oracle ACE Pro/ex-AWS BigData Consultant/ex-DEC/Oracle,PostgreSQL,Aurora,Redshift,Athena,SCT,DMS,Glue/偶にNowPlaying♪垂れ流し/

シェア

またはPlayer版

埋め込む »CMSなどでJSが使えない場合

ダウンロード

ダウンロード(pdf - 52.3MB)

関連スライド

slide-thumbnail

E34：[JPOUG Presents] Oracle Database の隠されている様々な謎を解くセッション「なーんでだ？」再び @ db tech showcase 2015 Tokyo
user-img Hiroshi Sekiguchi 🍥 661
slide-thumbnail

db tech showcase Tokyo 2014 - L36 - JPOUG：SQLチューニング総合診療所　ケースファイルX
user-img Hiroshi Sekiguchi 🍥 499
slide-thumbnail

db tech showcase Tokyo 2013 - A35 特濃JPOUG：潮溜まりでジャブジャブ、SQLチューニング
user-img Hiroshi Sekiguchi 🍥 427
slide-thumbnail

SQLチューニング総合診療Oracle CloudWorld出張所
user-img Hiroshi Sekiguchi 🍥 101
slide-thumbnail

IT業界のサバイバビリティ - Oracle LOVERS 勉強会 #12 : 18-Jun-2009
user-img Hiroshi Sekiguchi 🍥 >100
slide-thumbnail

Oracle cloudworld な〜んでだ？#3
user-img Hiroshi Sekiguchi 🍥 >100
各ページのテキスト
1.

2.

2

3.

3

4.

4

5.

5

6.

5

7.

6

8.

✔ ✔ ✔ ✔ 7

9.

8

10.

9

11.

10

12.

10

13.

10

14.

11

15.

12

16.

12

17.

| Operation | Name | Mem | Temp | ================================================= | SELECT STATEMENT | | | | | HASH JOIN | | 4M | 1G | | PARTITION RANGE SINGLE | | | | | TABLE ACCESS FULL | FOO | | | | TABLE ACCESS FULL | BAR | | | 12

18.

| Operation | Name | Mem | Temp | ================================================= | SELECT STATEMENT | | | | | HASH JOIN | | 4M | 1G | | PARTITION RANGE SINGLE | | | | | TABLE ACCESS FULL | FOO | | | | TABLE ACCESS FULL | BAR | | | 12

19.

13

20.

13

21.

| Operation | Name | Mem | ========================================== | SELECT STATEMENT | | | | HASH JOIN | | 250M| | PARTITION RANGE SINGLE | | | | TABLE ACCESS FULL | FOO | | | TABLE ACCESS FULL | BAR | | 13

22.

| Operation | Name | Mem | ========================================== | SELECT STATEMENT | | | | HASH JOIN | | 250M| | PARTITION RANGE SINGLE | | | | TABLE ACCESS FULL | FOO | | | TABLE ACCESS FULL | BAR | | 13

23.

----------------------------------------------------------------------------| Id | Operation | Name | E-Rows | A-Rows | A-Time | Reads | ----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | 0 |00:00:01.05 | 20052 | | 1 | SORT ORDER BY | | 20 | 0 |00:00:01.05 | 20052 | |* 2 | TABLE ACCESS FULL| FOOBAR | 20 | 0 |00:00:01.05 | 20052 | ----------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------| Id | Operation | Name | E-Rows | A-Rows | A-Time | Reads | Writes | Used-Mem | Used-Tmp| ----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | 20000 |00:00:04.21 | 37325 | 17273 | | | | 1 | SORT ORDER BY | | 20000 | 20000 |00:00:04.21 | 37325 | 17273 | 71M (1)| 135K| |* 2 | TABLE ACCESS FULL| FOOBAR | 20000 | 20000 |00:00:01.25 | 20052 | 0 | | | ----------------------------------------------------------------------------------------------------------14

24.

----------------------------------------------------------------------------| Id | Operation | Name | E-Rows | A-Rows | A-Time | Reads | ----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | 0 |00:00:01.05 | 20052 | | 1 | SORT ORDER BY | | 20 | 0 |00:00:01.05 | 20052 | |* 2 | TABLE ACCESS FULL| FOOBAR | 20 | 0 |00:00:01.05 | 20052 | ----------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------| Id | Operation | Name | E-Rows | A-Rows | A-Time | Reads | Writes | Used-Mem | Used-Tmp| ----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | 20000 |00:00:04.21 | 37325 | 17273 | | | | 1 | SORT ORDER BY | | 20000 | 20000 |00:00:04.21 | 37325 | 17273 | 71M (1)| 135K| |* 2 | TABLE ACCESS FULL| FOOBAR | 20000 | 20000 |00:00:01.25 | 20052 | 0 | | | ----------------------------------------------------------------------------------------------------------14

25.

----------------------------------------------------------------------------| Id | Operation | Name | E-Rows | A-Rows | A-Time | Reads | ----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | 0 |00:00:01.05 | 20052 | | 1 | SORT ORDER BY | | 20 | 0 |00:00:01.05 | 20052 | |* 2 | TABLE ACCESS FULL| FOOBAR | 20 | 0 |00:00:01.05 | 20052 | ----------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------| Id | Operation | Name | E-Rows | A-Rows | A-Time | Reads | Writes | Used-Mem | Used-Tmp| ----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | 20000 |00:00:04.21 | 37325 | 17273 | | | | 1 | SORT ORDER BY | | 20000 | 20000 |00:00:04.21 | 37325 | 17273 | 71M (1)| 135K| |* 2 | TABLE ACCESS FULL| FOOBAR | 20000 | 20000 |00:00:01.25 | 20052 | 0 | | | ----------------------------------------------------------------------------------------------------------14

26.

----------------------------------------------------------------------------| Id | Operation | Name | E-Rows | A-Rows | A-Time | Reads | ----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | 0 |00:00:01.05 | 20052 | | 1 | SORT ORDER BY | | 20 | 0 |00:00:01.05 | 20052 | |* 2 | TABLE ACCESS FULL| FOOBAR | 20 | 0 |00:00:01.05 | 20052 | ----------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------| Id | Operation | Name | E-Rows | A-Rows | A-Time | Reads | Writes | Used-Mem | Used-Tmp| ----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | 20000 |00:00:04.21 | 37325 | 17273 | | | | 1 | SORT ORDER BY | | 20000 | 20000 |00:00:04.21 | 37325 | 17273 | 71M (1)| 135K| |* 2 | TABLE ACCESS FULL| FOOBAR | 20000 | 20000 |00:00:01.25 | 20052 | 0 | | | ----------------------------------------------------------------------------------------------------------14

27.

----------------------------------------------------------------------------| Id | Operation | Name | E-Rows | A-Rows | A-Time | Reads | ----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | 0 |00:00:01.05 | 20052 | | 1 | SORT ORDER BY | | 20 | 0 |00:00:01.05 | 20052 | |* 2 | TABLE ACCESS FULL| FOOBAR | 20 | 0 |00:00:01.05 | 20052 | ----------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------| Id | Operation | Name | E-Rows | A-Rows | A-Time | Reads | Writes | Used-Mem | Used-Tmp| ----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | 20000 |00:00:04.21 | 37325 | 17273 | | | | 1 | SORT ORDER BY | | 20000 | 20000 |00:00:04.21 | 37325 | 17273 | 71M (1)| 135K| |* 2 | TABLE ACCESS FULL| FOOBAR | 20000 | 20000 |00:00:01.25 | 20052 | 0 | | | ----------------------------------------------------------------------------------------------------------14

28.

----------------------------------------------------------------------------| Id | Operation | Name | E-Rows | A-Rows | A-Time | Reads | ----------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | 0 |00:00:01.05 | 20052 | | 1 | SORT ORDER BY | | 20 | 0 |00:00:01.05 | 20052 | |* 2 | TABLE ACCESS FULL| FOOBAR | 20 | 0 |00:00:01.05 | 20052 | ----------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------| Id | Operation | Name | E-Rows | A-Rows | A-Time | Reads | Writes | Used-Mem | Used-Tmp| ----------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | | 20000 |00:00:04.21 | 37325 | 17273 | | | | 1 | SORT ORDER BY | | 20000 | 20000 |00:00:04.21 | 37325 | 17273 | 71M (1)| 135K| |* 2 | TABLE ACCESS FULL| FOOBAR | 20000 | 20000 |00:00:01.25 | 20052 | 0 | | | ----------------------------------------------------------------------------------------------------------14

29.

========================================================================================================= | 0 | SELECT STATEMENT | | | 1 | | | | | 1 | HASH JOIN | | | 1 | | | | | 2 | TABLE ACCESS FULL | FOOBAR | 1 | 1 | 0 | 100.00 | direct path read (1) | | 3 | TABLE ACCESS FULL | HOGEHOGE | | | | | | ========================================================================================================= 15

30.

========================================================================================================= | 0 | SELECT STATEMENT | | | 1 | | | | | 1 | HASH JOIN | | | 1 | | | | | 2 | TABLE ACCESS FULL | FOOBAR | 1 | 1 | 0 | 100.00 | direct path read (1) | | 3 | TABLE ACCESS FULL | HOGEHOGE | | | | | | ========================================================================================================= 15

31.

========================================================================================================= | 0 | SELECT STATEMENT | | | 1 | | | | | 1 | HASH JOIN | | | 1 | | | | | 2 | TABLE ACCESS FULL | FOOBAR | 1 | 1 | 0 | 100.00 | direct path read (1) | | 3 | TABLE ACCESS FULL | HOGEHOGE | | | | | | ========================================================================================================= 15

32.

=============================================================================================================== | 0 | SELECT STATEMENT | | 2 | 1 | 0 | | | | 1 | HASH JOIN | | 3 | 1 | 0 | 100.00 | direct path write temp (3) | | 2 | TABLE ACCESS FULL | FOOBAR | 2 | 1 | 20000 | | | | 3 | TABLE ACCESS FULL | HOGEHOGE | | 1 | | | | =============================================================================================================== ========================================================================================================= | 0 | SELECT STATEMENT | | | 1 | | | | | 1 | HASH JOIN | | | 1 | | | | | 2 | TABLE ACCESS FULL | FOOBAR | 1 | 1 | 0 | 100.00 | direct path read (1) | | 3 | TABLE ACCESS FULL | HOGEHOGE | | | | | | ========================================================================================================= 15

33.

=============================================================================================================== | 0 | SELECT STATEMENT | | 2 | 1 | 0 | | | | 1 | HASH JOIN | | 3 | 1 | 0 | 100.00 | direct path write temp (3) | | 2 | TABLE ACCESS FULL | FOOBAR | 2 | 1 | 20000 | | | | 3 | TABLE ACCESS FULL | HOGEHOGE | | 1 | | | | =============================================================================================================== ========================================================================================================= | 0 | SELECT STATEMENT | | | 1 | | | | | 1 | HASH JOIN | | | 1 | | | | | 2 | TABLE ACCESS FULL | FOOBAR | 1 | 1 | 0 | 100.00 | direct path read (1) | | 3 | TABLE ACCESS FULL | HOGEHOGE | | | | | | ========================================================================================================= 15

34.

=============================================================================================================== | 0 | SELECT STATEMENT | | 2 | 1 | 0 | | | | 1 | HASH JOIN | | 3 | 1 | 0 | 100.00 | direct path write temp (3) | | 2 | TABLE ACCESS FULL | FOOBAR | 2 | 1 | 20000 | | | | 3 | TABLE ACCESS FULL | HOGEHOGE | | 1 | | | | =============================================================================================================== ========================================================================================================= | 0 | SELECT STATEMENT | | | 1 | | | | | 1 | HASH JOIN | | | 1 | | | | | 2 | TABLE ACCESS FULL | FOOBAR | 1 | 1 | 0 | 100.00 | direct path read (1) | | 3 | TABLE ACCESS FULL | HOGEHOGE | | | | | | ========================================================================================================= 15

35.

============================================================================================================== | Id | Operation | Name | Time | Execs | Rows | Activity | Activity Detail | | | | | Active(s) | | (Actual) | (%) | (# samples) | ============================================================================================================== | 0 | SELECT STATEMENT | | 229 | 1 | 400K | 25.00 | Cpu (2) | | 1 | HASH JOIN | | 229 | 1 | 400K | 50.00 | Cpu (3) | | | | | | | | | direct path read temp (1) | | 2 | TABLE ACCESS FULL | FOOBAR | 3 | 1 | 20000 | | | | 3 | TABLE ACCESS FULL | HOGEHOGE | 107 | 1 | 20000 | 12.50 | Cpu (1) | ============================================================================================================== =============================================================================================================== | 0 | SELECT STATEMENT | | 2 | 1 | 0 | | | | 1 | HASH JOIN | | 3 | 1 | 0 | 100.00 | direct path write temp (3) | | 2 | TABLE ACCESS FULL | FOOBAR | 2 | 1 | 20000 | | | | 3 | TABLE ACCESS FULL | HOGEHOGE | | 1 | | | | =============================================================================================================== ========================================================================================================= | 0 | SELECT STATEMENT | | | 1 | | | | | 1 | HASH JOIN | | | 1 | | | | | 2 | TABLE ACCESS FULL | FOOBAR | 1 | 1 | 0 | 100.00 | direct path read (1) | | 3 | TABLE ACCESS FULL | HOGEHOGE | | | | | | ========================================================================================================= 15

36.

============================================================================================================== | Id | Operation | Name | Time | Execs | Rows | Activity | Activity Detail | | | | | Active(s) | | (Actual) | (%) | (# samples) | ============================================================================================================== | 0 | SELECT STATEMENT | | 229 | 1 | 400K | 25.00 | Cpu (2) | | 1 | HASH JOIN | | 229 | 1 | 400K | 50.00 | Cpu (3) | | | | | | | | | direct path read temp (1) | | 2 | TABLE ACCESS FULL | FOOBAR | 3 | 1 | 20000 | | | | 3 | TABLE ACCESS FULL | HOGEHOGE | 107 | 1 | 20000 | 12.50 | Cpu (1) | ============================================================================================================== =============================================================================================================== | 0 | SELECT STATEMENT | | 2 | 1 | 0 | | | | 1 | HASH JOIN | | 3 | 1 | 0 | 100.00 | direct path write temp (3) | | 2 | TABLE ACCESS FULL | FOOBAR | 2 | 1 | 20000 | | | | 3 | TABLE ACCESS FULL | HOGEHOGE | | 1 | | | | =============================================================================================================== ========================================================================================================= | 0 | SELECT STATEMENT | | | 1 | | | | | 1 | HASH JOIN | | | 1 | | | | | 2 | TABLE ACCESS FULL | FOOBAR | 1 | 1 | 0 | 100.00 | direct path read (1) | | 3 | TABLE ACCESS FULL | HOGEHOGE | | | | | | ========================================================================================================= 15

37.

============================================================================================================== | Id | Operation | Name | Time | Execs | Rows | Activity | Activity Detail | | | | | Active(s) | | (Actual) | (%) | (# samples) | ============================================================================================================== | 0 | SELECT STATEMENT | | 229 | 1 | 400K | 25.00 | Cpu (2) | | 1 | HASH JOIN | | 229 | 1 | 400K | 50.00 | Cpu (3) | | | | | | | | | direct path read temp (1) | | 2 | TABLE ACCESS FULL | FOOBAR | 3 | 1 | 20000 | | | | 3 | TABLE ACCESS FULL | HOGEHOGE | 107 | 1 | 20000 | 12.50 | Cpu (1) | ============================================================================================================== =============================================================================================================== | 0 | SELECT STATEMENT | | 2 | 1 | 0 | | | | 1 | HASH JOIN | | 3 | 1 | 0 | 100.00 | direct path write temp (3) | | 2 | TABLE ACCESS FULL | FOOBAR | 2 | 1 | 20000 | | | | 3 | TABLE ACCESS FULL | HOGEHOGE | | 1 | | | | =============================================================================================================== ========================================================================================================= | 0 | SELECT STATEMENT | | | 1 | | | | | 1 | HASH JOIN | | | 1 | | | | | 2 | TABLE ACCESS FULL | FOOBAR | 1 | 1 | 0 | 100.00 | direct path read (1) | | 3 | TABLE ACCESS FULL | HOGEHOGE | | | | | | ========================================================================================================= 15

38.

ID COUNT(1) ---------- ---------1 2000 25 20 22 20 34 20 30 20 940 912 930 885 906 925 20 20 20 20 20 20 ID COUNT(1) ---------- ---------1 20000 25 20 22 20 34 20 30 20 940 912 930 885 906 925 ID COUNT(1) ---------- ---------1 2 25 20 22 20 34 20 30 20 20 20 20 20 20 20 940 912 930 885 906 925 16 20 20 20 20 20 20 ID COUNT(1) ---------- ---------1 2 25 20 22 20 34 20 30 20 940 912 930 885 906 925 20 20 20 20 20 20

39.

ID COUNT(1) ---------- ---------1 2000 25 20 22 20 34 20 30 20 940 912 930 885 906 925 20 20 20 20 20 20 ID COUNT(1) ---------- ---------1 20000 25 20 22 20 34 20 30 20 940 912 930 885 906 925 ID COUNT(1) ---------- ---------1 2 25 20 22 20 34 20 30 20 20 20 20 20 20 20 940 912 930 885 906 925 16 20 20 20 20 20 20 ID COUNT(1) ---------- ---------1 2 25 20 22 20 34 20 30 20 940 912 930 885 906 925 20 20 20 20 20 20

40.

ID COUNT(1) ---------- ---------1 2000 25 20 22 20 34 20 30 20 940 912 930 885 906 925 20 20 20 20 20 20 ID COUNT(1) ---------- ---------1 20000 25 20 22 20 34 20 30 20 940 912 930 885 906 925 ID COUNT(1) ---------- ---------1 2 25 20 22 20 34 20 30 20 20 20 20 20 20 20 940 912 930 885 906 925 16 20 20 20 20 20 20 ID COUNT(1) ---------- ---------1 2 25 20 22 20 34 20 30 20 940 912 930 885 906 925 20 20 20 20 20 20

41.

ID COUNT(1) ---------- ---------1 2000 25 20 22 20 34 20 30 20 940 912 930 885 906 925 20 20 20 20 20 20 ID COUNT(1) ---------- ---------1 20000 25 20 22 20 34 20 30 20 940 912 930 885 906 925 ID COUNT(1) ---------- ---------1 2 25 20 22 20 34 20 30 20 20 20 20 20 20 20 940 912 930 885 906 925 16 20 20 20 20 20 20 ID COUNT(1) ---------- ---------1 2 25 20 22 20 34 20 30 20 940 912 930 885 906 925 20 20 20 20 20 20

42.

=================================================================================================================== | Id | Operation | Name | Rows | Time | Rows | Mem | Activity | Activity Detail | | | | | (Estim) | Active(s) | (Actual) | (Max) | (%) | (# samples) | =================================================================================================================== | 0 | SELECT STATEMENT | | | 225 | 400K | | 75.00 | Cpu (3) | | 1 | HASH JOIN | | 401K | 225 | 400K | 17M | | | | 2 | TABLE ACCESS FULL | FOOBAR | 2000 | 1 | 2000 | | | | | 3 | TABLE ACCESS FULL | HOGEHOGE | 20291 | 225 | 20000 | | 25.00 | direct path read (1) | =================================================================================================================== =================================================================================================================== | 0 | SELECT STATEMENT | | | 1 | 4 | | | | | 1 | HASH JOIN | | 23 | 1 | 4 | 458K | | | | 2 | TABLE ACCESS FULL | FOOBAR | 20 | 1 | 2 | | | | | 3 | TABLE ACCESS FULL | HOGEHOGE | 313 | 1 | 2 | | 100.00 | direct path read (1) | =================================================================================================================== 17

43.

=================================================================================================================== | Id | Operation | Name | Rows | Time | Rows | Mem | Activity | Activity Detail | | | | | (Estim) | Active(s) | (Actual) | (Max) | (%) | (# samples) | =================================================================================================================== | 0 | SELECT STATEMENT | | | 225 | 400K | | 75.00 | Cpu (3) | | 1 | HASH JOIN | | 401K | 225 | 400K | 17M | | | | 2 | TABLE ACCESS FULL | FOOBAR | 2000 | 1 | 2000 | | | | | 3 | TABLE ACCESS FULL | HOGEHOGE | 20291 | 225 | 20000 | | 25.00 | direct path read (1) | =================================================================================================================== =================================================================================================================== | 0 | SELECT STATEMENT | | | 1 | 4 | | | | | 1 | HASH JOIN | | 23 | 1 | 4 | 458K | | | | 2 | TABLE ACCESS FULL | FOOBAR | 20 | 1 | 2 | | | | | 3 | TABLE ACCESS FULL | HOGEHOGE | 313 | 1 | 2 | | 100.00 | direct path read (1) | =================================================================================================================== 17

44.

=================================================================================================================== | Id | Operation | Name | Rows | Time | Rows | Mem | Activity | Activity Detail | | | | | (Estim) | Active(s) | (Actual) | (Max) | (%) | (# samples) | =================================================================================================================== | 0 | SELECT STATEMENT | | | 225 | 400K | | 75.00 | Cpu (3) | | 1 | HASH JOIN | | 401K | 225 | 400K | 17M | | | | 2 | TABLE ACCESS FULL | FOOBAR | 2000 | 1 | 2000 | | | | | 3 | TABLE ACCESS FULL | HOGEHOGE | 20291 | 225 | 20000 | | 25.00 | direct path read (1) | =================================================================================================================== =================================================================================================================== | 0 | SELECT STATEMENT | | | 1 | 4 | | | | | 1 | HASH JOIN | | 23 | 1 | 4 | 458K | | | | 2 | TABLE ACCESS FULL | FOOBAR | 20 | 1 | 2 | | | | | 3 | TABLE ACCESS FULL | HOGEHOGE | 313 | 1 | 2 | | 100.00 | direct path read (1) | =================================================================================================================== 17

45.

18

46.

ID ---------2 4 5 3 1 8 9 10 11 12 COL1 -----------------------------****************************** ****************************** ****************************** ****************************** ****************************** ****************************** ****************************** ****************************** ****************************** ****************************** COL2 -------------************** ************** ************** ************** ************** ************** ************** ************** ************** ************** 18

47.

ID ---------2 4 5 3 1 8 9 10 11 12 COL1 -----------------------------****************************** ****************************** ****************************** ****************************** ****************************** ****************************** ****************************** ****************************** ****************************** ****************************** COL2 -------------************** ************** ************** ************** ************** ************** ************** ************** ************** ************** 18

48.

ID ---------2 4 5 3 1 8 9 10 11 12 COL1 -----------------------------****************************** ****************************** ****************************** ****************************** ****************************** ****************************** ****************************** ****************************** ****************************** ****************************** COL2 -------------************** ************** ************** ************** ************** ************** ************** ************** ************** ************** 18

49.

SEGMENT_NAME SUM(BLOCKS) MB ------------- ----------- ---------SEGMENTSIZE 1024 8 TABLE_NAME ------------SEGMENTSIZE SEGMENTSIZE SEGMENTSIZE COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY AVG_COL_LEN NUM_BUCKETS HISTOGRAM ------------ --------- ------------ ---------- ----------- ----------- ---------ID 0 1000 .001 4 1 NONE DATA1 0 1 1 4001 1 NONE DATA2 0 1 1 3001 1 NONE 19

50.

SEGMENT_NAME SUM(BLOCKS) MB ------------- ----------- ---------SEGMENTSIZE 1024 8 TABLE_NAME ------------SEGMENTSIZE SEGMENTSIZE SEGMENTSIZE COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY AVG_COL_LEN NUM_BUCKETS HISTOGRAM ------------ --------- ------------ ---------- ----------- ----------- ---------ID 0 1000 .001 4 1 NONE DATA1 0 1 1 4001 1 NONE DATA2 0 1 1 3001 1 NONE 19

51.

SEGMENT_NAME SUM(BLOCKS) MB ------------- ----------- ---------SEGMENTSIZE 1024 8 TABLE_NAME ------------SEGMENTSIZE SEGMENTSIZE SEGMENTSIZE COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY AVG_COL_LEN NUM_BUCKETS HISTOGRAM ------------ --------- ------------ ---------- ----------- ----------- ---------ID 0 1000 .001 4 1 NONE DATA1 0 1 1 4001 1 NONE DATA2 0 1 1 3001 1 NONE 19

52.

SEGMENT_NAME SUM(BLOCKS) MB ------------- ----------- ---------SEGMENTSIZE 1024 8 TABLE_NAME ------------SEGMENTSIZE SEGMENTSIZE SEGMENTSIZE COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY AVG_COL_LEN NUM_BUCKETS HISTOGRAM ------------ --------- ------------ ---------- ----------- ----------- ---------ID 0 1000 .001 4 1 NONE DATA1 0 1 1 4001 1 NONE DATA2 0 1 1 3001 1 NONE SEGMENT_NAME SUM(BLOCKS) MB ----------------- ----------- ---------SMALL_SEGMENTSIZE 8 .0625 TABLE_NAME ----------------SMALL_SEGMENTSIZE SMALL_SEGMENTSIZE SMALL_SEGMENTSIZE COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY AVG_COL_LEN NUM_BUCKETS HISTOGRAM ------------ --------- ------------ ---------- ----------- ----------- ---------ID 0 1000 .001 4 1 NONE DATA1 0 1 1 3 1 NONE DATA2 0 1 1 6 1 NONE 19

53.

SEGMENT_NAME SUM(BLOCKS) MB ------------- ----------- ---------SEGMENTSIZE 1024 8 TABLE_NAME ------------SEGMENTSIZE SEGMENTSIZE SEGMENTSIZE COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY AVG_COL_LEN NUM_BUCKETS HISTOGRAM ------------ --------- ------------ ---------- ----------- ----------- ---------ID 0 1000 .001 4 1 NONE DATA1 0 1 1 4001 1 NONE DATA2 0 1 1 3001 1 NONE SEGMENT_NAME SUM(BLOCKS) MB ----------------- ----------- ---------SMALL_SEGMENTSIZE 8 .0625 TABLE_NAME ----------------SMALL_SEGMENTSIZE SMALL_SEGMENTSIZE SMALL_SEGMENTSIZE COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY AVG_COL_LEN NUM_BUCKETS HISTOGRAM ------------ --------- ------------ ---------- ----------- ----------- ---------ID 0 1000 .001 4 1 NONE DATA1 0 1 1 3 1 NONE DATA2 0 1 1 6 1 NONE 19

54.

SEGMENT_NAME SUM(BLOCKS) MB ------------- ----------- ---------SEGMENTSIZE 1024 8 TABLE_NAME ------------SEGMENTSIZE SEGMENTSIZE SEGMENTSIZE COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY AVG_COL_LEN NUM_BUCKETS HISTOGRAM ------------ --------- ------------ ---------- ----------- ----------- ---------ID 0 1000 .001 4 1 NONE DATA1 0 1 1 4001 1 NONE DATA2 0 1 1 3001 1 NONE SEGMENT_NAME SUM(BLOCKS) MB ----------------- ----------- ---------SMALL_SEGMENTSIZE 8 .0625 TABLE_NAME ----------------SMALL_SEGMENTSIZE SMALL_SEGMENTSIZE SMALL_SEGMENTSIZE COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY AVG_COL_LEN NUM_BUCKETS HISTOGRAM ------------ --------- ------------ ---------- ----------- ----------- ---------ID 0 1000 .001 4 1 NONE DATA1 0 1 1 3 1 NONE DATA2 0 1 1 6 1 NONE 19

55.

SEGMENT_NAME SUM(BLOCKS) MB ------------- ----------- ---------SEGMENTSIZE 1024 8 TABLE_NAME ------------SEGMENTSIZE SEGMENTSIZE SEGMENTSIZE COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY AVG_COL_LEN NUM_BUCKETS HISTOGRAM ------------ --------- ------------ ---------- ----------- ----------- ---------ID 0 1000 .001 4 1 NONE DATA1 0 1 1 4001 1 NONE DATA2 0 1 1 3001 1 NONE SEGMENT_NAME SUM(BLOCKS) MB ----------------- ----------- ---------SMALL_SEGMENTSIZE 8 .0625 TABLE_NAME ----------------SMALL_SEGMENTSIZE SMALL_SEGMENTSIZE SMALL_SEGMENTSIZE COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY AVG_COL_LEN NUM_BUCKETS HISTOGRAM ------------ --------- ------------ ---------- ----------- ----------- ---------ID 0 1000 .001 4 1 NONE DATA1 0 1 1 3 1 NONE DATA2 0 1 1 6 1 NONE 19

56.

SEGMENT_NAME SUM(BLOCKS) MB ------------- ----------- ---------SEGMENTSIZE 1024 8 TABLE_NAME ------------SEGMENTSIZE SEGMENTSIZE SEGMENTSIZE COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY AVG_COL_LEN NUM_BUCKETS HISTOGRAM ------------ --------- ------------ ---------- ----------- ----------- ---------ID 0 1000 .001 4 1 NONE DATA1 0 1 1 4001 1 NONE DATA2 0 1 1 3001 1 NONE SEGMENT_NAME SUM(BLOCKS) MB ----------------- ----------- ---------SMALL_SEGMENTSIZE 8 .0625 TABLE_NAME ----------------SMALL_SEGMENTSIZE SMALL_SEGMENTSIZE SMALL_SEGMENTSIZE COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY AVG_COL_LEN NUM_BUCKETS HISTOGRAM ------------ --------- ------------ ---------- ----------- ----------- ---------ID 0 1000 .001 4 1 NONE DATA1 0 1 1 3 1 NONE DATA2 0 1 1 6 1 NONE 19

57.

SEGMENT_NAME SUM(BLOCKS) MB ------------- ----------- ---------SEGMENTSIZE 1024 8 TABLE_NAME ------------SEGMENTSIZE SEGMENTSIZE SEGMENTSIZE COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY AVG_COL_LEN NUM_BUCKETS HISTOGRAM ------------ --------- ------------ ---------- ----------- ----------- ---------ID 0 1000 .001 4 1 NONE DATA1 0 1 1 4001 1 NONE DATA2 0 1 1 3001 1 NONE SEGMENT_NAME SUM(BLOCKS) MB ----------------- ----------- ---------SMALL_SEGMENTSIZE 8 .0625 TABLE_NAME ----------------SMALL_SEGMENTSIZE SMALL_SEGMENTSIZE SMALL_SEGMENTSIZE COLUMN_NAME NUM_NULLS NUM_DISTINCT DENSITY AVG_COL_LEN NUM_BUCKETS HISTOGRAM ------------ --------- ------------ ---------- ----------- ----------- ---------ID 0 1000 .001 4 1 NONE DATA1 0 1 1 3 1 NONE DATA2 0 1 1 6 1 NONE 19

58.

============================================================================== | Id | Operation | Name | Rows | Rows | Read | Read | | | | | (Estim) | (Actual) | Reqs | Bytes | ============================================================================== | 0 | SELECT STATEMENT | | | 1000 | | | | 1 | TABLE ACCESS FULL | SEGMENTSIZE | 1000 | 1000 | 37 | 8MB | ============================================================================== ==================================================================================== | Id | Operation | Name | Rows | Rows | Read | Read | | | | | (Estim) | (Actual) | Reqs | Bytes | ==================================================================================== | 0 | SELECT STATEMENT | | | 1000 | | | | 1 | TABLE ACCESS FULL | SMALL_SEGMENTSIZE | 1000 | 1000 | 2 | 32768 | ==================================================================================== 20

59.

21

60.

Plan hash value: 3426918692 ----------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | ----------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | |* 1 | FILTER | | 1 | | | 2 | TABLE ACCESS BY INDEX ROWID| FOO | 1 | 50 | |* 3 | INDEX RANGE SCAN | PK_FOO | 1 | 50 | ----------------------------------------------------------------Plan hash value: 2962421549 -------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| -------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | | 1 | SORT ORDER BY | | 1 | 5000 | 2485K| |* 2 | FILTER | | 1 | | | |* 3 | TABLE ACCESS FULL| FOO | 1 | 5000 | 2485K| -------------------------------------------------------------22

61.

✋ Plan hash value: 3426918692 ----------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | ----------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | |* 1 | FILTER | | 1 | | | 2 | TABLE ACCESS BY INDEX ROWID| FOO | 1 | 50 | |* 3 | INDEX RANGE SCAN | PK_FOO | 1 | 50 | ----------------------------------------------------------------Plan hash value: 2962421549 -------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| -------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | | 1 | SORT ORDER BY | | 1 | 5000 | 2485K| |* 2 | FILTER | | 1 | | | |* 3 | TABLE ACCESS FULL| FOO | 1 | 5000 | 2485K| -------------------------------------------------------------23

62.

✋ Plan hash value: 3426918692 ----------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | ----------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | |* 1 | FILTER | | 1 | | | 2 | TABLE ACCESS BY INDEX ROWID| FOO | 1 | 50 | |* 3 | INDEX RANGE SCAN | PK_FOO | 1 | 50 | ----------------------------------------------------------------Plan hash value: 2962421549 -------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| -------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | | 1 | SORT ORDER BY | | 1 | 5000 | 2485K| |* 2 | FILTER | | 1 | | | |* 3 | TABLE ACCESS FULL| FOO | 1 | 5000 | 2485K| -------------------------------------------------------------24

63.

✋ Plan hash value: 3426918692 ----------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | ----------------------------------------------------------------| 0 | SELECT UNIQUENES STATEMENT | | 1 | | INDEX_NAME DISTINCT_KEYS CLUSTERING_FACTOR |* 1 | FILTER--------- ------------| | 1 | | -------------------------| 2 | TABLE ACCESS BY INDEX ROWID| FOO | 1 | 50 | PK_FOO UNIQUE 20000 19993 |* 3 | INDEX RANGE SCAN | PK_FOO | 1 | 50 | ----------------------------------------------------------------Plan hash value: 2962421549 可能性が高い -------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| -------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | | 1 | SORT ORDER BY | | 1 | 5000 | 2485K| |* 2 | FILTER | | 1 | | | |* 3 | TABLE ACCESS FULL| FOO | 1 | 5000 | 2485K| -------------------------------------------------------------24

64.

Plan hash value: 3426918692 ----------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | ----------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | |* 1 | FILTER | | 1 | | | 2 | TABLE ACCESS BY INDEX ROWID| FOO | 1 | 50 | |* 3 | INDEX RANGE SCAN | PK_FOO | 1 | 50 | ----------------------------------------------------------------Plan hash value: 2962421549 -------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| -------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | | 1 | SORT ORDER BY | | 1 | 5000 | 2485K| |* 2 | FILTER | | 1 | | | |* 3 | TABLE ACCESS FULL| FOO | 1 | 5000 | 2485K| -------------------------------------------------------------25

65.

Plan hash value: 3426918692 ----------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | ----------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | |* 1 | FILTER | | 1 | | | 2 | TABLE ACCESS BY INDEX ROWID| FOO | 1 | 50 | |* 3 | INDEX RANGE SCAN | PK_FOO | 1 | 50 | ----------------------------------------------------------------Plan hash value: 2962421549 -------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| -------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | | 1 | SORT ORDER BY | | 1 | 5000 | 2485K| |* 2 | FILTER | | 1 | | | |* 3 | TABLE ACCESS FULL| FOO | 1 | 5000 | 2485K| -------------------------------------------------------------25

66.

Plan hash value: 3426918692 ----------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | ----------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | |* 1 | FILTER | | 1 | | | 2 | TABLE ACCESS BY INDEX ROWID| FOO | 1 | 50 | |* 3 | INDEX RANGE SCAN | PK_FOO | 1 | 50 | ----------------------------------------------------------------Plan hash value: 2962421549 -------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| -------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | | 1 | SORT ORDER BY | | 1 | 5000 | 2485K| |* 2 | FILTER | | 1 | | | |* 3 | TABLE ACCESS FULL| FOO | 1 | 5000 | 2485K| -------------------------------------------------------------25

67.

Plan hash value: 3426918692 ----------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | ----------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | |* 1 | FILTER | | 1 | | | 2 | TABLE ACCESS BY INDEX ROWID| FOO | 1 | 50 | |* 3 | INDEX RANGE SCAN | PK_FOO | 1 | 50 | ----------------------------------------------------------------Plan hash value: 2962421549 -------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| -------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | | 1 | SORT ORDER BY | | 1 | 5000 | 2485K| |* 2 | FILTER | | 1 | | | |* 3 | TABLE ACCESS FULL| FOO | 1 | 5000 | 2485K| -------------------------------------------------------------26

68.

Plan hash value: 3426918692 ----------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | ----------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | |* 1 | FILTER | | 1 | | | 2 | TABLE ACCESS BY INDEX ROWID| FOO | 1 | 50 | |* 3 | INDEX RANGE SCAN | PK_FOO | 1 | 50 | ----------------------------------------------------------------Plan hash value: 2962421549 -------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| -------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | | 1 | SORT ORDER BY | | 1 | 5000 | 2485K| |* 2 | FILTER | | 1 | | | |* 3 | TABLE ACCESS FULL| FOO | 1 | 5000 | 2485K| -------------------------------------------------------------26

69.

Plan hash value: 3426918692 ----------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | ----------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | |* 1 | FILTER | | 1 | | | 2 | TABLE ACCESS BY INDEX ROWID| FOO | 1 | 50 | |* 3 | INDEX RANGE SCAN | PK_FOO | 1 | 50 | ----------------------------------------------------------------Plan hash value: 2962421549 -------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| -------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | | 1 | SORT ORDER BY | | 1 | 5000 | 2485K| |* 2 | FILTER | | 1 | | | |* 3 | TABLE ACCESS FULL| FOO | 1 | 5000 | 2485K| -------------------------------------------------------------26

70.

Plan hash value: 3426918692 ----------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows | ----------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | |* 1 | FILTER | | 1 | | | 2 | TABLE ACCESS BY INDEX ROWID| FOO | 1 | 50 | |* 3 | INDEX RANGE SCAN | PK_FOO | 1 | 50 | ----------------------------------------------------------------Plan hash value: 2962421549 -------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| -------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | | 1 | SORT ORDER BY | | 1 | 5000 | 2485K| |* 2 | FILTER | | 1 | | | |* 3 | TABLE ACCESS FULL| FOO | 1 | 5000 | 2485K| -------------------------------------------------------------27

71.

✋ 28

72.

INDEX_NAME CLUSTERING_FACTOR NUM_OF_TABLE_BLOCKS NUM_OF_TABLE_ROWS ----------- ----------------- ------------------- ----------------PK_HICLS 992 80 1000 PK_LOCLS 72 80 1000 29

73.

INDEX_NAME CLUSTERING_FACTOR NUM_OF_TABLE_BLOCKS NUM_OF_TABLE_ROWS ----------- ----------------- ------------------- ----------------PK_HICLS 992 80 1000 PK_LOCLS 72 80 1000 29

74.

INDEX_NAME CLUSTERING_FACTOR NUM_OF_TABLE_BLOCKS NUM_OF_TABLE_ROWS ----------- ----------------- ------------------- ----------------PK_HICLS 992 80 1000 PK_LOCLS 72 80 1000 29

75.

30

76.

Plan hash value: 2962421549 -------------------------------------------------------------hash value: 2962421549 | Id Plan | Operation | Name | Starts | E-Rows |E-Bytes| --------------------------------------------------------------------------------------------------------------------------| Operation | Name | Starts | E-Rows |E-Bytes| | 0| |IdSELECT STATEMENT | | 1 | | | -------------------------------------------------------------| 1 | SORT ORDER BY | | 1 | 5000 | 2485K| | 0 | SELECT STATEMENT | | 1 | | | |* 2 | FILTER | | 1 | | | SORTACCESS ORDER FULL| BY | 1 | 1 5000 | 5000 | 2485K| |* 3| | 1 |TABLE FOO| | | 2485K| |* 2 | FILTER | | 1 | | | -------------------------------------------------------------|* 3 | TABLE ACCESS FULL| FOO | 1 | 5000 | 2485K| -------------------------------------------------------------- 31

77.

32

78.

Plan Hash # Value Executions --- ---------------- -------------1 1885085827 2000 2 1756993664 2000 3 3400946141 2000 4 13177233 2000 5 3564047035 2000 33

79.

Plan Hash # Value Executions --- ---------------- -------------1 1885085827 2000 2 1756993664 2000 3 3400946141 2000 4 13177233 2000 5 3564047035 2000 33

80.

34

81.

|MERGE JOIN | | | TABLE ACCESS BY INDEX ROWID| FOO | | INDEX RANGE SCAN | PK_FOO | | SORT JOIN | | | TABLE ACCESS FULL | BAR | |NESTED LOOPS | | NESTED LOOPS | | TABLE ACCESS FULL | FOO | INDEX RANGE SCAN | PK_BAR | TABLE ACCESS BY INDEX ROWID| BAR |HASH JOIN | | TABLE ACCESS FULL| FOO | TABLE ACCESS FULL| BAR | | | | | |HASH JOIN | TABLE ACCESS | INDEX RANGE | TABLE ACCESS | INDEX RANGE |NESTED LOOPS | | | NESTED LOOPS | | | TABLE ACCESS BY INDEX ROWID BATCHED| FOO | | INDEX RANGE SCAN | PK_FOO | | INDEX RANGE SCAN | PK_BAR | | TABLE ACCESS BY INDEX ROWID | BAR | 34 | | | | BY INDEX ROWID BATCHED| SCAN | BY INDEX ROWID BATCHED| SCAN | FOO PK_FOO BAR PK_BAR

82.

Plan hash value: 2962421549 -------------------------------------------------------------hash value: 2962421549 | Id Plan | Operation | Name | Starts | E-Rows |E-Bytes| --------------------------------------------------------------------------------------------------------------------------| Operation | Name | Starts | E-Rows |E-Bytes| | 0| |IdSELECT STATEMENT | | 1 | | | -------------------------------------------------------------| 1 | SORT ORDER BY | | 1 | 5000 | 2485K| | 0 | SELECT STATEMENT | | 1 | | | |* 2 | FILTER | | 1 | | | SORTACCESS ORDER FULL| BY | 1 | 1 5000 | 5000 | 2485K| |* 3| | 1 |TABLE FOO| | | 2485K| |* 2 | FILTER | | 1 | | | -------------------------------------------------------------|* 3 | TABLE ACCESS FULL| FOO | 1 | 5000 | 2485K| -------------------------------------------------------------- 35

83.

Plan hash value: 2962421549 -------------------------------------------------------------hash value: 2962421549 | Id Plan | Operation | Name | Starts | E-Rows |E-Bytes| --------------------------------------------------------------------------------------------------------------------------| Operation | Name | Starts | E-Rows |E-Bytes| | 0| |IdSELECT STATEMENT | | 1 | | | -------------------------------------------------------------| 1 | SORT ORDER BY | | 1 | 5000 | 2485K| | 0 | SELECT STATEMENT | | 1 | | | |* 2 | FILTER | | 1 | | | SORTACCESS ORDER FULL| BY | 1 | 1 5000 | 5000 | 2485K| |* 3| | 1 |TABLE FOO| | | 2485K| |* 2 | FILTER | | 1 | | | -------------------------------------------------------------|* 3 | TABLE ACCESS FULL| FOO | 1 | 5000 | 2485K| -------------------------------------------------------------- 35

84.

:v1 = 1 :v2 = 2120 :v3 = 2000 :v1 = 200 :v2 = 2300 :v3 = 2000 :v1 = 50 :v2 = 300 :v3 = 2000 :v1 = 15000 :v2 = 17400 :v3 = 2000 :v1 = x :v2 = x+300 :v3 = 2000 |HASH JOIN | | TABLE ACCESS FULL| FOO | TABLE ACCESS FULL| BAR |HASH JOIN | TABLE ACCESS | INDEX RANGE | TABLE ACCESS | INDEX RANGE | | | | BY INDEX ROWID BATCHED| SCAN | BY INDEX ROWID BATCHED| SCAN | FOO PK_FOO BAR PK_BAR |NESTED LOOPS | | | NESTED LOOPS | | | TABLE ACCESS BY INDEX ROWID BATCHED| FOO | | INDEX RANGE SCAN | PK_FOO | | INDEX RANGE SCAN | PK_BAR | | TABLE ACCESS BY INDEX ROWID | BAR | |NESTED LOOPS | | NESTED LOOPS | | TABLE ACCESS FULL | FOO | INDEX RANGE SCAN | PK_BAR | TABLE ACCESS BY INDEX ROWID| BAR | | | | | |MERGE JOIN | | | TABLE ACCESS BY INDEX ROWID| FOO | | INDEX RANGE SCAN | PK_FOO | | SORT JOIN | | | TABLE ACCESS FULL | BAR | 36

85.

:v1 = 1 :v2 = 2120 :v3 = 2000 :v1 = 200 :v2 = 2300 :v3 = 2000 :v1 = 50 :v2 = 300 :v3 = 2000 :v1 = 15000 :v2 = 17400 :v3 = 2000 :v1 = x :v2 = x+300 :v3 = 2000 |HASH JOIN | | TABLE ACCESS FULL| FOO | TABLE ACCESS FULL| BAR |HASH JOIN | TABLE ACCESS | INDEX RANGE | TABLE ACCESS | INDEX RANGE | | | | BY INDEX ROWID BATCHED| SCAN | BY INDEX ROWID BATCHED| SCAN | FOO PK_FOO BAR PK_BAR |NESTED LOOPS | | | NESTED LOOPS | | | TABLE ACCESS BY INDEX ROWID BATCHED| FOO | | INDEX RANGE SCAN | PK_FOO | | INDEX RANGE SCAN | PK_BAR | | TABLE ACCESS BY INDEX ROWID | BAR | |NESTED LOOPS | | NESTED LOOPS | | TABLE ACCESS FULL | FOO | INDEX RANGE SCAN | PK_BAR | TABLE ACCESS BY INDEX ROWID| BAR | | | | | |MERGE JOIN | | | TABLE ACCESS BY INDEX ROWID| FOO | | INDEX RANGE SCAN | PK_FOO | | SORT JOIN | | | TABLE ACCESS FULL | BAR | 36

86.

:v1 = 1 :v2 = 2120 :v3 = 2000 :v1 = 200 :v2 = 2300 :v3 = 2000 :v1 = 50 :v2 = 300 :v3 = 2000 :v1 = 15000 :v2 = 17400 :v3 = 2000 :v1 = x :v2 = x+300 :v3 = 2000 |HASH JOIN | | TABLE ACCESS FULL| FOO | TABLE ACCESS FULL| BAR |HASH JOIN | TABLE ACCESS | INDEX RANGE | TABLE ACCESS | INDEX RANGE | | | | BY INDEX ROWID BATCHED| SCAN | BY INDEX ROWID BATCHED| SCAN | FOO PK_FOO BAR PK_BAR |NESTED LOOPS | | | NESTED LOOPS | | | TABLE ACCESS BY INDEX ROWID BATCHED| FOO | | INDEX RANGE SCAN | PK_FOO | | INDEX RANGE SCAN | PK_BAR | | TABLE ACCESS BY INDEX ROWID | BAR | |NESTED LOOPS | | NESTED LOOPS | | TABLE ACCESS FULL | FOO | INDEX RANGE SCAN | PK_BAR | TABLE ACCESS BY INDEX ROWID| BAR | | | | | |MERGE JOIN | | | TABLE ACCESS BY INDEX ROWID| FOO | | INDEX RANGE SCAN | PK_FOO | | SORT JOIN | | | TABLE ACCESS FULL | BAR | 36

87.

:v1 = 1 :v2 = 2120 :v3 = 2000 :v1 = 200 :v2 = 2300 :v3 = 2000 :v1 = 50 :v2 = 300 :v3 = 2000 |HASH JOIN | | TABLE ACCESS FULL| FOO | TABLE ACCESS FULL| BAR |HASH JOIN | TABLE ACCESS | INDEX RANGE | TABLE ACCESS | INDEX RANGE | | | | BY INDEX ROWID BATCHED| SCAN | BY INDEX ROWID BATCHED| SCAN | FOO PK_FOO BAR PK_BAR :v1 = 15000 :v2 = 17400 :v3 = 2000 |NESTED LOOPS | | | NESTED LOOPS | | | TABLE ACCESS BY INDEX ROWID BATCHED| FOO | | INDEX RANGE SCAN | PK_FOO | | INDEX RANGE SCAN | PK_BAR | | TABLE ACCESS BY INDEX ROWID | BAR | :v1 = x :v2 = x+300 :v3 = 2000 |NESTED LOOPS | | NESTED LOOPS | | TABLE ACCESS FULL | FOO | INDEX RANGE SCAN | PK_BAR | TABLE ACCESS BY INDEX ROWID| BAR | | | | | |MERGE JOIN | | | TABLE ACCESS BY INDEX ROWID| FOO | | INDEX RANGE SCAN | PK_FOO | | SORT JOIN | | | TABLE ACCESS FULL | BAR | 37

88.

Plan Hash # Value Executions --- ---------------- ------------1 1756993664 2000 2 3400946141 2000 3 1885085827 2000 4 13177233 2000 5 3564047035 2000 Plan Hash # Value Executions --- ---------------- ------------1 3564047035 10,000 2 13177233 0 1 1 2 3 4 5 1756993664 3564047035 13177233 3400946141 1885085827 1 13177233 2004 5991 2000 4 1 10,000 38 3564047035 10,000

89.

Plan Hash # Value Executions --- ---------------- ------------1 1756993664 2000 2 3400946141 2000 3 1885085827 2000 4 13177233 2000 5 3564047035 2000 Plan Hash # Value Executions --- ---------------- ------------1 3564047035 10,000 2 13177233 0 1 1 2 3 4 5 1756993664 3564047035 13177233 3400946141 1885085827 1 13177233 2004 5991 2000 4 1 10,000 38 3564047035 10,000

90.

Plan Hash # Value Executions --- ---------------- ------------1 1756993664 2000 2 3400946141 2000 3 1885085827 2000 4 13177233 2000 5 3564047035 2000 Plan Hash # Value Executions --- ---------------- ------------1 3564047035 10,000 2 13177233 0 1 1 2 3 4 5 1756993664 3564047035 13177233 3400946141 1885085827 1 13177233 2004 5991 2000 4 1 10,000 38 3564047035 10,000

91.

Plan Hash # Value Executions --- ---------------- ------------1 1756993664 2000 2 3400946141 2000 3 1885085827 2000 4 13177233 2000 5 3564047035 2000 Plan Hash # Value Executions --- ---------------- ------------1 3564047035 10,000 2 13177233 0 1 1 2 3 4 5 1756993664 3564047035 13177233 3400946141 1885085827 1 13177233 2004 5991 2000 4 1 10,000 38 3564047035 10,000

92.

Plan Hash # Value Executions --- ---------------- ------------1 1756993664 2000 2 3400946141 2000 3 1885085827 2000 4 13177233 2000 5 3564047035 2000 Plan Hash # Value Executions --- ---------------- ------------1 3564047035 10,000 2 13177233 0 1 1 2 3 4 5 1756993664 3564047035 13177233 3400946141 1885085827 1 13177233 2004 5991 2000 4 1 10,000 38 3564047035 10,000

93.

Plan Hash # Value Executions --- ---------------- ------------1 1756993664 2000 2 3400946141 2000 3 1885085827 2000 4 13177233 2000 5 3564047035 2000 Plan Hash # Value Executions --- ---------------- ------------1 3564047035 10,000 2 13177233 0 1 1 2 3 4 5 1756993664 3564047035 13177233 3400946141 1885085827 1 13177233 2004 5991 2000 4 1 10,000 38 3564047035 10,000

94.

39

95.

40

96.

40

97.

40

98.

40

99.

40

100.

41

101.

42

102.

43

103.

✔dba_sql_plan_dir_objects OWNER --------MDSYS MDSYS MDSYS MDSYS MDSYS SH SH SH OBJECT -----COLUMN COLUMN COLUMN COLUMN TABLE COLUMN COLUMN TABLE OBJECT_NAME --------------RDF_LINK$ RDF_LINK$ RDF_LINK$ RDF_LINK$ RDF_LINK$ PRODUCTS PRODUCTS PRODUCTS SUBOBJECT_NAME -----------------CANON_END_NODE_ID G_ID P_VALUE_ID START_NODE_ID PROD_CATEGORY PROD_ID ✔dba_sql_plan_directives; TYPE ---------------DYNAMIC_SAMPLING DYNAMIC_SAMPLING DYNAMIC_SAMPLING REASON -----------------------------------SINGLE TABLE CARDINALITY MISESTIMATE JOIN CARDINALITY MISESTIMATE GROUP BY CARDINALITY MISESTIMATE 43

104.

43

105.

44

106.

45

107.

CBO (Cost Base Optimizer) SQL Transformation (Join Elimination, View Merge, Vector Transform) Bind Peek Adaptive Cursor Sharing Statistics Feedback Dynamic Statistics Adaptive Plan SQL Plan Directives SQL Plan stability Management SQL Hints 46

108.

CBO (Cost Base Optimizer) SQL Transformation (Join Elimination, View Merge, Vector Transform) Bind Peek Adaptive Cursor Sharing Statistics Feedback Dynamic Statistics Adaptive Plan SQL Plan Directives SQL Plan stability Management SQL Hints 46 ✌