Re: HASH UNIQUE "producing" millions of cr blocks out of nowhere

Hi Stefan  

   Looks like my earlier email was bounced..    One reason I can think of is that there is a function call in that step.    It also possible to be a bug. See bug 4926618 "HASH UNIQUE TAKES 100 TIMES MORE TIME TO SORT THAN SORT UNIQUE "    It doesn't look like it is fixed though. Test this with 9i version of optimizer to differentiate whether this is execution issue or code issue.

         select /*+ optimizer_features_enable ('9.0.1') */ count(*) from es.ooc_cat_hardware

Cheers
Riyaj
Stefan Knecht wrote:
> Hello list
>
> I'm sort of stuck looking at this:
>
> PARSING IN CURSOR #11 len=40 dep=0 uid=0 oct=3 lid=0
> tim=28320555905074 hv=1189208669 ad='106ff490'
> select count(*) from es.ooc_cat_hardware
> END OF STMT
> WAIT #0: nam='log file sync' ela= 3695 buffer#=3319 p2=0 p3=0
> obj#=8707 tim=28320781782931
> STAT #11 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=2224255
> pr=0 pw=0 time=180210957 us)'
> STAT #11 id=2 cnt=4480 pid=1 pos=1 obj=436056 op='VIEW
> OOC_CAT_HARDWARE (cr=2224255 pr=0 pw=0 time=180212109 us)'
> STAT #11 id=3 cnt=4480 pid=2 pos=1 obj=0 op='HASH UNIQUE (cr=2224255
> pr=0 pw=0 time=180207620 us)'
> STAT #11 id=4 cnt=8945 pid=3 pos=1 obj=0 op='HASH JOIN (cr=92 pr=0
> pw=0 time=160024 us)'
> STAT #11 id=5 cnt=92 pid=4 pos=1 obj=584822 op='INDEX FULL SCAN
> CAT_HARDWARE_IDX$$_41590014 (cr=1 pr=0 pw=0 time=363 us)'
> STAT #11 id=6 cnt=8994 pid=4 pos=2 obj=0 op='HASH JOIN (cr=91 pr=0
> pw=0 time=95009 us)'
> STAT #11 id=7 cnt=41 pid=6 pos=1 obj=584831 op='TABLE ACCESS FULL
> CAT_RATEPLAN (cr=15 pr=0 pw=0 time=369 us)'
> STAT #11 id=8 cnt=9097 pid=6 pos=2 obj=584828 op='TABLE ACCESS FULL
> CAT_PRICE (cr=76 pr=0 pw=0 time=18284 us)'
>
>
> How can a HASH UNIQUE of approx 150 blocks in total, produce over 2
> million consistent reads ? Any idea on how to further diagnose this ?
>
> Stefan
> OPITZ CONSULTING Schweiz GmbH

--
http://www.freelists.org/webpage/oracle-l