Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" DUMPFILE=STGTAB_SQLSET.dmp DIRECTORY=DATA_PUMP_DIR TABLES=ITSM.STGTAB_SQLSET REMAP_SCHEMA=ITSM:SYSTEM
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SYSTEM"."STGTAB_SQLSET_CPLANS" 481.9 KB 934 rows
. . imported "SYSTEM"."STGTAB_SQLSET" 152.6 KB 61 rows
. . imported "SYSTEM"."STGTAB_SQLSET_CBINDS" 9.523 KB 0 rows
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at 15:17:02
1.7. 在测试库解压中转表的数据到STS
SQL> EXEC DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET(sqlset_name=>'sql_test',replace=>true ,staging_table_name=>'STGTAB_SQLSET',staging_schema_owner => 'SCOTT');
PL/SQL procedure successfully completed.
2. 创建SPA任务
2.1. 在测试库创建一个SPA任务
SQL> variable sts_task VARCHAR2(64);
SQL> exec :sts_task:=DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name=>'sql_test',task_name=>'spa_task1');
/
PL/SQL procedure successfully completed
3. 分析更改前SQL工作负载
现实环境中，通常源库和测试库的版本至少差一个版本，比如源库可能是10.2版本，而测试库版本是11G,如果源库的版本是10G，需要在测试库将参数：optimizer_features_enable 设置为10.2.0,将此参数设置为此10.2.0后,生成的性能数据就是升级前的数据了.
SQL>
alter SYSTEM SET optimizer_features_enable = '10.2.0.3';
SQL>
exec DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'spa_task1',execution_type=>'test execute',execution_name=>'before_change');
PL/SQL procedure successfully completed.
一定注意黄色部分,test 和 execute 之前不是下划线,而是空格.
4. 分析升级后的sql工作负载
SQL> alter SYSTEM SET optimizer_features_enable = '11.2.0.3';
SQL>
exec DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'spa_task1',execution_type=>'test execute',execution_name=>'after_change');
PL/SQL procedure successfully completed.
5. 比较SQL性能
5.1. 为了比较升级前和升级后SQL性能,需要第三次执行EXECUTE_ANALYSIS_TASK过程.
SQL> exec DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(task_name=>'spa_task1',execution_type=>'compare performance');
PL/SQL procedure successfully completed.
5.2. 生成SPA报表
SQL> variable report1 clob;
SQL>
exec :report1 := DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name=>'spa_task1',type=>'text',level=>'typical',section=>'summary');
PL/SQL procedure successfully completed.
SQL> SQL> set long 100000 longchunksize 100000 linesize 120
SQL> print :report1
REPORT1
------------------------------------------------------------------------------------------------------------------------
General Information
---------------------------------------------------------------------------------------------
Task Information: Workload Information:
--------------------------------------------- ---------------------------------------------
Task Name : spa_task1 SQL Tuning Set Name : sql_test
Task Owner : SYS SQL Tuning Set Owner : SYS
Description : Total SQL Statement Count : 73
Execution Information:
---------------------------------------------------------------------------------------------
Execution Name : EXEC_1642 Started : 01/22/2014 16:27:23
Execution Type : COMPARE PERFORMANCE Last Updated : 01/22/2014 16:27:23
Description : Global Time Limit : UNLIMITED
Scope : COMPREHENSIVE Per-SQL Time Limit : UNUSED
Status : COMPLETED Number of Errors : 0
Number of Unsupported SQL : 11
Analysis Information:
---------------------------------------------------------------------------------------------
Before Change Execution: After Change Execution:
---------------------------------