This was first published on https://blog.dbi-services.com/12102-wait-event-histograms-in-s (2014-08-04)
Republishing here for new followers. The content is related to the the versions available at the publication date
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
Hi Franck,
unfortunately they forgot to capture this info in AWR In AWR, there’s still only the DBA_HIST_EVENT_HISTOGRAM.
Maris
Hi Maris, I tried to open a Enhancement Request when 12c came out. I’ll re-try. Thanks for the reminder. Regards, Franck.