1 手工创建sql profile方式
执行出执行计划
SQL> explain plan for select * From t1;
Explained.
SQL> commit;
Commit complete.
查看执行计划
SQL> select * From table(dbms_xplan.display(null,null,'advanced'));
commit;
Plan hash value: 1880946022
------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1359 | 21 (5)| 00:00:01 |
|* 1 | VIEW | | 1 | 1359 | 21 (5)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 1 | 1346 | 21 (5)| 00:00:01 |
|* 4 | SORT ORDER BY STOPKEY | | 1 | 342 | 21 (5)| 00:00:01 |
|* 5 | FILTER | | | | | |
| 6 | NESTED LOOPS OUTER | | 1 | 342 | 20 (0)| 00:00:01 |
| 7 | NESTED LOOPS | | 1 | 280 | 15 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 271 | 14 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 263 | 13 (0)| 00:00:01 |
| 10 | NESTED LOOPS | | 1 | 225 | 11 (0)| 00:00:01 |
|* 11 | TABLE ACCESS BY INDEX ROWID| xxxxxxx_SHEET | 1 | 87 | 5 (0)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | IDX_TAI_IUCT_DISTIME | 1 | | 3 (0)| 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID| xxxxxxx_ORDER | 2 | 276 | 6 (0)| 00:00:01 |
|* 14 | INDEX RANGE SCAN | IDX_TAI_ISHEET_ID166 | 2 | | 3 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | XPK_INAS_PRODUCT | 1 | | 1 (0)| 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | xxxxxxx_OPERTYPE | 1 | 8 | 1 (0)| 00:00:01 |
|* 17 | INDEX UNIQUE SCAN | XPK_T_INAS_OPERTYPE | 1 | | 0 (0)| 00:00:01 |
| 18 | TABLE ACCESS BY INDEX ROWID | xxxxxxx_ORDERSTATE | 1 | 9 | 1 (0)| 00:00:01 |
|* 19 | INDEX UNIQUE SCAN | XPK_INAS_ORDERSTATE | 1 | | 0 (0)| 00:00:01 |
| 20 | TABLE ACCESS BY INDEX ROWID | xxxxxxx_FLOWNODE_INST | 8 | 496 | 5 (0)| 00:00:01 |
|* 21 | INDEX RANGE SCAN | XINDEX_INAS_FLOW_INST_ID | 8 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_NL(@"SEL$CBB13033" "NI"@"SEL$3")
USE_NL(@"SEL$CBB13033" "T6"@"SEL$5")
USE_NL(@"SEL$CBB13033" "T5"@"SEL$5")
USE_NL(@"SEL$CBB13033" "T3"@"SEL$5")
USE_NL(@"SEL$CBB13033" "T2"@"SEL$4")
LEADING(@"SEL$CBB13033" "T1"@"SEL$5" "T2"@"SEL$4" "T3"@"SEL$5" "T5"@"SEL$5" "T6"@"SEL$5" "NI"@"SEL$3")
INDEX_RS_ASC(@"SEL$CBB13033" "NI"@"SEL$3" ("xxxxxxx_FLOWNODE_INST"."FLOWINST_ID"))
INDEX_RS_ASC(@"SEL$CBB13033" "T6"@"SEL$5" ("xxxxxxx_ORDERSTATE"."ORDERSTATE_ID"))
INDEX_RS_ASC(@"SEL$CBB13033" "T5"@"SEL$5" ("xxxxxxx_OPERTYPE"."OPER_TYPE_ID"))
INDEX_RS_ASC(@"SEL$CBB13033" "T3"@"SEL$5" ("xxxxxxx_PRODUCT"."PRODUCT_CODE"))
INDEX_RS_ASC(@"SEL$CBB13033" "T2"@"SEL$4" ("xxxxxxx_ORDER"."SHEET_ID"))
INDEX_RS_ASC(@"SEL$CBB13033" "T1"@"SEL$5" ("xxxxxxx_SHEET"."DISPATCH_TIME"))
NO_ACCESS(@"SEL$2" "A"@"SEL$2")
NO_ACCESS(@"SEL$1" "B"@"SEL$1")
OUTLINE(@"SEL$3")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
MERGE(@"SEL$3")
OUTLINE(@"SEL$37633EB5")
ELIMINATE_JOIN(@"SEL$5" "T4"@"SEL$5")
OUTLINE(@"SEL$7F8A0496")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$2")
MERGE(@"SEL$37633EB5")
OUTLINE_LEAF(@"SEL$CBB13033")
ALL_ROWS
OPTIMIZER_FEATURES_ENABLE('10.2.0.5')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("B"."ROW_NUM">0)
2 - filter(ROWNUM<=30)
4 - filter(ROWNUM<=30)
5 - filter("NI"."NODEINST_STATE"=1 OR "NI"."NODETYPE_NAME"='结束节点' OR "NI"."FLOWINST_ID" IS NULL)
11 - filter("T1"."SHEET_CODE" LIKE '%4402a88740bb%' ESCAPE '/' AND "T1"."SHEET_CODE" IS NOT NULL)
12 - access("T1"."DISPATCH_TIME">=TO_DATE(' 2019-08-02 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
14 - access("T1"."SHEET_ID"="T2"."SHEET_ID")
15 - access("T2"."PRODUCT_CODE"="T3"."PRODUCT_CODE")
17 - access("T2"."OPER_TYPE_ID"="T5"."OPER_TYPE_ID")
19 - access("T2"."ORDERSTATE_ID"="T6"."ORDERSTATE_ID")
21 - access("T2"."CURRENT_FLOWINST"="NI"."FLOWINST_ID"(+))
---创建pofile并绑定-
declare
v_hints sys.sqlprof_attr;
begin
v_hints:=sys.sqlprof_attr(
q'<USE_NL(@"SEL$CBB13033" "NI"@"SEL$3")>',
q'<'USE_NL(@"SEL$CBB13033" "T6"@"SEL$5")>',
q'<'USE_NL(@"SEL$CBB13033" "T5"@"SEL$5")>',
q'<'USE_NL(@"SEL$CBB13033" "T3"@"SEL$5")>',
q'<'USE_NL(@"SEL$CBB13033" "T2"@"SEL$4")>',
q'<'LEADING(@"SEL$CBB13033" "T1"@"SEL$5" "T2"@"SEL$4" "T3"@"SEL$5" "T5"@"SEL$5" "T6"@"SEL$5" "NI"@"SEL$3")>',
q'<'INDEX_RS_ASC(@"SEL$CBB13033" "NI"@"SEL$3" ("xxxxxxx_FLOWNODE_INST"."FLOWINST_ID"))>',
q'<'INDEX_RS_ASC(@"SEL$CBB13033" "T6"@"SEL$5" ("xxxxxxx_ORDERSTATE"."ORDERSTATE_ID"))>',
q'<'INDEX_RS_ASC(@"SEL$CBB13033" "T5"@"SEL$5" ("xxxxxxx_OPERTYPE"."OPER_TYPE_ID"))>',
q'<'INDEX_RS_ASC(@"SEL$CBB13033" "T3"@"SEL$5" ("xxxxxxx_PRODUCT"."PRODUCT_CODE"))>',
q'<'INDEX_RS_ASC(@"SEL$CBB13033" "T2"@"SEL$4" ("xxxxxxx_ORDER"."SHEET_ID"))>',
q'<'INDEX_RS_ASC(@"SEL$CBB13033" "T1"@"SEL$5" ("xxxxxxx_SHEET"."DISPATCH_TIME"))>'
);
dbms_sqltune.import_sql_profile(q'<sql语句,绑定变量的>',v_hints,'sqlprofile2XXXXX',force_match => true,replace => true);
end;
2 sql profile方式
本文主要采用数据库10g以上STA得方式得到释放得sql profile，接受对应得sql profile然后参数
参数force_match =true，对于查询类值不一致也使用对应得sql profile(object_id between 200 and 900; 和between 100 and 200;)，
类似绑定变量功效，id=1，id=2，id=3 ,
如果在接受sql profile方式时参数force_match =false的话则sql需要完全匹配。
1 操作案例如下
create table t1 as select * from dba_objects;
create index idx on t1(object_id);
select * from t1 where object_id between 200 and 900;
select * from v$sql where sql_text like '%between 200 and 900%'; --sql_id为18996shf498a5
select * from table(dbms_xplan.display_cursor('18996shf498a5'));
SQL_ID 18996shf498a5, child number 0
-------------------------------------
select * from t1 where object_id between 200 and 900
Plan hash value: 430179204
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 42 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 680 | 137K| 42 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX | 680 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=200 AND "OBJECT_ID"<=900)
Note
-----
- dynamic sampling used for this statement (level=2);
select /*+full(t1)*/* from t1 where object_id between 200 and 900;
select * from table(dbms_xplan.display_cursor('bt77wasqm9ah7'));
SQL_ID bt77wasqm9ah7, child number 0
-------------------------------------
select /*+full(t1)*/* from t1 where object_id between 200 and 900
Plan hash value: 838529891
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 481 (100)| |
|* 1 | TABLE ACCESS FULL| T1 | 680 | 137K| 481 (2)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("OBJECT_ID">=200 AND "OBJECT_ID"<=900))
Note
-----
- dynamic sampling used for this statement (level=2)
使用自动的sql profile优化这个使用全表的sql,STA方式优化分析这个查询。
SQL> declare
2 my_task_name varchar2(300);
3 my_sqltext clob;
4 begin
----select sql_fulltext from v$sql where sql_id=&SQL_ID WHERE ROWNUM=1;
5 select q'<select /*+full(t1)*/* from t1 where object_id between 200 and 900>' INTO my_sqltext from dual;
6 my_task_name:=dbms_sqltune.create_tuning_task(sql_text => my_sqltext,user_name => 'NMOSDB',scope => 'COMPREHENSIVE',time_limit => 100,task_name => 'sql_tuning_task1112',description => 'sql');
7 dbms_sqltune.execute_tuning_task(task_name => 'sql_tuning_task1112');
8 end;
9 /
PL/SQL procedure successfully completed
SQL> SET LONG 100000
SQL> select dbms_sqltune.report_tuning_task('sql_tuning_task1112') from dual;
DBMS_SQLTUNE.REPORT_TUNING_TAS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : sql_tuning_task1112
Tuning Task Owner : NMOSDB
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 100
Completion Status : COMPLETED
Started at : 06/27/2019 12:01:22
Completed at : 06/27/2019 12:01:26
-------------------------------------------------------------------------------
Schema Name: NMOSDB
SQL ID : 95xmzjbc39xb1
SQL Text : select /*+full(t1)*/* from t1 where object_id between 200 and 900
-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
尚未分析表 "NMOSDB"."T1"。
Recommendation
--------------
- 考虑收集此表的优化程序统计信息。
execute dbms_stats.gather_table_stats(ownname => 'NMOSDB', tabname =>
'T1', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
=> 'FOR ALL COLUMNS SIZE AUTO');
Rationale
---------
为了选择好的执行计划, 优化程序需要此表的最新统计信息。
2- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
为此语句找到了性能更好的执行计划。
Recommendation (estimated benefit: 99.43%)
------------------------------------------
- 考虑接受推荐的 SQL 概要文件。
execute dbms_sqltune.accept_sql_profile(task_name =>
'sql_tuning_task1112', task_owner => 'NMOSDB', replace => TRUE); ----STA给出建议为接受sql profile
Validation results
------------------
已对 SQL profile 进行测试, 方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。如果其中一个计划运行在很短的时间内就完成,
则另一计划可能只执行了一部分。
Original Plan With SQL Profile % Improved
------------- ---------------- ----------
Completion Status: COMPLETE COMPLETE
Elapsed Time (s): .029895 .000445 98.51 %
CPU Time (s): .029895 .000445 98.51 %
User I/O Time (s): 0 0
Buffer Gets: 2120 12 99.43 %
Physical Read Requests: 0 0
Physical Write Requests: 0 0
Physical Read Bytes: 0 0
Physical Write Bytes: 0 0
Rows Processed: 680 680
Fetches: 680 680
Executions: 1 1
Notes
-----
1. the original plan 的统计信息是 10 执行的平均值。
2. the SQL profile plan 的统计信息是 10 执行的平均值。
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 838529891
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 550 | 481 (2)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T1 | 5 | 550 | 481 (2)| 00:00:06 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<=900 AND "OBJECT_ID">=200)
2- Using SQL Profile
--------------------
Plan hash value: 430179204
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 550 | 3 (0)| 00:00:
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 5 | 550 | 3 (0)| 00:00:
|* 2 | INDEX RANGE SCAN | IDX | 5 | | 2 (0)| 00:00:
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=200 AND "OBJECT_ID"<=900)
-------------------------------------------------------------------------------
SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'sql_tuning_task1112', task_owner => 'NMOSDB', replace => TRUE,force_match => TRUE);
select * from table(dbms_xplan.display_cursor('bt77wasqm9ah7',NULL,'advanced'));
SQL_ID bt77wasqm9ah7, child number 0
-------------------------------------
select /*+full(t1)*/* from t1 where object_id between 200 and 900
Plan hash value: 430179204
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 5 | 550 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX | 5 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
2 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=200 AND "OBJECT_ID"<=900)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T1"."OWNER"[VARCHAR2,30], "T1"."OBJECT_NAME"[VARCHAR2,128],
"T1"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"T1"."DATA_OBJECT_ID"[NUMBER,22], "T1"."OBJECT_TYPE"[VARCHAR2,19],
"T1"."CREATED"[DATE,7], "T1"."LAST_DDL_TIME"[DATE,7],
"T1"."TIMESTAMP"[VARCHAR2,19], "T1"."STATUS"[VARCHAR2,7],
"T1"."TEMPORARY"[VARCHAR2,1], "T1"."GENERATED"[VARCHAR2,1],
"T1"."SECONDARY"[VARCHAR2,1], "T1"."NAMESPACE"[NUMBER,22],
"T1"."EDITION_NAME"[VARCHAR2,30]
2 - "T1".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Note
-----
- SQL profile SYS_SQLPROF_016b971f49430001 used for this statement
select /*+full(t1)*/* from t1 where object_id between 100 and 200;
Plan hash value: 430179204
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 330 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3 | 330 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX | 3 | | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
2 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=100 AND "OBJECT_ID"<=200)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T1"."OWNER"[VARCHAR2,30], "T1"."OBJECT_NAME"[VARCHAR2,128],
"T1"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"T1"."DATA_OBJECT_ID"[NUMBER,22], "T1"."OBJECT_TYPE"[VARCHAR2,19],
"T1"."CREATED"[DATE,7], "T1"."LAST_DDL_TIME"[DATE,7],
"T1"."TIMESTAMP"[VARCHAR2,19], "T1"."STATUS"[VARCHAR2,7],
"T1"."TEMPORARY"[VARCHAR2,1], "T1"."GENERATED"[VARCHAR2,1],
"T1"."SECONDARY"[VARCHAR2,1], "T1"."NAMESPACE"[NUMBER,22],
"T1"."EDITION_NAME"[VARCHAR2,30]
2 - "T1".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Note
-----
- SQL profile "SYS_SQLPROF_016b971f49430001" used for this statement
SQL> exec dbms_sqltune.drop_sql_profile('SYS_SQLPROF_016b971f49430001');
execute dbms_sqltune.accept_sql_profile(task_name =>'sql_tuning_task1112', task_owner => 'NMOSDB', replace => TRUE,force_match => false);
explain plan for select /*+full(t1)*/* from t1 where object_id between 100 and 200;
commit;
select * from table(dbms_xplan.display(null,null,'advanced'))
Plan hash value: 838529891
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 20700 | 481 (2)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T1 | 100 | 20700 | 481 (2)| 00:00:06 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "T1"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('11.2.0.4')
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID">=100 AND "OBJECT_ID"<=200)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T1"."OWNER"[VARCHAR2,30], "T1"."OBJECT_NAME"[VARCHAR2,128],
"T1"."SUBOBJECT_NAME"[VARCHAR2,30], "OBJECT_ID"[NUMBER,22],
"T1"."DATA_OBJECT_ID"[NUMBER,22], "T1"."OBJECT_TYPE"[VARCHAR2,19],
"T1"."CREATED"[DATE,7], "T1"."LAST_DDL_TIME"[DATE,7],
"T1"."TIMESTAMP"[VARCHAR2,19], "T1"."STATUS"[VARCHAR2,7],
"T1"."TEMPORARY"[VARCHAR2,1], "T1"."GENERATED"[VARCHAR2,1],
"T1"."SECONDARY"[VARCHAR2,1], "T1"."NAMESPACE"[NUMBER,22],
"T1"."EDITION_NAME"[VARCHAR2,30]
Note
-----
- dynamic sampling used for this statement (level=2)
2 使用outline方式创建sql profile方式，并且绑定到对应sql语句上。
偷换outline信息即可。force_match依然有效
----查询sql语句执行计划，并查询到对应的outline。
select * from t1 where object_id between 200 and 900;
select * from v$sql where sql_text like '%between 200 and 900%';
select * from table(dbms_xplan.display_cursor('cbgstmzg1hjmu'));
SQL> select * from table(dbms_xplan.display_cursor('cbgstmzg1hjmu',null,'advanced'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID cbgstmzg1hjmu, child number 0
-------------------------------------
select * from t1 where object_id between 200 and 900
Plan hash value: 3204894120
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 18 (
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| T1 | 699 | 328K| 18
|* 2 | INDEX RANGE SCAN | IDX000A1 | 699 | | 3
--------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
2 - SEL$1 / T1@SEL$1
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
DB_VERSION('12.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX_RS_ASC(@"SEL$1" "T1"@"SEL$1" ("T1"."OBJECT_ID"))
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "T1"@"SEL$1")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID">=200 AND "OBJECT_ID"<=900)
Column Projection Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----------------------------------------------------------
1 - "T1"."OWNER"[VARCHAR2,128], "T1"."OBJECT_NAME"[VARCHAR2,128],
"T1"."SUBOBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22],
"T1"."DATA_OBJECT_ID"[NUMBER,22], "T1"."OBJECT_TYPE"[VARCHAR2,23],
"T1"."CREATED"[DATE,7], "T1"."LAST_DDL_TIME"[DATE,7], "T1"."TIMESTAMP"[VA
"T1"."STATUS"[VARCHAR2,7], "T1"."TEMPORARY"[VARCHAR2,1], "T1"."GENERATED"
"T1"."SECONDARY"[VARCHAR2,1], "T1"."NAMESPACE"[NUMBER,22],
"T1"."EDITION_NAME"[VARCHAR2,128], "T1"."SHARING"[VARCHAR2,18],
"T1"."EDITIONABLE"[VARCHAR2,1], "T1"."ORACLE_MAINTAINED"[VARCHAR2,1],
"T1"."APPLICATION"[VARCHAR2,1], "T1"."DEFAULT_COLLATION"[VARCHAR2,100],
"T1"."DUPLICATED"[VARCHAR2,1], "T1"."SHARDED"[VARCHAR2,1],
"T1"."CREATED_APPID"[NUMBER,22], "T1"."CREATED_VSNID"[NUMBER,22],
"T1"."MODIFIED_APPID"[NUMBER,22], "T1"."MODIFIED_VSNID"[NUMBER,22]
2 - "T1".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
3 spm自动加载(用新的计划代替老的计划)
当使用SPM后，每一个sql语句都会存在对应的sql plan baseline，这个sql plan baseline里存储的就是sql的执行计划，如果一个sql有多个执行计划，那么该sql语句会对应多个sql plan baseline，可以从视图dba_sql_baselines中查询所有目标sql所有的sql plan baseline。
DBA_SQL_PLANBASES中的enabled和accepted列用来描述一个sql plan baselines所对应的执行计划是否被oracle启用，只有enabled和accepted均为yes才被oracle启用。如果一个sql有超过1个 sql plan baselines的enabled和accepted均为yes则oracle会选择cost消耗最小的作为该sql的执行计划。
两种获得sql plan baseline 的方法：
1 自动捕获，参数optimizer_capture_sql_plan_baselines用于控制是否自动捕获sql plan baselines，默认为false，这个参数可以在session级别调整，（修改为true则第一次捕获的sql执行计划enabled和accepted为yes，计划变更后enabled为yes，enabled为NO，sql语句会沿用第一次捕获的sql plan baselines执行），参数optimizer_use_sql_plan_baselines控制是否使用baselines默认为true。
(sql语句重复执行，才会自动捕获sql plan baselines）
SQL> show parameter sql_plan
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
SQL>
案例分析1：
SQL> alter session set optimizer_capture_sql_plan_baselines=true; --修改自动捕获为true
Session altered.
SQL> alter session set optimizer_use_sql_plan_baselines=false; --修改自动应用为false
Session altered.
SQL> create table t1 as select * from dba_objects;
SQL> create index idx1 on t1(object_id) online;
SQL> exec dbms_stats.gather_TABLE_STATS(ownname=>'SYS',TABNAME=>'T1',ESTIMATE_PERCENT=>100,CASCADE=>TRUE);
SQL> SELECT OBJECT_ID,OBJECT_NAME FROM T1 WHERE OBJECT_ID BETWEEN 100 AND 200;
SQL> SELECT * fROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ADVANCED'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 85awada7k8x84, child number 0
-------------------------------------
SELECT OBJECT_ID,OBJECT_NAME FROM T1 WHERE OBJECT_ID BETWEEN 100 AND
200
--------------------------------------------------------------------------------
|* 2 | INDEX RANGE SCAN | IDX1 | 101 | | 2 (0)
1 - "OBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22]
2 - "T1".ROWID[ROWID,10], "OBJECT_ID"[NUMBER,22] 执行计划走的是索引范围扫描。
SQL> select sql_handle,sql_text,ORIGIN,ENABLED,ACCEPTED from dba_sql_plan_baselines; 为空
SQL> select sql_handle,sql_text,ORIGIN,ENABLED,ACCEPTED from dba_sql_plan_baselines;
SQL_HANDLE SQL_TEXT
------------------------------ --------------------------------------------------------------------------------
ORIGIN ENA ACC
----------------------------- --- ---
SQL_8d9b1f159c2a7f71 select object_id,object_name from t1 where object_Id between 100 and 200
AUTO-CAPTURE YES YES
如果手工修改索引的聚簇因子为240000，让表走全表扫描。
SQL> exec dbms_stats.set_index_stats(ownname=>'SYS',INDNAME=>'IDX1',CLSTFCT=>2000000000,NO_INVALIDATE=>FALSE);
SQL>select object_id,object_name from t1 where object_Id between 100 and 200;
查询dba_sql_plan_baselines视图
SQL> alter session set optimizer_use_sql_plan_baselines=true; --修改自动应用为false
Session altered.
使用了YES YES的sql plan baselines。
################如何在sql plan baselines之间进行切换那？？？？？？？？？######
因为所有accepted值为YES是不能调节为NO的，所以步骤如下：
1 把新的计划accepted值调整为YES
var a pls_integer;
var b CLOB; --12C
exec :B:=DBMS_SPM.evolve_sql_plan_baseline(SQL_HANDLE=>'SQL_8d9b1f159c2a7f71',PLAN_NAME=>'SQL_PLAN_8v6sz2qf2nzvjdbd90e8e',verify=>'NO',commit=>'YES');
exec :A:=DBMS_SPM.ALTER_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_8d9b1f159c2a7f71',PLAN_NAME=>'SQL_PLAN_8v6sz2qf2nzvja4d15564',ATTRIBUTE_NAME=>'ENABLED',ATTRIBUTE_VALUE=>'NO');
SQL> SELECT SQL_HANDLE,PLAN_NAME,SQL_TEXT ,ENABLED,ACCEPTED FROM DBA_SQL_PLAN_BASELINES;
4 手工加载sql plan baseline，偷梁换柱
select * from t1 where object_id<100 --索引扫描 ，调整为全表扫描
select SQL_ID from v$sql where sql_text like '%object_id<100%' --确定sql_id
select * from (dbms_xplan.display_cursor('8g23n5b4ynw4t')); --确定hash_value
--加载 baseline
DECLARE
L_PLANS_LOADED PLS_INTEGER;
BEGIN
L_PLANS_LOADED:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( SQL_ID=>'8g23n5b4ynw4t',plan_hash_value=>'3204894120');
END;
--删除baseline
DECLARE
L_PLANS_LOADED PLS_INTEGER;
BEGIN
L_PLANS_LOADED:=DBMS_SPM.drop_sql_plan_baseline(sql_handle => 'SQL_d4933718e6a07abe',plan_name => 'SQL_PLAN_d94tr33ma0ypyff23534e');
END;
--查看baseline
SELECT * fROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(SQL_HANDLE=>'SQL_d4933718e6a07abe',FORMAT=>'BASIC'));
select /*+full(t1)*/* from t1 where object_id<10000 7ag5ry5406axu
select * from (dbms_xplan.display_cursor('7ag5ry5406axu')); 3617692013 ---全表扫描的 plan hash value
---将全表扫描的执行计划，附加到以上的baseline的sql_handle上。
DECLARE
L_PLANS_LOADED PLS_INTEGER;
BEGIN
L_PLANS_LOADED:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE( SQL_ID=>'7ag5ry5406axu',plan_hash_value=>'3617692013',sql_handle=>'SQL_d4933718e6a07abe');
END;
1通过删除索引扫描的形式达到目的
DECLARE
L_PLANS_LOADED PLS_INTEGER;
BEGIN
L_PLANS_LOADED:=DBMS_SPM.drop_sql_plan_baseline(sql_handle => 'SQL_d4933718e6a07abe',plan_name => 'SQL_PLAN_d94tr33ma0ypyff23534e');
END;
2 通过修改enabled属性为NO 达到目的
DECLARE
L_PLANS_LOADED PLS_INTEGER;
BEGIN
L_PLANS_LOADED:=DBMS_SPM.alter_sql_plan_baseline(sql_handle => 'SQL_d4933718e6a07abe',plan_name => 'SQL_PLAN_d94tr33ma0ypyff23534e',attribute_name => 'ENABLED',attribute_value => 'NO');
END;
验证：
EXPLAIN PLAN FOR
select * from t1 where object_id<10000
SELECT * FROM TABLE(DBMS_XPLAN.display(NULL,NULL,'ADVANCED'));
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7776 | 3652K| 388 (1)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 7776 | 3652K| 388 (1)| 00:00:01 |
--------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / T1@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
FULL(@"SEL$1" "T1"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('12.2.0.1')
OPTIMIZER_FEATURES_ENABLE('12.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"<10000)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "T1"."OWNER"[VARCHAR2,128], "T1"."OBJECT_NAME"[VARCHAR2,128],
"T1"."SUBOBJECT_NAME"[VARCHAR2,128], "OBJECT_ID"[NUMBER,22],
"T1"."DATA_OBJECT_ID"[NUMBER,22], "T1"."OBJECT_TYPE"[VARCHAR2,23],
"T1"."CREATED"[DATE,7], "T1"."LAST_DDL_TIME"[DATE,7],
"T1"."TIMESTAMP"[VARCHAR2,19], "T1"."STATUS"[VARCHAR2,7],
"T1"."TEMPORARY"[VARCHAR2,1], "T1"."GENERATED"[VARCHAR2,1],
"T1"."SECONDARY"[VARCHAR2,1], "T1"."NAMESPACE"[NUMBER,22],
"T1"."EDITION_NAME"[VARCHAR2,128], "T1"."SHARING"[VARCHAR2,18],
"T1"."EDITIONABLE"[VARCHAR2,1], "T1"."ORACLE_MAINTAINED"[VARCHAR2,1],
"T1"."APPLICATION"[VARCHAR2,1], "T1"."DEFAULT_COLLATION"[VARCHAR2,100],
"T1"."DUPLICATED"[VARCHAR2,1], "T1"."SHARDED"[VARCHAR2,1],
"T1"."CREATED_APPID"[NUMBER,22], "T1"."CREATED_VSNID"[NUMBER,22],
"T1"."MODIFIED_APPID"[NUMBER,22], "T1"."MODIFIED_VSNID"[NUMBER,22]
Note
-----
- dynamic statistics used: dynamic sampling (level=2)
- SQL plan baseline "SQL_PLAN_d94tr33ma0ypydbd90e8e" used for this statement
参数：
alter session set optimizer_use_sql_plan_baselines=true;
alter session set optimizer_use_capture_sql_plan_baselines=true;
451
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
