Oracle 12.1.0.2: Wait event histograms in μs

By Franck Pachot

.
When an Oracle Database spends a high percentage of its DB time in User I/O, I usually check the wait event histograms in order to see if the storage system is working well. But today, with storage going to SSD, most I/O are less than 1 milliseconds and we have no details about those wait times.

Here is what is exposed by V$WAIT_EVENT_HISTOGRAM:

select event,wait_time_milli,wait_count from v$event_histogram where event like 'db file sequential read' order by event,wait_time_milli;
EVENT                          WAIT_TIME_MILLI WAIT_COUNT
------------------------------ --------------- ----------
db file sequential read                      1      27140
db file sequential read                      2          6
db file sequential read                      4          1

The latest Oracle 12c patchset, 12.1.0.2, besides changing the future of the database world with the In-Memory option, comes with a small new feature that helps us in our day-to-day tasks: the introduction of the V$EVENT_HISTOGRAM_MICRO view:

select event,wait_time_micro,wait_count,wait_time_format from v$event_histogram_micro where event like 'db file sequential read' order by event,wait_time_micro;
EVENT                          WAIT_TIME_MICRO WAIT_COUNT WAIT_TIME_FORMAT
------------------------------ --------------- ---------- ----------------
db file sequential read                      1        120   1 microsecond
db file sequential read                      2         24   2 microseconds
db file sequential read                      4         51   4 microseconds
db file sequential read                      8        212   8 microseconds
db file sequential read                     16      19600  16 microseconds
db file sequential read                     32       5958  32 microseconds
db file sequential read                     64        550  64 microseconds
db file sequential read                    128        492 128 microseconds
db file sequential read                    256         98 256 microseconds
db file sequential read                    512         14 512 microseconds
db file sequential read                   1024         21   1 millisecond
db file sequential read                   2048          6   2 milliseconds
db file sequential read                   4096          1   4 milliseconds

Here it is: the wait event are detailed up to microseconds. It’s good for I/O when on SSD. It’s good for In-Memory events as well.

Unfortunately, this has not been yet introduced in the AWR reports (I made an enhancement request for that).

Now, if you wonder which disk I’m using to get the microsecond i/o above…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

…

… here is how I created that database:

mkdir -p /mnt/ramdisk
mount -t tmpfs -o size=8G tmpfs /mnt/ramdisk
$ORACLE_HOME/bin/dbca -createDatabase -templateName General_Purpose.dbc -gdbName RAMDB -sid RAMDB -sysPassword oracle -systemPassword oracle -storageType FS -characterSet AL32UTF8 -listeners LISTENER -sampleSchema  true -totalMemory 600 -databaseType MULTIPURPOSE -silent -datafileDestination /mnt/ramdisk