| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Tablespace reorg tools
Ana,
The 50% pctincrease default is, I think, a pretty good idea by Oracle. As in your case where you implemented quickly (I assume without an experienced dba to hand) and therefore did not have anyone, or the time, to carry out db space planning. In this case the 50% prevents tables hitting maxextents - which would be a show stopper.
There has been a lot of discussion here and elsewhere because eliminating tablespace fragmentation, chaining and the maintenance required to rectify them have been the Oracle DBA's holy grail for a long time. I was even on a site once where the Finance Director (he who seems often to be hovering over the IT dept) had issued a directive that no db segment should exceed 10 extents. He had been on the Oracle training courses and come back with the 'multiple extents are bad' mantra echoing through his head. I had a set to with him on it of the 'You pay me to run the database so let me [insert expletive here] do it'. The site, who will remain nameless, were experiencing outages almost every other day (no kidding) as objects hit arbitrarily defined storage limits. It was great in a way because after my first month in the post there had been no downtime and I (cheekily) asked for a rate increase and got it.
Anyways, I am now of the school that goes for uniform extent sizes in tablespaces wherever possible. In any single tablespace, all segments will have the same initial and next extent sizes defined. In this way there is no need for regular coalescing and Oracle has to do less space management. I also [usually] export and import without compression, otherwise you bugger up all the good work. Of course this is not the end of it (that's why lots of books - some of them worth reading - have been written on the subject), certain situations and certain tables require special consideration but I think it is a good start and it gives me more time with my beautiful wife and lovely kids.
Phil...
> -----Original Message-----
> From: achoto_at_american.edu [SMTP:achoto_at_american.edu]
> Sent: Wednesday, April 26, 2000 7:28 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Tablespace reorg tools
>
>
> We coalesce our tablespaces regularly. I have checked and all our
> tablespaces show 100% coalesced. We have left the 50% increase as the
> default, and for what I see in this list that may not have been a good
> idea. Unfortunately we were brand new to Oracle when we were to turn off
> our mainframe and move quickly our legacy data to Oracle. So, not much
> thought was put into the configuration and now we're in trouble trying to
> tune the database as we use Oracle more and more.
>
> We have now a fair amount of fragmentation in the two tablespaces we use
> for our Data Warehouse and also we have sporadic problems with our
> rollback
> segments with the ora-01555 error.
>
> I really appreciate the input I get from the list. I've gotten much more
> out of it than from Oracle support and/or the Oracle manuals.
>
> Thanks again to all of you
>
> Ana
>
>
>
>
> Jared Still
>
> <jkstill_at_bcbs To: Multiple recipients of
> list ORACLE-L <ORACLE-L_at_fatcity.com>
> o.com> cc:
>
> Sent by: Subject: RE: Tablespace reorg
> tools
> root_at_fatcity.
>
> com
>
>
>
>
>
> 04/26/00
>
> 11:52 AM
>
> Please
>
> respond to
>
> ORACLE-L
>
>
>
>
>
>
>
>
>
>
> Why would you see an increase in fragmentation?
>
> You may see more fragments in the free space, but
> they will be coalescable.
>
> Part of Oracle's extent allocation algorithm is to
> coalesce free space as needed.
>
> Whether you coalesce it up front, or wait until you
> actually need it, the effects on fragmentation
> will be the same.
>
> Jared
>
> On Tue, 25 Apr 2000, Gregorio Ortiz wrote:
>
> > boys||girls
> >
> > Effect of pct_increase 0
> >
> > If one uses PCT_INCREASE 0, you will see an increase in fragmentation.
> I
> > usually use 1 in order for the tablespaces
> > to coalesce automatically via SMON. Unless you are proactive and
> manually
> > coalescing the tablespaces at regular intervals, you will be fine. By
> all
> > means, do not leave the default of 50%.
> >
> > /gxodba
> >
> > -----Original Message-----
> > From: bounce-oracle-102619_at_telelists.com
> > [mailto:bounce-oracle-102619_at_telelists.com]On Behalf Of Morton, Ronald D
> > Sent: Tuesday, April 25, 2000 3:21 PM
> > To: oracle list
> > Subject: RE: Tablespace reorg tools
> >
> >
> > Correct me if I'm wrong but I believe that the default value for
> PCTINCREASE
> > is 50.
> > You must specifically set it to zero if you want to have full control
> over
> > your extents.
> > I set all of mine to zero in the DEFAULT STORAGE clause when I create a
> > tablespace.
> >
> > HTH,
> > Ron
> >
> > > -----Original Message-----
> > > From: Lisa_Koivu_at_gelco.com [SMTP:Lisa_Koivu_at_gelco.com]
> > > Sent: Tuesday, April 25, 2000 11:21 AM
> > > To: oracle list
> > > Cc: oracle list
> > > Subject: Re: Tablespace reorg tools
> > >
> > > Ana, have you tried 'TRUNCATE TABLE DROP STORAGE' and removing the
> > > PCTINCREASE?
> > > I never use pctincrease, I want to specifically state the size of the
> > > extents.
> > > Others may disagree...
> > >
> > > I don't see why you would have to reorg if you are truncating.
> Truncating
> > > is
> > > the first step in reorging tables. I wish I could truncate some of my
> > > tables
> > > daily.
> > >
> > >
> > >
> > >
> > >
> > > achoto_at_american.edu on 04/25/2000 10:09:03 AM
> > >
> > > Please respond to achoto_at_american.edu
> > >
> > > To: "oracle list" <oracle_at_telelists.com>
> > > cc: (bcc: Lisa Koivu/GELCO)
> > >
> > > Subject: Tablespace reorg tools
> > >
> > >
> > >
> > > We're having problems with two tablespaces that have plenty of free
> space,
> > > but, Oracle doesn't seem to recognize it. We truncate some tables and
> > > reload them every night, but once in a while one or two large tables
> fail
> > > to load because the next available extent is not large enough. All of
> our
> > > tables have an initial extent of 81920, 50% increase, minimum extents
> 1,
> > > and maximum extents 1017. I suspect that the 50% increase may be the
> > > culprit. Do we need to look closely at this and maybe change it?
> > >
> > > We're on Oracle 8.0.5 in a Sun box. By looking at the tablespace map
> I
> > > can
> > > see that there appear to be a lot of fragmentation. There are many
> free
> > > blocks scattered in the middle, but everything to the top and bottom
> of
> > > the
> > > tablespace appear full.
> > >
> > > My question is, other than exporting, dropping the tables, and
> importing
> > > them, is there another way to reorganize the tables in the tablespace?
> > > We'd like to do this with as little disturbance to our users as
> possible.
> > >
> > > Are there any tools that someone out there in Oracleland can recommend
> to
> > > keep track of fragmentation and to reorganize tablespaces?
> > >
> > > Any light that someone in this list can shed will be greatly
> appreciated.
> > >
> > > Thanks
> > >
> > > Ana E. Choto
> > > Systems Programmer
> > > American University
> > > Office of Information Technology
> > > Phone (202) 885-2275
> > > Fax (202) 885-2224
> > >
> > >
> > > ---
> > > You are currently subscribed to oracle as: lisa_koivu_at_gelco.com
> > > To unsubscribe send a blank email to
> leave-oracle-119953R_at_telelists.com
> > > or visit the Oracle mailing list on the Web at:
> > > http://www.telelists.com/cgi-bin/lyris.pl?enter=oracle
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > ---
> > > You are currently subscribed to oracle as: rdmorton_at_switch.com
> > > To unsubscribe send a blank email to leave-oracle-76034V_at_telelists.com
> > > or visit the Oracle mailing list on the Web at:
> > > http://www.telelists.com/cgi-bin/lyris.pl?enter=oracle
> >
> > ---
> > You are currently subscribed to oracle as: gortiz_at_houston.omnes.slb.com
> > To unsubscribe send a blank email to leave-oracle-102619X_at_telelists.com
> > or visit the Oracle mailing list on the Web at:
> > http://www.telelists.com/cgi-bin/lyris.pl?enter=oracle
> >
> >
> > ---
> > You are currently subscribed to oracle as: jkstill_at_bcbso.com
> > To unsubscribe send a blank email to leave-oracle-20875Y_at_telelists.com
> > or visit the Oracle mailing list on the Web at:
> > http://www.telelists.com/cgi-bin/lyris.pl?enter=oracle
> >
>
>
> Jared Still
> Certified Oracle DBA and Part Time Perl Evangelist ;-)
> Regence BlueCross BlueShield of Oregon
> jkstill_at_bcbso.com - Work - preferred address
> jkstill_at_teleport.com - private
>
>
> --
> Author: Jared Still
> INET: jkstill_at_bcbso.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).
>
>
>
> --
> Author:
> INET: achoto_at_american.edu
>
> 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).
 |
 |