[Q] Optimizer_mode and performance?

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)

Subject: [Q] Optimizer_mode and performance?

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