11203测试sql baseline
背景
11203
在sys/tmp用户下分别创建test表create table test as select * from dba_objects；
案例1：
Sys—添加索引ind_test1： test(owner,object_type);
Tmp—添加索引ind_test1： test(owner,object_type); ind_test2： test(owner);
并为其收集统计信息
sys执行select /*justin1*/ count(*) from test where wner='SYS'，并生成baseline；
tmp执行同样sql，查看是否会采用该baseline；
参数设置
optimizer_capture_sql_plan_baselines FALSE
optimizer_use_sql_plan_baselines TRUE
OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES enables or disables the automatic recognition of repeatable SQL statements, as well as the generation of SQL plan baselines for such statements.
OPTIMIZER_USE_SQL_PLAN_BASELINES enables or disables the use of SQL plan baselines stored in SQL Management Base. When enabled, the optimizer looks for a SQL plan baseline for the SQL statement being compiled. If one is found in SQL Management Base, then the optimizer will cost each of the baseline plans and pick one with the lowest cost.
执行
--sys
SQL> select /*justin1*/ count(*) from test where wner='SYS';
COUNT(*)
----------
31890
--执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 1286133460
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 108 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX FAST FULL SCAN| IND_TEST1 | 31526 | 277K| 108 (1)| 00:00:02 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
397 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> select sql_id,sql_text from v$sql where sql_text like 'select /*justin1*/%';
SQL_ID SQL_TEXT
------------- ----------------------------------------------------------------------
07fv3kz81d6d8 select /*justin1*/ count(*) from test where wner='SYS'
加载baseline，由于optimizer_capture_sql_plan_baselines=false，默认不会对sql生成baseline
variable cnt number;
execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id=>'07fv3kz81d6d8');
--查看执行计划
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 108 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX FAST FULL SCAN| IND_TEST1 | 31526 | 277K| 108 (1)| 00:00:02 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
Note
-----
- SQL plan baseline "SQL_PLAN_99p6tj8q5jjn842a88a34" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
397 consistent gets
0 physical reads
--tmp
SQL> select /*justin1*/ count(*) from test where wner='SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1286133460
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 108 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX FAST FULL SCAN| IND_TEST1 | 1542 | 13878 | 108 (1)| 00:00:02 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
Note
-----
- SQL plan baseline "SQL_PLAN_99p6tj8q5jjn842a88a34" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
397 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
--但是有更高效的执行计划
SQL> select count(*) from test where wner='SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1445532557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX RANGE SCAN| IND_TEST2 | 1542 | 13878 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
68 consistent gets
0 physical reads
0 redo size
528 bytes sent via SQL*Net to client
524 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
如果某sql已经生成了baseline且发现了新的执行计划，则会为其生成一个新的unaccepted baseline
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines where sql_text like 'select /*justin1*/%';
SQL_HANDLE PARSING_SCHEMA_NAME PLAN_NAME ENA ACC
------------------------------ ------------------------------ ------------------------------ --- ---
SQL_94d4d98a2c58c688 TMP SQL_PLAN_99p6tj8q5jjn838fd23d5 YES NO
SQL_94d4d98a2c58c688 SYS SQL_PLAN_99p6tj8q5jjn842a88a34 YES YES
让tmp接受新的baseline
variable evol_out clob;
execute :evol_out := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SQL_94d4d98a2c58c688',plan_name=>'SQL_PLAN_99p6tj8q5jjn838fd23d5',verify=>'NO');
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines where sql_text like 'select /*justin1*/%';
SQL_HANDLE PARSING_SCHEMA_NAME PLAN_NAME ENA ACC
------------------------------ ------------------------------ ------------------------------ --- ---
SQL_94d4d98a2c58c688 TMP SQL_PLAN_99p6tj8q5jjn838fd23d5 YES YES
SQL_94d4d98a2c58c688 SYS SQL_PLAN_99p6tj8q5jjn842a88a34 YES YES
SQL> select /*justin1*/ count(*) from test where wner='SYS';
Execution Plan
----------------------------------------------------------
Plan hash value: 1445532557
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 5 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX RANGE SCAN| IND_TEST2 | 1542 | 13878 | 5 (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
Note
-----
- SQL plan baseline "SQL_PLAN_99p6tj8q5jjn838fd23d5" used for this statement
此时为sys.test的owner字段添加索引ind_test3，
SQL> create index ind_test3 on sys.test(owner);
Index created
执行计划依旧选择ind_test1
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 108 (1)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX FAST FULL SCAN| IND_TEST1 | 31526 | 277K| 108 (1)| 00:00:02 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OWNER"='SYS')
Note
-----
- SQL plan baseline "SQL_PLAN_99p6tj8q5jjn842a88a34" used for this statement
将索引名字改为ind_test2
alter index ind_test3 rename to ind_test2;
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 88 (0)| 00:00:02 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | INDEX RANGE SCAN| IND_TEST2 | 31526 | 277K| 88 (0)| 00:00:02 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OWNER"='SYS')
Note
-----
- SQL plan baseline "SQL_PLAN_99p6tj8q5jjn838fd23d5" used for this statement
可见若要使用baseline需要其sql text完全一致，即便索引名字也是如此
小结：
默认optimizer_capture_sql_plan_baselines=false， sql不会生成baseline，需要手工调用dbms_spm.load_plans_from_cursor_cache；
一旦生成了baseline，该sql若再有新执行计划则会自动生成unaccepted baseline；
如果sql接受了某baseline，即便当前有更优执行计划也不会立即选用，而是将其设为unaccepted；
此时可执行dbms_spm.evolve_sql_plan_baseline对unaccepted baseline进行验证，若其性能比accepted更佳则改为accepted
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15480802/viewspace-763699/ ，如需转载，请注明出处，否则将追究法律责任。
