| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: dbms_stats using no_parallel_index internally
Sometimes the plan was totally different, and
sometimes only slight change whereby, the 'SORT GROUP
BY' changed to 'HASH GROUP BY'. We didn't have enough
time to look into each and every query, due to
deadlines etc., and the optimizer setting was kind of
workaround until we gather the stats for 10.2.0.2.
Deepak
> Hi Deepak
>
> Thanks for sharing the info. FWIW, decision to
> add the no_parallel
> hint is based upon the segment size(as per the
> segment header block, NOT
> from the statistics on that segment). If the segment
> is shorter than 100
> blocks as per the segment header block
> (approximately), then no_parallel
> and no_parallel_index hint is added to the queries
> accessing that
> partition/subpartition. This is in 10.1.0.5
>
> I don't have 10.2.0.2 right now, but I bet,
> Oracle tuned these SQLs
> and eliminated unnecessary work in that version ;-)
>
> Out of curiosity, was there any access plan
> difference between
> 10.1.0.4 and 10.2.0.2 for similar queries ?
>
> Thanks
> Riyaj Shamsudeen
>
>
> Deepak Sharma wrote:
> > We found the solution to our (own-created?)
> problem.
> > As I mentioned in this thread earlier, we had
> upgraded
> > to 10.2.0.2 from 10.1.0.4, and found some of the
> > queries behave very poorly. When, as suggested by
> > Oracle, we changed the
> > optimizer_features_enable="10.1.0.4", those
> queries
> > ran as before - quick. The difference was Hrs Vs.
> > seconds. So, next step for us was to gather the
> > statistics afresh on all the tables, where we
> started
> > seeing issues with the dbms_stats job itself, (and
> > hence this thread). About an hour ago it dawned
> onto
> > me to try changing the
> > optimizer_features_enable="10.2.0.2" at
> session-level
> > and run the dbms-stats again. I tested with 2
> > subpartitions that each took 2 Hrs and 5Hrs resp.,
> and
> > after this setting took 9 minutes. So, that's the
> > game-plan now, to use the 10.2.0.2 setting at
> > session-level, gather all statistics, and then
> > selectively change the setting to 10.2.0.2 for
> other
> > areas/schemas etc.
> >
> > Thanks,
> > Deepak
> >
> > --- Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
> wrote:
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 30 2006 - 16:11:14 CST
 |
 |