Re: How to Monitor fragmentation

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 ----- From: oradba guess <oradba_guess_at_hotmail.com> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Wednesday, November 01, 2000 4:32 PM Subject: How to Monitor fragmentation

> 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).
Received on Wed Nov 01 2000 - 17:38:55 CST