时间：2021-07-01 10:21:17 帮助过：28人阅读
Attribute
Description
SQL_HANDLE
A unique SQL identifier in string form; it can be used as a search key
PLAN_NAME
A unique SQL plan identifier in string form; it can be used as a search key
SQL_TEXT
The SQL statement’s unnormalized, actual text
ORIGIN
Tells if the SQL Plan was either:
ENABLED
Indicates that the SQL Plan is enabled (YES) for CBO utilization or not (NO). Disabled plans are ignored by the CBO
ACCEPTED
Indicates that the SQL Plan is validated as a good plan, either because Oracle 11g has:
FIXED
SQL Plans whose FIXED attribute is set to YES will be considered by the CBO. If multiple plans are marked as FIXED, the CBO will only select the best execution plan from those so marked
OPTIMIZER_COST
The total cost estimated by the CBO to execute the SQL statement using this execution plan
查看已存在SQL Plan Baselines中，对一条SQL语句执行有潜在影响的另一个方法是通过DBMS_XPLAN的新过程DISPLAY_SQL_PLAN_BASELINE。例如：能用这个过程来查看SMB中和SQL语句柄匹配的所有SQLPlan Baselines；如果提供了SQL语句的计划名，也可以显示该语句的执行计划等。
4. 自动捕获的实现和过程
下面，我们分析自动捕获SQL Plan Baselines的过程。首先，我们设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES参数为TRUE（默认为FALSE）以开启SQL Plan Baselines自动捕获；我们还需要把OPTIMIZER_USE_SQL_PLAN_BASELINES参数设置为TRUE (默认值)。该参数控制CBO是否检查SQL语句重复执行产生的计划是否将被评估为一个好的计划。
接着，我们执行同样的一个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新特点——SQL Plan Management(SPM)
标签：oracle11g sql tuning cursor sharing spm acs