Re: Simple SQL waiting on 'log file sync'

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.

--

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