Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: I have a task to size a new db server.......
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
16 AND rownum<100 17 AND sn.snap_id>63
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 Totalper 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
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Apr 08 2004 - 12:35:04 CDT