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博客 ” ，链接：https://blog.itpub.net/22034023/viewspace-1241481/ ，如需转载，请注明出处，否则将追究法律责任。
