| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> 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_at_freelists.org
> >>put 'unsubscribe' in the subject line.
> >>--
> >>Archives are at http://www.freelists.org/archives/oracle-l/
> >>FAQ is at http://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_at_freelists.org
> > put 'unsubscribe' in the subject line.
> > --
> > Archives are at http://www.freelists.org/archives/oracle-l/
> > FAQ is at http://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_at_freelists.org
>put 'unsubscribe' in the subject line.
>--
>Archives are at http://www.freelists.org/archives/oracle-l/
>FAQ is at http://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"
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Mar 09 2004 - 06:02:51 CST
 |
 |