Using direct I/O and the solaris dtrace tool

Using Solaris Dynamic Tracing (dtrace) can help to eliminate hotspots on files and give an insight to your system. Oracle ported dtrace to Oracle Linux which makes it just more interesting. This article gives a quick start using the dtrace facility and direct I/O. The detailed dtrace description can be found in its documentation.




We first check the direct I/O facility usage. You need to install the directiostat from solarisinternals.  
Make sure the DB parameter filesystemio_options is set to SETALL in your database. You need to to generate some I/O on your database. For example you can gather statistics and at the same time copy a bigger table for testing purposes. While reads and writes take place use the directiostat command:

-bash-3.00$ ./directiostat 3

  lreads lwrites  preads pwrites     Krd     Kwr holdrds  nflush

      52      29      28      38       0       0       0       0

       0      18       0      18       6     160       0       0

       0       3       0       3       6      38       0       0

Watch the following column to see if the database uses directio:

• lreads = logical reads to the UFS via directio
• lwrites = logical writes to the UFS via directio

If you want to compare the actual system performance with a new system before a migration takes place you have to produce the same read and write action on both systems and then use dtrace to debug the insights. You can also compare day peak write or read performance for two systems or use the dtrace facility to document a system in case of future performance problems. First you need to grant permissions to the oracle user. Issue as root:

usermod -K defaultpriv=basic,dtrace_kernel oracle

Measure the read performance with the following command:

-bash-3.00# dtrace -n 'syscall::read*:return {@a[execname ] = quantize(arg0);}’

dtrace: description 'syscall::read*:return ' matched 3 probes

^C

  oracle

           value  ------------- Distribution ------------- count

              -1 |                                         0

               0 |                                         2

               1 |                                         0

               2 |                                         0

               4 |                                         0

               8 |                                         3

              16 |                                         0

              32 |                                         5

              64 |                                         5

             128 |                                         6

             256 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@    483

             512 |@                                        16

            1024 |                                         4

            2048 |                                         1

            4096 |                                         1

            8192 |                                         0

           16384 |                                         2

           32768 |                                         0

To measure the write performance we need to write a script and call it using the "-s" switch:

#pragma D option quiet

io:::start

/args[0]->b_flags&B_WRITE/

{

@[execname,args[2]->fi_dirname]=count();

}

END

{

printf("%20s%51s%5s\n","WHO","WHERE","COUNT");

printa("%20s%51s%5@d\n",@);

}

The output looks like this. You can see who writes in which directory and identify the process.

-bash-3.00# dtrace -s ./whowrites.d

^C

                 WHO                                              WHERECOUNT

             fsflush                        /global/oradb/oradata/files    4

              oracle                        /global/oradb/oradata/files 1143

        ...