SPMBASELINE怎么用

这篇文章主要介绍“SPM BASELINE怎么用”,在日常操作中,相信很多人在SPM BASELINE怎么用问题上存在疑惑,小编查阅了各式资料,整理出简单好用的操作方法,希望对大家解答”SPM BASELINE怎么用”的疑惑有所帮助!接下来,请跟着小编一起来学习吧!

目前创新互联建站已为近千家的企业提供了网站建设、域名、网页空间、网站托管维护、企业网站设计、柘荣网站维护等服务,公司将坚持客户导向、应用为本的策略,正道将秉承"和谐、参与、激情"的文化,与客户和合作伙伴齐心协力一起成长,共同发展。

基线的进化

如果针对已经创建过baseline的sql,优化器意识到可能有更好的执行计划,那么优化器会自动生成一个baseline,这个baselne在视图中dba_sql_plan_baselines的accepted状态为NO。DBA可以通过两种方式来对baseline进行进化:dbms_spm.evolve_sql_plan_baseline函数和 SQL Tuning Advisor。

test@DLSP>create  index t_ind on test(status);

 

Index created.

 

test@DLSP>select  count(name) from test where status= :a;

 

COUNT(NAME)

-----------

        100

 

test@DLSP>select  sql_handle, plan_name, origin, accepted,fixed 

  2         from dba_sql_plan_baselines                      

  3        where sql_text like '%count(name)%';   

 

SQL_HANDLE           PLAN_NAME                        ORIGIN          ACCEPT FIXED

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

SQL_619bd8394153fd05  SQL_PLAN_636ys750p7z8519ccc485    AUTO-CAPTURE    NO     NO

SQL_619bd8394153fd05  SQL_PLAN_636ys750p7z856b581ab9    MANUAL-LOAD     YES    NO

我们在test的status字段上建立索引后,再次执行查询,发生在dba_sql_plan_baselines中又产生了一个新的baseline,这个baseline的产生方式是:AUTO-CAPTURE,accepted为NO。接下来我们分别通过函数dbms_spm.evolve_sql_plan_baseline和 SQL Tuning Advisor两种方式来进行进化baseline。

1)   dbms_spm包的方式

下面的代码我们通过dbms_spm 包的evolve_sql_plan_baseline函数来进化baseline,verify参数设置为了YES:只有性能确实有提升的情况下才会进行进化。

test@DLSP>SELECT dbms_spm.evolve_sql_plan_baseline(       

  2    sql_handle => 'SQL_619bd8394153fd05',          

  3    plan_name => 'SQL_PLAN_636ys750p7z8519ccc485', 

  4    time_limit => 10,                              

  5    verify => 'yes',                               

  6    commit => 'yes'                                

  7    )                                               

  8    FROM dual;                                     

 

DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_619BD8394153FD05',PLAN_NAME=

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

 

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

                        Evolve SQL Plan  Baseline Report

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

 

Inputs:

-------

  SQL_HANDLE = SQL_619bd8394153fd05

  PLAN_NAME   = SQL_PLAN_636ys750p7z8519ccc485

  TIME_LIMIT = 10

  VERIFY      = yes

  COMMIT      = yes

 

Plan:  SQL_PLAN_636ys750p7z8519ccc485

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

  Plan was verified: Time used .05 seconds.

  Plan passed performance criterion: 2.06  times better than baseline plan.

  Plan was changed to an accepted plan.

 

                            Baseline  Plan      Test Plan       Stats Ratio

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

  Execution Status:              COMPLETE       COMPLETE

  Rows Processed:                       1              1

  Elapsed Time(ms):                 2.167           .253              8.57

  CPU Time(ms):                     2.221           .222                10

  Buffer Gets:                        210            102              2.06

  Physical Read Requests:               0              0

  Physical Write Requests:              0              0

  Physical Read Bytes:                  0              0

  Physical Write Bytes:                 0              0

  Executions:                           1              1

 

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

                                 Report  Summary

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

Number of plans  verified: 1

Number of plans  accepted: 1

函数dbms_spm.evolve_sql_plan_baseline执行后,会产生出一个report,详细的对比了2个baseline对应的执行计划的消耗资源的差异。由于待进化的baseline经过验证后,性能确实有提高,因此优化器接收了这个baseline。如下代码也显示了,视图dba_sql_plan_baselines中的accepted字段也已经从NO变为了YES。重新执行查询,也已经使用到了我们新创建的baseline。

 

test@DLSP>select  sql_handle, plan_name, origin, accepted,fixed 

  2         from dba_sql_plan_baselines                    

  3        where sql_text like '%count(name)%';   

 

SQL_HANDLE           PLAN_NAME                        ORIGIN          ACCEPT FIXED

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

SQL_619bd8394153fd05  SQL_PLAN_636ys750p7z8519ccc485    AUTO-CAPTURE    YES    NO

SQL_619bd8394153fd05  SQL_PLAN_636ys750p7z856b581ab9    MANUAL-LOAD     YES    NO

 

test@DLSP>select  count(name) from test where status= :a;

 

COUNT(NAME)

-----------

        100

 

test@DLSP>select  * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

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

SQL_ID  aa8mzbnrzu42f, child number  0

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

select  count(name) from test where status= :a

 

Plan hash value:  4130896540

 

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

| Id  | Operation                    | Name  | Rows   | Bytes | Cost (%CPU)| Time      |

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

|   0 | SELECT STATEMENT             |       |        |       |     2 (100)|          |

|   1 |   SORT AGGREGATE               |       |     1 |     25 |            |          |

|   2 |    TABLE ACCESS BY INDEX ROWID| TEST   |   100 |  2500 |      2   (0)| 00:00:01 |

|*  3 |     INDEX RANGE SCAN          |  T_IND |   100 |       |      1   (0)| 00:00:01 |

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

 

Predicate  Information (identified by operation id):

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

 

   3 - access("STATUS"=:A)

 

Note

-----

   - SQL plan baseline  SQL_PLAN_636ys750p7z8519ccc485 used for this statement

2)    SQL Tuning Advisor方式

我们重新倒回到baseline还没进化的时候。

test@DLSP>select  sql_handle, plan_name, origin, accepted,fixed 

  2         from dba_sql_plan_baselines                      

  3        where sql_text like '%count(name)%';   

 

SQL_HANDLE           PLAN_NAME                        ORIGIN          ACCEPT FIXED

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

SQL_619bd8394153fd05  SQL_PLAN_636ys750p7z8519ccc485    AUTO-CAPTURE    NO     NO

SQL_619bd8394153fd05  SQL_PLAN_636ys750p7z856b581ab9    MANUAL-LOAD     YES    NO

我们通过dbms_sqltune包的CREATE_TUNING_TASK函数来创建一个调优任务。

test@DLSP>var  c varchar2(100)

test@DLSP>exec  :c := dbms_sqltune.CREATE_TUNING_TASK(SQL_ID=>'aa8mzbnrzu42f')

 

PL/SQL procedure  successfully completed.

 

test@DLSP>exec  dbms_sqltune.execute_tuning_task(task_name => :c)

 

PL/SQL procedure  successfully completed.

 

test@DLSP>select  dbms_sqltune.report_tuning_task(:c) from dual;

 

DBMS_SQLTUNE.REPORT_TUNING_TASK(:C)

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

GENERAL  INFORMATION SECTION

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

Tuning Task  Name   : TASK_980

Tuning Task  Owner  : TEST

Workload  Type      : Single SQL Statement

Scope              : COMPREHENSIVE

Time  Limit(seconds): 1800

Completion  Status  : COMPLETED

Started at         : 07/29/2014 15:48:50

Completed at       : 07/29/2014 15:48:51

 

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

Schema Name: TEST

SQL ID     : aa8mzbnrzu42f

SQL Text   : select count(name) from test where  status= :a

Bind Variables :

 1 -   (VARCHAR2(2000)):Inactive

 

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

FINDINGS SECTION  (2 findings)

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

 

1- SQL Profile  Finding (see explain plans section below)

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

  A potentially better execution plan was  found for this statement.

 

  Recommendation (estimated benefit: 51.46%)

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

  - Consider accepting the recommended SQL  profile. The SQL plan baseline

    corresponding to the plan with the SQL  profile will also be updated to an

    accepted plan.

    execute  dbms_sqltune.accept_sql_profile(task_name => 'TASK_980',

            task_owner => 'TEST', replace  => TRUE);

 

  Validation results

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

  The SQL profile was tested by executing  both its plan and the original plan

  and  measuring their respective execution statistics. A plan may have been

  only partially executed if the other could  be run to completion in less time.

 

                           Original Plan  With SQL Profile  % Improved

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

  Completion Status:            COMPLETE          COMPLETE

  Elapsed Time (s):             .002302           .000358      84.44 %

  CPU Time (s):                 .002199             .0003      86.35 %

  User I/O Time (s):                  0                 0

  Buffer Gets:                      210               102      51.42 %

  Physical Read Requests:             0                 0

  Physical Write Requests:            0                 0

  Physical Read Bytes:                0                 0

  Physical Write Bytes:               0                 0

  Rows Processed:                     1                 1

  Fetches:                            1                 1

  Executions:                         1                 1

 

  Notes

  -----

  1. Statistics for the original plan were  averaged over 10 executions.

  2. Statistics for the SQL profile plan were  averaged over 10 executions.

 

 

调优任务执行结束后会生成一个report,report里给出了建议,让我们接受一个sql profile来完成优化任务。

test@DLSP>execute  dbms_sqltune.accept_sql_profile(task_name => 'TASK_980',task_owner =>  'TEST', replace => TRUE);

 

 

PL/SQL procedure  successfully completed.

 

test@DLSP>select  sql_handle, plan_name, origin, accepted,fixed 

  2         from dba_sql_plan_baselines                    

  3        where sql_text like '%count%';   

 

SQL_HANDLE           PLAN_NAME                      ORIGIN           ACCEPT FIXED

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

SQL_619bd8394153fd05  SQL_PLAN_636ys750p7z856b581ab9 MANUAL-LOAD      YES     NO

SQL_619bd8394153fd05  SQL_PLAN_636ys750p7z8519ccc485 AUTO-CAPTURE     YES     NO

 

test@DLSP>select  * from table(dbms_xplan.display_cursor);

 

PLAN_TABLE_OUTPUT

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

SQL_ID  aa8mzbnrzu42f, child number 0

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

select  count(name) from test where status= :a

 

Plan hash value:  4130896540

 

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

|  Id  | Operation                    | Name  | Rows   | Bytes | Cost (%CPU)| Time      |

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

|   0 | SELECT STATEMENT             |       |        |       |     2 (100)|          |

|   1 |   SORT AGGREGATE               |       |     1 |     25 |            |          |

|   2 |    TABLE ACCESS BY INDEX ROWID| TEST   |   100 |  2500 |      2   (0)| 00:00:01 |

|*  3 |     INDEX RANGE SCAN          |  T_IND |   100 |       |      1   (0)| 00:00:01 |

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

 

Predicate  Information (identified by operation id):

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

 

   3 - access("STATUS"=:A)

 

Note

-----

   - SQL profile SYS_SQLPROF_0147811f30c60000  used for this statement

   - SQL plan baseline  SQL_PLAN_636ys750p7z8519ccc485 used for this statement

接受SQL PROFILE后,之前为不可接受状态的baseline也已经变为可接受状态。执行这个SQL后查看执行计划,已经走了索引扫描,而且执行计划的Note部分显示,这个SQL同时使用到了SQL profile和baseline。这里我们可以简单的证明一下,一个SQL语句可以同时使用到SQL profile和baseline,并且两个都会发挥作用。我们上面例子里,虽然通过Note部分看到SQL profile和baseline都使用了,但是由于2个所实现的功能都是一样的,都是让执行计划走索引扫描,因此不能确认两个都发挥了作用或者说不能确认是哪个发挥了作用。我们可以构造一个例子:

1)让SQL profile做一件事,这个事baseline没有做

2)让baseline做一件事,这个事SQL profile没有做

3)上面所做的两个事保证不能冲突(比如一个全表扫描,一个索引扫描会冲突)

我们可以让baseline不动,然后删除生成的SQL profile,然后重新为这个SQL产生一个SQL profile,增加gather_plan_statistics这个hint到SQL里。

test@DLSP>exec  dbms_sqltune.drop_sql_profile('profile_aa8mzbnrzu42f_dwrose');

 

PL/SQL procedure  successfully completed.

 

test@DLSP>var  a varchar2(100)

test@DLSP>exec  :a :='Inactive';

 

PL/SQL procedure  successfully completed.

 

test@DLSP>select  count(name) from test where status= :a;

 

COUNT(NAME)

-----------

        100

test@DLSP>@profile

Enter value for  sql_id: aa8mzbnrzu42f

 

PLAN_TABLE_OUTPUT

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

SQL_ID  aa8mzbnrzu42f, child number 0

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

select  count(name) from test where status= :a

 

Plan hash value:  4130896540

 

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

|  Id  | Operation                    | Name  | Rows   | Bytes | Cost (%CPU)| Time      |

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

|   0 | SELECT STATEMENT             |        |       |       |      2 (100)|          |

|   1 |   SORT AGGREGATE               |       |     1 |     25 |            |          |

|   2 |    TABLE ACCESS BY INDEX ROWID| TEST   |   100 |  2500 |      2   (0)| 00:00:01 |

|*  3 |     INDEX RANGE SCAN          |  T_IND |   100 |       |      1   (0)| 00:00:01 |

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

 

Outline Data

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

 

  /*+

      BEGIN_OUTLINE_DATA

      IGNORE_OPTIM_EMBEDDED_HINTS

      OPTIMIZER_FEATURES_ENABLE('11.2.0.3')

      DB_VERSION('11.2.0.3')

      OPT_PARAM('optimizer_dynamic_sampling'  10)

       OPT_PARAM('_optimizer_skip_scan_enabled' 'false')

      ALL_ROWS

      OUTLINE_LEAF(@"SEL$1")

      INDEX_RS_ASC(@"SEL$1"  "TEST"@"SEL$1" ("TEST"."STATUS"))

      END_OUTLINE_DATA

  */

 

Predicate  Information (identified by operation id):

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

 

   3 - access("STATUS"=:A)

 

Note

-----

   - SQL plan baseline  SQL_PLAN_636ys750p7z8519ccc485 used for this statement

 

 

40 rows selected.

 

Enter value for  hint_text: gather_plan_statistics

 

Profile  profile_aa8mzbnrzu42f_dwrose created.

 

test@DLSP>select  count(name) from test where status= :a;

 

COUNT(NAME)

-----------

        100

 

test@DLSP>select  * from table(dbms_xplan.display_cursor(null,null,'iostats last'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  aa8mzbnrzu42f, child number 0

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

select  count(name) from test where status= :a

 

Plan hash value:  4130896540

 

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

|  Id  | Operation                    | Name  | Starts | E-Rows | A-Rows |   A-Time    | Buffers |

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

|   0 | SELECT STATEMENT             |       |       1 |        |      1  |00:00:00.01 |     102 |

|   1 |   SORT AGGREGATE               |       |      1 |       1 |      1 |00:00:00.01 |     102 |

|   2 |    TABLE ACCESS BY INDEX ROWID| TEST   |      1 |    100 |     100 |00:00:00.01 |     102 |

|*  3 |     INDEX RANGE SCAN          | T_IND |      1 |     100 |    100 |00:00:00.01 |       2 |

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

 

Predicate  Information (identified by operation id):

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

 

   3 - access("STATUS"=:A)

 

Note

-----

   - SQL profile profile_aa8mzbnrzu42f_dwrose  used for this statement

   - SQL plan baseline  SQL_PLAN_636ys750p7z8519ccc485 used for this statement

到此,关于“SPM BASELINE怎么用”的学习就结束了,希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习,快去试试吧!若想继续学习更多相关知识,请继续关注创新互联网站,小编会继续努力为大家带来更多实用的文章!


网页名称:SPMBASELINE怎么用
网站URL:http://myzitong.com/article/ghdjoj.html