##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博客 ” ，链接：https://blog.itpub.net/28572479/viewspace-2140791/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
