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

Baseline Internal

 我们接着上面一节继续来思考，一个SQL在dba_sql_plan_baselines中存在2个accepted为YES状态的baseline。优化器如何决定使用哪一个？看到网上有些文章提到是依据dba_sql_plan_baselines. OPTIMIZER_COST来决定，哪个baseline对应的执行计划的cost小，就使用哪个。但是很容易验证说法是错误的。我们可以修改dba_sql_plan_baselines视图的基表sqlobj$auxdata中的OPTIMIZER_COST字段，来调整2个baseline的OPTIMIZER_COST的大小，但是实验结果最终证明是无效的，并不是通过dba_sql_plan_baselines. OPTIMIZER_COST的大小来决定使用哪个baseline。实验结果这里不再贴出。那到底是如何决定呢？这个要根据系统的配置来定，我们接着上面的例子，但是先关闭ACS，但是绑定变量窥探的功能保持打开。

test@DLSP>show parameter _optimizer_extended_cursor_sharing_rel   NAME                                   TYPE                   VALUE -------------------------------------- ---------------------- -------- _optimizer_extended_cursor_sharing_rel string                 NONE   test@DLSP>show parameter bind   NAME                                 TYPE                   VALUE ------------------------------------ ---------------------- ----------- _optim_peek_user_binds               boolean                TRUE   test@DLSP>alter system flush shared_pool;   System altered.

提示：针对这个SQL当前在dba_sql_plan_baselines视图中存在2个baseline。

test@DLSP>ALTER SYSTEM FLUSH SHARED_POOL;   System altered.   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: 4130896540   -------------------------------------------------------------------------------------- | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          | |   1 |  SORT AGGREGATE              |       |     1 |    25 |            |          | |   2 |   TABLE ACCESS BY INDEX ROWID| TEST  |   100 |  2500 |     2   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN          | T_IND |   100 |       |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      3 - access("STATUS"=:A)   Note -----    - SQL plan baseline SQL_PLAN_636ys750p7z8519ccc485 used for this statement

当对status的绑定值传入Inactive时，选用了我们进化后的baseline。由于列上存在直方图信息，我们看到了执行计划的输出基数部分非常的准确。这也要归功于绑定变量窥探的作用。上面读者可以再继续试试多次执行这个SQL，执行计划都会一直使用索引扫描。我们继续试验，看看重新刷新共享池，对status的绑定之传入Active会怎么样：

test@DLSP>alter system flush shared_pool;   System altered.   test@DLSP>exec :a :='Active';   PL/SQL procedure successfully completed.   test@DLSP>select count(name) from test where status= :a;   COUNT(NAME) -----------       49900   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 | 49900 |  1218K|    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，执行计划已经走了全表扫描了。虽然baseline的作用是为了稳固执行计划，但是就像我们看到的，由于同一个SQL存在2个可接受的baseline，因此执行计划发生了不稳固的现象，绑定变量窥探的害处在这里又重新体现出来了。其实绑定变量和ACS都会在baseline起作用前就会发生，但是绑定变量窥探和ACS发挥作用后产生的执行计划必须从baseline中选用。因此就会发生前面产生的场景，绑定变量窥探导致已经使用baseline的SQL的执行计划不稳定。这种情况可以通过ACS来解决：

--------开启ACS test@DLSP>alter system set "_optimizer_extended_cursor_sharing_rel"=simple ;   System altered.   test@DLSP>alter system flush shared_pool;   System altered.   test@DLSP>exec :a :='Active';   PL/SQL procedure successfully completed.   test@DLSP>select count(name) from test where status= :a;   COUNT(NAME) -----------       49900   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 | 49900 |  1218K|    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     23 rows selected.   test@DLSP> -- 检查ACS状态 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         452 Y  Y  N   -- 直方图 SELECT   hash_value, sql_id, child_number, bucket_id, COUNT test@DLSP>test@DLSP>  2        FROM v$sql_cs_histogram   3       WHERE sql_id='aa8mzbnrzu42f'   4    ORDER BY sql_id, child_number;   HASH_VALUE SQL_ID                     CHILD_NUMBER  BUCKET_ID      COUNT ---------- -------------------------- ------------ ---------- ----------  805113934 aa8mzbnrzu42f                         0          0          0  805113934 aa8mzbnrzu42f                         0          2          0  805113934 aa8mzbnrzu42f                         0          1          1   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> -- 检查ACS状态 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          2         662 Y  Y  N     test@DLSP>-- 直方图 test@DLSP>SELECT   hash_value, sql_id, child_number, bucket_id, COUNT   2        FROM v$sql_cs_histogram   3       WHERE sql_id='aa8mzbnrzu42f'   4    ORDER BY sql_id, child_number;   HASH_VALUE SQL_ID                     CHILD_NUMBER  BUCKET_ID      COUNT ---------- -------------------------- ------------ ---------- ----------  805113934 aa8mzbnrzu42f                         0          0          1  805113934 aa8mzbnrzu42f                         0          2          0  805113934 aa8mzbnrzu42f                         0          1          1   test@DLSP>select count(name) from test where status= :a;   COUNT(NAME) -----------         100   test@DLSP> -- 检查ACS状态 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          2         662 Y  N  N            1          1         102 Y  Y  Y   test@DLSP>-- 直方图 test@DLSP>SELECT   hash_value, sql_id, child_number, bucket_id, COUNT   2        FROM v$sql_cs_histogram   3       WHERE sql_id='aa8mzbnrzu42f'   4    ORDER BY sql_id, child_number;   HASH_VALUE SQL_ID                     CHILD_NUMBER  BUCKET_ID      COUNT ---------- -------------------------- ------------ ---------- ----------  805113934 aa8mzbnrzu42f                         0          1          1  805113934 aa8mzbnrzu42f                         0          0          1  805113934 aa8mzbnrzu42f                         0          2          0  805113934 aa8mzbnrzu42f                         1          1          0  805113934 aa8mzbnrzu42f                         1          0          1  805113934 aa8mzbnrzu42f                         1          2          0   6 rows selected.   --------查看child_number为1的执行计划 test@DLSP>select * from table(dbms_xplan.display_cursor('aa8mzbnrzu42f',1));   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID  aa8mzbnrzu42f, child number 1 ------------------------------------- select count(name) from test where status= :a   Plan hash value: 4130896540   -------------------------------------------------------------------------------------- | Id  | Operation                    | Name  | Rows  | Bytes | Cost (%CPU)| Time     | -------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT             |       |       |       |     2 (100)|          | |   1 |  SORT AGGREGATE              |       |     1 |    25 |            |          | |   2 |   TABLE ACCESS BY INDEX ROWID| TEST  |   100 |  2500 |     2   (0)| 00:00:01 | |*  3 |    INDEX RANGE SCAN          | T_IND |   100 |       |     1   (0)| 00:00:01 | --------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      3 - access("STATUS"=:A)   Note -----    - SQL plan baseline SQL_PLAN_636ys750p7z8519ccc485 used for this statement   test@DLSP>exec :a :='Active';   PL/SQL procedure successfully completed.   test@DLSP>select count(name) from test where status= :a;   COUNT(NAME) -----------       49900   test@DLSP> -- 检查ACS状态 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          2         662 Y  N  N            1          1         102 Y  Y  Y            2          1         210 Y  Y  Y   test@DLSP>-- 直方图 test@DLSP>SELECT   hash_value, sql_id, child_number, bucket_id, COUNT   2        FROM v$sql_cs_histogram   3       WHERE sql_id='aa8mzbnrzu42f'   4    ORDER BY sql_id, child_number;   HASH_VALUE SQL_ID                     CHILD_NUMBER  BUCKET_ID      COUNT ---------- -------------------------- ------------ ---------- ----------  805113934 aa8mzbnrzu42f                         0          1          1  805113934 aa8mzbnrzu42f                         0          0          1  805113934 aa8mzbnrzu42f                         0          2          0  805113934 aa8mzbnrzu42f                         1          0          1  805113934 aa8mzbnrzu42f                         1          1          0  805113934 aa8mzbnrzu42f                         1          2          0  805113934 aa8mzbnrzu42f                         2          1          1  805113934 aa8mzbnrzu42f                         2          0          0  805113934 aa8mzbnrzu42f                         2          2          0   9 rows selected.   test@DLSP>select * from table(dbms_xplan.display_cursor('aa8mzbnrzu42f',2));   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID  aa8mzbnrzu42f, child number 2 ------------------------------------- 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 | 49900 |  1218K|    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

 

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

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