io rate - join

From: hopehope_123 <hopehope_123_at_yahoo.com>
Date: 12 Jul 2005 01:02:05 -0700
Message-ID: <1121155325.672923.180720@g44g2000cwa.googlegroups.com>

Hi,

Why does the queries which uses join have a lower data rate than simple count(*) statement?

The db version is 9.2.0.5 , the disk array is raid 10 , emc , fiber channel via 1hba. (100mb. per second read is possible)

 select /*+parallel(t,16) */
count(*) from tolga2 t

                  extended device statistics

    r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device    99.7 0.0 90733.1 0.0 0.0 4.4 0.1 43.7 1 100 c2t16d65     0.0 1.0 0.0 8.0 0.0 0.0 0.0 3.2 0 0 c1t1d0

                    extended device statistics

    r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device    89.0 0.0 81901.2 0.0 0.0 4.2 0.1 47.5 1 100 c2t16d65     0.0 1.0 0.0 8.0 0.0 0.0 0.0 4.5 0 0 c1t1d0

910kb. per read , 90mb. read per second

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

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

                    extended device statistics

    r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device    10.7 0.0 10072.1 0.0 0.0 0.7 0.1 64.9 0 44 c2t16d65     0.0 108.3 0.0 866.7 0.0 0.7 0.0 6.3 0 67 c1t1d0

941kb. per read, 10mb. read per second

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

nested loop

                  extended device statistics

    r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device

   38.3    0.0 33989.0    0.0  0.0  1.8    0.1   46.3   0  97 c2t16d65
    0.0    1.0    0.0    8.0  0.0  0.0    0.0    3.5   0   0 c1t1d0
    7.0    0.0   51.3    0.0  0.0  0.0    0.0    4.3   0   3 c1t0d0
                    extended device statistics

    r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device

   34.3    0.0 31957.6    0.0  0.0  1.8    0.1   51.5   0  98 c2t16d65
    0.7    2.7    5.3   17.3  0.0  0.0    0.0    5.9   0   2 c1t1d0
    2.0    0.3   14.3    5.3  0.0  0.0    0.0    5.9   0   1 c1t0d0

931kb.per read , 31Mb. read per second

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

I will be appreciated if someone can comment about this.

Kind Regards,
hope Received on Tue Jul 12 2005 - 03:02:05 CDT