Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Table sizing exercise

Re: Table sizing exercise

From: Jerry Gitomer <jgitomer_at_erols.com>
Date: 2000/04/08
Message-ID: <38EFEAB7.CB5B1C09@erols.com>#1/1

Gregory P Lechkun wrote:
>
> Hello,
>
> I'm tuning a table for size, extents, pctfree, and pctused.
> Here are some of the statistics on the table:
>
> block_size 8192
> initial_extent 16384
> next_extent 1048576 -- This was originally 8192 before it
> was adjusted
> max_extents 505
> num_rows 35698
> blocks 315
> empty_blocks 60
> avg_space 3011
> avg_row_len 42
> pctfree 10
> pctused 40
>
> Now, in this table, every row will eventually be deleted. The
> table is being used as a circular buffer in a FIFO manner, and
> rows are never updated, just deleted. Presently, the oldest
> row in the table is dated March 12, but a majority of the rows
> will be cleared out within 7-10 days. This table will start
> off at the 35,000 rows and within a year will have 1,000,000
> rows.
>
> I'm figuring I'm going to set the initial_extent & next_extent
> to 1048576, and setting the pctfree = 5 and pctused = 90 (Kevin
> Loney's DBA Handbook suggest pctused should be 95-pctfree).
>
> - Does this sound reasonable for a table that has such volatile
> data?
> - Will having such values in the pctfree & pctused cause a
> performance issue?
>
>
> --
> Regards,
> Greg Lechkun
> gpl :-)
>
> DTE
> Energy====================================================================
>
> DTE ////// //////////// Gregory Lechkun - EM&D/PDO-PT
>
> DTE // // // // Power Application Software
> Engineer
> DTE // // // //// lechkung_at_detroitedison.com
> DTE // // // // g.p.lechkun_at_ieee.org
> (personal)
> DTE ////// // ////// (313)235-9445 Office
> (313)940-3306 Pgr.
> (313)235-8597 FAX
>

        I would suggest that you make INITIAL and NEXT the same size and that set PCTINCREASE to 0. Doing so will avoid creating unusable holes in your datafile.

-- 
Jerry Gitomer 
Once I learned how to spell DBA, I became one
Received on Sat Apr 08 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US