RE: [Q] Optimizer_mode and performance?

Nothing of the kind has not been established. The "compute" option is the only exact option, everything else is just an approximation. Except with the help of controlled substances, an approximation can not be better then reality. What has been established is that, provided your data is not deviating from the average by too much, the ESTIMATE option gives you almost as usable results as the COMPUTE option. Try using the DBMS_STATS package instead of the direct analyze for several reasons:
a) It's faster and can work in parallel. b) It allows statistics to be exported/imported from another database.

Finally, you can collect trace and statistics information, identify which statements perform suboptimally and get the vendor to tune them properly instead of giving you a rude suggestion of getting more horsepower. But, again, you must be able to identify the suboptimal SQL by yourself. Any vendor will always tell you that their SQL is perfect unless confronted with the evidence to the contrary. If there aren't any suboptimal SQL statements, then the suggestion is valid: get a bigger box, just like that Taco Bell dog in the Godzilla commercial.

-----Original Message-----
From: Kresimir Fabijanic [mailto:kresimir_at_ozemail.com.au] Sent: Friday, December 01, 2000 8:51 PM
To: Multiple recipients of list ORACLE-L Subject: Re: [Q] Optimizer_mode and performance?

Dear L

It has been established that estimate based on (test vary from one version to another and from platform to another, so it is best you try it youserlf) sample of 10 to 20 % ensures better performance than compute.

HTH Regards

Kresimir Fabijanic

• Original Message ----- To: <oracle-l_at_fatcity.com>; <oracledba_at_quickdoc.co.uk> Sent: Saturday, December 02, 2000 6:55 AM

> We have ORACLE 7.3.4.4 on SUN SPARC Solaris 2.6. Our ORACLE database
> running third party application on it. Recently, due to data growing
> (2i.5GB data on data tablespace), user starting to compliant performance
> slow on some SQL statements. The "optimizer_mode" we use are "choose" and
> I "analyze" the schema every week use following statements:
>
>
> exec dbms_utility.analyze_schema('USER1', 'COMPUTE',NULL,NULL,'FOR ALL
> INDEXED COLUMNS');
> exec dbms_utility.analyze_schema('USER1', 'COMPUTE');
>
>
>
> I turn on "tkprof" to trace the third party application (we DON'T have
> source code). I found some SQL statements run quickly under "rule" mode.
> After I change database "optimizer_mode" to "rule", users complaint other
> SQL statements run very slow. I report this problem to third party
> company. The engineer continue said following:
>
> 1. Analyze schema (actually we did)
> 2. Buy high speed CPU and high speed Hard disk RAID.
>
> My questions are:
>
> 1. Does my "analyze" statements collect NOT enought information?
>
> 2. user better hardware to fix software problem is correct way?
>
> 3. any other suggestion?
>
> Thanks.
>
>
> --------
> Think you know someone who can answer the above question? Forward it to
them!
> to unsubscribe, send a blank email to oracledba-unsubscribe_at_LAZYDBA.com
> to subscribe send a blank email to oracledba-subscribe_at_LAZYDBA.com
> Visit the list archive: http://www.LAZYDBA.com/odbareadmail.pl
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Kresimir Fabijanic
  INET: kresimir_at_ozemail.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may