oracle笔记-执行计划

sqlplus sys/admin as sysdba；

--清空缓存
alter system flush [SHARED_POOL/BUFFER_CACHE/GLOBAL CONTEXT]

---------------------使用10046事件查看真实执行计划 start-----------
--在当前session中使用oradebug命令
oradebug setmypid;
--设置10046事件:
oradebug event 10046 trace name context forever,level 12;

select empno,ename,dname from scott.emp,scott.dept where emp.deptno = dept.deptno;

oradebug tracefile_name;

--关闭10046事件
oradebug event 10046 trace name context off;

--翻译trc文件
cmd命令: tkprof E:\oracle\diag\rdbms\march\march\trace\march_ora_22348.trc e:\march_ora_22348_tkprof.trc;

--使用DBMS_XPLAN包查看执行计划
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
set linesize;
select sql_text,executions as 执行次数,sql_id,version_count from v$sqlarea where sql_text like 'select empno,ename,dname from scott.emp%'

---------------------使用10046事件查看真实执行计划 end-----------

 

 

 

---------------------自动捕获SQL PLAN Baseline start -------------------------
show parameter sql_plan;
/**
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE
**/ 

alter session set optimizer_use_sql_plan_baselines = false;
alter session set optimizer_capture_sql_plan_baselines= true;
create table t2 as select * from dba_objects;
 
create index idx_t2 on t2(object_id);

--对t2表收集统计信息
exec dbms_stats.gather_table_stats(ownname => 'system',tabname => 't2',estimate_percent => 100,cascade => true);

select object_id,object_name from t2 where object_id between 103 and 108;

select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
--查看是否被自动捕获
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';

select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

--再次执行查询sql
select object_id,object_name from t2 where object_id between 103 and 108;

--修改聚簇因子
exec dbms_stats.set_index_stats(ownname => 'system', indname => 'idx_t2', clstfct => 24000000, no_invalidate => false);
--验证聚簇因子是否被修改
select index_name,clustering_factor from dba_indexes where index_name = 'idx_t2';

select object_id,object_name from t2 where object_id between 103 and 108;

select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
--关闭自动捕获,同时开启SPM,即相当于回复oracle11g中的默认设置
alter session set optimizer_capture_sql_plan_baselines= false;
alter session set optimizer_use_sql_plan_baselines = true;

select object_id,object_name from t2 where object_id between 103 and 108;

select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

--修改执行计划
var temp varchar2(1000);
exec :temp := dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_ac526b1e4be74880',plan_name => 'SQL_PLAN_asnmb3t5yfk4024c6dbb6',verify => 'NO',commit => 'YES');
--验证是否被修改
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
--修改第一条执行计划,将其失效
exec :temp := dbms_spm.alter_sql_plan_baseline(sql_handle => 'SYS_SQL_ac526b1e4be74880',plan_name => 'SQL_PLAN_asnmb3t5yfk4024c6dbb6',attribute_name => 'ENABLED',attribute_value => 'NO');
--验证是否被修改
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like 'select object_id%';
select object_id,object_name from t2 where object_id between 103 and 108;

select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
---------------------自动捕获SQL PLAN Baseline end -------------------------

 

 

---------------------手工SQL PLAN Baseline start -------------------------
alter session set optimizer_use_sql_plan_baselines = false;
alter session set optimizer_capture_sql_plan_baselines= true;
select object_id,object_name from t2 where object_id between 103 and 108;

select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

--使用强制索引
select /*+ no_index (t2 idx_t2)*/object_name,object_id from t2 where object_id between 103 and 108;

--获取sql_id : 92x8kgjvzm7hd  和plan_hash_value : 1513984157
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

--使用目标sql的初始执行计划所对应的sqlid和plan_hash_value手工生成对应的SQL PLAN Baseline
var temp varchar2(1000);
exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id => '92x8kgjvzm7hd',plan_hash_value => '1513984157');

--获取目标sql的初始执行计划获取sql_handle 和plan_name 
--sql_handle : SYS_SQL_65dfba7728297334 和plan_name : SQL_PLAN_6brxufwn2kwtnb860bcf2
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like '%select /*+ no_index (t2 idx_t2)*/object_name%';

--改写原目标sql,加入强制索引后重新执行
select /*+ index (t2 idx_t2)*/object_name,object_id from t2 where object_id between 103 and 108;
--获取改写sql的sqlid和plan_hash_value
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));
--sqlid : 7xsxnsfgw7n4n  plan_hash_value : 2008370210

--用改写后的新执行计划的sqlid和plan_hash_value 以及原目标sql的SQL PLAN Baseline的sql_handle手工生成新的SQL PLAN Baseline
var temp varchar2(1000);
exec :temp := dbms_spm.load_plans_from_cursor_cache(sql_id => '7xsxnsfgw7n4n',plan_hash_value => '2008370210',sql_handle => 'SYS_SQL_65dfba7728297334');
--获取原s目标sql所有的SQL PLAN Baseline
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like '%select /*+ no_index (t2 idx_t2)*/object_name%';
--drop掉原执行计划
exec :temp := dbms_spm.drop_sql_plan_baseline(sql_handle => 'SYS_SQL_65dfba7728297334',plan_name => 'SQL_PLAN_6brxufwn2kwtnb860bcf2');
--验证
select sql_handle,plan_name,origin,enabled,accepted,sql_text from dba_sql_plan_baselines where sql_text like '%select /*+ no_index (t2 idx_t2)*/object_name%';
select /*+ no_index (t2 idx_t2)*/object_name,object_id from t2 where object_id between 103 and 108;
select * from table(dbms_xplan.display_cursor(null,null,'advanced'));

---------------------手工SQL PLAN Baseline end -------------------------