一. SQL Performance Analyzer（SPA）简介
二. SPA测试范围和目标
测试范围：
本次计划采集的两套库分别为XXA库和XXB库。因为是双节点的RAC环境，需要在两个实例里面均做采集。
测试目标：
需要尽可能多的捕捉生产环境的SQL语句，并对关键业务、AWR实现完整的覆盖。这里我们将计划对游标缓存和AWR历史资料库进行双重的采集。
三. SPA采集：捕获生产SQL负载
对于SPA采集、需要先建立环境，然后采用游标采集、AWR采集等。
SPA采集环境包括创建SPA采集用户并授权、创建脚本部署目录、创建SQL Tuning Sets（STS）。
create user spa identified by spa default tablespace sysaux;
grant connect ,resource to spa;
grant ADMINISTER SQL TUNING SET to spa;
grant execute on dbms_sqltune to spa;
grant select any dictionary to spa;
需要在A,B库分别创建SPA用户。
1）创建spa目录，用户部署spa采集脚本
cd /oracle
mkdir spa
--a库
exec dbms_sqltune.create_sqlset(sqlseta1_tab1,sqlset_owner=>SPA);
exec dbms_sqltune.create_sqlset(sqlseta1_tab2,sqlset_owner=>SPA);
…
exec dbms_sqltune.create_sqlset(sqlseta2_tab1,sqlset_owner=>SPA);
exec dbms_sqltune.create_sqlset(sqlseta2_tab2,sqlset_owner=>SPA);
…
--b库与a库方式一致，命名有差别
首先确定需要采集的SPA用户，确认好后，查询下v$sql中对应的sql_id分布，对于sql_id占5W的+的要多带带采集，否则很慢，如下DBAOPER1用户需要多带带采集，其它用户放在一起采集。
--a库 26个用户
select PARSING_SCHEMA_NAME,count(*)
from v$sql where PARSING_SCHEMA_NAME in (…省略
)
group by PARSING_SCHEMA_NAME;
--b库 28个用户
select PARSING_SCHEMA_NAME,count(distinct sql_id),count(*)
from v$sql where PARSING_SCHEMA_NAME in (…省略
)
group by PARSING_SCHEMA_NAME;
--a库26个用户。集中在BILLING,DBAOPER1用户下，分布如下：
PARSING_SCHEMA_NAME COUNT(*)
------------------------------------------- ----------
ADMTEST 6
BILLING 6224
ADMTEST1 2
MONITORX 3
BIDB1 15
ZWOPTADM 8
TESAGENT 1
DBAOPER1 156870 --10w条+
TEST 359
--b库28个用户，和a库分布一样
PARSING_SCHEMA_NAME COUNT(DISTINCTSQL_ID) COUNT(*)
------------------------------------------ ------------------------------------ ------------
BILLING 7473 7477
ADMTEST 9 9
ZWOPTADM 8 8
DBAOPER1 156505 156746
DBCMOPR 80 80
MONITORX 3 3
BIDB1 69 69
AUDITOR 2 2
TESAGENT 2 2
TEST 355 355
由于库中的v$sql存放的数据量较大，有15w+,多的时候超过20w条，直接采集耗时长，而且重复类型的SQL_ID较多（字面量不同），游标采集需要可以最大限度的帮助我们采集到更多的SQL语句。为了保证采集到更多的SQL，我们需要进行一个长期的捕捉，采用不间断捕获。采集的过程中可能因为有literal sql，这会导致我们的SQLSET的结果集非常大，因为相关的表涉及到一些CLOB字段，如果结果集过大的话，将导致转换成中间表非常的慢。转换到一半因为UNDO不够大，还还会导致出现ORA-01555错误。为了解决这个问题，建议在采集的过程中实施过滤。对于采集脚本可以编写shell脚本后台执行，每个节点均需采集，设置不同的sqlset,最后按库合并。
以a库节点1为例，采集数据放到sqlseta1_tab1中。
主要通过buffer_gets,elapsed_time等构造查询条件，并且通过rownum<5000限制在5000条，5000条是个大概数据，对v$sql有20w条sql的情况，查询5000条耗时2-5分钟，第一次采集约耗时1小时左右。对于buffer_gets,elapsed_time,disk_reads等条件的选择，可以通过v$sql查看max(buffer_gets),max(elapsed_time),min(buffer_gets),min(elapsed_time)以及求平均avg,然后取平均值和最大值测试，一般条件选出的数据在5000行之内，如果超过5000行，再加其他条件拆分，比如executions。
第一个采集脚本可以采集多次，但是第一次采集完毕后，需要建立剔重表
（每个节点1个）：
create table spa.spaqc_a1 as select distinct FORCE_MATCHING_SIGNATURE from DBA_SQLSET_STATEMENTS;
delete from spa.spaqc_a1 where FORCE_MATCHING_SIGNATURE=0;
之后采集脚本条件改为：
and buffer_gets<=7 and elapsed_time>2000 and elapsed_time<=5000 and rownum<5000
and FORCE_MATCHING_SIGNATURE not in (select FORCE_MATCHING_SIGNATURE from spa.spaqc_a1)
后面如果还需要采集，则可以在脚本中增加：
execute immediate truncate table spa.spaqc_a1 ;
insert/*+append*/ into spa.spaqc_a1 select distinct FORCE_MATCHING_SIGNATURE from DBA_SQLSET_STATEMENTS;
直到第一个SQLSET采集完毕，20w条左右。
以下sql脚本是：sqlseta1_tab1.sql，可以编写对应shell脚本调度，放到crontab中。
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (DBAOPER1) and buffer_gets>150 and rownum<5000,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => sqlseta1_tab1,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 1:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
/
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (DBAOPER1) and buffer_gets>50 and buffer_gets<=150 and rownum<5000,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => sqlseta1_tab1,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 2:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
/
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (DBAOPER1) and buffer_gets>40 and buffer_gets<=50 and rownum<5000,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => sqlseta1_tab1,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 3:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
/
…此处省略很多
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (DBAOPER1) and buffer_gets<=7 and elapsed_time>700 and elapsed_time<=1000 and rownum<5000,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => sqlseta1_tab1,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 19:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
/
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (DBAOPER1) and buffer_gets<=7 and elapsed_time<=700 and rownum<5000,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => sqlseta1_tab1,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 20:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
/
--非DBAOPER1用户采集，因为比较少，<5w,放到一起
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (AAAA,BILLING…此处省略
) and rownum<5000,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => sqlseta1_tab1,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 21:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
/
等第一个sqlset采集完毕后，采用过滤采集，需要建立控制表，控制SPA采集是否启动。并且采用循环采集。每5分钟执行一次采集。
0:exit,1:running
create table spa.sqlseta1_control(status number);
insert into spa.sqlseta1_control values(1);
grant execute on dbms_lock to spa;
从sqlseta1_tab22开始
控制表控制SPA是否采集
选择spaqc_a1spaqc_a sts_A1_0830等作为已经采集过的sqlset，不需要再采集，进行过滤。
每个sqlset容纳20w条SQL，如果达到20w条，放到下1个sqlset中。
因为循环采集，每次采集500条（才开始可以设5000条，后续SQL采集的差不多了，可以逐步减少），并且不采集insert into values…等
可以增加其他条件，比如MODULE等，排除SQL*PLUS,PL/SQL DEVELOPER。。。
--sqlseta1_tab22
declare
v_status number;
v_cnt number;
v_sqlset_name varchar2(100) :=sqlseta1_tab22;
begin
loop
select nvl(max(status),0) into v_status from spa.sqlseta1_control;
if v_status = 0 then
exit;
end if;
execute immediate truncate table spa.spaqc_a1 ;
insert/*+append*/ into spa.spaqc_a1 select distinct FORCE_MATCHING_SIGNATURE from DBA_SQLSET_STATEMENTS;
commit;
insert/*+append*/ into spa.spaqc_a1 select distinct FORCE_MATCHING_SIGNATURE from spa.spaqc_a a where not exists(select 1 from spa.spaqc_a1 b where a.FORCE_MATCHING_SIGNATURE=b.FORCE_MATCHING_SIGNATURE);
commit;
insert/*+append*/ into spa.spaqc_a1 select distinct FORCE_MATCHING_SIGNATURE from spa.STS_TAB_A_TEST1;
commit;
insert/*+append*/ into spa.spaqc_a1 select distinct FORCE_MATCHING_SIGNATURE from spa.sts_A1_0830;
commit;
delete from spa.spaqc_a1 where FORCE_MATCHING_SIGNATURE=0;
commit;
select statement_count into v_cnt from dba_sqlset where name=sqlseta1_tab22;
if v_cnt > 200000 then
v_sqlset_name := sqlseta1_tab23;
select statement_count into v_cnt from dba_sqlset where name=sqlseta1_tab23;
if v_cnt > 200000 then
v_sqlset_name := sqlseta1_tab24;
select statement_count into v_cnt from dba_sqlset where name=sqlseta1_tab24;
if v_cnt > 200000 then
v_sqlset_name := sqlseta1_tab25;
select statement_count into v_cnt from dba_sqlset where name=sqlseta1_tab25;
if v_cnt > 200000 then
v_sqlset_name := sqlseta1_tab26;
select statement_count into v_cnt from dba_sqlset where name=sqlseta1_tab26;
if v_cnt > 200000 then
v_sqlset_name := sqlseta1_tab27;
end if;
end if;
end if;
end if;
end if;
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (DBAOPER1) and rownum<500
and FORCE_MATCHING_SIGNATURE not in (select FORCE_MATCHING_SIGNATURE from spa.spaqc_a1)
and FORCE_MATCHING_SIGNATURE IS NOT NULL
and upper(sql_text) not like %INSERT%INTO%VALUES%,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => v_sqlset_name,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 1:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
DECLARE
mycur DBMS_SQLTUNE.SQLSET_CURSOR;
BEGIN
OPEN mycur FOR
SELECT value(P)
FROM TABLE(dbms_sqltune.select_cursor_cache(parsing_schema_name in (AAAA,BILLING,ADMTEST1,ADMTEST…此处省略
) and rownum<500
and FORCE_MATCHING_SIGNATURE not in (select FORCE_MATCHING_SIGNATURE from spa.spaqc_a1)
and FORCE_MATCHING_SIGNATURE IS NOT NULL
and upper(sql_text) not like %INSERT%INTO%VALUES%,
NULL,
NULL,
NULL,
NULL,
1,
NULL,
ALL)) p;
dbms_sqltune.load_sqlset(sqlset_name => v_sqlset_name,
sqlset_owner=>SPA,
populate_cursor => mycur,
load_option => MERGE);
CLOSE mycur;
dbms_output.put_line(step 2:||to_char(SYSDATE,yyyymmdd hh24:mi:ss));
END;
sys.dbms_lock.sleep(300);
end loop;
end;
/
采集45天前到当前日期的数据，只需要根据dba_hist_snapshot查询开始和结束snap_id即可。只关注DBAOPER1,ZWOPTADM两个用户，其它用户SQL较少，也可以全部取。
