oracle 11g sql plan baseline(2)調整baselines屬性

測試記錄sql plan baselines的更新屬性

SQL> show user
USER is "SYS"
SQL> drop table t3;

Table dropped.

SQL> create table t3 (a int);

Table created.

SQL> declare
2      begin
3      for i in 1..1000 loop
4      insert into t3 values(i);
5      end loop;
6      commit;
7      end;
8      /

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_table_stats('SYS','T3');

PL/SQL procedure successfully completed.

SQL> select * from t3 where a=3;

A
----------
3

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5dpupnmss7tuc, child number 0
-------------------------------------
select * from t3 where a=3

Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     2 (100)|          |
|* 1 | TABLE ACCESS FULL| T3   |     1 |     3 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=3)

18 rows selected.
此時PLAN 為fts，未使用baselines

將走fts的執行計劃做成baselines
SQL> DECLARE
2        ret PLS_INTEGER;
3      BEGIN
4        ret := dbms_spm.load_plans_from_cursor_cache(sql_id          => '&sql_id',
5                                                     plan_hash_value => NULL);
6        dbms_output.put_line(ret || ' SQL plan baseline(s) created');
7      END;
8     /
Enter value for sql_id: 5dpupnmss7tuc
old   4:       ret := dbms_spm.load_plans_from_cursor_cache(sql_id          => '&sql_id',
new   4:       ret := dbms_spm.load_plans_from_cursor_cache(sql_id          => '5dpupnmss7tuc',

PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, sql_text, enabled, accepted
2 FROM dba_sql_plan_baselines
WHERE creator = user
3 4 AND created > systimestamp - to_dsinterval('0 00:15:00');

SQL_HANDLE                     SQL_TEXT             ENA ACC
------------------------------ -------------------- --- ---
SYS_SQL_e7f5742a31533a9c       select * from t3 whe YES YES
                               re a=3

SQL> desc dba_sql_plan_baselines;
Name                                                                                                                                                                     Null?    Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SIGNATURE                                                                                                                                                                NOT NULL NUMBER
SQL_HANDLE                                                                                                                                                               NOT NULL VARCHAR2(30)
SQL_TEXT                                                                                                                                                                 NOT NULL CLOB
PLAN_NAME                                                                                                                                                                NOT NULL VARCHAR2(30)
CREATOR                                                                                                                                                                   VARCHAR2(30)
ORIGIN                                                                                                                                                                    VARCHAR2(14)
PARSING_SCHEMA_NAME                                                                                                                                                       VARCHAR2(30)
DESCRIPTION                                                                                                                                                               VARCHAR2(500)
VERSION                                                                                                                                                                   VARCHAR2(64)
CREATED                                                                                                                                                                  NOT NULL TIMESTAMP(6)
LAST_MODIFIED                                                                                                                                                             TIMESTAMP(6)
LAST_EXECUTED                                                                                                                                                             TIMESTAMP(6)
LAST_VERIFIED                                                                                                                                                             TIMESTAMP(6)
ENABLED                                                                                                                                                                   VARCHAR2(3)
ACCEPTED                                                                                                                                                                  VARCHAR2(3)
FIXED                                                                                                                                                                     VARCHAR2(3)
AUTOPURGE                                                                                                                                                                 VARCHAR2(3)
OPTIMIZER_COST                                                                                                                                                            NUMBER
MODULE                                                                                                                                                                    VARCHAR2(48)
ACTION                                                                                                                                                                    VARCHAR2(32)
EXECUTIONS                                                                                                                                                                NUMBER
ELAPSED_TIME                                                                                                                                                              NUMBER
CPU_TIME                                                                                                                                                                  NUMBER
BUFFER_GETS                                                                                                                                                               NUMBER
DISK_READS                                                                                                                                                                NUMBER
DIRECT_WRITES                                                                                                                                                             NUMBER
ROWS_PROCESSED                                                                                                                                                            NUMBER
FETCHES                                                                                                                                                                   NUMBER
END_OF_FETCH_COUNT                                                                                                                                                        NUMBER

SQL> select sql_text,enabled,accepted,fixed, plan_name from dba_sql_plan_baselines where sql_handle='SYS_SQL_e7f5742a31533a9c';

SQL_TEXT ENA ACC FIX PLAN_NAME
-------------------- --- --- --- ------------------------------
select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c2dae97d6
re a=3

建立一個index
SQL> create index t3_id on t3(a);

Index created.

SQL> select * from t3 where a=3;

A
----------
3

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5dpupnmss7tuc, child number 1
-------------------------------------
select * from t3 where a=3

Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     2 (100)|          |
|* 1 | TABLE ACCESS FULL| T3   |     1 |     3 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=3)

Note
-----
- SQL plan baseline SYS_SQL_PLAN_31533a9c2dae97d6 used for this statement

22 rows selected.

使用了baselines 如果未使用的話應該走index

檢視各項屬性
SQL> select sql_text,enabled,accepted,fixed, plan_name from dba_sql_plan_baselines where sql_handle='SYS_SQL_e7f5742a31533a9c';

SQL_TEXT ENA ACC FIX PLAN_NAME
-------------------- --- --- --- ------------------------------
select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c2dae97d6~~~baselines走fts的
re a=3

select * from t3 whe YES NO NO SYS_SQL_PLAN_31533a9c3523b9c8~~~~新生成的baselins 走index的
re a=3

新生成的baselines如何產生的，看(1)測試記錄

簡單說下幾個引數
enable:defalut yes，如果為no baselines不可以使用
accepted:新生成的baselines為no,第一次生成的為yes，只有為yes，query optimizer才會使用這個baseline，no的需要演化成yes(後面會測試演化)
fixed:default no,當yes時如果存在多個baseline 都enable,都可接受（accept=yes），此時比較cost,query optimizer選cost的baselines用，但fixed為yes則優先用fixed=yes的
另外oracle trouble shooting performance中說如果為fixed 那麼baseline將無法演化
autopurge:是否自動清除 baseline，在保留時間內未使用的，自動清除，預設yes

開始更改baselines的屬性,將新生成的走index的baseline fixed改為yes
SQL> DECLARE
2    ret PLS_INTEGER;
3 BEGIN
4    ret := dbms_spm.alter_sql_plan_baseline(
5       sql_handle      => '&sql_handle',
6 plan_name=>'&plan_name',
7 attribute_name=>'&attribute',
8 attribute_value=>'&value'
9           );
10   dbms_output.put_line(ret || ' SQL plan baseline(s) dropped');
11 END;
12 /
Enter value for sql_handle: SYS_SQL_e7f5742a31533a9c
old   5:      sql_handle      => '&sql_handle',
new   5:      sql_handle      => 'SYS_SQL_e7f5742a31533a9c',
Enter value for plan_name: SYS_SQL_PLAN_31533a9c3523b9c8
old   6: plan_name=>'&plan_name',
new   6: plan_name=>'SYS_SQL_PLAN_31533a9c3523b9c8',
Enter value for attribute: fixed
old   7: attribute_name=>'&attribute',
new   7: attribute_name=>'fixed',
Enter value for value: yes
old   8: attribute_value=>'&value'
new   8: attribute_value=>'yes'

PL/SQL procedure successfully completed.

SQL> select sql_text,enabled,accepted,fixed, plan_name from dba_sql_plan_baselines where sql_handle='SYS_SQL_e7f5742a31533a9c';

SQL_TEXT ENA ACC FIX PLAN_NAME
-------------------- --- --- --- ------------------------------
select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c2dae97d6
re a=3

select * from t3 whe YES NO YES SYS_SQL_PLAN_31533a9c3523b9c8 ~~~~~~~~~~~yes了
re a=3

下面演化這個新fixed=yes的新baseline（演化具體過程原理，單獨測試）
SQL> select dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_e7f5742a31533a9c',plan_name=>'SYS_SQL_PLAN_31533a9c3523b9c8',time_limit=>10,verify=>'yes',commit=>'yes') from dual;

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_E7F5742A31533A9C',PLAN_NA
--------------------------------------------------------------------------------

-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------

Inputs:
-------
SQL_HANDLE = SYS_SQL_e7f5742a31533a9c
PLAN_NAME = SYS_SQL_PLAN_31533a9c3523b9c8
TIME_LIMIT = 10
VERIFY = yes

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_E7F5742A31533A9C',PLAN_NA
--------------------------------------------------------------------------------
COMMIT = yes

Plan: SYS_SQL_PLAN_31533a9c3523b9c8
-----------------------------------
Plan was verified: Time used .02 seconds.
Passed performance criterion: Compound improvement ratio >= 2.05.
Plan was changed to an accepted plan.

                      Baseline Plan      Test Plan     Improv. Ratio
                      -------------      ---------     -------------
Execution Status:        COMPLETE       COMPLETE

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_E7F5742A31533A9C',PLAN_NA
--------------------------------------------------------------------------------
Rows Processed:                 1              1
Elapsed Time(ms):               0              0
CPU Time(ms):                   1              0
Buffer Gets:                    4              2                 2
Disk Reads:                     0              1                 0
Direct Writes:                  0              0
Fetches:                        0              1                 0
Executions:                     1              1

-------------------------------------------------------------------------------
Report Summary

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_E7F5742A31533A9C',PLAN_NA
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.

發現可以演化完成，即便設定為fixed

SQL> select sql_text,enabled,accepted,fixed, plan_name from dba_sql_plan_baselines where sql_handle='SYS_SQL_e7f5742a31533a9c';

SQL_TEXT ENA ACC FIX PLAN_NAME
-------------------- --- --- --- ------------------------------
select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c2dae97d6
re a=3

select * from t3 whe YES YES YES SYS_SQL_PLAN_31533a9c3523b9c8~~~~~~~~~~~走index的baseline現在可接受且還為fixed
re a=3

將走index的baseline,fixed設定為no

SQL> DECLARE
2    ret PLS_INTEGER;
3 BEGIN
4    ret := dbms_spm.alter_sql_plan_baseline(
5       sql_handle      => '&sql_handle',
6 plan_name=>'&plan_name',
7 attribute_name=>'&attribute',
8 attribute_value=>'&value'
9           );
10   dbms_output.put_line(ret || ' SQL plan baseline(s) dropped');
11 END;
12 /
Enter value for sql_handle: SYS_SQL_PLAN_31533a9c3523b9c8
old   5:      sql_handle      => '&sql_handle',
new   5:      sql_handle      => 'SYS_SQL_PLAN_31533a9c3523b9c8',
Enter value for plan_name: SYS_SQL_PLAN_31533a9c3523b9c8
old   6: plan_name=>'&plan_name',
new   6: plan_name=>'SYS_SQL_PLAN_31533a9c3523b9c8',
Enter value for attribute: fixed
old   7: attribute_name=>'&attribute',
new   7: attribute_name=>'fixed',
Enter value for value: no
old   8: attribute_value=>'&value'
new   8: attribute_value=>'no'
DECLARE
*
ERROR at line 1:
ORA-38131: specified SQL handle SYS_SQL_PLAN_31533a9c3523b9c8 does not exist
ORA-06512: at "SYS.DBMS_SPM", line 2340
ORA-06512: at line 4

SQL> /
Enter value for sql_handle: SYS_SQL_e7f5742a31533a9c
old   5:      sql_handle      => '&sql_handle',
new   5:      sql_handle      => 'SYS_SQL_e7f5742a31533a9c',
Enter value for plan_name: SYS_SQL_PLAN_31533a9c3523b9c8
old   6: plan_name=>'&plan_name',
new   6: plan_name=>'SYS_SQL_PLAN_31533a9c3523b9c8',
Enter value for attribute: fixed
old   7: attribute_name=>'&attribute',
new   7: attribute_name=>'fixed',
Enter value for value: no
old   8: attribute_value=>'&value'
new   8: attribute_value=>'no'

PL/SQL procedure successfully completed.

SQL> select sql_text,enabled,accepted,fixed, plan_name from dba_sql_plan_baselines where sql_handle='SYS_SQL_e7f5742a31533a9c';

SQL_TEXT ENA ACC FIX PLAN_NAME
-------------------- --- --- --- ------------------------------
select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c2dae97d6
re a=3

select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c3523b9c8~~~~~~~~~~no了
re a=3

現在有兩個accept=yes的baseline
SQL> select * from t3 where a=3;

A
----------
3

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5dpupnmss7tuc, child number 0
-------------------------------------
select * from t3 where a=3

Plan hash value: 254505518

--------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |     1 (100)|          |
|* 1 | INDEX RANGE SCAN| T3_ID |     1 |     3 |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"=3)

Note
-----
- SQL plan baseline SYS_SQL_PLAN_31533a9c3523b9c8 used for this statement

22 rows selected.
可以看到query optimizer用了走index 的baselines ,原因是走index cost更小

SQL> select sql_text,enabled,accepted,fixed, plan_name from dba_sql_plan_baselines where sql_handle='SYS_SQL_e7f5742a31533a9c';

SQL_TEXT ENA ACC FIX PLAN_NAME
-------------------- --- --- --- ------------------------------
select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c2dae97d6
re a=3

select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c3523b9c8
re a=3

現在將走fts的baseline fix設定為yes
SQL> DECLARE
2    ret PLS_INTEGER;
3 BEGIN
4    ret := dbms_spm.alter_sql_plan_baseline(
5       sql_handle      => '&sql_handle',
6 plan_name=>'&plan_name',
7 attribute_name=>'&attribute',
8 attribute_value=>'&value'
9           );
10   dbms_output.put_line(ret || ' SQL plan baseline(s) dropped');
11 END;
12 /
Enter value for sql_handle: SYS_SQL_e7f5742a31533a9c
old   5:      sql_handle      => '&sql_handle',
new   5:      sql_handle      => 'SYS_SQL_e7f5742a31533a9c',
Enter value for plan_name: SYS_SQL_PLAN_31533a9c2dae97d6
old   6: plan_name=>'&plan_name',
new   6: plan_name=>'SYS_SQL_PLAN_31533a9c2dae97d6',
Enter value for attribute: fixed
old   7: attribute_name=>'&attribute',
new   7: attribute_name=>'fixed',
Enter value for value: yes
old   8: attribute_value=>'&value'
new   8: attribute_value=>'yes'

PL/SQL procedure successfully completed.

SQL> select sql_text,enabled,accepted,fixed, plan_name from dba_sql_plan_baselines where sql_handle='SYS_SQL_e7f5742a31533a9c';

SQL_TEXT ENA ACC FIX PLAN_NAME
-------------------- --- --- --- ------------------------------
select * from t3 whe YES YES YES SYS_SQL_PLAN_31533a9c2dae97d6~~~~~yes了
re a=3

select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c3523b9c8
re a=3

SQL> select * from t3 where a=3;

A
----------
3

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5dpupnmss7tuc, child number 2
-------------------------------------
select * from t3 where a=3

Plan hash value: 4161002650

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     2 (100)|          |
|* 1 | TABLE ACCESS FULL| T3   |     1 |     3 |     2   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("A"=3)

Note
-----
- SQL plan baseline SYS_SQL_PLAN_31533a9c2dae97d6 used for this statement

22 rows selected.

可以看到走fts了，用了 fixed=yes的baseline,因為它優先順序別高，都fixed還是比cost，誰小用誰

關閉使用baselins,db 預設如果有baseline用baseline
SQL> show parameter optimizer_use

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
optimizer_use_invisible_indexes      boolean     FALSE
optimizer_use_pending_statistics     boolean     FALSE
optimizer_use_sql_plan_baselines     boolean     TRUE~~~~~~~~~~~~~~~~
SQL> alter system set optimizer_use_sql_plan_baselines=FALSE;~~~~~~~~改為false

System altered.

SQL> select * from t3 where a=3;

A
----------
3

SQL> SELECT * FROM table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5dpupnmss7tuc, child number 0
-------------------------------------
select * from t3 where a=3

Plan hash value: 254505518

--------------------------------------------------------------------------
| Id | Operation        | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |       |       |     1 (100)|          |
|* 1 | INDEX RANGE SCAN| T3_ID |     1 |     3 |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("A"=3)

18 rows selected.

發現沒有使用baselins 沒有這些 ( Note
-----
- SQL plan baseline SYS_SQL_PLAN_31533a9c2dae97d6 used for this statement)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12020513/viewspace-628959/，如需轉載，請註明出處，否則將追究法律責任。

oracle 11g sql plan baseline(2)調整baselines屬性

相關文章