SQLTUNE以及SQL_PLAN_BASELINE

深碍√TFBOYSˉ_ 2022-08-02 07:27 163阅读 0赞

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