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: Thu, 30 Mar 2006 13:02:53 -0800 (PST)
Message-ID: <20060330210253.85163.qmail@web52812.mail.yahoo.com>


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

> 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
Received on Thu Mar 30 2006 - 15:02:53 CST

Original text of this message

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