Database Reference
In-Depth Information
Appl Wait Con Wait Cluster Wait User I/O wait
SQL_ID (ms) (ms) (ms) (ms)
------------- ------------ ----------- ------------- -------------
akb0u6dfpbcqn 12458.69 50881.92 68022.46 1049989.45
1dq43tb564dmb 26041.02 55622.92 71508.33 371585.91
4vtan8phkg4d0 .88 17.39 76868.96 336453.90
7npc1vbn2qwga 18004.79 52477.85 78335.88 1482720.46
6vd3bn115g0h7 6.13 1510.75 85650.27 651101.40
6v9fbpz6tpjhy .80 127.32 86482.62 2243458.18
3aycbjhvzgrk0 1.86 167.34 88968.07 4079031.46
argkfb1k5m1zu .00 104.20 90153.16 5070781.66
0308r77fnfdzx .54 553.85 105665.74 176199.54
4dra31bj7pqvr 2.03 106.54 160539.64 2738713.70
aka36ggk0gjpa 6.17 23143.29 223482.02 5718669.42
0kkhhb2w93cx0 2.13 33484.57 235952.26 6912.19
c4vucwab0u7ht .00 694.43 263394.35 171307.56
b8h7a9cy1tz81 8.31 485.29 267919.84 3606626.98
3p777gjzf6zhj 7.85 219.88 441139.22 6095130.74
c3h445x17v910 2.33 504.53 472236.90 4719911.04
g7mt7ptq286u7 34228.53 99153.58 487576.19 6971.50
6zyv4k3b8c9f0 2.16 114.17 551468.72 25795599.91
d47vn4kg3ksyv .00 33301.52 972390.02 2206026.51
Once the queries with high cluster wait time have been identified, the specific query can be retrieved using the SQL_ID.
SQL> SELECT SQL_FULLTEXT FROM GV$SQLSTATS WHERE SQL_ID='d47vn4kg3ksyv';
SQL_FULLTEXT
--------------------------------------------------------------------
SELECT SCHEDULES.SUPPLIER_WID, SCHEDULES.PRODUCT_WID, SCHEDULES.PLANT_LOC_WID, SCHEDULES.
INVENTORY_ORG_WID, SCHEDULES.PURCH_OR
G_WID, SCHEDULES.STORAGE_LOC_WID, SCHEDULES.RCPT_LOC_WID, SCHEDULES.PURCH_GROUP_ORG_WID, SCHEDULES.
COMPANY_ORG_WID, SCHEDULES.X
ACT_TYPE_WID, SCHEDULES.LINE_TYPE_WID, SCHEDULES.CREATED_BY_WID, SCHEDULES.CHANGED_BY_WID,
SCHEDULES.INVENTORY_PROD_WID, SCHEDU
……………………… ………. ………………
………… …………….. …………….
SQL_FULLTEXT is of datatype BLOB. To retrieve the complete information from this column, PL/SQL packages
such as DBMS_LOB.READ should be used.
In Oracle Database 11g Release 2, a few more columns have been added to V$SQLSTATS view, providing even
closer instrumentation into cluster-level statistics:
IO_CELL_OFFLOAD_ELIGIBLE_BYTES NUMBER
IO_INTERCONNECT_BYTES NUMBER
PHYSICAL_READ_REQUESTS NUMBER
PHYSICAL_READ_BYTES NUMBER
PHYSICAL_WRITE_REQUESTS NUMBER
PHYSICAL_WRITE_BYTES NUMBER
EXACT_MATCHING_SIGNATURE NUMBER
FORCE_MATCHING_SIGNATURE NUMBER
IO_CELL_UNCOMPRESSED_BYTES NUMBER
IO_CELL_OFFLOAD_RETURNED_BYTES NUMBER
Search WWH ::




Custom Search