Hi Group ,

I want to ask your opinion about system resources usage .

My system is a datawarehouse. I have heavy usage of parallel query ,
full table scans. Tables with 60,000,000 rows exist , two or three
tables at this size are joined usually . My current system is redhat
linux itanium , oracle rac 9.2 with 2 nodes. Each node has 4 ia64 cpus
, 8gb. ram , 1 hba , and i use emc , raid10. The db size is 2TB. I use
ocfs files system . My db_file_multiblock_read_count is set to 64 .
With 16kb. block size, this means oracle requests 1MB. io from the os.
Due to a known bug with this version of redhat linux, although oracle
requests 1MB. io for full scans ( direct_path_Read , wait event p3
value=1MB. ) , the os splits this into multiple 32KB. requests. I can
also see this in iostat . Direct_io is enabled.No async io is
available.

Here is the second server : This is sun solaris , emc , 1 hba , 2cpu.
4gb. ram. This server comparing to the redhat , is less powerful in
terms of cpu and memory. But the io bug i mentioned above is not a
concern. Both direct_io and async are available.File system is ufs.

My question :

There is a table:
Table_a is 3gb. in size , has 20,000,000 rows both in redhat and sun
solaris.

1.
select /*+parallel(a,8)*/count(*) from table_a a

This is a simple sql,. it reads all table data from the underlying disk
system.

in redhat ,

this sql takes 2.min. During the execution , oracle shows 1MB. io
requests, direct_path_read, os iostat command shows 20MB. read per
second , each read is 32KB.
Disk utilization is high (>95)

in sun solaris:

it takes 1min. oracle shows again 1MB. io , but this time iostat shows
90MB. per read. each read is 1MB.

2. A real sql :

select /*+parallel(a,8)*/cust_id,count(*) from table_a a group by
a.cust_id

This is different than count. This time redhat is much better than sun
..

in redhat :

It takes 4 min. cpu utilization is %30


in sun :

25 min. cpus are 100% utilized.

When i monitor the group by execution both in v$session_wait and
v$sql_workarea_active , i see that first data is read by using the
direct_path read, than group by calculation is done.


The question is , when choosing a datawarehouse system , i consider ,
high io rate so HBA, emc , emc cache , fibre channel is important . But
a group by sql or hash join sql seems to use more cpu resources than io
.. Which one is better , more hbas , channels or more cpu resources in
order to run sqls faster.If io is the concern , sun server makes io
better than redhat so count sql takes less time . But group by runs
faster in redhat .

I am about making the decision of migrating redhat box to a sun box.
The new sun box may have 6CPUs ( dual core so 12 cpus) 2hba , but i
wonder whether the 6 cpus in new box can provide me a better
performance .



Thanks fpr your comments.

tolga