Thought of trying this out...
I analyzed a huge table in our system and simultaneously checked for
locks...
Oracle did not lock the concerned table being analysed, but aquired locks on
sys tables for sometime, both in estimate and compute options.
On oracle 8.1.7.
rgds
amar
-Original
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
Yes, the library cache object is locked so it is not dropped during an
analyze.
Walking on water and developing software from a specification are easy if
both are frozen.
Christopher R. Spence
Oracle DBA
Fuelspot
-Original Message-
Sent: Thursday, May 31, 2001 5:25 AM
To: Multiple
:
Analyze table and locking
All,
Just a quicky
!!
Anyone know if
there are any locking issues while analyzing statistics for objects
??
TIA
Lee
The information contained in this
communication isconfidential, is intended only for the use of the
recipientnamed above
Hi Lee,
Compute option locks a table, but 'estimate' doesn't
Regards,
Ed
-Original Message-
Sent: 30 ??? 2001 ?. 15:22
To: Multiple recipients of list ORACLE-L
All,
Just a quicky !!
Anyone know if there are any locking issues while analyzing statistics for
objects ??
TIA
Ok, now I'm confused
One reply
-
It depends. For instance, 'analyze index validate structure' takes out
'shared' lock on the table (preventing inserts/deletes/updates), while
'analyze index compute/estimate statistics' does not.
Another
---
Compute option locks a
I think thats the reason why we have two options - Estimate and Compute Statistics.
Rajaram.
-Original Message-
From: Robertson Lee - lerobe [SMTP:[EMAIL PROTECTED]]
Sent: Wednesday, May 30, 2001 7:22 AM
To: Multiple recipients of list ORACLE-L
Subject:Analyze table
DBAPerceptron, Inc.(734)414-4627[EMAIL PROTECTED]
- Original Message -
From:
Robertson Lee -
lerobe
To: Multiple recipients of list ORACLE-L
Sent: Wednesday, May 30, 2001 7:21
AM
Subject: Analyze table and locking
All,
Just a quicky
!!
Anyone know
PROTECTED]
cc:
Subject:RE: Analyze table and locking
Ok, now I'm confused
One reply
-
It depends. For instance, 'analyze index validate structure' takes out
'shared' lock on the table (preventing inserts/deletes/updates), while
'analyze index compute
]
To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED]
Subject: Analyze table and locking
Date: Wed, 30 May 2001 03:21:46 -0800
All,
Just a quicky !!
Anyone know if there are any locking issues while analyzing statistics for
objects ??
TIA
Lee
The information contained
Not necessarily. 8i+ DOESN'T require _ANY_ lock to COMPUTE while collecting
statistics (99.99% of total operation time). It needs short lock to start
and complete analyze.
Regards
Vadim
-Original Message-
Sent: Wednesday, May 30, 2001 9:07 AM
To: Multiple recipients of list ORACLE-L
Hi
Many
thanks
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]Sent: 30 May 2001
17:10To: Multiple recipients of list ORACLE-LSubject:
RE: Analyze table and lockingAnalyze index validate structure takes shared lock on the table
disallowing any changes
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
At 8:10 -0800 30/5/01, [EMAIL PROTECTED] wrote:
In earlier versions (7.3 ?) 'analyze ..compute' used to take table
level locks. From 8 onwards, compute or estimate does not take any
table level or row level locks at all...
Sorry, not true. From at least 7.2.3 (the oldest I have here) there
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
nice to see you posting again :)
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 4:03 PM
To: Multiple recipients of list ORACLE-L
At 8:10 -0800
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
recipients of list ORACLE-L
Subject:RE: Analyze table and locking
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
Sorry but it is true. I have a 7.3.3 database and if it can't acquire a
table lock it cannot do the compute.
-Original Message-
Sent: Wednesday, May 30, 2001 1:03 PM
To: Multiple recipients of list ORACLE-L
At 8:10 -0800 30/5/01, [EMAIL PROTECTED] wrote:
In earlier versions (7.3 ?)
It could be a bug with my version... I don't think it actually
keeps a lock but it wants to be able to get the lock. For the most
part we don't get our analyzes in due to this. Fortunately for me,
we no longer want to and have deleted the stats.
-Original Message-
Sent: Wednesday, May
On May 30, 2001 03:21 pm, Jeremiah Wilton wrote:
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.
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
22 matches
Mail list logo