Tuning the Database - Expert Oracle RAC Performance Diagnostics and Tuning - page 283

Workshop 2
In this workshop, let's try to analyze the waits experienced during the INSERT operation in a RAC environment
Step 1
A good start to look at the overall SQL query performance is the V$SQLSTATS view. This view has considerable amount
of information and provides instrumentation into the various resource layers of the database.
Script: MVRACPDnTap_clsqlstats.sql
SELECT inst_id INT,
sql_id,
application_wait_time awt,
concurrency_wait_time conwt,
cluster_wait_time clwt,
io_interconnect_bytes ioicbytes
FROM gv$sqlstats
WHERE cluster_wait_time > 1000000
ORDER BY inst_id,
user_io_wait_time DESC;
INT SQL_ID AWT CONWT CLWT IOICBYTES
---- ---------------- -------- ------------- ------------- -------------
1 4sk2z4rskuv4m 5333 1103230925 10615735924 48594944
2ub6q6w9aa9pf 577 1961830 227813563 73883648
ah2dbxq8w1d8h 2151 4856912 41917285 46096384
fcxg48dtnwxqj 1479 307829 17081420 7299072
0xta36p7ykq6g 0 358559 1658681 688128
gbdcj3cn39mrm 8031 182480 1464970 5324800
2 4sk2z4rskuv4m 27978 1151084992 10582537422 51216384
2ub6q6w9aa9pf 229 1705140 213407755 80986112
ah2dbxq8w1d8h 3614 143960067 42598312 28966912
fcxg48dtnwxqj 2654 250894 19748310 9322496
0xta36p7ykq6g 0 162982 2758555 491520
gbdcj3cn39mrm 429 112164 1854546 3538944
9x7rfd9d7vcsg 523 158200 1400292 294912
4 3kvu10uhykg7y 29592274 6879477278 42519308702 14344192
8436p6pb4j6pz 3.89E+08 2067177857 8736564233 6168576
7g94kt7nkq1yt 612 672397897 8283446822 56672256
2ub6q6w9aa9pf 1618 53981572 2026659828 51994624
38kt82gam41ak 43108567 90322921 520242545 434176
ast9xrxar0yx6 1309 11456019 273045708 4268032
92ccq3p60gwz2 139 64122983 203911401 425984
0h6tt5nw7kyad 1927 4306372 148356800 41164800