I recall that these were the days before LMT and at the start of moving away
from fine-grained object sizing. In hindsight, 1 size per tablespace makes
more sense. There could be several exceptions. For example, all of the
reference tables/indexes were in a single tablespace. Since these were
fairly small and very static, we had a more classic approach to sizing
(still only 3 extent sizes).
LMTs are of 2 varieties Uniform size and Autoallocate. Uniform means 1 and
only 1 extent size. Autoallocate has 4 extent sizes (Oracle documents only
3). I have used autoallocate when the rough size was not known or we were
mixing several general sizes. Uniform would be good for known partitions,
small reference tables, etc.
So, if this is the goldilocks approach...who are the 3 bears?
-----Original Message-----
Sent: Tuesday, September 03, 2002 12:49 PM
To: Multiple recipients of list ORACLE-L
Dan,
Why 3 extent sizes in each tablespace? I can see how you prevented the
dreaded fragmentation problem by making them multiples of one another
but I don't understand why you did that.
If I'm using LMTs can I still do that? I haven't really used them, I'd
thought to not specify the initial and next extent sizes and just let
the LMT deal with it.
As for the Goldilocks name, you are more than welcome to steal, uh,
borrow, it :)
Rachel
- "Fink, Dan" <Dan.Fink_at_mdx.com> wrote:
> We used this method (I wish I had come up with the Goldilocks code
> name...)
> in 8.0.4 on a couple of data warehouses. The bottom line is that we
> had 1
> space related failure (application code filled up the error log) in
> over 9
> months of operation. Which so impressed management that they decided
> to cut
> the support budget (not enough outages/calls to help desk).
>
> There were over 150 tablespaces per database so we could perform I/O
> balancing, object segregation, etc. We did not follow the SAFE (3
> and only
> 3 extent sizes in the database). Rather we used 3 extent sizes per
> tablespace with medium a multiple of small and large a multiple of
> medium.
>
> PCTFREE - set to 10 (no updates, but provided a little space just in
> case
> this changed)
> PCTUSED - set to 80 (same reason as above)
> FREELIST - set to 5 (originally designed so only 2 processes would
> ever
> concurrently insert data)
>
> Sounds like you are on the right track.
>
> -----Original Message-----
> Sent: Tuesday, September 03, 2002 9:49 AM
> To: Multiple recipients of list ORACLE-L
>
>
> time for me to ask the experts again.
>
> My data warehouse will be 9.2, with all locally managed tablespaces.
> We
> will be following what I have taken to calling the "Goldilocks"
> principle -- that of small, medium and large tablespace extent sizes,
> with variations in that we will separate indexes and data, and will
> have even more separation for our fact tables into partitioned tables
> and tablespaces.
>
> However, now comes the time for me to work out storage clauses. And a
> quick read through the docs leaves me wondering if I should just turn
> on automatic segment-space management and not worry about setting
> PCTFREE, PCTUSED and FREELIST parameters. I can't find any real
> information or bugs on MetaLink either.
>
> Does anyone have any experience, good OR bad, with using this
> feature?
> If you are doing data warehouse work, what are good values for the
> parameters if I DO use them? One fact table is likely to be highly
> updated (customer info) as we collect more and more specific
> information from customers. The rest will be, as you would expect
> from
> a DW, mostly inserts.
>
> Help?
>
> Thanks!
>
> Rachel
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Finance - Get real-time stock quotes
> http://finance.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Rachel Carmichael
> INET: wisernet100_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Fink, Dan
> INET: Dan.Fink_at_mdx.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing
> Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Do You Yahoo!?
Yahoo! Finance - Get real-time stock quotes
http://finance.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Rachel Carmichael
INET: wisernet100_at_yahoo.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Fink, Dan
INET: Dan.Fink_at_mdx.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Tue Sep 03 2002 - 14:15:30 CDT
