【云和恩墨大讲堂】玩转AWR裸数据(下)

讲师简介:

罗海雄 云和恩墨性能优化总监

ITPUB论坛数据库管理版版主,2012 ITPUB全国SQL大赛冠军得主,他还是资深的架构师和性能优化专家,对 SQL 优化和理解尤其深入;从开发到性能管理,他有着超过10年的企业级系统设计和优化经验。曾经服务于甲骨文公司,组织和主讲过多次《甲骨文技术开发人员日》和《Oracle圆桌会议》,并具备丰富的制造行业系统架构经验。

上一次分享,主要介绍了如果通过分析函数/行列转换等Oracle的高级SQL技巧从AWR的裸数据中获取有用信息的方法。

比如:

  • value - lag(value) over(partition by stat_name order by snap_id) 可以获取两次snap_id间的数据区别
  • 通过PIVOT, 可以轻松的做行列转行。

select * from (select snap_id,STAT_NAME, value-lag(value) over(partition by STAT_NAME order by snap_id) value from dba_hist_sysstat where stat_name in ( ‘redo size’,‘execute count’,‘DB time’,‘physical reads‘ ) ) PIVOT (sum(value) for stat_name in ( 'redo size','execute count','DB time','physical reads‘ ))order by snap_id;

  • 通过Ratio_To_Report() over() 可以获得当前行数据在所有同组数据内占的比例

上次的分享,侧重点更多是在SQL上。而这次,我会更侧重于AWR. 也就是说,从裸数据中进行分析,从而能在实际中帮到大家分析性能。

在看AWR时,有几个区域是必看的。

  • 第一个是LOAD PROFILE.

也就是我在上次分享中,用来演示lag() 函数的部分:

select * from (select snap_id,STAT_NAME, value-lag(value) over(partition by STAT_NAME order by snap_id) value from dba_hist_sysstat where stat_name in ( ‘redo size’,‘execute count’,‘DB time’,‘physical reads‘ ) ) PIVOT (sum(value) for stat_name in ( 'redo size','execute count','DB time','physical reads‘ ))order by snap_id;

把stat_name里面的部分,加上LOAD PROFILE的其他指标,就是个完整的load profile了。

通过load profile, 大家可以对系统的总体负载有个准确的认识。

  • 第二个部分,是Top timed events, 最耗时间的等待事件(包括CPU)的部分。

通过这个部分,大家可以了解整个系统的性能瓶颈。同样,我在上次的分享中也给出了SQL:

select snap_id,event,pct||'%' PCT,time from ( select snap_id,event,round(time)time, round(RATIO_TO_REPORT(TIME) over(partition by snap_id)*100,1) pct from( select 'CPU Time' EVENT,snap_id,value/100 - LAG(value)over(partition by stat_name order by snap_id)/100 TIME from DBA_HIST_SYSSTAT where stat_name = 'CPU used by this session' union all select event_name,snap_id, time_waited_micro/1e6 - LAG(time_waited_micro) over(partition by event_name order by snap_id)/1e6 from DBA_HIST_SYSTEM_EVENT where wait_class!='Idle' )where time>0) where pct>1 order by snap_id,time desc

  • 通常来说,知道了系统负载,系统瓶颈,我们还需要了解的是第三个部分: Top SQL

通过Top SQL, 我们可以了解系统运行过哪些主要的语句。

但是,传统的AWR报告中的Top SQL是有缺陷的。最主要的问题,它的信息是分散的。

在对SQL进行判断时,我会结合多个指标。执行时间(elapsed Time)、CPU时间(CPU Time)、逻辑读(Buffer gets)、物理读(disk reads)、执行次数(executions)、返回行数(rows_processed),但是,传统的awr报告,这些指标分布在不同位置。看起来很不方便。比如说这个,有执行时间,执行次数,CPU时间。但缺乏逻辑读,物理读,返回行数等,有时候,还得专门去找。

所以呢,我经常访问裸数据,使用SQL, 直接从数据库里取出包含完整信息的Top SQL.

另外,根据不同的情况,我们可能关心的点也不一样。比如说,系统CPU消耗严重,我们更关心SQL order by CPU, I/O严重时,关心的则是物理读。所以呢,我用的SQL, 可以支持同时取出按不同指标的排序的Top N SQL.

比如说, Top 10 by elapsed time, Top 10 by CPU, Top 10 by disk reads.

大家都知道,传统的order by + rownum < N 的方式只支持对其中一个指标进行排名,显然是不够的。而分析函数,又再次发挥了作用。

select sql.*, (select SQL_TEXT from dba_hist_sqltext t where t.sql_id = sql.sql_id and rownum=1 ) SQLTEXT from (select a.* , RANK() over( order by els desc) as r_els, RANK() over( order by phy desc) as r_phy, RANK() over( order by get desc) as r_get, RANK() over( order by exe desc) as r_exe, RANK() over( order by CPU desc) as r_cpu from ( select sql_id,sum(executions_delta) exe,round(sum(elapsed_time_delta ) / 1e6, 2) els ,round(sum(cpu_time_delta) / 1e6, 2) cpu, round(sum(iowait_delta) / 1e6, 2) iow,sum(buffer_gets_delta) get, sum(disk_reads_delta) phy,sum(rows_processed_delta) RWO, round(sum(elapsed_time_delta) / greatest(sum(executions_delta), 1) / 1e6,4) elsp, round(sum(cpu_time_delta) / greatest(sum(executions_delta), 1) / 1e6, 4) cpup, round(sum(iowait_delta) / greatest(sum(executions_delta), 1) / 1e6, 4) iowp, round(sum(buffer_gets_delta) / greatest(sum(executions_delta), 1), 2) getp, round(sum(disk_reads_delta) / greatest(sum(executions_delta), 1), 2) phyp, round(sum(rows_processed_delta) / greatest(sum(executions_delta), 1), 2) ROWP from dba_hist_sqlstat s --where snap_id between ... and ... group by sql_id ) a )SQL where r_els <= 10 or r_phy <=10 or r_cpu<=10 order by els desc

大家可以看到,这里面用到了 RANK() 函数。这个函数可以得出根据某个指标排序的排名。然后再通过最后的 r_els <= 10 or r_phy <=10 or r_cpu<=10 的过滤条件,就可以获取按照多个指标排序的Top N了。

有时候,我会把这个结果想办法做成HTML, 就变成这个效果了。

在分析SQL中,还有很重要的信息。

第一个是执行计划。

select * from table(dbms_xplan.display_awr('&SQLID'))

除了执行计划,还有一个信息不可或缺,就是绑定变量。

我碰到的SQL问题里面,有一个典型分类,就是SQL本来执行好好的,突然变差。这时候,在分析时,需要很关注的,就是历史绑定变量。Oracle在AWR裸数据中也保留了绑定变量:

DBA_HIST_SQLSTAT.BIND_DATA 这个栏位里面,保存了绑定变量

通过以下SQL, 可以获取历史绑定变量:

select snap_id,sq.sql_id,bm.position, dbms_sqltune.extract_bind(sq.bind_data,bm.position).value_string value_string from dba_hist_sqlstat sq ,dba_hist_sql_bind_metadata bm where sq.sql_id = bm.sql_id --and sq.sql_id = '&sql'

出来的是行格式的,读起来不方便。用PIVOT 做一个行列转换就漂亮了。

select * from ( select snap_id, to_char(sn.begin_interval_time,'MM/DD-HH24:MI') snap_time, sq.sql_id,bm.position, dbms_sqltune.extract_bind(bind_data,bm.position).value_string value_string from dba_hist_snapshot sn natural join dba_hist_sqlstat sq ,dba_hist_sql_bind_metadata bm where sq.sql_id = bm.sql_id and sq.sql_id = '&sql' ) PIVOT (max(value_string) for position in (1,2,3,4,5,6,7,8,9,10)) order by snap_id

完美的取出不同时间段的历史绑定变量值.

对于“SQL本来执行好好的,突然变差”的问题,有一个比较简洁的解决方式,就是尝试让SQL走回以前的执行计划。

Select plan_hash_value ,Sum(Elapsed_time_Delta) /greatest(Sum(Executions_Delta),1),sum(Executions_Delta) From dba_hist_sqlstat where sql_id = '&SQLID' group by plan_hash_value ;

通过以上SQL, 可以快速获取某个SQL多个执行计划的执行效果。然后再想办法应用其执行计划,往往可以收到奇效。绑定执行计划的方法有多种,SPM/SQL Profile/SQL Patch等,具体我就不展开了。

不知道大家有没有碰到过这样的情况, 有时候,明明性能瓶颈在SQL,但Top SQL中DB Time(%)指标却很低,前10个加起来也不足20%.

像这个AWR, Top SQL by elapsed Time才记录了2%. 也就是说,你只能看到2%的性能相关的SQL.

其中一个主要原因是由于Shared Pool大小限制以及非绑定变量问题,导致SQL可能会被漏记,这种情况下,怎么办呢?

其实,有个地方不会被漏记。就是Top Segments.通常,如果Top SQL中找不到太多信息,我们可以去看看Top Segments:

这是摘自同一个AWR的信息。 Top segments 告诉我们,对表的访问集中在前面3个,我们可以专注于这几个表的问题。

当然, 同样可以通过SQL直接访问裸数据获取相关信息:

Select begin_interval_time,seg.snap_id,PHYSICAL_READS_DELTA, object_name,subobject_name from DBA_HIST_SEG_STAT SEG ,DBA_HIST_SEG_STAT_OBJ O , dba_hist_snapshot snap where o.obj# = seg.obj# and o.dataobj# = seg.dataobj# and PHYSICAL_READS_DELTA > 1e5 and seg.snap_id = snap.snap_id and begin_interval_time > sysdate - 4/24 order by PHYSICAL_READS_DELTA desc

这是一个常用的AWR裸数据的列表:

多数的AWR分析可以从这些裸数据开始。Load Profile, Top Timed Event, Top SQL, SQL Plan, SQL 绑定变量, Top Segments,相关的SQL 陆陆续续都贴出来了.

AWR裸数据如此的重要,对于关心数据库性能的DBA们,我们需要好好的保护好它们~

1. 系统保存时间,默认7天远远不足,建议改到30天以上,跨过一个月结周期

2. 需要的时候,我们可以对裸数据进行离线备份

@?/rdbms/admin/awrextr

3. 甚至,我们可以把裸数据专门找个数据库存起来,作为一个资料库使用。

@?/rdbms/admin/awrload

4. 有时候,也可以针对特定的表进行备份。比如说,我刚刚贴的这个列表

总结

今天分享主要内容是:

1. AWR的分析办法: Load Profile, Top Timed Event, Top SQL, SQL Plan, SQL 绑定变量,Top Segments

2. 一些高级SQL用法: 分析函数 Lag/Rank/Ratio_to_report, 行列转换 PIVOT

原文发布于微信公众号 - 数据和云(OraNews)

原文发表时间:2017-01-09

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏程序人生

软件性能调优:看数据,还是谈概念?

上周写了「想让服务器跑得快,并不是换个编程语言那么简单」,很多朋友的留言歪了楼:论性能,C语言甩Python数倍到数十倍,你说和编程语言没关?拜托,程序君只是说...

2514
来自专栏Golang语言社区

棋牌游戏服务器架构: 详细设计(二) 应用层设计

这里的应用层,指的是CenterServer、LogonServer、LogServer、RoomServer等几个服务器,另外还包括游戏模块的设计。不过游戏...

40111
来自专栏HappenLee的技术杂谈

大数据小视角4:小议Lambda 与 Kappa 架构,不可变数据的计算探索

首先我们来看看什么是Lambda架构,Lambda演算在编程语言之中是一个编程范式,它遵循如下几个特点:

802
来自专栏一名叫大蕉的程序员

慢SQL,压垮团队的最后一根稻草No.92

先说结论,我支持将逻辑写在 Java 等应用系统中。 背景:今天只讨论一种应用模式,就是最普遍的,前端实时调用后端web服务,服务端经过DB的增删改查作出响应的...

3466
来自专栏小蠢驴iOS专题

实际开发中的多线程简单使用场景

1326
来自专栏圣杰的专栏

性能优化知多少

1. 引言 最近一段时间,系统新版本要发布,在beta客户测试期间,暴露了很多问题,除了一些业务和异常问题外,其他都集中在性能上。有幸接触到这些性能调优的机会,...

1779
来自专栏Spark学习技巧

第5篇:数据库系统的实现

前言 前面的文章中,主要都是在围绕关系数据库理论进行研究,没有涉及到数据库系统的具体实现。 虽说数据库系统的具体实现因业务环境,RDBMS等因素而异,但总体开发...

2817
来自专栏何俊林

开发直播app中要了解的原理

前言:每个成功者多是站在巨人的肩膀上!在做直播开发时 碰到了很多问题,在收集了许多人博客的基础上做出来了成功的直播项目并做了整理 。本文来自OneTea,袁峥S...

22810
来自专栏Golang语言社区

棋牌游戏服务器架构: 详细设计(二) 应用层设计

这里的应用层,指的是CenterServer、LogonServer、LogServer、RoomServer等几个服务器,另外还包括游戏模块的设计。不过游戏...

33713
来自专栏数据和云

罗海雄:仅仅使用AWR做报告? 性能优化还未入门(含PPT)

编辑手记:祝贺罗海雄老师加入Oracle ACE社区,他是数据库SQL开发和性能优化专家,也是ITPUB论坛的资深版主,我们整理了罗老师一篇AWR裸数据分析的文...

692

扫描关注云+社区