数据库升级性能保障利器SQL Performance Analyzer_PART3

上一篇：数据库升级性能保障利器SQL Performance Analyzer_PART2

5.SPA数据字典视图和管理

   SPA分析视图主要以DBA/USER_SQLSET开头的系列视图以及DBA/USER_ADVISOR开头的系列视图，主要查询语句如下：

STS常用查询如下：

--查询sts中采集sql数目

select name,owner,to_char(last_modified,'yyyy-mm-dd hh24:mi:ss') last_modify,statement_count cnt from dba_sqlset;

--查询对应sql以及执行信息，如buffer gets

select * from dba_sqlset_statements where sql_id='8v4dradbvqqy5';

--获取绑定变量

select dbms_sqltune.extract_binds(bind_data) from dba_sqlset_statements where sql_id='8v4dradbvqqy5';

--dbms_sqltune.extract_binds获取绑定变量字段信息，关注name,position,datatype_string,value_string

   name                VARCHAR2(30),                     /* bind variable name */

   position            NUMBER,            /* position of bind in sql statement */

   dup_position        NUMBER,    /* if any, position of primary bind variable */

   datatype            NUMBER,                    /* datatype id for this bind */

   datatype_string     VARCHAR2(15),/* string representation of above datatype */

   character_sid       NUMBER,              /* character set id if bind is NLS */

   precision           NUMBER,                               /* bind precision */

   scale               NUMBER,                                   /* bind scale */

   max_length          NUMBER,                          /* maximum bind length */

   last_captured       DATE,      /* DATE when this bind variable was captured */

   value_string        VARCHAR2(4000),     /* bind value (text representation) */

   value_anydata       ANYDATA)         /* bind value (anydata representation) */

在实际根据SPA报告分析性能下降原因时，可以通过以上脚本查询出SQL以及对应绑定变量,这样方便进行性能分析，直接使用SPA报告中的SQL，经常因为空格等原因会报错。

SPA分析常用查询如下：

--检查并行运行的SPA任务的状态

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;

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

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_NAMEs,EXECUTION_START,EXECUTION_END,STATUS_MESSAGE,ERROR_MESSAGE from DBA_ADVISOR_EXECUTIONS where task_name like 'SPA_TASK_RUN%' order by EXECUTION_START desc;

--查询出错SQL_ID

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 '%ORA-01555%';

6.SPA 性能分析：分析性能下降原因

6.1 如何分析报告

根据每个STS跑出的SPA分析报告：buffer gets、cpu time、elapsed time、error、unsupport ，分别分析，重点关注buffer gets、cpu time、elapsed time 3份报告，按照顺序分析，一般来说，cpu time,elapsed time中出现的SQL，基本都在buffer gets中。报告样式如下：

标题头：关注status 是否是COMPLETED,关注其他是否正常，比如SQL语句数目，出错信息对应语句可以用SPA常见查询，查询出错SQL_ID。

汇总信息：查看SPA分析的汇总情况，总量，多少性能提高的，多少计划改变的，未变的，出错的数量

明细信息：SPA列出TOP 300的信息，重点关注。有object_id,sql_id,执行负载，10g执行频率，执行前对应指标度量信息，执行后对应指标度量信息，影响，计划是否改变。

6.1.1 需要分析的SQL

对每份报告，首先看报告头，判断报告是否正常执行完毕，如果正常，主要分析明细信息。将TOP 300的明细COPY到EXCEL中。然后按照plan change,选择y的，然后按照Impact on SQL从小到大排序，只关注Impact on SQL值<0的。也就是分析执行计划改变、性能下降的SQL，由于升级伴随着导入导出，一般执行计划未变的，无须分析，除非发现特别慢的，可以分析。最后excel可以增加备注列，说明性能下降原因，以便汇总和解决。

以上excel每个报告一份，并且将buffer_gets、cpu time、elapsed time作为单独的sheet。每个报告需要输出：

l  每条待分析SQL原因放到备注中。

l  按报告输出分析过程，包括SQL，执行计划，原因等到对应报告的文本文件中。

l  有些SQL需要10046、10053分析，也需要输出文件。

6.1.2分析性能下降原因

10.2.0.4升级到11.2.0.4,SQL出现性能下降，侧重于分析如下方面：

l  优化器新特性引入导致的BUG，如Adaptive Cursor sharing/Cardnality feedback，经常存在导致SQL计划频繁改变。

l  优化器新特性引入导致的限制，特别是查询转换方面的，如BUG：9380298 By design JPPD does not consider to push join predicates into a view if this results in a cartesian product between the tables involved in the pushed predicates.(Optimizer trace shows

   JPPD:     JPPD bypassed: Cartesian product found

)

ORACLE不使用JPPD谓词推入

l  参数问题，比如改变参数的默认值，导致在10g中SQL性能很好，但是在11g中不可以。

l  优化器改变导致执行计划细微差别，但是本质一致，比如10g是UNION ALL PARTITION,11g是UNION ALL。

l  统计信息问题：包括原10g统计信息不准确，因为算法差别，到11g中有问题，解决需要重新收集，比如更新过期统计信息、不完整统计信息、需要收集某些列直方图等。

l  对象有效性问题：比如11g中索引因某些问题导致失效，需要进行索引有效性检查。

l  FIX CONTROL开关问题，引入的很多特性都可以通过FIX CONTROL开关控制。如"_fix_control"可以设置为'9380298:ON'。

l  SPA报告不准确，可能数据量变化、或者返回行不同等会造成结果不同，但是SPA报告只对比对应指标性能，而且10g的指标是个平均值,11g是单次SPA测试结果。