Re: dbms_stats using no_parallel_index internally

We found the solution to our (own-created?) problem. 
As I mentioned in this thread earlier, we had upgraded
to 10.2.0.2 from 10.1.0.4, and found some of the
queries behave very poorly. When, as suggested by
Oracle, we changed the
optimizer_features_enable="10.1.0.4", those queries
ran as before - quick. The difference was Hrs Vs.
seconds. So, next step for us was to gather the
statistics afresh on all the tables, where we started
seeing issues with the dbms_stats job itself, (and
hence this thread). About an hour ago it dawned onto
me to try changing the
optimizer_features_enable="10.2.0.2" at session-level
and run the dbms-stats again.  I tested with 2
subpartitions that each took 2 Hrs and 5Hrs resp., and
after this setting took 9 minutes.  So, that's the
game-plan now, to use the 10.2.0.2 setting at
session-level, gather all statistics, and then
selectively change the setting to 10.2.0.2 for other
areas/schemas etc.

Thanks,
Deepak

--- Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx> wrote:

> Deepak
> 
>     "Looks like" the decision to add this
> no_parallel hint depends upon 
> the attribute of that partition/subpartition. I
> created a test case to 
> test this theory. Three partitions and one of the
> partition is bigger 
> compared to other two. I see no_parallel hint for
> the smaller partition, 
> but no such hint exists for the bigger partition.
> Almost trying to 
> implement the guideline: If the (sub)partition is
> smaller in size, then 
> don't use parallelism.
> 
>     To find the attribute used by oracle, I tried
> various combination of 
> table length, table partition length, index length,
> index partition 
> length, row count, partition row count etc,  by
> modifying the stats 
> table and importing that stats table. But I am
> unable to pinpoint which 
> one of the attribute is used by Oracle to make this
> decision. It makes 
> sense to use the partition length to make a decision
> not to parallelize, 
> but unfortunately, that doesn't seems to be the case
> or my test case has 
> flaws. Either way, I am planning to test this more
> thoroughly.
>    
> small partition:
> ==========
> select  /*+ cursor_sharing_exact use_weak_name_resl
> dynamic_sampling(0) 
> no_monit
> oring no_parallel(t) no_parallel_index(t) */  
> count(*),count("N1"),count(distinc
> t 
>
"N1"),sum(vsize("N1")),substrb(dump(min("N1"),16,0,32),1,120),substrb(dump(max
> ("N1"),16,0,32),1,120),count("C1"),count(distinct 
> "C1"),sum(vsize("C1")),substrb
>
(dump(min(substrb("C1",1,32)),16,0,32),1,120),substrb(dump(max(substrb("C1",1,32
> )),16,0,32),1,120),count("N3"),count(distinct 
> "N3"),sum(vsize("N3")),substrb(dum
>
p(min("N3"),16,0,32),1,120),substrb(dump(max("N3"),16,0,32),1,120)
> from 
> "RSHAMSU
> D"."TEST_PART" partition ("P1") sample ( 
> 1.0000000000) t
> 
> bigger partition:
> ===========
> select  /*+ cursor_sharing_exact use_weak_name_resl
> dynamic_sampling(0) 
> no_monit
> oring */  count(*),count("N1"),count(distinct 
> "N1"),sum(vsize("N1")),substrb(dum
>
p(min("N1"),16,0,32),1,120),substrb(dump(max("N1"),16,0,32),1,120),count("C1"),c
> ount(distinct 
>
"C1"),sum(vsize("C1")),substrb(dump(min(substrb("C1",1,32)),16,0,3
>
2),1,120),substrb(dump(max(substrb("C1",1,32)),16,0,32),1,120),count("N3"),count
> (distinct 
>
"N3"),sum(vsize("N3")),substrb(dump(min("N3"),16,0,32),1,120),substrb(
> dump(max("N3"),16,0,32),1,120) from
> "RSHAMSUD"."TEST_PART" partition 
> ("PM") samp
> le (  1.0000000000) t
> 
> So, you might want to see whether this happens only
> for smaller 
> subpartitions or is it for every subpartition. I am
> testing in 10.1.0.4. 
> It is quite possible this is changed in 10.2.
> 
> BTW, I am surprised to find some inefficiencies in
> the dbms_stats trace 
> file (this is probably old news for many folks!):
> Oracle is trying to 
> run few SQL couple of times, for example SQL used a
> sample clause of 1%, 
> and then went ahead and executed same SQL with a
> sample clause of 
> 10%(for larger partition) or 100%. Bottomline is
> that work is done 
> twice. Almost looks like some kind of sanity check
> is done in dbms_stats 
> code to see whether the statistics are dependable or
> not, and then 
> executed again with higher precision.
> 
> select  /*+ cursor_sharing_exact use_weak_name_resl
> dynamic_sampling(0) 
> no_monit
> oring no_parallel(t) no_parallel_index(t) */  
> count(*),count("N1"),count(distinc
> t 
>
"N1"),sum(vsize("N1")),substrb(dump(min("N1"),16,0,32),1,120),substrb(dump(max
> ("N1"),16,0,32),1,120),count("C1"),count(distinct 
> "C1"),sum(vsize("C1")),substrb
>
(dump(min(substrb("C1",1,32)),16,0,32),1,120),substrb(dump(max(substrb("C1",1,32
> )),16,0,32),1,120),count("N3"),count(distinct 
> "N3"),sum(vsize("N3")),substrb(dum
>
p(min("N3"),16,0,32),1,120),substrb(dump(max("N3"),16,0,32),1,120)
> from 
> "RSHAMSU
> D"."TEST_PART" partition ("P1") sample ( 
> 1.0000000000) t
> 
> followed by
> 
> select  /*+ cursor_sharing_exact use_weak_name_resl
> dynamic_sampling(0) 
> no_monit
> oring no_parallel(t) no_parallel_index(t) */  
> count(*),count("N1"),count(distinc
> t 
>
"N1"),sum(vsize("N1")),substrb(dump(min("N1"),16,0,32),1,120),substrb(dump(max
> ("N1"),16,0,32),1,120),count("C1"),count(distinct 
> "C1"),sum(vsize("C1")),substrb
>
(dump(min(substrb("C1",1,32)),16,0,32),1,120),substrb(dump(max(substrb("C1",1,32
> )),16,0,32),1,120),count("N3"),count(distinct 
> "N3"),sum(vsize("N3")),substrb(dum
>
p(min("N3"),16,0,32),1,120),substrb(dump(max("N3"),16,0,32),1,120)
> from 
> "RSHAMSU
> D"."TEST_PART" partition ("P1") t
> 


__________________________________________________
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

Other related posts: