Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> hanging sessions related to ST enqueue and user quota

hanging sessions related to ST enqueue and user quota

From: Barun, Vlado <Vlado.Barun_at_JTV.com>
Date: Mon, 5 Mar 2007 23:05:05 -0500
Message-ID: <3BE217C62DE38F47A0DC58568DFB0A2C6EBEA9@KPMSPWV04.jewelry.acn>


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

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Mar 05 2007 - 22:05:05 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US