Re: Oracle Myths

"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.
>
> 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 :-(

> 2. Contiguous extents might as well be one extent as far as my
> reasoning goes, so I would just create a table with a small extent
> size, and have an attached index with a small extent size.
>
> Procedure:
> create a table with a small extents size, and an index on a couple of
> columns with small extents on that table.
>
> Insert all your records.
>
> I am pretty sure this will cause the index and table extents to be
> interleaved?

I'd imagine so but I don't know.
>
> Do this for a few tables.
>
> Create a few tables with large extents and detached indexes in a
> different tablspace with large extents
>
> Run select on 1 table
> run select on 2 tables concurrently
> run select on 3 tables concurrently
> ...
> Until disks are saturated.
>
> Repeat with index scan
>
> Keep your disk throughput, and timings.
>
> 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?

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

why?

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

Finally I just thought I'd comment that I'm a bit pleased that something under a thousand pounds worth of home made kit can run at approximately 1/2 a million insertions per minute these days.

--
Niall Litchfield
Oracle DBA
Audit Commission UK