db tech showcase Tokyo 2014 - L36 - JPOUG：SQLチューニング総合診療所　ケースファイルX

499 Views

November 19, 14

#Database #Oracle #Query Plan #Execution Statistics #SQL

スライド概要

Slideshare移行 2023-08-10時点View : 4840 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 - 12.64MB)

関連スライド

slide-thumbnail

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

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

DB Tech Showcase 2016 - E35 - SQLチューニング総合診療所的予防医学
user-img Hiroshi Sekiguchi 🍥 310
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.

1

2.

〜 2

3.

Oracle Core Operating System 3

4.

4

5.

5

6.

6

7.

7

8.

8

9.

9

10.

10

11.

11

12.

12

13.

13

14.

14

15.

🔺 15

16.

🔺 ✖ ✖ 16

17.

17

18.

18

19.

19

20.

20

21.

21

22.

22

23.

〜 23

24.

24

25.

25000行が選択されました。 実行計画 ------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 25000 | 12M| 1841 (1)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DELUDING_TAB | 25000 | 12M| 1841 (1)| 00:00:01 | |* 2 | INDEX RANGE SCAN | PK_DELUDING_TAB | 25000 | | 54 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------30000行が選択されました。 実行計画 ---------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 30000 | 14M| 1947 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DELUDING_TAB | 30000 | 14M| 1947 (1)| 00:00:01 | ---------------------------------------------------------------------------------- 25

26.

25000行が選択されました。 実行計画 ------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 25000 | 12M| 1841 (1)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DELUDING_TAB | 25000 | 12M| 1841 (1)| 00:00:01 | |* 2 | INDEX RANGE SCAN | PK_DELUDING_TAB | 25000 | | 54 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------30000行が選択されました。 実行計画 ---------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 30000 | 14M| 1947 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DELUDING_TAB | 30000 | 14M| 1947 (1)| 00:00:01 | ---------------------------------------------------------------------------------- 26

27.

2100行が選択されました。 実行計画 -------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2102 | 1077K| 2115 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DELUDING_TAB2 | 2102 | 1077K| 2115 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | PK_DELUDING_TAB2 | 2102 | | 13 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------2500行が選択されました。 実行計画 ----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2482 | 1272K| 2117 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DELUDING_TAB2 | 2482 | 1272K| 2117 (1)| 00:00:01 | ----------------------------------------------------------------------------------- 27

28.

2100行が選択されました。 実行計画 -------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2102 | 1077K| 2115 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| DELUDING_TAB2 | 2102 | 1077K| 2115 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | PK_DELUDING_TAB2 | 2102 | | 13 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------------2500行が選択されました。 実行計画 ----------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 2482 | 1272K| 2117 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| DELUDING_TAB2 | 2482 | 1272K| 2117 (1)| 00:00:01 | ----------------------------------------------------------------------------------- 28

29.

INDEX_NAME CLUSTERING_FACTOR NUM_ROWS ------------------------------ ----------------- ---------PK_DELUDING_TAB 7143 100000 PK_DELUDING_TAB2 99990 100000 29

30.

30

31.

31

32.

32

33.

33

34.

34

35.

35

36.

統計 ---------------------------------------------------------0 recursive calls 0 db block gets 107173 consistent gets 統計 ---------------------------------------------------------0 recursive calls 0 db block gets 13911 consistent gets 36

37.

37

38.

🔺 38

39.

39

40.

select * from bad_statistics_tab where status=リテラル値; 40

41.

--------------------------------------------------------| Id | Operation | Name | Rows |-------------------------------------------------------| 0 | SELECT STATEMENT | | 99900 | |* 1 | TABLE ACCESS FULL| BAD_STATISTICS_TAB | 99900 | ---------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------1 - filter("STATUS"=0) 41

42.

1 recursive calls 0 db block gets 20159 consistent gets 0 physical reads 0 redo size --------------------------------------------------------2262529 bytes sent via SQL*Net to client | Id | Operation | Name | Rows 73800 bytes received via SQL*Net from client |-------------------------------------------------------6661 SQL*Net roundtrips to/from client | 0 | SELECT STATEMENT | 99900 | 0 sorts (memory)| |* 1 | TABLE ACCESS FULL| BAD_STATISTICS_TAB | 99900 | 0 sorts (disk) ---------------------------------------------------------99900 rows processed Predicate Information (identified by operation id): --------------------------------------------------1 - filter("STATUS"=0) 42

43.

-------------------------------------------------------------------------| Id | Operation | Name | Rows | -------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BAD_STATISTICS_TAB | 1 | |* 2 | INDEX RANGE SCAN | BAD_INDEX | 1 | -------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - access("STATUS"=1) 43

44.

1 recursive calls 0 db block gets 35214 consistent gets 0 physical reads 674340 redo size -------------------------------------------------------------------------103579092 bytes sent via SQL*Net | toName client | Id | Operation | Rows | 73800 bytes received via SQL*Net from client -------------------------------------------------------------------------6661 SQL*Net roundtrips to/from client | 0 | SELECT STATEMENT | | 1 | 0 sorts (memory) | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BAD_STATISTICS_TAB | 1 | 0 sorts |* 2 | INDEX RANGE SCAN (disk) | BAD_INDEX | 1 | 99900 rows processed -------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - access("STATUS"=1) 44

45.

-------------------------------------------------------------------------| Id | Operation | Name | Rows | -------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BAD_STATISTICS_TAB | 1 | |* 2 | INDEX RANGE SCAN | BAD_INDEX | 1 | -------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - access("STATUS"=2) 45

46.

1 recursive calls 0 db block gets 35215 consistent gets 0 physical reads 674340 redo size -------------------------------------------------------------------------103579092 bytes sent via SQL*Net to client | Id | Operation | Name | Rows | 73800 bytes received via SQL*Net from client -------------------------------------------------------------------------SQL*Net roundtrips to/from client | 0 | SELECT 6661 STATEMENT | | 1 | 0 sorts (memory) | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BAD_STATISTICS_TAB | 1 | 0 sorts |* 2 | INDEX RANGE SCAN (disk) | BAD_INDEX | 1 | 99900 rows processed -------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - access("STATUS"=2) 46

47.

1 recursive calls 0 db block gets 35215 consistent gets 0 physical reads 674340 redo size -------------------------------------------------------------------------103579092 bytes sent via SQL*Net to client | Id | Operation | Name | Rows | 73800 bytes received via SQL*Net from client -------------------------------------------------------------------------SQL*Net roundtrips to/from client | 0 | SELECT 6661 STATEMENT | | 1 | 0 sorts (memory) | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BAD_STATISTICS_TAB | 1 | 0 sorts |* 2 | INDEX RANGE SCAN (disk) | BAD_INDEX | 1 | 99900 rows processed -------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - access("STATUS"=2) 47

48.

48

49.

🔺 49

50.

🔺 50

51.

51

52.

52

53.

--------------------------------------------------| Id | Operation | Name | Rows | --------------------------------------------------| 0 | SELECT STATEMENT | | 100K| | 1 | TABLE ACCESS FULL| DELUDING_TAB2 | 100K| --------------------------------------------------統計 -------------------------------------------------0 recursive calls 0 db block gets 13905 consistent gets 7742 physical reads 0 redo size 55292936 bytes sent via SQL*Net to client 73878 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 53

54.

============================================~============================================== | Id | Operation | Name |~| Activity | Activity Detail | | | | |~| (%) | (# samples) | ============================================~============================================== | 0 | SELECT STATEMENT | |~| 20.00 | Cpu (1) | | 1 | TABLE ACCESS FULL | DELUDING_TAB2 |~| 80.00 | Cpu (1) | | | | |~| | SQL*Net message to client (1) | | | | |~| | SQL*Net more data to client (2) | ============================================~============================================== 54

55.

call count ------- -----Parse 1 Execute 1 Fetch 6668 ------- -----total 6670 cpu elapsed disk query current -------- ---------- ---------- ---------- ---------0.00 0.00 0 0 0 0.00 0.00 0 0 0 0.85 0.81 7742 13905 0 -------- ---------- ---------- ---------- ---------0.85 0.81 7742 13905 0 Elapsed times include waiting on following events: Event waited on Times ---------------------------------------Waited SQL*Net message to client 6668 direct path read 130 SQL*Net message from client 6668 55 Max. Wait ---------0.00 0.00 0.01 rows ---------0 0 100000 ---------100000 Total Waited -----------0.14 0.02 8.17

56.

56

57.

統計 -------------------------------------------------0 recursive calls 0 db block gets 13905 consistent gets 7742 physical reads 0 redo size 55292936 bytes sent via SQL*Net to client 73878 bytes received via SQL*Net from client 6668 SQL*Net roundtrips to/from client 0 0 8367 7742 0 54128936 7878 668 recursive calls db block gets consistent gets physical reads redo size bytes sent via SQL*Net to client bytes received via SQL*Net from client SQL*Net roundtrips to/from client 57

58.

============================================~============================================== | Id | Operation | Name |~| Activity | Activity Detail | | | | |~| (%) | (# samples) | ============================================~============================================== | 0 | SELECT STATEMENT | |~| 20.00 | Cpu (1) | | 1 | TABLE ACCESS FULL | DELUDING_TAB2 |~| 80.00 | Cpu (1) | | | | |~| | SQL*Net message to client (1) | | | | |~| | SQL*Net more data to client (2) | ============================================~============================================== ============================================~============================== | Id | Operation | Name |~| Activity | Activity Detail | | | | |~| (%) | (# samples) | ============================================~============================== | 0 | SELECT STATEMENT | |~| | | | 1 | TABLE ACCESS FULL | DELUDING_TAB2 |~| 100.00 | Cpu (2) | ============================================~============================== 58

59.

FETCH #140651855557776:c=0,e=68,p=0,cr=2,cu=0,mis=0,r=15, .... WAIT #140651855557776: nam='SQL*Net message from client' .... WAIT #140651855557776: nam='SQL*Net message to client' .... FETCH #140651855557776:c=0,e=79,p=0,cr=2,cu=0,mis=0,r=15 .... WAIT #140651855557776: nam='SQL*Net message from client'.... WAIT #140651855557776: nam='SQL*Net message to client'.... FETCH #140651855557776:c=0,e=147,p=0,cr=9,cu=0,mis=0,r=15.... WAIT #140651855557776: nam='SQL*Net message from client' .... WAIT #140651855557776: nam='SQL*Net message to client'.... FETCH #140651855557776:c=0,e=101,p=0,cr=9,cu=0,mis=0,r=15,.... ....以下略.... 59

60.

FETCH #140651855557776:c=2000,e=1695,p=64,cr=13,cu=0,mis=0,r=150,... WAIT #140651855557776: nam='SQL*Net message from client'..... WAIT #140651855557776: nam='SQL*Net message to client'..... FETCH #140651855557776:c=0,e=1093,p=0,cr=12,cu=0,mis=0,r=150,... WAIT #140651855557776: nam='SQL*Net message from client'..... WAIT #140651855557776: nam='SQL*Net message to client'..... FETCH #140651855557776:c=0,e=161,p=0,cr=12,cu=0,mis=0,r=150,.... WAIT #140651855557776: nam='SQL*Net message from client'..... WAIT #140651855557776: nam='SQL*Net message to client'..... FETCH #140651855557776:c=18000,e=17507,p=0,cr=13,cu=0,mis=0,r=150,.. ....以下略.... 60

61.

call count ------- -----Parse 1 Execute 1 Fetch 6668 Fetch 668 cpu elapsed disk query current -------- ---------- ---------- ---------- ---------0.00 0.00 0 0 0 0.00 0.00 0 0 0 0.85 0.81 7742 13905 0 0.17 0.17 7742 8367 Elapsed times include waiting on following events: Event waited on Times ---------------------------------------Waited SQL*Net message to client 6668 direct path read 130 SQL*Net message from client 6668 SQL*Net message from client 668 61 0 rows ---------0 0 100000 100000 Max. Wait ---------0.00 0.00 0.01 Total Waited -----------0.14 0.02 8.17 0.03 3.83

62.

62

63.

🔺 63

64.

64

65.

65

66.

http://www.jpoug.org 66 ✌