溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹數(shù)據(jù)庫中sql plan baseline怎么用，文中介紹的非常詳細(xì)，具有一定的參考價值，感興趣的小伙伴們一定要看完！
測試內(nèi)容：
1、dba_sql_plan_baselines表中和時間有關(guān)的四個字段CREATED，LAST_MODIFIED，LAST_EXECUTED，LAST_VERIFIED的變化規(guī)律
2、候選sql plan變?yōu)閍ccepted sql plan baseline的幾種方法
3、SQL語句對應(yīng)的sql plan baseline均失效的情況下Optimizer將新生成的執(zhí)行計劃演進為sql plan baseline的過程
4、不同用戶針對各自用戶下的表，執(zhí)行同一條sql語句， sql plan baseline的共享機制
建立測試用表：
grant connect,resource,unlimited tablespace to scott identified by sdfg_1234;
create table scott.t1 tablespace ts_pub as select * from dba_objects;
create table scott.t2 tablespace ts_pub as select * from dba_objects where rownum<100;
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t2',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
1、dba_sql_plan_baselines表中和時間有關(guān)字段的變化規(guī)律，涉及到以下4個字段
CREATED
LAST_MODIFIED
LAST_EXECUTED
LAST_VERIFIED
###開啟session級的sql capture，自動生成首條sql plan baseline
--session 1，設(shè)置Session級的capture
SQL> select * from dba_sql_plan_baselines;
no rows selected
alter system optimizer_capture_sql_plan_baselines=TRUE;
select count(*) from scott.t1 where object_id in (select object_id from scott.t2);
--session 2，dba_sql_plan_baselines中沒有記錄，因為上述sql只執(zhí)行了一次
select sql_handle,sql_text,plan_name,creator,last_modified,last_executed,last_verified from dba_sql_plan_baselines;
--session 1，再次執(zhí)行一遍sql
select count(*) from scott.t1 where object_id in (select object_id from scott.t2);
--session 2，dba_sql_plan_baselines產(chǎn)生了首條sql plan baseline，首條初始狀態(tài)就是accepted
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified
from dba_sql_plan_baselines;
###上述結(jié)果中的時間點字段值，last_verified值為空，因為其是這條sql生成的首條baseline所以沒有經(jīng)過驗證；因為是新建的sql plan baseline其余三個時間字段值都一樣
CREATED：02-JUL-14 02.37.20.000000 PM
LAST_MODIFIED：02-JUL-14 02.37.20.000000 PM
LAST_EXECUTED：02-JUL-14 02.37.20.000000 PM
LAST_VERIFIED：NULL
###上述結(jié)果中的時間點字段值，last_verified值為空，因為其是這條sql生成的首條baseline所以沒有經(jīng)過
--session 1，第三次執(zhí)行sql，執(zhí)行前關(guān)閉sql capture參數(shù)
alter session set optimizer_capture_sql_plan_baselines=FALSE;
select count(*) from scott.t1 where object_id in (select object_id from scott.t2);
--session 2，觀察時間字段狀態(tài)，CREATED、LAST MODIFIED兩個字段值沒有變化，這個可以理解，LAST_EXECUTED值應(yīng)該變化為最近一次的執(zhí)行時間，但事實卻沒有變化，即使alter system flush shared_pool以后重新執(zhí)行語句，也沒有變化
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines;
###通過DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE查看sql_plan_baseline對應(yīng)的執(zhí)行計劃為FTS
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk1822a9c5af'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_d11d993788ae4828
SQL text: select count(*) from scott.t1 where object_id in (select object_id from
scott.t2)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_d27ct6y4awk1822a9c5af Plan id: 581551535
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1240933221
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 462 (2)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | HASH JOIN RIGHT SEMI| | 3 | 27 | 462 (2)| 00:00:06 |
| 3 | TABLE ACCESS FULL | T2 | 99 | 297 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 177K| 1042K| 455 (1)| 00:00:06 |
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"="OBJECT_ID")
28 rows selected.
###t1表的object_id字段上創(chuàng)建索引，再次執(zhí)行sql
create index scott.ind_objid_t1 on scott.t1(object_id) tablespace ts_pub;
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t2',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
###dba_sql_plan_baselines里又生成了一條plan_name= SQL_PLAN_d27ct6y4awk18b1b38b11(sql_handle與前一條相同的sql)，但沒有被accepted的baseline，這條記錄的CREATED、LAST_MODIFIED字段表明了該條baseline的創(chuàng)建時間，LAST_EXECUTED、LAST_VERIFIED均為空值
col sql_handle format a20
col creator format a5
col sql_text format a50
col created format a30
col last_modified format a30
col last_executed format a30
col last_verified format a30
set linesize 190
set pagesize 200
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines;
###執(zhí)行sql，雖然有索引，但因為baseline的存在，走的依然是FTS
set autotrace traceonly;
SQL> select count(*) from scott.t1 where object_id in (select object_id from scott.t2);
Execution Plan
----------------------------------------------------------
Plan hash value: 1240933221
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 462 (2)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | HASH JOIN RIGHT SEMI| | 3 | 27 | 462 (2)| 00:00:06 |
| 3 | TABLE ACCESS FULL | T2 | 99 | 297 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 177K| 1042K| 455 (1)| 00:00:06 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"="OBJECT_ID")
Note
-----
- SQL plan baseline "SQL_PLAN_d27ct6y4awk1822a9c5af" used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2557 consistent gets
2556 physical reads
0 redo size
526 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
###人工演進sql plan baseline，根據(jù)Buffer Get優(yōu)化前后的對比2557/11=232.45，得出使用索引的sql plan baseline所獲得的性能是FTS的232倍，oracle情況下根據(jù)隱含參數(shù)_plan_verify_improvement_margin(默認(rèn)值為150，表示1.5倍)的值決定性能達到原先多少倍時accept新的sql plan baseline，此例中已經(jīng)達到了232被，所以當(dāng)讓是verified and accepted
set serveroutput on
set long 10000
declare
result_clob clob;
begin
result_clob:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk18b1b38b11',verify=>'YES',commit=>'YES');
dbms_output.put_line(result_clob);
end;
/
-------------------------------------------------------------------------------，
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
------
Inputs:
-------
SQL_HANDLE = SQL_d11d993788ae4828
PLAN_NAME =
SQL_PLAN_d27ct6y4awk18b1b38b11
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY =
YES
COMMIT = YES
Plan:
SQL_PLAN_d27ct6y4awk18b1b38b11
------------------------------------
Plan was
verified: Time used .901 seconds.
Plan passed performance criterion: 232.77
times better than baseline plan.
Plan was changed to an accepted plan.
Baseline Plan Test Plan Stats Ratio
------------- --------- -----------
Execution Status:
COMPLETE COMPLETE
Rows Processed: 1
1
Elapsed Time(ms): 59.641 .298 200.14
CPU Time(ms): 34.444 0
Buffer Gets:
2557 11 232.45
Physical Read Requests: 0
0
Physical Write Requests: 0 0
Physical Read
Bytes: 0 0
Physical Write Bytes:
0 0
Executions: 1
1
-----------------------------------------------------------------------------
--
Report
Summary
------------------------------------------------------------------------
-------
Number of plans verified: 1
Number of plans accepted: 1
PL/SQL procedure successfully completed.
###查看PLAN_NAME=SQL_PLAN_d27ct6y4awk18b1b38b11對應(yīng)sql plan baseline，LAST_VERIFIED和
LAST_MODIFIED為同一個時間，LAST_VERIFIED表示在這個時間完成了Verify動作，LAST_MODIFIED表示在
Verify通過后將此baseline從not accepted變?yōu)閍ccepted的時間。
CREATED: 02-JUL-14 03.22.41.000000 PM
LAST_MODIFIED: 02-JUL-14 03.44.10.000000 PM
LAST_VERIFIED:02-JUL-14 03.44.10.000000 PM
###執(zhí)行該SQL后發(fā)現(xiàn)last_executed時間已經(jīng)是最新的時間了
SQL> select count(*) from scott.t1 where object_id in (select object_id from scott.t2);
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines;
LAST_EXECUTED:02-JUL-14 04.25.33.000000 PM
###用dbms_xplan.display_sql_plan_baseline顯示Plan_name=SQL_PLAN_d27ct6y4awk18b1b38b11的執(zhí)行計劃，這次采用的是Nest Loop
SQL> select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk18b1b38b11'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
SQL handle: SQL_d11d993788ae4828
SQL text: select count(*) from scott.t1 where object_id in (select object_id fro
m
scott.t2)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_d27ct6y4awk18b1b38b11 Plan id: 2981333777
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Enabled: YES Fixed: NO Accepted: YES Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
Plan hash value: 2406492491
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 1 | 9 | 56 (2)| 00:00
:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | |
|
| 2 | NESTED LOOPS | | 99 | 891 | 56 (2)| 00:00
:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 3 | SORT UNIQUE | | 99 | 297 | 5 (0)| 00:00
:01 |
| 4 | TABLE ACCESS FULL| T2 | 99 | 297 | 5 (0)| 00:00
:01 |
|* 5 | INDEX RANGE SCAN | IND_OBJID_T1 | 1 | 6 | 1 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("OBJECT_ID"="OBJECT_ID")
階段總結(jié)：
CREATED：sql plan生成到plan_history的時間(可以是accept或者not accept狀態(tài))
LAST_MODIFIED：sql plan上一次修改的時間，這個修改時間反映了sql plan演進過程中將not
accetped的sql plan更新為accepted動作發(fā)生的時間，也能反映使用alter_sql_plan_baseline
對于sql plan任何屬性更改的時間
LAST_VERIFIED：sql plan最后一次被驗證的時間，同一個plan被驗證一遍之后如果再重復(fù)進
行驗證，時間還是停留在首次驗證的時間；第一條sql plan自動成為sql plan baseline時其
last_verified時間為空，說明其沒有經(jīng)過verify，即使后續(xù)對首條sql plan人工進行演進，其last_verified時間依然為空
LAST_EXECUTED：名義上為最后一次執(zhí)行的時間，實際測下來定格在首次執(zhí)行的時間，后續(xù)
的執(zhí)行并不會更新
2、使sql plan變?yōu)閍ccepted sql plan baseline的幾種方法
(1) 調(diào)用Dbms_spm.evolve_sql_plan_baseline函數(shù)，需要人工調(diào)用(在12c版本里已經(jīng)引入sql plan evolve advisor能實現(xiàn)自動演進sql plan baseline)，這個是最常用的方法，只做如下說明：
其中Verify=yes表示經(jīng)過optimizer驗證
verify=no表示不經(jīng)過optimizer驗證強制變?yōu)閍ccepted狀態(tài)
(2) 調(diào)用Dbms_spm.LOAD_PLANS_FROM_CURSOR_CACHE或者LOAD_PLANS_FROM_SQLSET函數(shù)，這里使用LOAD_PLANS_FROM_CURSOR_CACHE函數(shù)將shared pool中已經(jīng)存在的執(zhí)行計劃load到baseline，且狀態(tài)變?yōu)閍ccepted；
###執(zhí)行sql，使其cache到shared pool
variable v_objid number;
exec :v_objid:=1000;
select count(*) from scott.t1 where object_id<:v_objid;
SQL> select sql_text,sql_id,child_number,plan_hash_value from v$sql where sql_text like 'select count(*) from scott.t1%';
SQL_TEXT SQL_ID CHILD_NUMBER PLAN_HASH_VALUE
------------------------------------------------------------------------------------------ ------------- ------------ ---------------
select count(*) from scott.t1 where object_id<:v_objid 9hup7n51za19u 0 4020739011
###顯示執(zhí)行計劃
select * from table(dbms_xplan.display_cursor(sql_id=>'9hup7n51za19u',cursor_child_no=>0,format=>'ALL'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 9hup7n51za19u, child number 0
-------------------------------------
select count(*) from scott.t1 where object_id<:v_objid
Plan hash value: 4020739011
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | | | 5 (100)|
|
| 1 | SORT AGGREGATE | | 1 | 6 | |
|
|* 2 | INDEX RANGE SCAN| IND_OBJID_T1 | 8893 | 53358 | 5 (0)| 00:00:01
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|
--------------------------------------------------------------------------------
--
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / T1@SEL$1
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<:V_OBJID)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=0) COUNT(*)[22]
###從shared pool中將上述sql的執(zhí)行計劃load到sql plan baseline，load進來之后就變成了Accepted，沒有verify的過程
set serveroutput on
declare
result_int pls_integer;
begin
result_int:=DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id=>'9hup7n51za19u',plan_hash_value=>4020739011,fixed=>'NO',enabled=>'YES');
dbms_output.put_line(result_int);
end;
/
###在dba_sql_plan_baselines中找到了該條sql plan baseline，已經(jīng)被accepted
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines where sql_text like '%v_objid';
###再次執(zhí)行sql時已經(jīng)能用到了這條sql plan baseline了
variable v_objid number;
exec :v_objid:=500;
select count(*) from scott.t1 where object_id<:v_objid;
set autotrace traceonly;
select count(*) from scott.t1 where object_id<:v_objid;
Execution Plan
----------------------------------------------------------
Plan hash value: 4020739011
--------------------------------------------------------------------------------
--
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
--
| 0 | SELECT STATEMENT | | 1 | 6 | 5 (0)| 00:00:01
|
| 1 | SORT AGGREGATE | | 1 | 6 | |
|
|* 2 | INDEX RANGE SCAN| IND_OBJID_T1 | 8893 | 53358 | 5 (0)| 00:00:01
|
--------------------------------------------------------------------------------
--
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<TO_NUMBER(:V_OBJID))
Note
-----
- SQL plan baseline "SQL_PLAN_gm8nknf6mhghn28a6f5d9" used for this statement
Statistics
----------------------------------------------------------
27 recursive calls
16 db block gets
15 consistent gets
13 physical reads
3136 redo size
527 bytes sent via SQL*Net to client
520 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
(3) 通過dbms_sqltune對SQL語句進行調(diào)優(yōu)，并接受其調(diào)優(yōu)建議
這里沿用本文第一部分對于select count(*) from scott.t1 where object_id in (select object_id from scott.t2)語句生成的兩條sql plan baseline，作如下處理：刪除走索引的那條plan（只保留FTS）->使用sql tuning advisor對語句進行調(diào)優(yōu)->接受advisor使用索引訪問的建議
###人工刪除掉走索引的sql plan
set numformat 9999999999999999999999999
col sql_handle format a20
col creator format a5
col sql_text format a50
col created format a30
col last_modified format a30
col last_executed format a30
col last_verified format a30
set linesize 180
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified
from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828';
--刪除其中使用索引的那條
set serveroutput on
declare
result_int pls_integer;
begin
result_int:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk18b1b38b11');
dbms_output.put_line(result_int);
end;
/
--刪除成功只剩一條FTS的plan
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828';
###執(zhí)行dbms_sqltune，生成并接受優(yōu)化建議
--生成tuning任務(wù)
declare
my_task_name varchar2(30);
my_sqltext clob;
begin
my_sqltext:='select count(*) from scott.t1 where object_id in (select object_id from scott.t2)';
my_task_name:=dbms_sqltune.create_tuning_task(sql_text=>my_sqltext,user_name=>'SCOTT',scope=>'COMPREHENSIVE',time_limit=>60,task_name=>'scott_sql_tune_1',description=>'tune 1');
end;
/
--執(zhí)行tuning任務(wù)
begin
dbms_sqltune.execute_tuning_task(task_name=>'scott_sql_tune_1');
end;
/
###查看sqltune報告，截取了相關(guān)內(nèi)容
set long 9000
set longchunksize 1000
set linesize 800
select dbms_sqltune.report_tuning_task('scott_sql_tune_1') from dual;
1- Original With Adjusted Cost
------------------------------
Plan hash value: 1240933221
DBMS_SQLTUNE.REPORT_TUNING_TASK('SCOTT_SQL_TUNE_1')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 462 (2)| 00:00:06 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
|* 2 | HASH JOIN RIGHT SEMI| | 3 | 27 | 462 (2)| 00:00:06 |
| 3 | TABLE ACCESS FULL | T2 | 99 | 297 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | T1 | 177K| 1042K| 455 (1)| 00:00:06 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
DBMS_SQLTUNE.REPORT_TUNING_TASK('SCOTT_SQL_TUNE_1')
2- Using SQL Profile
--------------------
Plan hash value: 2406492491
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
DBMS_SQLTUNE.REPORT_TUNING_TASK('SCOTT_SQL_TUNE_1')
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 56 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | NESTED LOOPS | | 99 | 891 | 56 (2)| 00:00:01 |
| 3 | SORT UNIQUE | | 99 | 297 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 99 | 297 | 5 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IND_OBJID_T1 | 1 | 6 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
###接受Advisor推薦走索引的Profile，同時可以看到dba_sql_plan_baseline里又增加了一條accepted=yes的plan，這條正是我們剛才刪除的，表明接受dbms_sqltune的調(diào)優(yōu)結(jié)果也可以實現(xiàn)sql plan baseline的演進
execute dbms_sqltune.accept_sql_profile(task_name=>'scott_sql_tune_1',task_owner=>'SCOTT',replace=>TRUE);
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828';
###驗證已經(jīng)新的sql plan baseline已經(jīng)被使用
SQL> set autotrace traceonly explain
SQL>select count(*) from scott.t1 where object_id in (select object_id from scott.t2);
Execution Plan
----------------------------------------------------------
Plan hash value: 2406492491
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 9 | 56 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | NESTED LOOPS | | 99 | 891 | 56 (2)| 00:00:01 |
| 3 | SORT UNIQUE | | 99 | 297 | 5 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| T2 | 99 | 297 | 5 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | IND_OBJID_T1 | 1 | 6 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("OBJECT_ID"="OBJECT_ID")
Note
-----
- SQL profile "SYS_SQLPROF_0146fae6b2110000" used for this statement
- SQL plan baseline "SQL_PLAN_d27ct6y4awk18b1b38b11" used for this statement
階段總結(jié)：
方法(1)適用于已經(jīng)存在于sql plan history里但還未被accepted的sql plan，可以通過optimizer驗證(verify=yes)后實現(xiàn)演進，或者不通過驗證(verify=no)而直接演進為sql plan baseline
方法(2)在不開啟session級或system級自動捕捉(optimizer_capture_sql_plan_baselines=FALSE)的情況下，人工將已經(jīng)生成的執(zhí)行計劃裝載為sql plan baseline，即繞過optimizer的評估，直接演進為accepted plan的情況。這種方法需要人工確認(rèn)該執(zhí)行計劃是一定是最優(yōu)的，否則會導(dǎo)致后續(xù)按照該baseline執(zhí)行的SQL產(chǎn)生性能問題
方法(3)語句出現(xiàn)性能問題后，求助sql tuning advisor得到并應(yīng)用優(yōu)化建議，生成accepted的sql plan baseline，屬于事后調(diào)優(yōu)的范疇
3、SQL語句對應(yīng)的sql plan baseline均失效的情況下，sql plan演進會跳過verify步驟，直接變?yōu)閍ccepted
###Drop掉原有的sql plan baseline
declare
result_int pls_integer;
begin
result_int:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_d11d993788ae4828');
end;
/
###重新構(gòu)建測試環(huán)境
create table scott.t1 tablespace ts_pub as select * from dba_objects;
create table scott.t2 tablespace ts_pub as select * from dba_objects where rownum<100;
create index scott.ind_objid_t1 on scott.t1(object_id) tablespace ts_pub;
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t2',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
alter session set optimizer_capture_sql_plan_baselines=TRUE;
select count(*) from scott.t1 where object_id in (select object_id from scott.t2); --執(zhí)行至少兩次
alter session set optimizer_capture_sql_plan_baselines=FALSE;
###drop掉索引，再次執(zhí)行sql，觀察到dba_sql_plan_baselines里，索引對應(yīng)的plan REPRODUCED變成了NO，受索引被drop的影響此條plan baseline失效了；同時新增了一條FTS的plan，但狀態(tài)為not accepted
drop index scott.ind_objid_t1;
select count(*) from scott.t1 where object_id in (select object_id from scott.t2);
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828';
###現(xiàn)在把FTS的plan演進為Accepted sql plan baseline，從EVOLVE_SQL_PLAN_BASELINE函數(shù)的輸出可以看出，雖然指定了verify=YES，但因走索引的plan已經(jīng)失效，oracle并沒有進行verify就直接accept此plan了。
set serveroutput on
set long 10000
declare
result_clob clob;
begin
result_clob:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk1822a9c5af',verify=>'YES',commit=>'YES');
dbms_output.put_line(result_clob);
end;
/
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
------
Inputs:
-------
SQL_HANDLE = SQL_d11d993788ae4828
PLAN_NAME =
SQL_PLAN_d27ct6y4awk1822a9c5af
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY =
YES
COMMIT = YES
Plan:
SQL_PLAN_d27ct6y4awk1822a9c5af
------------------------------------
Plan was
not verified.
Using cost-based plan as could not reproduce any
accepted and
enabled baseline plan.
Plan was changed to an accepted
plan.
-------------------------------------------------------------------------
------
Report
Summary
------------------------------------------------------------------------
-------
Number of plans verified: 0
Number of plans accepted: 1
###演進的結(jié)果驗證，F(xiàn)TS 對應(yīng)的sql plan baseline已經(jīng)變成Accepted=yes了
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828'
###對于走索引的這條sql plan baseline，若要使其重新生效，即reproduced從NO變?yōu)閅ES，必須重新建立索引并且執(zhí)行一次sql才行
select count(*) from scott.t1 where object_id in (select object_id from scott.t2);
create index scott.ind_objid_t1 on scott.t1(object_id) tablespace ts_pub;
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t1',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
exec dbms_stats.gather_table_stats(ownname=>'scott',tabname=>'t2',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
###僅通過Verify并不能使其重新生效，提示已經(jīng)是accepted sql plan baseline
set serveroutput on
set long 10000
declare
result_clob clob;
begin
result_clob:=DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE(sql_handle=>'SQL_d11d993788ae4828',plan_name=>'SQL_PLAN_d27ct6y4awk18b1b38b11',verify=>'YES',commit=>'YES');
dbms_output.put_line(result_clob);
end;
/
-------------------------------------------------------------------------------
Evolve SQL Plan Baseline
Report
-------------------------------------------------------------------------
------
Inputs:
-------
SQL_HANDLE = SQL_d11d993788ae4828
PLAN_NAME =
SQL_PLAN_d27ct6y4awk18b1b38b11
TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
VERIFY =
YES
COMMIT = YES
Plan:
SQL_PLAN_d27ct6y4awk18b1b38b11
------------------------------------
It is
already an accepted
plan.
-------------------------------------------------------------------------
------
Report
Summary
------------------------------------------------------------------------
-------
There were no SQL plan baselines that required processing.
select
sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED
from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828'
###只有重新執(zhí)行sql，reproduced才會變?yōu)閅ES，此外還可以觀察到這兩條有效的sql plan baseline的last_verified字段均為空，表明這兩條sql plan入駐的時候都沒有經(jīng)過verify，也間接說明了入駐的當(dāng)時沒有有效的sql plan baseline存在，是被直接”保送”進了sql plan baseline
select count(*) from scott.t1 where object_id in (select object_id from scott.t2);
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED
from dba_sql_plan_baselines where sql_handle='SQL_d11d993788ae4828';
1、不同用戶針對各自用戶下的表，執(zhí)行同一條sql語句， sql plan baseline的共享機制
測試場景描述：兩個用戶scott1、scott2下各有一張名為t1的表，scott1.t1(object_id)上建立名為ind_objid_t的non-unique索引，且在scott1用戶下執(zhí)行select * from t1 where object_id<100000生成首條sql plan baseline；之后分別在以下幾種場景下使用Scott2用戶執(zhí)行同樣的語句：select * from t1 where object_id<100000，觀察是否能用到scott1用戶生成的首條sql plan baseline，這幾種場景包括：
(1) Scott2.t1(object_id)字段沒有索引
(2) Scott2.t1(object_id)字段創(chuàng)建non-unique索引，索引名稱和Scott1保持一致
(3) Scott2.t1(object_id)字段創(chuàng)建non-unique索引， 索引名稱和Scott1保持一致，人工增大clustering_factor，使Optimizer偏向選擇FTS
(4) Scott2.t1(object_id)字段創(chuàng)建non-unique索引，索引名稱有別于Scott1
(5) Scott2.t1(object_id)字段創(chuàng)建unique索引，索引名稱和Scott1保持一致
(6) 重建Scott2.t1表，同時更改scott2.t1表結(jié)構(gòu)，除了object_id字段外，其余字段均和Scott2.t1中的字段不相同
數(shù)據(jù)環(huán)境準(zhǔn)備：
###生成scott1用戶下的表
grant connect,resource,unlimited tablespace to scott1 identified by scott1_1234;
grant plustrace to scott1;
create table scott1.t1 tablespace ts_pub as select * from dba_objects;
create index scott1.ind_objid_t on scott1.t1(object_id) tablespace ts_pub;
exec dbms_stats.gather_table_stats(ownname=>'scott1',tabname=>'t1',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
###生成scott2用戶下的表
grant connect,resource,unlimited tablespace to scott2 identified by scott2_5678;
grant plustrace to scott2;
create table scott2.t1 tablespace ts_pub as select * from dba_objects;
exec dbms_stats.gather_table_stats(ownname=>'scott2',tabname=>'t1',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
##清理現(xiàn)有環(huán)境中的sql plan baseline，保持dba_sql_plan_baseline為空
set serveroutput on
declare
result_int pls_integer;
cursor t_cur is select distinct sql_handle from dba_sql_plan_baselines;
begin
for v_cur in t_cur loop
result_int:=dbms_spm.drop_sql_plan_baseline(sql_handle=>v_cur.sql_handle);
dbms_output.put_line(result_int);
end loop;
end;
/
alter system flush shared_pool;
##scott1用戶生成首條sql plan baseline，
sqlplus scott1/scott1_1234
alter session set optimizer_capture_sql_plan_baselines=true;
select * from t1 where object_id<100000; --執(zhí)行至少兩遍
alter session set optimizer_capture_sql_plan_baselines=false;
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED from dba_sql_plan_baselines;
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_91e3f036b4b3ac44',plan_name=>'SQL_PLAN_93szh7uub7b2453067583')); --對應(yīng)的執(zhí)行計劃是index range scan
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3560 | 337K| 213 (0)|
00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3560 | 337K| 213 (0)|
00:00:03 |
|* 2 | INDEX RANGE SCAN | IND_OBJID_T | 3560 | | 10 (0)|
00:00:01 |
場景(1): Scott2.t1(object_id)字段沒有索引，Scott2用戶執(zhí)行select * from t1 where object_id<100000;
select * from t1 where object_id<100000;
###t1.object_id字段沒有索引，無法用上Scott1用戶下的baseline，但會把Scott1用戶創(chuàng)建的plan變成reproduced=NO同時在sql plan history里生成了一條FTS的plan，Creator為scott2，狀態(tài)為not accepted
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED
from dba_sql_plan_baselines;
select * from table(dbms_xplan.display_sql_plan_baseline(sql_handle=>'SQL_91e3f036b4b3ac44',plan_name=>'SQL_PLAN_93szh7uub7b24dbd90e8e')); --plan_name= SQL_PLAN_93szh7uub7b24dbd90e8e執(zhí)行計劃如下
--------------------------------------------------------------------------------
SQL handle: SQL_91e3f036b4b3ac44
SQL text: select * from t1 where object_id<100000
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
Plan name: SQL_PLAN_93szh7uub7b24dbd90e8e Plan id: 3688435342
Enabled: YES Fixed: NO Accepted: NO Origin: AUTO-CAPTURE
--------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 838529891
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3560 | 337K| 456 (1)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T1 | 3560 | 337K| 456 (1)| 00:00:06 |
--------------------------------------------------------------------------
階段結(jié)論：scott2用戶的t1表上沒有索引，優(yōu)化器為sql生成的執(zhí)行計劃無法與scott1用戶創(chuàng)建的sql plan baseline匹配，所以只能采用FTS的訪問路徑添加到sql plan history，同時將scott1用戶plan_name=SQL_PLAN_93szh7uub7b2453067583置為reproduced=NO。可見優(yōu)化器在匹配sql plan baseline時依據(jù)的是sql_handle，和這個plan的creator無關(guān)。
場景(2): Scott2.t1(object_id)字段創(chuàng)建non-unique索引，索引名稱和Scott1保持一致
##接著場景(1)，在scott2.t1(object_id)創(chuàng)建和scott1同名的索引
create index scott2.ind_objid_t on scott2.t1(object_id) tablespace ts_pub;
exec dbms_stats.gather_table_stats(ownname=>'scott2',tabname=>'t1',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
##scott2執(zhí)行sql，看到plan_name=SQL_PLAN_93szh7uub7b2453067583重新變?yōu)镽EPRODUCED=YES了，而且通過sql語句的執(zhí)行計劃可以看到plan_name=SQL_PLAN_93szh7uub7b2453067583重新被使用上了
set autotrace traceonly
select * from t1 where object_id<100000;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 3560 | 337K| 213 (0)|
00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3560 | 337K| 213 (0)|
00:00:03 |
|* 2 | INDEX RANGE SCAN | IND_OBJID_T | 3560 | | 10 (0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<100000)
Note
-----
- SQL plan baseline "SQL_PLAN_93szh7uub7b2453067583" used for this statement
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED from dba_sql_plan_baselines
階段結(jié)論：scott2. t1表與scott2.t1完全相同，這個相同包括表結(jié)構(gòu)、索引名稱、統(tǒng)計信息等都和scott1.t1保持一致，所以生成的執(zhí)行計劃能完全匹配scott1走索引的plan_name，REPRODUCED重新置為YES
場景(3): Scott2.t1(object_id)字段創(chuàng)建non-unique索引， 索引名稱和Scott1保持一致，人工增大clustering_factor，使Optimizer偏向選擇FTS
##創(chuàng)建Scott2.t1(object_id)索引
。。。步驟同上，此處省略
##先把optimizer_use_sql_plan_baselines設(shè)成false，觀察一下未啟用sql plan baseline的情況下，改大scott2.t1
表索引的clustering_factor值，對執(zhí)行計劃的影響
---修改前走的是index range scan
alter session set optimizer_use_sql_plan_baselines=FALSE;
select table_name,index_name,clustering_factor from user_indexes where table_name='T1';
TABLE_NAME INDEX_NAME CLUSTERING_FACTOR
------------------------------ ------------------------------ -----------------
T1 IND_OBJID_T 10126
set autotrace traceonly
select * from t1 where object_id<100000;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 3560 | 337K| 213 (0)|
00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3560 | 337K| 213 (0)|
00:00:03 |
|* 2 | INDEX RANGE SCAN | IND_OBJID_T | 3560 | | 10 (0)|
00:00:01 |
---修改后走的是fts
exec dbms_stats.set_index_stats(ownname=>'SCOTT2',indname=>'IND_OBJID_T',clstfct=>2000000);
select table_name,index_name,clustering_factor from user_indexes where table_name='T1';
TABLE_NAME INDEX_NAME CLUSTERING_FACTOR
------------------------------ ------------------------------ -----------------
T1 IND_OBJID_T 2000000
set autotrace traceonly
select * from t1 where object_id<100000;
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3560 | 337K| 456 (1)| 00:00:06 |
|* 1 | TABLE ACCESS FULL| T1 | 3560 | 337K| 456 (1)| 00:00:06 |
--------------------------------------------------------------------------
##optimizer_use_sql_plan_baselines置為true，觀察在啟用sql plan baseline的情況下，在IND_OBJID_T索引統(tǒng)
計信息改變之后，oracle是否還會繼續(xù)去啟用plan_name=SQL_PLAN_93szh7uub7b2453067583這條走索引
的plan
--為使結(jié)果更為明朗，這里先刪除掉scott2用戶在場景(1)里創(chuàng)建出的走FTS的plan
set serveroutput on
declare
result_int pls_integer;
begin
result_int:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_91e3f036b4b3ac44',plan_name=>'SQL_PLAN_93szh7uub7b24dbd90e8e');
dbms_output.put_line(result_int);
end;
/
--只剩一條走索引的plan= SQL_PLAN_93szh7uub7b2453067583
Select
sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRO
DUCED from
dba_sql_plan_baselines;
--scott2用戶執(zhí)行sql，plan= SQL_PLAN_93szh7uub7b2453067583會被啟用
alter session set optimizer_use_sql_plan_baselines=TRUE;
set autotrace traceonly
select * from t1 where object_id<100000;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 3560 | 337K| 40066 (1)|
00:08:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3560 | 337K| 40066 (1)|
00:08:01 |
|* 2 | INDEX RANGE SCAN | IND_OBJID_T | 3560 | | 10 (0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<100000)
Note
-----
- SQL plan baseline "SQL_PLAN_93szh7uub7b2453067583" used for this statement
---但同時也會生成一個FTS的plan，clustering_factor值遠(yuǎn)大于table所占用的blocks的情況下，、優(yōu)化器認(rèn)為
FTS才是合適的選擇
select
sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRO
DUCED from
dba_sql_plan_baselines
階段結(jié)論：只要sql plan baseline的reproduced!=NO，就一定會被優(yōu)化器選中，哪怕這條baseline
對應(yīng)的執(zhí)行計劃效率再差。與此同時優(yōu)化器執(zhí)行sql時還是要去收集所執(zhí)行對象的統(tǒng)計信息，
并且把它計算出的執(zhí)行計劃添加到sql plan history作為演進時的候選對象。
場景(4): Scott2.t1(object_id)字段創(chuàng)建non-unique索引，索引名稱有別于Scott1
##修改Scott2.ind_objid_t索引名稱
alter index scott2.IND_OBJID_T rename to IND_OBJID_T2;
exec dbms_stats.gather_table_stats(ownname=>'scott2',tabname=>'t1',method_opt=>'for all columns size
1',cascade=>TRUE,no_invalidate=>FALSE);
##重新執(zhí)行sql，得到了不同的執(zhí)行計劃(這里的不同主要是指索引名稱的改變，訪問的路徑還是index range
scan)，結(jié)果是在dba_sql_plan_baseline里新增了1條plan_name=SQL_PLAN_93szh7uub7b2483309cfd，與此
同時還發(fā)現(xiàn)scott1用戶下的plan_name= SQL_PLAN_93szh7uub7b2453067583 reproduced屬性變?yōu)镹O，原
因是索引名稱變了匹配不上了，即IND_OBJID_T !=IND_OBJID_T2
set autotrace traceonly
select * from t1 where object_id<100000;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 3560 | 337K| 213 (0)
| 00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3560 | 337K| 213 (0)
| 00:00:03 |
|* 2 | INDEX RANGE SCAN | IND_OBJID_T2 | 3560 | | 10 (0)
| 00:00:01 |
階段結(jié)論：雖然我們平時關(guān)注的主要是執(zhí)行計劃中的access-path部分，但其實索引名稱也是執(zhí)行計劃的重要組成部分也是決定sql plan baseline能否被重用的一個重要因素
場景(5): Scott2.t1(object_id)字段創(chuàng)建unique索引，索引名稱和Scott1保持一致
##scott2重建索引，名稱和scott1名稱等同，但索引類型變?yōu)閡nique
--先Drop掉creator=scott2的兩條sql plan
set serveroutput on
declare
result_int1 pls_integer;
result_int2 pls_integer;
begin
result_int1:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_91e3f036b4b3ac44',plan_name=>'SQL_PLAN_93szh7uub7b24dbd90e8e');
result_int2:=dbms_spm.drop_sql_plan_baseline(sql_handle=>'SQL_91e3f036b4b3ac44',plan_name=>'SQL_PLAN_93szh7uub7b2483309cfd');
dbms_output.put_line(result_int1);
dbms_output.put_line(result_int2);
end;
/
select sql_handle,sql_text,plan_name,enabled,accepted,creator,created,last_modified,last_executed,last_verified,REPRODUCED from dba_sql_plan_baselines;
--重建scott2.t1上的索引
drop index scott2.ind_objid_t2;
create unique index scott2.ind_objid_t on scott2.t1(object_id) tablespace ts_pub;
exec dbms_stats.gather_table_stats(ownname=>'scott2',tabname=>'t1',method_opt=>'for all columns size 1',cascade=>TRUE,no_invalidate=>FALSE);
##scott2執(zhí)行sql觀察到scott1用戶的plan_name=SQL_PLAN_93szh7uub7b2453067583還是能夠被利用
set autotrace traceonly
select * from t1 where object_id<100000;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 3560 | 337K| 212 (0)|
00:00:03 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 3560 | 337K| 212 (0)|
00:00:03 |
|* 2 | INDEX RANGE SCAN | IND_OBJID_T | 3560 | | 9 (0)|
00:00:01 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<100000)
Note
-----
- SQL plan baseline "SQL_PLAN_93szh7uub7b2453067583" used for this statement
階段結(jié)論：雖然這次索引變成了unique的，但執(zhí)行計劃中并沒有使用index unique scan，用的依然是index range scan，這就和plan=SQL_PLAN_93szh7uub7b2453067583所指向的access-path保持一致，說明只要在access-path，索引名稱相同的情況下，oracle不會對索引是否為unique有強制的要求
場景(6): 重建Scott2.t1表，同時更改scott2.t1表結(jié)構(gòu)，除了object_id字段外，其余字段均和Scott2.t1中的字段不相同
##重構(gòu)Scott2.t1表
drop table scott2.t1;
create table scott2.t1 (col1 varchar2(2),object_id number,col3 varchar2(100)) tablespace ts_pub;
declare
begin
for i in 1..170000 loop
insert into scott2.t1 values('AA',i,'scott2.t1');
end loop;
commit;
end;
/
create index scott2.ind_objid_t on scott2.t1(object_id) tablespace ts_pub;
exec dbms_stats.gather_table_stats(ownname=>'scott2',tabname=>'t1',method_opt=>'for all columns size
1',cascade=>TRUE,no_invalidate=>FALSE);
##scott用戶執(zhí)行sql，sql plan baseline能夠被重用
set autotrace traceonly
select * from t1 where object_id<100000;
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
-----------
| 0 | SELECT STATEMENT | | 100K| 1757K| 545 (1)|
00:00:07 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 100K| 1757K| 545 (1)|
00:00:07 |
|* 2 | INDEX RANGE SCAN | IND_OBJID_T | 100K| | 225 (1)|
00:00:03 |
--------------------------------------------------------------------------------
-----------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"<100000)
Note
-----
- SQL plan baseline "SQL_PLAN_93szh7uub7b2453067583" used for this statement
階段結(jié)論：只要執(zhí)行計劃能完全匹配上，就能利用到已生成的sql plan baseline，對于表結(jié)構(gòu)，
表內(nèi)容等項目oracle不作檢查，可見sql plan baseline對環(huán)境的適應(yīng)能力是很強的，除了對象不可用之外(例如索引被刪除)，都能將預(yù)先生成的執(zhí)行計劃提供給優(yōu)化器執(zhí)行。
以上是“數(shù)據(jù)庫中sql plan baseline怎么用”這篇文章的所有內(nèi)容，感謝各位的閱讀！希望分享的內(nèi)容對大家有幫助，更多相關(guān)知識，歡迎關(guān)注億速云行業(yè)資訊頻道！
免責(zé)聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
