Re: newbie Q: Oracle 6.x; percent_increase and next_extent settings

Philip Bondi; 416.368.7979 x224 (pjbondi_at_agate.spctrm.com) wrote:
: Hello to all:
 

: Thank you very much for reading this posting.
 

: One of our client's recently had a disasterous out of space situation.
: The most active table in our database assumedly started off with the
: standard 10k next_extent size and 50% percent_increase setting.
: However, the next_extent size finally became larger than the largest
: available extent.
 

: What is the standard method of protecting against such situations?
: Under Sybase SQL Server extent sizes are fixed at 16k. Does anyone
: find a flaw in standardizing all Oracle tables to have a 16k
: extent_size and 0% percent_increase?
 

: Please help a couple of newbies! Reply to via a posting and via
: direct mail to:
 

: pjbondi_at_spctra.com
: bessiet_at_spctra.com
 

: Thank you in advance.
 

: .....Philip.
 

: PS: Info about our system, for those who care:
 

: - 24 table schema
: - MF_ORDER is the most critical table
: - 1,200 orders per day (avg)
: - 3,500 orders per day (peak during Canadian RSP season)
: - purged weekly to keep 45 calendar days of history
: - all inserts and deletes are sequential
: - most queries leverage an index based on date of the order or table scan
: - OLTP environment with warm backup mechanism implemented via cuts
: of the Oracle transaction log that are loaded onto another instance
: - processing window: 8am-4pm
: - WYSE 386MP 4.0 Unix
: - Oracle 6.0.37
: - pledge of continued product support from Oracle for this platform
 

: --
: Philip Bondi Spectra Securities Software, Inc.
: Database Administrator 150 York St., #700, Toronto, Ontario, M5H 3S5, Canada
: pjbondi_at_spctra.com voice: (416)368-7979 x224 fax: (416)368-7886
: --
: Philip Bondi Spectra Securities Software, Inc.
: Database Administrator 150 York St., #700, Toronto, Ontario, M5H 3S5, Canada
: pjbondi_at_spctra.com voice: (416)368-7979 x224 fax: (416)368-7886

All Unix implementations have a finite value for max extents usually about 120. If you want to keep fragmentation to a minimum precreate your table (initial extent) big enough to insert all your new rows in the time between DB
refreshes (I try to keep refreshes down to 2/3 a year). To keep the disc utilisation predictable set pctincrease to 0 and Next to a reasonable value ( I usually try at about 20% for volatile tables). Despite all Oracle's assusances I find that fragmentation does slow retreival down so I try not to get large numbers of extents, particularly on tables. Also I run a monitoring report weekly which shows all the tables that are getting fragmented so I can keep an eye on the system.

Hope this is useful

Stuart Received on Wed Mar 22 1995 - 17:19:48 CET