oracle性能优化终极武器-awr

2019.06.06 - 30-转载

接上篇ogg,触发器加工数据,导致ogg延迟。最后了解到awr,真的是太管用了,让拖累ogg同步的性能低下的sql无处可逃!

下面转载一个awr的介绍,真的很有用。

原文地址:https://www.cnblogs.com/tenchina/p/8609448.html

什么是AWR?

一堆历史性能数据,放在sysaux表空间上,AWR和sysaux都是10g出现的,是oracle调优的关键特性。

默认快照间隔1小时;10g保存7天;11g保存8天;

可以通过DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS修改

AWR核心是dbms_workload_repository包

@?/rdbms/admin/awrrpt 本实例

@?/rdbms/admin/awrrpti  RAC中选择实例号(在主机中想拉另一台oracle的awr报告)

基础统计指标:

SQL和优化器指标

OS指标

等待事件类型

时间指标(oracle时间模型)

AWR小技巧

手动执行一个快照:

exec dbms_workload_repository.create_snapshot;

创建一个awr基线:

exec dbms_workload_repository.create_baseline(start_snap_id,end_snap_id,baseline_name);

@?/rdbms/admin/awrddrpt AWR比对报告

@?/rdbms/admin/awrgrpt   RAC全局AWR

自动生成AWR HTML报告:

http://www.oracle-base.com/dba/10g/generate_multiple_awr_reports.sql

AWR报告分析可从以下几点入手:

(1).Oacle主机资源开销分析及负载情况  

(2).oracle top信息分析        Top 10 Foreground Events by Total Wait Time

(3).sql开销情况            SQL ordered by Elapsed Time

(4).oracle负载情况          Load Profile

(5).oracle实例效率分析        Instance Efficiency Percentages (Target 100%)

(6).oracle共享池分析         Shared Pool Statistics

AWR报告指标分析:

Oacle主机资源开销分析及负载情况:

CPUs:32 逻辑cpu数

Elapsed快照监控时间:如果为了诊断特定时段性能问题则Elapsed不宜过长15分钟~2、3个小时。如果是看全天负载那么可以长一些,最常见是60分钟后者120分钟。

DB Time:不包括Oracle后台进程消耗的时间,如果DB Time远远小于Elapsed时间,说明数据库比较空闲。

DB Time= cpu time + wait time(不包含空闲等待) (非后台进程)

数据库耗时17分钟,共32个逻辑cpu:17/32=0.53,平均每个cpu耗时0.53分钟

cpu利用率:0.53/60=0.008  0.8%利用率很小,说明cpu空闲

如果超过100%说明出现等待现象

 oracle负载情况:

Redo size:每秒产生的日志大小(单位字节),可标志数据变更频率, 数据库任务的繁重与否。

Logical reads:每秒/每事务逻辑读的块数.平决每秒产生的逻辑读的block数。Logical Reads= Consistent Gets + DB Block Gets;

Block changes:每秒/每事务修改的块数;

Physical reads:每秒/每事务物理读的块数;

Physical writes:每秒/每事务物理写的块数;

User calls:每秒/每事务用户call次数;

Sorts:每秒/每事务的排序次数;

Logons:每秒/每事务登录的次数;

Executes:每秒/每事务SQL执行次数;

Transactions:每秒事务数.每秒产生的事务数,反映数据库任务繁重与否。

Blocks changed per Read:表示逻辑读用于修改数据块的比例.在每一次逻辑读中更改的块的百分比。

Recursive Call:递归调用占所有操作的比率.递归调用的百分比,如果有很多PL/SQL,那么这个值就会比较高。

Rollback per transaction:每事务的回滚率.看回滚率是不是很高,因为回滚很耗资源 ,如果回滚率过高,可能说明你的数据库经历了太多的无效操作 ,过多的回滚可能还会带来Undo Block的竞争 该参数计算公式如下:

Round(User rollbacks / (user commits + user rollbacks) ,4)* 100% 。

Rows per Sort:每次排序的行数

Transactions:数据库层的TPS(单独看没什么意义,可用作优化前后的对比值)。

parses:解析次数;软解析加硬解析

Hard parses:硬解析  万恶之源,会造成多种等待,不要超过每秒20次

结合Time Model Statistics查看

Hard parses(硬解析数)和hard parse (sharing criteria) elapsed time对应,看一眼Time Model Statistics,

即可知该系统是否是解析敏感的

注:

Oracle的硬解析和软解析

  提到软解析(soft prase)和硬解析(hard prase),就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle,在执行和获取结果前,Oracle对此sql将进行几个步骤的处理过程:

  1、语法检查(syntax check)

  检查此sql的拼写是否语法。

  2、语义检查(semantic check)

  诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。

  3、对sql语句进行解析(prase)

  利用内部算法对sql进行解析,生成解析树(parse tree)及执行计划(execution plan)。

  4、执行sql,返回结果(execute and return)

  其中,软、硬解析就发生在第三个过程里。

  Oracle利用内部的hash算法来取得该sql的hash值,然后在library cache里查找是否存在该hash值;

  假设存在,则将此sql与cache中的进行比较;

  假设“相同”,就将利用已有的解析树与执行计划,而省略了优化器的相关工作。这也就是软解析的过程。

  诚然,如果上面的2个假设中任有一个不成立,那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。

  创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作,所以,应当极力避免硬解析,尽量使用软解析。

oracle top信息分析:

 

db file scattered read等待事件是当SESSION等待multi-block I/O时发生的,通过是由于full table scans或index fast full scans。发生过多读操作的Segments可以在“Segments by Physical Reads”和 “SQL ordered by Reads”节中识别(在其它版本的报告中,可能是别的名称)。如果在OLTP应用中,不应该有过多的全扫描操作,而应使用选择性好的索引操作。

DB file sequential read等待意味着发生顺序I/O读等待(通常是单块读取到连续的内存区域中),如果这个等待非常严重,应该使用上一段的方法确定执行读操作的热点SEGMENT,然后通过对大表进行分区以减少I/O量,或者优化执行计划(通过使用存储大纲或执行数据分析)以避免单块读操作引起的sequential read等待。通过在批量应用中,DB file sequential read是很影响性能的事件,总是应当设法避免。

Log File Parallel Write事件是在等待LGWR进程将REDO记录从LOG 缓冲区写到联机日志文件时发生的。虽然写操作可能是并发的,但LGWR需要等待最后的I/O写到磁盘上才能认为并行写的完成,因此等待时间依赖于OS完成所有请求的时间。如果这个等待比较严重,可以通过将LOG文件移到更快的磁盘上或者条带化磁盘(减少争用)而降低这个等待。

Buffer Busy Waits事件是在一个SESSION需要访问BUFFER CACHE中的一个数据库块而又不能访问时发生的。缓冲区“busy”的两个原因是:1)另一个SESSION正在将数据块读进BUFFER。2)另一个SESSION正在以排它模式占用着这块被请求的BUFFER。可以在“Segments by Buffer Busy Waits”一节中找出发生这种等待的SEGMENT,然后通过使用reverse-key indexes并对热表进行分区而减少这种等待事件。

Log File Sync事件,当用户SESSION执行事务操作(COMMIT或ROLLBACK等)后,会通知 LGWR进程将所需要的所有REDO信息从LOG BUFFER写到LOG文件,在用户SESSION等待LGWR返回安全写入磁盘的通知时发生此等待。减少此等待的方法写Log File Parallel Write事件的处理。

Enqueue Waits是串行访问本地资源的本锁,表明正在等待一个被其它SESSION(一个或多个)以排它模式锁住的资源。减少这种等待的方法依赖于生产等待的锁类型。导致Enqueue等待的主要锁类型有三种:TX(事务锁), TMD(ML锁)和ST(空间管理锁)。

sql开销情况:

 

SQL ordered by Elapsed Time部分可以最准确定位到某个导致的性能问题。重点关注3个参数的值:

(1).Elapsed Time(S)表示sql执行的总时间。

(2).Executions表示sql执行次数。

(3).Elap per Exec(s): 执行一次SQL的平均时间,单位时间为秒。

oracle实例效率分析:

主要关注一下两个参数:

(1).Buffer Nowait%:表示在内存获得数据的未等待比例

(2).Parse CPU to Parse Elapsd %:解析总时间中消耗总CPU的时间百分比,该值越低表示实例越空闲。

由于实例在50%左右实例处于正常状态。

Parse CPU to Parse Elapsd:说明在解析sql语句过程中,cpu占整个的解析时间比例。

解析实际运行时间/(解析实际运行时间+解析中等待资源时间),越高越好。计算公式为:Parse CPU to Parse Elapsd %= 100*(parse time cpu / parse time elapsed)。即:解析实际运行时间/(解析实际运行时间+解析中等待资源时间)。如果该比率为100%,意味着CPU等待时间为0,没有任何等待。

Shared Pool Statistics:

Memory Usage%维持在90以上,所以共享池没有造成严重浪费。

- END -

32
0

js 递归获取子节点所有父节点,深度遍历获取第一个子树

前端需求。 – 递归 – 深度优先遍历算法 // 查找一个节点的所有父节点 famil […]