| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: [Q] Optimizer_mode and performance?
I think that this is not correct. You must analyze all the tables and/or
indexes that are joined in one single query. The optimizer generates a plan
for each query individually, so whether other tables/indexes are analyzed or
not does not make difference. Otherwise for each query oracle would have to
check all tables/indexes in a schema to see whether any of them is or is not
analyzed.
If some tables/indexes in a join query are analyzed and some are not, oracle would go with the cost based optimizer, it this may easily end up as the worst solution, as non-primary key indexes for non-analyzed tables usually not be used. Also if you are using a hint, it invokes the cost based optimizer, and if the tables are not analyzed the subotimal plan could result.
Djordje
> 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).
> >
>
>
> Get FREE Email/Voicemail with 15MB at Lycos Communications at
http://comm.lycos.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: aaaa wwwwww
> INET: krisibm_at_lycos.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
Received on Fri Dec 01 2000 - 19:37:16 CST
 |
 |