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.