Did you know that there’s something like Active Session History also in the Exadata storage cells? ;-)
The V$CELL_THREAD_HISTORY view is somewhat like V$ACTIVE_SESSION_HISTORY, but it’s measuring thread activity in the Exadata Storage Cells:
SQL> @desc v$cell_thread_history
Name Null? Type
------------------------------- -------- ----------------------------
1 CELL_NAME VARCHAR2(1024)
2 SNAPSHOT_ID NUMBER
3 SNAPSHOT_TIME DATE
4 THREAD_ID NUMBER
5 JOB_TYPE VARCHAR2(32)
6 WAIT_STATE VARCHAR2(32)
7 WAIT_OBJECT_NAME VARCHAR2(32)
8 SQL_ID VARCHAR2(13)
9 DATABASE_ID NUMBER
10 INSTANCE_ID NUMBER
11 SESSION_ID NUMBER
12 SESSION_SERIAL_NUM NUMBER
It keeps about 10 minutes worth of samples of Exadata Storage Cell thread activity:
SQL> @minmax snapshot_time v$cell_thread_history Show min/max (low/high) values in column "snapshot_time" of table v$cell_thread_history... MIN(SNAPSHOT_TIME MAX(SNAPSHOT_TIME ----------------- ----------------- 20130419 14:42:15 20130419 14:52:54
Note that it’s not the V$ view or the database instance which stores this array – it’s the storage cells themselves. If you query the v$cell_thread_history view, your Oracle database session is going to “gather” this instrumentation data from all the required cells and present it to you, that’s why the “cell statistics gather” wait event shows up:
PARSING IN CURSOR #140596385017248 len=42 dep=0 uid=0 oct=3 lid=0 tim=1366404896817011 hv=4063158547 ad='19e452578' sqlid='63awy1gg t2xs8m' select count(*) from v$cell_thread_history END OF STMT PARSE #140596385017248:c=0,e=115,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4272803188,tim=1366404896817010 EXEC #140596385017248:c=0,e=27,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=4272803188,tim=1366404896817127 WAIT #140596385017248: nam='SQL*Net message to client' ela= 1 driver id=1413697536 #bytes=1 p3=0 obj#=40 tim=1366404896817152 WAIT #140596385017248: nam='cell statistics gather' ela= 283 cellhash#=0 p2=0 p3=0 obj#=40 tim=1366404896818846 WAIT #140596385017248: nam='cell statistics gather' ela= 352 cellhash#=0 p2=0 p3=0 obj#=40 tim=1366404896819317 WAIT #140596385017248: nam='cell statistics gather' ela= 376 cellhash#=0 p2=0 p3=0 obj#=40 tim=1366404896820929 WAIT #140596385017248: nam='cell statistics gather' ela= 326 cellhash#=0 p2=0 p3=0 obj#=40 tim=1366404896822198 WAIT #140596385017248: nam='cell statistics gather' ela= 580 cellhash#=0 p2=0 p3=0 obj#=40 tim=1366404896823620 ...
Now, how frequently do the cells sample their “ASH” data:
SQL> SELECT DISTINCT (snapshot_time - LAG(snapshot_time)
OVER (ORDER BY snapshot_time)) * 86400 lag_seconds
FROM (SELECT distinct snapshot_time
FROM v$cell_thread_history WHERE cell_name = '192.168.12.3');
LAG_SECONDS
-----------
1
Looks like the sampling is done exactly once per second!
So, great, what can we do with this data?
Here’s an Exadata performance script (cth.sql) which uses V$CELL_THREAD_HISTORY to dig into cell activity from the database layer.
