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;
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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10271187/viewspace-1767435/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10271187/viewspace-1767435/