[askdba] Re: How to tune tables for inserts and Updates?

  • From: Todd Pepling <tpepling@xxxxxxx>
  • To: askdba@xxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 01 Oct 2004 16:38:43 -0400

First, create LMT tablespaces w/ Automatic Space Segment Management.  Gets
rid of the whole freelist/initrans issue.
Second, tune (match) database IO to operating system IO on the via the
"db_block_size * db_file_multiblock_read_count" parameters.  In Solaris,
it'd be the value of maxphys*pagesize.

Third, create different buffer pool(s) for the extent size(s) of your OLTP
tables (i.e. db_2k_cache_size, db_4k_cache_size, db_8k_cache_size,
db_16k_cache_size, db_32k_cache_size), and put your heavy OLTP segments in
the appropriate tablespace(s) to utilize the different buffer pools.

Forth, cache small "look-up" or "validate" tables (you'll need to set

Fifth, spread your IO across as many disks (physical spindles, not logical
ones) as you can.

My big DB processes over 200,000 transactions in a 30 minute batch job w/ 8k
blocks (yeah, I know), so 10,000 shouldn't be too tough to achieve.

-----Original Message-----
From: askdba-bounce@xxxxxxxxxxxxx [mailto:askdba-bounce@xxxxxxxxxxxxx]On
Behalf Of Chirag DBA
Sent: Friday, October 01, 2004 2:10 AM
To: askdba@xxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Subject: [askdba] How to tune tables for inserts and Updates?

Hi friends,

I have a Database in which few tables are for Transaction.

As the number of transactions will be more than 10000 per day, I need
to decide the tuning strategy for the tables having more hits.

I m planning to put them ina different tablespace.

Any other idea how I can do that.

 - Chirag Majmundar

-- Attached file included as plaintext by Ecartis --
-- Desc: Signature

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any

Other related posts: