执行计划的管理---baseline（11g）

来源：互联网发布：泰克飞石知乎编辑：程序博客网时间：2024/04/28 10:30

baseline是oracle11g的新功能。

它是一种新的方式来管理sql_plan,相比之前的sql调优方式，baseline有一种防患于未然的效果，只有经过优化器

评估，不会使得性能下降的sql_plan才会加入到baseline中。具体baseline的知识请参考tuning guide 15章。

当使用了baseline管理执行计划的时候

alter system set optimizer_use_sql_plan_baselines=true;
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
create table gg as select * from all_objects;
analyze table gg estimate statistics;

select * from gg where object_id=4
--sql_id = 0h2mtxura560p

-- sql_plain_name : SQL_PLAN_5sv1s3v6qv6xm00585137
SELECT sql_handle, sql_text, enabled, accepted
FROM dba_sql_plan_baselines
WHERE PLAN_NAME='SQL_PLAN_5sv1s3v6qv6xm00585137'

SELECT sql_handle, sql_text, enabled, accepted, fixed,PLAN_NAME from dba_sql_plan_baselines
where sql_handle = 'SYS_SQL_5c6c381ecd6d9bb3'

SQL_HANDLE SQL_TEXT ENABLE ACCEPT FIXED PLAN_NAME
------------------------------ ---------------------------------------- ------ ------ ------ ---------------------------------------------
SYS_SQL_5c6c381ecd6d9bb3 select * from gg where object_id = 4 YES YES NO SQL_PLAN_5sv1s3v6qv6xm00585137
SYS_SQL_5c6c381ecd6d9bb3 select * from gg where object_id = 4 YES NO NO SQL_PLAN_5sv1s3v6qv6xm35f17884

也可以这样展示：

SELECT * FROM TABLE(
DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(
sql_handle=>'SYS_SQL_5c6c381ecd6d9bb3 ',
format=>'basic'));

SET SERVEROUTPUT ON
SET LONG 10000
DECLARE
report clob;
BEGIN
report := DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(
sql_handle => 'SYS_SQL_5c6c381ecd6d9bb3');
DBMS_OUTPUT.PUT_LINE(report);
END;
/
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_5c6c381ecd6d9bb3
PLAN_NAME =
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY = YES
COMMIT = YES

Plan:
SQL_PLAN_5sv1s3v6qv6xm35f17884
------------------------------------
Plan was verified: Time used .03 seconds.
Plan passed performance criterion:
57.16 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): 1.198 .031 38.65
CPU Time(ms): .999 .111 9
Buffer Gets: 172 3 57.33
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
PL/SQL procedure successfully completed.

SQL> SELECT sql_handle, sql_text, enabled, accepted, fixed,PLAN_NAME from dba_sql_plan_baselines
2 where sql_handle = 'SYS_SQL_5c6c381ecd6d9bb3'
3 /

SQL_HANDLE SQL_TEXT ENABLE ACCEPT FIXED PLAN_NAME
------------------------------ ---------------------------------------- ------ ------ ------ ---------------------------------------------
SYS_SQL_5c6c381ecd6d9bb3 select * from gg where object_id = 4 YES YES NO SQL_PLAN_5sv1s3v6qv6xm00585137
SYS_SQL_5c6c381ecd6d9bb3 select * from gg where object_id = 4 YES YES NO SQL_PLAN_5sv1s3v6qv6xm35f17884

--可以设置一个sql_plan的fixed值为yes
DECLARE
ret PLS_INTEGER;
BEGIN
ret := dbms_spm.alter_sql_plan_baseline(
sql_handle => '&sql_handle',
plan_name=>'&plan_name',
attribute_name=>'&attribute',
attribute_value=>'&value'
);
dbms_output.put_line(ret || ' SQL plan baseline(s) dropped');
END;
/
Enter value for sql_handle: SYS_SQL_d7ca54dcd5e813c7
old 5: sql_handle => '&sql_handle',
new 5: sql_handle => 'SYS_SQL_d7ca54dcd5e813c7',
Enter value for plan_name: SQL_PLAN_dgkknvmayh4y713fa51c2
old 6: plan_name=>'&plan_name',
new 6: plan_name=>'SQL_PLAN_dgkknvmayh4y713fa51c2',
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'

SQL> SELECT sql_handle, sql_text, enabled, accepted, fixed,PLAN_NAME from dba_sql_plan_baselines
2 where sql_handle = 'SYS_SQL_5c6c381ecd6d9bb3'
3 /

SQL_HANDLE SQL_TEXT ENABLE ACCEPT FIXED PLAN_NAME
------------------------------ ---------------------------------------- ------ ------ ------ ---------------------------------------------
SYS_SQL_5c6c381ecd6d9bb3 select * from gg where object_id = 4 YES YES NO SQL_PLAN_5sv1s3v6qv6xm00585137
SYS_SQL_5c6c381ecd6d9bb3 select * from gg where object_id = 4 YES YES YES SQL_PLAN_5sv1s3v6qv6xm35f17884
---都是fixed状态的时候比较 cost
SQL_HANDLE SQL_TEXT ENABLE ACCEPT FIXED PLAN_NAME
------------------------------ ---------------------------------------- ------ ------ ------ ---------------------------------------------
SYS_SQL_5c6c381ecd6d9bb3 select * from gg where object_id = 4 YES YES YES SQL_PLAN_5sv1s3v6qv6xm00585137
SYS_SQL_5c6c381ecd6d9bb3 select * from gg where object_id = 4 YES YES YES SQL_PLAN_5sv1s3v6qv6xm35f17884

--比如：用了 fixed=yes的baseline,因为它优先级别高，都是fixed的时候还是比较cost，谁的低使用谁
SQL> select * from gg where object_id = 4
Execution Plan
----------------------------------------------------------
Plan hash value: 1559536923
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 92 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| GG | 1 | 92 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | GG_IDX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=4)
Note
-----
- SQL plan baseline "SQL_PLAN_5sv1s3v6qv6xm35f17884" used for this statement

alter system set optimizer_use_sql_plan_baselines=FALSE;~~~~~~~~改为false
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