반응형
Table Access 패턴을 이용한 SQL 튜닝.
특정 테이블의 엑세스 패턴을 분석하여 인덱스 생성 가이드시 활용 한다.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
|
-- ###################################################
-- SQL 액세스 패턴 분석
--
-- 특정 테이블을 사용하는 전체 쿼리의 액세스 패턴 분석
-- ###################################################
WITH W_SQL_LIST AS
(
-- (1) 대상 테이블을 포함한 SQL_ID 추출
SELECT DISTINCT SQL_ID
, INST_ID
FROM GV$SQL_PLAN P
WHERE 1=1
AND OBJECT_NAME IN (SELECT UPPER(:TNAME) FROM DUAL
UNION ALL
SELECT INDEX_NAME FROM DBA_INDEXES
WHERE TABLE_NAME = UPPER(:TNAME))
),
W_SQL_FACE AS
(
-- (2) 추출된 SQL_ID의 성능 정보 추출
SELECT /*+ LEADING(SP) USE_HASH(SA) */
SA.INST_ID
, SA.SQL_ID
, SA.VERSION_COUNT
, SA.EXECUTIONS
, SA.FIRST_LOAD_TIME
, SA.DISK_READS
, SA.DIRECT_WRITES
, SA.BUFFER_GETS
, SA.ROWS_PROCESSED
, SA.OPTIMIZER_COST
, SA.PARSING_SCHEMA_NAME
, SA.HASH_VALUE
, SA.PLAN_HASH_VALUE
, SA.MODULE
, SA.CPU_TIME
, SA.ELAPSED_TIME
, SA.LAST_LOAD_TIME
, RANK() OVER( PARTITION BY SA.INST_ID, SA.PLAN_HASH_VALUE ORDER BY BUFFER_GETS DESC ) RK
FROM GV$SQLAREA SA, W_SQL_LIST SP
WHERE 1=1
AND SA.SQL_ID = SP.SQL_ID
AND SA.INST_ID = SP.INST_ID
-- AND MODULE NOT LIKE 'TOAD%'
-- AND MODULE NOT LIKE 'MAX%'
-- AND MODULE NOT LIKE 'TOAD%'
-- AND MODULE NOT LIKE 'LITE%'
-- AND MODULE NOT LIKE 'LINESTOP%'
-- AND MODULE NOT LIKE 'GOLDEN%'
-- AND MODULE NOT LIKE 'GOLDEN%'
-- AND MODULE NOT LIKE 'ORANGE%'
-- AND PARSING_SCHEMA_NAME LIKE 'GMES20%'
) ,
W_SQL_PT AS
(
-- (3) 추출된 SQL_ID별 성능 정보에 대한 PLAN 정보 추출
SELECT /*+ USE HASH(P X) */ P.INST_ID
, P.SQL_ID
, P.PLAN_HASH_VALUE
, P.OBJECT_NAME ||' ' ||P.OPERATION ||' ' ||P.OPTIONS AS OPERATION
, P.OBJECT_TYPE
, P.ID
, P.PARENT_ID
, P.DEPTH
, P.ACCESS_PREDICATES
, P.FILTER_PREDICATES
, P.CHILD_NUMBER
FROM GV$SQL_PLAN P,
( SELECT /*+ NO MERGE USE HASH(P F) */ P.INST_ID
, P.PLAN_HASH_VALUE
, P.OPERATION
, P.OPTIONS
, P.OBJECT_NAME
, P.OBJECT_TYPE
, P.ID
, P.PARENT_ID
, P.DEPTH
, P.ACCESS_PREDICATES
, P.FILTER_PREDICATES
, P.SQL_ID
FROM GV$SQL_PLAN P, W_SQL_FACE F
WHERE 1=1
AND F.RK = 1
AND F.SQL_ID = P.SQL_ID
AND OBJECT_NAME IN (SELECT UPPER(:TNAME) FROM DUAL
UNION ALL
SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME = UPPER(:TNAME))
) X
WHERE X.INST_ID = P.INST_ID
AND X.PARENT_ID = P.ID
AND P.SQL_ID = X.SQL_ID
AND P.PLAN_HASH_VALUE = X.PLAN_HASH_VALUE
-- AND P.OPERATION = 'HASH JOIN'
UNION ALL
SELECT /*+ USE HASH(P F)*/ P.INST_ID
, P.SQL_ID
, P.PLAN_HASH_VALUE
, P.OBJECT_NAME ||' ' ||P.OPERATION ||' ' ||P.OPTIONS AS OPERATION
, P.OBJECT_TYPE
, P.ID
, P.PARENT_ID
, P.DEPTH
, P.ACCESS_PREDICATES
, P.FILTER_PREDICATES
, P.CHILD_NUMBER
FROM GV$SQL_PLAN P, W_SQL_FACE F
WHERE 1=1
AND F.RK = 1
AND F.SQL_ID = P.SQL_ID
AND OBJECT_NAME IN (SELECT UPPER(:TNAME) FROM DUAL
UNION ALL
SELECT INDEX_NAME FROM DBA_INDEXES WHERE TABLE_NAME = UPPER(:TNAME))
)
SELECT DECODE( SEQ# , 1 , DECODE(INST_CNT , 2 , 3 , 1 , INST_ID)) INST_ID
, DECODE( SEQ# , 1 , MODULE ) MODULE
, DECODE( SEQ# , 1 , (SELECT KGLFNOBJ FROM SYS.X$KGLCURSOR_CHILD X WHERE X.KGLOBT03 = A.SQL_ID AND ROWNUM <= 1)) TEXT
, DECODE( SEQ# , 1 , SQL_ID ) SQL_ID
, DECODE( SEQ# , 1 , LITER_CNT ) LITER_CNT
, DECODE( SEQ# , 1 , PLAN_HASH_VALUE ) PLAN_HASH_VALUE
, DECODE( SEQ# , 1 , PARSING_SCHEMA_NAME ) PARSING_SCHEMA_NAME
, DECODE( SEQ# , 1 , EXECUTIONS ) EXEC_T
, DECODE( SEQ# , 1 , FACE_EXEC ) FA_EXEC
, DECODE( SEQ# , 1 , BUFFER_GETS ) BG
, DECODE( SEQ# , 1 , FACE_BGETS ) FA_BG
, DECODE( SEQ# , 1 , ROUND(( BUFFER_GETS ) /DECODE(( EXECUTIONS ) , 0 , 1 , ( EXECUTIONS ) ) , 0 )) Buffer_gets
, DECODE( SEQ# , 1 , ROUND(( DISK_READS ) /DECODE(( EXECUTIONS ) , 0 , 1 , ( EXECUTIONS ) ) , 2 )) Disk_reads
, DECODE( SEQ# , 1 , ROUND(( ROWS_PROCESSED ) /DECODE(( EXECUTIONS ) , 0 , 1 , ( EXECUTIONS ) ) , 0 )) Rows_Proc
, DECODE( SEQ# , 1 , ROUND((( ELAPSED_TIME ) /DECODE(( EXECUTIONS ) , 0 , 1 , ( EXECUTIONS ) ) ) /1000000 , 3 )) Elapsed
, DECODE( SEQ# , 1 , ROUND((( CPU_TIME ) /DECODE(( EXECUTIONS ) , 0 , 1 , ( EXECUTIONS ) ) ) /1000000 , 3 )) CPU_Time
, ID
, PARENT_ID
, SEQ#
, OPERATION
, FILTER_PREDICATES
, ACCESS_PREDICATES
FROM (SELECT A.* , RANK() OVER (ORDER BY BUFFER_GETS DESC, SQL_ID, PLAN_HASH_VALUE) BG_SEQ#
FROM (SELECT /*+ LEADING(P) USE HASH(P F S) */ ROW_NUMBER() OVER( PARTITION BY X.SQL_ID, X.PLAN_HASH_VALUE ORDER BY X.ID) SEQ#, X.*
FROM (SELECT P.ID
, P.PARENT_ID
, P.SQL_ID
, P.PLAN_HASH_VALUE
, SUM( S.LITER_CNT ) LITER_CNT
, MAX( P.OPERATION ) OPERATION
, MAX( P.FILTER_PREDICATES ) FILTER_PREDICATES
, MAX( P.ACCESS_PREDICATES ) ACCESS_PREDICATES
, MAX( F.MODULE ) MODULE
, MAX( F.PARSING_SCHEMA_NAME ) PARSING_SCHEMA_NAME
, MAX( S.EXECUTIONS ) EXECUTIONS
, MAX( F.EXECUTIONS ) FACE_EXEC
, MAX( S.BUFFER_GETS ) BUFFER_GETS
, MAX( F.BUFFER_GETS ) FACE_BGETS
, MAX( S.DISK_READS ) DISK_READS
, MAX( S.ROWS_PROCESSED ) ROWS_PROCESSED
, MAX( S.ELAPSED_TIME ) ELAPSED_TIME
, MAX( S.CPU_TIME ) CPU_TIME
, MAX( F.INST_ID ) INST_ID
, MAX( DISTINCT F.INST_ID ) INST_CNT
FROM W_SQL_PT P, W_SQL_FACE F,
( SELECT INST_ID --같은 PLAN HASH VALUE 를 같는 쿼리의 일량을 모두 합침
, COUNT( DISTINCT SQL_ID ) LITER_CNT
, MAX( VERSION_COUNT ) VERSION_COUNT
, SUM( EXECUTIONS ) EXECUTIONS
, MIN( FIRST_LOAD_TIME ) FIRST_LOAD_TIME
, SUM( DISK_READS ) DISK_READS
, SUM( DIRECT_WRITES ) DIRECT_WRITES
, SUM( BUFFER_GETS ) BUFFER_GETS
, SUM( ROWS_PROCESSED ) ROWS_PROCESSED
, AVG( OPTIMIZER_COST ) OPTIMIZER_COST
, MAX( PARSING_SCHEMA_NAME ) PARSING_SCHEMA_NAME
, MAX( HASH_VALUE ) HASH_VALUE
, PLAN_HASH_VALUE
, MAX( MODULE ) MODULE
, SUM( CPU_TIME ) CPU_TIME
, SUM( ELAPSED_TIME ) ELAPSED_TIME
, MAX( LAST_LOAD_TIME ) LAST_LOAD_TIME
FROM W_SQL_FACE
WHERE 1=1
GROUP BY INST_ID, PLAN_HASH_VALUE ) S
WHERE F.RK = 1
AND F.PLAN_HASH_VALUE = S.PLAN_HASH_VALUE
AND F.INST_ID = S.INST_ID
AND F.SQL_ID = P.SQL_ID
AND F.INST_ID = P.INST_ID
GROUP BY P.ID, P.SQL_ID ,P.PARENT_ID ,P.PLAN_HASH_VALUE
) X
) A
) A
ORDER BY BG_SEQ# , SEQ#
;
|
cs |
[참조 사이트]
TABLE ACCESS 패턴을 이용한 SQL 튜닝 – DATA ON-AIR (dataonair.or.kr)
TABLE ACCESS 패턴을 이용한 SQL 튜닝
TABLE ACCESS 패턴을 이용한 SQL 튜닝 ㈜엑셈 컨설팅본부 /DB컨설팅팀 오 경렬 본 문서는 필자가 사용하는 TABLE ACCESS 패턴을 확인하는 스크립트에 대한 내용입니다. 필자가 SQL 튜닝을 진행하면서 애용
dataonair.or.kr
반응형
'[DATABASE] ORACLE > SQL Tuning' 카테고리의 다른 글
| Random I/O 최소화 (인덱스 컬럼추가 & 인덱스만 읽고 처리) (0) | 2022.11.25 |
|---|---|
| 4. 수평적 탐색 최적화 (0) | 2022.10.31 |
| 데이터베이스 성능 튜닝 3대 핵심 요소 (0) | 2022.10.05 |
| 3. 커서공유 (0) | 2022.10.05 |
| 2. SQL Hint (힌트) (0) | 2022.09.29 |
댓글