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

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Mon, 04 Oct 2004 06:48:53 +0000
Message-Id: <1096872533l.21496l.0l@medo.noip.com>

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=20
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

Received on Mon Oct 04 2004 - 01:44:28 CDT