SQL执行计划基线调优

经常遇到这样的场景：一些关键业务的SQL，执行计划是固定的，例如根据某个订单号查询订单的信息，根据用户ID查询用户的详情信息情况等，这个时候为了性能的稳定，会考虑去固化这部分的SQL执行计划，下面是利用Oracle 11g固化执行计划的方法示例：

1，首先创建测试表并收集统计信息：

SQL> drop table t;

SQL> create table t as select * from dba_objects;

SQL> execute dbms_stats.gather_table_stats(ownname => 'SYS',tabname => 'T',cascade => true,method_opt=>'for all columns size 254');

PL/SQL procedure successfully completed

2，然后通过设置OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES参数捕捉SQL PLAN基线：

SQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=true;

Session altered

SQL> select * from t where object_id=100;

SQL> select * from t where object_id=100; --执行两次

SQL> alter session set OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=false;

Session altered

通过查找该SQL的SQL_ID:

select sql_id,sql_text from v$sql where sql_text like 'select * from t where object_id=100%';

SQL_ID SQL_TEXT

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

5cm0va8g584j9 select * from t where object_id=100

再查找其SQL_BASELINE(即SQL_PLAN_NAME):

select sql_id,sql_text,SQL_PLAN_BASELINE from v$sql where sql_id='5cm0va8g584j9';

SQL_ID SQL_TEXT SQL_PLAN_BASELINE

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

5cm0va8g584j9 select * from t where object_id=100 SQL_PLAN_4q08488wmbbup94ecae5c

然后通过PLAN_NAME查找该SQL的SQL_HANDLE：

select sql_handle from dba_sql_plan_baselines where plan_name='SQL_PLAN_4q08488wmbbup94ecae5c';

SQL_HANDLE

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

SQL_4b0104423935af55

最后通过SQL_HANDLE查找SQL BASELINE:

select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle => 'SQL_4b0104423935af55'))


PLAN_TABLE_OUTPUT

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

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

SQL handle: SQL_4b0104423935af55

SQL text: select * from t where object_id=100

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

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

Plan name: SQL_PLAN_4q08488wmbbup94ecae5c Plan id: 2498539100

Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE

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

Plan hash value: 1601196873

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

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

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

| 0 | SELECT STATEMENT | | 1 | 98 | 304 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T | 1 | 98 | 304 (1)| 00:00:04 |

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

此时已经固定了执行计划，就算有性能更好的执行计划产生，也会标志为ENABLE,但NOT ACCEPT状态：

create index t_id on t(object_id);

select * from t where object_id=100;

创建索引后查询：

SELECT sql_handle, plan_name,enabled, accepted FROM dba_sql_plan_baselines WHERE sql_handle = 'SQL_4b0104423935af55';

SQL_HANDLE PLAN_NAME ENABLED ACCEPTED

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

SQL_4b0104423935af55 SQL_PLAN_4q08488wmbbup94ecae5c YES YES

SQL_4b0104423935af55 SQL_PLAN_4q08488wmbbupaf20fe5e YES NO

可见，执行计划并没有采纳，此时进行SQL PLAN 演化可对采取性能较好的执行计划：

SELECT DBMS_SPM.evolve_sql_plan_baseline(sql_handle => 'SQL_4b0104423935af55') from dual;

--演化执行计划：

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

                        Evolve SQL Plan Baseline Report

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

Inputs:

-------

  SQL_HANDLE = SQL_4b0104423935af55

  PLAN_NAME =

  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT

  VERIFY = YES

  COMMIT = YES

Plan: SQL_PLAN_4q08488wmbbupaf20fe5e

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

  Plan was verified: Time used .11 seconds.

  Plan passed performance criterion: 372.93 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): 7.866 .056 140.46

  CPU Time(ms): 7.887 0

  Buffer Gets: 1118 3 372.67

  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

可见，新的执行计划（TEST PLAN)的消耗时间和逻辑读都比原执行计划都少很多），演化后接受了新的执行计划（Number of plans accepted: 1）

再次查询BASELINE状态：

SELECT sql_handle, plan_name,enabled, accepted FROM dba_sql_plan_baselines WHERE sql_handle = 'SQL_4b0104423935af55';

SQL_HANDLE PLAN_NAME ENABLED ACCEPTED

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

SQL_4b0104423935af55 SQL_PLAN_4q08488wmbbup94ecae5c YES YES

SQL_4b0104423935af55 SQL_PLAN_4q08488wmbbupaf20fe5e YES YES

注：产生这种问题可能是统计信息更新不够及时或优化器一些异常引起，所以有时候别太相信优化器的智能判断。