Re: What's the point of decreasing consistent gets by using IOTs?

On Thu, 17 Apr 2003 20:01:13 +0100, "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:

>
>c) Something that tests well serially on
> a single-cpu machine might not behave
> so well on a large multi-CPU system

Test was actually ran on a 10CPU machine (used a database that's no longer used in production)

>Bear in mind that under high pressure, there may
>be many people trying to do the same thing at the
>same time, though. Don't just check the
>consistent gets, check the latching (especially
>cache buffers chains latch). Repeat the tests on a
>multiple CPU system, and see if the latching is
>sufficiently heavy that you get a contention
>issue. By reducing buffer accesses you
>tend also to reduce latch acquisitions - and
>on a highly concurrent system that may be
>the more significant benefit.

So I listened to you :) Ran the test from 3 sessions concurrently (as fast as I could switch from window to window and paste the code)

First I shutdown the db, restarted, logged in from 3 sessions (all local) (each session took about 940-950secs) Then I looked at v$latch:
(all numbers close to 0 (under 10) )

After:

LATCH TYPE                                SLEEPS     MISSES SLEEP RATE
------------------------------------- ---------- ---------- ----------
cache buffers chains                       23145    3132232      0.08%

Then i restarted the db and did the same with iot's, the execution times were all around 850secs which is 10%+ better.

looked at v$latch before running the test from 3 sessions: (all numbers close to 0 (under 10) )

and after running the tests

LATCH TYPE                                SLEEPS     MISSES SLEEP RATE
cache buffers chains                       16615    2532168      0.11%

So numbers are better.. (of course this is very artificial, but we do have concurrent uses of those tables, since it's a web app) I think I am going to start convinving developers in switching to IOTs. for small and large tables.

Thanks for your input.
.......
We use Oracle 8.1.7.4 on Solaris 2.7 boxes remove NSPAM to email Received on Thu Apr 17 2003 - 15:36:32 CDT