Re: Oracle 7's cost based optimizer

In article <2lhsqr$lms_at_usenet.ins.cwru.edu>, Diana Tracy <bs794_at_cleveland.Freenet.Edu> wrote:
>
>In a previous article, dirkt_at_srs.gov ("R. Dirk Tolson") says:
>
>>This is probably a FAQ but since there isn't one:
>>
>>This is the first week that we have had Oracle 7 running in production here and
>>it has been quite a week. We have seen forms and reports that, though not
>>'snappy' in the first place, have taken quite a hit in performance. System
>>tuning was immediately begun and addressed some of it. Yesterday one of our
>>DBAs tracked down another who had made the same transistion and taken the same
>>performance hit. They tracked it down to the cost-based optimizer. Last night
>>it was turned off here and we are awaiting results.
>>
>>In the mean time what experiences have others had with Oracle 7 and the new
>>optimizer? What steps have you taken to optimize performance? Are there other
>>gotcha's out there?
>>
>>TIA
>>
>>// dirk tolson
>>// dirkt_at_srs.gov
>>
>One thing that might help is to ANALYZE the tables. You can do them all at
>once, look it up in the manuals. Basically, the cost-based optimizer does
>not work efficiently if the ANALYZE command has not been run because it
>is guessing at the statistics for your tables. With accurate statistics, it
>works much better.
>--
>Diana Tracy, System Designer -- Excitement, Adventure
>bs794_at_cleveland.Freenet.Edu -- and Really Wild Things

I second Dianas recommendation! After putting an Oracle 7.0.15 database (3GB now) into system test we took a serious hit on performance. We had a series of batch jobs running for like 5 days without ever completing...
After fooling around for some time we RE-CREATED one of our system test databases and got the same job running in 1/2 a day. As this was a rather dramatic meassure we decided to go slow on the other database and try out less dramatic things one step at time and measure performance in between. We started out ANALYZING our tables and got 9x% of the performance gain just by doing that.

--
Erik E. Graversen, Systems Engineer	--	    eeg_at_criinc.com
CRI Inc, Bellevue WA.			--	crieeg_at_halcyon.com