Re: dbms_stats using no_parallel_index internally

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@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 

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.

Other related posts: