Re: dbms_stats using no_parallel_index internally
- From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
- To: breitliw@xxxxxxxxxxxxx
- Date: Wed, 29 Mar 2006 20:20:19 -0800 (PST)
I agree that Oracle must have a reason to put that
hint in, but I need to confirm if it is expected
behaviour or a Bug.
As to why this is important is because, we recently
upgraded our database from 10.1.0.4 to 10.2.0.2, and
saw lot of performance issues just after the upgrade.
We had a few SRs opened for this issue. One of these
suggested us to change the optimizer_features_enable
to 10.1.0.4, followed by gathering stats for all the
tables in the database and then selectively enable the
optimizer_features_enable to 10.2.0.2 after testing
the queries. Now, we have some large tables that are
sub-partitioned, and I noticed this behaviour while
using dbms_stats with granularity 'SUBPARTIITON' where
it used to 5 minutes to run ealier, and now it took as
much as 1.5 Hrs. This table itself has 120
subpartitions and the time it would take to analyze
the entire table would be a lot.
--- Wolfgang Breitling <breitliw@xxxxxxxxxxxxx> wrote:
> a few comments come to mind:
>
> a) why are you so hell-bent on forcing index
> statistics gathering to
> use parallel execution? I wouldn't rule out that
> Oracle has a reason
> for putting the no_parallel_index hint in the
> recursive sql.
>
> b) I wouldn't expect the "_optimizer_ignore_hints"
> setting to remove
> the hints from the sql, just ignore them (as the
> name "hints", pun
> intended). Do you have evidence that the hints were
> not ignored?
>
> c) I would not at all be surprised if Oracle ignores
> the parameter
> for recursive sql.
>
> At 08:35 PM 3/29/2006, Deepak Sharma wrote:
> >I tried, _optimizer_ignore_hints to true, at
> >session-level and ran the dbms_stats again, but
> still
> >see the hints in there. Listed below are 3
> different
> >SQLs that were generated during the first few
> minutes:
> >
> >Case 1 (Good):
> >==
> >INSERT /*+ append */INTO
> SYS.ora_temp_1_ds_58831
> > SELECT /*+ parallel(t,16) parallel_index(t,16)
> >dbms_stats cursor_sharing_exact use_weak_name_resl
> >dynamic_sampling(0) no_monitoring */
> ><other lines>
> >
> >Case 2 (Good):
> >==
> >SELECT SUBSTRB (DUMP (val, 16, 0, 32), 1, 120)
> ep,
> >cnt
> > FROM (SELECT /*+ parallel(t,16)
> >parallel_index(t,16) dbms_stats
> cursor_sharing_exact
> >use_weak_name_resl dynamic_sampling(0)
> no_monitoring
> >*/
> ><other lines>
> >
> >Case 3 (NOT Good - Uses no_parallel_index hint):
> >==
> >SELECT /*+ no_parallel_index(t,"TYPE_P_BIX")
> >dbms_stats cursor_sharing_exact use_weak_name_resl
> >dynamic_sampling(0) no_monitoring no_expand
> >index(t,"TYPE_P_BIX") */
> > COUNT (DISTINCT sys_op_lbid (8752793, 'R',
> >t.ROWID)) AS nrw,
> > COUNT (DISTINCT sys_op_lbid (8752793, 'L',
> >t.ROWID)) AS nlb,
> > COUNT (DISTINCT "TYPE_KEY") AS ndk, NULL AS
> clf
> > FROM "D1"."D1_FACT" t
> > WHERE tbl$or$idx$part$num ("D1"."D1_FACT", 0, 3,
> 0,
> >"ROWID") = :objn
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
