resource utilization / hardware selection

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 Received on Fri Feb 17 2006 - 01:14:49 CST