| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: dbms_stats using no_parallel_index internally
('binary' encoding is not supported, stored as-is)
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:
("N1"),16,0,32),1,120),count("C1"),count(distinct
bigger partition:
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
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
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_at_yahoo.com> 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? > -- > http://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.
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 30 2006 - 12:57:11 CST
 |
 |