联系：QQ(5163721)
标题：SPA(SQL性能分析器)的使用-1-收集和迁移SQL Tuning Set
作者：Lunar©版权所有[文章允许转载,但必须以链接方式注明源地址,否则追究法律责任.]
SPA(SQL性能分析器)的使用-1-收集和迁移SQL Tuning Set
SPA(SQL性能分析器)的使用-2-Unpack STS和生成SPA Report
.
SPA(SQL Performance Analyzer , SQL 性能分析器)，是11g引入的新功能，主要用于预测潜在的更改对 SQL 查询工作量的性能影响。
一般有几种情况下，我们会建议做SPA:
1，OS版本发生变化
2，硬件发生变化
3，数据库版本的升级
4，实施某些优化建议
5, 收集统计信息
6，更改数据库参数
等等
.
SPA的主要实施步骤如下：
1, 在生产系统上捕捉SQL负载，并生成SQL Tuning Set；
2, 创建一个中转表，将SQL Tuning Set导入到中转表，导出中转表并传输到测试库；
3, 导入中转表，并解压中转表的数据到SQL Tuning Set；
4, 创建SPA任务，先生成10g的trail，然后在11g中再生成11g的trail；
5, 执行比较任务，再生成SPA报告；
6, 分析性能退化的SQL语句；
.
我这里的例子是，将一根数据库从10.2.0.1升级到11.2.0.4.
1，在源库创建spa用户：
create user LUNAR identified by LUNAR;
grant connect,resource,dba to LUNAR;
10:38:37 lunar@LUNAR>select username,default_tablespace,temporary_tablespace
10:41:41 2 from dba_users
10:41:41 3 where username in ('LUNAR','SPA')
10:41:41 4 order by 1,2;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE
------------------------------ ------------------------------ ------------------------------
LUNAR USERS TEMP
Elapsed: 00:00:00.27
10:41:41 lunar@LUNAR>
2，检查SYSAUX空间是否足够
09:26:38 sys@LUNAR>@ts Name TS Type All Size Max Size Free Size Max Free Pct. Free Max Free% ------------------------------ ------------ ---------- ---------- ---------- ---------- --------- --------- UNDOTBS1 UNDO 148,433 221,521 19,467 92,555 13 42 LUNAR_IDX PERMANENT 352,256 352,256 84,272 84,272 24 24 LUNAR_DAT PERMANENT 1,048,576 1,048,576 258,728 258,728 25 25 LUNAR_TESTS PERMANENT 251,904 251,904 139,424 139,424 55 55 LUNAR_TESTS_IDX PERMANENT 329,728 329,728 196,351 196,351 60 60 USERS PERMANENT 4,096 32,768 2,582 31,254 63 95 SYSAUX PERMANENT 4,096 32,768 2,786 31,458 68 96 SYSTEM PERMANENT 4,096 32,768 2,882 31,554 70 96 8 rows selected. Elapsed: 00:00:00.07 09:26:40 sys@LUNAR>
3，创建SQL优化器：
conn LUNAR/LUNAR
10:33:30 lunar@LUNAR>exec dbms_sqltune.create_sqlset('Lunar_11201STS_LUNAR');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.11
10:34:25 lunar@LUNAR>
4，往SQL优化其中，加载优化集
1). 从AWR快照中加载
11:31:55 lunar@LUNAR>select INSTANCE_NUMBER ,min(snap_id),max(snap_id) from dba_hist_snapshot group by INSTANCE_NUMBER;
INSTANCE_NUMBER MIN(SNAP_ID) MAX(SNAP_ID)
--------------- ------------ ------------
1 19355 19555
Elapsed: 00:00:00.01
11:32:12 lunar@LUNAR>
b).加载2个快照之间的所有查询（这一步大概执行了4分钟）
11:33:12 lunar@LUNAR>declare 11:33:14 2 own VARCHAR2(30) := 'LUNAR'; 11:33:14 3 bid NUMBER := '&begin_snap'; 11:33:14 4 eid NUMBER := '&end_snap'; 11:33:14 5 stsname VARCHAR2(30) :='Lunar_11201STS_LUNAR'; 11:33:14 6 sts_cur dbms_sqltune.sqlset_cursor; 11:33:14 7 begin 11:33:14 8 open sts_cur for 11:33:14 9 select value(P) from table(dbms_sqltune.select_workload_repository(bid,eid, null, null, null, null, null, 1, null, 'ALL')) P; 11:33:14 10 dbms_sqltune.load_sqlset(sqlset_name => stsname,populate_cursor => sts_cur,load_option => 'MERGE'); 11:33:14 11 end; 11:33:14 12 / Enter value for begin_snap: 19355 old 3: bid NUMBER := '&begin_snap'; new 3: bid NUMBER := '19355'; Enter value for end_snap: 19555 old 4: eid NUMBER := '&end_snap'; new 4: eid NUMBER := '19555'; PL/SQL procedure successfully completed. Elapsed: 00:03:07.05 11:36:29 lunar@LUNAR>
c) 验证创建的SQL优化集
10:52:58 lunar@LUNAR>select NAME,OWNER,CREATED,STATEMENT_COUNT, LAST_MODIFIED FROM DBA_SQLSET; NAME OWNER CREATED STATEMENT_COUNT LAST_MODIFIED ------------------------------ ------------------------------ ------------------- --------------- ------------------- Lunar_11201STS_LUNAR LUNAR 2015-04-18 10:34:25 921 2015-04-18 10:38:27 Elapsed: 00:00:00.06 10:53:03 lunar@LUNAR>
2). 如果需要，可以从AWR快照中加载指定sql_id和plan_hash_value的sql语句
12:06:31 lunar@LUNAR>SELECT sql_id, substr(sql_text, 1, 50) sql
12:06:32 2 FROM TABLE( DBMS_SQLTUNE.select_sqlset ('Lunar_11201STS_LUNAR'))
12:06:32 3 where sql_id in ('34xbj7bv7suyk','gxsfh4gm276d3');
SQL_ID SQL
------------- --------------------------------------------------
34xbj7bv7suyk UPDATE "LUNAR_PRD".MDRT_1472A$ set info= :1 where ro
gxsfh4gm276d3 update LUNARINFO t set TIME=:1, LUNARMARK=:2, LO
Elapsed: 00:00:01.14
12:06:34 lunar@LUNAR>
3). 从当前游标缓存中加载
DECLARE
cur sys_refcursor;
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('Lunar_11201STS_LUNAR', cur);
CLOSE cur;
END;
/
上述过程一般执行时间比较长，因此，通常放到后台执行。
这里我们看到加载的SQL明显增加了很多：
12:55:02 sys@LUNAR>select NAME,OWNER,CREATED,STATEMENT_COUNT FROM DBA_SQLSET; NAME OWNER CREATED STATEMENT_COUNT ------------------------------ ------------------------------ ------------------- --------------- Lunar_11201STS_LUNAR LUNAR 2015-04-18 11:31:55 41928 12:57:11 sys@LUNAR>
执行完上述所有操作后，我们就可以将这个SQL TUNING SET迁移到新的环境，进行分析，具体过程如下：
1，在新库中创建SQL优化器用户
create user LUNAR identified by LUNAR; grant connect,resource,dba to LUNAR;
2，检查SYSAUX空间是否足够
3，在源库上执行打包SQL TUNING SET的操作，然后exp/imp到新库上
[oracle@lunardb tmp]$ ss SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 18 23:22:26 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 23:22:26 sys@GPS>conn LUNAR/LUNAR Connected. 23:22:28 lunar@GPS>BEGIN 23:22:33 2 DBMS_SQLTUNE.create_stgtab_sqlset(table_name => 'SQLSET_TAB_LUNAR', 23:22:34 3 schema_name => 'LUNAR', 23:22:34 4 tablespace_name => 'USERS'); 23:22:34 5 END; 23:22:34 6 / PL/SQL procedure successfully completed. Elapsed: 00:00:01.32 23:22:36 lunar@GPS>
3，打包SQL TUNING SET的操作，然后exp/imp到新库上
conn LUNAR/LUNAR BEGIN DBMS_SQLTUNE.pack_stgtab_sqlset(sqlset_name => 'Lunar_11201STS_GPS', sqlset_owner => 'LUNAR', staging_table_name => 'SQLSET_TAB_LUNAR', staging_schema_owner => 'LUNAR'); END; /
执行过程中，我们可以监控一下：
[oracle@lunardb tmp]$ ss
SQL*Plus: Release 11.2.0.1.0 Production on Sat Apr 18 23:26:18 2015
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
23:26:18 sys@GPS>select count(*) from LUNAR.SQLSET_TAB_LUNAR;
COUNT(*)
----------
496641
Elapsed: 00:00:00.57
23:28:04 sys@GPS>
exp LUNAR/LUNAR tables=SQLSET_TAB_LUNAR file=/u01/oradata/tmp/exp_SQLSET_TAB_LUNAR.dmp log=/u01/oradata/tmp/exp_SQLSET_TAB_LUNAR.log FEEDBACK=1000 BUFFER=5000000
4，在新库上执行导入SQL TUNING SET的表（LUNAR.SQLSET_TAB_LUNAR）
imp LUNAR/LUNAR fromuser=LUNAR touser=LUNAR file=/u01/oradata/tmp/exp_SQLSET_TAB_LUNAR.dmp feedback=1000 log=/u01/oradata/tmp/imp_SQLSET_TAB_LUNAR.log BUFFER=5000000
