Re: optimal storage parameters for heavy inserts tables
- From: <sfaroult@xxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Mon, 29 Mar 2004 06:37:03 EST5EDT
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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
