Re: Simple SQL waiting on 'log file sync'
- From: The Human Fly <sjaffarhussain@xxxxxxxxx>
- To: jonathan@xxxxxxxxxxxxxxxxxx
- Date: Thu, 24 Nov 2005 11:16:01 +0300
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.
Thanks once again for sharing this info with us.
On 11/24/05, Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx> wrote:
>
> I missed the start of this
>
> Which version of the database.
>
> Given your comment about a large index being
> rebuilt, it may be you've hit a bug/anomaly where
> leaf blocks splits cause recursive transactions
> that have to be rolled back because the split
> demands an empty block which is on the free
> list but still in the index-tree. Under certain "exotic"
> circumstances, the call can fail and rollback after
> most of the splitting work has been done. This
> means another leaf block has to be found, and
> the process repeated. The workaround is to
> coalesce the index at 'low-stress' moments so
> that the empty blocks are detached from the
> index-tree structure.
>
> Did you also see a high number of 'transaction
> rollbacks' at the same time ?
>
>
>
>
> 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
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Best Regards,
Syed Jaffar Hussain
OCP 8i & 9i DBA,
Banque Saudi Fransi,
Saudi Arabia
----------------------------------------------------------------------------------
"Winners don't do different things. They do things differently."
--
//www.freelists.org/webpage/oracle-l
Other related posts:
