Re: The query seems efficient - why poor performance?

On Mon, 30 Jun 2014 16:15:48 -0700, vsevolod afanassiev wrote:

> Yes, the plan is the same - at least this is what Statspack says.
> It used a different plan for a while, but then it went back to the
> original plan.

Vsevolod, plans degrade over time. Tables grow, there are split rows or migrated rows, indexes grow bigger and have more levels, columns are added and modified and the application systems degrade in performance. Application systems have their life cycle. There is usually a point in time when it's necessary to start from scratch and re-engineer the application.
Since you are using Oracle RDBMS 9i, I would say that your application system is coming close to that moment. The newer versions of the database, with the automatic self-tuning abilities, are more resilient to the change and will keep performing well for a longer periods of time, but they too will need to be re-engineered from time to time. There is so called 80:20 rule which says that 80% of the performance improvement is achieved by 20% of the effort necessary to achieve 100% performance improvement. And that's the tipping point: when you start counting milliseconds, it's time to start from scratch or get a bigger box and postpone starting from scratch by adding the raw power.

-- 
Mladen Gogala
The Oracle Whisperer
http://mgogala.byethost5.com