|
Home > Archive > Oracle Server > May 2005 > Performance issue on table queries on increasing size of unrelated tables
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Performance issue on table queries on increasing size of unrelated tables
|
|
| Oracle_crusader 2005-05-26, 3:23 am |
| 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...
| |
| Sybrand Bakker 2005-05-26, 3:23 am |
| On 25 May 2005 23:00:15 -0700, "Oracle_crusader" <impraveen@gmail.com>
wrote:
>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
| |
| Oracle_crusader 2005-05-26, 3:23 am |
| 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...
| |
| Mark D Powell 2005-05-26, 8:23 pm |
| 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 --
| |
| Daniel 2005-05-27, 3:23 am |
| 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
| |
| bchorng@yahoo.com 2005-05-31, 8:23 pm |
| 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.
|
|
|
|
|