DECLARE
V_COUNT NUMBER;
V_TASK_NAME VARCHAR2(30) := 'TUNING_FOR_SQL';
V_SQLSET_NAME VARCHAR2(30) := 'SQL_SET';
V_CURSOR DBMS_SQLTUNE.SQLSET_CURSOR; --声明游标类型
BEGIN
--创建前先删除历史同名调优任务和调优集
SELECT COUNT(1)
INTO V_COUNT
FROM DBA_ADVISOR_LOG A
WHERE A.TASK_NAME = V_TASK_NAME;
IF V_COUNT <> 0 THEN
DBMS_SQLTUNE.DROP_TUNING_TASK(TASK_NAME => V_TASK_NAME);
END IF;
SELECT COUNT(1)
INTO V_COUNT
FROM DBA_SQLSET A
WHERE A.NAME = V_SQLSET_NAME;
IF V_COUNT <> 0 THEN
DBMS_SQLTUNE.DROP_SQLSET(SQLSET_NAME => V_SQLSET_NAME);
END IF;
--1、创建调优集
DBMS_SQLTUNE.CREATE_SQLSET(SQLSET_NAME => V_SQLSET_NAME,
DESCRIPTION => 'FROM USER,FROM SNAP');
--2、获取负载报告的内容 或者从 DBMS_SQLTUNE.SELECT_CURSOR_CACHE
OPEN V_CURSOR FOR
SELECT *
FROM (SELECT VALUE(P)
FROM TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(BEGIN_SNAP => 6463,
END_SNAP => 6464)) P
WHERE P.SQL_ID = '7tr9tsy9vrd77' -- 如果知道 sql_id
ORDER BY CPU_TIME DESC)
--3、将负载报告加载到调优集
DBMS_SQLTUNE.LOAD_SQLSET(SQLSET_NAME => V_SQLSET_NAME,
POPULATE_CURSOR => V_CURSOR);
--4、创建调优任务,将CPU_TIME作为排序依据,在3600S内时间限制
V_TASK_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK(TASK_NAME => V_TASK_NAME,
SQLSET_NAME => V_SQLSET_NAME,
RANK1 => 'CPU_TIME',
TIME_LIMIT => 3600,
DESCRIPTION => 'TUNE SQLSET ORDERED BY CPU_TIME');
--如果知道具体到sql_id 可以
/*V_TASK_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_ID => '7tr9tsy9vrd77',TASK_NAME => V_TASK_NAME);*/
--5、执行调优任务
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(V_TASK_NAME);
END;
/
--获取调优建议
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNING_FOR_SQL') FROM DUAL;
--调优建议脚本
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('TUNING_FOR_SQL') FROM DUAL;
--------------------------------------------------------sql_基线部分-----------------
--加载基线 (关于基线参数 SHOW PARAMETER BASELINE)
DECLARE
V_SQL PLS_INTEGER;
BEGIN
--按SQL_ID
V_SQL := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'7tr9tsy9vrd77'); --从sql_set加载 DBMS_SPM.load_plans_from_sqlset
--按PLAN_ID
--V_SQL := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'7tr9tsy9vrd77',plan_hash_value=>945857953,fixed =>'YES', enabled=>'YES');
END;
/
--删除计划基线(可以按SQL_ID,PLAN_ID)
DECLARE
V_SQL PLS_INTEGER;
BEGIN
--按SQL_ID
V_SQL :=DBMS_SPM.DROP_SQL_PLAN_BASELINE(SQL_HANDLE => 'SYS_SQL_2d69aa4521578bfd');
--按PLAN_ID
--V_SQL :=DBMS_SPM.DROP_SQL_PLAN_BASELINE(PLAN_NAME => 'SQL_PLAN_2uuda8nhpg2zx2bdb2585');
END;
/
/*如果解析过程中发现有比已经保存在基线里cost更少的执行计划存在,这时会自动生成一个新的 not-accepted的执行计划保存在基线中
需要手工演化来验证 不可接受的执行计划基线是否会带来更好的恨性能,如果是需要将基线更改为可接受状态 */
--手工演化
SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_2d69aa4521578bfd') From dual;
--修改基线
/*
1.ENABLED :设置该属性的值为NO告诉Oracle 11g临时禁用某个计划,一个SQL计划必须同时标记为ENABLED和ACCEPTED,否则CBO将忽略它
2.FIXED:设置为YES,那个计划将是优化器唯一的选择[最高优先级],即使如果某个计划可能拥有更低的成本。
这让DBA可以撤销SMB的默认行为,对于转换一个存储概要进入一稳定的SQL计划基线特别有用,
注意当一个新计划被添加到被标记为FIXED的SQL计划基线,该新计划不能被利用除非它申明为FIXED状态
3.AUTOPURG:设置这个属性的值为NO告诉Oracle 11g无限期保留它,从而不用担心SMB的自动清除机制*/
declare
V_SQL PLS_INTEGER;
BEGIN
V_SQL :=dbms_spm.alter_sql_plan_baseline(plan_name => 'SQL_PLAN_2uuda8nhpg2zx0ac5261e',attribute_name => 'fixed',attribute_value => 'YES');
END;
/
--执行计划
SELECT SQL_PLAN_BASELINE,
CHILD_NUMBER,
EXECUTIONS,
PLAN_HASH_VALUE,
ELAPSED_TIME / 1000000,
ELAPSED_TIME / 1000000 / EXECUTIONS,
LAST_ACTIVE_TIME,
ROWS_PROCESSED
FROM V$SQL
WHERE EXECUTIONS > 0
and sql_id = '7tr9tsy9vrd77'
ORDER BY LAST_ACTIVE_TIME DESC;
select * from dba_sql_plan_baselines;
select * from table(dbms_xplan.display_sql_plan_baseline('SYS_SQL_2d69aa4521578bfd',null,'BASIC +NOTE'));