www.Orasql.Com
 
用户名: 密码:  验证码: 注册|找回密码 oracleawen's blog  
 
         当前位置 >> 技术论坛  发表帖子     论坛版块     查找帖子
通过awr设计orion io测试方案-技术论坛||Oracle高手之路—Orasql.Com!
            通过awr设计orion io测试方案
 
发贴人:oracle_awen
oracleawen@gmail.com448315233127.0.0.1
用户级别:管理员
发贴总数:1245
注册时间:2011-05-05 09:23:03
2011-06-01 08:15:45
通过awr设计orion io测试方案 首先从awr 中找出 到instance activity stats找出physical io相关的部分 physical read io requests 2,261,504 632.74 555.79 physical read bytes ############### 30,069,201.79 26,412,095.17 physical read total io requests 2,316,706 648.19 569.36 physical read total bytes ############### 30,110,667.00 26,448,517.25 physical read total multi block requests 884,211 247.39 217.30 physical reads 13,767,646 3,852.04 3,383.55 physical reads cache 12,593,873 3,523.63 3,095.08 physical reads cache prefetch 11,271,526 3,153.65 2,770.10 physical reads direct 525,123 146.92 129.05 physical reads direct (lob) 4 0.00 0.00 physical reads direct temporary tablespace 525,109 146.92 129.05 physical reads prefetch warmup 0 0.00 0.00 physical write io requests 21,804 6.10 5.36 physical write bytes 4,315,103,232 1,207,320.42 1,060,482.49 physical write total io requests 33,242 9.30 8.17 physical write total bytes 4,361,985,024 1,220,437.45 1,072,004.18 physical write total multi block requests 23,316 6.52 5.73 physical writes 683,226 191.16 167.91 首先看一下读写比 physical read total io requests / physical write total io requests =648.19/6.10 =1061 physical read total io requests是读的总io次数,physical read total multi block requests是多块读的io次数 sigle block io requests/per sec = 648.19 - 247.39 = 400.9 ---用于计算small io iops multi block io requests/per sec = 247.39 ---用于计算mbps reads large /small = 5 8 从上面的值我们可以对系统在该时段io有个大概的了解以读为主(读写比1001),大块读/小块读 = 12,事实上这是一个数据仓库的awr 可以设计一个的测试方案 reads / writes = 90 10 reads large /small = 1 2 writes large /small = 1 2 现有系统 read mbps = physical read total bytes /1024/1024 = 30,110,667.00 /1024/1024 = 28.7 mbps read iops = 648.19 write mbps = 1 mbps write iops = 6.10 latency 如果top 5 timed evetnts 有user i/o 相关的 event,把avg wait拿过来 如果是在rac环境,以上只是其中一个节点的数据,下面就要把其他节点的数据加起来 有了以上的数据,我们可以用orion设计我们的测试方案 比如说 $orion -run advanced -testname dbw_read -num_disks 10 -size_large 1024 -type seq -matrix point -write 10 -num_streamio 10 -verbose -cache_size 0 -num_small 2 -num_large 1 $orion -run advanced -testname dbw_read1 -num_disks 10 -size_large 1024 -type seq -matrix point -write 10 -num_streamio 10 -verbose -cache_size 0 -num_small 4 -num_large 2 $orion -run advanced -testname dbw_read2 -num_disks 10 -size_large 1024 -type seq -matrix point -write 10 -num_streamio 10 -verbose -cache_size 0 -num_small 6 -num_large 3 或者跑一个长的(因为num_small/num_large不好把握,因为small总比large快) ./orion_solaris_sparc64 -run advanced -testname dbw_read -num_disks 10 -size_large 1024 -type seq -matrix detailed -write 10 -num_streamio 10 -verbose -cache_size 0 如不希望用cache,比如这次测nfs,设置了-cache_size 0 测试结果可以通过excel画个图,这个可以直接看文档 --以上,在orion文档中都有提及 这里再说一个问题,在awr期间没有结束的sql,不会计算在instance activity stats中,比如在同一个awr中(db version 10.2.0.3.0) sql ordered by cpu time resources reported for pl/sql code includes the resources used by all sql statements called by the code. % total db time is the elapsed time of the sql statement divided into the total database time multiplied by 100 cpu time (s) elapsed time (s) executions cpu per exec (s) % total db time sql id sql module sql text 340 3,488 0 48.80 31x2gj64cj3n4 sqlplus delete from n_dim_customer a w... 340 3,483 76 4.47 48.74 87tb7nc6prpc3 sqlplus select /+ all_rows / count(... sql ordered by reads total disk reads 13,767,646 captured sql account for 82.4% of total physical reads executions reads per exec %total cpu time (s) elapsed time (s) sql id sql module sql text 11,155,338 0 81.03 340.08 3487.67 31x2gj64cj3n4 sqlplus delete from n_dim_customer a w... 11,154,884 76 146,774.79 81.02 339.95 3483.10 87tb7nc6prpc3 sqlplus select /+ all_rows / count(... 其中2个sql的read mbps (11,155,338+11,154,884)8k/3488 ~= 50mbps ,远远超过只根据physical read total bytes算出的值

      
 
  
 Orasql.Com|E-Mail:oracleawen@gmail.com|Phone:15271942915|Copyright © 2011|All Rights Reserved|