Re: A couple of tablespace questions for the experts

"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message news:1063985133.421060_at_yasure...
> Comments interspersed.
>
> Paul Dixon wrote:
>
> >I am planning the design of a new database to host an existing decision
> >support sytem, and am trying to decide :-
> >
> >1] Whether to use Automatic Segment Space Management for tablespaces or
to
> >use Uniform Extent sizes (as in Howard Rogers tablespace fragmentation
> >document) and manually manage the Freelists / Freelist Groups.
> >
> Use both ... they have nothing to do with each other as in the following
> example:
>
> CREATE TABLESPACE "DATA_SML" LOGGING
> DATAFILE 'c:\oracle\oradata\orabase\datasm01.dbf' SIZE 100M BLOCKSIZE 8192
> EXTENT MANAGEMENT LOCAL UNIFORM SIZE 256K SEGMENT SPACE MANAGEMENT AUTO;
>
> >2] Whether there are any siginficant implications from using a small
number
> >of large datafiles for a tablespace instead of a larger number of smaller
> >(i.e. 2 GByte) datafiles.
> >
> Datafile size is reasonably irrelevant unless you are talking about
> countless 50M files. Placement is everything.
>
> Assuming you are striping based on what you wrote below ... and I
> snipped ... I'd be heading for 16GB datafiles
> on raw devices striped across every spindle I could with the sole
> exception being the log files.

Daniel,

thanks for the response.

Niall has also pointed out my misunderstanding of the relationship between EXTENT MANAGEMENT and SEGMENT SPACE MANAGEMENT.

I really need to decide whether the 17+% extra cost for full table scans with ASSM that Howard Rogers spoke about in http://groups.google.com/groups?selm=NKawa.33877%241s1.492365%40newsfeeds.bi gpond.com is a bigger problem for my application users than freelist contention is for my batch jobs.

I suspect that the users probably wouldn't notice the 17 % longer response times (especially as the new hardware configuration should speed things up anyway), but that they will get very vocal if batch jobs overun and the data isn't available on the system when they log in at 7:00 a.m.

I'll definitely go with the larger data files as you suggest.

Paul Dixon Received on Fri Sep 19 2003 - 11:13:40 CDT