测试记录sql plan baselines的 更新属性
SQL> show user
USER is "SYS"
SQL> drop table t3;
Table dropped.
SQL> create table t3 (a int);
Table created.
SQL> declare
2 begin
3 for i in 1..1000 loop
4 insert into t3 values(i);
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> execute dbms_stats.gather_table_stats('SYS','T3');
PL/SQL procedure successfully completed.
SQL> select * from t3 where a=3;
A
----------
3
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5dpupnmss7tuc, child number 0
-------------------------------------
select * from t3 where a=3
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T3 | 1 | 3 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=3)
18 rows selected.
此时PLAN 为fts，未使用baselines
将走fts的执行计划做成baselines
SQL> DECLARE
2 ret PLS_INTEGER;
3 BEGIN
4 ret := dbms_spm.load_plans_from_cursor_cache(sql_id => '&sql_id',
5 plan_hash_value => NULL);
6 dbms_output.put_line(ret || ' SQL plan baseline(s) created');
7 END;
8 /
Enter value for sql_id: 5dpupnmss7tuc
old 4: ret := dbms_spm.load_plans_from_cursor_cache(sql_id => '&sql_id',
new 4: ret := dbms_spm.load_plans_from_cursor_cache(sql_id => '5dpupnmss7tuc',
PL/SQL procedure successfully completed.
SQL> SELECT sql_handle, sql_text, enabled, accepted
2 FROM dba_sql_plan_baselines
WHERE creator = user
3 4 AND created > systimestamp - to_dsinterval('0 00:15:00');
SQL_HANDLE SQL_TEXT ENA ACC
------------------------------ -------------------- --- ---
SYS_SQL_e7f5742a31533a9c select * from t3 whe YES YES
re a=3
SQL> desc dba_sql_plan_baselines;
Name Null? Type
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SIGNATURE NOT NULL NUMBER
SQL_HANDLE NOT NULL VARCHAR2(30)
SQL_TEXT NOT NULL CLOB
PLAN_NAME NOT NULL VARCHAR2(30)
CREATOR VARCHAR2(30)
ORIGIN VARCHAR2(14)
PARSING_SCHEMA_NAME VARCHAR2(30)
DESCRIPTION VARCHAR2(500)
VERSION VARCHAR2(64)
CREATED NOT NULL TIMESTAMP(6)
LAST_MODIFIED TIMESTAMP(6)
LAST_EXECUTED TIMESTAMP(6)
LAST_VERIFIED TIMESTAMP(6)
ENABLED VARCHAR2(3)
ACCEPTED VARCHAR2(3)
FIXED VARCHAR2(3)
AUTOPURGE VARCHAR2(3)
OPTIMIZER_COST NUMBER
MODULE VARCHAR2(48)
ACTION VARCHAR2(32)
EXECUTIONS NUMBER
ELAPSED_TIME NUMBER
CPU_TIME NUMBER
BUFFER_GETS NUMBER
DISK_READS NUMBER
DIRECT_WRITES NUMBER
ROWS_PROCESSED NUMBER
FETCHES NUMBER
END_OF_FETCH_COUNT NUMBER
SQL> select sql_text,enabled,accepted,fixed, plan_name from dba_sql_plan_baselines where sql_handle='SYS_SQL_e7f5742a31533a9c';
SQL_TEXT ENA ACC FIX PLAN_NAME
-------------------- --- --- --- ------------------------------
select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c2dae97d6
re a=3
建立一个index
SQL> create index t3_id on t3(a);
Index created.
SQL> select * from t3 where a=3;
A
----------
3
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5dpupnmss7tuc, child number 1
-------------------------------------
select * from t3 where a=3
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T3 | 1 | 3 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=3)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_31533a9c2dae97d6 used for this statement
22 rows selected.
使用了baselines 如果未使用的话 应该走index
查看各项属性
SQL> select sql_text,enabled,accepted,fixed, plan_name from dba_sql_plan_baselines where sql_handle='SYS_SQL_e7f5742a31533a9c';
SQL_TEXT ENA ACC FIX PLAN_NAME
-------------------- --- --- --- ------------------------------
select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c2dae97d6~~~baselines走fts的
re a=3
select * from t3 whe YES NO NO SYS_SQL_PLAN_31533a9c3523b9c8~~~~新生成的baselins 走index的
re a=3
新生成的baselines如何产生的 ，看(1)测试记录
简单说下 几个参数
enable:defalut yes，如果为no baselines不可以使用
accepted:新生成的baselines为no,第一次生成的为yes，只有为yes，query optimizer才会使用这个baseline，no的需要演化成yes(后面会测试演化)
fixed:default no,当yes时 如果存在多个baseline 都enable,都可接受（accept=yes），此时比较cost,query optimizer选cost的baselines用，但fixed为yes则优先用fixed=yes的
另外oracle trouble shooting performance中说 如果为fixed 那么baseline将无法演化
autopurge:是否自动清除 baseline，在保留时间内未使用的，自动清除，默认yes
开始更改baselines的属性,将新生成的走index的baseline fixed改为yes
SQL> DECLARE
2 ret PLS_INTEGER;
3 BEGIN
4 ret := dbms_spm.alter_sql_plan_baseline(
5 sql_handle => '&sql_handle',
6 plan_name=>'&plan_name',
7 attribute_name=>'&attribute',
8 attribute_value=>'&value'
9 );
10 dbms_output.put_line(ret || ' SQL plan baseline(s) dropped');
11 END;
12 /
Enter value for sql_handle: SYS_SQL_e7f5742a31533a9c
old 5: sql_handle => '&sql_handle',
new 5: sql_handle => 'SYS_SQL_e7f5742a31533a9c',
Enter value for plan_name: SYS_SQL_PLAN_31533a9c3523b9c8
old 6: plan_name=>'&plan_name',
new 6: plan_name=>'SYS_SQL_PLAN_31533a9c3523b9c8',
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'
PL/SQL procedure successfully completed.
SQL> select sql_text,enabled,accepted,fixed, plan_name from dba_sql_plan_baselines where sql_handle='SYS_SQL_e7f5742a31533a9c';
SQL_TEXT ENA ACC FIX PLAN_NAME
-------------------- --- --- --- ------------------------------
select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c2dae97d6
re a=3
select * from t3 whe YES NO YES SYS_SQL_PLAN_31533a9c3523b9c8 ~~~~~~~~~~~yes了
re a=3
下面演化这个新fixed=yes的新baseline（演化具体过程原理，单独测试）
SQL> select dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SYS_SQL_e7f5742a31533a9c',plan_name=>'SYS_SQL_PLAN_31533a9c3523b9c8',time_limit=>10,verify=>'yes',commit=>'yes') from dual;
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_E7F5742A31533A9C',PLAN_NA
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline Report
-------------------------------------------------------------------------------
Inputs:
-------
SQL_HANDLE = SYS_SQL_e7f5742a31533a9c
PLAN_NAME = SYS_SQL_PLAN_31533a9c3523b9c8
TIME_LIMIT = 10
VERIFY = yes
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_E7F5742A31533A9C',PLAN_NA
--------------------------------------------------------------------------------
COMMIT = yes
Plan: SYS_SQL_PLAN_31533a9c3523b9c8
-----------------------------------
Plan was verified: Time used .02 seconds.
Passed performance criterion: Compound improvement ratio >= 2.05.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Improv. Ratio
------------- --------- -------------
Execution Status: COMPLETE COMPLETE
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_E7F5742A31533A9C',PLAN_NA
--------------------------------------------------------------------------------
Rows Processed: 1 1
Elapsed Time(ms): 0 0
CPU Time(ms): 1 0
Buffer Gets: 4 2 2
Disk Reads: 0 1 0
Direct Writes: 0 0
Fetches: 0 1 0
Executions: 1 1
-------------------------------------------------------------------------------
Report Summary
DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(SQL_HANDLE=>'SYS_SQL_E7F5742A31533A9C',PLAN_NA
--------------------------------------------------------------------------------
-------------------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 1.
发现可以演化完成，即便设置为fixed
SQL> select sql_text,enabled,accepted,fixed, plan_name from dba_sql_plan_baselines where sql_handle='SYS_SQL_e7f5742a31533a9c';
SQL_TEXT ENA ACC FIX PLAN_NAME
-------------------- --- --- --- ------------------------------
select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c2dae97d6
re a=3
select * from t3 whe YES YES YES SYS_SQL_PLAN_31533a9c3523b9c8~~~~~~~~~~~走index的baseline现在可接受 且还为fixed
re a=3
将走index的baseline,fixed设置为no
SQL> DECLARE
2 ret PLS_INTEGER;
3 BEGIN
4 ret := dbms_spm.alter_sql_plan_baseline(
5 sql_handle => '&sql_handle',
6 plan_name=>'&plan_name',
7 attribute_name=>'&attribute',
8 attribute_value=>'&value'
9 );
10 dbms_output.put_line(ret || ' SQL plan baseline(s) dropped');
11 END;
12 /
Enter value for sql_handle: SYS_SQL_PLAN_31533a9c3523b9c8
old 5: sql_handle => '&sql_handle',
new 5: sql_handle => 'SYS_SQL_PLAN_31533a9c3523b9c8',
Enter value for plan_name: SYS_SQL_PLAN_31533a9c3523b9c8
old 6: plan_name=>'&plan_name',
new 6: plan_name=>'SYS_SQL_PLAN_31533a9c3523b9c8',
Enter value for attribute: fixed
old 7: attribute_name=>'&attribute',
new 7: attribute_name=>'fixed',
Enter value for value: no
old 8: attribute_value=>'&value'
new 8: attribute_value=>'no'
DECLARE
*
ERROR at line 1:
ORA-38131: specified SQL handle SYS_SQL_PLAN_31533a9c3523b9c8 does not exist
ORA-06512: at "SYS.DBMS_SPM", line 2340
ORA-06512: at line 4
SQL> /
Enter value for sql_handle: SYS_SQL_e7f5742a31533a9c
old 5: sql_handle => '&sql_handle',
new 5: sql_handle => 'SYS_SQL_e7f5742a31533a9c',
Enter value for plan_name: SYS_SQL_PLAN_31533a9c3523b9c8
old 6: plan_name=>'&plan_name',
new 6: plan_name=>'SYS_SQL_PLAN_31533a9c3523b9c8',
Enter value for attribute: fixed
old 7: attribute_name=>'&attribute',
new 7: attribute_name=>'fixed',
Enter value for value: no
old 8: attribute_value=>'&value'
new 8: attribute_value=>'no'
PL/SQL procedure successfully completed.
SQL> select sql_text,enabled,accepted,fixed, plan_name from dba_sql_plan_baselines where sql_handle='SYS_SQL_e7f5742a31533a9c';
SQL_TEXT ENA ACC FIX PLAN_NAME
-------------------- --- --- --- ------------------------------
select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c2dae97d6
re a=3
select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c3523b9c8~~~~~~~~~~no了
re a=3
现在有两个accept=yes的baseline
SQL> select * from t3 where a=3;
A
----------
3
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5dpupnmss7tuc, child number 0
-------------------------------------
select * from t3 where a=3
Plan hash value: 254505518
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| T3_ID | 1 | 3 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"=3)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_31533a9c3523b9c8 used for this statement
22 rows selected.
可以看到query optimizer用了走index 的baselines ,原因是走index cost更小
SQL> select sql_text,enabled,accepted,fixed, plan_name from dba_sql_plan_baselines where sql_handle='SYS_SQL_e7f5742a31533a9c';
SQL_TEXT ENA ACC FIX PLAN_NAME
-------------------- --- --- --- ------------------------------
select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c2dae97d6
re a=3
select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c3523b9c8
re a=3
现在将走fts的baseline fix设置为yes
SQL> DECLARE
2 ret PLS_INTEGER;
3 BEGIN
4 ret := dbms_spm.alter_sql_plan_baseline(
5 sql_handle => '&sql_handle',
6 plan_name=>'&plan_name',
7 attribute_name=>'&attribute',
8 attribute_value=>'&value'
9 );
10 dbms_output.put_line(ret || ' SQL plan baseline(s) dropped');
11 END;
12 /
Enter value for sql_handle: SYS_SQL_e7f5742a31533a9c
old 5: sql_handle => '&sql_handle',
new 5: sql_handle => 'SYS_SQL_e7f5742a31533a9c',
Enter value for plan_name: SYS_SQL_PLAN_31533a9c2dae97d6
old 6: plan_name=>'&plan_name',
new 6: plan_name=>'SYS_SQL_PLAN_31533a9c2dae97d6',
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'
PL/SQL procedure successfully completed.
SQL> select sql_text,enabled,accepted,fixed, plan_name from dba_sql_plan_baselines where sql_handle='SYS_SQL_e7f5742a31533a9c';
SQL_TEXT ENA ACC FIX PLAN_NAME
-------------------- --- --- --- ------------------------------
select * from t3 whe YES YES YES SYS_SQL_PLAN_31533a9c2dae97d6~~~~~yes了
re a=3
select * from t3 whe YES YES NO SYS_SQL_PLAN_31533a9c3523b9c8
re a=3
SQL> select * from t3 where a=3;
A
----------
3
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5dpupnmss7tuc, child number 2
-------------------------------------
select * from t3 where a=3
Plan hash value: 4161002650
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | TABLE ACCESS FULL| T3 | 1 | 3 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("A"=3)
Note
-----
- SQL plan baseline SYS_SQL_PLAN_31533a9c2dae97d6 used for this statement
22 rows selected.
可以看到走fts了，用了 fixed=yes的baseline,因为它优先级别高，都fixed还是比cost，谁小用谁
关闭使用baselins,db 默认 如果有baseline用baseline
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~~~~~~~~~~~~~~~~
SQL> alter system set optimizer_use_sql_plan_baselines=FALSE;~~~~~~~~改为false
System altered.
SQL> select * from t3 where a=3;
A
----------
3
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 5dpupnmss7tuc, child number 0
-------------------------------------
select * from t3 where a=3
Plan hash value: 254505518
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | INDEX RANGE SCAN| T3_ID | 1 | 3 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"=3)
18 rows selected.
发现没有使用baselins 没有这些 ( Note
-----
- SQL plan baseline SYS_SQL_PLAN_31533a9c2dae97d6 used for this statement)
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12020513/viewspace-628959/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-628959/
465
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
