Re: Simple SQL waiting on 'log file sync'

Sorry to bother you with my questions. A last question, could you please elobrate about your last point:
 (In this respect, it's a bit like
> the index that is being used as a FIFO queue,
> which is the index most likely to degenerate
> into 99% empty with most of it's data in 1%
> of the leaf blocks).

Just elobration of FIFO queue.

On 11/24/05, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> Given that we are currently guessing that
> the problem is an unlucky pattern of empty
> blocks appearing on the truncate, you might
> try to do a coalesce on the index that causes
> the problem immediately after the truncate.
>
> The 'maintain global indexes' simply does
> a bulk delete (like an sql*load in reverse)
> from the index, it doesn't rebuild it. And this
> is why you can get the empty blocks.
>
> It's important to note that the problem ISN'T
> just the truncate/maintain - it is also an unlucky
> side-effect of the nature of the index that is
> being maintained. (In this respect, it's a bit like
> the index that is being used as a FIFO queue,
> which is the index most likely to degenerate
> into 99% empty with most of it's data in 1%
> of the leaf blocks).
>
>
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
> Cost Based Oracle: Fundamentals
>
> http://www.jlcomp.demon.co.uk/appearances.html
> Public Appearances - schedule updated 4th Nov 2005
>
> ----- Original Message -----
> From: "The Human Fly" <sjaffarhussain_at_gmail.com>
> To: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
> Cc: "oracle-l" <oracle-l_at_freelists.org>
> Sent: Thursday, November 24, 2005 9:13 AM
> Subject: Re: Simple SQL waiting on 'log file sync'
>
>
> You are 100% correct in your advice. I felt the same. Can you please
> suggest me any other workaround to achieve the same, I mean to bypass
> this oddity sequences what I am following.
> It would be a great help and great relieaf for me, if you could give
> any workaround.
> Otherwise, should I include index coalesce as a practice after all the
> prior sequences done?
> My question is, when I am truncating partitions and updating global
> indexes, wont that I am rebuilding them? If so, index still left with
> empty leafs?
>
> Thanks for your time Jonathan.
>
>
>
>

--
Best Regards,
Syed Jaffar Hussain
OCP 8i & 9i DBA,
Banque Saudi Fransi,
Saudi Arabia
----------------------------------------------------------------------------------
"Winners don't do different things. They do things differently."
--
http://www.freelists.org/webpage/oracle-l