Oracle数据库11g 新特性SPA测试报告

ALLSTARS_ORACLE

Oracle数据库11g 新特性SPA测试报告

SPA介绍

大型业务关键应用程序要在响应时间、吞吐量、运行时间和可用性方面提供特定服务级别的保证。对系统的任何更改（如升级数据库或修改配置）通常都需要进行全面的测试和验证，然后才能在生产系统中实施这些更改。在移到生产系统之前为了保证安全，数据库管理员(DBA) 必须让测试系统承受与生产环境中的工作量很近似的压力，以便分析系统级更改对整体SQL性能的影响，并在在移到生产之前进行必要的优化。

Oracle Database 11g引入了SQL 性能分析器；使用该工具可以准确地预测系统更改对SQL 语句性能的影响。这种功能可向DBA 提供有关SQL 语句性能的详细信息，例如，执行前后的统计信息，提高或降低性能的语句。这样，你就在测试环境中先进行更改，以确定数据库升级是否会影响SQL性能。

SQL 性能分析器可用于预测和防止会影响SQL 执行计划结构的任何数据库环境更改所带来的潜在性能问题。这些更改可以包括（但不限于）以下任何一种更改：

l  数据库升级

l  实施优化建议

l  更改方案

l  收集统计信息

l  更改数据库参数

l  更改操作系统和硬件

SQL 性能分析器是11g的新功能，通过相关的配置可以应用在Oracle 10g/Oracle9i升级到11g的过程中，大致的框架如下：

先在10g/9i上通过各种方法生成SQL优化集，然后再将优化集导入到11g上,最后对每条SQL生成10g和11g上的对比报告，从这个报告便能发现SQL性能是否改善，执行计划有没有改变。

SPA实施步骤
1.在10g和11g上应用必须必须要的补丁

参考:RealApplication Testing Now Available for Earlier Releases (Doc ID 560977.1)

2.在10g源数据库中创建SQL优化集，并将[测试]语句加载到优化集中。

参考:HOW TO LOADQUERIES INTO A SQL TUNING SET (Doc ID 1271343.1)

1).创建SQL优化集

execdbms_sqltune.create_sqlset('my10gsts_jiesuan');

新建spa用户（不要在sys用户下），建立my10gsts_jiesuan

2).从当前游标缓存中加载

a) 加载指定sql_id的语句

DECLARE

cur sys_refcursor;

BEGIN

open cur for

select value(p) fromtable(dbms_sqltune.select_cursor_cache('sql_id = ''fgtq4z4vb0xx5''')) p;

dbms_sqltune.load_sqlset('my10gsts_jiesuan',cur);

close cur;

END;

/

b) 加载逻辑读大于1000的语句

DECLARE

cur sys_refcursor;

BEGIN

open cur for

select value(p) fromtable(dbms_sqltune.select_cursor_cache('sql_text like ''%querystring%'' andbuffer_gets > 1000')) p;

dbms_sqltune.load_sqlset('my10gsts_jiesuan',cur);

close cur;

END;

/

说明 :

"basic_filter"是根据SQLSET_ROW属性从游标缓存中过滤SQL的条件。

如果basic_filter在调用时没有被设置，那么子程序只捕捉

CREATE TABLE, INSERT, SELECT, UPDATE, DELETE,and MERGE等类型的语句.

CREATE TYPEsqlset_row AS object (

sql_id VARCHAR(13),

force_matching_signatureNUMBER,

sql_text CLOB,

object_listsql_objects,

bind_data RAW(2000),

parsing_schema_nameVARCHAR2(30),

module VARCHAR2(48),

action VARCHAR2(32),

elapsed_time NUMBER,

cpu_time NUMBER,

buffer_gets NUMBER,

disk_reads NUMBER,

direct_writesNUMBER,

rows_processedNUMBER,

fetches NUMBER,

executions NUMBER,

end_of_fetch_countNUMBER,

optimizer_costNUMBER,

optimizer_envRAW(2000),

priority NUMBER,

command_type NUMBER,

first_load_timeVARCHAR2(19),

stat_period NUMBER,

active_stat_periodNUMBER,

other CLOB,

plan_hash_valueNUMBER,

sql_plansql_plan_table_type,

bind_list sql_binds);

3).从AWR快照中加载

a).找出需要的快照

select snap_id,begin_interval_time, end_interval_time from dba_hist_snapshot order by 1;

由于数据force open是在2012-8-28号左右打开的，故生产系统的awr是在这之前的

select snap_id, begin_interval_time, end_interval_time fromdba_hist_snapshot where  to_char(begin_interval_time,'YYYYMMDD')<'20120828'order by 1;

ALLSTARS_ORACLE

b).加载2个快照之间的所有查询


declare
own VARCHAR2(30) := 'SYS';
bid NUMBER := '&begin_snap';
eid NUMBER := '&end_snap';
stsname VARCHAR2(30) :='MY10GSTS_JIESUAN';
sts_cur dbms_sqltune.sqlset_cursor;
begin
open sts_cur for
select value(P) from table(dbms_sqltune.select_workload_repository(bid,eid, null, null, null, null, null, 1, null, 'ALL')) P;
dbms_sqltune.load_sqlset(sqlset_name => stsname,populate_cursor => sts_cur,load_option => 'MERGE');
end;
/

我们取awr里全部的历史数据
19037
     19037 24-AUG-12 03.00.19.312 AM                                                   24-AUG-12 04.00.31.582 AM
     19037 24-AUG-12 03.00.19.407 AM                                                   24-AUG-12 04.00.31.660 AM
18990
     18990 22-AUG-12 12.00.18.146 AM                                                   22-AUG-12 01.00.01.108 AM
     18990 22-AUG-12 12.00.18.220 AM                                                   22-AUG-12 01.00.01.030 AM：
declare
own VARCHAR2(30) := 'SPA';
bid NUMBER := '&begin_snap';
eid NUMBER := '&end_snap';
stsname VARCHAR2(30) :='my10gsts_jiesuan';-----区分大小写
sts_cur dbms_sqltune.sqlset_cursor;
begin
open sts_cur for
select value(P) from table(dbms_sqltune.select_workload_repository(bid,eid, null, null, null, null, null, 1, null, 'ALL')) P;
dbms_sqltune.load_sqlset(sqlset_name => stsname,populate_cursor => sts_cur,load_option => 'MERGE');
end;
/


c) 验证创建的SQL优化集

## 查询载入的子句数 等SQLSET的信息
select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;
SQL> select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET;

NAME                           OWNER                          CREATED   STATEMENT_COUNT
------------------------------ ------------------------------ --------- ---------------
my10gsts_jiesuan               SPA                            29-AUG-12            1031
exec dbms_sqltune.DROP_SQLSET(SQLSET_NAME=>'my10gsts_jiesuan',SQLSET_OWNER=>'ZS');

exec dbms_sqltune.DROP_SQLSET(SQLSET_NAME=>'my10gsts_jiesuan',SQLSET_OWNER=>'SYS');
exec dbms_sqltune.DROP_SQLSET(SQLSET_NAME=>'teststs',SQLSET_OWNER=>'SYS');
exec dbms_sqltune.DROP_SQLSET(SQLSET_NAME=>'teststs',SQLSET_OWNER=>'ZS');


c) 加载指定sql_id和plan_hash_value的sql语句

DECLARE
cur sys_refcursor;
BEGIN
open cur for
select value(p) from table(dbms_sqltune.select_workload_repository(begin_snap => 2245, end_snap => 2248, basic_filter => 'sql_id = ''fgtq4z4vb0xx5'' and plan_hash_value = 431456802')) p;
dbms_sqltune.load_sqlset('my10gsts_jiesuan', cur);
close cur;
END;
/

4).从AWR基线中加载


a) 查找需要加载的AWR基线

select baseline_name, start_snap_id, end_snap_id from dba_hist_baseline;


b) 从AWR基线中加载查询

DECLARE
cur sys_refcursor;
BEGIN
open cur for
select value(p) from table(dbms_sqltune.select_workload_repository('MY_BASELINE')) p;
dbms_sqltune.load_sqlset('my10gsts_jiesuan', cur);
close cur;
END;
/

5).从另外一个SQL优化集加载


a) 查询需要加载的SQL优化集

select name, owner, statement_count from dba_sqlset;


b) 从SQL优化集中加载查询语句

DECLARE
cur sys_refcursor;
BEGIN
open cur for
select value(p) from table(dbms_sqltune.select_sqlset(sqlset_name => 'HR_STS', sqlset_owner => 'HR', basic_filter => 'sql_text like ''%querystring%''')) p;
dbms_sqltune.load_sqlset('my10gsts_jiesuan', cur);
close cur;
END;
/


6).从10046跟踪文件加载(11g 新特性)


a) 在同一个数据库中加载进SQL优化集


(1). 数据库中创建包含trace文件的目录对象。

create directory my_dir as '/home/oracle/trace';


(2). 加载查询语句

DECLARE
cur sys_refcursor;
BEGIN
open cur for
select value(p) from table(dbms_sqltune.select_sql_trace(directory=>'MY_DIR', file_name=>'%.trc')) p;
dbms_sqltune.load_sqlset('my10gsts_jiesuan', cur);
close cur;
END;
/


b) 在另外一个数据库加载进SQL优化集


(1). 从产生trace文件的数据库上创建映射表

create table mapping as
select object_id id, owner, substr(object_name, 1, 30) name
from dba_objects
union all
select user_id id, username owner, null name
from dba_users;


(2). 将trace文件拷贝到目标端的文件系统中，在数据库中创建包含trace文件的目录对象,
并在目标数据库上导入映射表。

create directory my_dir as '/home/oracle/trace';


(3). 当加载查询中指定映射表

DECLARE
cur sys_refcursor;
BEGIN
open cur for
select value(p) from table(dbms_sqltune.select_sql_trace(directory=>'MY_DIR', file_name=>'%.trc', mapping_table_name=> 'MAPPING', mapping_table_owner=> 'HR')) p;
dbms_sqltune.load_sqlset('my10gsts_jiesuan', cur);
close cur;
END;
/



3.将SQL优化集移动至11g数据库

参考:How To Move SQL Tuning Set From One Database to Other (Doc ID 751068.1)

(1).在SYS用户下创建 stgtab sqlset_tab表

BEGIN
DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET_TAB',
schema_name => 'SYS',
tablespace_name => 'SYSAUX');
END;
/
SQL> ----不能使用SYS用户，使用新建用户
SQL> BEGIN
  2  DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET_TAB',
schema_name => 'SPA',
tablespace_name => 'SYSAUX');
END;
/
  3    4    5    6
PL/SQL procedure successfully completed.
(2).将my10gsts_jiesuan SQL优化集打包到表stgtab(SYS用户运行)
BEGIN
DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'test_set',
sqlset_owner => 'SYS',
staging_table_name => 'SQLSET_TAB',
staging_schema_owner => 'TEST');
END;
/
SQL> BEGIN
  2  DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'my10gsts_jiesuan',
  3  sqlset_owner => 'SPA',
  4  staging_table_name => 'SQLSET_TAB',
  5  staging_schema_owner => 'SPA');
  6  END;
  7  /


PL/SQL procedure successfully completed.
将表里的数据exp到文件系统，检查NLS_LANG设置
$ exp system/oracle  tables=spa.SQLSET_TAB file=/u03/soft/spa/SQLSET_TAB.dmp log=/u03/soft/spa/SQLSET_TAB.log FEEDBACK=100

Export: Release 10.2.0.3.0 - Production on Wed Aug 29 10:44:33 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to SYS
. . exporting table                     SQLSET_TAB
...........
                                                         1109 rows exported
. . exporting table              SQLSET_TAB_CBINDS
                                                            0 rows exported
. . exporting table              SQLSET_TAB_CPLANS
...........................................................................
..........................
                                                        10186 rows exported
Export terminated successfully without warnings.

(3).使用IMPORT将SQLSET_TAB表导入到11gRAT 资料数据库中
imp到11 RAT环境
imp****************
sqlplus "/as sysdba"
sql>
exec DBMS_SQLTUNE.create_sqlset(sqlset_name => 'my10gsts_jiesuan');

新建和生产库一样的用户，不要使用SYS用户
conn spa/spa
SQL> exec DBMS_SQLTUNE.create_sqlset(sqlset_name => 'my10gsts_jiesuan');

PL/SQL procedure successfully completed.

(4).解包my10gsts_jiesuan的SQL优化集

BEGIN
DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => 'my10gsts_jiesuan',
sqlset_owner => 'SYS',
replace => TRUE,
staging_table_name => 'SQLSET_TAB',
staging_schema_owner => 'SYS');
END;
/

BEGIN
DBMS_SQLTUNE.unpack_stgtab_sqlset(sqlset_name => 'my10gsts_jiesuan',
sqlset_owner => 'SPA',
replace => TRUE,
staging_table_name => 'SQLSET_TAB',
staging_schema_owner => 'SPA');
END;
/

4.11g RAT资料数据库中创建指向10g 的public database link

create public database link &dblink_name connect to &username identified by &password using 'connect_string';

create public database link link_spa connect to system identified by oracle using 'spa';
5.在RAT资料数据库中创建SPA任务

exec DBMS_SQLPA.DROP_ANALYSIS_TASK('SPA_TEST');

var tname varchar2(30);
var sname varchar2(30);
exec :sname := 'my10gsts_jiesuan';
exec :tname := 'SPA_TEST';
exec :tname := DBMS_SQLPA.CREATE_ANALYSIS_TASK(sqlset_name => :sname, task_name => :tname);

ALLSTARS_ORACLE

6.从STS生成10g的SPA Trial
1).测试执行方式
说明：在执行之前，需要flush shared pool和 buffer cache.
csracdb1:/u01/oracle/app/oracle/admin/zmjs/bdump> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Aug 29 15:14:28 2012

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> alter system flush shared_pool;

System altered.

SQL> alter system flush BUFFER_CACHE;

System altered.
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST',
execution_type => 'TEST EXECUTE',
execution_name => 'exec_10g',
execution_params => dbms_advisor.arglist('DATABASE_LINK', '&dblink_name'));
end;
/
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST',
execution_type => 'TEST EXECUTE',
execution_name => 'exec_10g',
execution_params => dbms_advisor.arglist('DATABASE_LINK', 'LINK_SPA'));
end;
/
因为需要到数据库实际执行，所以这个时间很长（平均半个小时100条）
2).STS转化方式
begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST',
execution_type => 'CONVERT SQLSET',
execution_name => 'convert_10g');
end;
/

7.在11g中测试执行，从性能数据生成SPA trial
说明：在执行之前，需要flush shared pool和 buffer cache.

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST',
execution_type => 'TEST EXECUTE',
execution_name => 'exec_11g');
end;
/

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
task_name => 'SPA_TEST',
execution_type => 'TEST EXECUTE',
execution_params => dbms_advisor.arglist('DATABASE_LINK', 'LINK_SPA'),
execution_name => 'exec_11g');
end;
/

8.执行比较报告
1).测试执行方式
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_10g', 'execution_name2', 'exec_11g', 'comparison_metric', 'elapsed_time') );
end;
/
2).STS转化方式
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;
/
v$advisor_metric
通过此视图找出需要比较的comparison_metric

9.生成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') FROM dual;
spool off;

说明：请注意参考<<SQL Performance Analyzer Report Shows Only 100 SQL Statements (Doc ID 795185.1)>>
SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK('pa_high_exec_withoutbinds' , 'HTML', 'CHANGED_PLANS','ALL',top_sql=>1700) FROM DUAL;