Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: io rate - join
"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 30GBReceived on Tue Jul 12 2005 - 17:02:10 CDT