re MAXEXTENTS UNLIMITED was -- RE: OEM Events

As a rule, I refrain from using MAXEXTENTS UNLIMITED on frown on any of my junior DBAs or Developers using it.

Denny and Rich have provided two examples of when M.U may not be good.

I come from the Oracle5 and Oracle6 days when tablespace and extent management did require DBA time. So why I do carry my old ways right up to 8.1.7 ? [It is only in 9i that I have begun using Locally Managed / Uniform/Auto Allocate
and ASSM].

    A number of my databases are still 7.3.4 to 8.0.6 and even those in 8.1.7 have been upgraded from lower versions with the minimal downtime allowed, no conversion to Locally Managed.

If I set M.U, I might not know that a table or index has grown very large or in to too many
extents until the tablespace datafile is full [and although I do use AUTOEXTEND, I don't
set MAXSIZE to unlimited either because I stick to datafiles of 2GB or less, for
various reasons, and file systems holding the database are not very large either !].

So why I do want to know if a table is growing too quickly ? An alert that a table has exceeded 25% of it's max_extents tells me that I should watch
this table -- see if I get alerts again soon after I have increased the max_extents
or the next_extent size. Either something has changed in the application or a new
module has been implemented and unexpected growth is occurring.

With rapid extent growth, I have honeycombed tablespaces which are not neat ! Querying DBA_EXTENTS takes time, finding a free extent / allocating a next extent
can take time if a number of tables are rapidly allocating new extents. An early alert tells
me that I need to review the NEXT_EXTENT of such tables.

A review of a database by another DBA or a pseudo-DBA manager while I am away or on leave could convey an impression that I don't know my databases when the reviewer finds tables/indexes with thousands of extents. I then have to fight a negative image.

And what happens when a table finally has 2billion extents ? Surely I should have known
about the table and modified the next extent or moved it to a tablespace with larger next_extents
much earlier.

An alert for a table/index that has exceeded 25% of max_extents is quite early enough to
avoid the possibility of the table/index hitting max_extents. [surely I don't expect the table
to quadruple in one day, such a table I should have been notified about by the developer/implementor/owner
much earlier -- setting 2billion max_extents isn't sufficient for a table that could quadruple in a day because I might not have enough space in my datafiles, inspite of any MAXSIZE].

All in all, I see absolutely no downside in knowing and tracking how my tables are growing
by properly using MAX_EXTENTS and NEXT_EXTENT in databases which are Dictionary Managed
and may even have non-uniform extent sizes or [heavens !] segments with PCTINCREASE of 50
[imagine what would happen if someone created a PCTINCREASE table and I don't know about
it till it hits 2billion extents].

You might say that there are other ways of monitoring growth without using MAX_EXTENTS --
just write a script to get the total number of extents [DBA_SEGMENTS.EXTENTS]. But how do I know about growth rate ? Then, I'd have to maintain a history of total extent counts for each table
and write analytical queries to get growth from the history. An alert saves me the bother
of going through all that trouble. Instead of keeping track of the 20,000 tables in the database, I just
monitor half a dozen tables which I know early enough are growing rapidly.

Each DBA has his own methods and has been burnt by something or the other. I will stick
to monitoring EXTENTS and NEXT_EXTENT vis-a-vis UNLIMITED. Sorry, but that's how I do
part of my job.

Hemant

At 03:26 PM 17-04-03 -0800, you wrote:
>There is a limit, it's 2147483645.
>
>Jared ;)
>
>
>
>
>
>Denny Koovakattu <groups_at_koovakattu.com>
> 04/17/2003 12:37 PM
>
>
> To: ORACLE-L_at_fatcity.com
> cc: Jared.Still_at_radisys.com
> Subject: RE: OEM Events
>
>
>
>
> I agree, but there has to be an upper limit. Case in point. One of my
>friends
>told me this story about them having to rename a table and move the data
>into
>another table because somebody thought it was a good idea to have an
>initial
>and next of 8K for a table which grew to a few GB. I would rather define a
>
>relatively large upper limit and correct it before it hits that limit than
>just
>let it increase without any control.
>
>Regards,
>Denny
>
>Quoting Jared.Still_at_radisys.com:
>
> > Hemant,
> >
> > You're missing the point. Numerous extents is *always*
> > better than crashing an application becuase it hit maxextents.
> >
> > I don't care *how* many extents it is, it is *always* better than
> > crashing.
> >
> > There are many possible scenarios for the number of extents suddenly
> > increasing before the DBA can catch the problem, especially when the
> > DBA flies solo.
> >
> > ( for those than join this thread late, we're discussing DD managed
> > extents)
> >
> > Jared
> >
> >
> >
> >
> >
> >
> > Hemant K Chitale <hkchital_at_singnet.com.sg>
> > Sent by: root_at_fatcity.com
> > 04/16/2003 09:54 AM
> > Please respond to ORACLE-L
> >
> >
> > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > cc:
> > Subject: RE: OEM Events
> >
> >
> >
> > "segments with extents > 25% of max_extents [yes, this is a really low
> > threshold]"
> > catches the application much before it can crash.
> >
> > Unlimited can result in tens of thousands of extents and someone then
> > wanting to drop the table. In Dictionary Managed tablespaces, there's
> > no point in allowing very many small extents. If my script catches
> > such tables early, I can modify the NEXT_EXTENT so that extent
> > growth isn't too great.
> > Hemant
> >
> > At 11:54 AM 15-04-03 -0800, you wrote:
> > >I like to set all objects to maxextents unlimited.
> > >
> > >It's silly to crash an app because of some arbitrary limit
> > >that has little meaning. If you don't like that many extents,
> > >you can always fix it later.
> > >
> > >Beats having unplanned downtime. :)
> > >
> > >Jared
> > >
> > >
> > >
> > >
> > >
> > >
> > >Hemant K Chitale <hkchital_at_singnet.com.sg>
> > >Sent by: root_at_fatcity.com
> > > 04/15/2003 09:28 AM
> > > Please respond to ORACLE-L
> > >
> > >
> > > To: Multiple recipients of list ORACLE-L
> > <ORACLE-L_at_fatcity.com>
> > > cc:
> > > Subject: RE: OEM Events
> > >
> > >
> > >
> > >Once a day is good enough if you have set the right thresholds --
> > >among the other monitoring scripts that I have are :
> > >
> > >tablespace with less than 20% free space on current datafile sizes
> > >[irrespective of the maxbytes of the datafile],
> > >
> > >segments with extents > 25% of max_extents [yes, this is a really low
> > >threshold]
> > >
> > >segments with next_extent > 25% of largest free extent in the
> > tablespace.
> > >
> > >Run by custom SQL scripts, called by CRON from a monitoring server.
> > >
> > >Hemant
> > >
> > >At 11:19 AM 14-04-03 -0800, you wrote:
> > > >How often do YOU check these limits (tablespace utilisation, and
> > "max
> > > >extents")?
> > > >
> > > >Once per day, somewhat less than every 10 minutes.
> > > >
> > > >Jared
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >
> > > >"Mark Leith" <mark_at_cool-tools.co.uk>
> > > >Sent by: root_at_fatcity.com
> > > > 04/14/2003 08:53 AM
> > > > Please respond to ORACLE-L
> > > >
> > > >
> > > > To: Multiple recipients of list ORACLE-L
> > ><ORACLE-L_at_fatcity.com>
> > > > cc:
> > > > Subject: RE: OEM Events
> > > >
> > > >
> > > >Sunny,
> > > >
> > > >There is a whitepaper that I found available from:
> > > >
> > > >www.csis.gvsu.edu/GeneralInfo/Oracle/em.920/a96675.pdf
> > > >
> > > >This is the "Oracle Enterprise Manager Event Test Reference Manual
> > >Release
> > > >9.2.0" document..
> > > >
> > > >I note two things in this document. For the "Tablespace Full" event,
> >
> > it's
> > > >"Recommended Frequency" is 30 seconds (!), though it contradicts
> > itself
> > >by
> > > >saying:
> > > >
> > > >"Note: Running the Tablespace Full event test may be a
> > resource-intensive
> > > >operation.
> > > >Therefore, Oracle recommends running the Tablespace Full event test
> > >during
> > > >off-peak periods."
> > > >
> > > >It says the same thing with the "Maximum Extents" event as well,
> > stating
> > > >the
> > > >"Recommended Frequency" as 10 minutes (you can take that one back
> > at
> > > >least),
> > > >whilst still stating:
> > > >
> > > >"Note: Running the Maximum Extents event test may be a
> > resource-intensive
> > > >operation. Therefore, Oracle recommends running the Maximum Extents
> >
> > event
> > > >test during off-peak periods."
> > > >
> > > >One last thing to consider, with all of these events set at 5
> > minute
> > > >intervals - do they all the checks execute at the same time? How
> > about
> > > >monitoring the monitor, to see what kind of resources it really is
> > >chewing
> > > >up? I wonder if you will start to see large spikes on five minute
> > > >intervals
> > > >;)
> > > >
> > > >Here's one for the list:
> > > >
> > > >How often do YOU check these limits (tablespace utilisation, and
> > "max
> > > >extents")?
> > > >
> > > >Regards
> > > >
> > > >Mark
> > > >
> > > >
> > > >
> > > >--
> > > >Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > > >--
> > > >Author:
> > > > INET: Jared.Still_at_radisys.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).
> > >
> > >Hemant K Chitale
> > >My personal web site is : http://hkchital.tripod.com
> > >
> > >
> > >--
> > >Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > >--
> > >Author: Hemant K Chitale
> > > INET: hkchital_at_singnet.com.sg
> > >
> > >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:
> > > INET: Jared.Still_at_radisys.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).
> >
> > Hemant K Chitale
> > My personal web site is : http://hkchital.tripod.com
> >
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.net
> > --
> > Author: Hemant K Chitale
> > INET: hkchital_at_singnet.com.sg
> >
> > 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:
> > INET: Jared.Still_at_radisys.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:
> INET: Jared.Still_at_radisys.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).

Hemant K Chitale
My personal web site is : http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

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).