[20120803]11G SPM的学习1.txt
开始学习SQL Plan Management(SPM) ,11G开始提供SPM，在10g下我经常使用sql profile看一些bad sql语句，sql profile我觉得已经做的很好，
有时候能够提供很好的建议。我开始学习SPM的时候感觉不习惯（也许是因为在toad下使用sql profile很简单）为什么oracle还有搞出SPM来，慢
慢看资料，才明白其中一些细节。
我看过别人在从8i升级到9i的时候，出现性能波动，里面的一些sql语句执行计划发生了变化。我自己也遇到从8i升级到10g时出现sql语句性能问
题，10g下的自动分析导致建立了一些字段建立不必要的直方图。
SPM对于sql执行计划的稳定起到很好的作用，对于升级和稳定sql语句起到很好的作用。
通过一些例子来学习，我的测试环境如下：
SQL> select * from v$version ;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
1.SPM设置与这两个参数有关：
SQL> show parameter plan_base
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
以上两个参数是缺省设置，如果设置optimizer_capture_sql_plan_baselines=true,可以让oracle自动捕获与建立sql plan baseline。
一般缺省设置是false。
optimizer_use_sql_plan_baselines=true,当查询sql分析时，oracle产生几个执行计划,对比sql plan baseline是否存在，如果存在
，oracle直接选取该计划。
建立测试表：
create table t as select rownum id , cast(dbms_random.string('a',6 ) as varchar2(10)) name from dual connect by level
--分析表
BEGIN
SYS.DBMS_STATS.GATHER_TABLE_STATS (
OwnName => user
,TabName => 'T'
,Estimate_Percent => NULL
,Method_Opt => 'FOR ALL COLUMNS SIZE 1 '
,Degree => 4
,Cascade => TRUE
,No_Invalidate => TRUE);
END;
/
2.建立sql plan baseline:
也许有许多方法，一般方法如下：
方法1:
设置optimizer_capture_sql_plan_baselines＝true
alter session set optimizer_capture_sql_plan_baselines＝true;
执行sql语句两次以上！
alter session set optimizer_capture_sql_plan_baselines＝false;
方法2:
使用dbms_spm包的load_plans_from_cursor_cache函数导入，我测试使用该方法。
SQL> alter system flush shared_pool;
SQL> variable x number;
SQL> exec :x :=100;
PL/SQL procedure successfully completed.
SQL> select * from t where id=:x;
ID NAME
---------- ----------
100 lAGCjs
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3yxwagyspybax, child number 0
-------------------------------------
select * from t where id=:x
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 69 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 69 (2)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:X)
--知道sql_id=3yxwagyspybax,plan_hash_value=1601196873
var v_basenum number;
exec :v_basenum:=dbms_spm.load_plans_from_cursor_cache(sql_id => '3yxwagyspybax',plan_hash_value =>1601196873 );
SQL> column signature format 9999999999999999999999
SQL> set serveroutput on
SQL> exec print_table('select * from dba_sql_plan_baselines') ;
SIGNATURE : 11842951964357158308
SQL_HANDLE : SYS_SQL_a45a9e109f85e5a4
SQL_TEXT : select * from t where id=:x
PLAN_NAME : SQL_PLAN_a8qny22gsbtd494ecae5c
CREATOR : SCOTT
ORIGIN : MANUAL-LOAD
PARSING_SCHEMA_NAME : SCOTT
DESCRIPTION :
VERSION : 11.2.0.1.0
CREATED : 2012-08-03 08:54:06.000000
LAST_MODIFIED : 2012-08-03 08:54:06.000000
LAST_EXECUTED :
LAST_VERIFIED :
ENABLED : YES
ACCEPTED : YES
FIXED : NO
AUTOPURGE : YES
OPTIMIZER_COST : 69
MODULE : SQL*Plus
ACTION :
EXECUTIONS : 1
ELAPSED_TIME : 7152
CPU_TIME : 6999
BUFFER_GETS : 273
DISK_READS : 0
DIRECT_WRITES : 0
ROWS_PROCESSED : 1
FETCHES : 2
END_OF_FETCH_COUNT : 1
-----------------
PL/SQL procedure successfully completed.
--我一直有一个疑问？如果通过这个查询到sql_id. 直接查询sql_text当然可以。
column EXACT_MATCHING_SIGNATURE like SIGNATURE
column FORCE_MATCHING_SIGNATURE like SIGNATURE
column sql_text format a60
SELECT sql_id, sql_text, sql_plan_baseline, exact_matching_signature, force_matching_signature FROM v$sql WHERE sql_id = '3yxwagyspybax';
SQL> SELECT sql_id, sql_text, sql_plan_baseline, exact_matching_signature, force_matching_signature FROM v$sql WHERE sql_id = '3yxwagyspybax';
SQL_ID SQL_TEXT SQL_PLAN_BASELINE EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- ------------------------------------------------------------ ------------------------------ ------------------------ ------------------------
3yxwagyspybax select * from t where id=:x 11842951964357158308 11842951964357158308
--可以发现EXACT_MATCHING_SIGNATURE，FORCE_MATCHING_SIGNATURE与SIGNATURE相等。而SQL_PLAN_BASELINE为NULL。
--执行select * from t where id=:x;2次，每次后访问v$sql视图看看！
SQL> select * from t where id=:x;
ID NAME
---------- ----------
100 lAGCjs
SQL> SELECT sql_id, sql_text, sql_plan_baseline, exact_matching_signature, force_matching_signature FROM v$sql WHERE sql_id = '3yxwagyspybax';
SQL_ID SQL_TEXT SQL_PLAN_BASELINE EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- ------------------------------------------------------------ ------------------------------ ------------------------ ------------------------
3yxwagyspybax select * from t where id=:x 11842951964357158308 11842951964357158308
SQL> select * from t where id=:x;
ID NAME
---------- ----------
100 lAGCjs
SQL> SELECT sql_id,child_number, sql_text, sql_plan_baseline, exact_matching_signature, force_matching_signature FROM v$sql WHERE sql_id = '3yxwagyspybax';
SQL_ID CHILD_NUMBER SQL_TEXT SQL_PLAN_BASELINE EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- ------------ ------------------------------------------------------------ ------------------------------ ------------------------ ------------------------
3yxwagyspybax 0 select * from t where id=:x 11842951964357158308 11842951964357158308
3yxwagyspybax 1 select * from t where id=:x SQL_PLAN_a8qny22gsbtd494ecae5c 11842951964357158308 11842951964357158308
--可以发现sql_id=3yxwagyspybax产生了子光标,v$sql.SQL_PLAN_BASELINE与dba_sql_plan_baselines.PLAN_NAME相等。
--可以通过这些信息,查询相关信息。
3.改动sql语句看看。（把select改为Select看看);
SQL> set serveroutput off
SQL> Select * from t where id=:x;
ID NAME
---------- ----------
100 lAGCjs
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 20cdk7cunfs5c, child number 0
-------------------------------------
Select * from t where id=:x
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 69 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 69 (2)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 100
Predicate Information (identified by operation id):
--------------------------------------------------
1 - filter("ID"=:X)
Note
-----
- SQL plan baseline SQL_PLAN_a8qny22gsbtd494ecae5c used for this statement
SQL> SELECT sql_id,child_number, sql_text, sql_plan_baseline, exact_matching_signature, force_matching_signature FROM v$sql WHERE sql_id in( '3yxwagyspybax','20cdk7cunfs5c');
SQL_ID CHILD_NUMBER SQL_TEXT SQL_PLAN_BASELINE EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- ------------ ------------------------------------------------------------ ------------------------------ ------------------------ ------------------------
3yxwagyspybax 0 select * from t where id=:x 11842951964357158308 11842951964357158308
3yxwagyspybax 1 select * from t where id=:x SQL_PLAN_a8qny22gsbtd494ecae5c 11842951964357158308 11842951964357158308
20cdk7cunfs5c 0 Select * from t where id=:x SQL_PLAN_a8qny22gsbtd494ecae5c 11842951964357158308 11842951964357158308
这样可以通过v$sql的SQL_PLAN_BASELINE与EXACT_MATCHING_SIGNATURE与dba_sql_plan_baselines的PLAN_NAME和SIGNATURE建立联系。
再改动sql语句，使用文字变量看看。
sELECT * from t where id=200;
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 8a9bd03uwsha2, child number 0
-------------------------------------
sELECT * from t where id=200
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 69 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 69 (2)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=200)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
--可以发现并没有使用sql plan baseline。
SQL> SELECT sql_id,child_number, sql_text, sql_plan_baseline, exact_matching_signature, force_matching_signature FROM v$sql WHERE sql_id in( '3yxwagyspybax','20cdk7cunfs5c','8a9bd03uwsha2');
SQL_ID CHILD_NUMBER SQL_TEXT SQL_PLAN_BASELINE EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- ------------ ------------------------------------------------------------ ------------------------------ ------------------------ ------------------------
8a9bd03uwsha2 0 sELECT * from t where id=200 3391601071756251946 5552877699744822552
3yxwagyspybax 0 select * from t where id=:x 11842951964357158308 11842951964357158308
3yxwagyspybax 1 select * from t where id=:x SQL_PLAN_a8qny22gsbtd494ecae5c 11842951964357158308 11842951964357158308
20cdk7cunfs5c 0 Select * from t where id=:x SQL_PLAN_a8qny22gsbtd494ecae5c 11842951964357158308 11842951964357158308
SQL> sELECT * from t where id=100;
ID NAME
---------- ----------
100 lAGCjs
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 9sm94r2b7vxf7, child number 0
-------------------------------------
sELECT * from t where id=100
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 69 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 69 (2)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=100)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
SQL> SELECT sql_id,child_number, sql_text, sql_plan_baseline, exact_matching_signature, force_matching_signature FROM v$sql WHERE sql_id in( '3yxwagyspybax','20cdk7cunfs5c','8a9bd03uwsha2','9sm94r2b7vxf7');
SQL_ID CHILD_NUMBER SQL_TEXT SQL_PLAN_BASELINE EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- ------------ ------------------------------------------------------------ ------------------------------ ------------------------ ------------------------
8a9bd03uwsha2 0 sELECT * from t where id=200 3391601071756251946 5552877699744822552
3yxwagyspybax 0 select * from t where id=:x 11842951964357158308 11842951964357158308
3yxwagyspybax 1 select * from t where id=:x SQL_PLAN_a8qny22gsbtd494ecae5c 11842951964357158308 11842951964357158308
20cdk7cunfs5c 0 Select * from t where id=:x SQL_PLAN_a8qny22gsbtd494ecae5c 11842951964357158308 11842951964357158308
9sm94r2b7vxf7 0 sELECT * from t where id=100 2379110987182820632 5552877699744822552
--有做了一些。
SQL> SELECT sql_id,child_number, sql_text, sql_plan_baseline, exact_matching_signature, force_matching_signature FROM v$sql WHERE sql_id in( '3yxwagyspybax','20cdk7cunfs5c','8a9bd03uwsha2','9sm94r2b7vxf7','b7bzjds8z5syr');
SQL_ID CHILD_NUMBER SQL_TEXT SQL_PLAN_BASELINE EXACT_MATCHING_SIGNATURE FORCE_MATCHING_SIGNATURE
------------- ------------ ------------------------------------------------------------ ------------------------------ ------------------------ ------------------------
8a9bd03uwsha2 0 sELECT * from t where id=200 3391601071756251946 5552877699744822552
b7bzjds8z5syr 0 seLECT * from t where id=100 2379110987182820632 5552877699744822552
3yxwagyspybax 0 select * from t where id=:x 11842951964357158308 11842951964357158308
3yxwagyspybax 1 select * from t where id=:x SQL_PLAN_a8qny22gsbtd494ecae5c 11842951964357158308 11842951964357158308
20cdk7cunfs5c 0 Select * from t where id=:x SQL_PLAN_a8qny22gsbtd494ecae5c 11842951964357158308 11842951964357158308
9sm94r2b7vxf7 0 sELECT * from t where id=100 2379110987182820632 5552877699744822552
6 rows selected.
--大家可以通过这些例子理解EXACT_MATCHING_SIGNATURE，FORCE_MATCHING_SIGNATURE。有点离题了,^_^。
--FORCE_MATCHING_SIGNATURE视乎是转成了绑定变量来对比，并且忽略sql语句大小写。这些知识自己不是很懂，跳过.
SQL> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO MANUAL-LOAD 11842951964357158308
4.建立索引:
SQL> create index i_t_id on t(id);
SQL> set serveroutput off
SQL> select * from t where id=:x;
ID NAME
---------- ----------
100 lAGCjs
SQL> @dpc
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3yxwagyspybax, child number 1
-------------------------------------
select * from t where id=:x
Plan hash value: 1601196873
--------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 69 (100)|
|* 1 | TABLE ACCESS FULL| T | 1 | 69 (2)|
--------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:X)
Note
-----
- SQL plan baseline SQL_PLAN_a8qny22gsbtd494ecae5c used for this statement
--可以发现由于sql plan baseline存在，执行计划依旧使用全表扫描.
SQL> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd40893a4b2 YES NO NO AUTO-CAPTURE 11842951964357158308
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO MANUAL-LOAD 11842951964357158308
--查询dba_sql_plan_baselines可以发现多了1条记录,即使我们设置optimizer_capture_sql_plan_baselines=FALSE,
--oracle可以捕获新的计划和baseline,只不过accepted=no,也就是执行不采用该计划。
--通过origin字段，也可以看到增加的记录是AUTO-CAPTURE。
--查看SQL_HANDLE=SYS_SQL_a45a9e109f85e5a4, PLAN_NAME=SQL_PLAN_a8qny22gsbtd40893a4b2的执行计划可以使用
select * from table(dbms_xplan.display_sql_plan_baseline ('SYS_SQL_a45a9e109f85e5a4','SQL_PLAN_a8qny22gsbtd40893a4b2'));
SQL> select * from table(dbms_xplan.display_sql_plan_baseline ('SYS_SQL_a45a9e109f85e5a4','SQL_PLAN_a8qny22gsbtd40893a4b2'))
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SYS_SQL_a45a9e109f85e5a4
SQL text: select * from t where id=:x
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_a8qny22gsbtd40893a4b2 Plan id: 143893682
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 4153437776
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 12 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=TO_NUMBER(:X))
25 rows selected.
--为了后面能操作比较快，我定义如下变量：
variable v_sql_handle varchar2(30);
variable v_plan_name_full varchar2(30);
variable v_plan_name_index varchar2(30);
exec :v_sql_handle := 'SYS_SQL_a45a9e109f85e5a4';
exec :v_plan_name_full := 'SQL_PLAN_a8qny22gsbtd494ecae5c';
exec :v_plan_name_index := 'SQL_PLAN_a8qny22gsbtd40893a4b2';
5.修改sql plan baseline:
variable v_basenum number;
exec :v_basenum :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => :v_sql_handle,plan_name => :v_plan_name_index,attribute_name => 'ACCEPTED',attribute_value => 'YES');
SQL> exec :v_basenum :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => :v_sql_handle,plan_name => :v_plan_name_index,attribute_name => 'ACCEPTED',attribute_value => 'YES');
BEGIN :v_basenum :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => :v_sql_handle,plan_name => :v_plan_name_index,attribute_name => 'ACCEPTED',attribute_value => 'YES'); END;
*
ERROR at line 1:
ORA-38136: invalid attribute name ACCEPTED specified
ORA-06512: at "SYS.DBMS_SPM", line 2469
ORA-06512: at line 1
--奇怪，没有ACCEPTED这个属性。
-- attribute_name - One of the following possible attribute names:
-- 'ENABLED',
-- 'FIXED',
-- 'AUTOPURGE',
-- 'PLAN_NAME',
-- 'DESCRIPTION'
-- attribute_value - If the attribute name denotes a plan status then
-- the legal values are: 'YES', 'NO'.
-- If the attribute name denotes a plan name then the
-- supplied value should not conflict with already
-- stored plan name.
-- If the attribute name denotes plan description then
-- any character string is allowed.
-- 也就是不能通过DBMS_SPM.ALTER_SQL_PLAN_BASELINE来修改ACCEPTED属性？
exec :v_basenum :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => :v_sql_handle,plan_name => :v_plan_name_index,attribute_name => 'ACCEPTED',attribute_value => 'NO');
BEGIN :v_basenum :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => :v_sql_handle,plan_name => :v_plan_name_index,attribute_name => 'ACCEPTED',attribute_value => 'NO'); END;
*
ERROR at line 1:
ORA-38136: invalid attribute name ACCEPTED specified
ORA-06512: at "SYS.DBMS_SPM", line 2469
ORA-06512: at line 1
--我看一些blog，属性ACCEPTED修改为NO,这样操作是可以的。不知道是否是版本的问题。
链接如下:https://forums.oracle.com/forums/thread.jspa?messageID=9470036
From 11R2 onwards to accept the plan the script. has changed but to unaccept we have to change the enable status to 'NO'
so that the plan is not in use and therefore we have suggested the script. which will make the plan enable as 'NO' and
thus the plan will become unusable.
If the ACCEPTED=YES and ENABLE=NO,then the plan will not be used as for the plan to be used the ENABLE has to 'YES',in
11R1 we were able to change the status of ACCEPTED to 'NO' and 'YES' but from 11R2 we can change the plan status of
ACCEPTED from 'NO' to 'YES'.
Therefore we requested you to change the enable status to 'NO' for which we have provided the script.
Here "verify" indicates that it will not do any performance review and plan will be accepted forcefully,if we set this
as 'YES' then it will do the performance review and then only will accept the plan.
For accepting the plan the "commit" has to be 'YES'.
var pbsts varcahr2(1000)
exec :pbsts := dbms_spm.evolve_sql_plan_baseline('SYS_SQL_7de69bb90f3e54d2','SQL_PLAN_7vtnvr47mwp6k54bc8843',verify=> 'NO', commit=>'YES');
要将属性ACCEPTED修改为YES,仅仅这样操作。
variable v_report clob;
exec :v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => :v_sql_handle);
print :v_report
V_REPORT
--------------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_a45a9e109f85e5a4
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES
Plan: SQL_PLAN_a8qny22gsbtd40893a4b2
------------------------------------
Plan was verified: Time used .06 seconds.
Plan passed performance criterion: 79.44 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): 2.993 .051 58.69
CPU Time(ms): 3.11 0
Buffer Gets: 238 3 79.33
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
select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd40893a4b2 YES YES NO AUTO-CAPTURE 11842951964357158308
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO MANUAL-LOAD 11842951964357158308
--plan_name=SQL_PLAN_a8qny22gsbtd40893a4b2,accepted=YES.
select * from t where id=:x;
SQL> @dpc
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 3yxwagyspybax, child number 1
-------------------------------------
select * from t where id=:x
Plan hash value: 4153437776
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | 1 (0)|
--------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:X)
Note
-----
- SQL plan baseline SQL_PLAN_a8qny22gsbtd40893a4b2 used for this statement
--可以发现oracle使用新的执行计划，plan_name=SQL_PLAN_a8qny22gsbtd40893a4b2 .
--还可以使用另外的方法：
--exec :v_basenum :=DBMS_SPM.ALTER_SQL_PLAN_BASELINE (sql_handle => :v_sql_handle,plan_name => :v_plan_name_index,attribute_name => 'ACCEPTED',attribute_value => 'NO');
select /*+index(t) */ * from t where id=:x;
SQL> @dpc
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 1pkw0u7cm46wk, child number 0
-------------------------------------
select /*+index(t) */ * from t where id=:x
Plan hash value: 4153437776
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | 1 (0)|
--------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:X)
exec :v_basenum:=dbms_spm.drop_sql_plan_baseline(sql_handle => :v_sql_handle,plan_name => :v_plan_name_index);
SQL> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO MANUAL-LOAD 11842951964357158308
--使用这个执行计划
exec :v_num:=dbms_spm.load_plans_from_cursor_cache(sql_id => '1pkw0u7cm46wk',plan_hash_value => 4153437776 ,sql_handle => :v_sql_handle);
select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL> select sql_handle, plan_name, enabled, accepted,fixed,origin,signature from dba_sql_plan_baselines ;
SQL_HANDLE PLAN_NAME ENA ACC FIX ORIGIN SIGNATURE
------------------------------ ------------------------------ --- --- --- -------------- -----------------------
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd40893a4b2 YES YES NO MANUAL-LOAD 11842951964357158308
SYS_SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c YES YES NO MANUAL-LOAD 11842951964357158308
--可以发现plan_name=SQL_PLAN_a8qny22gsbtd40893a4b2,ORIGIN=MANUAL-LOAD.
select * from t where id=:x;
SQL> @dpc
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID 3yxwagyspybax, child number 1
-------------------------------------
select * from t where id=:x
Plan hash value: 4153437776
--------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 2 (0)|
|* 2 | INDEX RANGE SCAN | I_T_ID | 1 | 1 (0)|
--------------------------------------------------------------------
Peeked Binds (identified by position):
--------------------------------------
1 - (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:X)
Note
-----
- SQL plan baseline SQL_PLAN_a8qny22gsbtd40893a4b2 used for this statement
--可以发现执行计划使用索引扫描。
时间： 2024-10-23 20:07:25