11g SPA SQL Performance Analyzer升級測試

1、建立測試表空間與使用者
SYS@TEST>create tablespace spa datafile '/u01/app/oracle/oradata/spa01.dbf' size 100M autoextend on;
Tablespace created.

SYS@TEST>create user spa identified by spa default tablespace spa;
User created.

SYS@TEST>grant connect,resource to spa;
Grant succeeded.

SYS@TEST>grant administer sql tuning set to spa;
Grant succeeded.

SYS@TEST>grant execute on dbms_sqltune to spa;
Grant succeeded.

SYS@TEST>grant select any dictionary to spa;
Grant succeeded.

--------------建立STS-----------------
SPA@TEST>exec dbms_sqltune.create_sqlset('jzh_test');
PL/SQL procedure successfully completed.

SPA@TEST>select name,OWNER,CREATED,STATEMENT_COUNT from dba_sqlset;

NAME                           OWNER                          CREATED   STATEMENT_COUNT
------------------------------ ------------------------------ --------- ---------------
jzh_test                       SPA                            10-AUG-15               0

--------------執行從遊標採集SQL-----------------
DECLARE
 cur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
 OPEN cur FOR
   SELECT VALUE(P)
     FROM table(
       DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
         'parsing_schema_name <> ''SYS''',
          NULL, NULL, NULL, NULL, 1, NULL,
         'ALL')) P;


DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'jzh_test',
                        populate_cursor => cur,
                        load_option =>'MERGE');
  close cur;
END;
/

從AWR快照中載入SQLset ROW到SQL TUNING SET
DECLARE
  cur sys_refcursor;
BEGIN
  OPEN cur FOR
    SELECT VALUE (P) 
    FROM table(dbms_sqltune.select_workload_repository(4146,4161)) P;
  DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'jzh_test',
                        populate_cursor => cur);
  CLOSE cur;
END;
/

2、建立一箇中轉表，將SQL Tuning Set匯入到中轉表，匯出中轉表並傳輸到測試庫；
-------------不要使用sys使用者建立stgtab表--------------

SPA@TEST>begin
  2    dbms_sqltune.create_stgtab_sqlset(table_name =>'SQLSET_TAB',
  3    schema_name => 'SPA',
  4    tablespace_name => 'SYSAUX');
  5    end;
  6    /

PL/SQL procedure successfully completed.
-------------將最佳化集打包到stgtab表裡面------------
SPA@TEST>begin
  2  dbms_sqltune.pack_stgtab_sqlset(sqlset_name=>'jzh_test',
  3  sqlset_owner=>'SPA',
  4  staging_table_name=>'SQLSET_TAB',
  5  staging_schema_owner=>'SPA');
  6  end;
  7  /
PL/SQL procedure successfully completed.

轉換成中轉表之後，我們可以再做一次去除重複的操作。當然，你也可以根據module來刪除一些不必要的遊標。
delete from SPA.SQLSET_TAB a where rowid !=(select max(rowid) from SQLSET_TAB b where 
a.FORCE_MATCHING_SIGNATURE=b.FORCE_MATCHING_SIGNATURE and a.FORCE_MATCHING_SIGNATURE<>0);

delete from SPA.SQLSET_TAB where MODULE='PL/SQL Developer';

sqlset_tab表結構
SYS@TEST>desc spa.sqlset_tab;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 OWNER                                              VARCHAR2(30)
 DESCRIPTION                                        VARCHAR2(256)
 SQL_ID                                             VARCHAR2(13)
 FORCE_MATCHING_SIGNATURE                           NUMBER
 SQL_TEXT                                           CLOB
 PARSING_SCHEMA_NAME                                VARCHAR2(30)
 BIND_DATA                                          RAW(2000)
 BIND_LIST                                          SPA.SQL_BIND_SET
 MODULE                                             VARCHAR2(48)
 ACTION                                             VARCHAR2(32)
 ELAPSED_TIME                                       NUMBER
 CPU_TIME                                           NUMBER
 BUFFER_GETS                                        NUMBER
 DISK_READS                                         NUMBER
 DIRECT_WRITES                                      NUMBER
 ROWS_PROCESSED                                     NUMBER
 FETCHES                                            NUMBER
 EXECUTIONS                                         NUMBER
 END_OF_FETCH_COUNT                                 NUMBER
 OPTIMIZER_COST                                     NUMBER
 OPTIMIZER_ENV                                      RAW(1000)
 PRIORITY                                           NUMBER
 COMMAND_TYPE                                       NUMBER
 FIRST_LOAD_TIME                                    VARCHAR2(19)
 STAT_PERIOD                                        NUMBER
 ACTIVE_STAT_PERIOD                                 NUMBER
 OTHER                                              CLOB
 PLAN_HASH_VALUE                                    NUMBER
 PLAN                                               SPA.SQL_PLAN_TABLE_TYPE
 SPARE1                                             NUMBER
 SPARE2                                             NUMBER
 SPARE3                                             BLOB
 SPARE4                                             CLOB

4、----------------匯出SPA使用者資料----------------
$ exp spa/spa file=spa.dmp
Export: Release 10.2.0.1.0 - Production on Mon Aug 10 08:41:56 2015
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SPA 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SPA 
About to export SPA's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SPA's tables via Conventional Path ...
. . exporting table                     SQLSET_TAB         32 rows exported
. . exporting table              SQLSET_TAB_CBINDS          0 rows exported
. . exporting table              SQLSET_TAB_CPLANS         77 rows exported
. . exporting table                              T      50433 rows exported
. . exporting table                             T1         16 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings

5、匯入中轉表，並解壓中轉表的資料到SQL Tuning Set；
這個步驟我們需要把我們匯出的中轉表的資料遷移到測試平臺，然後匯入資料，並再一次轉換成11g的SQL Tuning Set裡面；
-------------匯入資料到測試系統------------
$ imp spa/spa fromuser=spa touser=spa file=spa.dmp feedback=100
Import: Release 11.2.0.4.0 - Production on Mon Aug 10 16:48:36 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export file created by EXPORT:V10.02.01 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. . importing table                   "SQLSET_TAB"          32 rows imported
. . importing table            "SQLSET_TAB_CBINDS"          0 rows imported
. . importing table            "SQLSET_TAB_CPLANS"          77 rows imported
. . importing table                            "T"          50433 rows imported
. . importing table                           "T1"          16 rows imported
Import terminated successfully without warnings.

-------------unpack到sqlset-----------------
SPA@TEST>exec DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET('jzh_test','SPA',TRUE,'SQLSET_TAB','SPA');
PL/SQL procedure successfully completed.

6、建立SPA任務，先生成10g的trail，然後在11g中再生成11g的trail；
這個步驟一定要注意一點，先檢查測試庫上面有沒有dblink，如果有的話一定要刪除，免得連線到其他庫做一些不必要的動作，然後
就是在11g中生成11g的trail的時間可能比較慢，最好寫成指令碼放在後臺執行。

-------------新建SPA任務-----------
var tname varchar2(30);
var sname varchar2(30);
exec :sname := 'jzh_test';
exec :tname := '10g_11g_spa';
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname);
var tname varchar2(30);
var sname varchar2(30);
exec :sname := 'jzh_test';
exec :tname := '10g_11g_spa';
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname);
-------------生成10g的trail----------
SPA@TEST>begin
  2  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  3  task_name => '10g_11g_spa',
  4  execution_type => 'CONVERT SQLSET',
  5  execution_name => '10g_tril');
  6  end;
  7  /
PL/SQL procedure successfully completed.
-------------清空shared pool和buffer cache--------------
SPA@TEST>alter system flush shared_pool;
System altered.
SPA@TEST>alter system flush BUFFER_CACHE;
System altered.
-------------生成11g的trail------
SPA@TEST>begin
  2  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  3  task_name => '10g_11g_spa',
  4  execution_type => 'TEST EXECUTE',
  5  execution_name => '11g_trail');
  6  end;
  7  /
PL/SQL procedure successfully completed.

7、執行比較任務，再生成SPA報告；
我們可以從四個維度來進行對比,包括執行時間、CPU_TIME、Buffer_GET、Physical Read等.
-------------從elapsed_time來進行比較----------
SPA@TEST>begin
  2  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  3  task_name => '10g_11g_spa',
  4  execution_type => 'COMPARE PERFORMANCE',
  5  execution_name => 'Compare_elapsed_time',
  6  execution_params => dbms_advisor.arglist('execution_name1', '10g_tril', 'execution_name2', '11g_trail', 'comparison_metric', 'elapsed_time') );
  7  end;
  8  /

PL/SQL procedure successfully completed.

-------------從cpu_time來進行比較-------------
SPA@TEST>begin
  2  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  3  task_name => '10g_11g_spa',
  4  execution_type => 'COMPARE PERFORMANCE',
  5  execution_name => 'Compare_CPU_time',
  6  execution_params => dbms_advisor.arglist('execution_name1', '10g_tril', 'execution_name2', '11g_trail', 'comparison_metric', 'CPU_TIME') );
  7  end;
  8  /
PL/SQL procedure successfully completed.

-------------從buffer_gets來進行比較------------

SPA@TEST>begin
  2  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  3  task_name => '10g_11g_spa',
  4  execution_type => 'COMPARE PERFORMANCE',
  5  execution_name => 'Compare_BUFFER_GETS_time',
  6  execution_params => dbms_advisor.arglist('execution_name1', '10g_tril', 'execution_name2', '11g_trail', 'comparison_metric', 'BUFFER_GETS') );
  7  end;
  8  /

PL/SQL procedure successfully completed.

-------------從physical read來進行比較-------------
SPA@TEST>begin 
  2  DBMS_SQLPA.EXECUTE_ANALYSIS_TASK( 
  3  task_name => '10g_11g_spa', 
  4  execution_type => 'COMPARE PERFORMANCE', 
  5  execution_name => 'Compare_physical_reads', 
  6  execution_params => dbms_advisor.arglist('execution_name1', '10g_tril', 'execution_name2', '11g_trail', 'comparison_metric', 'disk_reads') ); 
  7  end; 
  8  /
PL/SQL procedure successfully completed.


SPA@TEST>set trimspool on
SPA@TEST>set trim on
SPA@TEST>set pages 0
SPA@TEST>set long 999999999
SPA@TEST>set linesize 1000
SPA@TEST>spool spa_report_elapsed_time.html
SELECT dbms_sqlpa.report_analysis_task('10g_11g_spa', 'HTML', 'ALL','ALL', 
top_sql=>300,execution_name=>'Compare_elapsed_time') FROM dual;
SPA@TEST>spool off;

SPA@TEST>spool spa_report_CPU_time.html
SELECT dbms_sqlpa.report_analysis_task('10g_11g_spa', 'HTML', 'ALL','ALL', 

top_sql=>300,execution_name=>'Compare_CPU_time') FROM dual;
SPA@TEST>spool off;

SPA@TEST>spool spa_report_buffer_time.html
SELECT dbms_sqlpa.report_analysis_task
('10g_11g_spa','HTML','ALL','ALL',top_sql=>300,execution_name=>'Compare_BUFFER_GETS_time') FROM dual;
SPA@TEST>spool off;

SPA@TEST>spool spa_report_errors.html
SELECT dbms_sqlpa.report_analysis_task('10g_11g_spa', 'HTML', 'errors','summary') FROM dual;
SPA@TEST>spool off;

SPA@TEST>spool spa_report_unsupport.html
SELECT dbms_sqlpa.report_analysis_task('10g_11g_spa', 'HTML', 'unsupported','all') FROM dual;
SPA@TEST>spool off;