本篇文章为大家展示了Oracle 11g中SPM指的是什么,内容简明扼要并且容易理解,绝对能使你眼前一亮,通过这篇文章的详细介绍希望你能有所收获。
前言
我们知道,SQL语句的性能在很大程度上依赖于SQL语句的执行计划,如果SQL语句的执行计划发生变化,那么SQL语句的性能有可能发生很大的变化。影响SQL语句执行计划的因素很多,常见的有:
▪优化器版本的变化
▪统计信息的变化
▪优化器相关的各种参数的变化
▪对象定义的修改
▪添加、删除相关索引
▪修改了系统的设置
▪绑定变量的"窥视"功能
▪绑定变量的定义发生变化(比如类型或长度有变化)
▪启用了outline或SQL Profile等设置
11g前我们的处理方式
在Oracle 11g前,我们可以借助存储大纲(Stored Outline)和SQL Profile来帮助我们固定某个SQL语句的执行计划,防止由于执行计划的改变从而导致SQL性能的衰退。不过这些技术需要DBA人为的处理,比如存储大纲需要DBA手工创建,而对10g中提供的SQL Profile来说,则需要DBA手工应用才可以生效。
11g中,Oracle 提供了SPM
SPM就是Sql Plan Management,是Oracle自我管理(或者说自动性)发展的新功能。通过这个特性,Oracle自动去判断某个SQL的新的执行计划是否更加合理(成本更低),只有在新的执行计划比原来的执行计划更好的情况下,它才会被使用,从而有效的保护了执行计划的稳定性,进而保证了SQL语句的执行效率。
关于SPM的工作原理,本文不做详细的介绍,有兴趣的同志可以参阅相关的资料(网上类似的文章也不少)
执行计划管理实例测试
测试一:自动捕获的场景
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0
Connected as study
SQL> create table test as select * from dba_objects;
Table created
SQL> exec DBMS_STATS.gather_table_stats(USER, 'TEST', cascade=>TRUE);
PL/SQL procedure successfully completed
SQL> select object_name from test where object_id=100;
OBJECT_NAME
—————-
FIXED_OBJ$
SQL> select object_name from test where object_id=100;
OBJECT_NAME
—————-
FIXED_OBJ$
尽管上面的查询语句执行了2次,但这个时候去查询dba_sql_plan_baseline的时候会发现,找不到我们需要的记录,因为这个时候optimizer_capture_sql_plan_baselines设置为false.禁止了自动捕获的功能.下面我将该参数设置为true,继续测试看看:
SQL> alter session set optimizer_capture_sql_plan_baselines=true;
Session altered
SQL> select object_name from test where object_id=100;
OBJECT_NAME
——————–
FIXED_OBJ$
SQL> select object_name from test where object_id=100;
OBJECT_NAME
——————–
FIXED_OBJ$
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines;
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE
———- ————————— —————————— ————– ——- ——– ———
4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f197bbe3d0 AUTO-CAPTURE YES YES YES
这个时候,我们再去查询dba_sql_plan_baseline的时候,就会发现该语句在plan history中记录了一个执行计划.
几个关键字段的说明
sql_handle:表示SQL语句的句柄
plan_name: 表示该SQL语句执行计划的名字
origin: 表示该执行计划是如何进入plan history的。AUTO-CAPTURE:优化器自动加入;MANUAL:DBA手工加入
enabled: 表示是否被启用了。YES:启用 NO:禁用。如果某个执行计划为禁用,则优化器根本就不会考虑使用该执行计划
accepted: 表示是否接受,也就是是否进入了plan baseline里,YES表示接受,NO表示不接受
autopurge: 表示是否为定期自动删除,YES表示是,NO表示否。
fixed: 当plan baseline中有多个执行计划时,表示优化器仅考虑这些被设置为FIXED的计划,从中选择一个最优的。
继续测试的旅程,新加一个index,再执行上面的SQL语句:
SQL> create index inx_test_object_id on test(object_id);
Index created
SQL> exec DBMS_STATS.gather_table_stats(USER, 'TEST', cascade=>TRUE);
PL/SQL procedure successfully completed
SQL> select object_name from test where object_id=100;
OBJECT_NAME
—————–
FIXED_OBJ$
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines;
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE
———- ————————– —————————— ————- ——- ——– ———
4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f127b7cc01 AUTO-CAPTURE YES NO YES
4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f197bbe3d0 AUTO-CAPTURE YES YES YES
SQL>
可以看到,dba_sql_plan_baselines视图里多了一个执行计划(上面灰色的),不过该执行计划的accepted为NO,表示还没有进入到plan baseline里,而是进入了plan history中。
如果想让该计划进入到plan baseline中,我们可以借助dbms_spm来完成:
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 Connected as study SQL> SQL> set serveroutput on long 100000 SQL> declare 2 report clob; 3 begin 4 report := dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_3c0de038050ab8f1', 5 plan_name => 'SYS_SQL_PLAN_050ab8f127b7cc01'); 6 dbms_output.put_line(report); 7 end; 8 / ------------------------------------------------------------------------------- Evolve SQL Plan Baseline Report ------------------------------------------------------------------------------- Inputs: ------- SQL_HANDLE = SYS_SQL_3c0de038050ab8f1 PLAN_NAME = SYS_SQL_PLAN_050ab8f127b7cc01 TIME_LIMIT = DBMS_SPM.AUTO_LIMIT VERIFY = YES COMMIT = YES Plan: SYS_SQL_PLAN_050ab8f127b7cc01 ----------------------------------- Plan was verified: Time used .062 seconds. Passed performance criterion: Compound improvement ratio >= 337.17. Plan was changed to an accepted plan. Baseline Plan Test Plan Improv. Ratio ------------- --------- ------------- Execution Status: COMPLETE COMPLETE Rows Processed: 1 1 Elapsed Time(ms): 13 0 CPU Time(ms): 15 0 Buffer Gets: 1010 3 336.67 Disk Reads: 0 0 Direct Writes: 0 0 Fetches: 0 0 Executions: 1 1 ------------------------------------------------------------------------------- Report Summary ------------------------------------------------------------------------------- Number of SQL plan baselines verified: 1. Number of SQL plan baselines evolved: 1. PL/SQL procedure successfully completed SQL>
再看一下dba_sql_plan_baselines
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines;
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE
———- ————————– —————————— ————- ——- ——– ———
4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f197bbe3d0 AUTO-CAPTURE YES YES YES
4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f127b7cc01 AUTO-CAPTURE YES YES YES
SQL>
咱们分别看一下这两个执行计划的详细信息:
SQL> select * from table(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_050ab8f127b7cc01'));
PLAN_TABLE_OUTPUT
——————————————————————————–
SQL handle: SYS_SQL_3c0de038050ab8f1
SQL text: select object_name from test where object_id=100
——————————————————————————–
Plan name: SYS_SQL_PLAN_050ab8f127b7cc01
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
——————————————————————————–
Plan hash value: 2422726699
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost
——————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 30 | 2
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 30 | 2
|* 2 | INDEX RANGE SCAN | INX_TEST_OBJECT_ID | 1 | | 1
——————————————————————————–
PLAN_TABLE_OUTPUT
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – access("OBJECT_ID"=100)
25 rows selected
SQL>
SQL> select * from table(DBMS_XPLAN.display_sql_plan_baseline(plan_name=>'SYS_SQL_PLAN_050ab8f197bbe3d0'));
PLAN_TABLE_OUTPUT
——————————————————————————–
——————————————————————————–
SQL handle: SYS_SQL_3c0de038050ab8f1
SQL text: select object_name from test where object_id=100
——————————————————————————–
——————————————————————————–
Plan name: SYS_SQL_PLAN_050ab8f197bbe3d0
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
——————————————————————————–
Plan hash value: 1357081020
————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 30 | 282 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 1 | 30 | 282 (1)| 00:00:04 |
————————————————————————–
PLAN_TABLE_OUTPUT
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – filter("OBJECT_ID"=100)
24 rows selected
SQL>
在看看这个SQL执行时到底选择了那个baseline:
SQL> conn study/study
已连接。
SQL> set autotrace trace
SQL> select object_name from test where object_id=100;
执行计划
———————————————————-
Plan hash value: 2422726699
————————————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |
————————————————————————————————–
| 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 30 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | INX_TEST_OBJECT_ID | 1 | | 1 (0)| 00:00:01 |
————————————————————————————————–
Predicate Information (identified by operation id):
—————————————————
2 – access("OBJECT_ID"=100)
Note
—–
– SQL plan baseline "SYS_SQL_PLAN_050ab8f127b7cc01" used for this statement
SQL>
下面再熟悉一下手工删除plan baseline里的执行计划,同样可以借助dbms_spm来完成。下面的DEMO把上面baseline里走full table scan的执行计划清除:
SQL> var ret number
SQL> exec :ret:=dbms_spm.drop_sql_plan_baseline(sql_handle => 'SYS_SQL_3c0de038050ab8f1',
plan_name => 'SYS_SQL_PLAN_050ab8f197bbe3d0');
PL/SQL procedure successfully completed
ret
———
1
SQL>
SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge from dba_sql_plan_baselines;
SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE
———- ———————— —————————— ————– ——- ——– ———
4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f127b7cc01 AUTO-CAPTURE YES YES YES
SQL>
测试二:手动捕获的场景
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 Connected as study SQL> select sql_text,sql_id from v$sql where sql_text like '%select object_name from test where%'; SQL_TEXT SQL_ID ----------------------------------------------------- ------------- select object_name from test where object_id=100 7j7jc706upva2 SQL> SQL> set serveroutput on SQL> declare 2 l_plans_loaded PLS_INTEGER; 3 begin 4 l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache( 5 sql_id => '7j7jc706upva2'); 6 7 DBMS_OUTPUT.put_line('Plans Loaded: ' || l_plans_loaded); 8 END; 9 / Plans Loaded: 1 PL/SQL procedure successfully completed SQL> SQL> select signature,sql_handle,plan_name,origin,enabled,accepted,autopurge 2 from dba_sql_plan_baselines; SIGNATURE SQL_HANDLE PLAN_NAME ORIGIN ENABLED ACCEPTED AUTOPURGE ---------- ------------------------- ------------------------------ ----------- ------- -------- --------- 4.32736134 SYS_SQL_3c0de038050ab8f1 SYS_SQL_PLAN_050ab8f127b7cc01 MANUAL-LOAD YES YES YES SQL>
上述内容就是Oracle 11g中SPM指的是什么,你们学到知识或技能了吗?如果还想学到更多技能或者丰富自己的知识储备,欢迎关注亿速云行业资讯频道。
原创文章,作者:kepupublish,如若转载,请注明出处:https://blog.ytso.com/200193.html