AWR报告简介与管理【博森瑞】.pptVIP

AWR报告简介与管理博森瑞 1-1 Copyright © Bosenrui, 2016. All rights reserved. 课程目标 1-2 Copyright © Bosenrui, 2016. All rights reserved. 1 什么是AWR2 AWR的原理3 AWR的配置4 AWR 脚本族研究5 AWR的批量收集6 AWR的迁移7 AWR组件的重建9 课后答疑 1-3 Copyright © Bosenrui, 2016. All rights reserved. 什么是AWR?Automatic Workload Repository (AWR)Automatic Workload Repository (AWR) is a repository of historical performance data that includes cumulative statistics for the system, sessions, individual SQL statements, segments, and services. These statistics are the foundation of performance tuning. By automating the gathering of database statistics for problem detection and tuning, AWR serves as the foundation for database self-management.AWR是存放历史性能信息的信息库,存放累计的系统,会话,个别的SQL语句,段,与服务.这些统计信息是性能优化的基础.通过自动收集数据库的统计信息可以对数据库进行问题诊断与调整,AWR也是数据库自我管理的基础 1-4 Copyright © Bosenrui, 2016. All rights reserved. 2 AWR的原理 1-5 Copyright © Bosenrui, 2016. All rights reserved. 2 AWR的原理As shown the database stores recent AWR statistics in the SGA. By default, the MMON process gathers statistics every hour and creates an AWR snapshot A snapshot is a set of performance statistics captured at a specific time. The database writes snapshots to the SYSAUX tablespace. AWR manages snapshot space, purging older snapshots according to a configurable snapshot retention policy.数据库存放最近的AWR信息在SGA中.默认MMON 每个小时获取统计信息,并且创建AWR快照.快照是在特定时间捕获的统计信息集合.数据库将快照写入到SYSAUX表空间中.AWR管理快照空间,根据snapshot retention policy设置进行清除 1-6 Copyright © Bosenrui, 2016. All rights reserved. 2 AWR的原理Manageability Monitor Processes (MMON and MMNL)The manageability monitor process (MMON) performs many tasks related to the Automatic Workload Repository (AWR). For example, MMON writes when a metric violates its threshold value, taking snapshots, and capturing statistics value for recently modified SQL objects.The manageability monitor lite process (MMNL) writes statistics from the Active Session History (ASH) buffer in the SGA to disk. MMNL writes to disk when