Re: Oracle performace degradation problem

On Tue, 28 Feb 2006 04:12:54 -0800, Nata wrote:

> After archiving the analyze (gather_schema_stats) was performed and DB
> instance was restarted and tests were performed.
> Now the problem: we discovered performace degradation of some queries
> on the original schema in 200%-300%. The question: WHY???

Well, collecting statistics is not always a wise thing. Optimizer might have changed some of the query plans and is using full table scan instead of index. You may want to gather system statistics as well and/or gather histograms for skewed columns. You may even want to rebuild some tables and sort them with respect to the indexed columns so that your indexes will have nice clustering factor.

> Ok, we have
> not executed yet de fragmentation and index rebuild,

What the heck is that? What is defragmentation? Are you using locally managed tablespaces or not? If not, why not?

> but WHY performace
> results got worsen and not remained at least the same????

There are 3 types of falsehood:

1. Lie
2. Darned lie
3. Statistics.

I believe you've been hit by the type 3, the worst one of all. It may have been easier to help you out if I knew your Oracle version. I am starting to sound like Dan or Sybrand, but it does make a lot of difference. 8i, 9i, 10.1 and 10.2 are very different animals. Here I implicitly expressed my hope that you're not running anything below Oracle8i. On the other hand, if you are running 4.0.14, 5.1.22 or 6.0.34, can you send me a copy of your installation kit?

-- 
http://www.mgogala.com