| 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: How to Monitor fragmentation
Thanks Tom...
But in this case even with queries which go through table scans, we are getting into the problems. So probably dropping and recreating the indexes wouldn't help. The only option is to rebuild the table.
>From: "Tom Pall" <tom_at_cdproc.com>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Re: How to Monitor fragmentation
>Date: Wed, 01 Nov 2000 15:43:31 -0800
>
>Nasty business, these large inserts/deletes.
>
>Remember that when you rebuild indexes, Oracle places canned
>statistics in the data dictionary. Unless you are plan stability, you
>might want to delete or re-analyze the indexes after rebuild.
>
>Raising pctused to 99 and pctfree to 0 works for me in helping to
>reuse space under the high water mark.
>
>If possible, rename, create new table as select from old or alter
>table/move whenever possible helps. Especially if you had a big
>job once that gave you a bunch of now empty table extents.
>
>Possible to partition your tables so you can drop instead of delete?
>
>----- Original Message -----
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Sent: Wednesday, November 01, 2000 4:32 PM
>
>
> > Hi,
> > In our datawarehouse application, there are certain tables
> > where we do large deletes and inserts everyday. Frequently we
> > see lot of performance problems with those tables. A query which
> > was running fine yesterday doesn't run atall today. When we drop
> > and recreate the table, the queries run fine.
> >
> > It's probably because of the fragmentation.
> > Any help on resolving this problems will be gratly appreciated.
> >
> > thanks,
> >
> > ora
> >
>_________________________________________________________________________
> > Get Your Private, Free E-mail from MSN Hotmail at
>http://www.hotmail.com.
> >
> > Share information about yourself, create your own public profile at
> > http://profiles.msn.com.
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: oradba guess
> > INET: oradba_guess_at_hotmail.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).
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Tom Pall
> INET: tom_at_cdproc.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).
Share information about yourself, create your own public profile at Received on Wed Nov 01 2000 - 18:12:34 CST
 |
 |