Re: longops vs sql

Brian Peasland wrote:
> > Well, I was basing that from "Oracle SQL Tuning: Pocket Reference" by
> > Mark Gurry. He claims that those times are "typical of a medium to high
> > end machine"
> >
>
> I'm not sure I buy that advice. Also, one needs to take advice like that
> with a grain of salt as disk subsystems get faster and bottlenecks choke
> performance....lots of different things to consider.
>
> To show that 300 disk I/O operations per second is not a relevant
> factor, consider the following snapshot of my 9.2.0.7 database on
> Solaris taken with Statspack:
>
> Load Profile
> ~~~~~~~~~~~~ Per Second Per Transaction
> --------------- ---------------
> Redo size: 44,784.31 4,652.94
> Logical reads: 20,146.63 2,093.17
> Block changes: 926.25 96.23
> Physical reads: 1,007.36 104.66
> Physical writes: 102.56 10.66
>
> Of course a lot of the Statspack output was snipped. But from the above,
> I can see over 1100 *physical* I/O's per second, and my system wasn't
> even that busy during this timeframe. My number is quite a factor larger
> than the 300 that was quoted.
>
> I would never say that my number is representative of anyone else's
> expected volume. Some will be higher and others lower. There's lots to
> take in to consideration. How many concurrent users? How many physical
> disk spindles? What is the disk speed? How many controllers? Fibre
> Channel? SATA? iSCSI? Direct I/O or buffered? Does the disk controller
> perform any read-ahead functionality?
>
>
> Cheers,
> Brian
>
>
> --
> ===================================================================
>
> Brian Peasland
> dba_at_nospam.peasland.net
> http://www.peasland.net
>
> Remove the "nospam." from the email address to email me.
>
>
> "I can give it to you cheap, quick, and good.
> Now pick two out of the three" - Unknown

Thanks Brian, like my previous posts have stated, I'm new to this stuff and trying to learn all I can and get a good understanding of it all.

Wouldn't that also be based upon how many physical reads were actually required? Say if you had a huge buffer cache and rarely had to go to disk for your data, that value could be quite smaller then, correct?

I have a few snap shots from our system, so I looked at our Load Profile, it lists:

Load Profile

~~~~~~~~~~~~                            Per Second       Per
Transaction
                                   ---------------
---------------
                  Redo size:            153,299.18
3,927.45
              Logical reads:             16,661.37
426.86
              Block changes:                540.73
13.85
             Physical reads:              1,752.49
44.90
            Physical writes:                152.31

So from this I should probably assume that at that point in time we were acheiving 1,752 I/O / sec? Would I add the writes into that to see the total I/O / sec? Received on Thu Sep 07 2006 - 14:59:52 CDT