Real-Time enqueue statistics

Again the same story : Oracle provides a useful view to check enqueue statistics (v$enqueue_statistics), but this view is a cumulative one. So how to check what’s is going on my database right now with cumulative values ?

You have to substract values as it has been done into Riyaj Shamsudeen’s post.

So to get real-time enqueue statistics, you can use the enqueue_statistics.pl script (click on the link and then on the view source button to copy/paste the source code) that basically takes a snapshot based on the gv$enqueue_statistics view each second (default interval) and computes the differences with the previous snapshot.

Let’s see an example:

./enqueue_statistics.pl

20:01:10   INST_NAME	EQ_NAME			EQ_TYPE		REQ_REASON	TOTAL_REQ 
20:01:10   BDT_1	Job Queue Date		JD		contention	1
20:01:10   BDT_1	Job Queue		JQ		contention	2 
20:01:10   BDT_1	Job Scheduler		JS		q mem clnup lck	2
20:01:10   BDT_1	Session Migration	SE		contention	4
20:01:10   BDT_1 	Job Scheduler		JS		contention	15
20:01:10   BDT_1 	Job Scheduler		JS		queue lock	15 
20:01:10   BDT_1	Media Recovery		MR         	contention	28    
20:01:10   BDT_1	Cursor		        CU		contention	107   
20:01:10   BDT_1	Transaction		TX		contention	122
20:01:10   BDT_1	DML	                TM		contention	166
--------------------------------------> NEW
20:01:11   INST_NAME	EQ_NAME			EQ_TYPE		REQ_REASON	TOTAL_REQ 
20:01:11   BDT_1	Job Queue Date		JD		contention	1
20:01:11   BDT_1	Job Queue		JQ		contention	1
20:01:11   BDT_1	Job Scheduler		JS		q mem clnup lck	1
20:01:11   BDT_1	Session Migration	SE		contention	2
20:01:11   BDT_1 	Job Scheduler		JS		contention	12
20:01:11   BDT_1 	Job Scheduler		JS		queue lock	17 
20:01:11   BDT_1	Media Recovery		MR         	contention	20    
20:01:11   BDT_1	Cursor		        CU		contention	100   
20:01:11   BDT_1	Transaction		TX		contention	134
20:01:11   BDT_1	DML	                TM		contention	185

As you can see during the last second the DML enqueue has been requested 185 times.

For lisiblity, I removed some columns into the output that are displayed by the script: TOTAL_WAIT,SUCC_REQ,FAILED_REQ,WAIT_TIME

Output is ordered based on the TOTAL_REQ column but you could choose to order on any column.

Let’s see the help:

./enqueue_statistics.pl help                   

Usage: enqueue_statistics.pl [Interval [Count]] [inst=] [top=] [eq_name=] [eq_type=] [req_reason=] [sort_field=]
        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                                    ALL        
  EQ_NAME=          ALL - Show all ENQ NAME (wildcard allowed)                   ALL        
  EQ_TYPE=          ALL - Show all ENQ TYPE (wildcard allowed)                   ALL        
  REQ_REASON=       ALL - Show all REASONS (wildcard allowed)                    ALL        
  SORT_FIELD=       TOTAL_REQ|TOTAL_WAIT|SUCC_REQ|FAILED_REQ|WAIT_TIME           TOTAL_REQ  

Example : enqueue_statistics.pl
Example : enqueue_statistics.pl sort_field=WAIT_TIME
Example : enqueue_statistics.pl eq_name='%sactio%' sort_field=WAIT_TIME
Example : enqueue_statistics.pl req_reason='contention'

As usual:

  • You can choose the number of snapshots to display and the time to wait between snapshots.
  • You can choose to filter on enqueue name, enqueue type and enqueue reason (by default no filter is applied).
  • You can choose the column to sort the output on (new).
  • 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.

Remark:

I’ll provide a few more real-time utility scripts (see this page for the existing ones) and after that I’ll put all those scripts together into a single “real-time” utility script.

About these ads
This entry was posted in Perl Scripts, ToolKit and tagged , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s