数据库升级性能保障利器sql performance analyzer-凯发app官方网站

spa采集到的sqlset约500余万条sql，需要生成buffer gets、cpu time、elapsed_time报告，此报告只列出top 300的记录，另外还需要生成error和unsupport报告。

生成报告之前需要做的准备工作如下：

1）  构造与升级目标库相同的测试环境。

2）  将10g生产库采集到的sql负载（sts）传输到测试库中，包括pack、传输、unpack等过程。

3）  将数据库中的表尽可能有生产保持一致地传输到测试库中。

4）  统计信息处理：

要执行spa分析升级前后性能，需要导入10g统计信息，对于统计信息的处理有3种：

1）  导入10g统计信息

2）  导入10g统计信息后，做修复处理，比如使用method_opt=>’for all columns size repeat’等，原10g未收集统计信息的表确认后排出收集。

3）  直接收集11g统计信息（不推荐使用，因为直方图不好确定）

一般1）和2）是常用的升级统计信息处理方式，如果10g统计信息有大量表存在问题，推荐使用2），所以在做spa分析前，可以对统计信息完整性进行检查（未收集表、收集但是丢失了如列、索引、分区等统计信息、统计信息过旧等），从而确定最佳方案。

spa报告生成流程如下：

1） 捕获生产环境sql tuning sets,这个在第3节：spa采集中已经说明。

2） 传输sql tuning sets,将10g上的sts通过诸如exp/imp,expdp/impdp等工具导入到11g待分析spa环境中。

3） 执行10g分析：这步骤很快

4） 执行11g分析：这步骤根据sql不同，耗时不同，库20w条左右sql set，大约耗时24小时。

5） 生成报告

6） 报告迭代：在执行3）到5）之前，由于sts数量很大，为了提高报告生成效率，需要进行sql set分割处理，之后生成报告，在分析报告的过程中，可能涉及到修改全局参数，这样，修改完参数后，还需要进行spa分析，因此3）到5）的步骤是个迭代的过程。

7） 实施：整理分析结果，进行生成实施。

4.1 10g sts导入到11g中

   10g sts以表形式导出，并导入到11g中，这需要在10g中对sts进行pack打包到一中转表中，然后导入到11g后，再unpack解包。

pack过程：以a库为例，将多个sqlset导入到一个中转表中,可以将脚本用shell放后台执行(可以并行执行5个，多了会报ora-01555错误)

--a库 sqlseta1_tab1到tab20  

begin

dbms_sqltune.create_stgtab_sqlset(table_name => 'sts_tab_a_0922',

schema_name => 'spa',

tablespace_name => 'sysaux');

end;

/

--pack

--a1

begin

dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'sqlseta1_tab1',

sqlset_owner => 'spa',

staging_table_name => 'sts_tab_a_0922',

staging_schema_owner => 'spa');

end;

/

。。。

begin

dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'sqlseta1_tab20',

sqlset_owner => 'spa',

staging_table_name => 'sts_tab_a_0922',

staging_schema_owner => 'spa');

end;

/

--a2

begin

dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'sqlseta2_tab1',

sqlset_owner => 'spa',

staging_table_name => 'sts_tab_a_0922',

staging_schema_owner => 'spa');

end;

/

。。。

begin

dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'sqlseta2_tab8',

sqlset_owner => 'spa',

staging_table_name => 'sts_tab_a_0922',

staging_schema_owner => 'spa');

end;

/

pack完毕后进行剔除处理：

剔除一些诸如sqlplus,plsql dev等执行的sql、以及按照force_matching_signature剔除重复行、剔除insert into values等。

查看对应sql类型的数据分布，后续按照数据分布情况分割sts：

--command_type对应含义可以查询v$sqlcommand

sql> select count(*),command_type from spa.sts_tab_a_0922 group by command_type;

  count(*) command_type

---------- ------------

      2469            7  --delete

     27506           47  --pl/sql execute

        11          170  --call method

    169548            6  --update

   2204682            3  --select

    253970            2  --insert

  执行剔除：

alter session enable parallel dml;

delete/* parallel(8)*/ from spa.sts_tab_a_0922 where module='pl/sql developer';   

commit;

delete/* parallel(8)*/ from spa.sts_tab_a_0922 where module='plsqldev.exe';

commit;

delete/* parallel(8)*/ from spa.sts_tab_a_0922 where module like 'sqlplus%';

commit;

delete/* parallel(8)*/ from spa.sts_tab_a_0922 where module like 'sql*plus';

commit;

delete/* parallel(8)*/ from spa.sts_tab_a_0922 a where rowid !=(select max(rowid)  from  spa.sts_tab_a_0922 b where a.force_matching_signature=b.force_matching_signature) and a.force_matching_signature<>0;

commit;

delete/* parallel(8)*/ from spa.sts_tab_a_0922 where upper(sql_text) like '%insert%into%values%';

commit;

--删除command_type in (48,1,189) --set transaction --create table  --alter tablespace

delete/* parallel(8)*/ from spa.sts_tab_a_0922 where command_type in (48,1,189);

commit;

10g导出sts：

export nls_lang=american_america.zhs16gbk

expspa/spa tables=spa.sts_tab_a_0922 file=/arch02/sts_tab_a_0922.dmp log=sts_tab_a_0922.log

导入sts到11g中：

imp spa/spa fromuser=spa touser=spa file=/oradata01/sts_tab_a_0922.dmp feedback=100

4.2 11g 500w条记录的sts 分割

由于采集到的sts数量很大，需要分割sts，每个sts中存放20w条左右的sql最佳，另外按照语句类型进行分割处理，这样spa分析报告可以对指定的sts进行并行分析，确保1到2天内能够完成报告生成，提高报告生成效率。

1）分割sts

将sts均分，update delete 1份，pl/sql execute call method 份，insert单独1份，select均分为10份，共13个sts。

--先查询总数量

sql> select count(*) from sts_tab_a_0922;

  count(*)

----------

   2658186

--查询各类型sql占的数量

select b.command_type,b.command_name,count(*)

from sts_tab_a_0922 a,v$sqlcommand b

where a.command_type=b.command_type

group by b.command_type,b.command_name;

command_type command_name                                                       count(*)

------------ ---------------------------------------------------------------- ----------

           3 select                                                              2204682

          47 pl/sql execute                                                        27506

           2 insert                                                               253970

           7 delete                                                                 2469

           6 update                                                               169548

         170 call method                                                              11

--建立索引，提高后续处理效率

create index idx_sts_tab_a_0922 on sts_tab_a_0922(sql_id) parallel 16;

--将select语句对应sts进行拆分为10份，每份22w条左右

declare

  l_curr_table_tips   number   :=0;

begin

  --select 0..9尾号 改为sqlset_run

  for x in (select sql_id from sts_tab_a_0922 where command_type =3 order by elapsed_time/executions) loop

    update sts_tab_a_0922 set name='sqlset_run_'||l_curr_table_tips where sql_id = x.sql_id;

    l_curr_table_tips := mod(l_curr_table_tips 1, 10);

  end loop;

end;

/

--update delete sqlset_run_10

update sts_tab_a_0922 set name='sqlset_run_10' where command_type in (6,7);

--pl/sql execute call method

update sts_tab_a_0922 set name='sqlset_run_11' where command_type in (47,170);

--insert

update sts_tab_a_0922 set name='sqlset_run_12' where command_type in (2);

commit;

--查询select对应数量

select name,count(*)

from sts_tab_a_0922

where command_type=3

group by name;

name                             count(*)

------------------------------ ----------

sqlset_run_9                       220468

sqlset_run_1                       220469

sqlset_run_5                       220468

sqlset_run_6                       220468

sqlset_run_7                       220468

sqlset_run_3                       220468

sqlset_run_2                       220468

sqlset_run_0                       220469

sqlset_run_8                       220468

sqlset_run_4                       220468

2）生成批量创建和删除并行sql set table的语句

用spa用户执行,开多个窗口，每个要10分钟

--共13个sqlset要处理

set line 9999 pagesize 9999

select 'create table sqlset_tab_run_'||(rownum-1)|| '

        nested table "bind_list" store as "sqlset_tab_run_b_'||(rownum-1)||'"

        nested table "plan" store as "sqlset_tab_run_p_'||(rownum-1) || '"

        as select * from sts_tab_a_0922 where name=''sqlset_run_'||(rownum-1)||''';' x

  from dba_objects where rownum <= 13;

3）检查数量

select 'select ''sqlset_tab_run_'||(level-1)||''' name,count(*)

from sqlset_tab_run_'||(level-1)||' union all'

from dual

connect by level<=13;

name                count(*)

----------------- ----------

sqlset_tab_run_0      220469

sqlset_tab_run_1      220469

sqlset_tab_run_2      220468

sqlset_tab_run_3      220468

sqlset_tab_run_4      220468

sqlset_tab_run_5      220468

sqlset_tab_run_6      220468

sqlset_tab_run_7      220468

sqlset_tab_run_8      220468

sqlset_tab_run_9      220468

sqlset_tab_run_10     172017

sqlset_tab_run_11      27517

sqlset_tab_run_12     253970

13 rows selected.

4.unpack sqlset

可以并行执行5个，多了会报ora-01555错误。可以写成shell脚本放后台执行。

declare

x number :=0;

begin

    dbms_sqltune.unpack_stgtab_sqlset (

                 sqlset_name          => 'sqlset_run_'||x,

                 sqlset_owner         => 'spa',

                 replace              => true,

                 staging_table_name   => 'sqlset_tab_run_'||x,

                 staging_schema_owner => 'spa');

end;

/

。。。

declare

x number :=12;

begin

    dbms_sqltune.unpack_stgtab_sqlset (

                 sqlset_name          => 'sqlset_run_'||x,

                 sqlset_owner         => 'spa',

                 replace              => true,

                 staging_table_name   => 'sqlset_tab_run_'||x,

                 staging_schema_owner => 'spa');

end;

/

4.3 spa分析需要的统计信息导入

测试环境中，准确的统计信息是运行spa测试的基础。先确定统计信息方案，等确定方案后，导入10g统计信息到11g,导入后可能还需要做一些处理，比如更新统计信息等。实际上，在spa分析过程中，因为统计信息问题导致2次spa分析迭代，第1次：直接导入10g统计信息，由于发现大量表统计信息有问题，缺失列信息，统计信息过旧等。后面确认采用导入10g统计信息后重新收集：

1） 生产库未收集统计信息的表，除非出现大的性能问题，确认后收集。

2） 生产库收集了统计信息的表，如果统计信息不完整，比如列或索引等缺失，则收集。采用method_opt => 'for all columns size repeat'，可以保证原先没有直方图的采用for all columns size 1,有直方图的更新直方图，estimate_percent默认。

统计信息处理流程如下：

将生产中需要的业务schema对象统计信息导出，然后传输到测试环境中，注意传到测试环境中，首先需要删除原有的统计信息，否则可能出现不一致的问题。流程和脚本如下：

1） 从10g生产环境导出统计信息

--ogg.ogg_userlist存放需要导出的业务用户名，用来拼导出脚本

select 'exec dbms_stats.export_schema_stats(ownname=>'''||upper(username)||''', stattab=>''stat_snc_10g_20140916'',statown=>''spa'', statid=>'''||upper(username)||''');' from ogg.ogg_userlist;

--a库 exportstat.sh

echo start `date`

sqlplus / as sysdba <

exec dbms_stats.export_schema_stats(ownname=>'aaaa', stattab=>'stat_snc_10g_20140916',statown=>'spa', statid=>'aaaa');

…省略

exec dbms_stats.export_schema_stats(ownname=>'test', stattab=>'stat_snc_10g_20140916',statown=>'spa', statid=>'test');

exit;

eof

echo end `date`

--后台执行

nohup ./exportstat.sh > exportstat.sh.log 2>&1 &

2） 删除11g测试环境统计信息

select 'exec dbms_stats.delete_schema_stats(ownname=>'''||upper(username)||''', force=>true, no_invalidate=>false);' from ogg.ogg_userlist;

exec dbms_stats.delete_schema_stats(ownname=>'aaaa', force=>true, no_invalidate=>false);

…省略

exec dbms_stats.delete_schema_stats(ownname=>'test', force=>true, no_invalidate=>false);

3） 导入10g统计信息到11g测试环境中

-- 升级10g统计信息为11g,a,b库都做,表结构不同

exec dbms_stats.upgrade_stat_table(ownname=>'spa', stattab=>'stat_snc_10g_20140916');

--导入，可编写shell脚本后台执行

--a库

--select 'exec dbms_stats.import_schema_stats(ownname=>'''||upper(username)||''', stattab=>''stat_snc_10g_20140916'',statown=>''spa'', statid=>'''||upper(username)||''', force=>true ,no_invalidate=>false);' from ogg.ogg_userlist;

exec dbms_stats.import_schema_stats(ownname=>'aaaa', stattab=>'stat_snc_10g_20140916',statown=>'spa', statid=>'aaaa', force=>true ,no_invalidate=>false);

…省略

exec dbms_stats.import_schema_stats(ownname=>'test', stattab=>'stat_snc_10g_20140916',statown=>'spa', statid=>'test', force=>true ,no_invalidate=>false);

在最终升级的统计信息方案是导入10g统计信息到11g后，对10g未收集的不收集，收集的采用for all columns size repeat更新，详细参考“4.最终升级统计信息方案”。

4.4创建并行分析任务生成报告

通过对sts进行拆分，可以实现spa并行分析，spa分析主要有：spa任务创建、生成10g trail,生成11g trail,生成对比分析报告。

1） 创建并行spa分析任务

此步骤很快，小于10s钟。

conn spa/spa

declare

  l_spa_task_name  varchar2(64);

begin

  for x in 0..12 loop

    l_spa_task_name := dbms_sqlpa.create_analysis_task(

                             task_name    => 'spa_task_run_'||x,

                             description  => 'spa analysis task at : '||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'),

                             sqlset_name  => 'sqlset_run_'||x,

                             sqlset_owner => 'spa');

    dbms_output.put_line('spa task created as : '||l_spa_task_name);

  end loop;

end;

/

--查询任务是否创建

select owner,task_name from dba_advisor_tasks where task_name like 'spa_task_run_%';

2） 生成10g trail

由于10g sql执行信息已经采集到，所以此过程很快，小于10s，使用shell并行执行。主要参数用execution_type指定为convert sqlset。

i=0

while [ "$i" -le 12 ]

do

cat > ./exec_spa_run_$i.sh <

sqlplus spa/spa <

exec dbms_sqlpa.execute_analysis_task( task_name => 'spa_task_run_'||$i,execution_name => 'exec_10g_run_'||$i,execution_type => 'convert sqlset',execution_desc => 'convert 10g sqlset for spa task at : '||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));

exit

eof

eofscript

chmod u x exec_spa_run_$i.sh

nohup ./exec_spa_run_$i.sh > exec_spa_run_$i.log 2>&1 &

i=$((i 1))

done

--查询执行情况以及是否有错误

set line 300 pagesize 9999

col status_message for a50;

col error_message for a50;

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select task_id,task_name,execution_name,execution_start,execution_end,status_message,error_message from dba_advisor_executions where task_name like 'spa_task_run_%';

select message,count(*) from dba_advisor_findings where type='error' group by message order by 2;

1） 生成11g trail

并行测试生成11g的执行信息,生成shell脚本，后台执行。这个过程最为关键的，也是最慢的过程，oracle会实际执行sts中对应sql，有可能有的sql执行计划改变，会执行的很慢，从而影响整理过程。最容易出问题的步骤就在这个过程中，因此，需要在执行过程中进行监控和分析、甚至需要迭代重跑并行分析任务。主要参数用execution_type指定为test execute。

i=0

while [ "$i" -le 12 ]

do

cat > ./exec_spa_run_$i.sh <

sqlplus spa/spa <

exec dbms_sqlpa.execute_analysis_task('spa_task_run_$i', 'test execute', 'exec_11g_run_$i', null,

'execute sql in 11g for spa task at : '||to_char(sysdate, 'yyyy-mm-dd hh24:mi:ss'));

exit

eof

eofscript

chmod u x exec_spa_run_$i.sh

nohup ./exec_spa_run_$i.sh > exec_spa_run_$i.log 2>&1 &

i=$((i 1))

done

1） 检查spa分析任务进度和状态

由于第3步：生成11g trail是非常耗时（库如果不进行sts拆分，需耗时13天左右）也是容易出问题的步骤，因此，需要在工作日，隔一小时查看下进度，并查看是否有报错。

set line 300 pagesize 999

col task_name for a20

col fin_ratio for a5

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select sid, task_id,(select distinct task_name from dba_advisor_executions b where a.task_id=b.task_id) task_name,sofar, totalwork, round(sofar/totalwork,2)*100||'%' fin_ratio,

elapsed_seconds,last_update_time,start_time, start_time (sysdate - start_time)/sofar * totalwork est_end_time

  from v$advisor_progress a

 where  task_id in (select task_id from  dba_advisor_executions where task_name like 'spa_task%')

  and sofar <> totalwork

   and sofar <> 0

order by 2;

----日志查询，查看出错信息

set line 300 pagesize 9999

col status_message for a10

col error_message for a50

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

select task_name,execution_start,execution_end,status,status_message,error_message from dba_advisor_log where task_name like 'spa%';

select task_id,task_name,execution_name,execution_start,execution_end,status_message,error_message from dba_advisor_executions where task_name like 'spa_task_run%' order by execution_start desc;

2） 生成spa分析报告

通过对比10g和11g sql执行统计信息:buffer gets、cpu time、elapsed time、plan_hash_value等来获得执行计划是否改变、sql性能是否下降报表。

并行执行分析过程并产生报告（shell环境中执行，最好建立一个新的目录spareport）,大约1小时。

cd /home/oracle/spa

mkdir spareport

脚本如下，放入后台执行

i=0

while [ "$i" -le 12 ]

do

cat > ./get_rpt_$i.sh <

sqlplus spa/spa <

-------------elapsed_time

begin

dbms_sqlpa.execute_analysis_task(

task_name => 'spa_task_run_$i',

execution_type => 'compare performance',

execution_name => 'compare_elapsed_time',

execution_params => dbms_advisor.arglist('execution_name1', 'exec_10g_run_$i', 'execution_name2', 'exec_11g_run_$i', 'comparison_metric', 'elapsed_time') );

end;

/

-------------cpu_time

begin

dbms_sqlpa.execute_analysis_task(

task_name => 'spa_task_run_$i',

execution_type => 'compare performance',

execution_name => 'compare_cpu_time',

execution_params => dbms_advisor.arglist('execution_name1', 'exec_10g_run_$i', 'execution_name2', 'exec_11g_run_$i', 'comparison_metric', 'cpu_time') );

end;

/

-------------buffer_gets

begin

dbms_sqlpa.execute_analysis_task(

task_name => 'spa_task_run_$i',

execution_type => 'compare performance',

execution_name => 'compare_buffer_gets_time',

execution_params => dbms_advisor.arglist('execution_name1', 'exec_10g_run_$i', 'execution_name2', 'exec_11g_run_$i', 'comparison_metric', 'buffer_gets') );

end;

/

alter session set events='31156 trace name context forever, level 0x400';

-------------report

set lines 1111 pages 50000 long 1999999999 trim on trims on serveroutput on size unlimited

spool spa_report_elapsed_time_$i.html

select dbms_sqlpa.report_analysis_task('spa_task_run_$i', 'html', 'all','all', top_sql=>300,execution_name=>'compare_elapsed_time') from dual;

spool off;

spool spa_report_cpu_time_$i.html

select dbms_sqlpa.report_analysis_task('spa_task_run_$i', 'html', 'all','all', top_sql=>300,execution_name=>'compare_cpu_time') from dual;

spool off;

spool spa_report_buffer_$i.html

select dbms_sqlpa.report_analysis_task('spa_task_run_$i','html','all','all',top_sql=>300,execution_name=>'compare_buffer_gets_time') from dual;

spool off;

spool spa_report_errors_$i.html

select dbms_sqlpa.report_analysis_task('spa_task_run_$i', 'html', 'errors','summary') from dual;

spool off;

spool spa_report_unsupport_$i.html

select dbms_sqlpa.report_analysis_task('spa_task_run_$i', 'html', 'unsupported','all') from dual;

spool off;

exit

eof

eofscript

chmod u x get_rpt_$i.sh

#nohup ./get_rpt_$i.sh >get_rpt_$i.log 2>&1 &

i=$((i 1))

done

4.5 spa分析的迭代过程

spa分析经过多次迭代，每迭代一次，则4.4的步骤就要重新做一遍，只需要修改对应的task_name即可，如果找到正确的方法，可以减少迭代次数。每次迭代都是由于经过spa分析发现问题，不得不修改如影响全局的优化器参数、打开或关闭fix control开关、收集大量表的统计信息等，主要由于影响较大，所以需要重跑spa分析。

第一次迭代：设置参数_optimizer_squ_bottomup=true和_optimizer_cost_based_transformation=linear。

第二次迭代：将"_fix_control"增加'9380298:on'，加上原有的开关，执行语句：

alter system  set "_fix_control"='9380298:on','8560951:on','8893626:off','9344709:off','9195582:off';

第三次迭代：导入10g统计信息到11g库中后，对10g原先不收集的表还是不收集，已收集的表通过method_opt=>’for all columns size repeat’更新。

其它迭代：由于执行过程中报ora-01555错误。见4.6.spa执行分析过程注意点。

4.6 spa执行分析过程注意点

spa执行过程中，某些sql可能因为执行计划改变或者数据量变化，导致执行超时或报错（ora-01555等），这时，spa分析可能终止，需要找出对应sql，从sts中清除出去，单独分析或设置超时。如下：

查询超时sql

 select b.sql_id

  from dba_advisor_findings a,dba_advisor_sqlstats b

   where a.task_id=b.task_id and a.object_id=b.object_id

   and a.type='error'

  and a.message like '%the current operation was interrupted because it timed out%';

--导致ora-01555错误的sql,暂且删除

exec dbms_sqltune.delete_sqlset('sqlset_run_3','sql_id=''5r5jth1k2prdr''','spa');

--修改undo

alter tablespace undo add datafile '....' size 8192m autoextend off;

alter system set undo_retention=10000;

报ora-01555错误，除了undo设置以外，还可能是执行时间超长，可以对执行task设置超时，当某个sql超出xx秒后，则自动结束，这个步骤，需要在生成11g trial之前做，如下：

--设置超时时间 很重要，有的特别长的，超时跳过，防止ora-01555

exec dbms_sqlpa.set_analysis_task_parameter(task_name=>'spa_task_run_0',parameter=>'local_time_limit',value=>'2000');

exec dbms_sqlpa.set_analysis_task_parameter(task_name=>'spa_task_run_1',parameter=>'local_time_limit',value=>'3000');

exec dbms_sqlpa.set_analysis_task_parameter(task_name=>'spa_task_run_3',parameter=>'local_time_limit',value=>'3000');

exec dbms_sqlpa.set_analysis_task_parameter(task_name=>'spa_task_run_4',parameter=>'local_time_limit',value=>'3000');

exec dbms_sqlpa.set_analysis_task_parameter(task_name=>'spa_task_run_6',parameter=>'local_time_limit',value=>'3000');

exec dbms_sqlpa.set_analysis_task_parameter(task_name=>'spa_task_run_7',parameter=>'local_time_limit',value=>'3000');