Negative Cell Offload – One reason

This is a post that has been sitting around for while… just an illustration of one of the reasons for negative cell offload numbers,

Global Stats
=========================================================================================================================
| Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  | Buffer | Read | Read  | Write | Write |  Cell   |
| Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) |  Gets  | Reqs | Bytes | Reqs  | Bytes | Offload |
=========================================================================================================================
|    2234 |    1660 |      574 |        0.00 |        0.00 |     0.01 |     5M | 206K |  54GB |  106K |  21GB | -29.87% |
=========================================================================================================================

Parallel Execution Details (DOP=4 , Servers Allocated=8)
===========================================================================================================================================================================================
| Name | Type  | Group# | Server# | Elapsed |   Cpu   |    IO    | Application | Concurrency | Cluster  | Buffer | Read  | Read  | Write | Write |   Cell   |         Wait Events         |
|      |       |        |         | Time(s) | Time(s) | Waits(s) |  Waits(s)   |  Waits(s)   | Waits(s) |  Gets  | Reqs  | Bytes | Reqs  | Bytes | Offload  |         (sample #)          |
===========================================================================================================================================================================================
| j012 | QC    |        |         |     173 |     173 |          |        0.00 |             |          |     13 |       |     . |       |     . |     NaN% |                             |
| p032 | Set 1 |      1 |       1 |      23 |      23 |     0.70 |             |             |          |     1M | 13352 |   9GB |       |     . |   51.46% |                             |
| p033 | Set 1 |      1 |       2 |      23 |      22 |     0.73 |             |             |          |     1M | 13057 |   9GB |       |     . |   51.22% | cell smart table scan (1)   |
| p034 | Set 1 |      1 |       3 |      23 |      23 |     0.71 |             |             |          |     1M | 13322 |   9GB |       |     . |   51.22% |                             |
| p035 | Set 1 |      1 |       4 |      23 |      23 |     0.80 |             |             |          |     1M | 13220 |   9GB |       |     . |   51.22% |                             |
| p028 | Set 1 |      2 |       1 |     492 |     349 |      143 |             |        0.00 |     0.00 |   7100 | 38385 |   4GB | 26400 |   5GB | -112.77% | direct path read temp (129) |
|      |       |        |         |         |         |          |             |             |          |        |       |       |       |       |          | direct path write temp (21) |
| p029 | Set 1 |      2 |       2 |     492 |     346 |      146 |             |        0.00 |     0.00 |   6745 | 37827 |   4GB | 26393 |   5GB | -112.77% | direct path read temp (122) |
|      |       |        |         |         |         |          |             |             |          |        |       |       |       |       |          | direct path write temp (17) |
| p030 | Set 1 |      2 |       3 |     491 |     352 |      139 |             |        0.00 |     0.00 |   6590 | 38809 |   4GB | 26390 |   5GB | -108.33% | direct path read temp (109) |
|      |       |        |         |         |         |          |             |             |          |        |       |       |       |       |          | direct path write temp (15) |
| p031 | Set 1 |      2 |       4 |     492 |     349 |      143 |             |        0.00 |     0.00 |   6710 | 38313 |   4GB | 26391 |   5GB | -112.77% | direct path read temp (136) |
|      |       |        |         |         |         |          |             |             |          |        |       |       |       |       |          | direct path write temp (14) |

Here is an illustration I’ve been hanging on to for ages – one reason why you might get negative cell offload numbers – temp.

SQL Monitor uses something like the following for calculating cell offload:

Cell Offload = 100 * ((PHYSICAL_READ_BYTES + PHYSICAL_WRITE_BYTES) – IO INTERCONNECT_BYTES)/NULLIF((PHYSICAL_READ_BYTES + PHYSICAL_WRITE_BYTES),0)

Whilst the PHYSICAL_%_BYTES metrics are measured above the ASM level, ASM then mirrors any writes so these can be tripled for redundancy and counted in IO_INTERCONNECT_BYTES.

So actually it’s often a bit of an apples vs oranges metric…

Negative Offload