Re: a heated discussion between Development Team & "a" DBA: reorg or not to reog

> Gather Statistics - If you already do this on a regular basis, then
I do it 3 times a week. with dbms_stats ..

> Rebuild Indexes - Indexes get fragmented very easily with a lot of
before the export/import, i did real reorg, ALTER TABLE MOVE..I wrote a script that puts all tables/indexes into 3 different extent size buckets according to their "used" size, not actual size. and runs ALTER TABLE MOVE STORAGE for the tables and REBUILDS their indexes changing the storage parameters according to used size.. so it is done.

> since Oracle does not reuse the space occupied in an index after a
I think Oracle does use the deleted index entries/blocks..it just does not deallocate them.

> turn the tables on them. I would begin capturing the SQL that occurs
> during the batch process and monitor it over time. By turning on sql
I have a script that turns on sql trace and emails me the TOP-N SQL (sorted by exec time, parse time and fetch time) of most CPU intensive process on unix for given period. I ran the script several times during night and each run shows that a DELETE is run thousands of times against a 80M table and it does tablescan. I recommended the index, created it in the test server and it cut down the time to half. But this particular manager of development team still wants to do export/drop db/import before trying the indexes.

> creating user or application objects in the system T.S. (and you are
> NOT doing that, correct) you should not have anything to worry about.
no user objects in SYSTEM. i even have an alert that tells that if there is any.

i.d. Received on Thu Jun 23 2005 - 20:04:51 CDT