RE: lock type of analyze

From: Jeremiah Wilton <jeremiah_at_ora-600.net>
Date: Sat, 6 Aug 2005 07:54:59 -0700 (PDT)
Message-ID: <Pine.LNX.4.60.0508060742470.5573@cpq7598>

> "Aggarwal, Meenakshi" <Meenakshi.Aggarwal_at_fishersci.com> wrote:
>
> > I guess analyze table compute statistics applies some kind of lock
> > on the table unless used with online option.

On Sat, 6 Aug 2005, raja rao wrote:

> Do we have ONLINE option (and for dbms_stats too ?)
> and what mekes the difference if I use ONLINE option.

Only "analyze table validate..." (used for diagnostic purposes) has ever locked the table, going back many versions. The other syntaxes (compute, estimate) don't use the ONLINE clause, so that isn't something you should specify for gathering stats.

In 9i and 10g you should be using DBMS_STATS. According to the documentation, analyze for gathering optimizer stats is only supported for backwards compatibility.

If you think about it, why should this kind of operation have to lock the table? You are just reading the data to determine size and distribution. Readers don't block in Oracle. Can you imagine big companies with highly-available systems taking their application down to gather optimizer stats on a half-Tb table?

Also if you are giving advice to thousands of people, guessing might not be the best tactic. Experimentation and the documentation (in that order) are far more reliable.

--
Jeremiah Wilton
ORA-600 Consulting
Emergencies - Seminars - Hiring
http://www.ora-600.net



>

> -----Original Message-----

> From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of raja rao

>

> Does the analyze table compute statistics (or dbms_stats) statement  lock the table

> in anymode

--
http://www.freelists.org/webpage/oracle-l

Received on Sat Aug 06 2005 - 09:57:01 CDT