Re: I have a task to size a new db server.......
- From: Paul Drake <discgolfdba@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 8 Apr 2004 10:38:09 -0700 (PDT)
--- "Nelson, Allan" <anelson@xxxxxxxxxxx> wrote:
> And I was reading Practical Oracle 8i. Jonathon
> suggests 10,000 logical
> I/O's per 100 MHZ of CPU and also indicates that 30
> I/O's per second is
> a conservative estimate for performance of any
> single disk. He also
> points out that no matter what the size of the disk
> is, it can only
> handle about 9GB or so of "hot" data. Since I have
> the current
> production system, I can get the logical and
> physical I/O's fairly
> easily and then build from there to estimate the new
> system in an easy
> to understand spreadsheet.
>
>
> Thanks
>
> > Allan L. Nelson
> > Oracle DBA
> > M-I L.L.C.=20
> > (832) 295-2238 office=20
> > (832) 351-4180 fax=20
> > anelson@xxxxxxxx <mailto:anelson@xxxxxxxx>
Quad PIII Xeon 900 MHz 2 MB cache.
This is the interval that had the highest logical IOs
per second, had poorly performing statements after
upgrade from 8.1.7 to 9.2.0.4.
STATSPACK report for
DB Name DB Id Instance Inst Num Release
Cluster Host
------------ ----------- ------------ --------
----------- ------- ------------
mydb 1405457872 mydb 1
9.2.0.4.0 NO myhost
Snap Id Snap Time Sessions
Curs/Sess Comment
------- ------------------ --------
--------- -------------------
Begin Snap: 770 23-Sep-03 16:00:02 210
6.3
End Snap: 780 23-Sep-03 17:00:03 174
6.5
Elapsed: 60.02 (mins)
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
Buffer Cache: 1,536M Std Block
Size: 8K
Shared Pool Size: 96M Log
Buffer: 512K
Load Profile
~~~~~~~~~~~~ Per Second
Per Transaction
---------------
---------------
Redo size: 12,344.91
19,277.55
Logical reads: 207,954.99
324,738.04
Block changes: 99.61
155.54
Physical reads: 9.72
15.18
Physical writes: 1.71
2.67
User calls: 106.22
165.86
Parses: 25.38
39.63
Hard parses: 1.27
1.98
Sorts: 6.88
10.75
Logons: 0.06
0.09
Executes: 31.84
49.72
Transactions: 0.64
Here is a query to use against the perfstat data.
1 SELECT * FROM (
2 SELECT SNAP_ID, SNAP_TIME, TSNAME, RDS, BLKRDS,
WRTS, BLKWRTS,
3 RDS+WRTS IOPS,
trunc(8192*(BLKRDS+BLKWRTS)/1048576) MB
4 FROM (
5 SELECT * FROM (
6 SELECT fs.snap_id, sn.snap_time, fs.tsname,
7 sum(phyrds) - LAG(sum(phyrds)) OVER
(PARTITION BY fs.tsname ORDER BY fs.snap_id) rds,
8 sum(phyblkrd) - LAG(sum(phyblkrd)) OVER
(PARTITION BY fs.tsname ORDER BY fs.snap_id) blkrds,
9 sum(phywrts) - LAG(sum(phywrts)) OVER
(PARTITION BY fs.tsname ORDER BY fs.snap_id) wrts,
10 sum(phyblkwrt) - LAG(sum(phyblkwrt)) OVER
(PARTITION BY fs.tsname ORDER BY fs.snap_id) blkwr
11 FROM stats$filestatxs fs, stats$snapshot sn
12 WHERE fs.snap_id = sn.snap_id
13 AND tsname like 'USER%' OR tsname LIKE
'INDEX%'
14 AND to_char(sn.snap_time,'HH24') BETWEEN '08'
and '18'
15 AND
substr(to_char(sn.snap_time,'DAY'),1,1)!='S'
16 AND rownum<100
17 AND sn.snap_id>63
18 GROUP BY sn.startup_time, fs.snap_id,
sn.snap_time, fs.tsname)
19 WHERE blkrds>100
20 AND blkwrts>100)
21 WHERE snap_time BETWEEN '20031001 08' AND
'20031001 18'
22 ORDER BY MB DESC)
23* WHERE ROWNUM<11
SQL> /
SNAP_ID SNAP_TIME TSNAME RDS
BLKRDS WRTS BLKWRTS IOPS MB
---------- ------------ --------------- --------
---------- -------- ---------- ---------- ---------
1494 20031001 11 USER_DATA_LARGE 2606959
17216203 6160 6160 2613119 134549
1495 20031001 12 USER_DATA_LARGE 3166323
10784017 8476 8476 3174799 84316
1496 20031001 13 USER_DATA_LARGE 3275602
9181410 4394 4394 3279996 71764
1504 20031001 14 USER_DATA_LARGE 2326308
6195487 288 288 2326596 48404
1514 20031001 17 USER_DATA_LARGE 995840
2244822 2442 2442 998282 17556
1505 20031001 15 USER_DATA_LARGE 884595
2137212 529 529 885124 16701
1506 20031001 16 USER_DATA_LARGE 998370
1812853 12456 12456 1010826 14260
1488 20031001 10 USER_DATA_LARGE 141514
822922 514 514 142028 6433
1495 20031001 12 USER_DATA 17355
34787 1268 1268 18623 281
1506 20031001 16 USER_DATA 15285
33297 808 808 16093 266
10 rows selected.
Instance Activity Stats for DB: mydb Instance: mydb
Snaps: 770 -780
Statistic Total
per Second per Trans
--------------------------------- ------------------
-------------- ------------
CPU used by this session 771,892
214.4 334.7
consistent gets 748,516,615
207,863.5 324,595.2
748.5 million consistent gets in 60 minutes.
= 207921 consistent gets / second
= 5776 consistent gets / 100 MHz CPU
hth.
Pd
__________________________________
Do you Yahoo!?
Yahoo! Small Business $15K Web Design Giveaway
http://promotions.yahoo.com/design_giveaway/
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
