Re: dbms_stats using no_parallel_index internally

From: Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>
Date: Wed, 29 Mar 2006 19:35:42 -0800 (PST)
Message-ID: <20060330033542.40676.qmail@web52801.mail.yahoo.com>

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

• Wolfgang Breitling <breitliw_at_centrexcc.com> wrote:

> If you are on Oracle 10
> AND
> you are adventurous
> you could set _optimizer_ignore_hints to true.
>
> Although I suppose Oracle has a reason for explxitly
> putting the
> no_parallel_index hint in the sql.
>
> Quoting Deepak Sharma
> <sharmakdeep_oracle_at_yahoo.com>:
>
> > Even with specifying DEGREE=4, the dbms_stats
> > internally generates this Hint to use
> > no_parallel_index when processing the Indexes for
> the
> > table being analyzed. Any ways to force it to use
> > parallel or NOT TO USE no_parallel_index hint? I
> have
> > tried 'alter session enable parallel query', but
> still
> > produced the same.
> >
> > SELECT /*+ no_parallel_index(t,"DT_P_BIX")
> dbms_stats
> > cursor_sharing_exact use_weak_name_resl
> > dynamic_sampling(0) no_monitoring no_expand
> > index(t,"DT_P_BIX") */
>

---

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 - 21:35:42 CST