Re: dbms_stats using no_parallel_index internally
- From: Riyaj Shamsudeen <rshamsud@xxxxxxxxxxxx>
- To: rjamya@xxxxxxxxx
- Date: Thu, 30 Mar 2006 12:57:11 -0600
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
rjamya wrote:
Now now Deepak, don't go about and start logging bugs about Oracle
generously using RULE hint in various views etc.
C'mon .. give them a break ... some of dem developers haven't yet read
the manuals that say
1. rule is deprecated, don't use it
and (my favorite)
2. don't use long, use CLOB.
he he he he 8:)
Raj
On 3/29/06, Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx> wrote:
I agree that Oracle must have a reason to put that
hint in, but I need to confirm if it is expected
behaviour or a Bug.
--
----------------------------------------------
Got RAC?
--
//www.freelists.org/webpage/oracle-l
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: