在Oracle中，SPM的使用有哪些步骤？
取消自动捕获，也可以不取消自动捕捉：
1show parameter baselines
2ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;
执行：
1[ZHLHRSPMDB2:oracle]:/oracle>ORACLE_SID=dlhr
2[ZHLHRSPMDB2:oracle]:/oracle>sqlplus / as sysdba
3
4SQL*Plus: Release 11.2.0.4.0 Production on Thu May 26 15:47:55 2016
5
6Copyright (c) 1982, 2013, Oracle. All rights reserved.
7
8
9Connected to:
10Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
11With the Partitioning, Real Application Clusters, OLAP, Data Mining
12and Real Application Testing options
13
14SYS@dlhr> conn lhr/lhr
15Connected.
16LHR@dlhr>
17LHR@dlhr>
18LHR@dlhr>
19LHR@dlhr> select * from v$version;
20
21BANNER
22--------------------------------------------------------------------------------
23Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
24PL/SQL Release 11.2.0.4.0 - Production
25CORE 11.2.0.4.0 Production
26TNS for IBM/AIX RISC System/6000: Version 11.2.0.4.0 - Production
27NLSRTL Version 11.2.0.4.0 - Production
28
29LHR@dlhr> show parameter baselines
30
31NAME TYPE VALUE
32------------------------------------ ----------- ------------------------------
33optimizer_capture_sql_plan_baselines boolean TRUE
34optimizer_use_sql_plan_baselines boolean TRUE
35
36LHR@dlhr> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;
37
38System altered.
--创建表并插入数据，脚本：
1CREATE TABLE tb_spm_test_lhr (
2 id NUMBER,
3 description VARCHAR2(50)
4);
5
6DECLARE
7 TYPE t_tab IS TABLE OF tb_spm_test_lhr%ROWTYPE;
8 l_tab t_tab := t_TAB();
9BEGIN
10 FOR i IN 1 .. 10000 LOOP
11 l_tab.extend;
12 l_tab(l_tab.last).id := i;
13 l_tab(l_tab.last).description := 'Description for ' || i;
14 END LOOP;
15
16 FORALL i IN l_tab.first .. l_tab.last
17 INSERT INTO tb_spm_test_lhr VALUES l_tab(i);
18
19 COMMIT;
20 END;
21 /
22
23
24EXEC DBMS_STATS.gather_table_stats(USER, 'tb_spm_test_lhr', cascade=>TRUE);
25
26set autot trace
27SELECT description FROM tb_spm_test_lhr WHERE id = 100;
开始执行：
1LHR@dlhr> CREATE TABLE tb_spm_test_lhr (
2 2 id NUMBER,
3 3 description VARCHAR2(50)
4 4 );
5
6Table created.
7
8LHR@dlhr>
9LHR@dlhr> DECLARE
10 2 TYPE t_tab IS TABLE OF tb_spm_test_lhr%ROWTYPE;
11 3 l_tab t_tab := t_TAB();
12 4 BEGIN
13 5 FOR i IN 1 .. 10000 LOOP
14 6 l_tab.extend;
15 7 l_tab(l_tab.last).id := i;
16 8 l_tab(l_tab.last).description := 'Description for ' || i;
17 9 END LOOP;
18 10
19 11 FORALL i IN l_tab.first .. l_tab.last
20 12 INSERT INTO tb_spm_test_lhr VALUES l_tab(i);
21 13
22 14 COMMIT;
23 15 END;
24 16 /
25
26PL/SQL procedure successfully completed.
27
28
29LHR@dlhr> set autot trace
30LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100;
31
32
33Execution Plan
34----------------------------------------------------------
35Plan hash value: 2196561629
36
37-------------------------------------------------------------------------------------
38| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
39-------------------------------------------------------------------------------------
40| 0 | SELECT STATEMENT | | 1 | 40 | 13 (0)| 00:00:01 |
41|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 40 | 13 (0)| 00:00:01 |
42-------------------------------------------------------------------------------------
43
44Predicate Information (identified by operation id):
45---------------------------------------------------
46
47 1 - filter("ID"=100)
48
49Note
50-----
51 - dynamic sampling used for this statement (level=2)
52
53
54Statistics
55----------------------------------------------------------
56 4 recursive calls
57 0 db block gets
58 94 consistent gets
59 0 physical reads
60 0 redo size
61 546 bytes sent via SQL*Net to client
62 519 bytes received via SQL*Net from client
63 2 SQL*Net roundtrips to/from client
64 0 sorts (memory)
65 0 sorts (disk)
66 1 rows processed
获取刚才查询的SQL_ID:
1set autot off
2col SQL_TEXT format a100
3select distinct a.SQL_ID,a.SQL_TEXT from v$sql a
4WHERE a.SQL_TEXT like '%SELECT description FROM tb_spm_test_lhr WHERE id = 100%'
5and a.SQL_TEXT not like '%v$sql%'
6AND sql_text NOT LIKE '%EXPLAIN%';
7
8LHR@dlhr> set autot off
9LHR@dlhr> col SQL_TEXT format a100
10LHR@dlhr> select distinct a.SQL_ID,a.SQL_TEXT from v$sql a
11 2 WHERE a.SQL_TEXT like '%SELECT description FROM tb_spm_test_lhr WHERE id = 100%'
12 3 and a.SQL_TEXT not like '%v$sql%'
13 4 AND sql_text NOT LIKE '%EXPLAIN%';
14
15SQL_ID SQL_TEXT
16------------- ----------------------------------------------------------------------------------------------------
17garkwg3yy2ram SELECT description FROM tb_spm_test_lhr WHERE id = 100
18
19----使用SQL_ID 从cursor cache中手工捕获执行计划：
20SET SERVEROUTPUT ON
21DECLARE
22 l_plans_loaded PLS_INTEGER;
23BEGIN
24 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
25 sql_id => '&sql_id');
26 DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
27END;
28/
29-- --使用DBA_SQL_PLAN_BASELINES视图查看SPM 信息：
30col sql_handle for a35
31col plan_name for a35
32set lin 300
33SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
34FROM dba_sql_plan_baselines
35WHERE sql_text LIKE '%tb_spm_test_lhr%'
36AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
37
38--刷新Share Pool，使下次SQL 执行时必须进行硬解析：
39ALTER SYSTEM FLUSH SHARED_POOL;
40
41LHR@dlhr> SET SERVEROUTPUT ON
42LHR@dlhr> DECLARE
43 2 l_plans_loaded PLS_INTEGER;
44 3 BEGIN
45 4 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(
46 5 sql_id => '&sql_id');
47 6 DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded);
48 7 END;
49 8 /
50Enter value for sql_id: garkwg3yy2ram
51old 5: sql_id => '&sql_id');
52new 5: sql_id => 'garkwg3yy2ram');
53Plans Loaded: 1
54
55PL/SQL procedure successfully completed.
56
57
58LHR@dlhr> col sql_handle for a35
59LHR@dlhr> col plan_name for a35
60LHR@dlhr> set lin 300
61LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted
62 2 FROM dba_sql_plan_baselines
63 3 WHERE sql_text LIKE '%tb_spm_test_lhr%'
64 4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
65
66SQL_HANDLE PLAN_NAME ENA ACC
67----------------------------------- ----------------------------------- --- ---
68SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 YES YES
69
70LHR@dlhr> ALTER SYSTEM FLUSH SHARED_POOL;
71
72System altered.
73
74LHR@dlhr> set autot trace
75SELECT description FROM tb_spm_test_lhr WHERE id = 100;
76LHR@dlhr>
77
78Execution Plan
79----------------------------------------------------------
80Plan hash value: 2196561629
81
82-------------------------------------------------------------------------------------
83| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
84-------------------------------------------------------------------------------------
85| 0 | SELECT STATEMENT | | 1 | 40 | 13 (0)| 00:00:01 |
86|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 40 | 13 (0)| 00:00:01 |
87-------------------------------------------------------------------------------------
88
89Predicate Information (identified by operation id):
90---------------------------------------------------
91
92 1 - filter("ID"=100)
93
94Note
95-----
96 - dynamic sampling used for this statement (level=2)
97 - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement
98
99
100Statistics
101----------------------------------------------------------
102 555 recursive calls
103 16 db block gets
104 667 consistent gets
105 0 physical reads
106 3056 redo size
107 546 bytes sent via SQL*Net to client
108 519 bytes received via SQL*Net from client
109 2 SQL*Net roundtrips to/from client
110 32 sorts (memory)
111 0 sorts (disk)
112 1 rows processed
113
114
115--创建索引，收集统计信息，并查询相同的SQL:
116CREATE INDEX spm_test_tab_idx ON tb_spm_test_lhr(id);
117EXEC DBMS_STATS.gather_table_stats(USER,'tb_spm_test_lhr', cascade=>TRUE);
118
119set autot trace
120SELECT description FROM tb_spm_test_lhr WHERE id = 100;
121
122
123LHR@dlhr> CREATE INDEX spm_test_tab_idx ON tb_spm_test_lhr(id);
124Index created.
125
126LHR@dlhr> EXEC DBMS_STATS.gather_table_stats(USER,'tb_spm_test_lhr', cascade=>TRUE);
127
128
129PL/SQL procedure successfully completed.
130
131LHR@dlhr>
132LHR@dlhr>
133LHR@dlhr>
134LHR@dlhr> set autot trace
135LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100;
136
137
138
139Execution Plan
140----------------------------------------------------------
141Plan hash value: 2196561629
142
143-------------------------------------------------------------------------------------
144| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
145-------------------------------------------------------------------------------------
146| 0 | SELECT STATEMENT | | 1 | 25 | 13 (0)| 00:00:01 |
147|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 25 | 13 (0)| 00:00:01 |
148-------------------------------------------------------------------------------------
149
150Predicate Information (identified by operation id):
151---------------------------------------------------
152
153 1 - filter("ID"=100)
154
155Note
156-----
157 - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement
158
159
160Statistics
161----------------------------------------------------------
162 640 recursive calls
163 39 db block gets
164 493 consistent gets
165 2 physical reads
166 12268 redo size
167 546 bytes sent via SQL*Net to client
168 519 bytes received via SQL*Net from client
169 2 SQL*Net roundtrips to/from client
170 10 sorts (memory)
171 0 sorts (disk)
172 1 rows processed
173
174
175--这里我们创建了索引，但是这里还是走的全表扫描，这里使用索引明显才是最优的方案。
176--查看SPM 视图：
177set autot off
178col sql_handle for a35
179col plan_name for a35
180set lin 300
181SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
182FROM dba_sql_plan_baselines
183WHERE sql_text LIKE '%tb_spm_test_lhr%'
184AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
185
186LHR@dlhr> set autot off
187LHR@dlhr> col sql_handle for a35
188LHR@dlhr> col plan_name for a35
189LHR@dlhr> set lin 300
190LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted
191 2 FROM dba_sql_plan_baselines
192 3 WHERE sql_text LIKE '%tb_spm_test_lhr%'
193 4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
194
195SQL_HANDLE PLAN_NAME ENA ACC
196----------------------------------- ----------------------------------- --- ---
197SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 YES YES
198SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws38b725570 YES NO
199
200--通过BASELINES查询的结果，可以看到SQL产生了两条执行计划，但是最优的执行计划并没有被标记为ACCEPT，所以没有使用。
201--下边我们演化执行计划：演化就是将cost低的执行计划标记为accept：
202LHR@dlhr> SET LONG 10000
203LHR@dlhr> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&sql_handle') FROM dual;
204Enter value for sql_handle: SQL_4f19d3cf57be7303
205old 1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => '&sql_handle') FROM dual
206new 1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_4f19d3cf57be7303') FROM dual
207
208DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_4F19D3CF57BE7303')
209--------------------------------------------------------------------------------
210
211-------------------------------------------------------------------------------
212 Evolve SQL Plan Baseline Report
213-------------------------------------------------------------------------------
214
215Inputs:
216-------
217 SQL_HANDLE = SQL_4f19d3cf57be7303
218 PLAN_NAME =
219 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
220 VERIFY = YES
221 COMMIT = YES
222
223Plan: SQL_PLAN_4y6fmtxbvwws38b725570
224------------------------------------
225 Plan was verified: Time used .018 seconds.
226 Plan passed performance criterion: 15 times better than baseline plan.
227 Plan was changed to an accepted plan.
228
229 Baseline Plan Test Plan Stats Ratio
230 ------------- --------- -----------
231 Execution Status: COMPLETE COMPLETE
232 Rows Processed: 1 1
233 Elapsed Time(ms): .308 .025 12.32
234 CPU Time(ms): .164 .015 10.93
235 Buffer Gets: 45 3 15
236 Physical Read Requests: 0 0
237 Physical Write Requests: 0 0
238 Physical Read Bytes: 0 0
239 Physical Write Bytes: 0 0
240 Executions: 1 1
241
242-------------------------------------------------------------------------------
243 Report Summary
244-------------------------------------------------------------------------------
245Number of plans verified: 1
246Number of plans accepted: 1
247
248
249--再次查看DBA_SQL_PLAN_BASELINES视图：
250set autot off
251col sql_handle for a35
252col plan_name for a35
253set lin 300
254SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
255FROM dba_sql_plan_baselines
256WHERE sql_text LIKE '%tb_spm_test_lhr%'
257AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
258
259LHR@dlhr> set autot off
260LHR@dlhr> col sql_handle for a35
261LHR@dlhr> col plan_name for a35
262LHR@dlhr> set lin 300
263LHR@dlhr> SELECT sql_handle, plan_name,enabled, accepted
264 2 FROM dba_sql_plan_baselines
265 3 WHERE sql_text LIKE '%tb_spm_test_lhr%'
266 4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
267
268SQL_HANDLE PLAN_NAME ENA ACC
269----------------------------------- ----------------------------------- --- ---
270SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 YES YES
271SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws38b725570 YES YES
272
273--再次执行SQL:
274set autot trace
275SELECT description FROM tb_spm_test_lhr WHERE id = 100;
276
277LHR@dlhr> set autot trace
278LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100;
279
280
281Execution Plan
282----------------------------------------------------------
283Plan hash value: 2587945646
284
285------------------------------------------------------------------------------------------------
286| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
287------------------------------------------------------------------------------------------------
288| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
289| 1 | TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR | 1 | 25 | 2 (0)| 00:00:01 |
290|* 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 (0)| 00:00:01 |
291------------------------------------------------------------------------------------------------
292
293Predicate Information (identified by operation id):
294---------------------------------------------------
295
296 2 - access("ID"=100)
297
298Note
299-----
300 - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws38b725570" used for this statement
301
302
303Statistics
304----------------------------------------------------------
305 13 recursive calls
306 14 db block gets
307 18 consistent gets
308 0 physical reads
309 3048 redo size
310 553 bytes sent via SQL*Net to client
311 519 bytes received via SQL*Net from client
312 2 SQL*Net roundtrips to/from client
313 0 sorts (memory)
314 0 sorts (disk)
315 1 rows processed
这次正确的使用了索引。因为只有标记为ENABLE和ACCEPT的plan才可以被使用。
下面示例将我们的第一个走全表扫描的执行计划标记为fixed。标记为fixed的执行计划会被优先使用。FIXED表示优化程序仅考虑标记为FIXED的计划，而不考虑其它计划。例如，如果有10个基线计划，其中的三个计划被标记为FIXED，则优化程序将仅使用这三个计划中的最佳计划，而忽略其它所有计划。如果某个SQL计划基线至少包含一个已启用的已修复计划，则该SQL计划基线就是FIXED的。如果在修复的SQL计划基线中添加了新计划，则在手动将这些新计划声明为FIXED之前，无法使用这些新计划。
1set autot off
2select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'));
3
4SET SERVEROUTPUT ON
5DECLARE
6 l_plans_altered PLS_INTEGER;
7BEGIN
8 l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
9 sql_handle => '&sql_handle',
10 plan_name => '&plan_name',
11 attribute_name => 'fixed',
12 attribute_value => 'YES');
13
14 DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
15END;
16/
17
18LHR@dlhr> SET SERVEROUTPUT ON
19LHR@dlhr> DECLARE
20 2 l_plans_altered PLS_INTEGER;
21 3 BEGIN
22 4 l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
23 5 sql_handle => '&sql_handle',
24 6 plan_name => '&plan_name',
25 7 attribute_name => 'fixed',
26 8 attribute_value => 'YES');
27 9
28 10 DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);
29 11 END;
30 12 /
31Enter value for sql_handle: SQL_4f19d3cf57be7303
32old 5: sql_handle => '&sql_handle',
33new 5: sql_handle => 'SQL_4f19d3cf57be7303',
34Enter value for plan_name: SQL_PLAN_4y6fmtxbvwws3184920d2
35old 6: plan_name => '&plan_name',
36new 6: plan_name => 'SQL_PLAN_4y6fmtxbvwws3184920d2',
37Plans Altered: 1
38
39PL/SQL procedure successfully completed.
40
41--验证：
42set autot off
43col sql_handle for a35
44col plan_name for a35
45set lin 300
46SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
47FROM dba_sql_plan_baselines
48WHERE sql_text LIKE '%tb_spm_test_lhr%'
49AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
50
51LHR@dlhr> set autot off
52LHR@dlhr> select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'));
53Enter value for sql_handle: SQL_4f19d3cf57be7303
54old 1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => '&sql_handle', format => 'basic'))
55new 1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => 'SQL_4f19d3cf57be7303', format => 'basic'))
56
57PLAN_TABLE_OUTPUT
58--------------------------------------------------------------------------------------
59
60--------------------------------------------------------------------------------
61SQL handle: SQL_4f19d3cf57be7303
62SQL text: SELECT description FROM tb_spm_test_lhr WHERE id = 100
63--------------------------------------------------------------------------------
64
65--------------------------------------------------------------------------------
66Plan name: SQL_PLAN_4y6fmtxbvwws3184920d2 Plan id: 407445714
67Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD
68--------------------------------------------------------------------------------
69
70Plan hash value: 2196561629
71
72---------------------------------------------
73| Id | Operation | Name |
74---------------------------------------------
75| 0 | SELECT STATEMENT | |
76| 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR |
77---------------------------------------------
78
79--------------------------------------------------------------------------------
80Plan name: SQL_PLAN_4y6fmtxbvwws38b725570 Plan id: 2339526000
81Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
82--------------------------------------------------------------------------------
83
84Plan hash value: 2587945646
85
86--------------------------------------------------------
87| Id | Operation | Name |
88--------------------------------------------------------
89| 0 | SELECT STATEMENT | |
90| 1 | TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR |
91| 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX |
92--------------------------------------------------------
93
9434 rows selected.
95
96LHR@dlhr> set autot off
97LHR@dlhr> col sql_handle for a35
98LHR@dlhr> col plan_name for a35
99LHR@dlhr> set lin 300
100LHR@dlhr> SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed
101 2 FROM dba_sql_plan_baselines
102 3 WHERE sql_text LIKE '%tb_spm_test_lhr%'
103 4 AND sql_text NOT LIKE'%dba_sql_plan_baselines%';
104
105SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX
106----------------------------------- ----------------------------------- -------------- --- --- ---
107SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 MANUAL-LOAD YES YES YES
108SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws38b725570 AUTO-CAPTURE YES YES NO
109
110
111
112--再次查看我们之前的SQL:
113set autot trace
114SELECT description FROM tb_spm_test_lhr WHERE id = 100;
115
116LHR@dlhr> set autot trace
117LHR@dlhr> SELECT description FROM tb_spm_test_lhr WHERE id = 100;
118
119
120Execution Plan
121----------------------------------------------------------
122Plan hash value: 2196561629
123
124-------------------------------------------------------------------------------------
125| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
126-------------------------------------------------------------------------------------
127| 0 | SELECT STATEMENT | | 1 | 25 | 13 (0)| 00:00:01 |
128|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 25 | 13 (0)| 00:00:01 |
129-------------------------------------------------------------------------------------
130
131Predicate Information (identified by operation id):
132---------------------------------------------------
133
134 1 - filter("ID"=100)
135
136Note
137-----
138 - SQL plan baseline "SQL_PLAN_4y6fmtxbvwws3184920d2" used for this statement
139
140
141Statistics
142----------------------------------------------------------
143 6 recursive calls
144 8 db block gets
145 46 consistent gets
146 0 physical reads
147 0 redo size
148 546 bytes sent via SQL*Net to client
149 519 bytes received via SQL*Net from client
150 2 SQL*Net roundtrips to/from client
151 0 sorts (memory)
152 0 sorts (disk)
153 1 rows processed
这里已经走了全表扫描，根据前边的示例，我们知道这里走索引会更优，但因为我们将走全表扫描的执行计划设置为fixed，所以优先使用这个执行计划。
& 说明：
有关固定执行计划的具体过程及更多的知识可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2107604/
本文选自《Oracle程序员面试笔试宝典》，作者：李华荣。
---------------优质麦课------------
详细内容可以添加麦老师微信或QQ私聊。
● 本文作者：小麦苗，只专注于数据库的技术，更注重技术的运用
● 作者博客地址：http://blog.itpub.net/26736162/abstract/1/
● 本系列题目来源于作者的学习笔记，部分整理自网络，若有侵权或不当之处还请谅解
● 版权所有，欢迎分享本文，转载请保留出处
● QQ：646634621 QQ群：618766405
● 提供OCP、OCM和高可用部分最实用的技能培训
● 题目解答若有不当之处，还望各位朋友批评指正，共同进步
长按下图识别二维码或微信扫描下图二维码来关注小麦苗的微信公众号：xiaomaimiaolhr，学习最实用的数据库技术。
最后修改时间：2020-01-10 18:17:40
文章转载自DB宝，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
