背景

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

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 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');

--檢視執行計劃

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------

-----------------------------------------------------------------------------------

| 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;

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

| 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

https://blogs.oracle.com/optimizer/entry/sql_plan_management_part_3_of_4_evolving_sql_plan_baselines_1

11203測試sql baseline

相關文章