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: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Thu, 30 Mar 2006 15:16:00 -0600
Message-ID: <442C4A90.1050502@jcpenney.com>

 ('binary' encoding is not supported, stored as-is)


Hi Deepak    

    Thanks for sharing the info. FWIW, decision to add the no_parallel hint is based upon the segment size(as per the segment header block, NOT from the statistics on that segment). If the segment is shorter than 100 blocks as per the segment header block (approximately), then no_parallel and no_parallel_index hint is added to the queries accessing that partition/subpartition. This is in 10.1.0.5

    I don't have 10.2.0.2 right now, but I bet, Oracle tuned these SQLs and eliminated unnecessary work in that version ;-)    

    Out of curiosity, was there any access plan difference between 10.1.0.4 and 10.2.0.2 for similar queries ?

Thanks
Riyaj Shamsudeen

Deepak Sharma wrote:
> 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_at_jcpenney.com> 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
>
>


The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. If the reader of this message is not the intended recipient, you are hereby notified that your access is unauthorized, and any review, dissemination, distribution or copying of this message including any attachments is strictly prohibited. If you are not the intended recipient, please contact the sender and delete the material from any computer.

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 30 2006 - 15:16:00 CST

Original text of this message

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