Oracle优化——基线自动捕捉（optimizer_capture_sql_plan_baselines，默认值为FALSE）

oracle优化——基线自动捕捉（optimizer_capture_sql_plan_baselines，默认值为false）

sys@ prod> select count(*) from dba_sql_plan_baselines ;

  count(*)
----------
         2

sys@ prod> show parameter spfile

name                                 type        value
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfileprod.ora

这个参数用来控制优化器是否会自己捕捉基线（在sql第一次执行之后自动捕捉）
sys@ prod> alter system set optimizer_capture_sql_plan_baselines = true ;

system altered.

查看当前的sql_handle
sys@ prod> select sql_handle from dba_sql_plan_baselines ;

sql_handle
------------------------------
sys_sql_0cf31d9f7b33b119
sys_sql_a8f88a44571be8dd
sys_sql_a8f88a44571be8dd
sys_sql_b61b88104ed13c53

注意，如果一个sql只是偶尔执行了一个，优化可能不会马上把它加入到基线中，所以多执行几次
sys@ prod> select count(*) from hr.employees ;

  count(*)
----------
       107

… …
sys@ prod> select count(*) from hr.employees ;

  count(*)
----------
       107

sys@ prod> select count(*) from hr.employees ;

  count(*)
----------
       107

sys@ prod> select sql_handle from dba_sql_plan_baselines ;

sql_handle
------------------------------
sys_sql_0cf31d9f7b33b119
sys_sql_a8f88a44571be8dd
sys_sql_a8f88a44571be8dd
sys_sql_a99903edfad71a5e
sys_sql_b61b88104ed13c53
sys_sql_ec276c73273b181f

6 rows selected.

关于这条sql的基线已经被捕捉到了
sys@ prod> select * from table( dbms_xplan.display_sql_plan_baseline('sys_sql_a99903edfad71a5e' , null , 'basic' ) ) ;

plan_table_output
------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
sql handle: sys_sql_a99903edfad71a5e
sql text: select count(*) from hr.employees
--------------------------------------------------------------------------------

--------------------------------------------------------------------------------
plan name: sql_plan_am683xrxdf6ky0892e805         plan id: 143845381
enabled: yes     fixed: no      accepted: yes     origin: auto-capture
--------------------------------------------------------------------------------

plan hash value: 3580537945

-----------------------------------------
| id  | operation        | name         |
-----------------------------------------
|   0 | select statement |              |
|   1 |  sort aggregate  |              |
|   2 |   index full scan| emp_email_uk |
-----------------------------------------

20 rows selected.