ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle 11g sql plan baseline(2)调整baselines属性

oracle 11g sql plan baseline(2)调整baselines属性

Linux操作系统 作者:宕机熟手 时间:2013-11-05 16:53:15 0 删除 编辑

测试记录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/29079814/viewspace-775731/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-11-05

  • 博文量
    5
  • 访问量
    8996