修正执行计划
baseline不仅仅可以用来稳固执行计划，还可以像SQL PROFILE、SQL PATCH一样用来修正执行计划，本节会介绍如何通过baseline来修正执行计划。首先构建测试需要的表和索引。
|
test@DLSP>create table t as select * from dba_objects;
Table created.
test@DLSP>create index t_oi_ind on t(object_id);
Index created.
test@DLSP>begin 2 dbms_stats.gather_table_stats(ownname =>'test', 3 tabname => 't', 4 no_invalidate => FALSE, 5 estimate_percent => 100, 6 force => true, 7 degree => 5, 8 method_opt => 'for all columns size 1', 9 cascade => true); 10 end; 11 /
PL/SQL procedure successfully completed.
|
上面的代码创建了测试表t，字段object_id存在索引，分析表的统计信息，不收集直方图。
|
test@DLSP>select /*+ full(t) */count(object_name) from t where object_id=2;
COUNT(OBJECT_NAME) ------------------ 1
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID bb0mwx9rcbptu, child number 0 ------------------------------------- select /*+ full(t) */count(object_name) from t where object_id=2
Plan hash value: 2966233522
--------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 49 (100)| | | 1 | SORT AGGREGATE | | 1 | 24 | | | |* 2 | TABLE ACCESS FULL| T | 1 | 24 | 49 (0)| 00:00:01 | ---------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
2 - filter("OBJECT_ID"=2) |
假如我们想修正这个SQL的执行计划，让查询走上索引扫描。我们可以先构造一个SQL，让构造的SQL使用到索引扫描：
|
test@DLSP>select count(object_name) from t where object_id=2;
COUNT(OBJECT_NAME) ------------------
1
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID 1k6p0yds2rjgp, child number 0 ------------------------------------- select count(object_name) from t where object_id=2
Plan hash value: 3312862475
----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 24 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 24 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_OI_IND | 1 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("OBJECT_ID"=2)
|
上面我们已经构造了一个SQL，执行计划走的是索引扫描。我们现在可以交换2个SQL的执行计划了。
|
test@DLSP>declare 2 m_clob clob; 3 begin 4 select sql_fulltext 5 into m_clob 6 from v$sql 7 where sql_id = 'bb0mwx9rcbptu' --------------原始sql的 sql_id 8 and child_number = 0; -----------------为了让sql只返回一行，也可以rownum=1代替 9 dbms_output.put_line(m_clob); 10 dbms_output.put_line(dbms_spm.load_plans_from_cursor_cache( sql_id => '1k6p0yds2rjgp', ------------构造SQL的 SQL_ID 11 plan_hash_value => 3312862475, -------------------构造SQL的 PLAN_HASH_VALUE 12 sql_text => m_clob, 13 fixed => 'YES', ---------------------禁止演化baseline 14 enabled => 'YES')); 15 16 end; 17 18 / |
通过SPM包的load_plans_from_cursor_cache函数我们索引扫描的执行计划转嫁到了我们原始的SQL上。我们看看再次查询有没有使用到索引扫描：
|
test@DLSP>select /*+ full(t) */count(object_name) from t where object_id=2;
COUNT(OBJECT_NAME) ------------------ 1
test@DLSP>select * from table(dbms_xplan.display_cursor(null,null));
PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------- SQL_ID bb0mwx9rcbptu, child number 2 ------------------------------------- select /*+ full(t) */count(object_name) from t where object_id=2
Plan hash value: 3312862475
----------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 2 (100)| | | 1 | SORT AGGREGATE | | 1 | 24 | | | | 2 | TABLE ACCESS BY INDEX ROWID| T | 1 | 24 | 2 (0)| 00:00:01 | |* 3 | INDEX RANGE SCAN | T_OI_IND | 1 | | 1 (0)| 00:00:01 | -----------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
3 - access("OBJECT_ID"=2)
Note ----- - SQL plan baseline SQL_PLAN_3uq7qskvgtv1s4f379100 used for this statement
|
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/22034023/viewspace-1241483/ ，如需转载，请注明出处，否则将追究法律责任。
