SQLTUNE以及SQL_PLAN_BASELINE

来源：互联网发布：淘宝店铺头像图片免费编辑：程序博客网时间：2024/05/21 07:50

-----------------------------------------------sqlset
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'));

0 0