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.
bdt's blog 