SPM適用的場景和示例
##spm開啟自動捕獲
SQL> show parameter sql_plan
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_capture_sql_plan_baselines boolean FALSE
optimizer_use_sql_plan_baselines boolean TRUE
將optimizer_capture_sql_plan_baselines設為為true，開啟自動捕獲
##SPM批次載入
#1 cursor cache中載入
exec DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE('sql_id');
#2 從sql tuning set中載入
exec DBMS_SPM.LOAD_PLANS_FROM_SQLSET('sql_tuning_set_name');
select * from DBA_SQLSET;
# 3 透過函式建立中間表，並透過Data Dump匯出匯入
exec DBMS_SPM.CREATE_STGTAB_BASELINE
SQL> desc DBMS_SPM
FUNCTION ALTER_SQL_PLAN_BASELINE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_HANDLE VARCHAR2 IN DEFAULT
PLAN_NAME VARCHAR2 IN DEFAULT
ATTRIBUTE_NAME VARCHAR2 IN
ATTRIBUTE_VALUE VARCHAR2 IN
PROCEDURE CONFIGURE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PARAMETER_NAME VARCHAR2 IN
PARAMETER_VALUE NUMBER IN DEFAULT
PROCEDURE CREATE_STGTAB_BASELINE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN
TABLE_OWNER VARCHAR2 IN DEFAULT
TABLESPACE_NAME VARCHAR2 IN DEFAULT
FUNCTION DROP_MIGRATED_STORED_OUTLINE RETURNS BINARY_INTEGER
FUNCTION DROP_SQL_PLAN_BASELINE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_HANDLE VARCHAR2 IN DEFAULT
PLAN_NAME VARCHAR2 IN DEFAULT
FUNCTION EVOLVE_SQL_PLAN_BASELINE RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_HANDLE VARCHAR2 IN DEFAULT
PLAN_NAME VARCHAR2 IN DEFAULT
TIME_LIMIT NUMBER(38) IN DEFAULT
VERIFY VARCHAR2 IN DEFAULT
COMMIT VARCHAR2 IN DEFAULT
FUNCTION EVOLVE_SQL_PLAN_BASELINE RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
PLAN_LIST NAME_LIST IN
TIME_LIMIT NUMBER(38) IN DEFAULT
VERIFY VARCHAR2 IN DEFAULT
COMMIT VARCHAR2 IN DEFAULT
FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ATTRIBUTE_NAME VARCHAR2 IN
ATTRIBUTE_VALUE VARCHAR2 IN
FIXED VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
FIXED VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
SQL_TEXT CLOB IN
FIXED VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
FUNCTION LOAD_PLANS_FROM_CURSOR_CACHE RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN
PLAN_HASH_VALUE NUMBER IN DEFAULT
SQL_HANDLE VARCHAR2 IN
FIXED VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
FUNCTION LOAD_PLANS_FROM_SQLSET RETURNS BINARY_INTEGER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQLSET_NAME VARCHAR2 IN
SQLSET_OWNER VARCHAR2 IN DEFAULT
BASIC_FILTER VARCHAR2 IN DEFAULT
FIXED VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
COMMIT_ROWS NUMBER IN DEFAULT
FUNCTION MIGRATE_STORED_OUTLINE RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
ATTRIBUTE_NAME VARCHAR2 IN
ATTRIBUTE_VALUE CLOB IN DEFAULT
FIXED VARCHAR2 IN DEFAULT
FUNCTION MIGRATE_STORED_OUTLINE RETURNS CLOB
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
OUTLN_LIST NAME_LIST IN
FIXED VARCHAR2 IN DEFAULT
FUNCTION PACK_STGTAB_BASELINE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN
TABLE_OWNER VARCHAR2 IN DEFAULT
SQL_HANDLE VARCHAR2 IN DEFAULT
PLAN_NAME VARCHAR2 IN DEFAULT
SQL_TEXT CLOB IN DEFAULT
CREATOR VARCHAR2 IN DEFAULT
ORIGIN VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
ACCEPTED VARCHAR2 IN DEFAULT
FIXED VARCHAR2 IN DEFAULT
MODULE VARCHAR2 IN DEFAULT
ACTION VARCHAR2 IN DEFAULT
FUNCTION UNPACK_STGTAB_BASELINE RETURNS NUMBER
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
TABLE_NAME VARCHAR2 IN
TABLE_OWNER VARCHAR2 IN DEFAULT
SQL_HANDLE VARCHAR2 IN DEFAULT
PLAN_NAME VARCHAR2 IN DEFAULT
SQL_TEXT CLOB IN DEFAULT
CREATOR VARCHAR2 IN DEFAULT
ORIGIN VARCHAR2 IN DEFAULT
ENABLED VARCHAR2 IN DEFAULT
ACCEPTED VARCHAR2 IN DEFAULT
FIXED VARCHAR2 IN DEFAULT
MODULE VARCHAR2 IN DEFAULT
ACTION VARCHAR2 IN DEFAULT
SQL>
##適用場景
# 10G升級11G
1 在現有10G環境捕獲sql語句的執行計劃儲存在STS中
begin
DBMS_SQLTUNE.CREATE_SQLSET('STS111TEST');
DBMS_SQLTUNE.CAPTURE_CURSOR_CACHE_SQLSET(SQLSET_NAME => 'STS111TEST',
TIME_LIMIT => 120,
REPEAT_INTERVAL => 5
);
end;
/
上面PL/SQL塊表示：先建立STS111TEST的STS，採集時長120秒，每隔5秒採集一次sql執行計劃到STS中。
2 建立中間表(STGTAB111),並將 STS111TEST中的內容包裝到中間表(STGTAB111)中
begin
DBMS_SQLTUNE.CREATE_STGTAB_SQLSET('STGTAB111');
DBMS_SQLTUNE.PACK_STGTAB_SQLSET(SQLSET_NAME => 'STS111TEST',
STAGING_TABLE_NAME => 'STGTAB111'
);
end;
/
3 通exp/expdp,imp/impdp將STGTAB111表內容匯入11G目標資料中
exp system/oracle file=STGTAB111.dmp tables=STGTAB111
imp system/oracle file=STGTAB111.dmp full=y
4 下面的PL/SQL塊：現將中間表(STGTAB111)開啟並儲存在STS111TEST的STS內，然後再把這些語句的執行計劃匯入Plan Baseline中
declare
my_plans pls_integer;
begin
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(SQLSET_NAME => 'STS111TEST',
SQLSET_OWNER => '%',
REPLACE => TRUE,
STAGING_TABLE_NAME => 'STGTAB111'
);
my_plans := DBMS_SQLTUNE.LOAD_PLANS_FROM_SQLSET(
SQLSET_NAME => 'STS111TEST',
SQLSET_OWNER => 'SYS',
BASIC_FILTER => 'sql_text like ''%''',
FIXED => 'YES',
ENABLED => 'YES',
COMMIT_ROWS => 1
);
end;
/
#從開發資料庫到生產資料庫
1 建立SPM_SAVED的stage表，在將SQL Plan Baseline資料匯入SPM_SAVED表
declare
spm number;
begin
DBMS_SPM.CREATE_STGTAB_BASELINE(
TABLE_NAME => 'SPM_SAVED',
TABLE_OWNER => 'SYSTEM'
);
spm := DBMS_SPM.PACK_STGTAB_BASELINE(
TABLE_NAME => 'SPM_SAVED',
TABLE_OWNER => 'SYSTEM'
);
end;
/
2 通exp/expdp,imp/impdp將SPM_SAVED表內容匯入生產資料中
exp system/oracle file=SPM_SAVED.dmp tables=SPM_SAVED
imp system/oracle file=SPM_SAVED.dmp full=y
3 將SPM_SAVED表的內容匯入生產庫的QL Plan Baseline
declare
spm number;
begin
spm := DBMS_SPM.UNPACK_STGTAB_BASELINE(
TABLE_NAME => 'SPM_SAVED',
TABLE_OWNER => 'SYSTEM'
);
end;
/
select SIGNATURE,SQL_HANDLE,SQL_TEXT,PLAN_NAME,ORIGIN,ENABLED,ACCEPTED,FIXED,AUTOPURGE,LAST_EXECUTED,LAST_MODIFIED from dba_sql_plan_baselines;
注：大部分內容出自：羅敏 <<感悟oracle核心技術>>
注：大部分內容出自：羅敏 <<感悟oracle核心技術>>
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28572479/viewspace-2140791/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MongoDB的優勢和適用場景MongoDB
- Streaming特性和適用場景
- Spark適用於哪些場景？不適用於哪些場景？Spark
- 本地索引和全域性索引的適用場景索引
- CDN適用哪些場景？
- TokuDB的優缺點和適用場景介紹
- INDEX SKIP SCAN適用場景Index
- 閉鎖和柵欄的區分以及適用場景
- 設計模式適用場景整理設計模式
- 區塊鏈不適用的若干場景區塊鏈
- innobackupex中--slave-info引數的含義和適用場景
- 全息投影技術所適用的場景分析
- 談談 Redux 與 Mobx 思想的適用場景Redux
- 函式索引的兩個應用場景示例（下）函式索引
- 企業雲盤適用哪些應用場景
- 【優化】ALL_ROWS模式和FIRST_ROWS模式的適用場景優化模式
- BITMAP CONVERSION FROM ROWIDS 適用場景
- 機械硬碟與固態硬碟的適用場景硬碟
- 物件儲存適用於哪些場景？這5個場景皆可使用！物件
- 【最佳化】ALL_ROWS模式和FIRST_ROWS模式的適用場景模式
- list與Set、Map區別及適用場景
- NIO是什麼？適用於何種場景？
- 企業API閘道器適用業務場景API
- Kotlin邊用邊學：Inline Functions的適用場景KotlininlineFunction
- 低程式碼適用於哪些應用開發場景
- 小議“悲觀鎖和樂觀鎖”的原理、場景、示例
- Hbase和Hive的特點，和應用場景Hive
- 箭頭函式適用場景及需要注意的地方函式
- 輪換代理介紹及適用場景介紹
- 自定義npm模組包——打包後適用多場景NPM
- NodeJS優缺點及適用場景討論NodeJS
- Tomcat高階配置(應用場景總結及示例)Tomcat
- 各種分散式事務的實現方式適用的場景分散式
- SQL 查詢 exist join in 的用法和相應的適用場景 （最佳化查詢）SQL
- ORA-00060的示例與若干場景
- 什麼場景適合mongodbMongoDB
- MyISAM和InnoDB的主要區別和應用場景
- 遷移學習的基礎研究問題及適用場景遷移學習