> Seems like something changed just over the last 4-6 weeks, but the last
> parameter change was 4 months ago.
> > - optimizer mode = choose.
> parameter is set to choose, and we use optimizer hints.
>
> > Did you generate statistics on tables / indexes?
> Yes, last_analyzed just 3 weeks ago.
>
Getting worse the last 4-6 weeks, last_analyzed 3 weeks ago? And you got
expensive statements?!
Well, seems to be the optimizer, which looks at data volume and distribution
and so on. So at some time, limits like for selectivity are crossed and
totally different (sometimes much better, sometimes much worse) plans are
choosen, thus killing the database. And everybody tells you "we didn't touch
anything", which is the truth.
I'm no friend of up-to-date statistics any more. I've seen to much trouble.
My opinion is to keep stats as they are (even if 1 year old) as long as the
performance is ok. If not, then go for new stats.
Regards
Stephan
BTW: As Sybrand wrote, it's always the application. You can get the biggest
gain by far by tuning the application compared to tuning some oracle
parameters, etc. So go for the sqls.
Received on Wed Apr 30 2003 - 05:20:15 CDT
