Re: Re: can huge updates cause locks
- From: <ryan.gaffuri@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 9 Mar 2004 8:16:53 -0500
if your in 9i, there is a really good article by arup nanda
http://www.dbazine.com/nanda1.html
its about 2 new view that help you drill down to the object for wait events. I
found it helpful.
>
> From: Hemant K Chitale <hkchital@xxxxxxxxxxxxxx>
> Date: 2004/03/09 Tue AM 07:05:24 EST
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: can huge updates cause locks
>
>
> As you have not provided information on what your findings [StatsPack,
> WaitStats, utlbstat/utlestat,
> sar, vmstat ... anything ...] were during that Peak Period, I believe
> that you might be speculating about the "ITL Waits and Latch Misses".
>
> ITL Waits would manifest as "buffer busy wait". "Latch Misses", of course,
> or waits on "latch free" -- but you need to drill down to find which latch.
>
> You might have been waiting on an Enqueue -- one or a few critical rows
> locked by a user and everyone else waiting on him.
>
> The LGWR might have been waiting on Database Checkpoints and reporting
> "unable to allocat new log" messages in the alert.log. Then, again, it might
> have been waiting for the Archiver to archive out the redo logs it has already
> switched out of before reusing them.
>
> These are the possible reasons which spring to mind immediately. There
> could very well be many others.
> Hemant
> At 12:28 PM 09-03-04 +0530, you wrote:
> >Yes ,I am sure they do,so do I .But my question was "ON PEAK TIME" when
> >all the users are accessing this table.I tried this yesterday during peak
> >load
> >(simulated) around 6.10 avg on the said box and the system became slow as a
> >snail.Plz read the last line I mentioned "IS THERE ANYTHING I AM MISSING".If
> >you know that plz tell me.
> >Deep
> > >
> > > What is your "hypothetical" explanation for why a single user with a
> > > 5000 row update causes ITL Waits and Latch Misses and "slowly but surely
> > > cripple the system" ? Are you sure about the "surely" ?
> > >
> > > A 5000 row update isn't necessarily going to cripple your system.
> > > Many people run xthousands of rows updates frequently on databases.
> > >
> > > Hemant
> > > At 04:24 PM 08-03-04 +0530, you wrote:
> > >>Hi,
> > >>First of all let me clarify this is a Hypothecal Situation.
> > >>Suppose there is a table x which houses important piece of transaction
> > >> data.Now this box happens to be a HP-UX 11.11,Dual Proc,4GB RAM Oracle
> > >> 9.2.0.1 Box having 200 users.Now say one of the users suddenly at peak
> > >> load period starts updating this table with 5000 new rows,my idea is it
> > >> will cause immediate ITL Waits,Latch Misses and slowly but surely
> > >> cripple the system.As the ITL waits will have a rippling effect I would
> > >> expect ORA-60 to turn up in the alert<sid>.ora .
> > >>
> > >>Will you plz let me knw if I am correct or there is something I am
> > >> missing alltogether.
> > >>Thanks
> > >>Deep
> > >>
> > >>
> > >>----------------------------------------------------------------
> > >>Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > >>----------------------------------------------------------------
> > >>To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > >>put 'unsubscribe' in the subject line.
> > >>--
> > >>Archives are at //www.freelists.org/archives/oracle-l/
> > >>FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > >>-----------------------------------------------------------------
> > >
> > > Hemant K Chitale
> > > Oracle 9i Database Administrator Certified Professional
> > > http://hkchital.tripod.com {last updated 24-Jan-04}
> > > "If you wish to leave your footprints on the sand, do not drag your
> > > feet"
> > >
> > > ----------------------------------------------------------------
> > > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > > ----------------------------------------------------------------
> > > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> > > put 'unsubscribe' in the subject line.
> > > --
> > > Archives are at //www.freelists.org/archives/oracle-l/
> > > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> > > -----------------------------------------------------------------
> >
> >
> >
> >----------------------------------------------------------------
> >Please see the official ORACLE-L FAQ: http://www.orafaq.com
> >----------------------------------------------------------------
> >To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> >put 'unsubscribe' in the subject line.
> >--
> >Archives are at //www.freelists.org/archives/oracle-l/
> >FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> >-----------------------------------------------------------------
>
> Hemant K Chitale
> Oracle 9i Database Administrator Certified Professional
> http://hkchital.tripod.com {last updated 24-Jan-04}
> "If you wish to leave your footprints on the sand, do not drag your feet"
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
> put 'unsubscribe' in the subject line.
> --
> Archives are at //www.freelists.org/archives/oracle-l/
> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
>
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
- » Re: Re: can huge updates cause locks
