Oracle DBA – A lifelong learning experience

  • Meta

  • Categories

  • Blog Stats

    • 1,654,056 hits
  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 377 other followers

  • Advertisements

Much faster performance with ASM – a real world example

Posted by John Hallas on October 9, 2009

We have a  Peoplesoft database that  manages HR for ~80,000 staff.  Oracle 10.2.03.  Peoplesoft Tools 8.49 PS HR 9 HPUX 11.31 Itanium

There has been long running problems caused by I/O bottlenecks writing down to Hitachi XP SAN disks. We were using filesystem storage and disk_asynch_io was set to false

The root cause of our problem was that the Time and Labour batches contend when they need to acquire exclusive access to write dirty blocks to data files during truncate operations. 

David Kurtz has written about a specific Peoplesoft feature around truncate

 We migrated to using ASM disk and achieved significant benefits.

 The testing stages were

1)       Prepare a 2 hour Time and labour batch run which would generate significant throughput

2)       Capture a baseline at

3)       Upgrade to and retest

4)       Upgrade to and migrate to ASM ( and retest

5)       Upgrade to and migrate to ASM ( and retest


Test 3 did not show any difference at all. We were still blocked on disk, which was expected. We probably would have remained at but gone to ASM but there are problems with seeing ASM disk when using (Metalink Note 434500, ORA-15059) so we had the option of applying a fix to get ASM working with or upgrading to We decided that the benefits of getting onto a terminal release and suggestions from Oracle that small changes in the truncate process had been made between and out-weighed the overhead of any application testing that we needed to do.

Test 4 gave us big performance benefits. Test 5 was not as clear cut but we knew some code had to be optimised and we did not have the luxury of time to address the issues. Remaining on 10g minimised the application testing, certainly compared to the testing require to upgrade to 11g.

The benefits can be seen quite clearly in the graph below


This shows a 2 hour period where a constant throughput of T&L jobs were being processed. In parallel a constant online load was being simulated

The black line shows throughput and elapsed time. The run consists of a number of jobs which take 2 hours to complete and batch time is around the 450 – 600 second mark

The same processing is then run with database and ASM. disk_aysnch_io is set to FALSE. The jobs complete 40 minutes faster and elapsed time is around the 300-400 second mark but showing spikes and inconsistent performance

Finally the orange line shows database and ASM. disk_aysnch_io =TRUE. The processing time has now been reduced to just over an hour and the elapsed time is significantly better at an average of 250 seconds and with a very consistent profile.

As with all things in life we have now moved onto the next bottleneck which is the wait flashback buf free caused because we have enabled flashback logging and as the redo log buffer fills up we wait for flashback to be written out. However this is a miniscule issue compared to the original problems.  We have taken flashback off some tablespaces primarily used for PS temporary tables (aka working storage tables) although that does give us an issue when when rebuilding the primary database after a DataGuard failover using flashback logs

I hope this real world example is of use


One Response to “Much faster performance with ASM – a real world example”

  1. Allan Webster said

    Also bear in mind:-

    To use asyncio, the dba O/S group must have the MLOCK privilege granted. Without it, disk_asynch_io is ignored.

    If MLOCK is not granted, then in, you will also get tons of .trc files with messages like:-

    *** 2009-09-09 12:15:23.498
    Ioctl ASYNC_CONFIG error, errno = 1
    WARNING:Could not set the asynch I/O limit to 64 for SQL direct I/O. It is set to 0

    *** 2009-09-09 12:15:25.100
    *** SESSION ID:(1950.3) 2009-09-09 12:15:25.101
    *** SERVICE NAME:(SYS$BACKGROUND) 2009-09-09 12:15:25.101
    *** MODULE NAME:(MMON_SLAVE) 2009-09-09 12:15:25.101
    *** ACTION NAME:(Advisor Task Maintenance) 2009-09-09 12:15:25.101

    WARNING:Could not set the asynch I/O limit to 64 for SQL direct I/O. It is set to 0
    WARNING:Could not set the asynch I/O limit to 64 for SQL direct I/O. It is set to 0

    You don’t get these in

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: