[20230803]12C SPM学习.txt

来源：这里教程网时间：2026-03-03 18:56:52 作者：

[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.

编辑推荐：

返回列表

[20230803]12C SPM学习.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推