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