【最佳化】SPM(下）手工捕獲sql執行計劃並檢視演進情況
手工捕獲sql執行計劃並檢視演進情況
前期準備：Sys下執行操作刪除baseline裡以有的執行計劃（根據需要多次執行）
--sys操作
declare
my_plans pls_integer;
begin
my_plans :=DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => 'SQL_1c32abf260291c48', plan_name => 'SQL_PLAN_1scpby9h2k728c6c83284')
;
end;
/
再刪除employees_bak。
HR@ORA11GR2>drop table employees_bak purge;
Table dropped.
--驗證檢視balinese
SYS@ORA11GR2>select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';
no rows selected
--正式操作：
1.關閉自動捕獲引數
HR@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>show parameter optimizer_capture_sql_plan_baselines
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
SYS@ORA11GR2> alter system set optimizer_capture_sql_plan_baselines=false;
System altered.
建表：
HR@ORA11GR2> create table employees_bak as select distinct last_name from employees;
Table created.
2.執行sql模擬業務（HR使用者)
HR@ORA11GR2>select * from employees_bak where last_name='Vargas';
LAST_NAME
-------------------------
Vargas
3.檢視cursor cache中的執行計劃資訊(sys使用者)
--sys使用者操作
col SQL_TEXT for a55
select sql_id,sql_text from v$sql where sql_text like 'select * from employees_bak where last_name=%';
SQL_ID SQL_TEXT
------------- -------------------------------------------------------
99yvub515tr63 select * from employees_bak where last_name='Vargas'
4.檢視baseline中的資訊
--sys使用者操作
select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';
no rows selected
5.手工裝載執行計劃再次檢視baseline
--sys使用者操作
declare
my_plans pls_integer;
begin
my_plans := dbms_spm.load_plans_from_cursor_cache( sql_id => '99yvub515tr63');
end;
/
PL/SQL procedure successfully completed.
6.再次檢視Baseline資訊：
col sql_handle for a20
col sql_text for a15
col plan_name for a15
col created for a10
col last_modified for a10
col last_executed for a10
col last_verified for a10
col version for a10
set pagesize 1000
select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION
-------------------- --------------- --------------- -------------- ----------
CREATED LAST_MODIF LAST_EXECU LAST_VERIF ENA ACC FIX
---------- ---------- ---------- ---------- --- --- ---
SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby MANUAL-LOAD 11.2.0.4.0
mployees_bak wh 9h2k728c6c83284
ere last_name='
Vargas'
13-OCT-16 13-OCT-16 YES YES NO
08.38.38.0 08.38.38.0
00000 PM 00000 PM
查詢得：手工load（manual-load模式）的執行計劃直接已經被演進到baseline裡了！
7.透過執行sql進行驗證，以選用baseline裡的執行計劃SQL_PLAN_1scpby9h2k728c6c83284：
--hr使用者執行：
set autot on
select * from employees_bak where last_name='Vargas';
LAST_NAME
-------------------------
Vargas
Execution Plan
----------------------------------------------------------
Plan hash value: 3604099949
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:0
1 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES_BAK | 1 | 14 | 3 (0)| 00:00:0
1 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='Vargas')
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_1scpby9h2k728c6c83284" used for this statement
Statistics
----------------------------------------------------------
18 recursive calls
13 db block gets
26 consistent gets
0 physical reads
3072 redo size
531 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
8.對sql進行最佳化，建立索引並重新執行sql
--hr使用者操作
set autot off
create index idx_employees_bak on employees_bak(last_name);
Index created.
--重新執行sql：
select * from employees_bak where last_name='Vargas';
LAST_NAME
-------------------------
Vargas
手工捕獲模式下，對同一條SQL的最佳化後的查詢，即最佳化後的sql的演進模式以變為自動捕獲AUTO-CAPTURE，且僅需執行一次就被捕獲進baseline。
9.檢視Baseline資訊：
--sys使用者
select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION
-------------------- --------------- --------------- -------------- ----------
CREATED LAST_MODIF LAST_EXECU LAST_VERIF ENA ACC FIX
---------- ---------- ---------- ---------- --- --- ---
SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby MANUAL-LOAD 11.2.0.4.0
mployees_bak wh 9h2k728c6c83284
ere last_name='
Vargas'
13-OCT-16 13-OCT-16 13-OCT-16 YES YES NO
08.38.38.0 08.38.38.0 08.41.39.0
00000 PM 00000 PM 00000 PM
SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby AUTO-CAPTURE 11.2.0.4.0
mployees_bak wh 9h2k728d8e34edf
ere last_name='
Vargas'
13-OCT-16 13-OCT-16 YES NO NO
08.43.28.0 08.43.28.0
00000 PM 00000 PM
注意：可以看到手工load執行計劃模式，當相同的sql執行再被執行，其執行計劃以自動捕獲的方式被放到baseline裡，但是第二個執行計劃還沒有被演進，即其執行計劃不能被複用，實際上是存放在sqlplan history而沒有真正意義上放進baseline裡。
10.驗證檢視執行的sql選擇的執行計劃：
HR@ORA11GR2>set autot trace
HR@ORA11GR2>select * from employees_bak where last_name='Vargas';
Execution Plan
----------------------------------------------------------
Plan hash value: 3604099949
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 42 | 3 (0)| 00:00:0
1 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES_BAK | 3 | 42 | 3 (0)| 00:00:0
1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='Vargas')
Note
-----
- SQL plan baseline "SQL_PLAN_1scpby9h2k728c6c83284" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到執行計劃是去的第一個SQL plan baseline "SQL_PLAN_1scpby9h2k728c6c83284"
HR@ORA11GR2>set autot off;
11.演進同一sql的第二個執行計劃，以使其執行計劃能夠複用:
--sys操作
set serveroutput on
set long 10000
declare
report clob;
begin
report := dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SQL_1c32abf260291c48');
dbms_output.put_line(report);
end;
/
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_1c32abf260291c48
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan:
SQL_PLAN_1scpby9h2k728d8e34edf
------------------------------------
Plan was
verified: Time used .02 seconds.
Plan passed performance criterion: 3 times
better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------ --------- -----------
Execution Status:
COMPLETE COMPLETE
Rows Processed: 1
1
Elapsed Time(ms): .02 .014 1.43
CPU Time(ms): 0 0
Buffer Gets:
3 1 3
Physical Read Requests: 0
0
Physical Write Requests: 0 0
Physical Read
Bytes: 0 0
Physical Write Bytes:
0 0
Executions: 1
1
-----------------------------------------------------------------------------
Report
Summary
------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1
PL/SQL procedure successfully completed.
12.再次檢視baseline驗證：
--sys:
select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION
-------------------- --------------- --------------- -------------- ----------
CREATED LAST_MODIF LAST_EXECU LAST_VERIF ENA ACC FIX
---------- ---------- ---------- ---------- --- --- ---
SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby MANUAL-LOAD 11.2.0.4.0
mployees_bak wh 9h2k728c6c83284
ere last_name='
Vargas'
13-OCT-16 13-OCT-16 13-OCT-16 YES YES NO
08.38.38.0 08.38.38.0 08.41.39.0
00000 PM 00000 PM 00000 PM
SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby AUTO-CAPTURE 11.2.0.4.0
mployees_bak wh 9h2k728d8e34edf
ere last_name='
Vargas'
13-OCT-16 13-OCT-16 13-OCT-16 YES YES NO
08.43.28.0 08.51.15.0 08.51.15.0
00000 PM 00000 PM 00000 PM
檢視相同sql的第二次執行計劃已經被演進到baseline裡；
13.驗證檢視執行的sql選擇的執行計劃：
HR@ORA11GR2>set autot trace
HR@ORA11GR2>select * from employees_bak where last_name='Vargas';
Execution Plan
----------------------------------------------------------
Plan hash value: 3689813232
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 1 (0)| 00:0
0:01 |
|* 1 | INDEX RANGE SCAN| IDX_EMPLOYEES_BAK | 1 | 14 | 1 (0)| 00:0
0:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("LAST_NAME"='Vargas')
Note
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_1scpby9h2k728d8e34edf" used for this statement
Statistics
----------------------------------------------------------
18 recursive calls
13 db block gets
26 consistent gets
0 physical reads
3064 redo size
531 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
檢視相同sql的執行計劃走的是索引掃描，其執行計劃為SQL plan baseline "SQL_PLAN_1scpby9h2k728d8e34edf"，即baseline裡的同一sql的最優sql_plan.
前期準備：Sys下執行操作刪除baseline裡以有的執行計劃（根據需要多次執行）
--sys操作
declare
my_plans pls_integer;
begin
my_plans :=DBMS_SPM.DROP_SQL_PLAN_BASELINE(sql_handle => 'SQL_1c32abf260291c48', plan_name => 'SQL_PLAN_1scpby9h2k728c6c83284')
;
end;
/
再刪除employees_bak。
HR@ORA11GR2>drop table employees_bak purge;
Table dropped.
--驗證檢視balinese
SYS@ORA11GR2>select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';
no rows selected
--正式操作：
1.關閉自動捕獲引數
HR@ORA11GR2>conn / as sysdba
Connected.
SYS@ORA11GR2>show parameter optimizer_capture_sql_plan_baselines
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean TRUE
SYS@ORA11GR2> alter system set optimizer_capture_sql_plan_baselines=false;
System altered.
建表：
HR@ORA11GR2> create table employees_bak as select distinct last_name from employees;
Table created.
2.執行sql模擬業務（HR使用者)
HR@ORA11GR2>select * from employees_bak where last_name='Vargas';
LAST_NAME
-------------------------
Vargas
3.檢視cursor cache中的執行計劃資訊(sys使用者)
--sys使用者操作
col SQL_TEXT for a55
select sql_id,sql_text from v$sql where sql_text like 'select * from employees_bak where last_name=%';
SQL_ID SQL_TEXT
------------- -------------------------------------------------------
99yvub515tr63 select * from employees_bak where last_name='Vargas'
4.檢視baseline中的資訊
--sys使用者操作
select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';
no rows selected
5.手工裝載執行計劃再次檢視baseline
--sys使用者操作
declare
my_plans pls_integer;
begin
my_plans := dbms_spm.load_plans_from_cursor_cache( sql_id => '99yvub515tr63');
end;
/
PL/SQL procedure successfully completed.
6.再次檢視Baseline資訊：
col sql_handle for a20
col sql_text for a15
col plan_name for a15
col created for a10
col last_modified for a10
col last_executed for a10
col last_verified for a10
col version for a10
set pagesize 1000
select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION
-------------------- --------------- --------------- -------------- ----------
CREATED LAST_MODIF LAST_EXECU LAST_VERIF ENA ACC FIX
---------- ---------- ---------- ---------- --- --- ---
SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby MANUAL-LOAD 11.2.0.4.0
mployees_bak wh 9h2k728c6c83284
ere last_name='
Vargas'
13-OCT-16 13-OCT-16 YES YES NO
08.38.38.0 08.38.38.0
00000 PM 00000 PM
查詢得：手工load（manual-load模式）的執行計劃直接已經被演進到baseline裡了！
7.透過執行sql進行驗證，以選用baseline裡的執行計劃SQL_PLAN_1scpby9h2k728c6c83284：
--hr使用者執行：
set autot on
select * from employees_bak where last_name='Vargas';
LAST_NAME
-------------------------
Vargas
Execution Plan
----------------------------------------------------------
Plan hash value: 3604099949
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 3 (0)| 00:00:0
1 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES_BAK | 1 | 14 | 3 (0)| 00:00:0
1 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='Vargas')
Note
-----
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_1scpby9h2k728c6c83284" used for this statement
Statistics
----------------------------------------------------------
18 recursive calls
13 db block gets
26 consistent gets
0 physical reads
3072 redo size
531 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
8.對sql進行最佳化，建立索引並重新執行sql
--hr使用者操作
set autot off
create index idx_employees_bak on employees_bak(last_name);
Index created.
--重新執行sql：
select * from employees_bak where last_name='Vargas';
LAST_NAME
-------------------------
Vargas
手工捕獲模式下，對同一條SQL的最佳化後的查詢，即最佳化後的sql的演進模式以變為自動捕獲AUTO-CAPTURE，且僅需執行一次就被捕獲進baseline。
9.檢視Baseline資訊：
--sys使用者
select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION
-------------------- --------------- --------------- -------------- ----------
CREATED LAST_MODIF LAST_EXECU LAST_VERIF ENA ACC FIX
---------- ---------- ---------- ---------- --- --- ---
SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby MANUAL-LOAD 11.2.0.4.0
mployees_bak wh 9h2k728c6c83284
ere last_name='
Vargas'
13-OCT-16 13-OCT-16 13-OCT-16 YES YES NO
08.38.38.0 08.38.38.0 08.41.39.0
00000 PM 00000 PM 00000 PM
SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby AUTO-CAPTURE 11.2.0.4.0
mployees_bak wh 9h2k728d8e34edf
ere last_name='
Vargas'
13-OCT-16 13-OCT-16 YES NO NO
08.43.28.0 08.43.28.0
00000 PM 00000 PM
注意：可以看到手工load執行計劃模式，當相同的sql執行再被執行，其執行計劃以自動捕獲的方式被放到baseline裡，但是第二個執行計劃還沒有被演進，即其執行計劃不能被複用，實際上是存放在sqlplan history而沒有真正意義上放進baseline裡。
10.驗證檢視執行的sql選擇的執行計劃：
HR@ORA11GR2>set autot trace
HR@ORA11GR2>select * from employees_bak where last_name='Vargas';
Execution Plan
----------------------------------------------------------
Plan hash value: 3604099949
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 42 | 3 (0)| 00:00:0
1 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES_BAK | 3 | 42 | 3 (0)| 00:00:0
1 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='Vargas')
Note
-----
- SQL plan baseline "SQL_PLAN_1scpby9h2k728c6c83284" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
531 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看到執行計劃是去的第一個SQL plan baseline "SQL_PLAN_1scpby9h2k728c6c83284"
HR@ORA11GR2>set autot off;
11.演進同一sql的第二個執行計劃，以使其執行計劃能夠複用:
--sys操作
set serveroutput on
set long 10000
declare
report clob;
begin
report := dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SQL_1c32abf260291c48');
dbms_output.put_line(report);
end;
/
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SQL_1c32abf260291c48
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan:
SQL_PLAN_1scpby9h2k728d8e34edf
------------------------------------
Plan was
verified: Time used .02 seconds.
Plan passed performance criterion: 3 times
better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------ --------- -----------
Execution Status:
COMPLETE COMPLETE
Rows Processed: 1
1
Elapsed Time(ms): .02 .014 1.43
CPU Time(ms): 0 0
Buffer Gets:
3 1 3
Physical Read Requests: 0
0
Physical Write Requests: 0 0
Physical Read
Bytes: 0 0
Physical Write Bytes:
0 0
Executions: 1
1
-----------------------------------------------------------------------------
Report
Summary
------------------------------------------------------------------------
Number of plans verified: 1
Number of plans accepted: 1
PL/SQL procedure successfully completed.
12.再次檢視baseline驗證：
--sys:
select sql_handle,sql_text,plan_name,origin,version,created,last_modified,last_executed,last_verified,enabled,accepted,fixed from dba_sql_plan_baselines where sql_text like 'select * from employees_bak where last_name=%';
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN VERSION
-------------------- --------------- --------------- -------------- ----------
CREATED LAST_MODIF LAST_EXECU LAST_VERIF ENA ACC FIX
---------- ---------- ---------- ---------- --- --- ---
SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby MANUAL-LOAD 11.2.0.4.0
mployees_bak wh 9h2k728c6c83284
ere last_name='
Vargas'
13-OCT-16 13-OCT-16 13-OCT-16 YES YES NO
08.38.38.0 08.38.38.0 08.41.39.0
00000 PM 00000 PM 00000 PM
SQL_1c32abf260291c48 select * from e SQL_PLAN_1scpby AUTO-CAPTURE 11.2.0.4.0
mployees_bak wh 9h2k728d8e34edf
ere last_name='
Vargas'
13-OCT-16 13-OCT-16 13-OCT-16 YES YES NO
08.43.28.0 08.51.15.0 08.51.15.0
00000 PM 00000 PM 00000 PM
檢視相同sql的第二次執行計劃已經被演進到baseline裡；
13.驗證檢視執行的sql選擇的執行計劃：
HR@ORA11GR2>set autot trace
HR@ORA11GR2>select * from employees_bak where last_name='Vargas';
Execution Plan
----------------------------------------------------------
Plan hash value: 3689813232
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 | 1 (0)| 00:0
0:01 |
|* 1 | INDEX RANGE SCAN| IDX_EMPLOYEES_BAK | 1 | 14 | 1 (0)| 00:0
0:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("LAST_NAME"='Vargas')
Note
- dynamic sampling used for this statement (level=2)
- SQL plan baseline "SQL_PLAN_1scpby9h2k728d8e34edf" used for this statement
Statistics
----------------------------------------------------------
18 recursive calls
13 db block gets
26 consistent gets
0 physical reads
3064 redo size
531 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
檢視相同sql的執行計劃走的是索引掃描，其執行計劃為SQL plan baseline "SQL_PLAN_1scpby9h2k728d8e34edf"，即baseline裡的同一sql的最優sql_plan.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31397003/viewspace-2126496/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【最佳化】SPM(上）自動捕獲sql執行計劃並演進SQL
- 檢視sql執行計劃SQL
- 微課sql最佳化(11) 、如何檢視執行計劃SQL
- 使用SPM和STA進行固定執行計劃
- 如何檢視SQL的執行計劃SQL
- 檢視SQL的執行計劃方法SQL
- 使用PL/SQL檢視執行計劃SQL
- Oracle 檢視SQL的執行計劃OracleSQL
- 檢視sql執行計劃--set autotraceSQL
- 【檢視】使用V$SQL_PLAN檢視獲取曾經執行過的SQL語句執行計劃SQL
- 檢視sql執行計劃方法彙總SQL
- MySQL 5.7 檢視理解SQL執行計劃MySql
- 【Explain Plan】檢視SQL的執行計劃AISQL
- 檢視執行計劃
- 根據SQL_ID檢視執行計劃SQL
- 多種方法檢視Oracle SQL執行計劃OracleSQL
- 檢視sql執行計劃--set autotrace [final]SQL
- oracle10g 檢視SQL執行計劃OracleSQL
- 透過查詢檢視sql執行計劃SQL
- Oracle檢視正在執行的SQL以及執行計劃分析OracleSQL
- 獲取SQL執行計劃SQL
- 【SPM】Oracle如何固定執行計劃Oracle
- 測試在cursor_sharing為force情況下執行計劃的共享情況
- 【sql調優之執行計劃】獲取執行計劃SQL
- 檢視執行計劃（一）
- 檢視執行計劃（二）
- 物化檢視重新整理遞迴SQL獲取執行計劃報錯遞迴SQL
- 微課sql最佳化(9)、如何獲取執行計劃SQL
- 檢視Oracle SQL執行計劃方法比較、分析OracleSQL
- SQLPLUS檢視oracle sql執行計劃命令SQLOracle
- 檢視sql 執行計劃的歷史變更SQL
- 【最佳化】檢視歷史的執行計劃之檢視AWR報告
- 檢視SQL執行計劃的方法(有待於進一步補充)SQL
- 執行計劃__獲取方法、檢視執行順序、統計資訊詳解
- Oracle檢視執行計劃（五）Oracle
- Oracle檢視執行計劃（六）Oracle
- Oracle檢視執行計劃（一）Oracle
- Oracle檢視執行計劃（二）Oracle