The conventional wisdom is to use sizes around 128K, 4M, & 128M.
If you do the math each is 32 times larger than the previous.
This means that an object that starts with a single 128K extent
will have 32 extents when it reaches 4MB. From my perspective,
it seems reasonable to "migrate" this object when the number
of it extents is 17 - 48; which is NO where near 500. The
same factors are involved for an object that starts as a
single 4MB extent. Of course you can wait until an object
is comprised of hundreds of extents & likely will not have
any negative impact on database response time or performance.
HTH & YMMV!
Mark Leith wrote:
>
> I got a theory question.. Just because I'm not going to be putting it in to
> practice myself really :0)
>
> When using all of the below (LMT & UNIFORM EXTENTS, with the default
> recommended extent sizes):
>
> How do you determine the max size a table should be for a particular extent
> size? Do any of you adopt a method of saying:
>
> Right, once a table reaches 500 extents, then it should be moved to a larger
> extent TS?
>
> Mark
>
> -----Original Message-----
> WILLIAMS
> Sent: Friday, September 21, 2001 21:11
> To: Multiple recipients of list ORACLE-L
>
> Ron - Sorry I wasn't clear. There are two alternatives for LMTs, automatic
> and uniform. You are entirely correct that there are many possibilities.
> Then I discovered the paper: "How to Stop Defragmenting and Start Living:
> The Definitive Word on Fragmentation"
> http://metalink.oracle.com/cgi-bin/cr/getfile_cr.cgi?239049
>
> This certainly sounded GREAT to me (I found it through the Gennick paper you
> reference). That mentions that Oracle has slightly different recommendations
> for Oracle8i. I found an Oracle paper "Fragmentation and Data
> Reorganization"
> http://otn.oracle.com/deploy/availability/htdocs/fragment.html, which
> contains the following statements:
> Oracle has a number of recommendations:
> First, set all the extents in a tablespace to the same size (to me
> that says Uniform).
> Second, . . . Choose tablespaces for segments based on three
> recommended extent sizes, 128k, 4MB, or 128MB.
>
> I believe that both papers are based on the same philosophy. At this point I
> am basing my physical layouts based on these two papers, believing that this
> represents the direction that Oracle is going. If you or anyone else has
> more information to contribute, I am eager to listen.
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
> -----Original Message-----
> Sent: Friday, September 21, 2001 2:10 PM
> To: Multiple recipients of list ORACLE-L
>
> Dennis,
> I know that Oracle lists 3 sizes for the LMTbut you can specify any size
> extent if you do not use the "auto" feature. I have created a lot of
> different LMT extent sizes for tables that are like in size and activity.
> That way I have a better handle on the resources especually when the disks
> are a premium. Why put an active table initial 50 K next 50 K in a 128K
> tablespace. Why not manage the tablespace and make the extents 100 K?
>
> From the ORAMAG article:"The Autuallocate option allows
> Oracle to take control of the extent allocation. Oracle will use extent
> sizes of 64KB, 1MB, 8MB, and 64MB to manage space in the tablespace." The
> table created in the tablespace will adapt the auto extent policy of the
> tablespace and use increasingly larger extents as the table grows in size."
> "Locally Managed Tablespaces"
> by Jonathan Gennick. located at
> http://www.oracle.com/oramag/oracle/00-nov/o60o8i.html
>
> Just a thought.
> ROR mª¿ªm
>
> >>> [EMAIL PROTECTED] 09/21/01 12:00PM >>>
> Ramon -
> 1) How to find the ideal size of the extent for a locally managed
> tablespace uniform size ?
> Oracle is recommending only three extent sizes: 128k, 4m, and 128m. See
> white papers at technet.oracle.com for further details on the new
> philosophy.
> Dennis Williams
> DBA
> Lifetouch, Inc.
> [EMAIL PROTECTED]
>
> -----Original Message-----
> Sent: Friday, September 21, 2001 10:40 AM
> To: Multiple recipients of list ORACLE-L
>
> Hi folks,
>
> Actually I have 8.0.5 and will install 8.1.7 in the weekend, and would like
> to have somethings clear.
>
> Actually I have just 2 tablespaces, but planning to make one for each
> application system,
> will be like 7.
>
> Of course will make all kind of backups, cold, hot including redo logs,
> password files, parameter files and archives.
>
> What I am planning to do is make an export of the user owner of the all
> objects and
> make and import in the new DB.
>
> 1) How to find the ideal size of the extent for a locally managed
> tablespace uniform size ?
>
> 2) As far as I know, not much, the import will create the same tablespaces
> where the
> objects exists in the old DB in the new DB. Now, if I use an alter
> table move
> tablespace to move the tables to the new LMT tablespace, will it use
> the extent
> size of the new TBS or will remain with the old one ?
>
> 3) I'm considering to recreate all the indexes.
>
> 4) Is it a good idea to have an TBS for each APP system ?
>
> 5) What are the consequences of having all of them with different extent
> size ?
>
> 6) If by any chance some of them are going to have the same extent size
> wouldn't be better to put them in one TBS ?
>
> 6) Any tips for sizing of the SGA, shared pool, log_buffers, sort_area_size
> ?
>
> 7) Planning make db_block_size 8k
>
> 8) My second step will be implement Forms Server, anything to include in
> the
> installation of have in mind from now ?
>
> My DB is transactional, not that heavily, like 40 users, but like 12 of them
> are remote.
>
> Hardware
> Compaq
> 2 Pentium III processor 800 mhz
> 6 Hard Drive 9.1 GB - raid 5
> 1 GB Memory
> Win 2000 server
>
> Thanks in advance and sure that your help will be very, very useful.
>
> Ramon E. Estevez
> [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]>
> Dominican Republic
> 809-565-3121
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: DENNIS WILLIAMS
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Ron Rogers
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: DENNIS WILLIAMS
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: Mark Leith
> INET: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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).
--
Charlie Mengler Maintenance Warehouse
[EMAIL PROTECTED] 10641 Scripps Summit Ct.
858-831-2229 San Diego, CA 92131
United WE Stand! Justice WILL Be Done!
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Charlie Mengler
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
