Re: dbms_stats using no_parallel_index internally

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
  • Date: Wed, 29 Mar 2006 21:05:36 -0700

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


Regards

Wolfgang Breitling
Centrex Consulting Corporation
www.centrexcc.com 


--
//www.freelists.org/webpage/oracle-l


Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 03-2006