Measure oracle real-time I/O performance

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.

About these ads