UE(p) from table(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(
&begin_snap_id,
&end_snap_id,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
NULL,
'ALL')) p;
DBMS_SQLTUNE.LOAD_SQLSET(
sqlset_name => 'my_sql_tuning_set',
populate_cursor => baseline_cursor);
end;
/
SELECT * FROM TABLE(DBMS_SQLTUNE.SELECT_SQLSET(
'my_sql_tuning_set',
'(disk_reads/buffer_gets) >= 0.75'));
---3.新建搜集baseline的表
BEGIN
dbms_spm.create_stgtab_baseline(
table_name => 'BASELINE_STG01',
table_owner => 'SCOTT',
db_version => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION
); --不能新建在SYS账户下
END;
/
----4.把Baseline数据填到表
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(
sqlset_name => 'OCPYANG_STS',
sqlset_owner => 'SCOTT',
staging_table_name => 'BASELINE_STG01',
staging_schema_owner => 'SCOTT',
db_version => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION );
END;
/
/**********语法
DBMS_SQLTUNE.PACK_STGTAB_SQLSET (
sqlset_name IN VARCHAR2,
sqlset_owner IN VARCHAR2 := NULL,
staging_table_name IN VARCHAR2,
staging_schema_owner IN VARCHAR2 := NULL,
db_version IN NUMBER := NULL);
***********/
---5.传递数据到目标服务器
使用Oracle Data Pump or database link or expdp等将表BASELINE_STG01
迁移到目标服务器.
---6.目标服务器新建STS
BEGIN
DBMS_SQLTUNE.CREATE_SQLSET(
sqlset_name => 'OCPYANG_STS01',
sqlset_owner => 'SCOTT',
description => 'ocpyangtest');
END;
/
----7.导入数据到目标服务器的STS
BEGIN
DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(
sqlset_name => 'OCPYANG_STS01',
replace => TRUE,
staging_table_name => 'BASELINE_STG01');
END;
/
---8.通过SPM BASELINE的包来把SQL调优集里的SQL都批量的生成BASELINE
declare
ret number;
begin
ret := dbms_spm.load_plans_from_sqlset(
sqlset_name => 'OCPYANG_STS01',
sqlset_owner => 'SCOTT');
end;
/
|