Re: Surprising Performance Changes with Oracle 11.2.0.1 (Long Post)
Date: Sat, 5 Sep 2009 09:14:43 -0700 (PDT)
Message-ID: <d205bc1c-17ca-4395-8f6f-f150ca9dd6e0_at_f10g2000vbf.googlegroups.com>
On Sep 5, 11:17 am, "Matthias Hoys" <a..._at_spam.com> wrote:
> "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote in message
>
> news:stydnRbjqr7nYjzXnZ2dnUVZ8nSdnZ2d_at_bt.com...
> >> "Charles Hooper" <hooperc2..._at_yahoo.com> wrote in message
> >>news:1ea99c67-8713-4ece-a0f5-85f66851b016_at_v2g2000vbb.googlegroups.com...
> >> I am working on an Oracle performance related project, so I thought
> >> that I would compare the performance of Oracle database 11.1.0.6 (on
> >> 64 bit Linux), 11.1.0.7 (on 64 bit Windows), and 11.2.0.1 (on 64 bit
> >> Linux).
>
> > ...
>
> > Charles,
>
> > Interesting figures.
> > A couple of follow-on questions:
> > How much memory in the machine ?
> > How many CPUs
> > Were you running the client locally - ipc or tcp
> > How much CPU usage "outside" Oracle in each test
> > Any significant variation in 'prefetch' blocks
> > Any other significant variation in stats
>
> > --
> > Regards
>
> > Jonathan Lewis
> >http://jonathanlewis.wordpress.com
>
> > Author: Cost Based Oracle: Fundamentals
> >http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> > The Co-operative Oracle Users' FAQ
> >http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
> And what disk subsystem did you use? DAS or a shared system?
>
> Matthias
Direct attached, so the only buffering was in Oracle's buffer cache and the 32MB of cache memory built into each of the drives. The problem, I believe, is caused by the drives having to constantly reposition their heads for each random I/O - I believe that the drives have either a 5ms or 6ms random I/O time rating. The average time for the db file sequential read wait is 5.942ms, which I believe is considered very good for a random I/O.
The trace file shows that Oracle keeps jumping between reads of the index, followed by several usually parallel reads (I believe that each of these block reads would also be considered a random I/O) of the table blocks.
On an unrelated note, it may take over an hour to generate the sample T1 table, so fair warning for anyone wanting to try the test on their system.
Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Sat Sep 05 2009 - 11:14:43 CDT