Exadata is about doing IO. I think if there’s one thing people know about Exadata, that’s it. Exadata brings (part of the) processing potentially closer to the storage media, which will be rotating disks for most (Exadata) users, and optionally can be flash.
But with Exadata, you either do normal alias regular IO, which will probably be single block IO, or multiblock IO, which hopefully gets offloaded. The single block reads are hopefully coming from the flashcache, which can be known by looking at v$sysstat/v$sesstat at the statistic (“cell flash cache read hits”), not directly by looking at the IO related views. To understand the composition of the response time of a smartscan, there is even lesser instrumentation in the database (for background, look at this blogpost, where is shown that the smartscan wait does not detail any of the steps done in a smartscan. In other words: if you experience performance differences on Exadata, and the waits point towards IO, there’s not much analysis which can be done to dig deeper.
Luckily, the Exadata storage server provides a very helpful dump which details IO latencies of what the cell considers celldisks (which are both flash and rotating disks). The dump provides:
– IO size by number of reads and writes
– IO size versus latency for reads and writes
– IO size versus pending IO count for reads and writes
– IO size versus pending IO sizes for reads and writes
This is how this dump is executed (in the cellcli of course):
alter cell events="immediate cellsrv.cellsrv_dump('iolstats',0)";
As with the other dumps, the cellcli provides the name of the trace file where the requested dump has been written to. If we look inside this trace file, this is how an IO latencies dump looks like:
IO length (bytes): Num read IOs: Num write IOs:
[ 512 - 1023) 212184 104402
[ 1024 - 2047) 0 138812
[ 2048 - 4095) 0 166282
[ 4096 - 8191) 35 134095
[ 8192 - 16383) 498831 466674
[ 16384 - 32767) 2006 73433
[ 32768 - 65535) 91 15072
[ 65536 - 131071) 303 4769
[ 131072 - 262143) 297 6376
[ 262144 - 524287) 1160 230
[ 524288 - 1048575) 2278 36
[1048576 - 2097151) 459 21
Average IO-latency distribution stats for CDisk CD_02_enkcel01
Number of Reads iosize-latency distribution
IO len(B)\IO lat(us) || [ 32 | [ 64 | [ 128 | [ 256 | [ 512 | [ 1024 | [ 2048 | [ 4096 | [ 8192 | [ 16384 | [ 32768 | [ 65536 | [ 131072 | [ 262144 | [ 524288 |
|| 63) | 127) | 255) | 511) | 1023) | 2047) | 4095) | 8191) | 16383) | 32767) | 65535) | 131071) | 262143) | 524287) | 1048575) |
---------------------||------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|------------|
[ 512, 1023) || 31075 | 14592 | 69575 | 55370 | 7744 | 385 | 725 | 6489 | 7044 | 11663 | 4030 | 1770 | 1310 | 408 | 4 |
[ 4096, 8191) || 0 | 6 | 5 | 6 | 0 | 0 | 0 | 0 | 7 | 8 | 3 | 0 | 0 | 0 | 0 |
[ 8192, 16383) || 66 | 101 | 3189 | 6347 | 717 | 1826 | 23168 | 124246 | 191169 | 79157 | 37032 | 18508 | 12778 | 526 | 1 |
[ 16384, 32767) || 22 | 46 | 22 | 1403 | 90 | 46 | 57 | 65 | 77 | 124 | 39 | 5 | 7 | 3 | 0 |
...
What struck me as odd, is the name of the celldisk (CD_02_enkcel01 here) is below the first table (IO lengths) about this celldisk(!)
In my previous post we saw a command to reset statistics (a cell events command). There is a command to reset the statistics for this specific dump (‘iolstats’) too (to be executed in the cellcli of course):
alter cell events = "immediate cellsrv.cellsrv_resetstats(iolstats)";
Next, I executed a smartscan
IO length (bytes): Num read IOs: Num write IOs:
[ 4096 - 8191) 0 24
[ 524288 - 1048575) 8 0
[1048576 - 2097151) 208 0
Average IO-latency distribution stats for CDisk CD_02_enkcel01
Number of Reads iosize-latency distribution
IO len(B)\IO lat(us) || [ 4096 | [ 8192 | [ 16384 | [ 32768 | [ 65536 | [ 131072 | [ 262144 |
|| 8191) | 16383) | 32767) | 65535) | 131071) | 262143) | 524287) |
---------------------||------------|------------|------------|------------|------------|------------|------------|
[ 524288, 1048575) || 0 | 0 | 3 | 1 | 0 | 2 | 2 |
[ 1048576, 2097151) || 1 | 3 | 15 | 22 | 89 | 59 | 19 |
As can be seen, the statistics have been reset (on the local cell/storage server!). This makes diagnosing the physical IO subsystem of Exadata possible!
Frits Hoogland Weblog 