Performance issue on table queries on increasing size of unrelated tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Oracle_crusader - 26 May 2005 07:00 GMT

Hi

Im seeing an increase in my applications' DB operations time (C++/ OCI)
on increasing the size of unrelated tables. The size of tables that im
concerned with are not changing over a period of time, whereas the size
of other tables has increased substantially in that same time frame.
The other tables are contantly getting updated (insertion/deletion) and
can grow upto, say 2-3 million records, and can get deleted to 1K
records only and then again grow to 2-3million. I cannot understand why
the size of completely unreleated tables is degrading the performace of
other constant size tables.
All tables are in the same tablespace, and the tablespace contains only
one large datafile.
If anyone has encountered such a situation then please let me know.
Thanks...

Reply to this Message

Sybrand Bakker - 26 May 2005 07:16 GMT

>Im seeing an increase in my applications' DB operations time (C++/ OCI)
>on increasing the size of unrelated tables.

This is definitely a new trend: Posting a question, as vague as
possible, with no details on O/S, and above all : no details on Oracle
version. The poster is hiding himself behind some 'funny' synonym and
a fake e-mail address or a gmail or a hotmail address.
I don't know what the intent of such posts is. Are they -unpaid- quiz
questions? Are they challenging someone's capacity in mind-reading?.
Or is the poster not aware of the fact that Usenet is a volunteer
business, and the best answer for such generic answers is:
Hire a consultant!

--
Sybrand Bakker, Senior Oracle DBA

Reply to this Message

Oracle_crusader - 26 May 2005 07:35 GMT

Hi again.. sorry folks for the stupid nickname... im more of a groups
kindof a person.... well the Oracle version in 9i and OS is Win2k, and
i just wanted to know if someone has ever encountered such a
situation... thanks again...

Reply to this Message

Mark D Powell - 26 May 2005 20:13 GMT

Yes, if the growing tables are accessed by ineficient SQL then this
poorly performing SQL could lead to a decline in the performance of the
database as a whole because the poorly performing SQL will be
performing large amounts of unnecessary IO, flooding the buffer cache,
and filling the temp segments.

First run explain plan on your SQL and make sure it is running
efficiently.
Then if it is in fact fine, tune the rest of the SQL
Once all the SQL has been tuned you can look to see if the DB needs any
tuning

HTH -- Mark D Powell --

Reply to this Message

Daniel - 27 May 2005 03:04 GMT

My only theory as to why the growth of some unrelated table might
affect a query is that possibly these growing tables are using up more
of the buffer cache, and therefore you need more physical IO when
performing this unrelated query, slowing it down. Running the query
with autotrace on might help prove/disprove this theory.

Daniel

Reply to this Message

bchorng@yahoo.com - 31 May 2005 21:57 GMT

I wouldn't jump into the conclusion that these 2 are related.
On one side, you have a problem of increased execution time.
On the other, you have some unrelated tables growing in size.

How do you know they are related? To me these are merely 2
separate observations.

If this is true then we can attribute all DB performance issues
to "some tables growing in size" as almost every single database
in the world has some tables always "growing in size".

I think they are most likely irrelevent and suggest you
should look into v$session_event or trace the questioned session
to find out how it spends its time.

Your problem may not even be in the database at all. Just because
your execution time from the Apps becomes longer does not mean they
all wind up in DB time.

Reply to this Message