Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: io rate - join

Re: io rate - join

From: <xhoster_at_gmail.com>
Date: 12 Jul 2005 22:02:10 GMT
Message-ID: <20050712180210.371$yH@newsreader.com>


"hopehope_123" <hopehope_123_at_yahoo.com> wrote:
> Hi,
>
> Why does the queries which uses join have a lower data rate than simple
> count(*) statement?

Because the join has to do stuff the non-join doesn't, and doing stuff takes time.

>
> select /*+parallel(t,16) parallel(t2,16)*/
> count(*) from tolga2 t , tolga2 t2
> where t.MUSTERI_ID=t2.musteri_id

Are you sure your parallel hints are being implemented?

>
> hash join
>
> extended device statistics
> r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device
> 12.0 0.0 8512.8 0.0 0.0 0.7 0.1 62.5 0 42 c2t16d65
> 0.0 112.4 0.0 900.3 0.0 0.7 0.0 6.1 0 64 c1t1d0

It looks to me like IO may no longer by your bottleneck. Or maybe writing temporary data is now the bottleneck.

...
>
> Why does the join performance differs from the simple count(*) sql?
> Does the internal mechanism which joins these tables decrease the io
> rate?

The computer doesn't do IO just for the hell of it. If Oracle has to sit and think longer about chunks of data it just read, it isn't going to make read requests as often, which means the io rate will be lower.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB
Received on Tue Jul 12 2005 - 17:02:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US