oracle11g新特点――SQLPlanManagement(SPM) - 数据库编程

个SQL两次。第一次执行时，SQL语句被记录，第二次执行时，计划自动被捕获进SMB并被标记为该语句ACCEPTED的SQLPlan Baseline。

当今后该SQL语句再次被执行，并产生了一个不同的新的计划时，该计划也会被自动捕获进SMB，但并不被标为ACCEPTED，所以，SPM只会把第一个计划标记为ENABLED和ACCEPTED。

5. SQL Plan Baseline的演化

SPB中未被标示为ACCEPTED的SQL Plan Baselines，需要进一步被演化为标示ACCEPTED状态，才可以被今后再次执行的SQL语句采用，对SPB进行演化的方法，主要有如下两种：

5.1. 手工方法

? 调用dbms_spm包的evolve_sql_plan_baseline()函数

SQL> var report clob;

SQL> exec :report := dbms_spm.evolve_sql_plan_baseline();

SQL> print :report

SQL> select sql_text, plan_name, enabled, accepted fromdba_sql_plan_baselines;

? 调用SQL Tuning Advisor工具包

SQL> var tname varchar2(30);

SQL> exec :tname :=dbms_sqltune.create_tuning_task(sql_id => 'bfbr3zrg9d5cc');

SQL> execdbms_sqltune.execute_tuning_task(task_name => :tname);

SQL> selectdbms_sqltune.report_tuning_task(:tname, 'TEXT', 'BASIC') FROM dual;

SQL> exec dbms_sqltune.accept_sql_profile(task_name=> :tname);

SQL> select sql_text, plan_name, enabled,accepted from dba_sql_plan_baselines;

5.2. 自动方法

? 定期调度dbms_spm包的evolve_sql_plan_baseline()

? 配置SQL TUNING ADVISOR，使其在自动任务窗口自动运行

6. 具体操作命令

? 开启自动捕获和采用SPM

ALTER SESSION SET optimizer_capture_sql_plan_baselines=TRUE;

ALTER SESSION SET optimizer_use_sql_plan_baselines=TRUE;

? 查看SPM元数据

COL creator         FORMAT A08      HEADING 'Creator'

COL hndle           FORMAT A08      HEADING 'SQL|Handle'

COL plnme           FORMAT A08      HEADING 'Plan|Name'

COL sql_hdr         FORMAT A25      HEADING 'SQL Text' WRAP

COL origin          FORMAT A12      HEADING 'Origin'

COL optimizer_cost  FORMAT 9999999  HEADING 'CBO|Cost'

COL enabled         FORMAT A04      HEADING 'Ena-|bled'

COL accepted        FORMAT A04      HEADING 'Acpt'

COL fixed           FORMAT A04      HEADING 'Fixd'

COL autopurge       FORMAT A04      HEADING 'Auto|Purg'

COL create_dt       FORMAT A11      HEADING 'Created|On' WRAP

COL lst_exc_dt      FORMAT A11      HEADING 'Last|Executed' WRAP

SELECT

     creator

    ,SUBSTR(sql_handle, -8, 8) hndle

    ,SUBSTR(plan_name, -8, 8)  plnme

    ,SUBSTR(sql_text, 1, 75) sql_hdr

    ,origin

    ,optimizer_cost

    ,enabled

    ,accepted

    ,fixed

    ,autopurge

    ,TO_CHAR(created, 'yyyy-mm-dd hh24:mi:ss') create_dt

    ,TO_CHAR(last_executed, 'yyyy-mm-dd hh24:mi:ss') lst_exc_dt

  FROM dba_sql_plan_baselines

  WHERE (sql_text LIKE '%SPM%')

 ORDER BY 1,2,3;

? 通过DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE显示已保留的包含特定文本的SQL Plan Baselines

SET LINESIZE 150

SET PAGESIZE 2000

SELECT PT.*

   FROM (SELECT

             DISTINCT sql_handle

           FROM dba_sql_plan_baselines

          WHERE sql_text like '%SPM%') SPB,

        TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(SPB.sql_handle, NULL,'TYPICAL +NOTE')) PT;

oracle11g新特点――SQLPlanManagement(SPM)(二)