Re: optimal storage parameters for heavy inserts tables

From: <sfaroult_at_oriole.com>
Date: Mon, 29 Mar 2004 06:37:02 -0500 (EST)
Message-ID: <20040329063702.GA11716@mail01g.rapidsite.net>

Syed,

In such a case what you mostly have to look for is dynamic allocation (not very good if Oracle spends a lot of time adding new extents, it's costly), contention, and internal table fragmentation. To Niall's suggestion of giving due attention to free-lists I would add a few points :

pctfree. Lower pctfree if you only have inserts, but increase it if you have updates likely to make the row bigger (updating something from NULL to something, mostly). The idea is to compute how many rows you have on average in your block when everything is said and done, and to set pctfree so as not to insert more rows than that initially - otherwise parts of some rws will have to be migrated elsewhere.
initrans. This has to be checked in relation to free lists. Given a number of free lists, how many concurrent inserts or updates do you expect to hit the same Oracle block ?

There is also something I see too often, which is overindexing. Index maintenance is extremely costly, so if you can get rid of a number of them, all the better.

You may also be interested in considering things such as reverse indexes, which come with a number of gotchas (basically, wave farewell to range scans). IOTs may also be worth considering in some cases.

HTH S Faroult

> Hi List,
>
> Some of our tables have very heavy inserts and a couple of tables among
> them have heavy updates as well. What are the good storage parameters
> for these tables. Right now I have initial = 1024, next=initial and
> pct_increase=0. We still uses DMT TBS and our version is 8.1.7.4. We
> are starting new season, so we have truncated all the old data and
> starting a fresh. I would like to have some good storage parameters
> settings for those tables.
>
> I am planning to create a new TBS as LMT and move those heavey inserts
> tables as uniform extent size as 1m.
>
> I would be thankful to you if you can suggest good storage parameters
> for these tables.
>
> Best Regards,
> Syed Jaffar Hussain
>

Oracle scripts, papers and DBA Tools --
Oriole Corporation --
http://www.oriole.com/ --

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Mon Mar 29 2004 - 05:38:20 CST