RE: Win2000/8.1.7.3.0/SQL

I think you are on to something Jared. We had this happen at a production site last year. For some reason, the site began reporting performance problems after upgrading from 7.3.4 to 8.1.6. I finally tracked it down to the fact that a majority of our tables had 'degree' set to 'DEFAULT', which forces the use of the CBO. We fixed this by doing 'alter table <table> noparallel' for all the tables which set degree to 1 and solved the problem.

Dan.

> -----Original Message-----
> From: Jared Still [ mailto:jkstill_at_cybcon.com
<mailto:jkstill_at_cybcon.com> ]
> Sent: Monday, August 19, 2002 7:38 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Win2000/8.1.7.3.0/SQL
>
>
>
> Mike,
>
> Is the DEGREEE on the table > 1?
>
> You can see this in DBA_TABLES.
>
> If the degree of parallelism is > 1 then CBO
> will be used regardless of OPTIMIZER_MODE or
> OPTIMIZER_GOAL.
>
> Using a RULE hint will override the optimizer settings.
>
> I'm guessing that degree is > 1 and you have old statistics.
>
> Jared
>
> On Monday 19 August 2002 17:58, Vergara, Michael (TEM) wrote:
> > Ok...this is strange. The optimizer_mode is set to RULE, but if
> > I put a RULE hint into the SQL it runs OK. I tried increasing
> > the sort_area_size, but no joy. The segment with the largest
> > number of extents is the silly TEMP segment with 985. After that
> > is IDL_UB1$ with 739, and it tapers off quickly after that.
> > On the other hand, this query runs fine on our SAP system, and
> > it has thousands of tables and lots of extents.
> >
> > Well, the RULE hint in a RULE system seems to have worked.
> > Weird. I never woulda tried that.
> >
> > Thanks,
> > Mike
> >
> > -----Original Message-----
> > [ mailto:Bruce.Reardon_at_comalco.riotinto.com.au
<mailto:Bruce.Reardon_at_comalco.riotinto.com.au> ]
> > Sent: Monday, August 19, 2002 4:28 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > or is that 1 db set to first_rows?
> > If so try the query with a rule hint?
> >
> > Bruce Reardon
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
<http://www.orafaq.com>
> --
> Author: Jared Still
> INET: jkstill_at_cybcon.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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Straub, Dan
  INET: Dan.Straub_at_McKesson.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).