Re: resource utilization / hardware selection

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Fri, 17 Feb 2006 17:28:40 +0100
Message-ID: <totbv1pgtsmumlns9601o1b6kks2tg733u@4ax.com>

Comments embedded

On 16 Feb 2006 23:14:49 -0800, "hopehope_123" <hopehope_123_at_yahoo.com> wrote:

>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.

There is no such thing as async_io possible on an ufs filesysyem. You can easily verify this by trussing dbwr.

>
>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)

Not high, but disastrous.
Parallel query is only useful when you are striped your data across multiple disks. Try removing the parallel hint, and it will be faster.

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

Maximum read ahead for ufs is 1 Mb. So nothing unusual.

>
>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

Apparently you expect to resolve your tuning problems by throwing hardware at the problem.
Please answer the following questions
- as this is the second time you post this story, and you have been asked to post the execution plans, why don't you do so? This is an Oracle newsgroup, not a car dealer.

- Are your statistics current?
- Did you gather system statistics?
- Or did you set the optimizer parameters appropiately?
- Are you aware you are forking off 16 query slaves for this query on

a box with 4 and 2 cpus for your second query?

- Are you aware those query slaves need to be coordinated?
- Did you stripe the data across 8 spindles?
- Aren't you just *creating* contention *by design*?
- So what do you expect of the outcome?
- Do you realize replacing the hardware for an untuned database is

probably not going to help you *at all*?

--
Sybrand Bakker, Senior Oracle DBA

Received on Fri Feb 17 2006 - 10:28:40 CST