oracle 性能优化 04_报告及日志解读

2019独角兽企业重金招聘Python工程师标准>>>

一、Alert日志解读
    告警日志文件是一类特殊的跟踪文件（trace file）。告警日志文件命名一般为
alert_<SID>.log，其中SID为ORACLE数据库实例名称。数据库告警日志是按时间顺序记录message
和错误信息。
1、查找alert日志目录
    show parameter dump; #即bdump目录10G
    select * from v$diag_info; 11G，增加了文本和XML格式的日志
2、告警日志结构及内容解读
    所有的内部错误（ORA-600）信息，块损坏错误（ORA-1578）信息，以及死锁错误（ORA-60）信息等。 
    管理操作，如CREATE、ALTER、DROP语句等，以及数据库启动、关闭以及日志归档的一些信息。 
        物理结构操作：例如创建、删除、重命名数据文件与联机重做日志文件的ALTER DATABASE命令，
        重新分配数据文件大小以及将数据文件联机与脱机的操作。 
        表空间操作，如DROP与CREATE命令，还包括进行用户管理的备份而将表空间置入和取出热备
        份模式的操作 
    与共享服务器或调度进程相关功能的消息和错误信息。 
    物化视图的自动刷新过程中出现的错误。 
    动态参数的修改信息。
3、监控告警日志的方案
a、通过外部表查看告警日志文件的内容。使用定制SQL语句来查询错误信息。
    create or replace directory  bdump as '/u01/app/oracle/admin/ipemsdb/bdump';
    create table alert_logs(
           text  varchar2(2000))
           organization external(
              type oracle_loader
              default directory bdump
           access parameters(
             records delimited by newline
             fields
             reject rows with all null fields)
           location(
               'alert_ipemsdb.log')
           )
           reject limit unlimited;
    select * from alert_logs where text like '%ORA-%'
4、告警日志归档
    告警日志如果不及时归档，时间长了，告警日志文件会变得非常大，查看、读取告警日志会引起额
外的IO开销。一般应该按天归档告警日志文件，保留一段时间（例如 90天），超过规定时间的删除。
参考资料：
http://www.cnblogs.com/kerrycode/p/3899558.html
http://www.cnblogs.com/kerrycode/p/3168662.html

二、statspack报告解读：
    StatsPack:oracle自带的性能分析工具，运行oracle自带脚本，生成一系列的统计表，生成
快照，通过快照采样生成报告。
安装StatsPack报告
设置job_queue_process：设置后台JOB相关进程个数；
alter system set job_queue_processess=6;
timed_statistics：设置为true，使收集的时间信息存储在动态性能视图中，会消耗资源，采集后改为false;
alter system[session] set timed_statistics=true;
创建表空间，用于保存采样数据，Statspack的报表数据存储需要最小100M左右的空间；
create tablespace perfstat datafile 'e:\hs01\dat\perstat.ora' size 100m;
安装运行@ ORACLE_HOME/rdbms/admin/spcreate.sql
生成自动采集数据任务 spauto.sql
生成分析报告 spreport.sql,输入起始和终止快照号，生成报告在用户系统目录下
卸载 spdrop.sql
清除不在需要的数据 sppurge.sql
清除所有的数据 sptrunc.sql
生成快照 exec statspack.snap 
查看产生的快照
select t.snap_id,to_char(t.snap_time,'yyyy-mm-dd hh:mi:ss') as S_Time,t.snapshot_exec_time_s from STATS$SNAPSHOT t;
删除全部历史数据
delete from stats$snapshot where snap_id;
StatsPack报告结构及使用
1.database的总体信息
数据库及系统信息
快照信息
三大缓存信息
2.负载间档（Load profile）：列出了每秒和每个事物的统计信息，监控系统吞吐量和负载变化
Redo size:每秒每事务产生的重做日志大小（单位字节），标志数据库变更频率，数据库繁忙程度；
Logical reads:产生逻辑读的oracle块数，也可以反映数据库的业务压力；
Block changes:变化块数，业务繁忙程度；
Physical reads:物理磁盘读块数，反映系统缓冲情况；
Physical writes:物理磁盘写块数；
User calls:每秒用户call次数
Parses:解析次数
Hard parses:硬解析次数；
Sorts:排序次数
Logons:登入次数
Executes:执行次数；
Transactions:每秒产生的事务数
% Blocks changed per Read:逻辑读系统改变的比例，另外部分为用于查询的比例
Recursive Call %:递归访问
Rollback per transaction %:事务回滚百分比 rollback/(rollback+commit),回滚率过多，说明数据库
                           经历无效操作
Rows per Sort:排序的行数，反映系统排序负载
3.实例的各组件的命中率
Buffer Nowait %:DB cache中获得buffer未等待的比例，<99%说明有热块，可以通过x$bh表的tch字段
                select file#,dbablk,obj,tch from x$bh order by tch desc;查到热块的文件和块号
                再通过对象号可以找到具体属于哪个对象的块。v$latch_children的cache buffers chains
                查找具体的latch
Redo NoWait %:在redo cache中获得buffer的未等待比率                 
Buffer  Hit   %：DB cache的命中率，小于90%需要调整db cache的大小；
In-memory Sort %:内存排序中的排序率
Library Hit   %:sql在共享区域中的命中率，95%左右的标准（加大共享池，绑定变量，cursor_sharing
                [FORCE | SIMILAR | EXACT]参数）    FORCE字面量，无论是否最佳，SIMILAR字面量使用最佳
                EXACT完全相同才使用                
Soft Parse %:软解析比率，反映sql重用情况
Execute to Parse %:1-parses/executions如果值小于0则说明shared pool存在性能问题
Latch Hit %:确保>99%否则及存在性能问题；
Parse CPU to Parse Elapsd %:解析实际运行时间/(解析实际运行时间+解析中等待资源时间)，反映CPU等
                            待时间
% Non-Parse CPU:解析外的时间，值很高说明系统大部分时间在执行查询操作
4.共享池在两个快照时间的总体情况
Memory Usage %:值应稳定在75%～90%之间
% SQL with executions>1:执行次数大于1的sql比率
% Memory for SQL w/exec>1:频繁使用的sql消耗内存比率
5.前5个等待事件，
%total call time=(wait times)/db time
Event(等待事件) 
Waits(等待次数) 
Time (s)(等待时间) 
avg wait(ms)(平均等待时间) 
%total call time(访问总时间百分比)
db file scattered read：该事件通常与全表扫描有关，全表扫描在内存中进行，不可能放入连续的缓
                        存区，索引可能有问题，必要全表扫描的小表可以放入keep池
db file sequential read：该事件说明在单个数据块上大量等待，该值过高通常由于表间连接顺序糟糕，
                         或者使用了非选择性索引。DB_CACHE_SIZE可以决定该事件出现的频率
buffer busy wait：缓冲区db cache不足或者如正在被读入到缓冲时，就会出现该等待。该值不应该大于1%                        
latch free：常因没有很好的应用绑定有关。闩锁是底层的互斥机制
log buffer space：日志缓冲区写的速度快于LGWR写REDOFILE的速度，可以增大日志文件，增加日志缓冲
                  区，使用更快的磁盘。
logfile switch：归档速度不够快，需要增大重做日志。
log file sync：提交或回滚数据时，LGWR将会话得重做操作从日志缓冲区填充到日志文件中，用户的进
               程必须等待这个填充工作完成修改应用的提交频率，将重做日志REDO LOG文件访在不同
               的物理磁盘上。
6.CPU的平均负载，内存统计，系统时间模型，等待事件，后台等待事件，等待事件柱状图
    s - second, cs - centisecond,  ms - millisecond, us - microsecond
7.TOP sql order by buffer gets,executions,parse calls
8.实例活动统计
9.OS统计信息
10.表空间IO统计
11.文件IO
12.buffer pool统计
13.PGA统计
14.进程统计
15.lstch活动统计
16.字典缓存统计
17.共享池统计
18.SGA统计
19.参数统计
参考资料：http://blog.csdn.net/tianlesoftware/article/details/4682329

三、AWR报告解读
    AWR(Automatic Workload Repository)自动工作负载信息库，采集与性能相关的统计数据，导出性能量
度，用以跟踪潜在的问题。快照由一个 MMON 的后台进程及其从进程自动地每小时采集一次，7天后自动清除，
快照频率和保留时间用户可以修改。
存储数据分类：
    WRM$表存储AWR的元数据（awrinfo.sql脚本）
    WRH$表存储采样快照的历史数据（awrrpt.sql脚本）
    WRI$表存储同数据库建议功能相关的数据（ADDM相关数据）
参数说明：
    statistics_level 默认是typical，在10g中表监控是激活的，建议在10g中此参数的值设为typical。如
果STATISTICS_LEVEL设置为basic，不仅不能监控表，而且将禁掉如下一些10g的新功能：
    ASH(Active Session History)
    ASSM(Automatic Shared Memory Management)
    AWR(Automatic Workload Repository)
    ADDM(Automatic Database Diagnostic Monitor)    
生成AWR报告：
    @ ORACLE_HOME/rdbms/admin/awrrpt.sql
    指定报告格式[html/txt]
    指定起始终止快照号
    指定报告名称
    生成报告默认存储在用户系统目录里
AWR报告参数调整：
    查看相关参数：
    select * from dba_hist_wr_control;
    调整AWR产生snapshot的频率和保留策略(快照30分钟生成一次，保留5天)
    exec dbms_workload_repository.modify_snapshot_settings(interval=>30, retention=>5*24*60);
    关闭AWR，把interval设为0则关闭自动捕捉快照
    exec dbms_workload_repository.modify_snapshot_settings(interval=>0);
    手工创建一个快照
    exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
    查看快照
    select * from sys.wrh$_active_session_history;
    手工删除指定范围的快照
    exec DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(low_snap_id => 973, high_snap_id => 999, dbid => 262089084);
    创建baseline，保存这些数据用于将来分析和比较
    exec dbms_workload_repository.create_baseline(start_snap_id => 1003, end_snap_id => 1013, 'apply_interest_1');
    删除baseline
    exec DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name => 'apply_interest_1', cascade => FALSE);
    将AWR数据导出并迁移到其它数据库以便于以后分析
    exec DBMS_SWRF_INTERNAL.AWR_EXTRACT(dmpfile => 'awr_data.dmp', mpdir => 'DIR_BDUMP', bid => 1003, eid => 1013);
    迁移AWR数据文件到其他数据库
    exec DBMS_SWRF_INTERNAL.AWR_LOAD(SCHNAME => 'AWR_TEST', dmpfile => 'awr_data.dmp', dmpdir => 'DIR_BDUMP');
    把AWR数据转移到SYS模式中：
    exec DBMS_SWRF_INTERNAL.MOVE_TO_AWR (SCHNAME => 'TEST');
AWR报告结构和使用：
1.数据库基础信息，快照范围信息
2.缓存信息
3.load profile
4.各内存区域命中率
5.shared pool统计快照区间内信息
6.TOP 5 timed events
7.Time model(时间模型)
8.Wait class
9.Wait Events
10.Background Wait Events
11.Operating system statistics
12.Service statistics
13.Service Wait class
14.SQL statistics(order by 消耗时间，CPUtime,buffer gets,physical reads,executions,Parse call,
Sharable Memory,Version count(子游标)) complete list of SQL text
15.实例活动统计
16.IO stats(tablespace,file)
17.buffer pool
18.Advisory statistics(实例恢复，buffer pool建议，PGA，shared pool建议，SGA，streams,Java建议)
19.Wait statistics(buffer,enqueue)
20.UNDO statistics
21.Latch statistics
22.segment statistics
23.dictionary cache stats
24.Library cache stats
25.Memory statistics
26.Streams statistics
27.Resource Limit Stats
28.init.ora parameter
TOP SQL的解析：
SQL ordered by Elapsed Time:记录了执行总和时间的TOP SQL(请注意是监控范围内该SQL的执行时间总和，
                              而不是单次SQL执行时间 Elapsed Time = CPU Time+ Wait Time)。
    Elapsed Time(S): SQL语句执行用总时长，此排序就是按照这个字段进行的。注意该时间不是单个SQL跑
                     的时间，而是监控范围内SQL执行次数的总和时间。单位时间为秒。Elapsed Time = 
                     CPU Time + Wait Time
    CPU Time(s): 为SQL语句执行时CPU占用时间总时长，此时间会小于等于Elapsed Time时间。单位时间为秒。
    Executions: SQL语句在监控范围内的执行次数总计。
    Elap per Exec(s): 执行一次SQL的平均时间。单位时间为秒。
    % Total DB Time: 为SQL的Elapsed Time时间占数据库总时间的百分比。
    SQL ID: SQL语句的ID编号，点击之后就能导航到下边的SQL详细列表中，点击IE的返回可以回到当前SQL ID
            的地方。
    SQL Module: 显示该SQL是用什么方式连接到数据库执行的，如果是用SQL*Plus或者PL/SQL链接上来的那基
                本上都是有人在调试程序。一般用前台应用链接过来执行的sql该位置为空。
    SQL Text: 简单的sql提示，详细的需要点击SQL ID。
SQL ordered by CPU Time:记录了执行占CPU时间总和时间最长的TOP SQL(请注意是监控范围内该SQL的
                          执行占CPU时间总和，而不是单次SQL执行时间)。
SQL ordered by Gets:记录了执行占总buffer gets(逻辑IO)的TOP SQL(请注意是监控范围内该SQL的执行占
                      Gets总和，而不是单次SQL执行所占的Gets)。
SQL ordered by Reads:记录了执行占总磁盘物理读(物理IO)的TOP SQL(请注意是监控范围内该SQL的执
                       行占磁盘物理读总和，而不是单次SQL执行所占的磁盘物理读)。
SQL ordered by Executions:记录了按照SQL的执行次数排序的TOP SQL。该排序可以看出监控范围内的SQL执
                            行次数。
SQL ordered by Parse Calls:记录了SQL的软解析次数的TOP SQL。说到软解析(soft prase)和硬解析(hard 
                             prase)，就不能不说一下Oracle对sql的处理过程。
SQL ordered by Sharable Memory:记录了SQL占用library cache的大小的TOP SQL。Sharable Mem (b)：占
                                 用library cache的大小，单位是byte。
SQL ordered by Version Count:记录了SQL的打开子游标的TOP SQL。
SQL ordered by Cluster Wait Time:记录了集群的等待时间的TOP SQL
参考资料：http://blog.itpub.net/26954807/viewspace-1300697/

四、ADDM报告解读
    ADDM（Automatc Database Diagnostic Monitor）数据库自动诊断监控工具收集相关的统计数据到
自动工作量知识库(Automatic Workload Repository AWR)中，而STA（SQL Tuning AdvisorSTA）则根据
这些数据，能够自动的完成最数据库的一些优化的建议，给出SQL的优化，索引的创建，统计量的收集等
建议。
    ADDM 通过检查和分析AWR获取的数据来判断Oracle数据库中可能的问题.然后ADDM会定位引起性能问
题的的根源,并提供解决的建议和预期能到到的改进效果.每次AWR快照(默认一小时一次)后,将会执行一次
ADDM分析,分析结果存在数据库中,通过OEM可以看到分析结果。
    ADDM分析的执行是从上到下的,首先确定状况,然后分析找到性能问题的根源. ADDM 使用DB time 统计
来确定性能问题.DB time是数据库处理用户请求花费的累计时间,包括等待时间和所有非闲置的用户session
的CPU时间.性能诊断的目标就是对于特定的工作量减少系统的DB time.通过减少DB time, 数据库使用同样
的资源能够支撑更多的用户请求. ADDM报告的问题区域指的就是显著占用了DB time的系统资源,它们是按照
相关的DB time 按从大到小的顺序列出的。
生成ADDM报告
    EM工具界面生成
    执行@ /u01/app/oracle/product/10.2.0/db_1/rdbms/admin/addmrpt.sql
    指定起始snapshot和结束snapshot值
    指定ADDM报告名称
    默认存放路径为用户的系统目录，根据字符集不同可能会生成部分中文的报告
ADDM报告分析范围是按照等待类别进行分类：
    内存使用
    CPU数据库时间
    SQL语句消耗（解析，commit,rollback）
    SGA大小设置
    PL/SQL消耗
    用户I/O
    过度频繁的login/logoff,内存设置偏小，导致热块的SQL，锁和ITL等待，Checkpointing导致，PL/SQL,java
    Top SQL,I/O问题，Parsing问题，数据库配置方面的问题
报告的结构和使用：
1.任务头信息，数据库及任务相关信息
2.FINDING n：#发现
    sql语句消耗数据库时间
3.RECOMMENDATION：#建议
4.ACTION:#活动，只能具体问题
5.RELEVANT OBJECT:#相关对象
6.RATIONALE:#原理说明
7.SYMPTOMS：#症状
参考资料：http://blog.csdn.net/zq9017197/article/details/16963001

五、ASH报告解读
    ASH（active session hostory）活动会话历史报告，数据来源为v$active_session_history视图
在实例级别抽取会话活动信息.记录活动会话等待的事件。包含了被捕获的每一个sql语句的执行计划.
可以使用这个信息来识别哪部分sql执行消耗了大部分的sql执行时间.
ash报告展现了以下各种信息:
    sql语句的sql标识符，执行计划标识符，执行计划的哈希值，执行计划，对象数，文件数，块数
等待事件标识符和参数，会话标识符和会话序列号，模块和操作名，服务哈希标识符，用户组标识符。
ASH报告可分析性能问题:
    短暂的性能问题通常只会持续几分钟，短暂的性能问题不在出现在addm分析中，addm试图报告指出
在分析周期内最对DB时间最有影响的性能问题。
    通过各种维度或者象时间,会话,模块,操作或sql_id的组合来进行有范围或针对性的性能分析。
生成ASH报告：
    执行@$ORACLE_HOME/rdbms/admin/ashrpt.sql
    @$ORACLE_HOME/rdbms/admin/ashrpti.sql[给别的实例生成ASH报告，DG备库等]
    html/txt
    开始时间-60 #60分钟前
    持续时间或结束时间，默认持续到当前
    报告名称
    报告默认路径linux在oracle用户目录，windows也在对应用户的目录里

报告结构及使用：
1.数据库信息及基本的取样信息
2.TOP EVENTs #被抽样会话活动中的顶级等待事件
    TOP user events #顶级用户等待事件
    TOP background events #后台登记等待事件
    TOP events values #等待事件的参数
3.LOAD Profile #抽样会话活动活动中的负载分析
    Top Service/Model #服务和模块信息
    Top client IDs #客户端ID信息
    Top Command Types #SQL命令类型
4.TOP SQL #高负载SQL语句
    top sql with top events：#等待SQL情况
    top sql with top row sources：#等待SQL和执行计划详细信息，确定那部分SQL最消耗时间
    top sql using literals：#是否使用字面值，确定对应SQL是否使用绑定变量
    top parsing module/action：#执行SQL语句的模块和操作
    complete list of sql text：#顶级sql语句的完整的文本内容
    top pl/sql：#主要等待的pl/sql过程.
    top java：#主要等待的java程序
5.TOP SESSION #主要的等待会话信息
    top blocking sessions：#主要的的阻塞会话
    top sessions running PQs：#主要等待的并行查询
6.TOP objects/Files/Latches #主要消耗数据库资源的信息
    top db objects：#主要的数据库对象(比如表和索引)
    top db files：#访问量很高百分比的数据库文件
    top latches：#很高百分比的闩锁信息
7.Activity over time #长周期中的ASH报告提供负载概要深层次的信息，将抽样时间分成10个时间段，统计
                      每个时段的信息
    列                       描述
    slot time(持续时间)      时段的持续时间
    solt count               在时段中抽样会话的数量
    event                    在时段中顶级的三个等待事件
    event count              ash抽样等待的等待事件的数量
    %event                   ash抽样等待的等待事件在整个分析期间所占的百分比
参考资料：http://blog.itpub.net/26015009/viewspace-776642/

转载于:https://my.oschina.net/peakfang/blog/2245821