There are certain dictionary views available to monitor the Exadata cells from the database.One of the view is gv$cell_thread_history. This view is very interesting as it delves down to the session level with its serial number, SQL, specific cell, instance and also which Exadata object was waited and every entry is logged with the time. Looks very cool:
SQL> desc gv$cell_thread_history
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
INST_ID NUMBER
CELL_NAME VARCHAR2(1024)
SNAPSHOT_ID NUMBER
SNAPSHOT_TIME DATE
THREAD_ID NUMBER
JOB_TYPE VARCHAR2(32)
WAIT_STATE VARCHAR2(32)
WAIT_OBJECT_NAME VARCHAR2(32)
SQL_ID VARCHAR2(13)
DATABASE_ID NUMBER
INSTANCE_ID NUMBER
SESSION_ID NUMBER
SESSION_SERIAL_NUM NUMBER
Here is a sample code snippet:
SQL> select wait_object_name,count(*) cnt from gv$cell_thread_history group by wait_object_name order by cnt;
WAIT_OBJECT_NAME CNT
-------------------------------- ----------
UserThread Cond 4
PredicateDisk-113 4
bwresv Q_1/2k 4
bufWaitObjQ_8k 4
PredicateDiskRead IO Completion 4
bwresv Q_8k 4
FSA: cache replacementQ_1MHugePa 4
CachePut Fence 4
NetworkDirectory ReqHandle 4
PredicateCacheGetJob 4
Flash Cache LRU 4
FSA: Cache Get Job 4
DiskDirectory HT 4
GenTimeStats Operation 4
in use Q_8k 4
Completed Map Element List 4
gdisk ext 4
FSA: fcCtxt 8
FSA: cache replacementQ_8k 8
FlashCache HT buckets 8
PredicateDisk-110 8
FC outstanding IOs 8
IOContext 8
Cache Completed Jobs 8
PredicateDisk-32 12
PredicateDisk-56 12
PredicateDisk-50 12
PredicateDisk-22 12
SQL> desc gv$cell_thread_history
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
INST_ID NUMBER
CELL_NAME VARCHAR2(1024)
SNAPSHOT_ID NUMBER
SNAPSHOT_TIME DATE
THREAD_ID NUMBER
JOB_TYPE VARCHAR2(32)
WAIT_STATE VARCHAR2(32)
WAIT_OBJECT_NAME VARCHAR2(32)
SQL_ID VARCHAR2(13)
DATABASE_ID NUMBER
INSTANCE_ID NUMBER
SESSION_ID NUMBER
SESSION_SERIAL_NUM NUMBER
Here is a sample code snippet:
SQL> select wait_object_name,count(*) cnt from gv$cell_thread_history group by wait_object_name order by cnt;
WAIT_OBJECT_NAME CNT
-------------------------------- ----------
UserThread Cond 4
PredicateDisk-113 4
bwresv Q_1/2k 4
bufWaitObjQ_8k 4
PredicateDiskRead IO Completion 4
bwresv Q_8k 4
FSA: cache replacementQ_1MHugePa 4
CachePut Fence 4
NetworkDirectory ReqHandle 4
PredicateCacheGetJob 4
Flash Cache LRU 4
FSA: Cache Get Job 4
DiskDirectory HT 4
GenTimeStats Operation 4
in use Q_8k 4
Completed Map Element List 4
gdisk ext 4
FSA: fcCtxt 8
FSA: cache replacementQ_8k 8
FlashCache HT buckets 8
PredicateDisk-110 8
FC outstanding IOs 8
IOContext 8
Cache Completed Jobs 8
PredicateDisk-32 12
PredicateDisk-56 12
PredicateDisk-50 12
PredicateDisk-22 12
