Re: How can i change the MAXEXTENTS ??

On Thu, 06 Aug 1998 18:42:11 -0400, James C. Specht, Jr. <jspecht_at_primenet.com> wrote:
>Thomas Kyte wrote:
>>
>> A copy of this was sent to ashesh48_at_my-dejanews.com
>> (if that email address didn't require changing)
>> On Thu, 06 Aug 1998 17:17:33 GMT, you wrote:
>>
>> >Hi All, I got into the problem with the MAXEXTENTS of the table. It has
>> >reached to its maxlevel of the 121/121. How can I increase it?? Has anybody
>> >know the syntex for it ? How it affects to performace? And what is the
>> >recommanded number oof extents for a table??
>> >
>> >Thanx for any help in advance.
>> >Ashesh
>> >
>> >-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>> >http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
>>
>> What version of Oracle? Try:
>>
>> SQL> alter table <TNAME> storage ( maxextents unlimited );
>>
>> or use some other number in place of unlimited.
>>
>> As long as your extent size is a multiple of your db_file_multiblock_read_count,
>> having 1, 100 or 1000 (or more) extents is pretty much the same.
>>
>>
>> Thomas Kyte
>> tkyte_at_us.oracle.com
>> Oracle Government
>> Herndon VA
>>
>> http://govt.us.oracle.com/ -- downloadable utilities
>>
>> ----------------------------------------------------------------------------
>> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>>
>> Anti-Anti Spam Msg: if you want an answer emailed to you,
>> you have to make it easy to get email to you. Any bounced
>> email will be treated the same way i treat SPAM-- I delete it.
>
>I have been told that you should keep you extents at 1. The more
>extents you have the slower your responses get. It is even discussed in
>the Oracle Admin Guide. Am I wasting my weekends resizing tables to
>keep myself at 1 extent?

Yes, if you are doing it for it's own sake. If your extents are a multiple of the blocks read during each multiblock read, and you aren't fighting with the Oracle roundings of your sizing, there isn't much performance penalty for full table scans. If you are using Parallel Query, you may want to have multiple extents, each on different disks. So, it really depends on what you are doing as to what you should be doing on your weekends. If you have really volatile tables, you may want to defragment often. If you are just adding extents with linearly increasing primary keys, no need to bother as long as you watch out for maxextents.

The Advanced Oracle Tuning and Administration book explains this well. Remember that Oracle has evolved over time, so some old rules of thumb have changed.

jg
--
These opinions are my own and not necessarily those of Information Quest or Pebble In The Sky http://www.informationquest.com mailto:jgarry@nospameiq.com http://ourworld.compuserve.com/homepages/joel_garry Remove nospam to reply. mailto:joel_garry_at_compuserve.nospam.com "See your DBA?" I AM the @#%*& DBA! Received on Thu Aug 06 1998 - 20:52:44 CDT