| 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
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.
> 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
>
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 29 2006 - 22:20:19 CST
 |
 |