经常遇到这样的场景:一些关键业务的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
注:产生这种问题可能是统计信息更新不够及时或优化器一些异常引起,所以有时候别太相信优化器的智能判断。
发表评论 取消回复