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

You may just try to shut the hash join down by

HASH_JOIN_ENABLED = OFF   in your initsid.ora. The CBO wil use nested loops instead of hash joins.

Sylvain Leclerc, DBA
sleclerc_at_magrit.com

> -----Original Message-----
> From: boulke_at_globalnet.co.uk (keith boulton)
> [SMTP:boulke_at_globalnet.co.uk]
> Posted At: Wednesday, March 17, 1999 5:14 AM
> Posted To: server
> Conversation: USING COST BASED OPTIMISATION ON A
> MULTI-PROCESSOR,MULTI-DISK DRIVE SERVER
> Subject: Re: USING COST BASED OPTIMISATION ON A
> MULTI-PROCESSOR,MULTI-DISK DRIVE SERVER
>
> 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 - 16:03:57 CST