gv$cell_thread_history ; A Nice View

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