Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: dbms_stats using no_parallel_index internally
a few comments come to mind:
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
-- http://www.freelists.org/webpage/oracle-lReceived on Wed Mar 29 2006 - 22:05:36 CST
![]() |
![]() |