| |
 |
|
Monitoring Oracle table I/O with v$segment_statistics
Oracle Tips by Mike Ault
|
The v$segment_statistics view is a
goldmine for funding wait events that are associated with a specific
Oracle table. I wrote the following script to show run-time
details about a segment (usually a table or an index), and this
powerful script interrogates the v$segment_statistics view
use a CASE statement.
If you examine the script below you will
see that the v$segment_statistics view is grouped by object_name.
For each object, we display counts of the major object wait events,
as seen in the CASE expression. The most important of
these object-level wait events will give us clues into the source of
the contention.
For example, buffer busy waits and ITL waits
all have a clear set of causes, and knowing this information is
critical to understanding the root cause of your contention.
For example, 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.
The v$segment_statistics provides a
statistic_name and value column for each table, unfortunately
this format doesn't lend itself to easy use. By utilizing the
crosstab technique we can easily create a report to show us these
vital tuning statistics for our system. An example of this type of
cross tab report is shown below.
obj.xtab.sql
-- Crosstab of object and statistic for an owner
--
col "Object" format a20
set numwidth 12
set lines 132
set pages 50
@title132 'Object Wait Statistics'
spool rep_out\&&db\obj_stat_xtab
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
see code
deport for full script
v$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)
/
spool off
clear columns
ttitle off
The cross tab report generates a listing
showing the statistics of concern as headers across the page rather
than listings going down the page and summarizes them by object.
This allows us to easily compare total buffer busy waits to the
number of ITL or row lock waits. This ability to compare the ITL and
row lock waits to buffer busy waits lets us see what objects may be
experiencing contention for ITL lists, which may be experiencing
excessive locking activity and through comparisons, which are highly
contended for without the row lock or ITL waits. AN example of the
output of the report, edited for length, is shown below.
Object ITL Waits Buffer
Busy Waits Row Lock Waits Physical Reads Logical Reads
------------- ---------
----------------- -------------- --------------
-------------
BILLING
0 63636 38267 1316055 410219712
BILLING_INDX1
1 16510 55 151085 21776800
...
DELIVER_INDX1
1963 36096 32962 1952600
60809744
DELIVER_INDX2
88 16250 9029 18839481
342857488
DELIVER_PK
2676 99748 29293 15256214
416206384
...
All Objects
12613 20348859 1253057 1139977207
20947864752
In the above report the BILLING_INDX1 index has a large
amount of buffer busy waits but we can't account for them from
the ITL or Row lock waits, this indicates that the index is
being constantly read and the blocks then aged out of memory
forcing waits as they are re-read for the next process. On the
other hand, almost all of the buffer busy waits for the
DELIVER_INDX1 index can be attributed to ITL and Row Lock waits.
In situations where there are large numbers of ITL waits we need
to consider the increase of the INITRANS setting for the table
to remove this source of contention.
If the predominant wait is row lock waits then
we need to determine if we are properly using locking and cursors in
our application (for example, we may be over using the SELECT…FOR
UPDATE type code.) If, on the other hand all the waits are
un-accounted for buffer busy waits, then we need to consider
increasing the amount of database block buffers we have in our SGA.
As you can see, this object wait cross tab
report can be a powerful addition to our tuning arsenal.
 |
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|
|
and include the URL for the page.
