Thursday, January 22, 2009

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


2 comments:

Arup said...

Great post. Thanks for the same, Surachart.

One more difference is that SQLSTATS is actually based on a different X$ view, x$kkssqlstat whereas V$SQL is based on x$kglcursor_child. This makes the data in V$SQLSTATS stay much longer, even after data is aged out of the library cache. Once more advantage of SQLSTATS.

Surachart said...

@Arup

Thank You for comment and good new learning for me (x$kkssqlstat )
:)