About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc
全部博文（169）
分类： Oracle
2022-04-14 15:11:08
为了保障数据库升级后性能稳定，我们将采用Oracle性能分析器(SQL Performance Analyzer)来预测数据库的关键SQL在新版本上的性能情况，以便提前发现问题并做相关性能优化。
大型业务关键应用程序要在响应时间、吞吐量、运行时间和可用性方面提供特定服务级别的保证。对系统的任何更改（如升级数据库或修改配置）通常都需要进行全面的测试和验证，然后才能在生产系统中实施这些更改。在移到生产系统之前为了保证安全，数据库管理员(DBA) 必须让测试系统承受与生产环境中的工作量很近似的压力，以便分析系统级更改对整体SQL 性能的影响，并在在移到生产之前进行必要的优化。Oracle Database11g引入了SQL 性能分析器；使用该工具可以准确地预测系统更改对SQL 语句性能的影响。这种功能可向DBA 提供有关SQL 语句性能的详细信息，例如，执行前后的统计信息，提高或降低性能的语句。这样，你就在测试环境中先进行更改，以确定数据库升级是否会影响SQL性能。
SQL 性能分析器(SPA)可用于预测和防止会影响SQL 执行计划结构的任何数据库环境更改所带来的潜在性能问题。这些更改可以包括（但不限于）以下任何一种更改：
l 数据库升级
l 实施优化建议
l 更改方案
l 收集统计信息
l 更改数据库参数
l 更改操作系统和硬件
SQL 性能分析器(SPA)是11g的新功能，大致的框架如下：
先在老库上通过各种方法生成SQL优化集(SQL Tuning Set)，然后再将优化集导入到新库上（升级目标环境或完全一致的测试环境）,最后对每条SQL生成新老库上的性能对比报告，从这个报告便能发现SQL性能是否改变，执行计划有没有变化等。SPA分析流程如下图：
测试范围：
本次计划采集的两套库分别为XXA库和XXB库。因为是双节点的RAC环境，需要在两个实例里面均做采集。
测试目标：
需要尽可能多的捕捉生产环境的SQL语句，并对关键业务、AWR实现完整的覆盖。这里我们将计划对游标缓存和AWR历史资料库进行双重的采集。
ORACLE使用SQL Tuning Sets（STS）来存储和管理SQL负载，在捕获生产SQL负载阶段，主要任务就是尽可能多地采集游标中存储的SQL以及SQL相关信息，这些信息都会存储在STS中。STS包含SQL执行的上下文信息：
l SQL内容、绑定变量、parsing schema
l SQL执行计划、运行期统计信息如executions、buffer gets等
捕获生产SQL负载，主要采集游标缓存中的SQL，等30天采集周期完毕后，做SPA分析前，还需要采集45天AWR信息。ORACLE提供DBMS_SQLTUNE包进行采集。如下图所示：
对于SPA采集、需要先建立环境，然后采用游标采集、AWR采集等。
SPA采集环境包括创建SPA采集用户并授权、创建脚本部署目录、创建SQL Tuning Sets（STS）。
创建SPA用户并授权
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,B库每个节点都需要创建。
2） 创建sqlset,用户存储采集到的SQL Tuning Sets。
sts命名：sqlseta1_1 sqlset节点_tab编号,awr的为sqlseta_awr1 （每个sts存放20w SQL左右，不要超过25w条,超过20w条之后分给下一个sts，因为一个sts存放过多，后续pack,unpack sqlset会很慢，也可能报ORA-01555错误）
每个库需要针对不同节点，各创建20个以上。也可以先少创建点，之后超过20w条，再增加。
--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,最后按库合并。
为了防止采集很多重复的字面量SQL，可以先按照buffer_gets,disk_reads,executions等条件采集（防止采集慢等情况），等采集完成第一个sqlset（20w条左右），再采用剔除重复行过滤采集，并增加rownum条件。
1) 以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。
2) 第一个采集脚本可以采集多次，但是第一次采集完毕后，需要建立剔重表（每个节点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开始采集为例子，过滤采集主要使用死循环，不过加了一个控制表sqlseta1_control判断，如果status=0,则退出采集，否则继续采集。以下脚本实现：
1. 从sqlseta1_tab22开始
2. 控制表控制SPA是否采集
3. 选择spaqc_a1\spaqc_a\ sts_A1_0830等作为已经采集过的sqlset，不需要再采集，进行过滤。
4. 每个sqlset容纳20w条SQL，如果达到20w条，放到下1个sqlset中。
5. 因为循环采集，每次采集500条（才开始可以设5000条，后续SQL采集的差不多了，可以逐步减少），并且不采集insert into values…等
6. 可以增加其他条件，比如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较少，也可以全部取。
--gatherawr.sh
echo start `date`
sqlplus spa/spa <
DECLARE
mycur sys_refcursor;
BEGIN
open mycur for
select value(p)
from table(dbms_sqltune.select_workload_repository(40601,
41698,
'parsing_schema_name in (''DBAOPER1'',''ZWOPTADM'')'
)
) p;
dbms_sqltune.load_sqlset(sqlset_name => 'sqlseta_awr1',
sqlset_owner=>'SPA',
populate_cursor => mycur,
load_option => 'MERGE');
close mycur;
END;
/
exit
EOF
echo end `date`
exit
nohup ./gatherawr.sh >gatherawr.log 2>&1 &
