| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: TableSpace Storage Parameters?
On Fri, 05 Feb 1999 21:57:19 GMT, "Oracleguru, Suresh Bhat"
<oracleguru_at_mailcity.com> wrote:
>Hi Jonathan,
>
>There is a big difference between dropping objects and deleting and
>inserting records in an object.
>
>Dropping objects does not happen all that frequently on the production
>machine but deleting and inserting records happens all the time and thats
>what causes fragmentation and requires COALESCE of free contiguous space.
>
>I have used pctincrease = 1 on tablespaces for about a year or so on two
>1.5 Gigabytes databases.
>We insert and delete 100,000 records every day in several different tables.
>
>It has simplified my job as a DBA. I hardly reorg, defrag or rebuild
>indexes anymore, where as prior to
>it, I used to either increase the datafile size or reorg every quarter.
Suresh,
Coalescing that SMON performs has nothing to do with your deletes/inserts! No matter how many deletes you perform, the extents that the rows were in remain there and even if there is not any single row in them, they are not available for SMON to coalesce them!
It is just the other way arround as you described it: if you do lot of DROPing or TRUNCATEing, then SMON might find some coalescing bussines to do, while frequent INSERTs and DELETEs do not cause any fragmentation the SMON could fix.
Also, if you belive your PCTINCREASE=1 setting has something to do with the "health" of your indexes (you say you don't have to do any index rebuilds now) - well, don't count on this, better check them now.... Again, this two things are not related!
Stick with the Jonathan's advice, it is the most reasonable cure for the possible impact of tablespace fragmentation on performance.
Regards,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |