RE: Analyze table and locking

It is a forum post I believe, I think I did a search on "analyze lock". I have seen numerous articles were Oracle claims locking during analyze, that is the only one I found with quick parusal. Like many other things, Oracle is to blame on this old wives tale.

"Walking on water and developing software from a specification are easy if both are frozen."

Christopher R. Spence
Oracle DBA
Fuelspot

-----Original Message-----
[mailto:Bruce.Reardon_at_comalco.riotinto.com.au] Sent: Wednesday, May 30, 2001 10:35 PM
To: Multiple recipients of list ORACLE-L

Christopher,

Is this document perhaps a TAR as I cannot find it on Metalink (but maybe the search engine doesn't like me today).

Do you have the exact URL for this note 213220.999?

Thanks,
Bruce

-----Original Message-----
Sent: Thursday, 31 May 2001 6:59
To: Multiple recipients of list ORACLE-L

Oracle actually claims this statement. There are numerous docs stating this, for example DOC ID: 213220.999. But I agree 100% with Jeremiah's claims, in fact it can be verified very easily looking at v$lock while analyzing a large table. (I have actually done this in the past and present) and there are no locks under my current sid or any additional locks under any sid during analyze.

Take any table that takes more than 3-4 seconds to analyze and in another window query V$LOCKS and you can see this first hand.

"Walking on water and developing software from a specification are easy if both are frozen."

Christopher R. Spence
Oracle DBA
Fuelspot

-----Original Message-----
Sent: Wednesday, May 30, 2001 2:21 PM
To: Multiple recipients of list ORACLE-L

I have used compute statistics since version 7.3.2 up through 8.1.6, and never
had it hold a table lock for the duration of the analyze. I don't know where
people are getting the idea that compute statistics holds a lock and estimate
doesn't. That just doesn't make sense. Why would compute need a lock? Are you saying I can estimate sample 99 percent, and get what amounts to a compute
and avoid the supposed lock? Does that make any sense? No.

Validate structure cascade holds a lock. The most the others do is grab some
resource [very] briefly at the end to update the dictionary. People claiming
that compute holds a lock, please post documentation to support that statement.

--
Jeremiah Wilton
http://www.speakeasy.net/~jwilton

On Wed, 30 May 2001, Raghu Kota wrote:



> Yeah If you use compute statistics your table get locked and will take

long


> time, But If you use  estimate statistics it will be fast But not as

> accurate as before. Better to analyze at nights by setting time.

>

> >From: Robertson Lee - lerobe <lerobe_at_acxiom.co.uk>

> >

> >Anyone know if there are any locking issues while analyzing statistics

for


> >objects ??


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jeremiah Wilton
  INET: jwilton_at_speakeasy.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: cspence_at_FuelSpot.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Christopher Spence
  INET: cspence_at_FuelSpot.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).