Database performance issue

[sha1]

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

[davey23uk]

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

[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

[waitecj]

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.

[pareshjavkar]

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.

[waitecj]

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).

[slimdave]

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.

[Bore]

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

[Mr.Hanky]

Any problems on the OS side?

[tomcat]

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.