Database Reference
In-Depth Information
Queries that have high version counts maintained on multiple instances also tend to show high
CONCURRENCY_WAIT_TIME
and
CLUSTER_WAIT_TIME
. When high version counts are maintained across multiple
instances, the concurrency wait time (
CONCURRENCY_WAIT_TIME
) increases. Version counts also cause Oracle to perform
a high degree of coordination among multiple instances; it increases the overall cluster wait time (
CLUSTER_WAIT_TIME
).
The
CLUSTER_WAIT_TIME
column shows the total time spent waiting for all waits that are categorized under the
“Cluster” class of wait events. The value is an accumulated wait time spent waiting for Oracle RAC cluster resources.
■
CONCURRENCY_WAIT_TIME
and
CLUSTER_WAIT_TIME
statistics can be obtained from
V$SQLSTATS
.
Note
The following query identifies queries that have high wait times:
SELECT inst_id inst,
sql_id,
cpu_time cputime,
elapsed_time elpsdtime,
px_servers_executions pxsrvEx,
application_wait_time awt,
concurrency_wait_time conwt,
cluster_wait_time clwt,
user_io_wait_time uiwt
FROM gv$sqlstats
WHERE user_io_wait_time > 0
ORDER BY user_io_wait_time;
INST SQL_ID CPUTIME ELPSDTIME PXSRVEX AWT CONWT CLWT UIWT
---- ------------- ---------- ---------- ---------- ---------- ---------- ---------- -----------
2 7u49y06aqxg1s 1280813 41663201 0 0 125214 15170669 22868319
3 2ns5gzz3fcpq2 7171914 30079286 0 0 29293 8539546 611029147
3 f47hz4fd4yyx8 3688442 272873315 0 0 196975 502986 23884755
3 0w26sk6t6gq98 5243202 99495596 0 1764954 1346243 693593 27981060
3 6q9zvynq8f0h0 1157818 38728380 0 995 45065 4157710 28085768
2 3waf8n8kvtntc 315952 31076715 0 0 21363 382094 30388177
3 7u49y06aqxg1s 1396798 48420345 0 0 16781 3554500 33859424
3 axq73su80h4wq 987849 60333409 0 0 43328 1108248 44998699
2 5k5207588w9ry 4778274 135105206 0 639018 1615341 1961831 45633987
2 fhf8upax5cxsz 7559852 154927182 0 713558 2151904 2413757 49989364
3 cvn54b7yz0s8u 934859 80678934 0 0 10912 7377111 52286936
3 fhf8upax5cxsz 10840350 220009195 0 2381637 12457716 3353428 61467176
4 0jzgrxvn2gag8 1451759 101130837 0 0 8391 12541211 603568199
4 4a0mfzwgqw5fq 60096857 220706621 0 0 194957 165022097 610027252
In Oracle 12.1.0 (12c), Oracle has added a few more delta columns related to cluster statistics. The “delta_”
columns in the following query reflect time spent by the cursor (in microseconds) since the last AWR snapshot:
SELECT inst_id inst,
sql_id,
delta_cpu_time dcputime,
delta_elapsed_time detime,
delta_application_wait_time dawt,
Search WWH ::
Custom Search