oracle11g查询与优化,oracle11g中SQL优化（SQL TUNING）新特性之SQL Plan Management(SPM)

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:

AUTO-CAPTURE: Automatically captured

MANUAL-LOAD: Manually evolved

MANUAL-SQLTUNE: Automatically evolved by SQL Tuning Advisor

AUTO-SQLTUNE: Automatically evolved by Automatic SQL Tuning

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:

Automatically accepted it, or

The DBA has forced its manual acceptance by changing its status to ACCEPTED via procedure DBMS_SPM.ALTER_SQL_PLAN_BASELINE()

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

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：http://www.cnblogs.com/llguanli/p/7007515.html