--源端操作
第一步：创建测试用户
su - oracle
sqlplus / as sysdba
create user spa identified by spa default tablespace users;
grant connect,resource to spa;
grant ADMINISTER SQL TUNING SET to spa;
grant execute on dbms_sqltune to spa;
grant select any dictionary to spa;
grant advisor to spa;
需要监控空间使用情况
第二步：创建SQL Tuning Set （mos (Doc ID 1271343.1)）--》使用awr快照的方式
su - oracle
sqlplus spa/spa
EXEC dbms_sqltune.create_sqlset('mysts');
--EXEC dbms_sqltune.drop_sqlset('mysts');
--查询收集的sql记录数
select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;
set linesize 400
select snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;
--替换snap_id
DECLARE
cur sys_refcursor;
BEGIN
open cur for
select value(p) from table(dbms_sqltune.select_workload_repository(begin_snap =>23, end_snap =>24)) p;
dbms_sqltune.load_sqlset('mysts', cur);
close cur;
END;
/
10.2.0.4 使用此方法进行收集awr
DECLARE
cur sys_refcursor;
BEGIN
open cur for
select value(p) from table(dbms_sqltune.select_workload_repository(15,16)) p;
dbms_sqltune.load_sqlset('mysts', cur);
close cur;
END;
/
--收集内存sql
DECLARE
cur sys_refcursor;
BEGIN
open cur for
select value(p) from table(dbms_sqltune.select_cursor_cache('sql_text like ''%insert into%'' ')) p;
dbms_sqltune.load_sqlset('mysts', cur);
close cur;
END;
/
DBMS_SQLTUNE.LOAD_SQLSET (
sqlset_name IN VARCHAR2,
populate_cursor IN sqlset_cursor,
load_option IN VARCHAR2 := 'INSERT',
update_option IN VARCHAR2 := 'REPLACE',
update_condition IN VARCHAR2 := NULL,
update_attributes IN VARCHAR2 := NULL,
ignore_null IN BOOLEAN := TRUE,
commit_rows IN POSITIVE := NULL,
sqlset_owner IN VARCHAR2 := NULL);
查看磁盘排序前10的sql
SELECT sql_id
,substr(sql_text,1,100)
,disk_reads, cpu_time, elapsed_time
FROM table(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(15,16,
null, null, 'disk_reads',null, null, null, 10))
ORDER BY disk_reads DESC;
--查询收集的sql记录数
select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;
第三步：存储信息到表中（ mos (Doc ID 751068.1)）
--创建转移表
su - oracle
sqlplus spa/spa
BEGIN
DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET_TAB_SPA',
schema_name => 'SPA',
tablespace_name => 'USERS');
END;
/
BEGIN
DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET_TAB_SPA',
schema_name => 'TEST',
tablespace_name => 'USERS');
END;
/
--将优化集打包到表 SQLSET_TAB_SPA里
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'mysts',
sqlset_owner => 'SPA',
staging_table_name => 'SQLSET_TAB_SPA',
staging_schema_owner => 'SPA');
END;
/
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'mysts',
sqlset_owner => 'TEST',
staging_table_name => 'SQLSET_TAB_SPA',
staging_schema_owner => 'TEST');
END;
/
--导出用户和表
sqlplus / as sysdba
grant read,write on directory cis_outdir to spa;
expdp spa/spa tables=SQLSET_TAB_SPA directory=CIS_OUTDIR dumpfile=exp_SQLSET_TAB_SPA.dmp logfile=exp_SQLSET_TAB_SPA.log cluster=n
--目标端操作
第四步：创建spa，名称与源端一直
su - oracle
sqlplus / as sysdba
create user spa identified by spa default tablespace users;
grant connect,resource to spa;
grant ADMINISTER SQL TUNING SET to spa;
grant execute on dbms_sqltune to spa;
grant select any dictionary to spa;
grant advisor to spa;
grant read,write on directory cis_outdir to spa;
exit
impdp test/test fromuser=test touser=test directory=CIS_OUTDIR dumpfile=exp_SQLSET_TAB_SPA.dmp log=imp_SQLSET_TAB_SPA.log
sqlplus test/test
EXEC dbms_sqltune.create_sqlset('mysts');
第五步；
--解包文件（把中转表里的数据解压到新建的 sqlset）
需要转换用户的话
exec dbms_sqltune.remap_stgtab_sqlset(old_sqlset_name=>'mysts',old_sqlset_owner=>'SPA',new_sqlset_name =>'mysts',new_sqlset_owner =>'TEST',staging_table_name =>'SQLSET_TAB_SPA',staging_schema_owner=>'TEST');
先执行上面的sql，注意修改user name
BEGIN
DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => 'mysts',
sqlset_owner => 'TEST',
replace => TRUE,
staging_table_name => 'SQLSET_TAB_SPA',
staging_schema_owner => 'TEST');
END;
/
第六步：创建spa 任务
exec DBMS_SQLPA.DROP_ANALYSIS_TASK('SPA_TEST');
var tname varchar2(30);
var sname varchar2(30);
exec :sname := 'mysts';
exec :tname := 'SPA_TEST';
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname);
第七步：执行spa任务
exec DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'SPA_TEST',execution_type => 'CONVERT SQLSET',execution_name => 'exec_pre_change');
alter system flush shared_pool;
alter system flush BUFFER_CACHE;
exec DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name => 'SPA_TEST',execution_type => 'TEST EXECUTE',execution_name => 'exec_post_change');
第八步：生成比较(eg. elapsed time):
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_elapsed_time',
execution_params => dbms_advisor.arglist('execution_name1','exec_pre_change', 'execution_name2', 'exec_post_change', 'comparison_metric', 'elapsed_time') );
end;
/
第九步：生成spa报告
set long 100000 longchunksize 100000 linesize 200 head off feedback off echo off
spool spa_report_elapsed_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TEST', 'HTML', 'ALL','ALL', execution_name=>'Compare_elapsed_time') -- this execution_name matches the execution_name in step 7
FROM dual;
spool off
不同的检查点测试
-------------从elapsed_time来进行比较
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_elapsed_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_10G', 'execution_name2', 'EXEC_11G', 'comparison_metric', 'elapsed_time') );
end;
/
-------------从cpu_time来进行比较
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_CPU_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_10G', 'execution_name2', 'EXEC_11G', 'comparison_metric', 'CPU_TIME') );
end;
/
-------------从buffer_gets来进行比较
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST',
execution_type => 'COMPARE PERFORMANCE',
execution_name => 'Compare_BUFFER_GETS_time',
execution_params => dbms_advisor.arglist('execution_name1', 'CONVERT_10G', 'execution_name2', 'EXEC_11G', 'comparison_metric', 'BUFFER_GETS') );
end;
-------------生成SPA报告
set trimspool on
set trim on
set pages 0
set long 999999999
set linesize 1000
spool spa_report_elapsed_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TEST', 'HTML', 'ALL','ALL', top_sql=>300,execution_name=>'Compare_elapsed_time') FROM dual;
spool off;
spool spa_report_CPU_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TEST', 'HTML', 'ALL','ALL', top_sql=>300,execution_name=>'Compare_CPU_time') FROM dual;
spool off;
spool spa_report_buffer_time.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TEST','HTML','ALL','ALL',top_sql=>300,execution_name=>'Compare_BUFFER_GETS_time') FROM dual;
spool off;
spool spa_report_errors.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TEST', 'HTML', 'errors','summary') FROM dual;
spool off;
spool spa_report_unsupport.html
SELECT dbms_sqlpa.report_analysis_task('SPA_TEST', 'HTML', 'unsupported','all') FROM dual;
spool off;
/
