Re: imp commit=n and rbs

From: Joel Garry <joel-garry_at_home.com>
Date: 1 May 2003 17:16:59 -0700
Message-ID: <91884734.0305011616.6bcdbc6f@posting.google.com>

Norman Dunbar <Norman.Dunbar_at_lfs.co.uk> wrote in message news:<E2F6A70FE45242488C865C3BC1245DA7039C0F05_at_lnewton.leeds.lfs.co.uk>...
> Morning Joel,
>
> >> 2.1 million rows * 18 = ?G?
> I see your point :o)
>
> >> compress=Y on export, table in single extent truncated, ignore=y.
> Due
> >> to previous performance issues, this table is explicitly as
> >> defragmented and contiguous as possible.
> I'm saying nothing !
>
>
> >> Yes, I know _all about_ compress=Y,
> >> and it is appropriate for the situation.).
> :o)
>
> >> [index creation] Well maybe, but more than 5 times the table size?
> I'm tellin' ya, my
> >> "something weird this way comes" light is flashing brightly.
> Mine's just glowing dimly at the moment, but it does seem odd - how many
> indexes on this table though ?
>
> >> [constraints] Hmmmm... need to check that, since this is a summary
> file, I don't
> >> think that should be at issue, but it is possible the 4GL that dinked
> >> with it may have done something overly-friendly...
> I've got a 4GL system called Uniface, but it only creates constraints
> and indexes that I tell it to !

Haven't had a chance to actually investigate that, but it does set up foreign key type relationships between fields named the same in different tables. I say "type" because I don't think it uses the Oracle mechanism (well, it didn't used to in older versions, that's what I would need to investigate.)

>
> >> but still, more than 5* the table size?
> It does sound excessive.
>
> I'd be interested to hear what you find out though.

I was able to run another test in between things, and found a bit of interest. I was watching the rbs with this tidbit 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

;

I noticed the row count (assuming there are locks on everything in the segment and it's all this transaction, hmmm...) grow to about 8851973 when it blew at 3.8G. So, I'm thinking it does indeed keep a row for every index. More than that, it seems these rows are about twice the size of the original average size (2.1 million in a bit over 500M in the original ts - unless I've been looking at this too long and can no longer divide! :-).

Also kinda interesting was watching the row count slowly go down after the ora-1562, as it released the locks. I've found that script useful for seeing if anything is actually happening to data vs just waiting on some locked resource, and sometimes even for calculating end of job time.

--
@home.com is bogus.
"The tree is still balanced, it just contains a lot of empty blocks."
- ixora

Received on Thu May 01 2003 - 19:16:59 CDT