Ok I'll admit it - I'm not a Oracle DBA. I'm actually working within a service optimisation team investigating performance issues on an Oracle platform. I need help with ODM and the Async device.

We have a 9i installation for a Real-time billing solution on a Superdome with 64 CPU's on a HPUX OS. This is a very busy service that handles something like 9K requests per second.

Way back in May last year we implemented ODM but found that we had little or no improvement in I/o performance. This is totally not what we thought would happen. We expected to get a 20% improvement in performance.

Once we started analysing the root cause we found that our SAN was configured incorrectly (IE all the devices were presented through just the one Port so they were running hot). We fixed this and noticed a 98% improvement in Writes but only a 5% improvement in Reads. Not sure why this is so, but thats for a SAN forum.

The question I have is that if you set the max_async_io too low you will have (Quote form man page) "Performance degradation". Unfortunately our Engineering group set this to just 50, when there are over 400 Oracle Processes. In the end we identified that just 22 Oracle processes were able to attach to the Asynchronous device. This would explain why we did not see the performance enhancement we expected.

Problem is I can’t convince the Engineering group to change it. Oracle and HP-UX Documentation suggest a minimum of 1000 for this tuneable but this is not convincing the damn engineers.

Does anyone out there have any experience with this tuneable? Have you seen this situation before where changing the parameter improved ODM performance? Please let me know.

Steve.B.

Hi Steve,

>> This is totally not what we thought would happen. We expected to get a 20% improvement in performance.

Ah, that's a clue. I've seen this many times, where people try to speed up I/O with no net effect, because the system bottleneck is not disk I/O. You may be network bound, CPU constrained, etc.

The way to know is to create a STATSPACK report and examine the top-5 timed events.

>> Does anyone out there have any experience with this tuneable?

I've found little on the web, but try here:

http://www-03.ibm.com/systems/storage/solu...base_layout.pdf

>> Problem is I can’t convince the Engineering group to change it.

Why not? It's just a max threshold, and unlike to cause problems with over-setting.

The best way to to get an opinion leader (an Oracle ACE or Oracle Certified Master) to make the recommendation.

Not to give a sales pitch, but if you want to hire one, just call 800-766-1884.

Thanks for the reply,

Firstly - Are we CPU bound? Absolutely, but I feel this is caused by the synchronous processes "Spinning" and churning up CPU. Of the 400 odd ORACLE processes just 22 get async ports. These processes use less that 5% of one CPU in any given window, while the others churn up to 95%.

Second, the reason I can’t convince the engineering group is that they implemented ODM incorrectly in the first place and refuse to accept that they made a mistake. The impact of making this change is huge due to the fact that this system takes 8 yours to restart (restart is required for this tunable). That’s 8 hours of no billing.

I think your suggestion of getting an ORACLE Master to put the recommendation forward is a good one. Unfortunately while we have many DBA’s here none of them have Master status and none have any clout with our customer, so your suggestion that we call you sound great and I may call you. Issue is that I’m in Australia. From your number it sounds like your in the States. Can you confirm this?

We are in an evironment where the customer is risk avert, due to the revenue impact of changes. Your comment of "and unlike to cause problems with over-setting" does not sway the customer. Do you know what the risks really are, if any? more to the point, what are the risks of setting it too low?

Not to take business away from Don, but since you're based in Australia, you might want to reach out to Steve Adams, who's also based in Australia, and is one of the top two or three Oracle performance experts in the world.

See http://www.ixora.com.au/ for his contact info.

Don,

Apologies to you if you think that's out of line. (It is your forum, after all.)

-Mark

Mark,

"Apologies to you if you think that's out of line. (It is your forum, after all.)"

Heck, why should he?

I did not notice that he was Australian.

However, Steve is a manager now, and I'm not sure if he still does Oracle consulting.

Hi Steve,

>> I think your suggestion of getting an ORACLE Master to put the recommendation forward is a good one.

I haver several Oracle ACE's and Oracle Certified Masters people who could connect remotely and give you advice, if that's something you want to pursue.

http://www.dba-oracle.com/qualifications.htm

Sometimes, a guru can convince people . . . . .

You might also try Steve Adams, tell him I sent you:

steve.adams@ixora.com.au

HAL 9000,

Are we talking about the same Steve Adams?? A manager? Where did you hear that?

-Mark

Hi Mark,

I heard the same thing, directly from Steve.

He told me that he has moved up (promoted) to mgt.

I may be mistaken, but that's how I remember the conversation . . . .

Huh...wow...I need to drop him a note, I haven't talked w/ Steve in quite some time.....

-Mark

Hi Mark,

Just curious, what's youyr take on the new 11g holistic tuning approach?

http://www.eweek.com/c/a/Database/Beware-O...gs-Ease-of-Use/

Honestly, Don, I'm still looking at converting a whole slew of 9i databases to 10g. 11g is still down the road for us, so, I haven't looked too closely at it.

However, my "grumpy old man" perspective is to be automatically suspicious of too much being automated...:-)

-Mark

Hi Mark,

>> However, my "grumpy old man" perspective is to be automatically suspicious of too much being automated...:-)

Me too! But I was asking about the idea of capturing and using real-world workloads to verify tuning changes.

Nothing beats a real workload for validating a change, IMHO, and we don't have to use expensive 3rd party tools (Benchmark Factory) anymore . . . . .

FYI, an update on the Steve Adams question:

I dropped Steve a note saying that I heard a rumor that he had made a move into management, and this was the reply:
"It is just a rumor.
All I manage is my family."

So, he's definitely not made a move to management.

-Mark

OK,

It took me 402 days to get Engineering to change max_async_ports from the default of 50 to 1024. Before we made this change we had just 22 Oracle processes using the ASYNC driver. After we had 722. The processing time for a bill run reduced by 38% and our I/O's per second increased from 3500 to 6800. I knew I was right but just found it hard to convince anyone who could actually change it.

I did contact Steve Via Email BTW but I did not get a response

anyway, thanks for the interest guys.