SPM BASELINE怎么用

网友投稿 390 2023-11-29

SPM BASELINE怎么用

这篇文章主要介绍“SPM BASELINE怎么用”，在日常操作中，相信很多人在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

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

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

| 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

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

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

| 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

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

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

| 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

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

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

| 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怎么用”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注网站，小编会继续努力为大家带来更多实用的文章！

标签：参数代码优化方法

暂时没有评论，来抢沙发吧~

SPM BASELINE怎么用

SPM BASELINE怎么用

基线的进化

最近发表

更多内容

推荐文章

解决方案

热评文章