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

  • From: Chirag DBA <ChiragDBA@xxxxxxxxx>
  • To: gogala@xxxxxxxxxxxxx
  • Date: Mon, 4 Oct 2004 13:11:29 +0530

Well Well Well...

Thanx....  I think I m missing the following.

Pumps means night jobs, which will repopulate my other tables for look
ups from some external client back end systems.

I m doin DML only on 19 tables, others I didn consider as they will be
used only for select.

Of course they have PK , FKs and other indexes on the basis of the
Select Criteria, but I m more worring abt Inserts and updates.

and as I said, the Querries are not too big to hit on the DB.I can say
I don need help of Cary Millsap, Dan Tow, Jonathan Lewis and Tanel
Poder to optimize, I have me for that.

and 5000 transacitons, make sure that it is per day.

My machine will be of 2 GB RAM, Dual Processor.HP - UX 11i.

I said MTS because I know that number of users will be very hign and
the data that will be transferred is only text data, so very low. MTS
can actually help me to save my resources.

Anything more information U want, Gogala ???

 - Chiku

On Mon, 04 Oct 2004 06:48:53 +0000, Mladen Gogala <gogala@xxxxxxxxxxxxx> wrote:
> On 10/04/2004 02:17:59 AM, Chirag DBA wrote:
> > Hi,
> >=20
> > I have one question here.
> Just one?
> >=20
> > I have a database with 60 tables and out of that 19 are for
> > transaction and others will be populated with the help of nightly
> > pumps.
> Nightly pumps? What is this? Popeye the sailor spinach database?
> >=20
> > there are 17 indexes which are PK and FK, n others are required to
> > create as per the requirement like Creating an index on Foreign key n
> > some on the basis of search criteria n all. I think it may go upto 30
> > indexes.
> 60 tables and up to 30 indexes? So, more then 50% of the tables doesn't hav=
> e=20
> a primary key? That probably means that you don't need to identify records
> in those 30+ tables? Then why are you creating them?
> >=20
> > Environment will be HP - UX 11i and 9iR2. I m expecting 5000
> > transaction so I think 7 transactions in 2 minutes
> 5000 transactions per millennium is easily sustainable. 5000 transactions
> per second is a bit of an art, but can also be sustained. It's not just the
> number of transaction that is important, the period over which they should
> happen also matters.
> .
> >=20
> > Can this lead to any Performance problem ? I m very confident but the
> > concrns from the client made me think again.
> Well, machine has a nice operating system, but the planned size of database=
> would
> be helpful in answering that question, as well as the size of memory, numbe=
> r of CPUs
> type and size of your disk configuration and the type of application that y=
> ou want to
> run. If you think that OS is enough to answer that question, then you shoul=
> d go with=20
> Windows. I guess that you will end up with a RAID-5 for redo logs.
> >=20
> > I think this is a very small DB compare to other Databases.=20
> Especially when compared to the databases with the capital "D".
> >=20
> > I have a plan for Block level tuning at a time of DB creation like
> > small block size, n pctfree, pctused, freelists and all are ok.
> As long as the plan is there, you're safe. If the plan doesn't agree
> with the facts, disregard the facts. Good plan is a terrible thing to=20
> waste.
> >=20
> > Or else I may put it in MTS too.=20
> Now, I am curious. Every DBA book tells you that MTS is an overhead and tha=
> t some=20
> features do not work with shared server session (automatic PGA management c=
> omes to mind)
> and yet you are planning to put it in, without even having a performance pr=
> oblem. Have you
> considered hiring a consultant to do such a job?=20
> >=20
> > No Querry is too big as I have tried to each entity in a different
> > table which has reduced my data redundancy n to merge the keys I have
> > Transaction tables which are very few.
> With 50% of tables not having primary key, I'm fairly sure that there will =
> be=20
> some interesting queries which would require joint expertize of Cary Millsa=
 p, Dan Tow, Jonathan Lewis and Tanel Poder to optimize.
> >=20
> > can any one see any performance issue?=20
> SQL originally stood for "Said Quixotically on Laurel" and was utilized by =
> the=20
> Oracle of Delphi, the early leader in executive decision support systems, n=
> ot to
> be confused with the Oracle Corp. So, let me give you that type of answer: =
> if you
> go ahead with your plans, somebody will be very happy.
> --=20
> Mladen Gogala
> Oracle DBA
> --
> http://www.freelists.org/webpage/oracle-l

Other related posts: