Why is CacheRead >> CacheReadIndInt + ... ?

[Former Member]

Further insight into the SQL Anywhere 16.0.0.2052 CacheRead* and DiskRead* database properties is requested; in particular:

Why is the database property CacheRead SO VERY MUCH larger than the sum of CacheReadIndInt, CacheReadIndLeaf, CacheReadTable and CacheReadWorkTable?

SELECT CAST ( DB_PROPERTY ( 'CacheRead' )          AS INTEGER ) AS CacheRead,
       CacheReadIndInt + CacheReadIndLeaf 
          + CacheReadTable + CacheReadWorkTable                 AS CacheReadPlua,
       CAST ( DB_PROPERTY ( 'CacheReadIndInt' )    AS INTEGER ) AS CacheReadIndInt,
       CAST ( DB_PROPERTY ( 'CacheReadIndLeaf' )   AS INTEGER ) AS CacheReadIndLeaf,
       CAST ( DB_PROPERTY ( 'CacheReadTable' )     AS INTEGER ) AS CacheReadTable,
       CAST ( DB_PROPERTY ( 'CacheReadWorkTable' ) AS INTEGER ) AS CacheReadWorkTable;

  CacheRead CacheReadPlua CacheReadIndInt CacheReadIndLeaf CacheReadTable CacheReadWorkTable 
----------- ------------- --------------- ---------------- -------------- ------------------ 
   27863526      11558306         3030368          3116314        5406258               5366 

Why is DiskRead database property is more-or-less-the-same as "DiskReadSome" (sum of DiskReadIndInt, DiskReadIndLeaf, DiskReadTable and DiskReadWorkTable) but does not appear to include DiskReadHintPages (included in "DiskReadAll")?

SELECT CAST ( DB_PROPERTY ( 'DiskRead' )          AS INTEGER ) AS DiskRead,
       DiskReadIndInt + DiskReadIndLeaf 
          + DiskReadTable + DiskReadWorkTable                  AS DiskReadSome,
       DiskReadIndInt + DiskReadIndLeaf 
          + DiskReadTable + DiskReadWorkTable 
          + DiskReadHintPages                                  AS DiskReadAll,
       CAST ( DB_PROPERTY ( 'DiskReadIndInt' )    AS INTEGER ) AS DiskReadIndInt,
       CAST ( DB_PROPERTY ( 'DiskReadIndLeaf' )   AS INTEGER ) AS DiskReadIndLeaf,
       CAST ( DB_PROPERTY ( 'DiskReadTable' )     AS INTEGER ) AS DiskReadTable,
       CAST ( DB_PROPERTY ( 'DiskReadWorkTable' ) AS INTEGER ) AS DiskReadWorkTable,
       CAST ( DB_PROPERTY ( 'DiskReadHintPages' ) AS INTEGER ) AS DiskReadHintPages;

   DiskRead DiskReadSome DiskReadAll DiskReadIndInt DiskReadIndLeaf DiskReadTable DiskReadWorkTable DiskReadHintPages 
----------- ------------ ----------- -------------- --------------- ------------- ----------------- ----------------- 
        662          672        1197             23             262           387                 0               525 

[Former Member]

This is my current understanding . . . HTH . . . I am sure others would know more about this.

As for Disk reads, the so-called 'Hint' pages are not strictly a separate category. Many of the disk reads (table or index) will have been 'prefetched' as hint pages and hopefully many of those would be encompassed by the counters for those. Think of them as a speculative prefetch. 'HintPages' are read using the power of prediction (as best that the engine can do without the aid of The Great Carnac ). Hint reads can become excess reads if they fail in their power to be predictive and then can be additive to the DiskRead* counters. {at least as I understand the process} The 'total' DiskRead may be less when there are some misses.

{of course, I am interpreting somewhat here}

My understanding about cache reads is even less informed but I usually see CacheReads and CacheHits being much larger than the other 4 counters. I would assume that is due to the many non-dbspace and non-worktable page types contributing to that. Heap pages, hash pages, transaction log, checkpoint log, rollback log, and many other page types are probably contributing. I suspect blob pages and bitmap pages are also a factor . . .

I have not tried to extrapolate from the other counters related to those other types to see if the sum can be made whole or not ...

HTH