Re: Simple SQL waiting on 'log file sync'

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.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Nov 24 2005 - 03:22:00 CST