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.