-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy path使用spm固定执行计划.txt
317 lines (241 loc) · 13.8 KB
/
使用spm固定执行计划.txt
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
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
方案一：
##################################################################################################################################################################
第一步：
declare
my_task_name varchar2(30);
my_sqltext clob;
begin
my_sqltext:='select /*+ no_index(t1 idx_t1) */ * from t1 where n=1';
my_task_name:=dbms_sqltune.create_tuning_task(
sql_text=>my_sqltext,
user_name=>'WBB',
scope=>'COMPREHENSIVE',
time_limit=>60,
task_name=>'my_sql_tuning_task_2',
description=>'Task to tune a query on table t1');
end;
/
第二步：
SQL> select task_name,status,execution_start,execution_end from user_advisor_log;
TASK_NAME STATUS EXECUTION EXECUTION
-------------------------------------------------------------------------------------------------------------------------------- ----------- --------- ---------
SYS_AUTO_SPM_EVOLVE_TASK COMPLETED 09-JAN-20 09-JAN-20
SYS_AI_SPM_EVOLVE_TASK INITIAL
SYS_AI_VERIFY_TASK INITIAL
SYS_AUTO_INDEX_TASK INITIAL
AUTO_STATS_ADVISOR_TASK COMPLETED 09-JAN-20 09-JAN-20
INDIVIDUAL_STATS_ADVISOR_TASK INITIAL
my_sql_tuning_task_1 INITIAL
第三步：
SQL> begin
dbms_sqltune.execute_tuning_task(task_name=>'my_sql_tuning_task_2');
end;
/
PL/SQL procedure successfully completed.
SQL> select task_name,status,execution_start,execution_end from user_advisor_log;
TASK_NAME STATUS EXECUTION EXECUTION
-------------------------------------------------------------------------------------------------------------------------------- ----------- --------- ---------
SYS_AUTO_SPM_EVOLVE_TASK COMPLETED 09-JAN-20 09-JAN-20
SYS_AI_SPM_EVOLVE_TASK INITIAL
SYS_AI_VERIFY_TASK INITIAL
SYS_AUTO_INDEX_TASK INITIAL
AUTO_STATS_ADVISOR_TASK COMPLETED 09-JAN-20 09-JAN-20
INDIVIDUAL_STATS_ADVISOR_TASK INITIAL
my_sql_tuning_task_1 COMPLETED 09-JAN-20 09-JAN-20
7 rows selected.
第四步：
set long 9000
set longchunksize 1000
select dbms_sqltune.report_tuning_task('my_sql_tuning_task_2') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : my_sql_tuning_task_2
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status : COMPLETED
Started at : 01/10/2020 08:23:09
Completed at : 01/10/2020 08:23:12
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------------------------
Schema Name : WBB
Container Name: WBB
SQL ID : 1kg76709mx29d
SQL Text : select /*+ no_index(t1 idx_t1) */ * from t1 where n=1
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 90.9%)
-----------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name =>
'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE);
Validation results
------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
The SQL profile was tested by executing both its plan and the original plan
and measuring their respective execution statistics. A plan may have been
only partially executed if the other could be run to completion in less time.
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .001968 .000039 98.01 %
CPU Time (s): .000327 .000039 88.07 %
User I/O Time (s): 0 0
Buffer Gets: 22 2 90.9 %
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 1 1
Fetches: 1 1
Executions: 1 1
Notes
-----
1. Statistics for the original plan were averaged over 10 executions.
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
2. Statistics for the SQL profile plan were averaged over 10 executions.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 4 | 7 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("N"=1)
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 3 (U - Unused (3))
---------------------------------------------------------------------------
0 - STATEMENT
U - IGNORE_OPTIM_EMBEDDED_HINTS / hint overridden by another in parent query block
U - OPTIMIZER_FEATURES_ENABLE(default) / hint overridden by another in parent query block
1 - SEL$1 / T1@SEL$1
U - no_index(t1 idx_t1) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
2- Using SQL Profile
--------------------
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
U - no_index(t1 idx_t1) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK_2')
-------------------------------------------------------------
第五步：
execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE);
验证即可：
普通用户执行
SQL> select /*+ no_index(t1 idx_t1) */ * from t1 where n=1;
N
----------
1
Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
U - no_index(t1 idx_t1) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
Note
-----
- SQL profile "SYS_SQLPROF_016f8ce95f930000" used for this statement
Statistics
----------------------------------------------------------
36 recursive calls
0 db block gets
11 consistent gets
1 physical reads
0 redo size
543 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
############################################################
注：按照此情况固定了执行计划，但目标sql一旦发生一点儿变动原有的sql_profile将会失去作用。需要将force_match 设为true
第六步：
select /*+ no_index(t1 idx_t1) */ * from t1 where n=2;
N
----------
2
Execution Plan
----------------------------------------------------------
Plan hash value: 1369807930
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T1 | 1 | 4 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("N"=2)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
U - no_index(t1 idx_t1) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS
Note
-----
- SQL profile "SYS_SQLPROF_016f8cf78bf80001" used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
6 consistent gets
1 physical reads
0 redo size
543 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
第七步：
execute dbms_sqltune.accept_sql_profile(task_name =>'my_sql_tuning_task_2', task_owner => 'SYS', replace => TRUE,force_match=>true);
方案二：
#####################################################################################################################################################################
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_016f8cf78bf80001'); SYS_SQLPROF_016f8cf78bf80001 可从执行计划中得知。
PL/SQL procedure successfully completed.
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_016f8ce95f930000');
PL/SQL procedure successfully completed.
select /*+ no_index(t1 idx_t1) */ * from t1 where n=1；查看执行计划已走全表
1.manual 类型的sql_profile本质上就是一堆hint的组合，这一堆hint的组合实际上来源于执行计划的outline data 部分的hint组合。