Re: Oracle Myths

Comments embedded...
Niall Litchfield wrote:
>
> "William Rice" <ricew_at_operamail.com> wrote in message
> news:1f1a539b.0205300805.338de47a_at_posting.google.com...
> > Thanks for the info on the tests. You've shown more motivation than
> > me. A couple of things(at least from my perspective)
> >
> > I am not sure that the tablespace you created would be contiguous on
> > disk. Especially seeing I am not Micro$oft literate I have no idea.
>
> Oh they wouldn't be. (well actually they might since the file system is
> fairly sparsely populated - what does a home user do with 80gb?). You are
> correct though, unless you use RAW devices the file system will fragment
> (and cache etc) files to a greater or lesser degree. I take the view that in
> most cases - who cares. If you absolutely optimally tune your IO what
> performance improvement will you get? 10% maybe - max.On the other hand if
> you can persuade the devs and business not to care about sequentially
> allocated id fields and just rely on a trigger sequence combo. Or to enforce
> bind variables.

My lack of Oracle knowledge is definitely showing, seeing I dont know what a bind variable, or a trigger sequence combo is, or why it will apply to me trying to optimize I/O. The environment I am considering is DSS, with quite a bit of data, and all batch processing

> >
> > If I get a box I will probably do something like the following to
> > test.
> >
> > 1. Create raw devices, or do something to make sure the space I was
> > getting was contiguous.
>
> raw device is all you can do. and besides AFAIK you can't play quake on a
> raw device :-(

Unfortunately,(or maybe not) none of my DBMS servers can run Quake :), at least that I know of...

<SNIP>
> > Note 1: You can't use the same table, or the fact it's cached would be
> > bad.
>
> Except of course that in Real life1 (TM) your data would be cached no?

If your system has to do a Seq scan of 200 GB, it won't all be cached the second time someone does it. So if you don't have 200GB to test with, or are to lazy/impatient to generate that much test data, you just have to find ways to make sure things dont get cached for you, or at least not cached as well... in order to duplicate your production environment.

If someone does an indexed lookup of 1% of the 200GB of data, and someone else does and indexed lookup on a different 1%, the performance would be different than doing two indexed lookups on the same set of data.

So essentially I am trying to do benchmarks on a smaller system to determine what can cause performance problems on a larger system. In an ideal world, I would have the box I want, and the time to generate the test data and tests. As the test boxes I end up getting are usually at least an order of magnitude smaller than my production box, I have to work with what I can.

>
> > Note 2: Make sure the data isn't cached in O/S or DBMS, if you keep
> > testing until disks are saturated though, this shouldn't be an issue
>
> see above
>
> > Note 3: if tables being scanned concurrently are in different
> > tablspaces, this would be ideal.
>

Trying to make sure that Read ahead for other queries don't end up caching data for other queries on accident.

>
> In general it seems to me that you are trying to eliminate all the caching
> and disk allocation mechanisms that your hardware and software gives you.
> Even if your results do suggest that in these circumstances smaller larger
> extents are better wouldnt you be in the position of the tourist who asked
> the local for directions and got the reply ' Arrggh I wouldn't start from
> here if I was you'.

*laughs* No, just trying to make sure I understand why my performance is good, so I can have a reasonable idea what can cause performance to drop, and what I might be able to do to prevent it. If you are doing large sequential scans(for good reason) you can't rely on things being cached. So, you need to make sure you know what will happen as your system gets used more and more. If my performance is good because of caching, but production doesn't end up getting the same kind of caching, the testing doesnt do me much good.

Will Received on Fri May 31 2002 - 11:43:59 CDT