get Cluster Waited Event from V$SQLSTATS

Nothing this day, I sat around and wrote script to check sql statement used much time on Cluster Waited Event.

That could query cluster_wait_time column on v$sql and v$sqlarea views.

Actually that could query cluster_wait_time on v$sqlstats as well.

On three Views could help get another else..., Example: BUFFER_GETS, DISK_READS, DIRECT_WRITES ...blah blah

Which view should we query?

V$SQLSTATS view is queried faster... 

select sql_text, cluster_wait_time from v$sql where cluster_wait_time > 50000000 order by 2;

Elapsed: 00:00:00.40

select sql_text, cluster_wait_time from v$sqlarea where cluster_wait_time > 50000000 order by 2;

Elapsed: 00:00:00.46

select sql_text, cluster_wait_time from v$sqlstats where cluster_wait_time > 50000000 order by 2;

Elapsed: 00:00:00.05

Why?...

Oracle database Version 10.2.0.2 and later, a SELECT from the V$SQLSTATS view is protected by mutexes. 

select sql_text, cluster_wait_time, sharable_mem, buffer_gets, disk_reads from v$sqlstats where cluster_wait_time > 50000000 order by 2

SQL_TEXT    CLUSTER_WAIT_TIME SHARABLE_MEM BUFFER_GETS DISK_READS

--------------------------------------------------------------------------------

call "TEST"()    6.8305E+10        11280   658815078    7653630