控制执行计划之-SPM BASELINE（一）

SPM BASELINE 是11G出现的技术，它的出现是为了取代outline技术，outline在后面的版本可能会被废弃。outline可以实现的功能baseline都可以实现，相对于outline，baseline还有着更多更优秀的功能，如优化器认为有更好的执行计划时，会自动产生一个baseline，DBA可以通过进化baseline来验证、接受新产生的baseline。baseline与outline一样被设计用来提供稳定的执行计划，以防止执行环境和统计信息等变化导致的执行计划变化，但是就像本章后面所描述的，有些情况也会导致baseline出现不稳固。此外，baseline也可以像outline,sql profile一样在不修改SQL语句的情况下，修正查询的执行计划，本章也会对这一技术进行讲解。

创建baseline

在讲述一些更高级的baseline知识之前，我们先看看如何去手工创建一个baseline。本文提供了三种方式来创建baseline：手工方式创建、自动创建、通过SQL调优集创建。

1)  手工创建

下面的代码创建了一张表T，并且对表T的统计信息进行了分析。表上status字段的值有数据倾斜。列status上分析了直方图。

test@DLSP>CREATE TABLE test   2   AS   3   SELECT ROWNUM id,   4          DBMS_RANDOM.STRING('A', 12) name,   5          DECODE(MOD(ROWNUM, 500), 0, 'Inactive', 'Active') status   6     FROM all_objects a,dba_objects b   7    WHERE ROWNUM <= 50000;   Table created. test@DLSP>begin   2     dbms_stats.gather_table_stats(ownname          =>'test',   3                                   tabname          => 'test',   4                                   no_invalidate    => FALSE,   5                                   estimate_percent => 100,   6                                   force            => true,   7                                   degree         => 5,   8                              method_opt       => 'for columns status size 2',   9                                   cascade          => true);  10   end;  11   /   PL/SQL procedure successfully completed.   test@DLSP>select status,count(*) from test group by status;   STATUS             COUNT(*) ---------------- ---------- Active                49900 Inactive                100

我们看看如何通过手工方式创建baseline。

test@DLSP>var a varchar2(100) test@DLSP>exec :a :='Inactive'   PL/SQL procedure successfully completed. test@DLSP>select count(name) from test where status= :a;   COUNT(NAME) -----------         100   test@DLSP>select * from table(dbms_xplan.display_cursor);   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID  aa8mzbnrzu42f, child number 0 ------------------------------------- select count(name) from test where status= :a   Plan hash value: 1950795681   --------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |       |       |    51 (100)|          | |   1 |  SORT AGGREGATE    |      |     1 |    25 |            |          | |*  2 |   TABLE ACCESS FULL| TEST |   100 |  2500 |    51   (2)| 00:00:01 | ---------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter("STATUS"=:A)

根据上面的输出，我们可以看到我们在对表的status字段做等值查询时，查询计划走了全表扫描，假如这个sql是生产环境的核心SQL，为了防止执行环境发生变化导致对执行计划产生影响，可以通过baseline技术来稳固这个SQL的执行计划。我们通过函数DBMS_SPM.load_plans_from_cursor_cache来对SQL的执行计划进行稳固：

test@DLSP>declare   2 l_pls number;   3 begin   4 l_pls := DBMS_SPM.load_plans_from_cursor_cache(sql_id => 'aa8mzbnrzu42f',   5                                                plan_hash_value => 1950795681   6                                                  );   7 end;   8 /   PL/SQL procedure successfully completed.   test@DLSP>select sql_handle, plan_name, origin, accepted,fixed    2        from dba_sql_plan_baselines                       3       where sql_text like '%count(name)%';      SQL_HANDLE           PLAN_NAME                        ORIGIN          ACCEPT FIXED -------------------- -------------------------------- --------------- ------ ------ SQL_619bd8394153fd05 SQL_PLAN_636ys750p7z856b581ab9   MANUAL-LOAD     YES    NO   test@DLSP>select * from table(dbms_xplan.display_sql_plan_baseline('SQL_619bd8394153fd05',null));   PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------   ------------------------------------------------------------------------------- SQL handle: SQL_619bd8394153fd05 SQL text: select count(name) from test where status= :a -------------------------------------------------------------------------------   ------------------------------------------------------------------------------- Plan name: SQL_PLAN_636ys750p7z856b581ab9         Plan id: 1800936121 Enabled: YES     Fixed: NO      Accepted: YES     Origin: MANUAL-LOAD --------------------------------------------------------------------------------   Plan hash value: 1950795681   --------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |     1 |    25 |    51   (2)| 00:00:01 | |   1 |  SORT AGGREGATE    |      |     1 |    25 |            |          | |*  2 |   TABLE ACCESS FULL| TEST | 25000 |   610K|    51   (2)| 00:00:01 | ---------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter("STATUS"=:A)

上面的代码通过DBMS_SPM包的load_plans_from_cursor_cache函数来创建baseline，使用此函数需要提供sql_id、plan_hash_value等参数。创建完baseline后，可以通过dba_sql_plan_baselines来查看已经创建的baseline的相关信息。sql_handle和plan_name标识出这个SQL特定的基线。 sql_handler非常重要，我们需要靠它来得到关于基线更多的信息，从上面输出字段accepted为YES可以知道这个基线已经被激活，在决定执行计划时将会被查询优化器所使用。我们还可以使用dbms_xplan.display_sql_plan_baseline来查看与此基线结合在一起的执行计划信息。我们可以重新执行SQL，看是否已经使用到了新创建的baseline。

test@DLSP>SELECT child_number, executions, buffer_gets, is_bind_sensitive,IS_SHAREABLE,   2           is_bind_aware   3      FROM v$sql   4     WHERE sql_id='aa8mzbnrzu42f';   CHILD_NUMBER EXECUTIONS BUFFER_GETS IS IS IS ------------ ---------- ----------- -- -- --            0          1           3 N  Y  N   test@DLSP>select count(name) from test where status= :a;   COUNT(NAME) -----------         100   test@DLSP>select * from table(dbms_xplan.display_cursor);   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID  aa8mzbnrzu42f, child number 0   An uncaught error happened in prepare_sql_statement : ORA-01403: no data found   NOTE: cannot fetch plan for SQL_ID: aa8mzbnrzu42f, CHILD_NUMBER: 0       Please verify value of SQL_ID and CHILD_NUMBER;       It could also be that the plan is no longer in cursor cache (check v$sql_plan)   8 rows selected.   test@DLSP>SELECT child_number, executions, buffer_gets, is_bind_sensitive,IS_SHAREABLE,   2           is_bind_aware   3      FROM v$sql   4     WHERE sql_id='aa8mzbnrzu42f';   no rows selected   test@DLSP>select count(name) from test where status= :a;   COUNT(NAME) -----------         100   test@DLSP>select * from table(dbms_xplan.display_cursor);   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID  aa8mzbnrzu42f, child number 0 ------------------------------------- select count(name) from test where status= :a   Plan hash value: 1950795681   --------------------------------------------------------------------------- | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     | --------------------------------------------------------------------------- |   0 | SELECT STATEMENT   |      |       |       |    51 (100)|          | |   1 |  SORT AGGREGATE    |      |     1 |    25 |            |          | |*  2 |   TABLE ACCESS FULL| TEST |   100 |  2500 |    51   (2)| 00:00:01 | ---------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      2 - filter("STATUS"=:A)   Note -----    - SQL plan baseline SQL_PLAN_636ys750p7z856b581ab9 used for this statement

上面的代码中显示，在创建baseline后，第一次SQL时，SQL之前的对应游标会被清除出shared_pool，并且没有产生新游标，上面代码执行select * from table(dbms_xplan.display_cursor);提示找不到child_number为0的游标，此提示并非偶尔，读者可以在各个版本测试，截止到12C都是如此，原因我还不清楚。当然如果在执行SQL前先刷新共享池，将不会导致这一情况，优化器会直接创建一个新的cursor，此cursor使用到了新创建的baseline。我们看到我们第二次执行SQL后，执行计划输出的Note部分：- SQL plan baseline SQL_PLAN_636ys750p7z856b581ab9 used for this statement，说明已经使用到了新创建的baseline。

未完，待续-----------

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-1241477/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-1241477/