Re: [askdba] Re: How to tune tables for inserts and Updates?
- From: Mladen Gogala <gogala@xxxxxxxxxxxxx>
- To: ChiragDBA@xxxxxxxxx
- Date: Mon, 04 Oct 2004 06:48:53 +0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
