Flash Cache

Have you ever heard the suggestion that if you see time lost on event write complete waits you need to get some faster discs.
So what’s the next move when you’ve got 96GB of flash cache plugged into your server (check the parameters below) and see time lost on event write complete waits: flash cache ?

db_flash_cache_file           /flash/oracle/flash.dat
db_flash_cache_size           96636764160

Here’s an extract from a standard 11.2.0.2 AWR report:

                           Total
Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s   >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
write complete waits: flas    32                     3.1              21.9  75.0


                           Waits
                           64ms
Event                      to 2s <32ms <64ms <1/8s <1/4s <1/2s   <1s   <2s  >=2s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
write complete waits: flas    11   3.1   3.1   3.1   3.1   6.3   6.3  12.5  62.5


                           Waits
                            4s
Event                      to 2m   <2s   <4s   <8s  <16s  <32s  < 1m  < 2m  >=2m
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
write complete waits: flas    20  37.5  18.8   9.4  12.5   9.4   9.4   3.1

It’s interesting to see the figures for single and multiblock reads from flash cache. The hardware is pretty good at single block reads – but there’s a strange pattern to the multiblock read times. The first set of figures is from the Top N section of the AWR, the second set is from the event histogram sections (the 11.2 versions are more informative than the 11.1 and 10.2 – even though the arithemtic seems a little odd at the edges). Given the number of reads from flash cache in the hour the tiny number of write waits isn’t something I’m going to worry about just yet – my plan is to get rid of a couple of million flash first. (Most of the read by other session waits are waiting on the flash cache read as well – so I’ll be aiming at two birds with one stone.)

                                                           Avg
                                                          wait   % DB
Event                                 Waits     Time(s)   (ms)   time Wait Class
------------------------------ ------------ ----------- ------ ------ ----------
db flash cache single block ph    3,675,650       5,398      1   35.2 User I/O
DB CPU                                            4,446          29.0
read by other session             1,092,573       1,407      1    9.2 User I/O
direct path read                      6,841       1,371    200    8.9 User I/O
db file sequential read             457,099       1,046      2    6.8 User I/O




                                                    % of Waits
                                 -----------------------------------------------
                           Total
Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s   >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
db flash cache multiblock  22.1K   7.6  12.3   7.7   6.0   8.8  24.7  32.8
db flash cache single bloc 3683K  66.6  22.6   3.6   2.4   3.9    .8    .0



                           Waits
                           64ms
Event                      to 2s <32ms <64ms <1/8s <1/4s <1/2s   <1s   <2s  >=2s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- -----
db flash cache multiblock   7255  67.2  21.5  10.4    .9    .0
db flash cache single bloc  1587 100.0    .0    .0    .0    .0    .0

Answers to the following question on a postcard please: Why do we get a “double hump” on the distribution of multiblock reads ?