OraFAQ Forum: SQL & PL/SQL » ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired while gathering stats

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired while gathering stats (Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired while gathering stats [message #605694]

Mon, 13 January 2014 12:47

athensromania
Messages: 46
Registered: July 2007
Location: Valencia,Spain

Member

Hi,
I'm not sure if this is the right post, I'm testing one procedure that can be running in parallel with this steps:

1.- Insert multiple rows in table T1
2.- COMMIT
3.- Execute gather table statistics for T1

If I run three jobs calling the same procedure some times, gives a ORA-00054: resource busy error due to the gathering of the statistics is locking the table in the other jobs.

My approach is to
1.- Check if the gathering is being executed
1.1.- If yes then : wait 5 secs
1.2.- If not execute statistics

My problem is that I have check the Oracle documentation and I don't know how to check if the dbms_stats.gather_table_stats is still running

Can somebody give me a clue?

Thanks.

Report message to a moderator

Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired while gathering stats [message #605696 is a reply to message #605694]

Mon, 13 January 2014 13:16

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

I do not have an answer and, although it is an interesting question, I don't have the time to do any research. But if I had this problem, I would query v$lock while dbms_stats is running to see what enqueue it takes. Perhaps you could test for the lock that before trying to gather. Any good?

Report message to a moderator

Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired while gathering stats [message #605700 is a reply to message #605694]

Mon, 13 January 2014 13:32

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

v$session_longops will tell you if some gathering is going on.

Report message to a moderator

Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired while gathering stats [message #605709 is a reply to message #605694]

Mon, 13 January 2014 14:05

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

athensromania wrote on Tue, 14 January 2014 00:17

I'm testing one procedure that can be running in parallel with this steps:

1.- Insert multiple rows in table T1
2.- COMMIT
3.- Execute gather table statistics for T1

PL/SQL is a procedural language, it means, you will see the next command/statement/step being executed only after the current step completes. So, when you say that the above steps are executed in parallel, what does it actually mean? Are you scheduling them as parallel jobs or one after other in procedural way?

Report message to a moderator

Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired while gathering stats [message #605736 is a reply to message #605709]

Tue, 14 January 2014 02:46

athensromania
Messages: 46
Registered: July 2007
Location: Valencia,Spain

Member

Hi,
the point is that three jobs will run the same procedure and sometimes will try to execute the statistics in the table at the same time.

Thanks for the answers.

Report message to a moderator

Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired while gathering stats [message #605743 is a reply to message #605736]

Tue, 14 January 2014 03:22

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Do you get the ORA-00054: resource busy when you try to gather statistics the second time, or before that in the step where you insert the lines?

Personally I don't see much gain in gathering statistics for a table again right after they were already gathered. What's the reason for that?

Report message to a moderator

Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired while gathering stats [message #605745 is a reply to message #605743]

Tue, 14 January 2014 03:32

Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web

Senior Member

ThomasG wrote on Tue, 14 January 2014 14:52

Personally I don't see much gain in gathering statistics for a table again right after they were already gathered. What's the reason for that?

Right. But it seems OP's intention is to gather stats after every insert transaction.

Report message to a moderator

Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired while gathering stats [message #605747 is a reply to message #605745]

Tue, 14 January 2014 03:47

athensromania
Messages: 46
Registered: July 2007
Location: Valencia,Spain

Member

that's the point Lalit.

Thanks.

Report message to a moderator

Re: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired while gathering stats [message #605748 is a reply to message #605747]

Tue, 14 January 2014 04:03

ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Well, you could always wrap the "gather statistics" call in an exception handler and retry it after 5 seconds when it hits the "ORA-00054: resource busy".

Of course the reason WHY you need those up do date statistics might point to an even better solution that doesn't slow down the processing so much because statistics are gathered all the time.

Report message to a moderator

Previous Topic:	Search String
Next Topic:	How to find last sunday of month

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 18:33:19 CDT 2024