Re: determining extent size, etc.

Ed Stevens wrote:

> Platform Win2K, Ora 8.1.7.3
>
> We are getting ready to upgrade all of our db's from 8.0.5 to 8.1.7 (finally!)
> and in the process are doing some rethinking on extent sizes, distribution, etc.
>
> In the past we have generally had 1 tablespace for user tables. With the
> upgrade we want to go to LMT and have 3 tablspaces for user tables -- one for
> 'small' tables (and a 'small' extent size), one 'medium' and one 'large', with
> uniform extent sizing in each TS roughly matched to the tables placed there.
>
> What I'm looking for is some rules of thumb (here we go again!) on determining
> the extent size for each TS and determining which tables to go in each. I
> figure the first step to be a query like
>
> SELECT NUM_ROWS * AVG_ROW_LEN tsize
> FROM DBA_TABLES
> WHERE OWNER = 'scott'
> ORDER BY tsize;
>
> So, given that info and assuming that the result does NOT yeild any clear break
> points in table sizes, where would one go from there?
>
> Or am I completely off base?
> --
> Ed Stevens
> (Opinions expressed do not necessarily represent those of my employer.)

If it helps I generally begin the thought process using orders of magnitude. So, for example:

256K
2.5M
25M

Then adjust from there.

Though based on some of the mythology thumping we've had around here lately I'm not at all sure some people wouldn't advocate going 256K (or some other value) for everything and just letting the number of extents expand indefinitely.

Daniel Morgan Received on Tue Jun 11 2002 - 11:48:57 CDT