Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Simple SQL waiting on 'log file sync'

Re: Simple SQL waiting on 'log file sync'

From: The Human Fly <sjaffarhussain_at_gmail.com>
Date: Thu, 24 Nov 2005 11:16:01 +0300
Message-ID: <97b7fd2f0511240016p2f40f051s4cafad46de1ba1e0@mail.gmail.com>


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_at_jlcomp.demon.co.uk> 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
>
>
> --
> http://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."
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Nov 24 2005 - 02:18:20 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US