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
全部博文（166）
- cbo（54）
- tuning&performan（32）
- concepts&archite（4）
- troubleshooting（5）
- new feature（7）
- sql（50）
- pl/sql（8）
- miscellaneous（3）
- mysql（3）
- 未分配的博文（0）
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2022-04-14 15:11:08
1.sql performance analyzer（spa）简介
为了保障数据库升级后性能稳定，我们将采用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分析流程如下图：
2.spa测试范围和目标
测试范围：
本次计划采集的两套库分别为xxa库和xxb库。因为是双节点的rac环境，需要在两个实例里面均做采集。
测试目标：
需要尽可能多的捕捉生产环境的sql语句，并对关键业务、awr实现完整的覆盖。这里我们将计划对游标缓存和awr历史资料库进行双重的采集。
3.spa采集：捕获生产sql负载
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包进行采集。如下图所示：
3.1 spa采集准备工作
对于spa采集、需要先建立环境，然后采用游标采集、awr采集等。
3.2 建立spa采集环境
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库方式一致，命名有差别
3.3 spa采集目标用户确定
首先确定需要采集的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
3.4 spa不间断采集方案
由于库中的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条件。
3.4.1第一个sqlset采集脚本
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;
/
3.4.2 过滤不间断采集方案
等第一个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;
/
3.5采集awr中的sql
采集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 &