–DBMS_SQLTUNE
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/index.html
–DBMS_SQLPA
https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_SQLPA.html#GUID-9AAF3672-1CF3-4354-AF41-5BA50BA295F8
1. 查询需要做SPA的时间段的AWR快照号
SET LINES 188 PAGES 1000
COL SNAP_TIME FOR A22
select INSTANCE_NUMBER,SNAP_ID,BEGIN_INTERVAL_TIME,END_INTERVAL_TIME
from DBA_HIST_SNAPSHOT WHERE END_INTERVAL_TIME > trunc(sysdate)-3 ORDER BY 1,2;
--------------------------------------------------------------------------------------
3 32389 21-MAY-20 08.00.07.526 AM 21-MAY-20 09.00.08.973 AM
3 32390 21-MAY-20 09.00.08.973 AM 21-MAY-20 10.00.12.567 AM
--------------------------------------------------------------------------------------
2. creates a SQL tuning set object in the database
alter user SPA identified by oracle;
EXEC DBMS_SQLTUNE.CREATE_SQLSET ( -
SQLSET_NAME => 'TESTDB_SQLSET_0519', -
DESCRIPTION => '11204 to 19600 upgrade SQL Set Create at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), -
SQLSET_OWNER => 'SPA');
3. 从AWR中收集SQL语句（DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY）
收集快照begin_snap and end_snap之间的所有SQL语句（22359, 22368）
PARSING_SCHEMA_NAME 可选择收集的SCHEMA，也可排除某些SCHEMA
将快照间隔收集到的SQL填充到SQL tuning set（DBMS_SQLTUNE.LOAD_SQLSET）
DECLARE
SQLSET_CUR DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN SQLSET_CUR FOR
SELECT VALUE(P) FROM TABLE(
DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY( 22359, 22368,
'PARSING_SCHEMA_NAME NOT IN (''SYS'', ''SYSTEM'', ''SPA'')', NULL, NULL, NULL, NULL, 1, NULL, 'ALL')) P;
DBMS_SQLTUNE.LOAD_SQLSET(
SQLSET_NAME => 'TESTDB_SQLSET_0519',
SQLSET_OWNER => 'SPA',
POPULATE_CURSOR => SQLSET_CUR);
CLOSE SQLSET_CUR;
END;
/
4. 创建一个临时表，通过该临时表导入和导出SQL tuning sets
EXEC DBMS_SQLTUNE.CREATE_STGTAB_SQLSET ('TESTDB_STSTAB_0519', 'SPA', 'USERS');
5. 将一个或多个SQL tuning sets从它们在SYS schema中的位置复制到 CREATE_STGTAB_SQLSET 过程创建的临时表
EXEC DBMS_SQLTUNE.PACK_STGTAB_SQLSET ( -
SQLSET_NAME => 'TESTDB_SQLSET_0519', -
SQLSET_OWNER => 'SPA', -
STAGING_TABLE_NAME => 'TESTDB_STSTAB_0519', -
STAGING_SCHEMA_OWNER => 'SPA');
6. 查询当前用户可访问的SQL tuning sets
STATEMENT_COUNT，表示SQL tuning sets中的语句数
set line 300
col NAME for a25
col DESCRIPTION for a70
select NAME,STATEMENT_COUNT,DESCRIPTION from user_sqlset;
NAME STATEMENT_COUNT DESCRIPTION
------------------------- --------------- ----------------------------------------------------------------------
TESTDB_SQLSET_0519 40 11204 to 19600 upgrade SQL Set Create at : 2020-05-22 15:27:00
7. 导出临时表，并导入到目标端上
expdp SPA/oracle dumpfile=TESTDB_STSTAB_0519.dmp directory=PUMP_DIR tables=TESTDB_STSTAB_0519 logfile=expdp_TESTDB_STSTAB_0519.log exclude=STATISTICS cluster=N
scp /backup/expdp/TESTDB_STSTAB_0519.dmp oracle@192.168.1.197:/data/expdp
目标端创建用户
sqlplus system/oracle@TESTDBPDB
alter user SPA identified by oracle;
目标端导入
impdp SPA/oracle@TESTDBPDB dumpfile=TESTDB_STSTAB_0519.dmp directory=PUMP_DIR FULL=Y logfile=impdp_TESTDB_STSTAB_0519.log cluster=N
8. 将一个或多个SQL tuning sets从它们在临时表中的位置复制到SQL tuning sets schema中，从而使它们成为正确的SQL tuning sets。
EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (-
SQLSET_NAME => 'TESTDB_SQLSET_0519', -
SQLSET_OWNER => 'SPA', -
REPLACE => TRUE, -
STAGING_TABLE_NAME => 'TESTDB_STSTAB_0519', -
STAGING_SCHEMA_OWNER => 'SPA');
9. 创建了一个任务来处理和分析一个或多个SQL语句
VARIABLE SPA_TASK VARCHAR2(64);
EXEC :SPA_TASK := DBMS_SQLPA.CREATE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_0519', -
DESCRIPTION => '11.2.0.4 to 19.6.0.0 upgrade SPA Analysis task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'), -
SQLSET_NAME => 'TESTDB_SQLSET_0519', -
SQLSET_OWNER => 'SPA');
10. 执行以前创建的分析任务,读取源端SQL语句执行信息
EXECUTION_TYPE=CONVERT SQLSET 用于读取在SQL Tuning Set中捕获的统计信息。
当希望避免执行SQL语句时可以使用它，因为SQL Tuning Set中已经存在用于实验的有效数据。
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_0519', -
EXECUTION_NAME => 'EXEC_11204_0519', -
EXECUTION_TYPE => 'CONVERT SQLSET', -
EXECUTION_DESC => 'Convert 11204 SQLSET for SPA Task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
11. 执行以前创建的分析任务,在目标端执行SQL获取执行信息
EXECUTION_TYPE=TEST EXECUTE 测试执行每条SQL语句并收集其执行计划和执行统计信息
vi spa_exec_sql_TESTDB_0519.sh
echo "WARNING: spa_exec_sql Start @`date`"
sqlplus SPA/oracle@TESTDBPDB << EOF!
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_0519', -
EXECUTION_NAME => 'EXEC_19600_0519', -
EXECUTION_TYPE => 'TEST EXECUTE', -
EXECUTION_DESC => 'Execute SQL in 19600 for SPA Task at : '||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
exit
EOF!
echo "WARNING:spa_exec_sql OK @`date`"
nohup sh spa_exec_sql_TESTDB_0519.sh &
12. 生成对比报告
vi spa_html_TESTDB_0519.sql
--ELAPSED_TIME
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_0519', -
EXECUTION_NAME => 'COMPARE_ET_0519', -
EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
'COMPARISON_METRIC', 'ELAPSED_TIME', -
'EXECUTE_FULLDML', 'TRUE', -
'EXECUTION_NAME1','EXEC_11204_0519', -
'EXECUTION_NAME2','EXEC_19600_0519'), -
EXECUTION_DESC => 'Compare SQLs between 11204 and 19600 at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
--CPU_TIME
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_0519', -
EXECUTION_NAME => 'COMPARE_CT_0519', -
EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
'COMPARISON_METRIC', 'CPU_TIME', -
'EXECUTE_FULLDML', 'TRUE', -
'EXECUTION_NAME1','EXEC_11204_0519', -
'EXECUTION_NAME2','EXEC_19600_0519'), -
EXECUTION_DESC => 'Compare SQLs between 11204 and 19600 at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
--BUFFER_GETS
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_0519', -
EXECUTION_NAME => 'COMPARE_BG_0519', -
EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
'COMPARISON_METRIC', 'BUFFER_GETS', -
'EXECUTE_FULLDML', 'TRUE', -
'EXECUTION_NAME1','EXEC_11204_0519', -
'EXECUTION_NAME2','EXEC_19600_0519'), -
EXECUTION_DESC => 'Compare SQLs between 11204 and 19600 at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
--DISK_READS
EXEC DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( -
TASK_NAME => 'SPA_TASK_0519', -
EXECUTION_NAME => 'COMPARE_DR_0519', -
EXECUTION_TYPE => 'COMPARE PERFORMANCE', -
EXECUTION_PARAMS => DBMS_ADVISOR.ARGLIST( -
'COMPARISON_METRIC', 'DISK_READS', -
'EXECUTE_FULLDML', 'TRUE', -
'EXECUTION_NAME1','EXEC_11204_0519', -
'EXECUTION_NAME2','EXEC_19600_0519'), -
EXECUTION_DESC => 'Compare SQLs between 11204 and 19600 at :'||TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
ALTER SESSION SET EVENTS='31156 TRACE NAME CONTEXT FOREVER, LEVEL 0X400';
SET LINES 1111 PAGES 50000 LONG 1999999999 TRIM ON TRIMS ON SERVEROUTPUT ON SIZE UNLIMITED
SPOOL spa_TESTDB_elapsed_all_0519.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_0519','HTML','ALL','ALL',NULL,1000,'COMPARE_ET_0519')).GETCLOBVAL(0,0) FROM DUAL;
spool off
SPOOL spa_TESTDB_buffer_all_0519.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_0519','HTML','ALL','ALL',NULL,1000,'COMPARE_BG_0519')).GETCLOBVAL(0,0) FROM DUAL;
spool off
SPOOL spa_TESTDB_cputime_all_0519.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_0519','HTML','ALL','ALL',NULL,1000,'COMPARE_CT_0519')).GETCLOBVAL(0,0) FROM DUAL;
spool off
SPOOL spa_TESTDB_disk_all_0519.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_0519','HTML','ALL','ALL',NULL,1000,'COMPARE_DR_0519')).GETCLOBVAL(0,0) FROM DUAL;
spool off
SPOOL spa_TESTDB_error_0519.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_0519','HTML','ERRORS','ALL',NULL,1000,'COMPARE_ET_0519')).GETCLOBVAL(0,0) FROM DUAL;
spool off
SPOOL spa_TESTDB_unsupported_0519.html
SELECT XMLTYPE(DBMS_SQLPA.REPORT_ANALYSIS_TASK('SPA_TASK_0519','HTML','UNSUPPORTED','ALL',NULL,1000,'COMPARE_ET_0519')).GETCLOBVAL(0,0) FROM DUAL;
spool off
sqlplus SPA/oracle@TESTDBPDB
set echo on
@spa_html_TESTDB_0519.sql
最后修改时间：2020-08-17 10:30:54
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
