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

Home -> Community -> Usenet -> c.d.o.server -> Re: lob storage and extent resizing

Re: lob storage and extent resizing

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Wed, 12 Dec 2001 07:09:15 +1100
Message-ID: <3c16679d$0$559$afc38c87@news.optusnet.com.au>


Comments below.
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Schmoltzie" <schmoltzie_at_hotmail.com> wrote in message
news:853faaeb.0112111148.7873491e_at_posting.google.com...

> Hello all,
>
> I inherited an Oracle 8i db that has poorly sized extents. One of the
> tables, with lob storage, ran out of extents. As a temporary fix I
> increased the max extents from 200 to 400. My current extents sit at
> 216 for this table.
>
> This lob storage is on the same tablespace as the rest of my segments
> which have an average segment size of 500k. The tablespace has 128k
> extents, the table has 64k extents, and the lob has 80k extents. My
> average segment size on the lob storage is 30m.
>
> Obviously I need to resize, but I'm a newbie at this so I was hoping
> for some advice on resizing based on the following questions .....
>
> Should the tablespace extent, table extent, lob extent sizes be the
> same as each other?
>
No.
> Should there be one extent per tablespace or 1 extent per segment.
>
Neither. The number of extents, subject to some provisos, is not true. You may be referring to some ancient advice that segments should come in 1 extent. Ignore it: it hasn't been true for years. 216 extents is not outrageous (it's bigger than I like to see, but it's not outrageously awful).
> What is the signifigance of chunksize with lobs?
>
It's effectively the smallest unit of access to the lob. The index accesses via chunks, for example. And reads and writes are done as chunks. Chunks can be as small as 1 oracle block (if memory serves, that's the default anyway). I tend to think of it like the db_multiblock_read_count parameter for full table scans: why scan 1000 blocks one at a time if my hardware can read 8 of them in one visit to the disk? So the significance of it is that if it's too small, performance degrades. If it's too big, performance degrades. It also affects initial and next extent sizes: they are supposed to be bigger than the chunksize.
> Whould anyone recommend locally managed tablespaces?
>
Absolutely, otherwise the earlier answer that said the number of extents is of no relevance is not true.
> Should you put lobs on seperate tablespaces?
>
I would. It's for administrative convenience on the one hand, and also has to do with that you are probably going to be I/Oing against the lobs like crazy, and I wouldn't want anything else getting in the way of all that activity.
> Can you place lob indexes on a tablespace other than the one that
> holds the lobs?
>
Not in 8i. You used to be able to do so in 8.0, IIRC, but not anymore.
> Lastly, how do you resize this table without losing data. Truncate?
> Delete? Export/Import? ??????
>
Not sure what you mean by 'resize the table'. Do you mean alter the size of the extents? If so, your best bet is to export the data, drop the table, re-create it with the same name but with all the right extent sizes, and then run import with IGNORE=Y. Regards HJR
> Thanks for your time,
>
> Chris Davis
Received on Tue Dec 11 2001 - 14:09:15 CST

Original text of this message

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