Re: Oracle Myths

William Rice wrote:
>
> 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

In terms of bind variables, try the following:

declare
 x number;
begin
  for i in 1 .. 50000 loop
    execute immediate 'select '||i||' into :b1 from dual' into x;   end loop;
end;

which is 50000 different SQL's being run...Then try

declare
 x number;
begin
  for i in 1 .. 50000 loop
    select i into x from dual;
  end loop;
end;

which is a single SQL being run 50000 times (thanks to bind variables).

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."