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

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 - 11:50:39 CST