day17_sql优化——AWR
AWR
负责收集、处理并维护性能统计信息，用于检查和分析性能问题(甚至生成的报告格式都非常接近)，AWR生成的统计数据即可以通过V$视图和DBA_*数据字典查看，也可以通过脚本来生成相应报表。
生成分析报表
AWR 是通过对比两次快照(snapshot)收集到的统计信息，来生成报表数据，生成的报表包括多个部分,AWR在生成报告时，可以选择生成TXT或HTML两种格式的报告.
awrrpt.sql ：生成指定快照区间的统计报表；
awrrpti.sql ：生成指定数据库实例，并且指定快照区间的统计报表；
awrsqlrpt.sql ：生成指定快照区间，指定SQL语句(实际指定的是该语句的SQLID)的统计报表；
awrsqrpi.sql ：生成指定数据库实例，指定快照区间的指定SQL语句的统计报表；
awrddrpt.sql ：指定两个不同的时间周期，生成这两个周期的统计对比报表；
awrddrpi.sql ：指定数据库实例，并指定两个的不同时间周期，生成这两个周期的统计对比报表
AWR收集信息最初保存到SGA中的shared pool中,但AWR最多使用 shared pool 5%空间,达到限制后把shared pool中内容写到磁盘,MMON进程负责固定时间写磁盘(默认1小时)
查看当前配置
SQL> col SNAP_INTERVAL format a20
SQL> col RETENTION format a20
SQL> select * from dba_hist_wr_control;
(SNAP_INTERVAL代表快照间隔 默认1小时,RETENTION代表保留时间 默认7天)
修改间隔
SQL> execute dbms_workload_repository.modify_snapshot_settings (interval=>30,retention=>14400); 每30分钟收集一次,保留10天
----------------------
生成标准统计报表
SQL> @$ORACLE_HOME/rdbms/admin/awrrpt.sql
Enter value for report_type: html(生成报表格式)
Enter value for num_days: 1 (输入开始和结束的snapshot编号输入天数信息)
Snap
Instance DB Name Snap Id Snap Started Level
------------ ------------ --------- ------------------ -----
TEST TEST 1 24 Apr 2012 06:00 1
2 24 Apr 2012 07:00 1
3 24 Apr 2012 08:11 1
4 24 Apr 2012 09:00 1
5 24 Apr 2012 10:00 1
(列出时间范围内的快照)
之后需要根据列出的时间范围，输入开始和结束的snap编号。
Enter value for begin_snap: 3
End Snapshot Id specified: 5
Enter value for report_name:(确定报告名,默认回车,输出很多内容最好报告成功)或/home/oracle/aa.html
Report written to awrrpt_1_3_5.html
=================================================
AWR分析
浏览器中 /home/oracle/aa.html
Elapsed 时间段内收集的快照
DB Time 数据库耗时(不包括Oracle后台进程消耗的时间。如果DB Time远远小于Elapsed时间，说明数据库比较空闲。)
Report Summary(报告摘要)
Load Profile(数据库负载情况)********重要
Redo size：每秒/每事务产生的redo大小（单位字节）可标志数据库任务的繁重程序。
Logical reads：每秒/每事务逻辑读的块数
Block changes：每秒/每事务修改的块数
Physical reads：每秒/每事务物理读的块数
Physical writes：每秒/每事务物理写的块数
User calls：每秒/每事务用户call次数
Parses：SQL解析的次数
Hard parses：其中硬解析的次数，硬解析太多，说明SQL重用率不高。***重要***
((
Oracle对sql的处理过程。当你发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程：
1、语法检查(syntax check)
检查此sql的拼写是否语法。
2、语义检查(semantic check)
诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。
3、对sql语句进行解析(parse)
利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan)。
4、执行sql，返回结果(execute and return)
其中，软、硬解析就发生在第三个过程里。
Oracle利用内部的hash算法来取得该sql的hash值，然后在library cache里查找是否存在该hash值；
假设存在，则将此sql与cache中的进行比较；
假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是软解析的过程。
诚然，如果上面的2个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。
创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。
))
Sorts：每秒/每事务的排序次数
Logons：每秒/每事务登录的次数
Executes：每秒/每事务SQL执行次数
Transactions：每秒事务数
Blocks changed per Read：表示逻辑读用于修改数据块的比例
Recursive Call：递归调用占所有操作的比率
Rollback per transaction：每事务的回滚率
Rows per Sort：每次排序的行数
--------------------------------------------
Instance Efficiency Percentages 实例效率
Buffer Nowait %：在缓冲区中获取Buffer的未等待比率
Redo NoWait %：在Redo缓冲区获取Buffer的未等待比率。
Buffer Hit %：数据块在数据缓冲区中的命中率，通常应在90%以上，否则，小于95%，需要调整重要的参数，小于90%可能是要加db_cache_size，但是大量的非选择的索引也会造成该值很高.
In-memory Sort %：在内存中的排序率。
Library Hit %：主要代表sql在共享区的命中率，通常在95%以上.
Soft Parse %：近似看作sql在共享区的命中率，小于<95%
Execute to Parse %：一个语句执行和分析了多少次的度量。在一个分析，然后执行语句，且再也不在同一个会话中执行它的系统中，这个比值为0。
Latch Hit %：要确保>99%，否则存在严重的性能问题
Parse CPU to Parse Elapsd %：100*(解析实际运行时间/(解析实际运行时间+解析中等待资源时间)
% Non-Parse CPU:PARSE_CPU相比，如果TOT_CPU很高，这个比值将接近100%，这是很好的，说明计算机执行的大部分工作是执行查询的工作，而不是分析查询的工作。
-------------------------------------------------
Shared Pool相关统计数据
Memory Usage %：正在使用的共享池的百分率。这个数字应该长时间稳定在75%～90%。如果这个百分率太低，就浪费内存
% SQL with executions>1：这是在共享池中有多少个执行次数大于一次的SQL语句的度量。
% Memory for SQL w/exec>1：这是与不频繁使用的SQL语句相比，频繁使用的SQL语句消耗内存多少的一个度量。
-----------------------------------------------------
Top 5 Timed Events首要的5个等待事件 ******重要********
waits 列 等待次数
times 列 总用时
avg wait(ms) 平均等待时间 (生产环境一般小于10ms正常) ms 毫秒 千分之一秒,微秒 千分之一 毫秒
db file sequential read：该事件说明在单个数据块上大量等待，该值过高通常是由于表间连接顺序很糟糕，或者使用了非选择性索引。
log file parallel write: 事件是在等待LGWR进程将REDO记录从LOG 缓冲区写到联机日志文件时发生的。
-------------------------------------------------------
SQL Statistics列出对资源消耗最严重的SQL语句 ****重要*****
============================================
调整AWR配置
手工创建一个快照
SQL> exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
查看快照
SQL> select * from sys.wrh$_active_session_history;
