Segment I/O Statistics
The next section of the report breaks the
latches into their child components. While this is interesting
from a tuning perspective, it really is not in the purview of this
book, so the segment I/O statistics sections will be covered next.
The first section deals with segment physical and logical reads.
Examples of these sections are shown in the following listing:
Segments by
Logical Reads DB/Inst: SSD/ssd2 Snaps: 3-4
-> % Total
shows % of logical reads for each top segment compared with total
logical
reads for all segments captured by the Snapshot
Tablespace Subobject Obj. Logical
Owner Name Object Name Name Type
Reads %Total
----------
---------- ------------------- ---------- ----- ------------
------
TPCC
USERS C_ORDER TABLE 5,125,520
85.29
TPCC
USERS C_STOCK_I1 INDEX 358,688
5.97
TPCC
USERS C_STOCK TABLE 128,976
2.15
TPCC
USERS C_ITEM_I1 INDEX 74,128
1.23
TPCC
USERS C_ORDER_LINE_I1 INDEX 58,064
.97
-------------------------------------------------------------
Segments by
Physical Reads DB/Inst: SSD/ssd2 Snaps: 3-4
Tablespace Subobject Obj. Physical
Owner Name Object Name Name Type
Reads %Total
----------
---------- ------------------- ---------- ----- ------------
------
TPCC
USERS C_CUSTOMER TABLE 3,050
55.53
TPCC
USERS C_ORDER_LINE TABLE 847
15.42
TPCC
USERS C_ORDER_LINE_I1 INDEX 666
12.12
TPCC
USERS C_STOCK TABLE 533
9.70
TPCC
USERS C_CUSTOMER_I2 INDEX 208
3.79
-------------------------------------------------------------
The above listing indicates that the majority
of I/O was logical in nature. This is to be expected, of course,
since the system is nearly 100% cached in the database data
buffers. There is some physical I/O for cache replacement
activities and other database upkeep activity such as commits,
rollbacks, and DML related events. In an environment where one
considers using SSD, this report would be used to isolate specific
objects that would benefit from being placed on possibly limited
SSD resources, choosing to move high I/O objects from the normal
disk array to the SSD.
The second third of the segment I/O report
shows segments that have undergone various wait activities. This
report section is handy for isolating objects which might need
tuning or are involved in transactions (SQL) that might need
tuning. The following listing is an example of this part of the
AWRRPT:
Segments by
Buffer Busy Waits DB/Inst: SSD/ssd2 Snaps: 3-4
Buffer
Tablespace Subobject Obj. Busy
Owner Name Object Name Name Type
Waits %Total
----------
---------- ------------------- ---------- ----- ------------
------
TPCC
USERS C_DISTRICT TABLE 70
72.16
TPCC
USERS C_WAREHOUSE TABLE 14
14.43
TPCC
USERS C_ORDER_LINE TABLE 5
5.15
TPCC
USERS C_ORDER_LINE_I1 INDEX 3
3.09
SYS
SYSTEM SEG$ TABLE 2
2.06
-------------------------------------------------------------
Segments by
Row Lock Waits DB/Inst: SSD/ssd2 Snaps: 3-4
Row
Tablespace Subobject Obj. Lock
Owner Name Object Name Name Type
Waits %Total
----------
---------- ------------------- ---------- ----- ------------
------
TPCC
USERS C_DISTRICT TABLE 354
82.71
TPCC
USERS C_STOCK TABLE 32
7.48
TPCC
USERS C_NEW_ORDER TABLE 16
3.74
TPCC
USERS C_ORDER_LINE_I1 INDEX 12
2.80
TPCC
USERS C_ORDER_I1 INDEX 9
2.10
-------------------------------------------------------------
Segments by
ITL Waits DB/Inst: SSD/ssd2 Snaps: 3-4
No data exists for this section of the report.
-------------------------------------------------------------
The above listing shows that the same objects
are showing buffer busy waits
as are showing
row lock waits. Again, this points to
transaction locking as the cause for the
buffer busy waits and
ties back also to the large number of TX enqueuesnoted in previous report section listings.
The last third of the Segment report section
deals with RAC I/O and is particularly interesting as it shows the
objects most transferred across the interconnect. The following
listing shows the RAC Segment I/O report for the global cache
service based transfers of blocks:
Segments by
CR Blocks Received DB/Inst: SSD/ssd2 Snaps: 3-4
CR
Tablespace Subobject Obj. Blocks
Owner Name Object Name Name Type
Received %Total
----------
---------- ------------------- ---------- ----- ------------
------
TPCC
USERS C_STOCK TABLE 20,761
41.50
TPCC
USERS C_ORDER TABLE 11,166
22.32
TPCC
USERS C_ORDER_LINE_I1 INDEX 4,746
9.49
TPCC
USERS C_DISTRICT TABLE 3,139
6.27
TPCC
USERS C_WAREHOUSE TABLE 2,813
5.62
-------------------------------------------------------------
Segments by
Current Blocks Received DB/Inst: SSD/ssd2 Snaps: 3-4
Current
Tablespace Subobject Obj. Blocks
Owner Name Object Name Name Type
Received %Total
----------
---------- ------------------- ---------- ----- ------------
------
TPCC
USERS C_STOCK TABLE 16,523
44.48
TPCC
USERS C_DISTRICT TABLE 3,079
8.29
TPCC
USERS C_NEW_ORDER_I1 INDEX 3,041
8.19
TPCC
USERS C_ORDER TABLE 2,977
8.01
TPCC
USERS C_ORDER_LINE_I1 INDEX 2,884
7.76
-------------------------------------------------------------
This listing makes it easy to see what
segments have blocks being transferred across the interconnect. In
a move to SSD in a RAC environment, these segments should be
considered as possible targets to be placed on SSD.
