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部落格 ” ，連結：http://blog.itpub.net/15480802/viewspace-763736/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- user 和 user profile是聚合的關係嗎
- Linux的.bash_profile和.bashrc與子Shell的關係Linux
- 影響執行計劃之oracle sql baseline與sql profile之互動OracleSQL
- zt_sql baseline_sql profile_sql outline修改固定執行計劃SQL
- sql tuning task和sql profileSQL
- 【Shell】【Tips】Linux的.bash_profile和.bashrc與子Shell的關係Linux
- 淺談WebSocket協議、WS協議和WSS協議原理及關係Web協議
- oracle 11g sql plan baseline(5)baseline的問題和補充OracleSQL
- 【SQL Profile】coe_xfr_sql_profile.sql內容SQL
- sql profile的使用SQL
- sql profileSQL
- sql pan baselineSQL
- Oracle SQL baselineOracleSQL
- sql多表的關係介紹SQL
- SQL優化--多表連線和走索引的關係SQL優化索引
- 關於sql_profile中的繫結變數SQL變數
- FAILGROUP和REDUNDANCY之間的關係關係！AI
- sql profile使用SQL
- sql_plan_baselineSQL
- sql plan baseline（二）SQL
- sql_profile的使用(一)SQL
- JavaScript和Java的關係JavaScript
- 平等和效率的關係
- hadoop中NameNode、DataNode和Client三者之間協作關係及通訊方式介紹Hadoopclient
- 程式執行緒協程關係執行緒
- 關於Oracle Baseline和DBMS_SQLTUNE工具OracleSQL
- oracle 11g sql plan baseline(3)演化baselineOracleSQL
- 非關係型資料庫（NOSQL）和關係型資料庫（SQL）區別詳解資料庫SQL
- ORACLE SQL PROFILE使用OracleSQL
- SQL PROFILE 測試SQL
- git 關於多人協作Git
- NSObject class和NSObject protocol的關係（抽象基類與協議）ObjectProtocol抽象協議
- SQL與NoSQL（關係型與非關係型）資料庫的區別SQL資料庫
- 與HTTP關係密切的協議：IP、TCP、DNSHTTP協議TCPDNS
- 檢視SQL PROFILE使用的HINTSQL
- 程式和產品的關係
- oracle session和process的關係OracleSession
- connection和session的關係Session