RE: hanging sessions related to ST enqueue and user quota

Further research indicates that prior to this issue, unlimited
tablespace was revoked from the owner of the object. 

 

The insert caused ORA 1536 - space quota exceeded for tablespace... but
a "when others null" exception handler hid the error.

 

Still, I'm trying to figure out why did we experience waits on the ST
enqueue... 

 

Could it be that the time for the waits was actually the time spent on
the oracle engine code path that handles those errors?

 

Regards,

 

Vlado

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Barun, Vlado
Sent: Monday, March 05, 2007 11:05 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: hanging sessions related to ST enqueue and user quota

 

Sessions in our 8.1.7 production database suddenly started hanging. 

 

We traced several of them and all were experiencing heavy waits on
executing the same insert into ... values ... statement.

88% of the execution time is attributed to enqueue waits with the same
p1 e.g.:

 

WAIT #39: nam='enqueue' ela= 308 p1=1398013958 p2=0 p3=0

 

which is an ST enqueue. 

 

The table being inserted into resides in a dictionary tablespace. 

 

However, as we were trying to decide what to do, one of my DBAs granted
quota unlimited on the table's tablespace to the owner of the table (not
to solve this problem, but for something else). 

 

Suddenly, the sessions stopped hanging and the ST enqueue waits
disappeared.

 

All this happened while I was attending the Hotsos symposium. 

Actually, I was trying to attend it, but was actually in one of the
hallways, on a conference call with my office trying to resolve this :-(

 

My question is whether the quota unlimited had anything to do with the
disappearance of the ST enqueues. 

Somebody at the symposium mentioned the existence of a bug related to
user quotas during STs (space management transaction) in 8i.

I have been trying to find the bug (and missing most of the evening
party), but no luck so far.

 

I'm trying to ensure that this will not happen again, and to do that I
need to understand what happened. 

 

Thank you for your time.

 

Regards,

 

Vlado Barun, M.Sc.
Manager, Database Administration
Jewelry Television

Other related posts: