Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: dbms_stats using no_parallel_index internally

Re: dbms_stats using no_parallel_index internally

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Wed, 29 Mar 2006 20:20:19 -0800 (PST)
Message-ID: <20060330042019.65198.qmail@web52806.mail.yahoo.com>


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
>



Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Mar 29 2006 - 22:20:19 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US