1Oracle性能调优之AWR/ASH/ADDM报告解读金蝶中国基础环境支持部杨银芳2021-04-222课程收益懂得如何收集AWR报告懂得如何收集ASH报告懂得如何收集ADDM报告懂得如何分析AWR/ASH/ADDM报告通过案例的讲解加深对数据库报告分析的理解懂得如何从系统层面优化数据库3课程内容数据库报告收集与解读01020304收集AWR\ASH\ADDMAWR\ASH\ADDM分析AWR基线实际案例数据库级优化4PART1：OracleAWR/ASH/ADDM报告收集5AWR保存时间•BEGIN•DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(retention=>43200,interval=>30,topnsql=>100);•END;•/•Retention快照保存时间，此处为43200分钟（1个月）•Interval快照生成频率，默认为60，建议设置为20，30或默认值，小的间隔更容易查看出问题的具体时间。6手工生成快照•execdbms_workload_repository.create_snapshot;7收集AWR•设置收集的顶级SQL的条数（oracle11g以上支持）:•execDBMS_WORKLOAD_REPOSITORY.AWR_SET_REPORT_THRESHOLDS(top_n_sql=>100);•收集语句：•@?/rdbms/admin/awrrpt.sql本实例•@?/rdbms/admin/awrrpti.sqlRAC中选择实例号•保存路径：当前目录8RAC的AWR收集•@?/rdbms/admin/awrrpt.sql本实例•默认输出格式为html，•此处直接按回车9RAC的AWR收集•此处输入想要诊断性能问题的距今的日期的天数1010RAC的AWR收集•此处依次输入开始快照号、结束快照号。•注意：快照间隔小更利于诊断出性能问题的准确时间，不要收集时间间隔超过1小时的AWR，除非有特定目的！1111创建AWR基线•为性能度量设置什么样的阈值是合适的？要检测哪些内容？如果要了解今天的性能是否与上周、上月此时的性能不同，则必须将当前性能与基线进行比较。•优点：更简单、高效，发现细微差别！•查看snapshotselectsnap_id,BEGIN_INTERVAL_TIME,dbidfromdba_hist_snapshot;•execDBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id，end_snap_id,baseline_name);1212与AWR基线差异AWR收集•@?/rdbms/admin/awrddrpt1313与AWR基线差异AWR收集•输入参照的时间段的snapshot间隔，不一定是要基线，性能好的时间段都可做为参考。1414与AWR基线差异AWR收集•输入性能有问题的时间段的snapshot间隔1515收集ASH报告@?/rdbms/admin/ashrpt.sql1616收集ASH报告•分别输入开始时间和持续时间1717收集ASH报告•键入回车，或指定文件名完成收集1818收集ADDM报告•@?/rdbms/admin/addmrpt1919PART2：OracleAWR/ASH/ADDM报告分析2020AWR报告分析问题的应用场景分析数据库本身、SQL语句、资源性能、硬件的问题:1.数据库执行耗用时间过多2.耗CPU高的SQL语句3.物理读高的SQL语句4.逻辑读高的SQL语句5.数据库锁等待6.数据库会话阻塞7.数据库服务器磁盘读写IO能力差8.数据库服务器内存不够用9.数据库内存相关参数分配不合理导致性能慢10.SQL语句硬解析过多导致性能慢11.OracleRAC集群组件同步等待慢…….2121负载判断•DBTime÷Elapsed值越高，性能越差•DBTime=1min,ElapsedTime=60minAAS=1/60负载很轻•ElapsedTime=60minAAS=60/60=1AAS=60/60=1负载一般•DBTime=60000min，ElapsedTime=60minAAS=1000系统hang了吧？2222负载很轻的数据库示例2323负载一般的数据库示例2424负载一般的数据库示例2525负载高的数据库示例12626负载高的数据库示例22727负载高的数据库示例32828AWR分析案例11.收集时间间隔过长2.物理内存64G，buffercache只分了11G3.DBTime值很大，负载高4.物理读一般2929AWR分析案例1•TX锁引起的数据库性能问题UPDATEt_gl_voucherSETFAuditorId=:1,fbizstatus=:2,FLastUpdateTime=:3WHEREfid=:4(性能高，是受害者)3030AWR分析案例21.负载有些高2.读理读不大3131AWR分析案例23.IO奇慢结论：存储坏,IO响应速度要低于10ms3232AWR分析案例31.收集时间间隔过长,搞平均主义2.Buffercache稍小3333AWR分析案例33.Physicalread达到527块次，物理内存就明显小了3434AWR分析案例34.日志切换耗费过长5.逻辑读过多解决办法：建6组500M日志文件，增加物理内存，增大buffercache3535AWR分析案例41.数据库负载很低，EAS慢2.SQL*Netmoredatafromclient事件出现表示网络问题严重3636AWR分析案例5--比较奇葩AWR报告1.数据库负载非常高2.用户IO磁盘平均等待69ms,远超6ms,存在严重的磁盘IO性能3737AWR分析案例5--比较奇葩AWR报告1.数据库磁盘IO每秒读944.4M,但每秒写5.2M2.为什么每秒写那么多，每秒读那么少？3.每秒读944M,是否达到了磁盘读峰值？4.是否磁盘真读了944M数据？5.操作系统或存储级别监控磁盘每秒读写有多少M?6.通过在有问题的数据库中对测试表插入上百万数据，看插入数据总耗时，然后跟笔记本电脑插入上百万数据进行对比分析插入时间耗时有没有问题？3838AWR分析案例5--比较奇葩AWR报告3939AWR分析案例5--比较奇葩AWR报告•并重启了对参数所有数据库服务器节点，并且确保没有其它业务在做，此时做创建表测试。•settimingon•createtabletest1asselect*fromdba_objectswhererownum<50001;•insertintotest1select*fromtest1;•insertintotest1select*fromtest1;•insertintotest1select*fromtest1;•insertintotest1select*fromtest1;•createtabletest2asselect*fromtest1;•80万行的表需要两秒多，比普通的笔记本上的数据库创建时间1秒还要慢。通过设置oracle10046事件，发现跟踪文件中磁盘的写入响应时间在四毫秒到十几毫秒不等，最长时达到四十多毫秒。比普通的笔记本上的写入速度要慢。4040ASH报告分析案例①②③④精准定位到了产生TX锁的sql语句、全表扫描耗时的SQL语句。针对性的对语句进行优化。4141ADDM报告分析案例1.数据库服务器物理内存不足，内存参数分配不合理2.磁盘IO性能差3.卡片数据删除引发了严重的行级锁等待4242PART3：AWR基线实际案例4343AWR基线报告实际案例分析•为性能度量设置什么样的阈值是合适的？要检测哪些内容？如果要了解今天的性能是否与上周、上月此时的性能不同，则必须将当前性能与基线进行比较。•优点：更简单、高效，发现细微差别！•查看snapshotselectsnap_id,BEGIN_INTERVAL_TIME,dbidfromdba_hist_snapshot;•execDBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id，end_snap_id,baseline_name);•@?/rdbms/admin/awrddrpt.sql我们一起来回顾下AWR基线报告生成的方法？？？4444AWR基线报告实际案例分析1.第二个快照集负载要比第一个快照集高2.两快照集数据库参数分配内存值一样4545AWR基线报告实际案例分析1.第二个快照集每秒生成的redo日志文件有所减少2.第二个快照集物理写比第一个快照集差异了46%，物理读能力有较大提升3.第二个快照集出现了严重的行级锁导致数据库性能有所下降4.两快照集都存在些系统IO性能，IO不稳定4646AWR基线报告实际案例分析第二个快照集存在严重的删除BOT关系表，销售分录表执行耗时，更新销售出库单表存在严重的锁等待应用问题，而第一个快照集没有那块的性能问题4747PART4：数据库级优化4848收集schema统计信息Dba_tables或user_tables查表的统计信息分析日期begindbms_stats.gather_schema_stats(ownname=>‘LUO',cascade=>TRUE,estimate_percent=>100,degree=>32,no_invalidate=>DBMS_STATS.AUTO_INVALIDATE,granularity=>'AUTO',method_opt=>'FORALLCOLUMNSSIZEAUTO',options=>'GATHER');end;/4949收集表统计信息BEGINDBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'LUO',TABNAME=>'OBJECTS',ESTIMATE_PERCENT=>100,DEGREE=>8,METHOD_OPT=>'FORALLCOLUMNSSIZEauto',GRANULARITY=>'ALL',CASCADE=>TRUE);END;/5050查看统计分析、直方图•SELECTCOLUMN_NAME,NUM_DISTINCT,DENSITY,NUM_NULLS,AVG_COL_LEN,HISTOGRAM,NUM_BUCKETSFROMDBA_TAB_COL_STATISTICSWHEREtable_name='OBJECTS'ANDOWNER='LUO';5151备份spfile,pfile•备份方式1：（pfile为纯文本，可修改)•createpfile='c:\init_eas.ora'fromspfile;•备份方式2：•复制spfile（spfile为非文本，可另存再修改)5252使用pfile启动数据库•如果spfile无法启动，可修改pfile的值，再用pfile启动5353修改数据库参数•若不设置memory_target，可将sga_max_size、sga_target设置为物理内存的70%左右，pga_aggregate_target设置为物理内存的10%；•db_cache_size应设置为物理内存的60%左右（如果内存大于128G，比例可以适当增大）•shared_pool_size应设置为物理内存的10%~15%左右•processes应设置大于500参数具体值可参考以上附件，对号入座5454修改数据库参数•数据库参数设置不当，再强大的服务器也无法发挥它的性能•若设置memory_target，可将该值设置为物理内存的80%左右，sga_max_size、sga_target、db_cache_size、shared_pool_size和pga_aggregate_target设置为0。5555表碎片整理•表无序•高水点位高•碎片严重•altertableschema.tablename1enablerowmovement;•altertableschema.tablename1move;•altertableschema.tablename1disablerowmovement;•重建索引5656总结•学会几个数据库的命令脚本的使用：awrrpt.sql,ashrpt.sql,addmrpt.sql,awrddrpt.sql•通过awr报告分析，分析服务器资源负载是否有问题，分析耗时SQL语句，分析数据库锁，分析数据库参数分配是否合理，分析服务器硬件、存储设备是否有问题。•通过ASH报告的分析，精确定位数据库锁产生的SQL语句等。•不懂数据为AWR报告分析，通过收集数据库ADDM报告，通过Oracle自动分析AWR报告的ADDM报告分析数据库性能。•通过建立基线，分析基线报告，进行不同时段数据库报告对比分析，找出差异点，针对性解决问题。•最后通过一些常规的数据库参数、统计分析、碎片整理优化数据库性能。5757课后练习•通过命令收集数据库1~2小时内的AWR报告一份•通过命令收集数据库1~2小时内的ASH报告一份•通过命令收集数据库1~2小时内的ADDM报告一份•针对以下提供的AWR报告分析数据库存在哪些性能问题58Thanksterimakasih感謝谢谢ありがとうขอบคุณ
