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

  • From: Chirag DBA <ChiragDBA@xxxxxxxxx>
  • To: askdba@xxxxxxxxxxxxx, jcave@xxxxxxxxxxx
  • Date: Sat, 2 Oct 2004 10:12:20 +0530

Thanx lot for yr response friends.

Problem is one of my client is a Technical manager n he blasted when
he saw 17 indexes of PK n FKs in my transaction table. He was worring
n asked me that Have i included all the indexes. I said no, there are
still some indexes are to be created on foreign keys and of course on
some other tables on the basis of search criteria.

He was worring that it may happen that it will take 30 indexes, I said
" Yes Sir..".

n he was out of control that this many indexes n all will degrade the

Now How Should I tell him that U don worry abt it, and U can design yr
DB for performance n I did so. But at least each different entity I
should put in a different table. I told him that I can reduce if u
want but the redundancy of data will be too high for that, which I can
say at all a bad design.

Performance is an ongoing Process n U cant expect only DB design
everytime to resolve performance issues. U need to see application
performance, Bandwidth, n many other things as this is a website
application for entire EMEA.

May be I m poor in communicating my views to him, but I m sure... 
Little Knowledge, always dangerous ( For others also...) ;-)

Thanx again guys...

 - Chiku

On Fri, 1 Oct 2004 17:55:34 -0600, Justin Cave (DDBC) <jcave@xxxxxxxxxxx> wrote:
> First off, 10,000 transactions per day works out to just 7 transactions
> per minute, which probably isn't going to put much of a load on the
> system unless you go with some absurd settings.
> Are you using locally managed tablespaces?  That will significantly
> reduce the costs of extent management.  I would also tend to go with
> automatic segment space management (ASSM), particularly if there are
> UPDATE operations.
> Separating tables into different tablespaces will probably not have a
> performance impact unless you are putting the data on separate physical
> disks.  If you are at HP, I'm pretty sure your databases are attached to
> a SAN which is heavily striped, so different tablespaces will not
> translate to any sort of performance benefit.
> If you have data that is inserted, updated for a period of time, and
> then becomes static, partitioning the tables may be a significant
> performance benefit.  If you will be keeping the data forever, it will
> probably also simplify administration.
> Justin Cave  <jcave@xxxxxxxxxxx>
> Distributed Database Consulting, Inc.
> http://www.ddbcinc.com/askDDBC
> -----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
> Chirag@xxxxxx

Other related posts: