SQLチューニング総合診療Oracle CloudWorld出張所

101 Views

April 12, 15

#SQL #Database #Query #Slide Presentation #Data Fetching

スライド概要

Slideshare移行2023-08-10時点View : 4,003 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 - 20.45MB)

関連スライド

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

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

3

4.

4

5.

select count(1) from foober; 5

6.

select count(1) from foober; 6

7.

select count(1) from foober; 7

8.

select count(1) from foober; 8

9.

select count(1) from foober; 9

10.

select count(1) from foober; 10

11.

select count(1) from foober; 11

12.

select count(1) from foober; 12

13.

select count(1) from foober; 13

14.

select count(1) from foober; 14

15.

15

16.

16

17.

select count(1) from foober; ・ ・ ・ ・ 17

18.

・ ・ ・ ・ SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7* AND status = '00' COUNT(1) ---------10000 SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7 AND status = '00' 8* AND ROWNUM <= 1 SCOTT> r SCOTT> r 1 SELECT COUNT(1) 1 SELECT 2 COUNT(1) ---------2 COUNT(1) 3 FROM 1 3 FROM 4 count4emptyset 4 count4emptyset 5 WHERE 5 WHERE 6 id BETWEEN 1 AND 10 6 id BETWEEN 1 AND 10 7 AND status = '00' 7* AND status = '01' 8* AND ROWNUM <= 101 COUNT(1) ---------101 18 COUNT(1) ---------0

19.

・ ・ SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN :1 AND :2 7 AND status = :3 8* AND ROWNUM <= :4 SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7* AND status = '00' COUNT(1) ---------1 SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7* AND status = ’01' COUNT(1) ---------101 COUNT(1) ---------0 COUNT(1) ---------10000 ・ ・ 19

20.

・ ・ SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN :1 AND :2 7 AND status = :3 8* AND ROWNUM <= :4 SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7* AND status = '00' COUNT(1) ---------1 SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7* AND status = ’01' AND ROWNUM <= 1 COUNT(1) ---------101 COUNT(1) ---------0 COUNT(1) ---------10000 ・ ・ 20

21.

・ ・ ・ ・ SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN :1 AND :2 7 AND status = :3 8* AND ROWNUM <= :4 SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN :1 AND :2 7* AND status = :3 COUNT(1) ---------10000 COUNT(1) — タイプ２ ---------101 COUNT(1) — タイプ３ ---------1 COUNT(1) ---------0 21 — タイプ４

22.

22

23.

23

24.

★表 SQL> desc count4emptyset 名前 ----------------------------------------ID BRANCH# STATUS DATA NULL? 型 -------NOT NULL NOT NULL NOT NULL ---------------------------NUMBER NUMBER CHAR(2) CHAR(500) ★索引 INDEX_NAME COLUMN_NAME ------------------------------ -----------------------------PK_COUNT4EMPTYSET ID BRANCH# ★列統計 DESC ---ASC ASC COLUMN_NAME NUM_DISTINCT NUM_NULLS HISTOGRAM ------------------------------ ------------ ---------- --------------ID 1000 0 NONE BRANCH# 1999 0 NONE STATUS 1 0 FREQUENCY DATA 1000 0 NONE ★索引統計 INDEX_NAME DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR ------------------------------ ------------- ---------- ----------------PK_COUNT4EMPTYSET 1000000 1000000 987729 24

25.
[beta] 
SCOTT> r
1 SELECT
2
COUNT(1)
3 FROM
4
count4emptyset
5 WHERE
6
id BETWEEN 1 AND 10
7*
AND status = '00'
COUNT(1)
---------10000
--------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows |..| Cost (%CPU)|..|
--------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |..| 9931
(1)|..|
|
1 | SORT AGGREGATE
|
|
1 |..|
|..|
|* 2 |
TABLE ACCESS BY INDEX ROWID BATCHED| COUNT4EMPTYSET
| 10008 |..| 9931
(1)|..|
|* 3 |
INDEX RANGE SCAN
| PK_COUNT4EMPTYSET | 10009 |..|
42
(0)|..|
--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------2 - filter("STATUS"='00')
3 - access("ID">=1 AND "ID"<=10)

25
26.
[beta] 
SCOTT> r
1 SELECT
2
COUNT(1)
3 FROM
4
count4emptyset
5 WHERE
6
id BETWEEN 1 AND 10
7*
AND status = '00'
COUNT(1)
---------10000
--------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows |..| Cost (%CPU)|..|
--------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |..| 9931
(1)|..|
|
1 | SORT AGGREGATE
|
|
1 |..|
|..|
|* 2 |
TABLE ACCESS BY INDEX ROWID BATCHED| COUNT4EMPTYSET
| 10008 |..| 9931
(1)|..|
|* 3 |
INDEX RANGE SCAN
| PK_COUNT4EMPTYSET | 10009 |..|
42
(0)|..|
--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------2 - filter("STATUS"='00')
3 - access("ID">=1 AND "ID"<=10)

26
27.
[beta] 
SCOTT> r
1 SELECT
2
COUNT(1)
3 FROM
4
count4emptyset
5 WHERE
6
id BETWEEN 1 AND 10
7*
AND status = '00'
COUNT(1)
---------10000
--------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows |..| Cost (%CPU)|..|
--------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |..| 9931
(1)|..|
|
1 | SORT AGGREGATE
|
|
1 |..|
|..|
|* 2 |
TABLE ACCESS BY INDEX ROWID BATCHED| COUNT4EMPTYSET
| 10008 |..| 9931
(1)|..|
|* 3 |
INDEX RANGE SCAN
| PK_COUNT4EMPTYSET | 10009 |..|
42
(0)|..|
--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------2 - filter("STATUS"='00')
3 - access("ID">=1 AND "ID"<=10)

27
28.
[beta] 
--------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows |..| Cost (%CPU)|..|
--------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |..| 9931
(1)|..|
|
1 | SORT AGGREGATE
|
|
1 |..|
|..|
|* 2 |
TABLE ACCESS BY INDEX ROWID BATCHED| COUNT4EMPTYSET
| 10008 |..| 9931
(1)|..|
|* 3 |
INDEX RANGE SCAN
| PK_COUNT4EMPTYSET | 10009 |..|
42
(0)|..|
--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------2 - filter("STATUS"='00')
3 - access("ID">=1 AND "ID"<=10)

統計
---------------------------------------------------------0 recursive calls
0 db block gets
9893 consistent gets
0 physical reads
0 redo size
・・・・・ 略 ・・・・・・・
2
0
0
1

SQL*Net roundtrips to/from client
sorts (memory)
sorts (disk)
rows processed
28
29.

SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7* AND status = '00' SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7* AND status = '01' COUNT(1) ---------10000 COUNT(1) ---------0 統計 統計 ----------------------------・・・・略・・・・ ----------------------------・・・・略・・・・ 9893 consistent gets 9893 consistent gets 0 physical reads ・・・・略・・・・ 0 physical reads ・・・・略・・・・ 29

30.
[beta] 
--------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows |..| Cost (%CPU)|..|
--------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |..| 9931
(1)|..|
|
1 | SORT AGGREGATE
|
|
1 |..|
|..|
|* 2 |
TABLE ACCESS BY INDEX ROWID BATCHED| COUNT4EMPTYSET
| 10008 |..| 9931
(1)|..|
|* 3 |
INDEX RANGE SCAN
| PK_COUNT4EMPTYSET | 10009 |..|
42
(0)|..|
--------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------2 - filter("STATUS"='00')
3 - access("ID">=1 AND "ID"<=10)

統計
---------------------------------------------------------・・・・・ 略 ・・・・・・・
9893 consistent gets
・・・・・ 略 ・・・・・・・
30
31.

31

32.

---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 7 | 27 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 7 | | | |* 2 | INDEX RANGE SCAN| IX1_COUNT4EMPTYSET | 10008 | 70056 | 27 (0)| 00:00:01 | ---------------------------------------------------------------------------------------Predicate Information (identified by operation id): --------------------------------------------------2 - access("ID">=1 AND "STATUS"='00' AND "ID"<=10) filter("STATUS"='00') 統計 ---------------------------------------------------------・・・・略・・・・ 26 consistent gets ・・・・略・・・ 32 治療済 ！

33.
[beta] 
SQL>
1
2
3
4
5
6
7
8*

r
SELECT
COUNT(1)
FROM
count4emptyset
WHERE
id BETWEEN 1 AND 10
AND status = '00'
AND ROWNUM <= 101

COUNT(1)
---------101
---------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows |..| Cost (%CPU)|..|
---------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |..|
104
(0)|..|
|
1 | SORT AGGREGATE
|
|
1 |..|
|..|
|* 2 |
COUNT STOPKEY
|
|
|..|
|..|
|* 3 |
TABLE ACCESS BY INDEX ROWID BATCHED| COUNT4EMPTYSET
|
102 |..|
104
(0)|..|
|* 4 |
INDEX RANGE SCAN
| PK_COUNT4EMPTYSET | 10009 |..|
3
(0)|..|
---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------2 - filter(ROWNUM<=101)
3 - filter("STATUS"='00')
4 - access("ID">=1 AND "ID"<=10)

33
34.

SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7 AND status = '00' 8* AND ROWNUM <= 101 SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7 AND status = '01' 8* AND ROWNUM <= 101 COUNT(1) ---------101 COUNT(1) ---------0 統計 統計 ---------------------------・・・・略・・・・ ---------------------------・・・・略・・・・ 103 consistent gets 9893 ・・・・略・・・・ consistent gets ・・・・略・・・・ 34

35.

SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7 AND status = '00' 8* AND ROWNUM <= 1 SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7 AND status = '01' 8* AND ROWNUM <= 1 COUNT(1) ---------1 COUNT(1) ---------0 統計 統計 ---------------------------・・・・略・・・・ ---------------------------・・・・略・・・・ 9893 4 consistent gets ・・・・略・・・・ consistent gets ・・・・略・・・・ 35

36.

SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7 AND status = '01' 8* AND ROWNUM <= 1 SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN 1 AND 10 7 AND status = ’00' 8* AND ROWNUM <= 1 COUNT(1) ---------0 COUNT(1) ---------1 統計 統計 ---------------------------・・・・略・・・・ ---------------------------・・・・略・・・・ 9893 consistent gets 4 consistent gets ・・・・略・・・・ ・・・・略・・・・ 36

37.
[beta] 
〜

---------------------------------------------------------------------------------------------| Id | Operation
| Name
| Rows |..| Cost (%CPU)|..|
---------------------------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |..| 9931
(1)|..|
|
1 | SORT AGGREGATE
|
|
1 |..|
|..|
|* 2 |
COUNT STOPKEY
|
|
|..|
|..|
|* 3 |
TABLE ACCESS BY INDEX ROWID BATCHED| COUNT4EMPTYSET
|
1 |..| 9931
(1)|..|
|* 4 |
INDEX RANGE SCAN
| PK_COUNT4EMPTYSET | 10009 |..|
42
(0)|..|
---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------2 - filter(ROWNUM<=101)
3 - filter("STATUS"='01')
4 - access("ID">=1 AND "ID"<=10)

37
38.
[beta] 
〜

治療済

---------------------------------------------------------------------------| Id | Operation
| Name
| Rows |..| Cost (%CPU)|..|
---------------------------------------------------------------------------|
0 | SELECT STATEMENT
|
|
1 |..|
3
(0)|..|
|
1 | SORT AGGREGATE
|
|
1 |..|
|..|
|* 2 |
COUNT STOPKEY
|
|
|..|
|..|
COUNT(1)
|* 3 |
INDEX RANGE SCAN| IX1_COUNT4EMPTYSET |
102 |..|
3
(0)|..|
-------------------------------------------------------------------------------------

101

Predicate Information (identified by operation id):
--------------------------------------------------2 - filter(ROWNUM<=101)
3 - access("ID">=1 AND "STATUS"='00' AND "ID"<=10)
filter("STATUS"='00')

COUNT(1)
---------1

統計
----------------------------・・・・略・・・・
3 consistent gets
・・・・略・・・・
COUNT(1)
---------0

統計
----------------------------・・・・略・・・・
4 consistent gets
・・・・略・・・・
38

統計
----------------------------・・・・略・・・・
24 consistent gets
・・・・略・・・・

！
39.

治療完 ！ ・ ・ ・ ・ SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN :1 AND :2 7* AND status = :3 + SCOTT> r 1 SELECT 2 COUNT(1) 3 FROM 4 count4emptyset 5 WHERE 6 id BETWEEN :1 AND :2 7 AND status = :3 8* AND ROWNUM <= :4 INDEX_NAME COLUMN_NAME ------------------------------ -----------------------------IX1_COUNT4EMPTYSET ID STATUS 39

40.

40

41.

41

42.

SELECT /*+ LEADING(t01 t02 t03) USE_NL(t01 t02 t03) INDEX(t01 ix01_count4emptyset) INDEX(t02 ix01_count4emptyset_b) INDEX(t03 ix01_count4emptyset_c) */ COUNT(1) FROM count4emptyset t01 INNER JOIN count4emptyset_b t02 ON t01.id = t02.id AND t01.branch# = t02.branch# AND t02.status = '00' INNER JOIN count4emptyset_c t03 ON t02.id = t03.id AND t02.branch# = t03.branch# AND t03.status = '01' WHERE t01.id BETWEEN 1 AND 10 AND t01.status = '00' AND ROWNUM <= 1; 42

43.

〜 -------------------------------------------------------------------| Id | Operation | Name |..| A-Rows |.. -------------------------------------------------------------------| 0 | SELECT STATEMENT | |..| 1 |.. | 1 | SORT AGGREGATE | |..| 1 |.. |* 2 | COUNT STOPKEY | |..| 0 |.. | 3 | NESTED LOOPS | |..| 0 |.. | 4 | NESTED LOOPS | |..| 10000 |.. |* 5 | INDEX RANGE SCAN| IX01_COUNT4EMPTYSET |..| 10000 |.. |* 6 | INDEX RANGE SCAN| IX01_COUNT4EMPTYSET_B |..| 10000 |.. |* 7 | INDEX RANGE SCAN | IX01_COUNT4EMPTYSET_C |..| 0 |.. -------------------------------------------------------------------統計 統計 -----------------------------・・・・略・・・・ 9 consistent gets 0 physical reads ・・・・略・・・・ 43 -----------------------------・・・・略・・・・ 812 consistent gets 0 physical reads ・・・・略・・・・

44.

http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2012/04/index-inde-only.html http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2013/06/rownum-1-a44c.html http://discus-hamburg.cocolog-nifty.com/mac_de_oracle/2014/07/table-access-by.html 44

45.

45

46.

http://www.jpoug.org 46 ✌