ORACLE性能优化工具整理

所需积分/C币:6 2017-11-16 14:16:08 1008KB PDF
收藏 收藏 2
举报

关于Oracle性能优化的相关工具的介绍,如AWR,ASH,ADDM等的详细原理介绍及实践使用,非常有用
0 racle性能调整工具-AWR、ASH、ADDM 基本概要 逝允会话断开, aSsession,w$ Session wait信息会删除0g中新出现了v$ session wait history视图保存每个活动 session 在 assession靚it中最近10次等待事件.但这远远不够对于数据性能状沉监测的,所以后添加了一个视图: v$ active session history。这就是ASH Active Sessicn llistory ASⅢ:以每嗝1秒钟将当前活动的 session的信恳记录在SGA的一个冲区(循环使用)中,采样闾 隔时间由 ash sampling interval参数确定。(采样调用后台进程:ML)而內存记录数据有限而且数据库重启(v$动态性能 视图将重新初始{)所有的ASH信息都会消失,为保存力史数据,引入了自动负载信息库( Automatic Workload Repository(AWR 用后台进程:MMN)在A驵信息写满后会调用MMCN将采集信息写出到AWR负载库中,不接受ASH全部写,一般只写入收集的 10%的数据量,(v$ active session history通过MMoN,MNL后台进程默认 ASH Buffers中采栏一次数据定期(每小时1次)地被 刷新到AWR负载库中,且保留一周来分析)写是使用 direct path insert完成,尽量减少日杰的生成,从而最小化数据库性能 影响 ADDM( Autonatic Database Diagnostic Monitor AWR)(自动化诊断工具 The Automatic Database Diagnostic Moni tor (ADD) analyzes data in the Automatic Workload Repository (AWR)to identify potential performance bottlenecks. ADlM:0race闪詺的一个顾问系统,能够自动的完成数据琫的一些优仁建议(awr怏照信息给予诊新),给"S的优化,索引 的创建,统计量的收集等硅议 sql tuning advisor单纯的考虑sql话句不考虑对象/负载: sql access advisor全面的考虑sql对象负载情况 注意事项 1)awr果留时间:(10g7天,1lg8天soL> select snap interval, retention from dba hist wr control;) mon后台进程会以每30分钟自动负责收集和清理aMr自动负载仓库数据一次,7或8天之前的 snap shot,而和 sysaux表空间使用率没有关系 2)如想在10:00>10:30期间也生成一次报告只需在10:30时只需动创建快照 SQL> exec dbms workload reposi tory create snapshot 再awr看卜会生成10:30的快照ID Instance DB Name Snap Id Snap Started Level SINODB 113520A:g201310:00 13620Aug201310:30 3) snap ID1135、1136要联系且期闻不能 shut down 原因:重启动态性能视图将重新初始化 ORA 20200: The instance was shut down between snapshots 1135 and 1136 看库中多少个动态性能视图: SELECT* FROM V$ FIXED TABLE C where c.TYPE=’VIEW;(UsER$以$结尾的是数据库自带 的字典表) AWR( Automatic Workload Reposi tory 官网介绍 A WR: (Oracle Database 10g new tools Automatic Workload Repository (AWR). A built-in repository in every Oracle database (a repository of historical formance data that inc lud native statistics for th ions, individual SqL statement staistics are the foundation of perf tun ing the gathering of da tabase statistics for problen detection and tuning), Oracle Dataoase periodical ly makes a snapshot of its vital statistics uCorualion and stores themr A Lhe fuurlalion for database se f-rlal can fast-speed diagnose problems and To solve the bottleneck) The database storEs ecent AWR stal istics in the SGA. By de fault, the MMON prucess gathers s lal ist ics every hour and creates an AWR snapshot, A snapshot is a set of performarce statistics captured at a specific time. The database writes snapshots to the SYSALX tablespace. AWR manages snapshot space, purging older snapshots according to a abl i default keep 7 days) An AWR baseline:默认永久保存,手动扒行 drop baseline(删除基线 is a collection of statistic rates usually taken over a period when the system is performing well at peak load. You can specify a Fair or range o=AWR snapshots as a baseline. By using an AWR report to compare statisties captured during a period of bad performance to a baseline, you can diagnose problems. An automated maintenance infrastructure known as AutoTask i- lustrates how Oracle Database uses AWR for self-management. By analyzing AWR data, AutoTask can determine the need for maintenance tasks and schedule them to run in Oracle Scheduler naintenance windows. Examples of tasks inc lude gathering statistics for the optimizer and running the Automatic Segment Advisor 拓扑图( topologi cal graph) Databas System Global Area(sGA Statistics MMON SYSAUX Tablespace VEACTIE SESSFN HISTORY WRHS ACTIVE SESSIDN HISTORY SGA 60分钟 内存中的 各关 快 统计信息 ADDM专分析系统 ADDM 结果 EM WR信息 ADDM结果 abeer.cor 1.MON默认每隔60分钟(可以调整)将 ash buffers中的数据的1/10 flush到磁盘,采集釣效据俣留讨间(7天) 查看: select* from dba bist wr control 例如:修改为收集快照频率20分钟,保留数据2天: begin workload repository. modify snapshot settings (interval=>20 retention =>2*24 *60) 2.MMNL默认当 ash buffers满66%的时候将 ash buffers中的1/10的数据写入德盘(具体1/10是哪些数据,遵循FIFO 原则) 3.MNL写入的采用数据百分比10%表示的是写入磁盐的数据占 ash buffers中采样数据量的百分比(而不是占asn buffers总大小的比例) 收集来源,方案、级别 收集来源 A用的多个表来仔储采集的性能统计数据,表都存储在 SYSAUX表空间中SYS月户下,并且以WMS*和WRH*,WRI$*,WR$* 的格式命名。AWR的历史数据主要存储在基砧表wrh$: tive session his tory(分区表) WRMS 类型存储NWR的元数据信息(如检查的数据库和采集的快照),M代表 me tada ta( awrinto.sql脚本 WrH$* 类型保存采样快照的历史统计数。H代表“历史数振”( ewrrpt.sql脚本) WRIS e 类型表示存诸数据库建议功能 advisor)相关的数据(ADM相关数据) WrR$ 代表的是1新功能 Work load capture以及 Workload Replay柑关信总 在这些表上枃硅了几科带前缀 DBA HIST的视图,这些视图可以用来编写您自己的性能诊断工具。视图的名称直接与表相关 例如,视图 DBA HIST SYSMETRIC SUMMARY是在WRH$ SYSMETRI SUMMARY表上构建的。 注意: ASH保存了系统最新的处于等待的会话记录,可以用米诊断数据库的当前状态;而AR中的信息最长可能有1小时的延迟(虽然 可以手τ调整),所以其采样信息并不能用丁诊断数据的当前状态,但可以用来作为一段时期内数据库性能调整的参考。 AWR数据提取 其实AR记录的信息不仅是ASH,还可以收集到数据库运行的各方面统计信息和竺待信息,用以诊断分析。 A阳R的采样方式是,以固定的时间闻隔为其所有重要的统计信息和负载信息执行一次采样 并将采样信息休存在AWR中。可以这样说:ASH中的信息被保存到了AWR中的视图 wrhs active session history中。AS是 的真子集。 这些采样数据都存储在 SYSAUX表空间中,当 SYSAU表空间满后,AWR将自动覆盖彐旧釣信息,并在警告日志中记录一条相关信 ORA-1688: unable to extend table SYS. WRHS ACTIVE SESSIoN HISTORY partition WRH$ ACTIVE 3533490838 1522 by 128 ir tablespace sysaUX(在 alert log中)通过修改 interval参数可以修改awr信息的采样频率。最小的值是10分钟,默认的是 60分钟.典型的值是10,20,30,60,120等等。把 interva1设为0则关闭自动捕捉快照.如将收集间隔时间改为30分钟一次。并 且休留5大时间(注:单位都是为分钟) 收集信息的视图含义 Tables that AWR uses to collect statistics vssys_time_mo de ime model stats (cb time, java execution time, pi/sql execution time, etc) eIn sla.ts (vg I idle ticks, eto giservice stats wait. statist.ics (ch cpu, app wail. time, user c mits, etD) ySsesstat session stats alter system set statistics level typica ctive alter system set statistics level=all De-activa alter system set statistics level bas: c Display show parameter statistics lovel Snapshot configuration exec dbms workload repository. modify snapshot settings interval->60, retention->43200); Change snapshotting interval ninutes values tention Display values select s from dba hist wr control Snapshot Mana gement Create a snapshot exec dbms workload repository. create snapshot Delete snapshots exec dbms_workload repository. drop_snapshot range (low_snap_id==>1077, high snap_ id->1078 Create a baseline exec dbms_workload repository. create_baseline(start_snap id->1070, end snap-id->1078, baseline_name=> Delete a eline exec dbms_workload repository. drop_baseline(baseline Normal Ba seline', cascade=> FALSE Display snap shots select srap id, begin interva- time, end interval time from dba hist snapshot order by 1: View the repository select table-name from dba tables where tablespace_rame= ' and substr(table_name. 1.2)='WR'and tables Uscful vicws dba hist active sess history ASH info (see below) dba hist base line base line into dba_hist_database_instance environment data dba hist sql plan sql execution path data dba hist wr control AWE &s dba hist snap shot snapshot info in the AwR the script will ask for begin snapshot and ard snapshot ard will be generated in text format wrapt. sql Note: reports went in Oracle home\db l\bin the script w=ll ask for begin snapshot and er d snapshot and will be generated in HTML format rrti. sql Note: r nt in soracl \db 1\b 收集级别参数 To active the AWR change the system parameter statistics level to one of three values basic- this option disables the aWr (Collect on of basic informatic typical (default)-activates standard level of collection (Most of the informa ticn collected)(eg:Buffer Cache Advice, MTTR Advice, Timed Statistics, Segment Level Statistics, PGA Advice ..,. and so on: Fetch information: select statistics name, activation level from vSstatistics level order by 2: 11- same as typical but includes execution plans and timing info from the o/S(except typical parameter collect infornation Also includes plan execution statistics, Timed OS statistics) TIMED STATISTICS指定是否收集于时间相关的统计信息,当没置为TURE时会获取有关时间的更多信息,从而帮助我们来优化 DB,如果 STATISTICS LEVEL设置为 TYFICAL或者ALL,那么 TIMED STATISTICS则默认为true。如只 STATISTICS LEVE设置 为3ASIC,则 TIMED STATISTIC3默认为 false。 收集方案 DAWR SQL>2?rdbms/admin/awrrpt sql 路径:cat$ ORACLE HOME/ rdbms/ admir/ awrrpt.sq1 dB Id DB Name Inst Num instance 3538310204S1NODB I innodb Specify the Re Would you ike an ITML report, or a plain text report? Enter- for an HTML report, or text for plain text Defaults to html Ente- value for report. type Type Specificd: html Tns tances i n th is Workload Repository sc:. DB⊥d1 nst Num db name Instance Host 3538310201 1S⊥NUDB sinodb 3538310204 for database Id Using 1 for instance number Specify the number of days of snapshots to choose from Entering the number of days (n will result in the most recent (n) days of snapshots being listed. Pressing <return>without specifying a number -ists all completed snapshots Enter value for nun days:1->取整 Listing the 1 ys Completed Instance p Id Snap started level sindh SINODB 113420Aug201309:00 113520Aug201310:00 Specify the Begin and End Snaps.ct Ids Enter value for begin snap: 1134 Begin Snapshot Id specified: 1134 End Snapshot Id specified: 1135 pecify the Report Nam The default report f:le name is awrrpt 1 1134 1135. html. To use this name press <return> to continue, otherwise enter an alternative 2)AWR baseline s>?/ rdbms/admin/awrddrpt sg1-2路: cat SORACLE HOV/ rdbms/ admin/ awrddrp.sq(C要执行 d Current Instance 35383102043538310204 SINODE sindh Specify the Report Ty Would you ike an HTML report, or a plain text report? Enter. for an HTML report, or text for plain text Defaults to htm Enter value for report type Type Specified: html Instances in this Workload Repository schema db Id Inst Num db name Instance Dost 3538310201 1S⊥NUDB SIno fdb Database Id and Instance Number for the First Pair of Snapshot Using 3538310204 for Database Id for the first pair of snapshots Using 1 for Instance Number for the first pair of snapshots Specify the number of days of slapshot. s 1. 0 choose: from ANN\NNNAUNNAUALNNVNNNAINANNVNNUANNANNWNNNNNANN\N Entering the number of days (n) will result in the most recent n) days of snapshots being listed. Pressing <return> without specifying a number -ists all completed snapshots Enter value for num days: 1 Listing the last days Completed Snapshots Instance DB Nane Snap Id Snap Started Level simod SINODB l13820Aug201312:00 113920Aug201313:00 111020Aug201314:00 114120Aug201315:00 Specify the First Pair of Begin and End Snapshot Ids Enter value for hegin snap: 1138 First Begin Snapshot Id specific: 1138 Enter value for end snap: 1139 First End Snapshot Id specifiec: 1139 Instances in this Workload Repository schema DB Id Inst、 lum db Name Instance Host 米3538310204 1S⊥NUDB innodb fdb Database Id and Instance Number for the Second Pair of Snapshots △内八,/汽 Using 3538310204 for Database Id for the second pair of snapshot s 1 For Instance Number for the second pair of snapshots ber of days of Entering the number of days (n, will result in the most recent (n) days of snapshots being listed. Pressing <return> without specifying a number lists all completed snapshots Ente- value for nun days2: 1 要比较的另个时间段的天数选择 Listing the last day' s Completed Snap shots Snap stand nap Snap Started Level SINODB113820Aug201312:00 114020Aug201314:00 114120Aug201315:?0 Specify the Second Pair of Besin and End Sr. apshot Ids Enter value for begin snap2: 1140 Second Begin Snapshot Id specified: 1140 Ent Le for end snap2: 1141 Second End Snap shot Id Nar The default report file name is awrdiff 1 1138 1 1140. htm To use this nane 1.1 Enter value for report name 报告图 Go of DB fime Time(seconds) Time per Trans(seconds) statistic Name st nd Diff 1st 2nd eDiff Znd Diff R iIH Iin0 nn 10nn n nn i,10897 88n 53 -1471228 n[?-99 353112810897281:810.840c in ourd PL/SQL rpc elayed time 0.00235723.8 0.0020369100.00.000c0 parse time elapsed 768291521467542508422350.180c94 herc parse elapsed time 7:927121973|74:23821:010.170c0-100 94297411788913357:330.832.150.c AWR报告详情 : 快照周期婁选择能够代表性能冋遨的时间段,如果忺照周恚跨度太长,且包含大量的数搓库空困时冋,分析结果没唅意乂 Ins tance信息 DB Name DB d Instance Inst num startup Time Release RAC KFDB 3776141240kfdb 109-J-131208 112.0.30NO 系统信息 Host Name Platform CPUs Cores Sockets Memory【GB TYEa111 nUx x85 64-bit 16 15.5 快照信息 Snap Id Snap Time Sessions Cursors session Begin snap:「2172220-1318000 End Snap 218823-Aug-1310:0:35 174 2.1 Elapse 580.49mins DB Time 3. 053.35 (mine Elapsed表示整个AMk报衣统计的时间长度(23-ug-1310:00:35-22-Mug-1318:00:06) Cpu时司:960.49CPU核数-8(命令: grep core s/proc/ cpuirfo unig)(960.498-7683.92) υ 3Time是记录的服多器花在数据庳运算(非后台进程)和等待(非空闲等待)上的时间 DB Tie= (pu 1.IIe+ wait lime(不包含空闲等待)(非后台进程) 如果 DB Time远远小于 Elapsed时间,说明数据库比较空闲(测试:不常月的实例 Elapsed:600.29(nins), DB Time:0.80 mins)) 在960分钟里(其间收集了2188-2172次快照数据),数据耗时3053分钟 cpu就共有16(C个数)*96C=15360分钟, DB time为3053分钟,则:cpu花费∫3053分钽在处理 Oracle非空困等待和运 算上(比如逻辑读),也就是说cpu有3053/15360*100%-19.87%花费在处理0 rac le的操作上,这还不包括后台进程 从 report的 Elapsed time和 DB Time就能大概了解t的负载,计算公式可参考为:cpu负载- DB Time/(cpu数* lapsed)*100% SGA各个区域的大小‘ Cache sizes Becin End uffer Cache 5oBM Std Block Size Shared Pool siz 2448M 2, 448M Log Buffer. 1784cK 日前数据库状态‘ Load Profile

...展开详情
试读 34P ORACLE性能优化工具整理
立即下载 低至0.43元/次 身份认证VIP会员低至7折
    一个资源只可评论一次,评论内容不能少于5个字
    英年攻城狮 介绍挺详细
    2019-09-15
    回复
    • 分享达人

      成功上传6个资源即可获取
    关注 私信 TA的资源
    上传资源赚积分,得勋章
    最新推荐
    ORACLE性能优化工具整理 6积分/C币 立即下载
    1/34
    ORACLE性能优化工具整理第1页
    ORACLE性能优化工具整理第2页
    ORACLE性能优化工具整理第3页
    ORACLE性能优化工具整理第4页
    ORACLE性能优化工具整理第5页
    ORACLE性能优化工具整理第6页
    ORACLE性能优化工具整理第7页
    ORACLE性能优化工具整理第8页
    ORACLE性能优化工具整理第9页
    ORACLE性能优化工具整理第10页
    ORACLE性能优化工具整理第11页

    试读已结束,剩余23页未读...

    6积分/C币 立即下载 >