[20230803]12C SPM学习.txt
--//以前做过一些SPM的测试在11g下，前段时间我通过sql profile固定执行计划遇到性能问题，我回头看了以前在11g下做的SPM笔记。
--//自己在12c重复测试看看，主要看给索引改名会出现什么情况。
1.环境：
SCOTT@test01p> @ver1
PORT_STRING VERSION BANNER CON_ID
-------------------- ---------- ---------------------------------------------------------------------------- ------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
SCOTT@test01p> show parameter plan_base
NAME TYPE VALUE
------------------------------------ ------- ------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
2.建立测试：
SCOTT@test01p> create table t as select rownum id , cast(dbms_random.string('a',6 ) as varchar2(10)) name from dual connect by level <=1e5;
Table created.
SCOTT@test01p> @ gts t '' '' ''
Gather Table Statistics for table t...
exec dbms_stats.gather_table_stats('SCOTT', 'T', estimate_percent => NULL, method_opt=>'FOR TABLE FOR ALL COLUMNS SIZE REPEAT', cascade=>true, no_invalidate=>false)
if lock table t, add force=>true.
press ctrl+c cancel, enter continue...
PL/SQL procedure successfully completed.
SCOTT@test01p> alter system flush shared_pool;
System altered.
SCOTT@test01p> variable x number;
SCOTT@test01p> exec :x :=100;
PL/SQL procedure successfully completed.
SCOTT@test01p> select * from t where id=:x;
ID NAME
--- ------
100 tqBVLd
SCOTT@test01p> @ dpc '' '-note' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3yxwagyspybax, child number 0
-------------------------------------
select * from t where id=:x
Plan hash value: 1601196873
----------------------------------------------------------------
|Id|Operation |Name|E-Rows|E-Bytes|Cost (%CPU)|E-Time |
----------------------------------------------------------------
| 0|SELECT STATEMENT | | | | 70 (100)| |
|*1| TABLE ACCESS FULL|T | 1| 12 | 70 (3)|00:00:01|
----------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :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 );
variable v_sql_handle varchar2(30);
variable v_plan_name varchar2(30);
exec :v_sql_handle := 'SQL_a45a9e109f85e5a4'
exec :v_plan_name := 'SQL_PLAN_a8qny22gsbtd494ecae5c'
SCOTT@test01p> select sql_handle, plan_name, created,enabled, accepted,fixed,autopurge,reproduced,origin,signature from dba_sql_plan_baselines where sql_handle=:v_sql_handle;
SQL_HANDLE PLAN_NAME CREATED ENA ACC FIX AUT REP ORIGIN SIGNATURE
-------------------- ------------------------------ -------------------------- --- --- --- --- --- ----------------------------- --------------------
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c 2023-08-03 21:29:10.000000 YES YES NO YES YES MANUAL-LOAD-FROM-CURSOR-CACHE 11842951964357158308
--//再次执行查看执行计划可以确定使用SPM。输出略。
3.建立索引:
SCOTT@test01p> create index i_t_id on t(id);
Index created.
SCOTT@test01p> select * from t where id=:x;
ID NAME
--- ------
100 tqBVLd
SCOTT@test01p> @ 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|E-Bytes|Cost (%CPU)|E-Time |
----------------------------------------------------------------
| 0|SELECT STATEMENT | | | | 70 (100)| |
|*1| TABLE ACCESS FULL|T | 1| 12 | 70 (3)|00:00:01|
----------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:X)
Note
-----
- SQL plan baseline SQL_PLAN_a8qny22gsbtd494ecae5c used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
--//虽然建立索引，因为存在SPM基线，oracle还是选择全表扫描。
SCOTT@test01p> select sql_handle, plan_name, created,enabled, accepted,fixed,autopurge,reproduced,origin,signature from dba_sql_plan_baselines where sql_handle=:v_sql_handle ;
SQL_HANDLE PLAN_NAME CREATED ENA ACC FIX AUT REP ORIGIN SIGNATURE
-------------------- ------------------------------ -------------------------- --- --- --- --- --- ----------------------------- --------------------
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd4946666a1 2023-08-03 21:40:32.000000 YES NO NO YES YES AUTO-CAPTURE 11842951964357158308
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c 2023-08-03 21:29:10.000000 YES YES NO YES YES MANUAL-LOAD-FROM-CURSOR-CACHE 11842951964357158308
--//增加1条,PLAN_NAME='SQL_PLAN_a8qny22gsbtd4946666a1',ORIGIN='AUTO-CAPTURE',但是accepted='NO',不使用走索引的执行计划.
--//加入注解：
SCOTT@test01p> Select /*+aaaa */ * from t where id=:x;
ID NAME
--- ------
100 tqBVLd
1 row selected.
SCOTT@test01p> @ dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f24pbxf9anmf4, child number 0
-------------------------------------
Select /*+aaaa */ * from t where id=:x
Plan hash value: 3446268138
-----------------------------------------------------------------------------------
|Id|Operation |Name |E-Rows|E-Bytes|Cost(%CPU)|E-Time |
-----------------------------------------------------------------------------------
| 0|SELECT STATEMENT | | | | 2 (100)| |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED|T | 1| 12 | 2 (0)|00:00:01|
|*2| INDEX RANGE SCAN |I_T_ID| 1| | 1 (0)|00:00:01|
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:X)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
36 rows selected.
--//可以发现这样可以使用索引。
variable v_report clob;
exec :v_report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle => :v_sql_handle);
print :v_report
SCOTT@test01p> print :v_report
V_REPORT
----------------------------------------------
GENERAL INFORMATION SECTION
----------------------------------------------
Task Information:
---------------------------------------------
Task Name : TASK_1699
Task Owner : SCOTT
Execution Name : EXEC_3244
Execution Type : SPM EVOLVE
Scope : COMPREHENSIVE
Status : COMPLETED
Started : 08/03/2023 21:51:31
Finished : 08/03/2023 21:51:33
Last Updated : 08/03/2023 21:51:33
Global Time Limit : 2147483646
Per-Plan Time Limit : UNUSED
Number of Errors : 0
---------------------------------------------------------------------------------------------
SUMMARY SECTION
---------------------------------------------------------------------------------------------
Number of plans processed : 1
Number of findings : 2
Number of recommendations : 1
Number of errors : 0
---------------------------------------------------------------------------------------------
DETAILS SECTION
---------------------------------------------------------------------------------------------
Object ID : 2
Test Plan Name : SQL_PLAN_a8qny22gsbtd4946666a1
Base Plan Name : SQL_PLAN_a8qny22gsbtd494ecae5c
SQL Handle : SQL_a45a9e109f85e5a4
Parsing Schema : SCOTT
Test Plan Creator : SCOTT
SQL Text : select * from t where id=:x
Bind Variables:
-----------------------------
1 - (NUMBER): 100
Execution Statistics:
-----------------------------
Base Plan Test Plan
---------- ----------------------------
Elapsed Time (s): .000155 .000004
CPU Time (s): .000173 0
Buffer Gets: 25 0
Optimizer Cost: 70 2
Disk Reads: 0 0
Direct Writes: 0 0
Rows Processed: 0 0
Executions: 10 10
FINDINGS SECTION
------------------------------------------------------------
Findings (2):
-----------------------------
1. The plan was verified in 0.07800 seconds. It passed the benefit criterion
because its verified performance was 83.39110 times better than that of the
baseline plan.
2. The plan was automatically accepted.
Recommendation:
-----------------------------
Consider accepting the plan.
EXPLAIN PLANS SECTION
------------------------------
Baseline Plan
-----------------------------
Plan Id : 1
Plan Hash Value : 2498539100
------------------------------------------------------
|Id|Operation |Name|Rows|Bytes|Cost|Time |
------------------------------------------------------
| 0|SELECT STATEMENT | | 1| 12| 70|00:00:01|
|*1| TABLE ACCESS FULL|T | 1| 12| 70|00:00:01|
------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 1 - filter("ID"=:X)
Test Plan
-----------------------------
Plan Id : 2
Plan Hash Value : 2489738913
--------------------------------------------------------------------------
|Id|Operation |Name |Rows|Bytes|Cost|Time |
--------------------------------------------------------------------------
| 0|SELECT STATEMENT | | 1| 12| 2|00:00:01|
| 1| TABLE ACCESS BY INDEX ROWID BATCHED|T | 1| 12| 2|00:00:01|
|*2| INDEX RANGE SCAN |I_T_ID| 1| | 1|00:00:01|
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
------------------------------------------
* 2 - access("ID"=:X)
---------------------------------------------------------------------------------------------
SCOTT@test01p> select sql_handle, plan_name, created,enabled, accepted,fixed,autopurge,reproduced,origin,signature from dba_sql_plan_baselines where sql_handle=:v_sql_handle ;
SQL_HANDLE PLAN_NAME CREATED ENA ACC FIX AUT REP ORIGIN SIGNATURE
-------------------- ------------------------------ -------------------------- --- --- --- --- --- ----------------------------- --------------------
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd4946666a1 2023-08-03 21:40:32.000000 YES YES NO YES YES AUTO-CAPTURE 11842951964357158308
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c 2023-08-03 21:29:10.000000 YES YES NO YES YES MANUAL-LOAD-FROM-CURSOR-CACHE 11842951964357158308
--//PLAN_NAME='SQL_PLAN_a8qny22gsbtd4946666a1',accepted ='YES',原来是'NO'.
--//再次执行sql语句，查看执行计划如下：
Plan hash value: 3446268138
-------------------------------------------------------------------------------------
|Id| Operation |Name |E-Rows|E-Bytes|Cost (%CPU)|E-Time |
-------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | | | 2 (100)| |
| 1| TABLE ACCESS BY INDEX ROWID BATCHED|T | 1| 12 | 2 (0)|00:00:01|
|*2| INDEX RANGE SCAN |I_T_ID| 1| | 1 (0)|00:00:01|
-------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
2 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=:X)
Note
-----
- SQL plan baseline SQL_PLAN_a8qny22gsbtd4946666a1 used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
--//可以发现现在可以使用索引。
4.改名索引呢?
SCOTT@test01p> alter index I_T_ID rename to pk_t;
Index altered.
SCOTT@test01p> select * from t where id=:x;
ID NAME
--- ------
100 tqBVLd
--//查看执行计划：
SCOTT@test01p> @ 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|E-Bytes|Cost (%CPU)|E-Time |
----------------------------------------------------------------
| 0|SELECT STATEMENT | | | | 70 (100)| |
|*1| TABLE ACCESS FULL|T | 1| 12 | 70 (3)|00:00:01|
----------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:X)
Note
-----
- SQL plan baseline SQL_PLAN_a8qny22gsbtd494ecae5c used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
--//又回到全表扫描的执行计划。
SCOTT@test01p> select sql_handle, plan_name, created,enabled, accepted,fixed,autopurge,reproduced,origin,signature from dba_sql_plan_baselines where sql_handle=:v_sql_handle ;
SQL_HANDLE PLAN_NAME CREATED ENA ACC FIX AUT REP ORIGIN SIGNATURE
-------------------- ------------------------------ -------------------------- --- --- --- --- --- ----------------------------- --------------------
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd4946666a1 2023-08-03 21:40:32.000000 YES YES NO YES YES AUTO-CAPTURE 11842951964357158308
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c 2023-08-03 21:29:10.000000 YES YES NO YES YES MANUAL-LOAD-FROM-CURSOR-CACHE 11842951964357158308
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd4bfbe9701 2023-08-03 22:01:24.000000 YES NO NO YES YES AUTO-CAPTURE 11842951964357158308
3 rows selected.
--//你可以发现再次增加1条记录。
--//可以发现使用SPM管理执行计划一样存在风险，你改一个索引名也可能导致生产系统发生性能问题。
--//生产系统19c，不知道是否也存在类似问题!!
5.继续:
--//将索引删除,再重新建立为主键索引以及约束看看.
drop index pk_t;
create unique index pk_t on t(id);
alter table t add constraint pk_t primary key (id);
variable x number;
exec :x :=100;
select * from t where id=:x;
@ dpc '' '' ''
SCOTT@test01p> @ 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 |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 70 (100)| |
|* 1 | TABLE ACCESS FULL| T | 1 | 12 | 70 (3)| 00:00:01 |
---------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (NUMBER): 100
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("ID"=:X)
Note
-----
- SQL plan baseline SQL_PLAN_a8qny22gsbtd494ecae5c used for this statement
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
35 rows selected.
--//依旧是全表扫描。
variable v_sql_handle varchar2(30);
exec :v_sql_handle := 'SQL_a45a9e109f85e5a4'
SCOTT@test01p> select sql_handle, plan_name, created,enabled, accepted,fixed,autopurge,reproduced,origin,signature from dba_sql_plan_baselines where sql_handle=:v_sql_handle ;
SQL_HANDLE PLAN_NAME CREATED ENA ACC FIX AUT REP ORIGIN SIGNATURE
-------------------- ------------------------------ -------------------------- --- --- --- --- --- ----------------------------- -----------------------
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd4946666a1 2023-08-03 21:40:32.000000 YES YES NO YES YES AUTO-CAPTURE 11842951964357158308
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd494ecae5c 2023-08-03 21:29:10.000000 YES YES NO YES YES MANUAL-LOAD-FROM-CURSOR-CACHE 11842951964357158308
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd4b948f327 2023-08-04 20:18:48.000000 YES NO NO YES YES AUTO-CAPTURE 11842951964357158308
SQL_a45a9e109f85e5a4 SQL_PLAN_a8qny22gsbtd4bfbe9701 2023-08-03 22:01:24.000000 YES NO NO YES YES AUTO-CAPTURE 11842951964357158308
--//有增加1条记录。
6.总结:
--//之所以做这个测试我想说的是至少在12C之前如果你使用SPM管理sql执行计划,可能不小心改1个索引名字一样造成生产系统发生性能
--//问题.我以前做优化总是先看看表上存在那些索引,有一些团队建立的索引命名非常混乱,我觉得至少在团队里面统一风格,我一般的建
--//议是主键索引以及约束名(不要采用1缺省格式)使用"PK_表名"的形式,而一般普通索引采用"I_表名_字段名1_字段名2..."格式,因为
--//以前受到索引名字长度30个字符的限制(现在没有这个限制我建议还是限制在30个字符之内),这样在使用"I_表名_字段名1_字段名
--//2..."风格时可能超过30个字符,可以后面的字段名仅仅取一个字符的形式,如果索引包含5-6个字段实在太多,可以不要包括这么多字
--//段名采用"_X"作为结束.
--//这样带来的好处是看执行计划就知道使用那个字段的索引,那些乱命名的项目,大部分可能从不看执行计划,简直是在乱建索引.
--//我曾经优化MS sql server的项目,发现竟然可以实现1个字段,建立3个一样的索引,仅仅索引名字不同而已,可以想象这个项目管理的
--//混乱.
--//另外去年我做1个19C项目优化,我发现该项目使用SPM管理:
SYS@192.168.100.235:1521/orcl> @ pr
==============================
PORT_STRING : x86_64/Linux 2.4.xx
VERSION : 19.0.0.0.0
BANNER : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID : 0
PL/SQL procedure successfully completed.
SYS@192.168.100.235:1521/orcl> show parameter plan_base
PARAMETER_NAME TYPE VALUE
------------------------------------ ------- -------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
--//optimizer_capture_sql_plan_baselines=FALSE.
SYS@192.168.100.235:1521/orcl> select count(*) from dba_sql_plan_baselines ;
COUNT(*)
----------
2329
SYS@192.168.100.235:1521/orcl> select origin,count(*) from dba_sql_plan_baselines group by origin;
ORIGIN COUNT(*)
----------------------------- ----------
EVOLVE-CREATE-FROM-ADAPTIVE 18
EVOLVE-LOAD-FROM-AWR 695
AUTO-CAPTURE 910
EVOLVE-LOAD-FROM-CURSOR-CACHE 706
--//从ORIGIN的分布看，似乎oracle在后台在某个时刻在做EVOLVE,来源是LOAD-FROM-AWR,LOAD-FROM-CURSOR-CACHE,CREATE-FROM-ADAPTIVE.
--//可能遇到更好的执行计划应该出现AUTO-CAPTURE。
--//我个人并不喜欢使用SPM,因此我删除全部SPM,但是第2天我发现会再次建立回来.最后我发现关闭sql tuning advisor任务后不再出现.
--//为了不再使用SPM,我执行如下,关闭sql tuning advisor任务.
SYS@192.168.100.235:1521/orcl> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);
PL/SQL procedure successfully completed.
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2969868/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
