oracle awr检查报告.doc

oracle awr检查报告1. 报告采集说明Awr报告可以总体反映oracle数据库的信息和运行情况，帮助快速定位数据库问题。建议生成业务高峰时段报告，如：8:00 -17:00 2. 生成awr报告方式：在服务器上以sysdba用户登录数据库sqlplus / as sysdba运行以下脚本SQL?/rdbmsadminawrrptCurrent Instance DB Id DB Name Inst Num Instance- - - - 1265912605 ORCL 1 orclSpecify the Report TypeWould you like an HTML report, or a plain text report?Enter html for an HTML report, or text for plain textDefaults to htmlEnter value for report_type: -回车，选择默认生成html格式Type Specified: htmlInstances in this Workload Repository schema DB Id Inst Num DB Name Instance Host- - - - -* 1265912605 1 ORCL orcl IBMT60Using 1265912605 for database IdUsing 1 for instance numberSpecify the number of days of snapshots to choose fromEntering the number of days (n) will result in the most recent(n) days of snapshots being listed. Pressing withoutspecifying a number lists all completed snapshots.Enter value for num_days:1 -输入要查看几天的快照，我们选一天 SnapInstance DB Name Snap Id Snap Started Level- - - - -orcl ORCL 293 10 May 2011 9:01 1 294 10 May 2011 10:00 1 295 11 May 2011 11:00 1Specify the Begin and End Snapshot IdsEnter value for begin_snap:Specify the Begin and End Snapshot IdsEnter value for begin_snap: 294 -报告开始时间对应的快照编号Begin Snapshot Id specified: 294Enter value for end_snap: 295End Snapshot Id specified: 295 -报告结束时间对应的快照编号Specify the Report NameThe default report file name is awrrpt_1_294_295.html. To use this name,press to continue, otherwise enter an alternative.Enter value for report_name: 回车，使用默认的报告名称，或者自己起个名字Using the report name awrrpt_1_294_295.html3. 查看报告当前目录下找到报告，下载到windows系统，用浏览器打开查看。 参数检查找到init.ora Parameters部分，检查数据库参数设置。Oracle数据库参数设置没有固定的标准，根据硬件和应用环境的不同，不同设置带来的效果也不同。但是每个参数有其影响的方面。部分参数说明如下：Parameter NameBegin value说明cluster_databaseTRUE是否是RAC模式db_file_multiblock_read_count16默认值16，影响全表扫面等场景的IO性能open_cursors300会话使用游标值上限，默认300，根据应用规模上调，以避免应用报错游标无法分配processes2200数据库的进程数上限，检查当前数据库实际占用的进程数量为多少，该参数是否足够大查看当前进程数：select count(*) from v$process;local_listener(ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.11.11)(PORT = 1521)默认为空，但是在RAC模式下必须设置，避免客户端连接报错session_cached_cursors20默认20，系统资源允许的情况下调高该参数，可以提高sql的缓存性能 查看top 5等待事件找到Top 5 Timed Events如下表，正常情况下CPU time处于第一位，一般占用比例超过50%，检查情况以实际系统运行情况为准。EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait ClassCPU time124,40968.5db file sequential read10,042,96625,645314.1User I/Oenq: TX - row lock contention11,7575,7074853.1Applicationdb file scattered read2,337,0974,78122.6User I/Ogc buffer busy2,543,5434,30122.4Cluster 查看命中率情况找到Instance Efficiency Percentages (Target 100%) 各项命中率接近100%最佳，其中Buffer Hit %和Library Hit %:都应该超过90，Soft Parse %最好能超过90Buffer Nowait %:99.95Redo NoWait %:100.00Buffer Hit %:99.72In-memory Sort %:100.00Library Hit %:99.56Soft Parse %:99.26Execute to Parse %:72.58Latch Hit %:99.98Parse CPU to Parse Elapsd %:84.79% Non-Parse CPU:99.11 查看IO响应找到File IO Stats查看数据文件的IO响应时间Av Rd(ms)一般20以内TablespaceFilenameReadsAv Reads/sAv Rd(ms)Av Blks/RdWritesAv Writes/sBuffer WaitsAv Buf Wt(ms)EPCDAT1/dev/vgp_data/rlv