 |
|
Oracle Tips by Burleson |
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
enqueues noted 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.
The
above book excerpt is from:
Oracle
Solid State Disk Tuning
High Performance Oracle
tuning with RAM disk
ISBN
0-9744486-5-6
Donald K. Burleson & Mike Ault
http://www.rampant-books.com/book_2005_1_ssd.htm
 |
For more details and scripts, see my new book "
Oracle
Tuning: The Definitive Reference", over 900 pages
of BC's favorite tuning tips & scripts.
You can buy it direct from the publisher for 30%-off and get
instant access to the code depot. |
|
