Oracle object wait statistics

common waits are ITL, Buffer and Row Lock contention. knowing the frequency of these is critical to understanding the root cause of any contention. When these occur, the most common remedies for high buffer busy waits include database writer (DBWR) contention tuning, adding freelists (or ASSM), and adding missing indexes. 

When reviewing objects for possible tuning issues, it is handy to have statistics such as the number of internal transaction list (ITL) waits, buffer busy waits and row lock waits that the object has experienced. Combined with the number of logical and physical reads the object has experienced, the above statistics give a complete picture of the usage of the object in question.

To see a list of these per object recorded since the database/instance was last restarted, run the following sql
SELECT * FROM
(
SELECT
DECODE
(GROUPING(a.object_name), 1, 'All Objects', a.object_name)
AS "Object",
SUM(CASE WHEN
a.statistic_name = 'ITL waits'
THEN
a.VALUE ELSE NULL END) "ITL Waits",
SUM(CASE WHEN
a.statistic_name = 'buffer busy waits'
THEN
a.VALUE ELSE NULL END) "Buffer Busy Waits",
SUM(CASE WHEN
a.statistic_name = 'row lock waits'
THEN
a.VALUE ELSE NULL END) "Row Lock Waits",
SUM(CASE WHEN
a.statistic_name = 'physical reads'
THEN
a.VALUE ELSE NULL END) "Physical Reads",
SUM(CASE WHEN
a.statistic_name = 'logical reads'
THEN
a.VALUE ELSE NULL END) "Logical Reads"
FROM
gv$segment_statistics a
WHERE
a.owner LIKE UPPER('&owner')
GROUP BY
rollup(a.object_name)) b
WHERE (b."ITL Waits">0 OR b."Buffer Busy Waits">0)