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

From: Riyaj Shamsudeen <>
Date: Thu, 13 Nov 2008 08:56:57 -0600
Message-ID: <>

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

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

Received on Thu Nov 13 2008 - 08:56:57 CST

Original text of this message