Re: Please comments This Tablespace Setup

"Frank van Bortel" <f.van.bortel_at_vnl.nl> wrote in message news:8l74hr$oh6$1_at_porthos.nl.uu.net...
> Don't agree on autoextent; adding datafiles manually is even more
> firefighting,
> but let's leave that for discussions at the fireplace, over a good glass,
> enjoying
> a fine cigar...

Well, on the grounds that I can never resist a fine cigar -my point is that proactive management of a database requires you to continually monitor your tablespaces for imminent running out of space, and to therefore add extra datafiles *before* they do so. I agree that sitting back and waiting for the thing to fall over due to lack of space, and *then* rushing around brandishing your 'add datafile' syntax is fire-fighting par excellence.

>
> I opt for: initial: 16k (twice your blocksize: 1 header, 1 datablock)
> reason: small (reference kind of) tables fit nicely in 1 initial extent.
> next: depends on your largest tables, anything from 128k to 1280k to 12M,
> but keep
> it with just two or three tops.
> Reason: allocating extents is perfomance releated.
> pctincrease: 0; reason better performance (what's easier for the Oracle
> process:
> building a house with similar shaped stones or all different?)
>

Well, I agree with that last sentence. But your suggestion for Initial and Nexts rather forces the issue: you're going to have odd-sized extents if a table ever does extend past the Initial, and that means potential fragmentation down the track.

Your small reference tables should be stored in their own tablespace, with Initial and Next both of 16K (since that's the size you specified). You then need another tablespace with Initial and Next of 128K, another of 1280K and so on.

> Fragmentation, or extents ar enot worrying (anymore, since 7.3), nor bad
 for
> performance.
> Allocating extents is bad for performance...
>

Agreed. Allocation of extents can indeed be a major performance concern, since (until you get to 8i) all allocations are recorded in the Data Dictionary -which means excessive extent allocation will likely cause contention on the datafiles of the system tablespace.

I don't agree that fragmentation is not a worry anymore. It still happens in 8i (and is demonstrated as happening on the latest 8i DBA training course), it still constitutes a waste of disk space, and there are some performance issues involved -coalescing free space take is not a 'free' process, and when you're reading blocks, you really don't want to have to dip and dive all over the place to retrieve them.

> I don't understand your 5 files... either these were made too big to start
> with, or
> for some reason, only file 5 is being used. With autoextent ON, why bother
> having 5 files
> for one tablespace??? Or is maxsize set as well? What was the reason for
> having 5 datafiles
> for one tablespace?
>

<ultiple files per tablespace gives you the option to distribute the I/O on the objects in that tablespace across separate physical devices. Hence performance issues. Also management issues: which you rather restore 1 of 6 500M files that has gone walkabout, or 1 3Gb file?

> BTW, 505 extents max is default for 8k (249 for 4k, 121 for 2k blocksize)
> Which brings up: why go for 8k? OLTP has 2k - 8k, DSS/DW 8k - 32k. 8k
 seems
> hybrid.

Funny. Blocksize seems to be like hem lines: the fashion changes all the time! Nevertheless, Block size *doesn't* need to vary depending on what use is being made of the database. You should use a large database block size if your application uses a lot of index based access plans, particularly index range scans -but that could be equally true for DSS and OLTP systems.

It also depends upon platform: Unix platforms that are not using raw devices, and not using direct I/O (which is most of 'em!), must use 8K blocks because the file system buffer size is 8K. NT, on the other hand, does use direct I/O -so a bigger block size can be used with no adverse effects. And the recommendation is that, other things being equal, you should use larger block sizes. The only major concern is that you should ideally be able to increase the size of your database buffer cache to compensate for the fact that doubling your block size would involve a halving of the number of blocks in the cache.

On NT, 16K is a no-brainer of a decision to make. On Unix with raw devices or direct I/O, 16K is also a no-brainer. Otherwise it's 8K all the way.

And for a full discussion of block size issues, I'd recommend a trip to http://www.ixora.com.au/tips/creation/block_size.htm

Cheers!
HJR
> --
> Kind Regards,
> Frank
> "Howard J. Rogers" <howardjr_at_www.com> wrote in message
> news:39756ac3_at_news.iprimus.com.au...
> >
> > Johnson <syner_at_pd.jaring.my> wrote in message
> > news:8l3o2p$bg2$1_at_news5.jaring.my...
> > > Hello DBA,
> > >
> > > Please comment on this tablespace (Mydata) setup, running in NT, with
> > > db_block_size = 8k. Do I need to fine tuning or rezise it? Since I put
 the
> > > autoextent = ON.
> > > --------------------------------------------------------
> > >
> > > Tablespace storage statistic
> > > -Total block : 284160
> > > -Used Block : 283526
> > > -Free : 634
> > >
> > > This tablespace is having 5 datafiles :
> > > - Data1 - 75520 Blocks
> > > - Data2 - 20480 Blocks
> > > - Data3 - 12800 Blocks
> > > - Data4 - 12800 Blocks
> > > - Data5 - 162560 Blocks
> > >
> > > The default storage
> > > - Initial - 40960
> > > - next - 40960
> > > - Min Ext - 1
> > > - Max Ext - 505
> > > - Pct increase - 50
> > >
> >
> >
> > PCTINCREASE should never be anything other than zero (fragmentation).
> > MAXEXTENT of 505?? Unusual number...but regardless, for optimal
> > performance, no segment should ever have more than half a dozen extents.
> >
> > Incidentally, the autoextend clause should never be specified unless you
 are
> > opting out of continually monitoring your database and doing *proactive*
> > management, instead of mere firefighting.
> >
> > Regards
> > HJR
> >
> >
> > > Thanks for you comment.
> > >
> > >
> > >
> > >
> >
> >
>
>
Received on Sun Jul 23 2000 - 00:00:00 CEST