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.

On 11/24/05, Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:
>
>
> Syed,
>
> I haven't had time to construct a reproducible
> example to demonstrate it yet, so I don't know
> how far-ranging this anomaly might be, but when
> you are dealing with partitioned tables and global
> indexes (particularly a small number of partitions)
> the sequence of events
> create holding table as select subset from one partition
> truncate partition maintaining global index
> insert subset from holding table
> is probably going to maximises the chances of the oddity
> appearing. It's quite likely to leave lots of empty blocks
> in the index, and then start filling adjacent blocks that
> start splitting and demanding some of the empty one -
> and then you're heading into the problem area.
>
>
> 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_at_jlcomp.demon.co.uk>
> Cc: "oracle-l" <oracle-l_at_freelists.org>
> Sent: Thursday, November 24, 2005 8:16 AM
> Subject: Re: Simple SQL waiting on 'log file sync'
>
>
> Jonathan,
>
> I would like to thank you from the bottom of my heart. After reading
> your reply to 'waiting on log file sync' I have solved one of my
> problem which I was struggling past two days to resolve.
> The scinario is like this. In our bank, we have a datawarehouse
> database and we keep 5 + 1 current month partitions data and whatever
> previous months, we just keep only the last day of data in the
> partitions and deletes rest of the data.
> What I usually use to do is that, putting the last day data into a
> temp table and truncate table with update global indexes, this table
> have global indexes, and then insert the data into this partiion from
> the temp table. But, for one table, which is small volume of data
> compartively other partitioned tables, giving we very wired problem.
> When I am putting data back to this partition from the temp table,
> after 10 min. oracle just waits on log file sync, giving restricted
> rowid of one index on this table, I can see this from OEM. It was for
> ages, I had to kill the process after couple of hrs.
> After reading your reply, it suddenly clicked in mind to coalesce the
> indexes and I did the same for the indexes of this partitioned tables.
> And, I am happy to say that its back to normal. I mean, data has been
> copied from temp to this partitions as expected time.
>
>
>
>

--
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