Re: Simple SQL waiting on 'log file sync'

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: "The Human Fly" <sjaffarhussain@xxxxxxxxx>
  • Date: Thu, 24 Nov 2005 09:19:15 -0000


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@xxxxxxxxx>
To: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
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.



--
//www.freelists.org/webpage/oracle-l


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 11-2005