-
Database performance issue
we are using oracle 9i in LINUX platform, since last 4 days database performance is drastically decreased it is when number of active sesseions increases then performance is slow even queries and report are taking time. There was no major changes made in the database except some rebuilding of indexes.
Based on our technical support we have increased our number of processes to 350 and sessions_cache_cursor to 150 but there is no improvement. We have 60+ users and sessions parameter is 390. Ram is 1 GB. As this is our production server we are having a very hard time
Can you please help me in this regard
Thanks in advance
-
Originally Posted by sha1
There was no major changes made in the database except some rebuilding of indexes.
So there was a major change, why did you do that - dont you think that perhaps that was the problem?
Are your stats up to date?
Have you ran statspack to compare when it was performing well
Have you traced any queries to see where they are taking their time
Have you dont any investigation apart from randomly changing some parameters
-
Thanks for your prompt reply
Rebuilding of indexes was done on 3 tables because of the performance of one report, as it was very slow to process so our dba did this rebuilding ( they recommend us to rebuild all the schema indexes.)
Yes we ran statspack and based on the comparision report our dba made changes in parameter
yes we have traced out top 10 queries which are taking time but these were running fine before
As for our investigation we have not found anything except this rebuilding of indexes..
IS this problem beacuse of rebuilding of indexes? what problem can be occured beacuse of this activity?
Grateful for you advice
-
There are a number of threads on the AskTom website that explain this pretty nicely. The one I have linked to is just one of them.
Assistance is Futile...
-
Originally Posted by sha1
Thanks for your prompt reply
Rebuilding of indexes was done on 3 tables because of the performance of one report, as it was very slow to process so our dba did this rebuilding ( they recommend us to rebuild all the schema indexes.)
Yes we ran statspack and based on the comparision report our dba made changes in parameter
yes we have traced out top 10 queries which are taking time but these were running fine before
As for our investigation we have not found anything except this rebuilding of indexes..
IS this problem beacuse of rebuilding of indexes? what problem can be occured beacuse of this activity?
Grateful for you advice
The problem is not rebuilding of Indexes, it might be because of the stats the query is getting.
Try and analyze the 3 tables whose indexes you rebuild.
-
Originally Posted by pareshjavkar
The problem is not rebuilding of Indexes
You can be certain of that can you? I'm not saying your are wrong, but I am saying that is a very strong statement from the information given.
Definitely check stats for indexes and indexed column histograms (if you had them before).
Assistance is Futile...
-
Originally Posted by sha1
Yes we ran statspack and based on the comparision report our dba made changes in parameter
...
yes we have traced out top 10 queries which are taking time but these were running fine before
...
IS this problem beacuse of rebuilding of indexes? what problem can be occured beacuse of this activity?
What parameter, and what change?
The queries were running fine before so ... so they must be running fine now? Do those queries access the tables you rebuilt the indexes on?
You may be incurring more block split activity on those indexes as they regrow to thier "comfortable" size. You may also be incurring more index-based access than is healthy because the indexes now look cheaper to access for range scans and fast full index scans.
-
People,
I never saw performance problem to be solved like that.
The performance needs investigations and often comes to try and fail/succeed aproach.
Do not really belive that one can make meaningfull statement based on the information provided here.
Moreover, even if the information was enough, I doubt that anyone of us could spend enough time on investigating it, in order to give a meaningfull statement
-
Originally Posted by Bore
Do not really belive that one can make meaningfull statement based on the information provided here.
Moreover, [...] I doubt that anyone of us could spend enough time on investigating it, in order to give a meaningfull statement
This is just a forum you know - yes, perhaps it's impossible to tune a "slow database" via a forum - but the goal is to generate ideas and share insights.
"False data can act only as a distraction. Therefore. I shall refuse to perceive you." - Bomb #20
-
Any problems on the OS side?
I remember when this place was cool.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|