Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: USING COST BASED OPTIMISATION ON A MULTI-PROCESSOR,MULTI-DISK DRIVE SERVER

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

From: keith boulton <boulke_at_globalnet.co.uk>
Date: Wed, 17 Mar 1999 10:14:07 GMT
Message-ID: <36ef7d08.1495630@195.147.246.90>


At the risk of appearing obsessed about it - my experience is that the cost based optimiser far too often chooses full tables scans and hash joins. The solution to this is to set optimizer_mode=first_rows in init.ora. This can be overridden with hints on a case by case basis if required.

I've encountered serious performance problems (as you would expect) when the tables where analyzed before some of them were populated with data.

On Mon, 15 Mar 1999 22:14:08 GMT, sudha_at_altasoft.com wrote:

>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 Wed Mar 17 1999 - 04:14:07 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US