Re: I have a task to size a new db server.......

From: Paul Drake <discgolfdba_at_yahoo.com>
Date: Thu, 8 Apr 2004 10:38:09 -0700 (PDT)
Message-ID: <20040408173809.98993.qmail@web20414.mail.yahoo.com>

"Nelson, Allan" <anelson_at_miswaco.com> 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_at_midf.com <mailto:anelson_at_midf.com>

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.

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_at_freelists.org put 'unsubscribe' in the subject line.

--
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