sql profile和baseline的协作关系
测试环境：11202 单实例
创建两个表
create table t1 as select object_id,object_name from dba_objects;
create table t2 as select * from dba_objects;
exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T1');
exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T2');
create index t2_idx on t2(object_id);
本次试验反复执行如下sql，验证baseline和profile对其执行计划的影响
select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id = t2.object_id;
当前执行计划
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id = t2.object_id;
52 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1960724093
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3762 | 146K| 244 (3)| 00:00:03 |
| 1 | MERGE JOIN | | 3762 | 146K| 244 (3)| 00:00:03 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 75237 | 808K| 141 (1)| 00:00:02 |
| 3 | INDEX FULL SCAN | T2_IDX | 75237 | | 17 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 3762 | 106K| 103 (5)| 00:00:02 |
|* 5 | TABLE ACCESS FULL | T1 | 3762 | 106K| 102 (4)| 00:00:02 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1771 consistent gets
0 physical reads
0 redo size
2773 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
52 rows processed
将当前cursor加载为baseline
SQL> variable cnt number;
SQL> execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id=>'bbh0jjgs297s4');
PL/SQL procedure successfully completed.
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines where sql_text like 'select t1.*,t2.owner%';
SQL_HANDLE PARSING_SCHEMA_NAME PLAN_NAME ENA ACC
------------------------------ ------------------------------ ------------------------------ --- ---
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0e768f510 YES YES
再次执行该sql，执行计划变为
Execution Plan
----------------------------------------------------------
Plan hash value: 1960724093
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3762 | 146K| 244 (3)| 00:00:03 |
| 1 | MERGE JOIN | | 3762 | 146K| 244 (3)| 00:00:03 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 75237 | 808K| 141 (1)| 00:00:02 |
| 3 | INDEX FULL SCAN | T2_IDX | 75237 | | 17 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 3762 | 106K| 103 (5)| 00:00:02 |
|* 5 | TABLE ACCESS FULL | T1 | 3762 | 106K| 102 (4)| 00:00:02 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
Note
-----
- SQL plan baseline "SQL_PLAN_7t3w708ggxzx0e768f510" used for this statement
Statistics
----------------------------------------------------------
26 recursive calls
16 db block gets
1789 consistent gets
0 physical reads
3024 redo size
2773 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
52 rows processed
使用sql profile查看是否有更优解
var tuning_task varchar2(100);
declare
l_sql_id v$session.prev_sql_id%TYPE;
l_tuning_task varchar2(30);
begin
l_sql_id := 'bbh0jjgs297s4';
l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
:tuning_task := l_tuning_task;
dbms_sqltune.execute_tuning_task(l_tuning_task);
dbms_output.put_line(l_tuning_task);
end;
/
set long 10000
col task format a300
select dbms_sqltune.report_tuning_task(:tuning_task) as task from dual;
输出如下
TASK
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_5811
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 06/11/2013 03:26:59
Completed at : 06/11/2013 03:27:02
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : bbh0jjgs297s4
SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
and t1.object_id = t2.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 74.87%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_5811',
task_owner => 'SYS', 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): .085682 .030687 64.18 %
CPU Time (s): .085587 .030695 64.13 %
User I/O Time (s): 0 0
Buffer Gets: 1763 442 74.92 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 52 52
Fetches: 52 52
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.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1960724093
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 2080 | 244 (3)| 00:00:03 |
| 1 | MERGE JOIN | | 52 | 2080 | 244 (3)| 00:00:03 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 75237 | 808K| 141 (1)| 00:00:02 |
| 3 | INDEX FULL SCAN | T2_IDX | 75237 | | 17 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 52 | 1508 | 103 (5)| 00:00:02 |
|* 5 | TABLE ACCESS FULL | T1 | 52 | 1508 | 102 (4)| 00:00:02 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
2- Using SQL Profile
--------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 2080 | 112 (4)| 00:00:02 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 52 | 2080 | 112 (4)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 52 | 1508 | 102 (4)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-------------------------------------------------------------------------------
接受profile
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_5811',task_owner => 'SYS', replace => TRUE);
PL/SQL procedure successfully completed.
注意：baseline自动添加一条记录，且状态为accepted
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines where sql_text like 'select t1.*,t2.owner%';
SQL_HANDLE PARSING_SCHEMA_NAME PLAN_NAME ENA ACC
------------------------------ ------------------------------ ------------------------------ --- ---
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0ceb9aeed YES YES
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0e768f510 YES YES
该sql执行计划变为
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id = t2.object_id;
52 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 2080 | 112 (4)| 00:00:02 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 52 | 2080 | 112 (4)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 52 | 1508 | 102 (4)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_013f32261e050000" used for this statement
- SQL plan baseline "SQL_PLAN_7t3w708ggxzx0ceb9aeed" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
455 consistent gets
0 physical reads
0 redo size
2777 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52 rows processed
同时引用了profile和新生成的baseline。
删除索引
SQL> drop index t2_idx ;
Index dropped.
再次查看执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 2080 | 400 (3)| 00:00:05 |
|* 1 | HASH JOIN | | 52 | 2080 | 400 (3)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| T1 | 52 | 1508 | 102 (4)| 00:00:02 |
| 3 | TABLE ACCESS FULL| T2 | 75237 | 808K| 296 (2)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
Note
-----
- SQL profile "SYS_SQLPROF_013f32261e050000" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1440 consistent gets
0 physical reads
0 redo size
2777 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52 rows processed
只剩下profile独舞，重新添加该索引，名字变动一下
SQL> create index t1_idx on t2(object_id);
Index created.
查看执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 4237736469
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 2080 | 112 (4)| 00:00:02 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 52 | 2080 | 112 (4)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 52 | 1508 | 102 (4)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T1_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_013f32261e050000" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
455 consistent gets
0 physical reads
0 redo size
2777 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52 rows processed
Baseline依旧没有出现，将索引改回原名，baseline才重新生效
SQL> alter index t1_idx rename to t2_idx;
Index altered.
执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 2080 | 112 (4)| 00:00:02 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 52 | 2080 | 112 (4)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 52 | 1508 | 102 (4)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_013f32261e050000" used for this statement
- SQL plan baseline "SQL_PLAN_7t3w708ggxzx0ceb9aeed" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
455 consistent gets
0 physical reads
0 redo size
2777 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52 rows processed
删除profile,该baseline还在，但是没有了profile的协助，优化器无法选择效率更高的SQL_PLAN_7t3w708ggxzx0ceb9aeed，执行计划也变回最初的那个
SQL> exec dbms_sqltune.DROP_SQL_PROFILE('SYS_SQLPROF_013f32261e050000');
PL/SQL procedure successfully completed.
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select t1.*,t2.owner%';
SQL_HANDLE PARSI PLAN_NAME ENA ACC OPTIMIZER_COST
------------------------------ ----- ------------------------------ --- --- --------------
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0ceb9aeed YES YES
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0e768f510 YES YES 244
执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 1960724093
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3762 | 146K| 209 (3)| 00:00:03 |
| 1 | MERGE JOIN | | 3762 | 146K| 209 (3)| 00:00:03 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 75237 | 808K| 141 (1)| 00:00:02 |
| 3 | INDEX FULL SCAN | T2_IDX | 75237 | | 17 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 3762 | 106K| 68 (8)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T1 | 3762 | 106K| 67 (6)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
Note
-----
- SQL plan baseline "SQL_PLAN_7t3w708ggxzx0e768f510" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1771 consistent gets
0 physical reads
0 redo size
2840 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
52 rows processed
只能删除SQL_PLAN_7t3w708ggxzx0e768f510，oracle在别无选择的情况下才选择SQL_PLAN_7t3w708ggxzx0ceb9aeed
SQL> exec :cnt := dbms_spm.DROP_SQL_PLAN_BASELINE('SQL_7c8f87021efeffa0','SQL_PLAN_7t3w708ggxzx0e768f510');
PL/SQL procedure successfully completed.
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select t1.*,t2.owner%';
SQL_HANDLE PARSI PLAN_NAME ENA ACC OPTIMIZER_COST
------------------------------ ----- ------------------------------ --- --- --------------
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0ceb9aeed YES YES
执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3762 | 146K| 820 (1)| 00:00:10 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 3762 | 146K| 820 (1)| 00:00:10 |
|* 3 | TABLE ACCESS FULL | T1 | 3762 | 106K| 67 (6)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL plan baseline "SQL_PLAN_7t3w708ggxzx0ceb9aeed" used for this statement
Statistics
----------------------------------------------------------
6 recursive calls
8 db block gets
455 consistent gets
0 physical reads
0 redo size
2844 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52 rows processed
而此时多出一条baseline，属于正常行为；
默认optimizer_capture_sql_plan_baselines=false， sql不会生成baseline，需要手工调用dbms_spm.load_plans_from_cursor_cache或其他子API；
一旦生成了baseline，该sql若再有新执行计划则会自动生成unaccepted baseline；
如果sql接受了某baseline，即便当前有更优执行计划也不会立即选用，而是将其设为unaccepted；
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select t1.*,t2.owner%';
SQL_HANDLE PARSI PLAN_NAME ENA ACC OPTIMIZER_COST
------------------------------ ----- ------------------------------ --- --- --------------
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0b4ddbcf5 YES NO 260
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0ceb9aeed YES YES
可调用evolve_sql_plan_baseline对unaccepted baseline进行评估，如果性能更佳则自动转换为accepted
SQL> execute :evol_out := dbms_spm.evolve_sql_plan_baseline();
PL/SQL procedure successfully completed.
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select t1.*,t2.owner%';
SQL_HANDLE PARSI PLAN_NAME ENA ACC OPTIMIZER_COST
------------------------------ ----- ------------------------------ --- --- --------------
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0b4ddbcf5 YES NO 260
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0ceb9aeed YES YES
强制转换
SQL> execute :evol_out := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SQL_7c8f87021efeffa0',plan_name=>'SQL_PLAN_7t3w708ggxzx0b4ddbcf5',verify=>'NO');
PL/SQL procedure successfully completed.
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select t1.*,t2.owner%';
SQL_HANDLE PARSI PLAN_NAME ENA ACC OPTIMIZER_COST
------------------------------ ----- ------------------------------ --- --- --------------
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0b4ddbcf5 YES YES 260
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0ceb9aeed YES YES
执行计划也随之转变
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3762 | 146K| 260 (4)| 00:00:04 |
|* 1 | HASH JOIN | | 3762 | 146K| 260 (4)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 3762 | 106K| 67 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 75237 | 808K| 191 (3)| 00:00:03 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
Note
-----
- SQL plan baseline "SQL_PLAN_7t3w708ggxzx0b4ddbcf5" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1440 consistent gets
0 physical reads
0 redo size
2844 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52 rows processed
结论：
一旦sql接受了profile，只要sql text不更改(忽略大小写和空格)，该profile就一直生效，可随着实际执行环境的改变实时生成不同的执行计划；
SPM会自动为accepted profile生成一条accepted baseline；
而一个baseline只对应一个sql plan，且对执行环境更敏感，哪怕引用的索引名字发生改动，该baseline便不再起作用；
Profile的优先级应该高于baseline；
后记：
貌似profile比较霸道，只要它在sql就必须使用，一旦起到副作用就毒害无穷；
同样的试验，以下是在11203 2节点rac下的表现
create table t1 as select object_id,object_name from dba_objects;
create table t2 as select * from dba_objects;
exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T1');
exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T2');
create index t2_idx on t2(object_id);
select /*+ index(t2) use_nl(t1 t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id = t2.object_id;
最初的执行计划
SQL> select /*+ index(t2) use_nl(t1 t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id = t2.object_id;
330 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4856 | 203K| 9842 (1)| 00:01:59 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 4856 | 203K| 9842 (1)| 00:01:59 |
|* 3 | TABLE ACCESS FULL | T1 | 4856 | 137K| 126 (2)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 14 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
760 consistent gets
0 physical reads
0 redo size
15791 bytes sent via SQL*Net to client
755 bytes received via SQL*Net from client
23 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
330 rows processed
将其加载至baseline
variable cnt number;
execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id=>'dvuw49t2aw37s');
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4856 | 203K| 9842 (1)| 00:01:59 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 4856 | 203K| 9842 (1)| 00:01:59 |
|* 3 | TABLE ACCESS FULL | T1 | 4856 | 137K| 126 (2)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 14 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL plan baseline "SQL_PLAN_6vcpdhw1pj1w8ceb9aeed" used for this statement
Statistics
----------------------------------------------------------
15 recursive calls
14 db block gets
773 consistent gets
0 physical reads
3220 redo size
15791 bytes sent via SQL*Net to client
755 bytes received via SQL*Net from client
23 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
330 rows processed
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines where sql_text like 'select /*+ index(t2) use_nl(t1 t2) */%';
SQL_HANDLE PARSING_SCHEMA_NAME PLAN_NAME ENA ACC
------------------------------ ------------------------------ ------------------------------ --- ---
SQL_6db2ad8703588788 SYS SQL_PLAN_6vcpdhw1pj1w8ceb9aeed YES YES
利用profile查看是否有更优解
var tuning_task varchar2(100);
declare
l_sql_id v$session.prev_sql_id%TYPE;
l_tuning_task varchar2(30);
begin
l_sql_id := 'dvuw49t2aw37s';
l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
:tuning_task := l_tuning_task;
dbms_sqltune.execute_tuning_task(l_tuning_task);
dbms_output.put_line(l_tuning_task);
end;
set long 10000
col task format a300
select dbms_sqltune.report_tuning_task(:tuning_task) as task from dual;
SQL> select dbms_sqltune.report_tuning_task(:tuning_task) as task from dual;
TASK
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_29160
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 06/11/2013 02:57:15
Completed at : 06/11/2013 02:57:19
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : dvuw49t2aw37s
SQL Text : select /*+ index(t2) use_nl(t1 t2) */ t1.*,t2.owner from t1,t2
where t1.object_name like '%T1%' and t1.object_id = t2.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 98.83%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_29160',
task_owner => 'SYS', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 96 will improve its response time
98.84% over the original plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 11.66% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.
Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0
Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 0
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4856 | 203K| 9842 (1)| 00:01:59 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 4856 | 203K| 9842 (1)| 00:01:59 |
|* 3 | TABLE ACCESS FULL | T1 | 4856 | 137K| 126 (2)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 14 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2- Using Parallel Execution
---------------------------
Plan hash value: 1933047403
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4856 | 203K| 114 (0)| 00:00:02 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | | | | | Q1,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | | | | | Q1,00 | PCWP | |
| 4 | NESTED LOOPS | | 4856 | 203K| 114 (0)| 00:00:02 | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL | T1 | 4856 | 137K| 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 7 | INDEX RANGE SCAN | T2_IDX | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 14 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL)
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-------------------------------------------------------------------------------
接受profile
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_29160',task_owner => 'SYS', replace => TRUE, profile_type =>DBMS_SQLTUNE.PX_PROFILE);
SPM自动接受由profile产生的baseline并且状态为accepted
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines where sql_text like 'select /*+ index(t2) use_nl(t1 t2) */%';
SQL_HANDLE PARSING_SCHEMA_NAME PLAN_NAME ENA ACC
------------------------------ ------------------------------ ------------------------------ --- ---
SQL_6db2ad8703588788 SYS SQL_PLAN_6vcpdhw1pj1w8a1ef6cc1 YES YES
SQL_6db2ad8703588788 SYS SQL_PLAN_6vcpdhw1pj1w8ceb9aeed YES YES
执行计划变为
Execution Plan
----------------------------------------------------------
Plan hash value: 1933047403
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4856 | 203K| 114 (0)| 00:00:02 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | | | | | Q1,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | | | | | Q1,00 | PCWP | |
| 4 | NESTED LOOPS | | 4856 | 203K| 114 (0)| 00:00:02 | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL | T1 | 4856 | 137K| 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 7 | INDEX RANGE SCAN | T2_IDX | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 14 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL)
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
- SQL profile "SYS_SQLPROF_023f3209456c0000" used for this statement
- SQL plan baseline "SQL_PLAN_6vcpdhw1pj1w8a1ef6cc1" used for this statement
Statistics
----------------------------------------------------------
766 recursive calls
16 db block gets
2343 consistent gets
3 physical reads
3072 redo size
16050 bytes sent via SQL*Net to client
755 bytes received via SQL*Net from client
23 SQL*Net roundtrips to/from client
29 sorts (memory)
0 sorts (disk)
330 rows processed
逻辑度不降反升
删除索引t2_idx
现在情况变得很糟糕，baseline不再起作用，但是profile依旧生效且起到副作用，不解的是为何会有如此高的逻辑度？
Execution Plan
----------------------------------------------------------
Plan hash value: 1178291774
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4856 | 203K| 22038 (1)| 00:04:25 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 4856 | 203K| 22038 (1)| 00:04:25 | Q1,01 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | 4856 | 203K| 22038 (1)| 00:04:25 | Q1,01 | PCWP | |
| 4 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 5 | PX RECEIVE | | | | | | Q1,01 | PCWP | |
| 6 | PX SEND BROADCAST | :TQ10000 | | | | | Q1,00 | P->P | BROADCAST |
| 7 | PX BLOCK ITERATOR | | 4856 | 137K| 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| T1 | 4856 | 137K| 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 9 | PX BLOCK ITERATOR | | 1 | 14 | 392 (1)| 00:00:05 | Q1,01 | PCWC | |
|* 10 | TABLE ACCESS FULL | T2 | 1 | 14 | 392 (1)| 00:00:05 | Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL)
10 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
- SQL profile "SYS_SQLPROF_023f3209456c0000" used for this statement
Statistics
----------------------------------------------------------
1101 recursive calls
68 db block gets
2379573 consistent gets
62 physical reads
9564 redo size
16268 bytes sent via SQL*Net to client
755 bytes received via SQL*Net from client
23 SQL*Net roundtrips to/from client
106 sorts (memory)
0 sorts (disk)
330 rows processed
创建两个表
create table t1 as select object_id,object_name from dba_objects;
create table t2 as select * from dba_objects;
exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T1');
exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T2');
create index t2_idx on t2(object_id);
本次试验反复执行如下sql，验证baseline和profile对其执行计划的影响
select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id = t2.object_id;
当前执行计划
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id = t2.object_id;
52 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1960724093
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3762 | 146K| 244 (3)| 00:00:03 |
| 1 | MERGE JOIN | | 3762 | 146K| 244 (3)| 00:00:03 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 75237 | 808K| 141 (1)| 00:00:02 |
| 3 | INDEX FULL SCAN | T2_IDX | 75237 | | 17 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 3762 | 106K| 103 (5)| 00:00:02 |
|* 5 | TABLE ACCESS FULL | T1 | 3762 | 106K| 102 (4)| 00:00:02 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1771 consistent gets
0 physical reads
0 redo size
2773 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
52 rows processed
将当前cursor加载为baseline
SQL> variable cnt number;
SQL> execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id=>'bbh0jjgs297s4');
PL/SQL procedure successfully completed.
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines where sql_text like 'select t1.*,t2.owner%';
SQL_HANDLE PARSING_SCHEMA_NAME PLAN_NAME ENA ACC
------------------------------ ------------------------------ ------------------------------ --- ---
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0e768f510 YES YES
再次执行该sql，执行计划变为
Execution Plan
----------------------------------------------------------
Plan hash value: 1960724093
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3762 | 146K| 244 (3)| 00:00:03 |
| 1 | MERGE JOIN | | 3762 | 146K| 244 (3)| 00:00:03 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 75237 | 808K| 141 (1)| 00:00:02 |
| 3 | INDEX FULL SCAN | T2_IDX | 75237 | | 17 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 3762 | 106K| 103 (5)| 00:00:02 |
|* 5 | TABLE ACCESS FULL | T1 | 3762 | 106K| 102 (4)| 00:00:02 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
Note
-----
- SQL plan baseline "SQL_PLAN_7t3w708ggxzx0e768f510" used for this statement
Statistics
----------------------------------------------------------
26 recursive calls
16 db block gets
1789 consistent gets
0 physical reads
3024 redo size
2773 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
52 rows processed
使用sql profile查看是否有更优解
var tuning_task varchar2(100);
declare
l_sql_id v$session.prev_sql_id%TYPE;
l_tuning_task varchar2(30);
begin
l_sql_id := 'bbh0jjgs297s4';
l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
:tuning_task := l_tuning_task;
dbms_sqltune.execute_tuning_task(l_tuning_task);
dbms_output.put_line(l_tuning_task);
end;
/
set long 10000
col task format a300
select dbms_sqltune.report_tuning_task(:tuning_task) as task from dual;
输出如下
TASK
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_5811
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 06/11/2013 03:26:59
Completed at : 06/11/2013 03:27:02
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : bbh0jjgs297s4
SQL Text : select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%'
and t1.object_id = t2.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 74.87%)
------------------------------------------
- Consider accepting the recommended SQL profile.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_5811',
task_owner => 'SYS', 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): .085682 .030687 64.18 %
CPU Time (s): .085587 .030695 64.13 %
User I/O Time (s): 0 0
Buffer Gets: 1763 442 74.92 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 52 52
Fetches: 52 52
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.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1960724093
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 2080 | 244 (3)| 00:00:03 |
| 1 | MERGE JOIN | | 52 | 2080 | 244 (3)| 00:00:03 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 75237 | 808K| 141 (1)| 00:00:02 |
| 3 | INDEX FULL SCAN | T2_IDX | 75237 | | 17 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 52 | 1508 | 103 (5)| 00:00:02 |
|* 5 | TABLE ACCESS FULL | T1 | 52 | 1508 | 102 (4)| 00:00:02 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
2- Using SQL Profile
--------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 2080 | 112 (4)| 00:00:02 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 52 | 2080 | 112 (4)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 52 | 1508 | 102 (4)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-------------------------------------------------------------------------------
接受profile
SQL> execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_5811',task_owner => 'SYS', replace => TRUE);
PL/SQL procedure successfully completed.
注意：baseline自动添加一条记录，且状态为accepted
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines where sql_text like 'select t1.*,t2.owner%';
SQL_HANDLE PARSING_SCHEMA_NAME PLAN_NAME ENA ACC
------------------------------ ------------------------------ ------------------------------ --- ---
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0ceb9aeed YES YES
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0e768f510 YES YES
该sql执行计划变为
SQL> select t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id = t2.object_id;
52 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 2080 | 112 (4)| 00:00:02 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 52 | 2080 | 112 (4)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 52 | 1508 | 102 (4)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_013f32261e050000" used for this statement
- SQL plan baseline "SQL_PLAN_7t3w708ggxzx0ceb9aeed" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
455 consistent gets
0 physical reads
0 redo size
2777 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52 rows processed
同时引用了profile和新生成的baseline。
删除索引
SQL> drop index t2_idx ;
Index dropped.
再次查看执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 2080 | 400 (3)| 00:00:05 |
|* 1 | HASH JOIN | | 52 | 2080 | 400 (3)| 00:00:05 |
|* 2 | TABLE ACCESS FULL| T1 | 52 | 1508 | 102 (4)| 00:00:02 |
| 3 | TABLE ACCESS FULL| T2 | 75237 | 808K| 296 (2)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
Note
-----
- SQL profile "SYS_SQLPROF_013f32261e050000" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1440 consistent gets
0 physical reads
0 redo size
2777 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52 rows processed
只剩下profile独舞，重新添加该索引，名字变动一下
SQL> create index t1_idx on t2(object_id);
Index created.
查看执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 4237736469
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 2080 | 112 (4)| 00:00:02 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 52 | 2080 | 112 (4)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 52 | 1508 | 102 (4)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T1_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_013f32261e050000" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
455 consistent gets
0 physical reads
0 redo size
2777 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52 rows processed
Baseline依旧没有出现，将索引改回原名，baseline才重新生效
SQL> alter index t1_idx rename to t2_idx;
Index altered.
执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 52 | 2080 | 112 (4)| 00:00:02 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 52 | 2080 | 112 (4)| 00:00:02 |
|* 3 | TABLE ACCESS FULL | T1 | 52 | 1508 | 102 (4)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_013f32261e050000" used for this statement
- SQL plan baseline "SQL_PLAN_7t3w708ggxzx0ceb9aeed" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
455 consistent gets
0 physical reads
0 redo size
2777 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52 rows processed
删除profile,该baseline还在，但是没有了profile的协助，优化器无法选择效率更高的SQL_PLAN_7t3w708ggxzx0ceb9aeed，执行计划也变回最初的那个
SQL> exec dbms_sqltune.DROP_SQL_PROFILE('SYS_SQLPROF_013f32261e050000');
PL/SQL procedure successfully completed.
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select t1.*,t2.owner%';
SQL_HANDLE PARSI PLAN_NAME ENA ACC OPTIMIZER_COST
------------------------------ ----- ------------------------------ --- --- --------------
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0ceb9aeed YES YES
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0e768f510 YES YES 244
执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 1960724093
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3762 | 146K| 209 (3)| 00:00:03 |
| 1 | MERGE JOIN | | 3762 | 146K| 209 (3)| 00:00:03 |
| 2 | TABLE ACCESS BY INDEX ROWID| T2 | 75237 | 808K| 141 (1)| 00:00:02 |
| 3 | INDEX FULL SCAN | T2_IDX | 75237 | | 17 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 3762 | 106K| 68 (8)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | T1 | 3762 | 106K| 67 (6)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
5 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
Note
-----
- SQL plan baseline "SQL_PLAN_7t3w708ggxzx0e768f510" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1771 consistent gets
0 physical reads
0 redo size
2840 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
52 rows processed
只能删除SQL_PLAN_7t3w708ggxzx0e768f510，oracle在别无选择的情况下才选择SQL_PLAN_7t3w708ggxzx0ceb9aeed
SQL> exec :cnt := dbms_spm.DROP_SQL_PLAN_BASELINE('SQL_7c8f87021efeffa0','SQL_PLAN_7t3w708ggxzx0e768f510');
PL/SQL procedure successfully completed.
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select t1.*,t2.owner%';
SQL_HANDLE PARSI PLAN_NAME ENA ACC OPTIMIZER_COST
------------------------------ ----- ------------------------------ --- --- --------------
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0ceb9aeed YES YES
执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3762 | 146K| 820 (1)| 00:00:10 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 3762 | 146K| 820 (1)| 00:00:10 |
|* 3 | TABLE ACCESS FULL | T1 | 3762 | 106K| 67 (6)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 11 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL plan baseline "SQL_PLAN_7t3w708ggxzx0ceb9aeed" used for this statement
Statistics
----------------------------------------------------------
6 recursive calls
8 db block gets
455 consistent gets
0 physical reads
0 redo size
2844 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52 rows processed
而此时多出一条baseline，属于正常行为；
默认optimizer_capture_sql_plan_baselines=false， sql不会生成baseline，需要手工调用dbms_spm.load_plans_from_cursor_cache或其他子API；
一旦生成了baseline，该sql若再有新执行计划则会自动生成unaccepted baseline；
如果sql接受了某baseline，即便当前有更优执行计划也不会立即选用，而是将其设为unaccepted；
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select t1.*,t2.owner%';
SQL_HANDLE PARSI PLAN_NAME ENA ACC OPTIMIZER_COST
------------------------------ ----- ------------------------------ --- --- --------------
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0b4ddbcf5 YES NO 260
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0ceb9aeed YES YES
可调用evolve_sql_plan_baseline对unaccepted baseline进行评估，如果性能更佳则自动转换为accepted
SQL> execute :evol_out := dbms_spm.evolve_sql_plan_baseline();
PL/SQL procedure successfully completed.
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select t1.*,t2.owner%';
SQL_HANDLE PARSI PLAN_NAME ENA ACC OPTIMIZER_COST
------------------------------ ----- ------------------------------ --- --- --------------
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0b4ddbcf5 YES NO 260
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0ceb9aeed YES YES
强制转换
SQL> execute :evol_out := dbms_spm.evolve_sql_plan_baseline(sql_handle=>'SQL_7c8f87021efeffa0',plan_name=>'SQL_PLAN_7t3w708ggxzx0b4ddbcf5',verify=>'NO');
PL/SQL procedure successfully completed.
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED,OPTIMIZER_COST from dba_sql_plan_baselines where sql_text like 'select t1.*,t2.owner%';
SQL_HANDLE PARSI PLAN_NAME ENA ACC OPTIMIZER_COST
------------------------------ ----- ------------------------------ --- --- --------------
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0b4ddbcf5 YES YES 260
SQL_7c8f87021efeffa0 SYS SQL_PLAN_7t3w708ggxzx0ceb9aeed YES YES
执行计划也随之转变
Execution Plan
----------------------------------------------------------
Plan hash value: 1838229974
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3762 | 146K| 260 (4)| 00:00:04 |
|* 1 | HASH JOIN | | 3762 | 146K| 260 (4)| 00:00:04 |
|* 2 | TABLE ACCESS FULL| T1 | 3762 | 106K| 67 (6)| 00:00:01 |
| 3 | TABLE ACCESS FULL| T2 | 75237 | 808K| 191 (3)| 00:00:03 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS
NOT NULL)
Note
-----
- SQL plan baseline "SQL_PLAN_7t3w708ggxzx0b4ddbcf5" used for this statement
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1440 consistent gets
0 physical reads
0 redo size
2844 bytes sent via SQL*Net to client
557 bytes received via SQL*Net from client
5 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
52 rows processed
结论：
一旦sql接受了profile，只要sql text不更改(忽略大小写和空格)，该profile就一直生效，可随着实际执行环境的改变实时生成不同的执行计划；
SPM会自动为accepted profile生成一条accepted baseline；
而一个baseline只对应一个sql plan，且对执行环境更敏感，哪怕引用的索引名字发生改动，该baseline便不再起作用；
Profile的优先级应该高于baseline；
后记：
貌似profile比较霸道，只要它在sql就必须使用，一旦起到副作用就毒害无穷；
同样的试验，以下是在11203 2节点rac下的表现
create table t1 as select object_id,object_name from dba_objects;
create table t2 as select * from dba_objects;
exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T1');
exec dbms_stats.gather_table_stats(ownname => user,tabname => 'T2');
create index t2_idx on t2(object_id);
select /*+ index(t2) use_nl(t1 t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id = t2.object_id;
最初的执行计划
SQL> select /*+ index(t2) use_nl(t1 t2) */ t1.*,t2.owner from t1,t2 where t1.object_name like '%T1%' and t1.object_id = t2.object_id;
330 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4856 | 203K| 9842 (1)| 00:01:59 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 4856 | 203K| 9842 (1)| 00:01:59 |
|* 3 | TABLE ACCESS FULL | T1 | 4856 | 137K| 126 (2)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 14 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
760 consistent gets
0 physical reads
0 redo size
15791 bytes sent via SQL*Net to client
755 bytes received via SQL*Net from client
23 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
330 rows processed
将其加载至baseline
variable cnt number;
execute :cnt := dbms_spm.load_plans_from_cursor_cache(sql_id=>'dvuw49t2aw37s');
Execution Plan
----------------------------------------------------------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4856 | 203K| 9842 (1)| 00:01:59 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 4856 | 203K| 9842 (1)| 00:01:59 |
|* 3 | TABLE ACCESS FULL | T1 | 4856 | 137K| 126 (2)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 14 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- SQL plan baseline "SQL_PLAN_6vcpdhw1pj1w8ceb9aeed" used for this statement
Statistics
----------------------------------------------------------
15 recursive calls
14 db block gets
773 consistent gets
0 physical reads
3220 redo size
15791 bytes sent via SQL*Net to client
755 bytes received via SQL*Net from client
23 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
330 rows processed
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines where sql_text like 'select /*+ index(t2) use_nl(t1 t2) */%';
SQL_HANDLE PARSING_SCHEMA_NAME PLAN_NAME ENA ACC
------------------------------ ------------------------------ ------------------------------ --- ---
SQL_6db2ad8703588788 SYS SQL_PLAN_6vcpdhw1pj1w8ceb9aeed YES YES
利用profile查看是否有更优解
var tuning_task varchar2(100);
declare
l_sql_id v$session.prev_sql_id%TYPE;
l_tuning_task varchar2(30);
begin
l_sql_id := 'dvuw49t2aw37s';
l_tuning_task := dbms_sqltune.create_tuning_task(sql_id => l_sql_id);
:tuning_task := l_tuning_task;
dbms_sqltune.execute_tuning_task(l_tuning_task);
dbms_output.put_line(l_tuning_task);
end;
set long 10000
col task format a300
select dbms_sqltune.report_tuning_task(:tuning_task) as task from dual;
SQL> select dbms_sqltune.report_tuning_task(:tuning_task) as task from dual;
TASK
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : TASK_29160
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 06/11/2013 02:57:15
Completed at : 06/11/2013 02:57:19
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : dvuw49t2aw37s
SQL Text : select /*+ index(t2) use_nl(t1 t2) */ t1.*,t2.owner from t1,t2
where t1.object_name like '%T1%' and t1.object_id = t2.object_id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 98.83%)
------------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution
for this statement.
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_29160',
task_owner => 'SYS', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);
Executing this query parallel with DOP 96 will improve its response time
98.84% over the original plan. However, there is some cost in enabling
parallel execution. It will increase the statement's resource consumption by
an estimated 11.66% which may result in a reduction of system throughput.
Also, because these resources are consumed over a much smaller duration, the
response time of concurrent statements might be negatively impacted if
sufficient hardware capacity is not available.
The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.
Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0
Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 0
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 1022743391
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4856 | 203K| 9842 (1)| 00:01:59 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 4856 | 203K| 9842 (1)| 00:01:59 |
|* 3 | TABLE ACCESS FULL | T1 | 4856 | 137K| 126 (2)| 00:00:02 |
|* 4 | INDEX RANGE SCAN | T2_IDX | 1 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 14 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT
NULL)
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
2- Using Parallel Execution
---------------------------
Plan hash value: 1933047403
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4856 | 203K| 114 (0)| 00:00:02 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | | | | | Q1,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | | | | | Q1,00 | PCWP | |
| 4 | NESTED LOOPS | | 4856 | 203K| 114 (0)| 00:00:02 | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL | T1 | 4856 | 137K| 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 7 | INDEX RANGE SCAN | T2_IDX | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 14 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL)
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
-------------------------------------------------------------------------------
接受profile
execute dbms_sqltune.accept_sql_profile(task_name => 'TASK_29160',task_owner => 'SYS', replace => TRUE, profile_type =>DBMS_SQLTUNE.PX_PROFILE);
SPM自动接受由profile产生的baseline并且状态为accepted
SQL> select SQL_HANDLE,PARSING_SCHEMA_NAME,PLAN_NAME,ENABLED,ACCEPTED from dba_sql_plan_baselines where sql_text like 'select /*+ index(t2) use_nl(t1 t2) */%';
SQL_HANDLE PARSING_SCHEMA_NAME PLAN_NAME ENA ACC
------------------------------ ------------------------------ ------------------------------ --- ---
SQL_6db2ad8703588788 SYS SQL_PLAN_6vcpdhw1pj1w8a1ef6cc1 YES YES
SQL_6db2ad8703588788 SYS SQL_PLAN_6vcpdhw1pj1w8ceb9aeed YES YES
执行计划变为
Execution Plan
----------------------------------------------------------
Plan hash value: 1933047403
------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4856 | 203K| 114 (0)| 00:00:02 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | | | | | Q1,00 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | | | | | Q1,00 | PCWP | |
| 4 | NESTED LOOPS | | 4856 | 203K| 114 (0)| 00:00:02 | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | | | | | Q1,00 | PCWC | |
|* 6 | TABLE ACCESS FULL | T1 | 4856 | 137K| 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 7 | INDEX RANGE SCAN | T2_IDX | 1 | | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
| 8 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 14 | 0 (0)| 00:00:01 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL)
7 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
- SQL profile "SYS_SQLPROF_023f3209456c0000" used for this statement
- SQL plan baseline "SQL_PLAN_6vcpdhw1pj1w8a1ef6cc1" used for this statement
Statistics
----------------------------------------------------------
766 recursive calls
16 db block gets
2343 consistent gets
3 physical reads
3072 redo size
16050 bytes sent via SQL*Net to client
755 bytes received via SQL*Net from client
23 SQL*Net roundtrips to/from client
29 sorts (memory)
0 sorts (disk)
330 rows processed
逻辑度不降反升
删除索引t2_idx
现在情况变得很糟糕，baseline不再起作用，但是profile依旧生效且起到副作用，不解的是为何会有如此高的逻辑度？
Execution Plan
----------------------------------------------------------
Plan hash value: 1178291774
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4856 | 203K| 22038 (1)| 00:04:25 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 4856 | 203K| 22038 (1)| 00:04:25 | Q1,01 | P->S | QC (RAND) |
| 3 | NESTED LOOPS | | 4856 | 203K| 22038 (1)| 00:04:25 | Q1,01 | PCWP | |
| 4 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 5 | PX RECEIVE | | | | | | Q1,01 | PCWP | |
| 6 | PX SEND BROADCAST | :TQ10000 | | | | | Q1,00 | P->P | BROADCAST |
| 7 | PX BLOCK ITERATOR | | 4856 | 137K| 2 (0)| 00:00:01 | Q1,00 | PCWC | |
|* 8 | TABLE ACCESS FULL| T1 | 4856 | 137K| 2 (0)| 00:00:01 | Q1,00 | PCWP | |
| 9 | PX BLOCK ITERATOR | | 1 | 14 | 392 (1)| 00:00:05 | Q1,01 | PCWC | |
|* 10 | TABLE ACCESS FULL | T2 | 1 | 14 | 392 (1)| 00:00:05 | Q1,01 | PCWP | |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
8 - filter("T1"."OBJECT_NAME" LIKE '%T1%' AND "T1"."OBJECT_NAME" IS NOT NULL)
10 - filter("T1"."OBJECT_ID"="T2"."OBJECT_ID")
Note
-----
- automatic DOP: skipped because of IO calibrate statistics are missing
- SQL profile "SYS_SQLPROF_023f3209456c0000" used for this statement
Statistics
----------------------------------------------------------
1101 recursive calls
68 db block gets
2379573 consistent gets
62 physical reads
9564 redo size
16268 bytes sent via SQL*Net to client
755 bytes received via SQL*Net from client
23 SQL*Net roundtrips to/from client
106 sorts (memory)
0 sorts (disk)
330 rows processed
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/15480802/viewspace-763736/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
11203测试sql baseline
下一篇：
col_usage$与直方图的收集
请登录后发表评论
登录
全部评论
