Re: [Q] Optimizer_mode and performance?

Dear Cheri

I read some where if U analyze tables U must analyze whole schema and see that optimizer follows COST

Pl.. correct me if I am wrong

Rao

--

On Fri, 01 Dec 2000 13:00:25  
 Cherie_Machler wrote:


>L,

>

>Your vendor has probably not optimized their application for Oracle.

>Their answer to increase hardware is just the easy out for them.

>

>Probably a better suggestion from your point of view is to either

>pressure the vendor to add hints in their SQL to force cost-based

>optimization when that works better and rule-based when that works

>better.  The vendor will probably not want to do this.

>

>One thing you might be able to do is to figure out which tables are used

>by the SQL that runs better with the cost-based optimizer and which tables

>are used by the SQL that runs better with rule-based.  If you are very, very

>lucky, these two lists of tables will be mutually exclusive.  If that's the

>case,

>then remove the statistics for the tables that run better under rule-based

>and continue to analyze the tables that run better under cost-based.

>

>With optimizer-mode set to choose, this selective analysis of tables

>will force optimizer mode of cost for all SQL referencing ANY (even just one)

>analyzed

>table and will force rule-based optimization for all SQL which references

>ONLY non-analyzed tables.

>

>Short of having access to the code in order to add hints, it's hard to

>do much on your end.  You might want to try using partitioned tables

>and indexes to improve performance.

>

>You also might try switching between cost-based and rule-based at

>different times of the day if the SQL that performs better under cost-based

>is run at different times than the SQL that performs better under rule-based.

>

>On our data warehouse we run with ALL_ROWS at night for batch loads

>and batch jobs and with CHOOSE during the day for on-line ad-hoq queries.

>

>Hope this helps.   I would fully investigate alternatives before jumping

>to buy more hardware.

>

>Cherie

>---------------------- Forwarded by Cherie Machler/GELCO on 12/01/2000 03:41 PM

>---------------------------

>

>

>L <leed_at_chele.cais.net> on 12/01/2000 02:00:25 PM

>

>Please respond to ORACLE-L_at_fatcity.com

>

>To:   Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>

>cc:    (bcc: Cherie Machler/GELCO)

>

>

>

>

>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.

>

>--

>Please see the official ORACLE-L FAQ: http://www.orafaq.com

>--

>Author: L

>  INET: leed_at_chele.cais.net

>

>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

>also send the HELP command for other information (like subscribing).

>

>

>

>

>-- 

>Please see the official ORACLE-L FAQ: http://www.orafaq.com

>-- 

>Author: 

>  INET: Cherie_Machler_at_gelco.com

>

>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

>also send the HELP command for other information (like subscribing).