| Need help on tuning [message #146186] |
Wed, 09 November 2005 02:06  |
gkodakalla
Messages: 49
Registered: March 2005
|
Member |
|
|
Hi all,
I have one pro*c program which was performing well ( Means 99% of the responses are less than a second) on oracle 9.2.0.
For the last 10 days, only 75 to 85% of responses are less than a second. There are no changes in program for last 6 months. I compared the indexes in development with the indexes in production and found that all required indexes are available. Please advice how to proceed on finding the root cause:
1) I am not sure if LAST_DDL_TIME in ALL_OBJECTS can help us to analyze further
on this problem. For some indexes, LAST_DDL_TIME is 09-NOV-05 and for other
indexes it is showing previous year's values.
2) What are the points i have to verify now to find the root cause.
Thanks in advance.
Giridhar
|
|
|
|
| Re: Need help on tuning [message #146187 is a reply to message #146186] |
Wed, 09 November 2005 02:23  |
Frank
Messages: 7901
Registered: March 2000
|
Senior Member |
|
|
I think you are on the right track: try to determine what happened around 10 days ago.
My advice would be to check the following
- based on the last_ddl_time of then indexes it could be that they were in some way recreated and not analyzed
- there was a large import, causing the distribution of data to change, rendering existing statistics 'invalid'
In both cases, analyze the appropriate objects.
Alas, it's too late now, but if you would use statspack, you could have made (and stored) a snapshot of the situation where all was nice and performant. You could compare this to a snapshot of the current situation to show what had changed.
hth
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
