Re: Operations in huge transaction slow down suddenly. Why?

From: <joel-garry_at_home.com>
Date: 21 Dec 2004 15:52:04 -0800
Message-ID: <1103673124.855108.318300@z14g2000cwz.googlegroups.com>

Volker's response banged me in the head for something I forgot in mine. Check what is happening in both your rollback segments and your sort area. One speculation is that you are suddenly having to use sort area when the indices are being generated, everything works fine until someone else hammers on the the sort area and it gets thrashed, or perhaps there are lots of little segments used that are making oracle work hard to get more segments (see if upping sort area size helps). With the rollback segments, might be problems if some transaction is causing a read-consistency issue on the original table. I've found this useful in some situations (from metalink Note:1039126.6):

select

substr(a.os_user_name,1,8)    "OS User",
substr(a.oracle_username,1,8) "DB User",
substr(b.owner,1,8)  "Schema",
substr(b.object_name,1,20)    "Object Name",
substr(b.object_type,1,10)    "Type",
substr(c.segment_name,1,5)  "RBS",
substr(d.used_urec,1,12)      "# of Records"

from
v$locked_object a,
dba_objects b,
dba_rollback_segs c,
v$transaction d,
v$session e
where a.object_id = b.object_id

and a.xidusn    =  c.segment_id
and a.xidusn    =  d.xidusn
and a.xidslot   =  d.xidslot
and d.addr      =  e.taddr

;

With respect to DMT v. LMT, LMT is far superior (google this group for explanations why). In particular, I was thinking you may be running into issues of having many extents, and having to thrash dictionary tables to get more. http://www.jlcomp.demon.co.uk/smon.doc has some interesting, if elderly, thoughts. What kind of tablespace is your temp (be exact!)?
jg

-- 
@home.com is bogus.
Happy Birthday, Samuel L. Jackson!

Received on Tue Dec 21 2004 - 17:52:04 CST