Did you already need to measure real-time I/O performance of an oracle database ?
AWR could provide an answer for historical period of time but what’s about: what is going on on my database right now ?
To measure real-time I/O performance I use one of my perl script (system_event.pl) based on the gv$system_event cumulative view.
As this view is a cumulative one (values are gathered since the instance started up), to extract real-time information the script takes a snapshot each second (default interval) and computes the differences with the previous snapshot.
Let’s see an example: as this post deals with IO performance, we can focus on this particular wait class that way:
./system_event.pl waitclass="User I/O"
18:27:36 INST_NAME EVENT NB_WAITS TIME_WAITED ms/Wait
18:27:36 BDTO_2 Disk file operations I/O 1 77 0.077
18:27:36 BDTO_2 db file scattered read 241 1423749 5.908
18:27:36 BDTO_2 db file sequential read 260 1168489 4.494
18:27:36 BDTO_2 db file parallel read 626 10447664 16.690
--------------------------------------> NEW
18:27:37 INST_NAME EVENT NB_WAITS TIME_WAITED ms/Wait
18:27:37 BDTO_2 Disk file operations I/O 1 84 0.084
18:27:37 BDTO_2 db file scattered read 205 1128732 5.506
18:27:37 BDTO_2 db file sequential read 261 1196085 4.583
18:27:37 BDTO_2 db file parallel read 592 9224011 15.581
So, during the last second we got 261 “db file sequential read” events for an average of 4.583 ms per wait.
You could get more detailed information thanks to the gv$event_histogram view (benefit of this view is described in this post).
As this view is a cumulative one, to extract real-time information, I wrote the event_histogram.pl perl script.
./event_histogram.pl event='db file sequential read'
18:32:52 INST_NAME EVENT WAIT_TIME_MS COUNT
18:32:52 BDTO_1 db file sequential read 1 0
18:32:52 BDTO_2 db file sequential read 2 0
18:32:52 BDTO_2 db file sequential read 256 0
18:32:52 BDTO_1 db file sequential read 4 0
18:32:52 BDTO_2 db file sequential read 8 0
18:32:52 BDTO_1 db file sequential read 8 0
18:32:52 BDTO_1 db file sequential read 512 0
18:32:52 BDTO_1 db file sequential read 16 0
18:32:52 BDTO_2 db file sequential read 4 2
18:32:52 BDTO_2 db file sequential read 1 5
In this example we can see that 5 “df file sequential read” took less than 1ms to complete during the last second.
Of course those two perl scripts deal with all oracle wait events and not only the ones related to the User I/O wait class: then you can extend their usage area following your needs on system events.
See the help for more details:
./system_event.pl help
Usage: ./system_event.pl [Interval [Count]] [inst] [top=] [waitclass=Concurrency|User I/O|System I/O|Administrative|Configuration|Other|Cluster|Application|Idle|Commit|Network] [event=]
Default Interval : 1 second.
Default Count : Unlimited
Parameter Comment Default
--------- ------- -------
INST= ALL - Show all Instance(s) ALL
CURRENT - Show Current Instance
INSTANCE_NAME,... - choose Instance(s) to display
<< Instances are only displayed in a RAC DB >>
TOP= Number of rows to display 10
WAITCLASS=Concurrency|User I/O|System I/O|Administrative|Configuration|Other|Cluster|Application|Idle|Commit|Network ALL
EVENT= ALL - Show all EVENTS ALL
As usual :
- You can choose the number of snapshots to display and the time to wait between snapshots.
- You can choose to filter on wait_class and on a particular event (by default no filter is applied).
- This script is oracle RAC aware : you can work on all the instances, a subset or the local one.
- You have to set oraenv on one instance of the database you want to diagnose first.
- The script has been tested on Linux, Unix and Windows.