Re: Simple SQL waiting on 'log file sync'
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <sjaffarhussain@xxxxxxxxx>
- Date: Thu, 24 Nov 2005 09:07:18 -0000
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@xxxxxxxxx>
To: <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
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.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
