RE: linux Async io

From: Hsieh, Joan <Joan.Hsieh_at_tufts.edu>
Date: Mon, 25 Mar 2013 13:40:17 +0000
Message-ID: <416FB269D9362D4AA40BE7124CA3EF9F0AAB04E8_at_SSVMEXDAG01MB02.tufts.ad.tufts.edu>

The AIX server is 16, I didn't set it on the new Linux server. Oracle automatically sets it to 128.

Thanks,

Joan

-----Original Message-----
From: CRISLER, JON A [mailto:JC1706_at_att.com] Sent: Friday, March 22, 2013 4:15 PM
To: Hsieh, Joan; Mark W. Farnham; 'Frits Hoogland'; david.barbour1_at_gmail.com Cc: 'oracle_l'
Subject: RE: linux Async io

What is the value of db_multiblock_read_count? I found that values of 64 or higher (ideally 128) returned best sequential performance.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hsieh, Joan Sent: Friday, March 22, 2013 4:01 PM
To: Mark W. Farnham; 'Frits Hoogland'; david.barbour1_at_gmail.com Cc: 'oracle_l'
Subject: RE: linux Async io

Thanks Mark,

I'm curious the sync setting, but we are actually experiencing the performance issue, 60% of wait time is user io, db_ sequential_read is on top of it. This is a show stopper for us, and I am trying to find out the cause.

Joan

-----Original Message-----
From: Mark W. Farnham [mailto:mwf_at_rsiz.com] Sent: Friday, March 22, 2013 2:52 PM
To: Hsieh, Joan; 'Frits Hoogland'; david.barbour1_at_gmail.com Cc: 'oracle_l'
Subject: RE: linux Async io

Frits' suggestion was to sql_trace at level 8, presumably to see whether you're getting actual async i/o and the details of i/o requests, as opposed to just whether your execution plan changed. While changes to your plans may indeed be of critical interest, the thread title is about async io.

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hsieh, Joan
Sent: Friday, March 22, 2013 2:39 PM
To: Frits Hoogland; david.barbour1_at_gmail.com Cc: oracle_l
Subject: RE: linux Async io

Thanks Frits,
I have compared the execution plans, they both are same. I created the new database on Linux and using datapump import the data from the source. The parameter are not much different, the memory_target set to 1000m on Linux, we use sga_target on Aix which is 700mb. The db_file_multiblock_read_count on AIX, it is 128 on Linux ( auto set).

Joan

From: Frits Hoogland [mailto:frits.hoogland_at_gmail.com] Sent: Friday, March 22, 2013 2:07 PM
To: david.barbour1_at_gmail.com
Cc: Hsieh, Joan; oracle_l
Subject: Re: linux Async io

A sane beginning would be to start with source platform and version and hardware layout en destination specifications. And parameters of both instances.

Then look at both (source and destionation) execution plans.

Then run the critical sql's on both platforms with SQL trace at level 8, and compare them.

Frits Hoogland

http://fritshoogland.wordpress.com<http://fritshoogland.wordpress.com/> frits.hoogland_at_gmail.com<mailto:frits.hoogland_at_gmail.com> +31 6 53569942<tel:+31%206%2053569942>

Op 22 mrt. 2013 om 18:55 heeft David Barbour <david.barbour1_at_gmail.com<mailto:david.barbour1_at_gmail.com>> het volgende geschreven:
Joan,

It appears it is - but may not be very efficient at this point. What version of Oracle and Linux are you using? What type of filesystem/disk/etc. (Not just NetApps - ASM, OCFS, etc.)?

On Fri, Mar 22, 2013 at 11:47 AM, Hsieh, Joan <Joan.Hsieh_at_tufts.edu<mailto:Joan.Hsieh_at_tufts.edu>> wrote:

Hi,
We are migrating DW database from AIX to linux, the online transaction is ok compared to AIX. But the batch job is 35% slower than AIX. Of course, the architecture are different, the storage is Netapps on Linux. From the awa report. 60% time is user IO waiting. I'm not sure how to read the following information, is it async io enabled?

Thank,

Joan

dwdb-prod-01:c0ra1e)SDWPRD:/oracle/product/11.2.0.3/bin<http://11.2.0.3/bin>
> cat /proc/slabinfo | grep kio

kioctx                56     80    384   10    1 : tunables   54   27    8 :
slabdata      8      8      0
kiocb                  0      0    256   15    1 : tunables  120   60    8 :
slabdata      0      0      0

dwdb-prod-01:c0ra1e)SDWPRD:/oracle/product/11.2.0.3/<http://11.2.0.3/>

--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l


--
http://www.freelists.org/webpage/oracle-l

Received on Mon Mar 25 2013 - 14:40:17 CET