全部栏目

首页 操作系统 后端开发 数据库 前端开发 移动端开发
您的当前位置：首页正文

Baseline測試

2024-11-07 来源：个人技术集锦

SQL> show parameter baselines;

NAME TYPE VALUE

optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE

SQL> ALTER SYSTEM SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;

創建測試表
SQL> create table tb_spm_test_lhr(id number,description varchar2(50));
Table created.

declare
type t_tab is table of tb_spm_test_lhr%ROWTYPE;
l_tab t_tab := t_TAB();
BEGIN
FOR i IN 1 … 10000 LOOP
l_tab.extend;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).description := 'Description for ’ || i;
END LOOP;

FORALL i IN l_tab.first .. l_tab.last
    INSERT INTO tb_spm_test_lhr VALUES l_tab(i);
    
COMMIT;

END;
/
EXEC DBMS_STATS.gather_table_stats(USER, ‘tb_spm_test_lhr’, cascade=>TRUE);
SQL> set autot trace
SQL> SELECT description FROM tb_spm_test_lhr WHERE id = 100;

Execution Plan

Plan hash value: 2270350513

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|

| 0 | SELECT STATEMENT | | 1 | 25 | 14 (0)| 00:00
:01 |

|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 25 | 14 (0)| 00:00
:01 |

Predicate Information (identified by operation id):

1 - filter(“ID”=100)

Statistics

      1  recursive calls
      0  db block gets
     47  consistent gets
      0  physical reads
      0  redo size
    546  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)

1 rows processed

获取刚才查询的SQL_ID:
select distinct a.sql_id,a.sql_text from v s q l a w h e r e a . s q l t e x t l i k e ′ a n d a . S Q L T E X T n o t l i k e ′ sql a where a.sql_text like '%SELECT description FROM tb_spm_test_lhr WHERE id = 100%' and a.SQL_TEXT not like '%v sqlawherea.sqlt​extlike′anda.SQLT​EXTnotlike′sql%’ and sql_text not like ‘%EXPLAIN%’;

SQL_ID SQL_TEXT

58bj62hams28u SELECT description FROM tb_spm_test_lhr WHERE id = 100
----使用SQL_ID 从cursor cache中手工捕获执行计划：
SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => ‘&sql_id’);
DBMS_OUTPUT.put_line('Plans Loaded: ’ || l_plans_loaded);
END;
/
Enter value for sql_id: 58bj62hams28u
old 4: l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => ‘&sql_id’);
new 4: l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => ‘58bj62hams28u’);
Plans Loaded: 1
PL/SQL procedure successfully completed.

– --使用DBA_SQL_PLAN_BASELINES视图查看SPM 信息：

SQL> COL sql_handle for a35
SQL> col plan_name for a35
SQL> set lin 300
SQL> SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE ‘%tb_spm_test_lhr%’ AND sql_text NOT LIKE’%dba_sql_plan_baselines%’;

SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX

SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 MANUAL-LOAD YES YES NO

–刷新Share Pool，使下次SQL 执行时必须进行硬解析：
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.

–手工再次加載執行計劃至baseline
SQL> SET SERVEROUTPUT ON
DECLARE
l_plans_loaded PLS_INTEGER;
BEGIN
l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => ‘&sql_id’);
DBMS_OUTPUT.put_line('Plans Loaded: ’ || l_plans_loaded);
END;
/
Enter value for sql_id: 58bj62hams28u
old 4: l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => ‘&sql_id’);
new 4: l_plans_loaded := DBMS_SPM.load_plans_from_cursor_cache(sql_id => '58bj62hams28u ');
Plans Loaded: 0
PL/SQL procedure successfully completed.

–查看baseline
SQL> col sql_handle for a35
SQL> col plan_name for a35
SQL> set lin 300
SQL> SELECT sql_handle, plan_name,enabled, accepted FROM dba_sql_plan_baselines WHERE sql_text LIKE ‘%tb_spm_test_lhr%’ AND sql_text NOT LIKE’%dba_sql_plan_baselines%’;

SQL_HANDLE PLAN_NAME ENA ACC

SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 YES YES

ALTER SYSTEM FLUSH SHARED_POOL;
System altered.

SQL> set autot trace
SQL> SELECT description FROM tb_spm_test_lhr WHERE id = 100;

Execution Plan

Plan hash value: 2270350513

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 25 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 25 | 14 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter(“ID”=100)

Note

  • SQL plan baseline “SQL_PLAN_4y6fmtxbvwws3184920d2” used for this statement

Statistics

    207  recursive calls
     16  db block gets
    350  consistent gets
      0  physical reads
   3064  redo size
    546  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
     17  sorts (memory)
      0  sorts (disk)

1 rows processed

–创建索引，收集统计信息，并查询相同的SQL:
SQL> CREATE INDEX spm_test_tab_idx ON tb_spm_test_lhr(id);
Index created.
SQL> EXEC DBMS_STATS.gather_table_stats(USER,‘tb_spm_test_lhr’, cascade=>TRUE);
PL/SQL procedure successfully completed.
SQL> set autot trace
SQL> SELECT description FROM tb_spm_test_lhr WHERE id = 100;
Execution Plan

Plan hash value: 2270350513

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 25 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 25 | 14 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter(“ID”=100)
Note

  • SQL plan baseline “SQL_PLAN_4y6fmtxbvwws3184920d2” used for this statement
    Statistics
    192  recursive calls
     28  db block gets
    231  consistent gets
      1  physical reads
  11468  redo size
    546  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
     14  sorts (memory)
      0  sorts (disk)
      1  rows processed

–这里我们创建了索引，但是这里还是走的全表扫描，这里使用索引明显才是最优的方案。
查看SPM 视图：
SQL> set autot off
SQL> col sql_handle for a35
SQL> col plan_name for a35
SQL> set lin 300
SQL> SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE ‘%tb_spm_test_lhr%’ AND sql_text NOT LIKE’%dba_sql_plan_baselines%’;
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX

SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 MANUAL-LOAD YES YES NO
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws38b725570 AUTO-CAPTURE YES NO NO

–通过BASELINES查询的结果，可以看到SQL产生了两条执行计划，但是最优的执行计划并没有被标记为ACCEPT，所以没有使用。
–下边我们演化执行计划：演化就是将cost低的执行计划标记为accept：註釋，用手工加載的方式也可以改變accept，如前面的命令
SQL> SET LONG 10000
SQL> SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => ‘&sql_handle’) FROM dual;
Enter value for sql_handle: SQL_4f19d3cf57be7303
old 1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => ‘&sql_handle’) FROM dual
new 1: SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => ‘SQL_4f19d3cf57be7303’) FROM dual

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>‘SQL_4F19D3CF57BE7303’)

                    Evolve SQL Plan Baseline Report

Inputs:

SQL_HANDLE = SQL_4f19d3cf57be7303
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>‘SQL_4F19D3CF57BE7303’)

COMMIT = YES

Plan: SQL_PLAN_4y6fmtxbvwws38b725570

Plan was verified: Time used .1 seconds.
Plan passed performance criterion: 15.34 times better than baseline plan.
Plan was changed to an accepted plan.

                        Baseline Plan      Test Plan       Stats Ratio
                        -------------      ---------       -----------

Execution Status: COMPLETE COMPLETE

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>‘SQL_4F19D3CF57BE7303’)

Rows Processed: 1 1
Elapsed Time(ms): .187 .04 4.68
CPU Time(ms): .222 0
Buffer Gets: 46 3 15.33
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Executions: 1 1

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>‘SQL_4F19D3CF57BE7303’)

                             Report Summary

Number of plans verified: 1
Number of plans accepted: 1

–再次执行SQL:
SQL> set autot trace
SQL> SELECT description FROM tb_spm_test_lhr WHERE id = 100;

Execution Plan

Plan hash value: 841051156

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 (0)| 00:00:01 |

Predicate Information (identified by operation id):

2 - access(“ID”=100)

Note

  • SQL plan baseline “SQL_PLAN_4y6fmtxbvwws38b725570” used for this statement

Statistics

     13  recursive calls
     14  db block gets
     18  consistent gets
      0  physical reads
   3056  redo size
    553  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)

1 rows processed
SQL> set autot trace
SQL> SELECT description FROM tb_spm_test_lhr WHERE id = 100;

Execution Plan

Plan hash value: 841051156

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 25 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR | 1 | 25 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX | 1 | | 1 (0)| 00:00:01 |

Predicate Information (identified by operation id):

2 - access(“ID”=100)

Note

  • SQL plan baseline “SQL_PLAN_4y6fmtxbvwws38b725570” used for this statement

Statistics

     13  recursive calls
     14  db block gets
     18  consistent gets
      0  physical reads
   3056  redo size
    553  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)

1 rows processed

这次正确的使用了索引。因为只有标记为ENABLE和ACCEPT的plan才可以被使用。
下面示例将我们的第一个走全表扫描的执行计划标记为fixed。标记为fixed的执行计划会被优先使用。FIXED表示优化程序仅考虑标记为FIXED的计划，而不考虑其它计划。例如，如果有10个基线计划，其中的三个计划被标记为FIXED，则优化程序将仅使用这三个计划中的最佳计划，而忽略其它所有计划。如果某个SQL计划基线至少包含一个已启用的已修复计划，则该SQL计划基线就是FIXED的。如果在修复的SQL计划基线中添加了新计划，则在手动将这些新计划声明为FIXED之前，无法使用这些新计划。
SQL> set autot off
SQL> select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => ‘&sql_handle’, format => ‘basic’));
Enter value for sql_handle: SQL_4f19d3cf57be7303
old 1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => ‘&sql_handle’, format => ‘basic’))
new 1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => ‘SQL_4f19d3cf57be7303’, format => ‘basic’))

PLAN_TABLE_OUTPUT

SQL handle: SQL_4f19d3cf57be7303
SQL text: SELECT description FROM tb_spm_test_lhr WHERE id = 100

Plan name: SQL_PLAN_4y6fmtxbvwws3184920d2 Plan id: 407445714
Enabled: YES Fixed: NO Accepted: YES Origin: MANUAL-LOAD

PLAN_TABLE_OUTPUT

Plan hash value: 2270350513

| Id | Operation | Name |

| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR |

Plan name: SQL_PLAN_4y6fmtxbvwws38b725570 Plan id: 2339526000

PLAN_TABLE_OUTPUT

Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE

Plan hash value: 841051156

| Id | Operation | Name |

| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR |
| 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX |

PLAN_TABLE_OUTPUT

SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => ‘&sql_handle’,
plan_name => ‘&plan_name’,
attribute_name => ‘fixed’,
attribute_value => ‘YES’);

DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);

END;
/
Enter value for sql_handle: SQL_4f19d3cf57be7303
old 5: sql_handle => ‘&sql_handle’,
new 5: sql_handle => ‘SQL_4f19d3cf57be7303’,
Enter value for plan_name: SQL_PLAN_4y6fmtxbvwws3184920d2
old 6: plan_name => ‘&plan_name’,
new 6: plan_name => ‘SQL_PLAN_4y6fmtxbvwws3184920d2’,
Plans Altered: 1

PL/SQL procedure successfully completed.

–验证：
SQL> set autot off
SQL> col sql_handle for a35
SQL> col plan_name for a35
SQL> set lin 300
SQL> SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE ‘%tb_spm_test_lhr%’ AND sql_text NOT LIKE’%dba_sql_plan_baselines%’;

SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX

SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 MANUAL-LOAD YES YES YES
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws38b725570 AUTO-CAPTURE YES YES NO

SQL> set autot off
SQL> select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => ‘&sql_handle’, format => ‘basic’));
Enter value for sql_handle: SQL_4f19d3cf57be7303
old 1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => ‘&sql_handle’, format => ‘basic’))
new 1: select * from table(dbms_xplan.display_sql_plan_baseline (sql_handle => ‘SQL_4f19d3cf57be7303’, format => ‘basic’))

PLAN_TABLE_OUTPUT

SQL handle: SQL_4f19d3cf57be7303
SQL text: SELECT description FROM tb_spm_test_lhr WHERE id = 100

Plan name: SQL_PLAN_4y6fmtxbvwws3184920d2 Plan id: 407445714
Enabled: YES Fixed: YES Accepted: YES Origin: MANUAL-LOAD

PLAN_TABLE_OUTPUT

Plan hash value: 2270350513

| Id | Operation | Name |

| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR |

Plan name: SQL_PLAN_4y6fmtxbvwws38b725570 Plan id: 2339526000

PLAN_TABLE_OUTPUT

Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE

Plan hash value: 841051156

| Id | Operation | Name |

| 0 | SELECT STATEMENT | |
| 1 | TABLE ACCESS BY INDEX ROWID| TB_SPM_TEST_LHR |
| 2 | INDEX RANGE SCAN | SPM_TEST_TAB_IDX |

PLAN_TABLE_OUTPUT

34 rows selected.

SQL> SELECT SQL_HANDLE,plan_name,origin,enabled,accepted,fixed FROM dba_sql_plan_baselines WHERE sql_text LIKE ‘%tb_spm_test_lhr%’ AND sql_text NOT LIKE’%dba_sql_plan_baselines%’;
SQL_HANDLE PLAN_NAME ORIGIN ENA ACC FIX

SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws3184920d2 MANUAL-LOAD YES YES YES
SQL_4f19d3cf57be7303 SQL_PLAN_4y6fmtxbvwws38b725570 AUTO-CAPTURE YES YES NO

–再次查看我们之前的SQL:
SQL> set autot trace
SQL> SELECT description FROM tb_spm_test_lhr WHERE id = 100;

Execution Plan

Plan hash value: 2270350513

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 1 | 25 | 14 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TB_SPM_TEST_LHR | 1 | 25 | 14 (0)| 00:00:01 |

Predicate Information (identified by operation id):

1 - filter(“ID”=100)

Note

  • SQL plan baseline “SQL_PLAN_4y6fmtxbvwws3184920d2” used for this statement

Statistics

      6  recursive calls
      0  db block gets
     47  consistent gets
      0  physical reads
      0  redo size
    546  bytes sent via SQL*Net to client
    523  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)

1 rows processed
这里已经走了全表扫描，根据前边的示例，我们知道这里走索引会更优，但因为我们将走全表扫描的执行计划设置为fixed，所以优先使用这个执行计划。

其它相關概念及命令語法：
在之前的Blog 里了解了Oracle 11g SQL Plan Management的理论，这篇Blog来演示一些具体的操作示例。
Oracle 11g 新特性 --SQL Plan Management 说明
http:///tianlesoftware/article/details/8292410

官网说明：
Using SQL Plan Management
http://docs.oracle.com/cd/B28359_01/server.111/b28274/optplanmgmt.htm

一. SPM 说明
与Oracle 9i 的outline和10g 的profile比，Oracle 11g的SPM相对更加的灵活。如，一条带有绑定变量的SQL语句，最好的执行计划会根据绑定变量的值而不同，11g以前的方法都无法解决这个问题。在11g中，与adaptive cursor sharing配合，SPM允许你同时接受多个执行计划。执行时，根据不同的变量值，SPM会花费很少的运算从中选择一条最合适的。

Oracle 11g 新特性 – 自适应游标共享(Adaptive Cursor Sharing： ACS) 说明
http:///tianlesoftware/article/details/7573502

SPM 相关的语句日志、计划历史记录和计划基线都存储在SQL 管理库(SMB) 中，该库还包含SQL概要文件。SMB 是数据库字典的一部分，存储在SYSAUX 表空间中。默认情况下，SMB 的空间预算限制被设置为SYSAUX 大小的10%。但是，可以使用DBMS_SPM.CONFIGURE 过程配置SMB，将空间预算更改为介于1% 和50%之间的一个值。
如果SMB 空间超过了定义的百分比限制，则会向预警日志中写入警告。通过清除一些SQL管理对象（如SQL 计划基线或SQL概要文件）来增加SMB 空间限制、增加SYSAUX 大小或者减小SMB 大小之前，将按周生成警报。

SPM相关参数:
optimizer_capture_sql_plan_baselines
optimizer_use_sql_plan_baselines
create_stored_outline
use_stored_outlines

注意事项：
（1） 使用多种方式控制执行计划时：

  • Stored Outline（9i）存在时，它具有最高的优先级。
  • 已经实施的SQL profile（10g）会被自动加入到SQL plan baseline中
  • STA(SQL Tuning Advisor) 会自动接收新的profile，意味着它会生成新的baseline。
    （2）如果可能话，尽量移植到SPM，混合多种方式会变得复杂。

1.1 相关名词说明
SQL Plan Management（SPM）：oracle11g 中提供的新特性，用来更好地控制执行计划。
Plan History：优化器生成的所有执行计划的总称。
SQL Plan Baseline： Plan History里那些被标记为“ACCEPTED”的执行计划的总称。
Plan Evolution：把一条执行计划从Plan History里标记为“ACCEPTED”的过程。
SQL Management Base（SMB）： 字典表里保存的执行计划的总称，包括Plan History，SQL Plan Baseline和SQL profile。

1.2 SPM的特点
1.2.1 与profile和outline相比，更加灵活的控制手段
（1）可以有很多的计划被保存下来，只有"ENABLED"并且"ACCEPTED"的执行计划才可以被选择。
（2）允许有多个"ACCEPTED"的执行计划，根据实际情况进行选择。
（3）可以用手工或者自动的方式，把执行计划演化(evolve)为"ACCEPTED"。 还可以控制只让性能更好的计划被接受。
（4）允许设置"FIXED"的计划。这样其他的计划将不会被选择。

1.1.2 SPM使计划真正的稳定
outline的缺点是太过死板，当数据量大幅度变化时无法做出相应的改变。 SQL proifle的缺点是，当数据量变化时，STA(SQL TuningAdvisor)会不可预知地去更改执行计划。 而SPM则会提供几个完整的plan供选择。

1.3 SPM的控制方式

SPM通过几个标记来实现对执行计划的控制：
（1）Enabled (控制活动)：

  • YES (活动的，但不一定会被使用)
  • NO (可以理解为被标记删除)
    （2）Accepted(控制使用)：
  • YES (只有 “Enabled” 并且“Accepted” 的计划才会被选择使用)
  • NO (如果是“Enabled” 那么只有被evolve成“Accepted”才有可能被执行)
    （3）Fixed(控制优先级)：
  • YES (如果是“Enabled”并且“Accepted”，会优先选择这个计划，这个计划会被视为不需要改变的)
  • NO (普通的计划，无需优先)
    （4）Reproduced(有效性)：
  • YES (优化器可以使用这个计划)
  • NO (计划无效，比如索引被删除)

1.4 SPM如何捕捉（加载）执行计划

1.4.1 自动捕捉

1.4.2 批量导入
导入的baseline都会被自动标记为ACCEPTED， Oralce提供六种方式把计划导入到sql plan baseline中：
（1）从 SQL Tuning Set STS 导入：DBMS_SPM.LOAD_PLANS_FROM_SQLSET
（2）从Cursor Cache中装载：DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
（3）从Stored Outlines中导入： DBMS_SPM.MIGRATE_STORED_OUTLINE
（4）从内存中存在的计划中导入：DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE;
（5）从staging table表中导入：dbms_spm.create_stgtab_baseline
（6）通过staging table从另外一个系统中移植：
DBMS_SPM.CREATE_STGTAB_BASELINE
DBMS_SPM.PACK_STGTAB_BASELINE
DBMS_SPM.UNPACK_STGTAB_BASELINE

1.5 执行计划的选择过程

在OPTIMIZER_USE_SQL_PLAN_BASELINES被设置成默认值TRUE，SQl Plan Baseline就会起作用。

  1. 首先，无论是否存在baseline，oracle都会正常进行硬解析或者软解析，为SQL生成一个执行计划。 由于ACS和bindpeeking的作用，存在baseline的SQL有可能在这时生成一个不同于baseline的执行计划。
  2. 如果baseline不存在，就按生成的计划执行。如果baseline存在，那么要查看history里是否有这个计划，如果没有，就将这个计划插入，并标记为ENABLED,NON-ACCEPTED.
  3. 在baseline中查看是否有FIXED的计划存在，如果存在，执行FIXED的计划，如果存在多个FIXED的计划，根据统计信息重新计算cost，选择cost小的那个。
  4. 如果FIXED的计划不存在，就选择ACCEPTED的计划执行。 如果存在多个ACCEPTED的计划，根据统计信息重新计算cost，选择cost小的那个。

注意：
这里每次重新计算cost的代价不大，因为执行计划是已知的，优化器不必遍历所有的可能，只需根据算法计算出已知计划的cost便可。

1.6 执行计划的演化(evolution)
执行计划的演化指PlanHistory里的执行计划从NON-ACCEPTED，变成ACCEPTED的过程。如果上所述，由于ACS和Bind Peeking的作用，存在baseline的SQL有可能生成新的执行计划，被保存到Plan History中。 Oracle提供了API，通过自动或手工的方式，将一个计划标记为ACCEPTED，这个计划就会被后续的执行所选择。

使用DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE这个API来控制执行计划的演化。语法：
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL, --> NULL表示针对所有SQL
plan_name IN VARCHAR2 := NULL,
time_limit IN INTEGER := DBMS_SPM.AUTO_LIMIT,
verify IN VARCHAR2 := ‘YES’,
commit IN VARCHAR2 := ‘YES’ )
RETURN CLOB;

这里由两个标记控制：
（1）Verify：

  • YES (只有性能更好的计划才会被演化)
  • NO (演化所有的计划)
    （2）Commit：
  • YES (直接演化)
  • NO (只生成报告)

这里可以通过不同的排列组合，达到不同的效果：
（1）自动接收所有性能更好的执行计划(Verify->YES, Commit->YES)
（2）自动接收所有新的执行计划 (Verify->NO,Commit->YES)
（3）比较性能，生成报告，人工确认是否演化(Verify->NO, Commit->NO)

注意：
对于性能的验证的方式，oracle会去实际执行来比较buffer gets

1.7 修改已有的Baseline

通过DBMS_SPM.ALTER_SQL_PLAN_BASELINE来完成。

语法：
DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
attribute_name IN VARCHAR2,
attribute_value IN VARCHAR2 )
RETURN PLS_INTEGER;

如把某个baseline 标记为FIXED：
SET SERVEROUT ON;
DECLARE
x NUMBER;
BEGIN
x := DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle => ‘&&sql_handle’,
plan_name => ‘&&plan_name’,
attribute_name => ‘FIXED’,
attribute_value => ‘YES’ );
END;
/

1.8 相关MOS 文档
Whitepaper: SQL Plan Management in Oracle Database 11g
Loading Hinted Execution Plans into SQLPlan Baseline. [ID 787692.1]
How to Use SQL Plan Management (SPM) -Example Usage (Doc ID 456518.1)
Plan Stability Features (Including SPM) Start Point (Doc ID 1359841.1)
HOW TO LOAD SQL PLANS INTO SPM FROM AWR (Doc ID 789888.1)
Sql Plan Baseline Not always created (Doc ID 788853.1)
Transporting SQL PLAN Baselines from one database to another. (Doc ID 880485.1)

以上内容转自：
https://blogs.oracle.com/Database4CN/entry/oracle_11g_%E9%92%88%E5%AF%B9sql%E6%80%A7%E8%83%BD%E7%9A%84%E6%96%B0%E7%89%B9%E6%80%A7_%E4%B8%89_sql

二． SPM 示例

2.1 自动捕捉
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES参数用来控制SPM的自动捕获，该参数默认值为FALSE。当该参数设置为TRUE时，对于重复执行的SQL 都会被观测，其对应的执行计划也会被加入Plan History。生成的第一个执行计划被标记为ENABLED并且是ACCEPTED，后续的执行计划会被标记为ENABLED但不是ACCEPTED。 仅当在演化的过程中，性能最优的Plan (即标记为ACCEPTED)才会被添加到 SQL Plan baseline。

SQL> show parameteroptimizer_capture_sql_plan_baselines
NAME TYPE VALUE

optimizer_capture_sql_plan_baselinesboolean FALSE

SQL> alter system set optimizer_capture_sql_plan_baselines=true;
System altered.

SQL> show parameter optimizer_capture_sql_plan_baselines
NAME TYPE VALUE

optimizer_capture_sql_plan_baselinesboolean TRUE
SQL>

2.2 手工捕获执行计划
SPM捕获执行计划有大致分自动捕获和手工捕获，手工捕获又有6种方法，具体见1.4.2 小节。

注意：
手工装载的执行计划默认都会被标记为accepted。如果SQL Plan baseline已经存在，那么装载的执行计划就会添加到对应的baseline里，如果不存在，就创建一个baseline。

这里演示最常用的从cursorcache中load plan。使用DBMS_SPM.load_plans_from_cursor_cache函数来完成，关于该函数的具体说明，请参考官方文档。

SQL> col plan_name for a35
SQL> col sql_handle for a30
SQL> col origin for a15
SQL> selectSQL_HANDLE,plan_name,origin,enabled,accepted,fixed from DBA_SQL_PLAN_BASELINES;

SQL_HANDLE PLAN_NAME ORIGIN ENABLE ACCEPT FIXED

SQL_a6f4c0adedb52ad0 SQL_PLAN_adx60prqvaaqhf8e55c8a AUTO-CAPTURE YES YES NO

SQL> DECLARE
2 l_plans_loaded PLS_INTEGER;
3 BEGIN
4 l_plans_loaded :=DBMS_SPM.load_plans_from_cursor_cache(sql_id => ‘axpwqnq0454s9’);
5 END;
6 /

PL/SQL procedure successfully completed.

SQL> selectSQL_HANDLE,plan_name,origin,enabled,accepted,fixed from DBA_SQL_PLAN_BASELINES;

SQL_HANDLE PLAN_NAME ORIGIN ENABLE ACCEPT FIXED

SQL_267afeb2e8216c2d SQL_PLAN_2cyryqbn22v1da82c8876 MANUAL-LOAD YES YES NO
SQL_a6f4c0adedb52ad0 SQL_PLAN_adx60prqvaaqhf8e55c8a AUTO-CAPTURE YES YES NO

SQL>

使用DBMS_SPM.load_plans_from_cursor_cache函数load 之后，在DBA_SQL_PLAN_BASELINES视图中多了一条记录，并且显示该plan 是accepted状态。

2.3 演化SQL Plan Baselines
演化的过程就是把non-accepted 的plan 改成accepted的过程。 对于手工load的执行计划，会自动执行evolving的过程，因此默认就是accepted，而对于自动装载的执行计划，就需要使用EVOLVE_SQL_PLAN_BASELINE函数来实现演化过程。

SQL> SET LONG 10000
SQL> SELECTDBMS_SPM.evolve_sql_plan_baseline(sql_handle => ‘SQL_267afeb2e8216c2d’)FROM dual;

修改已有的baseline
/*********语法
DBMS_SPM.ALTER_SQL_PLAN_BASELINE (
sql_handle IN VARCHAR2 := NULL,
plan_name IN VARCHAR2 := NULL,
attribute_name IN VARCHAR2,
attribute_value IN VARCHAR2 )
RETURN PLS_INTEGER;
************/

SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => ‘SQL_44c9b37ac97e85e9’,
plan_name => ‘SQL_PLAN_49kdmgb4rx1g95cd5cc6d’,
attribute_name => ‘AUTOPURGE’,
attribute_value => ‘NO’);
DBMS_OUTPUT.put_line('Plans Altered: ’ || l_plans_altered);
END;
/

把自动清除机制关闭了

使用hint強制走索引：
select /+ index(test_objects IX_OBJECT_NAME)/ * from test_objects

查詢執行的sql信息信息：
select * from v s q l a r e a w h e r e s q l i d i n ( s e l e c t s q l i d f r o m v sqlarea where sql_id in (select sql_id from v sqlareawheresqli​din(selectsqli​dfromvsql_plan where object_name=upper(‘test_objects’));
select * from v$sql_plan where object_name=upper(‘test_objects’);

sql_id:5ptdb66rf053s

加入baseline
DECLARE
k1 pls_integer;
begin
k1 := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE
(
sql_handle=>‘SQL_44c9b37ac97e85e9’,
sql_id=>‘5ptdb66rf053s’,
plan_hash_value=>127622217
);
end;
/

select * from dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT PLAN_NAME ORIGIN PARSING_SCHEMA_NAME ENABLED ACCEPTED FIXED REPRODUCED AUTOPURGE OPTIMIZER_COST
SQL_44c9b37ac97e85e9 SQL_PLAN_49kdmgb4rx1g95cd5cc6d MANUAL-LOAD SCOTT YES YES NO YES NO 275
SQL_44c9b37ac97e85e9 SQL_PLAN_49kdmgb4rx1g9f1aba20d MANUAL-LOAD SCOTT YES YES NO YES YES 35397

固定為走索引的計劃：
SET SERVEROUTPUT ON
DECLARE
l_plans_altered PLS_INTEGER;
BEGIN
l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(
sql_handle => ‘SQL_44c9b37ac97e85e9’,
plan_name => ‘SQL_PLAN_49kdmgb4rx1g9f1aba20d’,
attribute_name => ‘FIXED’,
attribute_value => ‘YES’);
DBMS_OUTPUT.put_line('Plans Altered: ’ || l_plans_altered);
END;
/

驗證走索引計劃：
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT

Plan hash value: 127622217

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

| 0 | SELECT STATEMENT | | 101K| 15M| 43710 (1)| 00:08:45 |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST_OBJECTS | 101K| 15M| 43710 (1)| 00:08:45 |
| 2 | INDEX FULL SCAN | IX_OBJECT_NAME | 101K| | 428 (1)| 00:00:06 |

Note

  • SQL plan baseline “SQL_PLAN_49kdmgb4rx1g9f1aba20d” used for this statement

13 rows selected.

删除基库里一个执行计划

SET SERVEROUTPUT ON
DECLARE
l_plans_dropped PLS_INTEGER;
BEGIN
l_plans_dropped := DBMS_SPM.drop_sql_plan_baseline (
sql_handle => ‘SQL_44c9b37ac97e85e9’,
plan_name => ‘SQL_PLAN_49kdmgb4rx1g95cd5cc6d’);

DBMS_OUTPUT.put_line(l_plans_dropped);
END;
/
显示全文