时间：2021-07-01 10:21:17 帮助过：29人阅读
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优化（SQL TUNING）新特性之SQL Plan Management(SPM)
标签：nbsp play clob 介绍 ring lan 解决 帮助 sed