RE: Locally Managed Tablespaces - Questions.

Richard

   Your update to Stop Defragmenting and Start Living is just what I'm looking for. I heartily encourage you. I agree with you that some aspects of the paper seem a bit dated. And since this paper promoted what many considered heresy at the time (lots of extents are okay), the recommendations may be a bit conservative. However, Oracle still has these sizes posted as a recommendation:
http://otn.oracle.com/deploy/availability/htdocs/fragment.html But my real problem is that nobody has provided an alternate recommendation. In digging into this more, I uncovered Oracle9i Database Administration Best Practices:
http://otn.oracle.com/products/manageability/database/pdf/BestPractices9i.pd f
This author recommends using Auto Allocate. ARRRRGGGG!!!

Dennis Williams
DBA, 40%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Saturday, April 12, 2003 6:09 AM
To: Multiple recipients of list ORACLE-L

Hi Dennis,

Couple of comments I would make:

"How to Stop Defragmenting and Start Living" was written way back in 7.3 days with 8.0 just coming out. Although it's still a useful document, it's somewhat outdated, especially with LMT in mind. I'm thinking of writing a paper called "Stop Defragmenting and Start Living In The 21st Century" ;)

Having tablespaces of 128k, 4m and 128m (or thereabouts) made sense with DMT and was recommended practice. But why recommend DMT practices for LMT in 9.2 ? You can fit 30 odd 128K segments in 4m and 30 odd 4m in 128m. So obviously there's an issue once we hit 30 extents or thereabouts in LMTs that warrants:

1. spending time and consideration in placing a segment into it's appropriate tablespace.
2. spending time and overheads in moving an object if it were to be incorrectly placed in the wrong tablespace.

else why separate them as such.

So what are these issues, what are the performance degradations, what are the management disadvantages of having an object with 30 extents. Or 300 extents. Or heaven forbid 3000 extents ... ? I can think of one possible reason but I'll be interested to hear your motives.

Finally, there is nothing new or brave with LMT. They've been around since the start of 8i and pretty well bug free by 8.1.4 and I know but 1 reason (lack of capacity management via maxextents) why they shouldn't be used in 9.2.

Regards

Richard
----- Original Message -----
To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Saturday, April 12, 2003 2:28 AM

> Ceri
> First study How to Stop Defragmenting and Start Living.
> http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049
> Make sure you really understand the concepts. Oracle recommends LMT with
> uniform extents. Create tablespaces in 3 sizes, 128k, 4m, and 128m, and
> assign tables appropriately.
> If you are unsure enough about this brave new LMT world that you are
> still considering dictionary-managed, maybe you shouldn't create the
SYSTEM
> tablespace as LMT this time.
>
> Dennis Williams
> DBA, 40%OCP, 100% DBA
> Lifetouch, Inc.
> dwilliams_at_lifetouch.com
>
>
>
> -----Original Message-----
> Sent: Friday, April 11, 2003 9:37 AM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi Listers,
>
> I am in the process of setting up a new Oracle database on 9.2.0.3.0.
> I want to use locally managed tablespaces, but im not sure about the
> extent and segment management options. Should I use uniform extents?
> If so how do i determine a good size for these, or is the Oracle
> Default good enough. Also what about sement space management, what
> benefits can this provide me, any potential pitfalls i should watch
> out for? Also should i make my system tablespace locally managed
> also? Does this mean i then cant create dictionary managed
> tablespaces?
>
> All help appreciated, or point me to a good artical.
>
> Thanks
>
> Ceri
> --
> Ceri Database Admin
> mailto:dba_at_persistent-worlds.net ICQ:153010767
>
> "I love deadlines. I like the whooshing sound they make as they fly by."
> Douglas Adams
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Ceri Townsend
> INET: dba_at_persistent-worlds.net
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
> --
> Author: DENNIS WILLIAMS
> INET: DWILLIAMS_at_LIFETOUCH.COM
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> 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.net
-- 
Author: Richard Foote
  INET: richard.foote_at_bigpond.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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.net
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).