Re: USING COST BASED OPTIMISATION ON A MULTI-PROCESSOR,MULTI-DISK DRIVE SERVER

I think you will find if you check your queries after you have done the analyze that they are not using the indexes. Have you analyzed the indexes? I think if you do the CBO it will then recognize that these are worth using (as It does not know this before optimization) and you queries will improve and load will reduce.

regards
Ross Cuthbertson

Oracleguru, Suresh Bhat wrote in message <01be6fe4$e600eec0$a504fa80_at_mndnet>...
>Hi!
>
>I had a similar problem on RDBMS 7.3.3.3. It used to take hours rather
>than 10 minutes.
>
>Problem was joining ANALYZED tables and views and using CBO(cost based
>optimizer).
>
>CBO is the default optimizer mode even if 1 joined table has analyzed
>statistics. RBO is the default for non-ANAYZED table joins.
>
>What I ended up doing was to use the following command to delete the
>statistics on all the analyzed tables that were joined and using RBO(rule
>based optimizer).
>
>analyze table_name delete statistics;
>
>What I remember was, that if even one joined table has analyzed statistics,
>then all tables get analyzed at run time for the SELECT command and this
>could take quite a while for large tables and thats what was my problem.
>
>Also, if even one table is analyzed then CBO is used.
>
>Let me know if you find anything else.
>
>Hope this helps.
>
>
>Oracleguru
>www.oraclguru.net
>oracleguru_at_mailcity.com
>
>
>sudha_at_altasoft.com wrote in article <7ck0oh$l5o$1_at_nnrp1.dejanews.com>...
>> Hello!! In our company we are currently using a Production Environment
>which
>> is Multi-cpu ( 4 processors ), Multi-disk drive ( Meaning the DataBase
>Table
>> Data are spread across multi-disk drives) and the Oracle Version is
>8.0.3. We
>> are enforcing Cost Based and Rule based Hints in the Application SQL .
>The
>> DataBase Load Average in Production shoots up very high immediately as
>soon
>> as Statistics are added and the code containing hints are applied whereas
>on
>> Test Machines which have half the RAM, HAlf the no of processors, Single
>disk
>> drive, we don't see such abnormal Load Average shoot-up. I would
>definitely
>> very much appreciate it if somebody could provide some suggestions as to
>what
>> could be causing the Problem in Production and how to go about detecting
>it.
>> Does adding Statistics on a Production Environment like the one mentioned
>> above, create problems??? Reply to sudha_at_altasoft.com THANKS IN
>ADVANCE!!
>>
>> -----------== Posted via Deja News, The Discussion Network ==----------
>> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
>>
Received on Tue Mar 16 1999 - 19:58:55 CST