Top 60 Oracle Blogs

Recent comments

September 2011

OOW 2011 – On Our Way

Early mornings…really hate them, but can avoid it this time due to our flight going out towards San Francisco is scheduled at 9.50PM. Oracle Open World is near and a few days away from getting started. Alex, Lucas and I meet up this morning in Schiphol, Hollands main airport for our flight to the ACE …

Continue reading »

Having Fun with ORA-00600 (qkebCreateConstantOpn1) – What is the Cause of this Error?

September 28, 2011 A friend brought an interesting Oracle Database problem to my attention. He is in the process of upgrading to a new Oracle Database server (and Oracle Database version) and a new version of an ERP package. He has experienced an ORA-00600 error with both patch 6 and patch 9 for [...]

Thirteen signs of DBA fudging

If you are a director, manager or project manager who works with DBAs, you probably have had the nagging suspicion at one time or another that a DBA’s assertions regarding his or her practices lack an empirical or scientific basis, or are simply deflections intended to pass the buck.

Manager: Mr. DBA, the application is really slow. Do you have any idea what’s wrong?

DBA: Oracle is very complex. It could be any of 100 different possible causes. I will begin checking each. Anyhow, what makes you think it is the database?

NoCOUG: Performance Training with Gaja Vaidyanatha Nov 15,16

There are only three days left (Sept 30)  to get the early bird rate for the Oracle Performance Management Seminar by Gaja Krishna Vaidyanatha covering the following topics.

Oracle Open World 2011 – My presentation on RAC topic

I will be talking about “Administering Parallel Execution in RAC” with demos on Sunday morning 9AM-10AM (session id 28060). This is part of IOUG RAC SIG presentation series. You would enjoy the content and demos I have prepared.

I know, it is too early, but hoping to see you there!
BTW, if you have attended my RAC Advanced Troubleshooting class series, please don’t hesitate to introduce yourself when we meet.

Session details:
Session ID: 28060
Session Title: IOUG: Administering Parallel Execution in Oracle RAC
Venue / Room: Moscone West- 2005
Date and Time: 10/2/11, 9:00 – 10:00

Update: I just completed this session in IOUG. Thank you for coming, if you were in the room.
You can download the pdf file from
PX execution in RAC

I/O Benchmarking tools

This blog post will  be a place to park ideas and experiences with I/O benchmark tools and will be updated  on an ongoing basis.

Please feel free to share your own experiences with these tools or others in the comments!

There are a number of tools out there to do I/O benchmark testing such as

  • fio
  • IOZone
  • bonnie++
  • FileBench
  • Tiobench
  • orion

My choice for best of breed is fio
#808080;">(thanks to Eric Grancher for suggesting fio).


Why Is My Index Not Being Used Quiz (Brain Damage)

This one is a little different as it comes in the form of a demo (and about 1 minute to read) so you have to work a little   I create table, index and sequence:     I then create a little procedure that simply adds 100,000 rows to the table:     I then [...]

Orion I/O calibration tool bug

All of my I/O testing and benchmarking has been geared toward Oracle, so the natural choice would for benchmarking I/O would be orion, the I/O benchmarking tool from Oracle. Unfortunately Orion has had some problems that have made it too undependable for me to trust. First there have been annoying problems getting it running especially on NFS. The problems are easy to resolve once the solutions are known. See previous blog on Orion errors.

Orion has a much more serious issue, at least in some cases. In some cases Orion re-reads the same blocks covering a much smaller data set size than requested.The following strange behavior is with orion on X86 Solaris. The orion binary was from an 11g distribution. The root of the strange behavior is that orion seems to revisit the same blocks over and over when doing it’s random read testing.

A dtrace script was used to trace which blocks orion was reading. The blocks in the test were on /domain.

    #!/usr/sbin/dtrace -s
    #pragma D option quiet
    / strstr((args[0])->v_path, "/domain") != NULL /
    {  printf("%lld\n", args[1]->_uio_offset._f); }
      1. Created a 96GB file and put it’s path in mytest.lun
      2. Modified io.d to filter for /domain .
      3. Ensure no non-orion I/O is going to the filesystem.
      4. Start running io.d > blocks-read.txt
      5. Kicked off orion with:
        export LD_LIBRARY_PATH=.
        ./orion -testname mytest -run advanced -matrix row -num_disks 5 -cache_size 51200 \
                   -duration 60 -simulate raid0 -write 0 -num_large 0

-run advanced : users can specify customizations
-matrix row : only small random I/O
-num_disks 5 : actual number of physical disks in test. Used to generate a range of loads
-cache_size 51200 : defines a warmup period
-duration 60 : duration of each point
-simulate raid0 : simulate striping across all the LUNs specified. There is only one LUN in this test
-write 0 : percentage of I/O that is write, which is zero in this test
-num_large 0 : maximum outstanding I/Os for large Random I/O. There is no large random I/O in this test.

Once the test is finished, stopped the dtrace script io.d .

Example output from a run
   Command line:
   -testname mytest -run advanced -matrix row -num_disks 5 -cache_size 51200 -duration 60
   -simulate raid0 -write 0 -num_large 0 

   These options enable these settings:
   Test: mytest
   Small IO size: 8 KB
   Large IO size: 1024 KB
   IO types: small random IOs, large random IOs
   Sequential stream pattern: RAID-0 striping for all streams
   Writes: 0%
   Cache size: 51200 MB
   Duration for each data point: 60 seconds
   Small Columns:,      0,      1,      2,      3,      4,      5,      6,      7,      8,      9,     10,     11,     12,
                       13,     14,     15,     16,     17,     18,     19,     20,     21,     22,     23,     24,     25
   Large Columns:,      0
   Total Data Points: 26

   Name: /domain0/group0/external/lun96g	Size: 103079215104
   1 files found.

   Maximum Small IOPS=62700 @ Small=16 and Large=0
   Minimum Small Latency=81.81 usecs @ Small=2 and Large=0
Things look wrong right away.
The average latency is in 100s microseconds (above the fastest minute was average of 81us) over a file that is 96G which is twice as big as the cache of 48G.
The max throughput was 489MB/s
Total blocks read
    # wc -l blocks-touched.txt
    78954834 blocks-touched.txt
Unique blocks read
    # sort blocks-touched.txt | uniq -c | sort -rn > block-count.txt
    # wc -l block-count.txt
    98305 block-count.txt
We only hit 98,305 unique offsets in the file yet a 96GB file has 12,582,912 unique 8k offsets.
The unique block hits totals around 768 MB of data which is easily cached.
Blocks  access frequency
    # tail block-count.txt
    695 109297664
    694 34532360192
    693 76259328
    693 34558271488
The least frequently hit blocks were hit almost 700 times and the average was over 800 yet there were 78,954,834 block access in a file of
12,582,912 unique blocks , so the average should have been about 6 hits per block.

This may be caused by having multiple steams starting from the beginning of the  file  or at the same “random” offset every test duration of 60 seconds. I’m not sure. If this is the case, the only work around would be to increase the duration to an amount of time that would insure kicking out most of the blocks from the beginning of the test. If each thread starts out at the same location and reads the same set of “random” blocks, then there is no workaround. Ideally I’d want each stream to be starting from a different random location and reading a different set of random blocks.


Oracle 11gR2 RAC on Oracle Linux 6.1 using VirtualBox…

It’s all a bit last minute, but today I decided to do an Oracle 11gR2 ( RAC installation on Oracle Linux 6.1 using VirtualBox. The patch has fixed all of the installation issues related to RAC on OL6.1, so it was pretty smooth. The procedure can be seen here.

As noted in the article, the screen shots of the GI and DB installers are from an article. I’ll update these screen shots when I get back from OpenWorld. Like I said, it was all a bit last minute. :) Normally I wouldn’t put an article like this live (and you can see it’s not on the homepage yet), but I get lots of questions about this subject, so I thought I would make it available to make my life easier.



PS. There is no suitable oracle-validated package available for this at the moment, so the prerequisites have to be done manually.


I’m in the AA lounge at Heathrow, just waiting to be called for my flight to Chicago en route to OOW. (I’m going to be getting there a bit early).

I don’t have anything timetabled for the event, yet, so if you see me wandering about aimlessly any time, feel free to stop me and say hello. It’s easy to recognise me, I’ll be carrying a laptop bag that’s only slightly smaller than an Exadata quarter rack.