/ Check-in [89272357]
Login

Many hyperlinks are disabled.
Use anonymous login to enable hyperlinks.

Overview
Comment:Add SQLITE_STMTSTATUS_CACHE_HIT/MISS and SQLITE_DB_STATUS_CACHE_HIT/MISS. For querying the number of pager cache hits and misses on a statement or connection basis.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | cache-stats
Files: files | file ages | folders
SHA1: 892723575c033ed3f02cf6ea61e08f039b9a0c40
User & Date: dan 2011-09-20 15:53:02
Context
2011-09-22
03:13
Merge the cache-stats enhancement into trunk. Closed-Leaf check-in: 646db971 user: drh tags: mistake
2011-09-20
15:53
Add SQLITE_STMTSTATUS_CACHE_HIT/MISS and SQLITE_DB_STATUS_CACHE_HIT/MISS. For querying the number of pager cache hits and misses on a statement or connection basis. Closed-Leaf check-in: 89272357 user: dan tags: cache-stats
2011-09-19
20:56
Minor comment change in the description of the different memory allocator options. No changes to code. check-in: 36be31ff user: drh tags: trunk
Changes
Hide Diffs Side-by-Side Diffs Show Whitespace Changes Patch

Changes to src/pager.c.

   666    666     int pageSize;               /* Number of bytes in a page */
   667    667     Pgno mxPgno;                /* Maximum allowed size of the database */
   668    668     i64 journalSizeLimit;       /* Size limit for persistent journal files */
   669    669     char *zFilename;            /* Name of the database file */
   670    670     char *zJournal;             /* Name of the journal file */
   671    671     int (*xBusyHandler)(void*); /* Function to call when busy */
   672    672     void *pBusyHandlerArg;      /* Context argument for xBusyHandler */
          673  +  int nHit, nMiss;            /* Total cache hits and misses */
   673    674   #ifdef SQLITE_TEST
   674         -  int nHit, nMiss;            /* Cache hits and missing */
   675    675     int nRead, nWrite;          /* Database pages read/written */
   676    676   #endif
   677    677     void (*xReiniter)(DbPage*); /* Call this routine when reloading pages */
   678    678   #ifdef SQLITE_HAS_CODEC
   679    679     void *(*xCodec)(void*,void*,Pgno,int); /* Routine for en/decoding data */
   680    680     void (*xCodecSizeChng)(void*,int,int); /* Notify of page size changes */
   681    681     void (*xCodecFree)(void*);             /* Destructor for the codec */
................................................................................
  4165   4165     ** pages belonging to the same sector.
  4166   4166     **
  4167   4167     ** The doNotSpill flag inhibits all cache spilling regardless of whether
  4168   4168     ** or not a sync is required.  This is set during a rollback.
  4169   4169     **
  4170   4170     ** Spilling is also prohibited when in an error state since that could
  4171   4171     ** lead to database corruption.   In the current implementaton it 
  4172         -  ** is impossible for sqlite3PCacheFetch() to be called with createFlag==1
         4172  +  ** is impossible for sqlite3PcacheFetch() to be called with createFlag==1
  4173   4173     ** while in the error state, hence it is impossible for this routine to
  4174   4174     ** be called in the error state.  Nevertheless, we include a NEVER()
  4175   4175     ** test for the error state as a safeguard against future changes.
  4176   4176     */
  4177   4177     if( NEVER(pPager->errCode) ) return SQLITE_OK;
  4178   4178     if( pPager->doNotSpill ) return SQLITE_OK;
  4179   4179     if( pPager->doNotSyncSpill && (pPg->flags & PGHDR_NEED_SYNC)!=0 ){
................................................................................
  5001   5001     assert( (*ppPage)->pgno==pgno );
  5002   5002     assert( (*ppPage)->pPager==pPager || (*ppPage)->pPager==0 );
  5003   5003   
  5004   5004     if( (*ppPage)->pPager && !noContent ){
  5005   5005       /* In this case the pcache already contains an initialized copy of
  5006   5006       ** the page. Return without further ado.  */
  5007   5007       assert( pgno<=PAGER_MAX_PGNO && pgno!=PAGER_MJ_PGNO(pPager) );
  5008         -    PAGER_INCR(pPager->nHit);
         5008  +    pPager->nHit++;
  5009   5009       return SQLITE_OK;
  5010   5010   
  5011   5011     }else{
  5012   5012       /* The pager cache has created a new page. Its content needs to 
  5013   5013       ** be initialized.  */
  5014   5014   
  5015         -    PAGER_INCR(pPager->nMiss);
  5016   5015       pPg = *ppPage;
  5017   5016       pPg->pPager = pPager;
  5018   5017   
  5019   5018       /* The maximum page number is 2^31. Return SQLITE_CORRUPT if a page
  5020   5019       ** number greater than this, or the unused locking-page, is requested. */
  5021   5020       if( pgno>PAGER_MAX_PGNO || pgno==PAGER_MJ_PGNO(pPager) ){
  5022   5021         rc = SQLITE_CORRUPT_BKPT;
................................................................................
  5044   5043           testcase( rc==SQLITE_NOMEM );
  5045   5044           sqlite3EndBenignMalloc();
  5046   5045         }
  5047   5046         memset(pPg->pData, 0, pPager->pageSize);
  5048   5047         IOTRACE(("ZERO %p %d\n", pPager, pgno));
  5049   5048       }else{
  5050   5049         assert( pPg->pPager==pPager );
         5050  +      pPager->nMiss++;
  5051   5051         rc = readDbPage(pPg);
  5052   5052         if( rc!=SQLITE_OK ){
  5053   5053           goto pager_acquire_err;
  5054   5054         }
  5055   5055       }
  5056   5056       pager_set_pagehash(pPg);
  5057   5057     }
................................................................................
  6077   6077     a[7] = pPager->nMiss;
  6078   6078     a[8] = 0;  /* Used to be pPager->nOvfl */
  6079   6079     a[9] = pPager->nRead;
  6080   6080     a[10] = pPager->nWrite;
  6081   6081     return a;
  6082   6082   }
  6083   6083   #endif
         6084  +
         6085  +/*
         6086  +** This function is used to access the cache hit/miss counts maintained
         6087  +** by the Pager object. Before returning, *pnHit is incremented by the
         6088  +** total number of cache-hits that have occurred since the pager was 
         6089  +** created, and *pnMiss is incremented by the total number of misses.
         6090  +*/
         6091  +void sqlite3PagerCacheStats(Pager *pPager, int *pnHit, int *pnMiss){
         6092  +  *pnHit += pPager->nHit;
         6093  +  *pnMiss += pPager->nMiss;
         6094  +}
  6084   6095   
  6085   6096   /*
  6086   6097   ** Return true if this is an in-memory pager.
  6087   6098   */
  6088   6099   int sqlite3PagerIsMemdb(Pager *pPager){
  6089   6100     return MEMDB;
  6090   6101   }

Changes to src/pager.h.

   151    151   const char *sqlite3PagerFilename(Pager*);
   152    152   const sqlite3_vfs *sqlite3PagerVfs(Pager*);
   153    153   sqlite3_file *sqlite3PagerFile(Pager*);
   154    154   const char *sqlite3PagerJournalname(Pager*);
   155    155   int sqlite3PagerNosync(Pager*);
   156    156   void *sqlite3PagerTempSpace(Pager*);
   157    157   int sqlite3PagerIsMemdb(Pager*);
          158  +void sqlite3PagerCacheStats(Pager *, int *, int *);
   158    159   
   159    160   /* Functions used to truncate the database file. */
   160    161   void sqlite3PagerTruncateImage(Pager*,Pgno);
   161    162   
   162    163   #if defined(SQLITE_HAS_CODEC) && !defined(SQLITE_OMIT_WAL)
   163    164   void *sqlite3PagerCodec(DbPage *);
   164    165   #endif

Changes to src/sqlite.h.in.

  5806   5806   **
  5807   5807   ** [[SQLITE_DBSTATUS_STMT_USED]] ^(<dt>SQLITE_DBSTATUS_STMT_USED</dt>
  5808   5808   ** <dd>This parameter returns the approximate number of of bytes of heap
  5809   5809   ** and lookaside memory used by all prepared statements associated with
  5810   5810   ** the database connection.)^
  5811   5811   ** ^The highwater mark associated with SQLITE_DBSTATUS_STMT_USED is always 0.
  5812   5812   ** </dd>
         5813  +**
         5814  +** [[SQLITE_DBSTATUS_CACHE_HIT]] ^(<dt>SQLITE_DBSTATUS_CACHE_HIT</dt>
         5815  +** <dd>This parameter returns the number of pager cache hits that have
         5816  +** occurred. ^The highwater mark associated with SQLITE_DBSTATUS_CACHE_HIT 
         5817  +** is always 0.
         5818  +** </dd>
         5819  +**
         5820  +** [[SQLITE_DBSTATUS_CACHE_MISS]] ^(<dt>SQLITE_DBSTATUS_CACHE_MISS</dt>
         5821  +** <dd>This parameter returns the number of pager cache misses that have
         5822  +** occurred. ^The highwater mark associated with SQLITE_DBSTATUS_CACHE_MISS 
         5823  +** is always 0.
         5824  +** </dd>
  5813   5825   ** </dl>
  5814   5826   */
  5815   5827   #define SQLITE_DBSTATUS_LOOKASIDE_USED       0
  5816   5828   #define SQLITE_DBSTATUS_CACHE_USED           1
  5817   5829   #define SQLITE_DBSTATUS_SCHEMA_USED          2
  5818   5830   #define SQLITE_DBSTATUS_STMT_USED            3
  5819   5831   #define SQLITE_DBSTATUS_LOOKASIDE_HIT        4
  5820   5832   #define SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE  5
  5821   5833   #define SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL  6
  5822         -#define SQLITE_DBSTATUS_MAX                  6   /* Largest defined DBSTATUS */
         5834  +#define SQLITE_DBSTATUS_CACHE_HIT            7
         5835  +#define SQLITE_DBSTATUS_CACHE_MISS           8
         5836  +#define SQLITE_DBSTATUS_MAX                  8   /* Largest defined DBSTATUS */
  5823   5837   
  5824   5838   
  5825   5839   /*
  5826   5840   ** CAPI3REF: Prepared Statement Status
  5827   5841   **
  5828   5842   ** ^(Each prepared statement maintains various
  5829   5843   ** [SQLITE_STMTSTATUS counters] that measure the number
................................................................................
  5870   5884   ** [[SQLITE_STMTSTATUS_AUTOINDEX]] <dt>SQLITE_STMTSTATUS_AUTOINDEX</dt>
  5871   5885   ** <dd>^This is the number of rows inserted into transient indices that
  5872   5886   ** were created automatically in order to help joins run faster.
  5873   5887   ** A non-zero value in this counter may indicate an opportunity to
  5874   5888   ** improvement performance by adding permanent indices that do not
  5875   5889   ** need to be reinitialized each time the statement is run.</dd>
  5876   5890   **
         5891  +** [[SQLITE_STMTSTATUS_CACHE_HIT]] <dt>SQLITE_STMTSTATUS_CACHE_HIT</dt>
         5892  +** <dd>^This is the number of pager cache hits encountered during execution of
         5893  +** the statement.</dd>
         5894  +**
         5895  +** [[SQLITE_STMTSTATUS_CACHE_MISS]] <dt>SQLITE_STMTSTATUS_CACHE_MISS</dt>
         5896  +** <dd>^This is the number of pager cache misses encountered during execution 
         5897  +** of the statement.</dd>
         5898  +**
  5877   5899   ** </dl>
  5878   5900   */
  5879   5901   #define SQLITE_STMTSTATUS_FULLSCAN_STEP     1
  5880   5902   #define SQLITE_STMTSTATUS_SORT              2
  5881   5903   #define SQLITE_STMTSTATUS_AUTOINDEX         3
         5904  +#define SQLITE_STMTSTATUS_CACHE_HIT         4
         5905  +#define SQLITE_STMTSTATUS_CACHE_MISS        5
  5882   5906   
  5883   5907   /*
  5884   5908   ** CAPI3REF: Custom Page Cache Object
  5885   5909   **
  5886   5910   ** The sqlite3_pcache type is opaque.  It is implemented by
  5887   5911   ** the pluggable module.  The SQLite core has no knowledge of
  5888   5912   ** its size or internal structure and never deals with the

Changes to src/sqliteInt.h.

   889    889     Db aDbStatic[2];              /* Static space for the 2 default backends */
   890    890     Savepoint *pSavepoint;        /* List of active savepoints */
   891    891     int nSavepoint;               /* Number of non-transaction savepoints */
   892    892     int nStatement;               /* Number of nested statement-transactions  */
   893    893     u8 isTransactionSavepoint;    /* True if the outermost savepoint is a TS */
   894    894     i64 nDeferredCons;            /* Net deferred constraints this transaction. */
   895    895     int *pnBytesFreed;            /* If not NULL, increment this in DbFree() */
          896  +  int aHitMiss[2];              /* DBSTATUS_CACHEHIT and CACHEMISS stats */
   896    897   
   897    898   #ifdef SQLITE_ENABLE_UNLOCK_NOTIFY
   898    899     /* The following variables are all protected by the STATIC_MASTER 
   899    900     ** mutex, not by sqlite3.mutex. They are used by code in notify.c. 
   900    901     **
   901    902     ** When X.pUnlockConnection==Y, that means that X is waiting for Y to
   902    903     ** unlock so that it can proceed.

Changes to src/status.c.

   213    213         db->pnBytesFreed = 0;
   214    214   
   215    215         *pHighwater = 0;
   216    216         *pCurrent = nByte;
   217    217   
   218    218         break;
   219    219       }
          220  +
          221  +    /*
          222  +    ** Set *pCurrent to the total cache hits or misses encountered by the
          223  +    ** database connection since the last reset. *pHighwater is always set to
          224  +    ** zero.
          225  +    */
          226  +    case SQLITE_DBSTATUS_CACHE_HIT:
          227  +    case SQLITE_DBSTATUS_CACHE_MISS: {
          228  +      assert( SQLITE_DBSTATUS_CACHE_MISS==SQLITE_DBSTATUS_CACHE_HIT+1 );
          229  +      *pHighwater = 0;
          230  +      *pCurrent = db->aHitMiss[op-SQLITE_DBSTATUS_CACHE_HIT];
          231  +      if( resetFlag ){
          232  +        db->aHitMiss[op-SQLITE_DBSTATUS_CACHE_HIT] = 0;
          233  +      }
          234  +      break;
          235  +    }
   220    236   
   221    237       default: {
   222    238         rc = SQLITE_ERROR;
   223    239       }
   224    240     }
   225    241     sqlite3_mutex_leave(db->mutex);
   226    242     return rc;
   227    243   }

Changes to src/test1.c.

  2246   2246     static const struct {
  2247   2247       const char *zName;
  2248   2248       int op;
  2249   2249     } aOp[] = {
  2250   2250       { "SQLITE_STMTSTATUS_FULLSCAN_STEP",   SQLITE_STMTSTATUS_FULLSCAN_STEP   },
  2251   2251       { "SQLITE_STMTSTATUS_SORT",            SQLITE_STMTSTATUS_SORT            },
  2252   2252       { "SQLITE_STMTSTATUS_AUTOINDEX",       SQLITE_STMTSTATUS_AUTOINDEX       },
         2253  +    { "SQLITE_STMTSTATUS_CACHE_HIT",       SQLITE_STMTSTATUS_CACHE_HIT       },
         2254  +    { "SQLITE_STMTSTATUS_CACHE_MISS",      SQLITE_STMTSTATUS_CACHE_MISS      },
  2253   2255     };
  2254   2256     if( objc!=4 ){
  2255   2257       Tcl_WrongNumArgs(interp, 1, objv, "STMT PARAMETER RESETFLAG");
  2256   2258       return TCL_ERROR;
  2257   2259     }
  2258   2260     if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
  2259   2261     zOpName = Tcl_GetString(objv[2]);

Changes to src/test_malloc.c.

  1321   1321     } aOp[] = {
  1322   1322       { "LOOKASIDE_USED",      SQLITE_DBSTATUS_LOOKASIDE_USED      },
  1323   1323       { "CACHE_USED",          SQLITE_DBSTATUS_CACHE_USED          },
  1324   1324       { "SCHEMA_USED",         SQLITE_DBSTATUS_SCHEMA_USED         },
  1325   1325       { "STMT_USED",           SQLITE_DBSTATUS_STMT_USED           },
  1326   1326       { "LOOKASIDE_HIT",       SQLITE_DBSTATUS_LOOKASIDE_HIT       },
  1327   1327       { "LOOKASIDE_MISS_SIZE", SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE },
  1328         -    { "LOOKASIDE_MISS_FULL", SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL }
         1328  +    { "LOOKASIDE_MISS_FULL", SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL },
         1329  +    { "CACHE_HIT",           SQLITE_DBSTATUS_CACHE_HIT           },
         1330  +    { "CACHE_MISS",          SQLITE_DBSTATUS_CACHE_MISS          }
  1329   1331     };
  1330   1332     Tcl_Obj *pResult;
  1331   1333     if( objc!=4 ){
  1332         -    Tcl_WrongNumArgs(interp, 1, objv, "PARAMETER RESETFLAG");
         1334  +    Tcl_WrongNumArgs(interp, 1, objv, "DB PARAMETER RESETFLAG");
  1333   1335       return TCL_ERROR;
  1334   1336     }
  1335   1337     if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
  1336   1338     zOpName = Tcl_GetString(objv[2]);
  1337   1339     if( memcmp(zOpName, "SQLITE_", 7)==0 ) zOpName += 7;
  1338   1340     if( memcmp(zOpName, "DBSTATUS_", 9)==0 ) zOpName += 9;
  1339   1341     for(i=0; i<ArraySize(aOp); i++){

Changes to src/vdbe.c.

   514    514     sqlite3 *db = p->db;
   515    515     sqlite3DbFree(db, p->zErrMsg);
   516    516     p->zErrMsg = sqlite3DbStrDup(db, pVtab->zErrMsg);
   517    517     sqlite3_free(pVtab->zErrMsg);
   518    518     pVtab->zErrMsg = 0;
   519    519   }
   520    520   
          521  +/*
          522  +** Call sqlite3PagerCacheStats() on all database pagers used by the VM
          523  +** passed as the first argument, incrementing *pnHit and *pnMiss for
          524  +** with each call.
          525  +*/
          526  +static void vdbeCacheStats(Vdbe *p, int *pnHit, int *pnMiss){
          527  +  int i;
          528  +  yDbMask mask;
          529  +  sqlite3 *db;
          530  +  Db *aDb;
          531  +  int nDb;
          532  +  if( p->lockMask==0 ) return;  /* The common case */
          533  +  db = p->db;
          534  +  aDb = db->aDb;
          535  +  nDb = db->nDb;
          536  +  for(i=0, mask=1; i<nDb; i++, mask += mask){
          537  +    if( i!=1 && (mask & p->lockMask)!=0 && ALWAYS(aDb[i].pBt!=0) ){
          538  +      sqlite3PagerCacheStats(sqlite3BtreePager(aDb[i].pBt), pnHit, pnMiss);
          539  +    }
          540  +  }
          541  +}
   521    542   
   522    543   /*
   523    544   ** Execute as much of a VDBE program as we can then return.
   524    545   **
   525    546   ** sqlite3VdbeMakeReady() must be called before this routine in order to
   526    547   ** close the program with a final OP_Halt and to set up the callbacks
   527    548   ** and the error message pointer.
................................................................................
   572    593     int iCompare = 0;          /* Result of last OP_Compare operation */
   573    594     int *aPermute = 0;         /* Permutation of columns for OP_Compare */
   574    595     i64 lastRowid = db->lastRowid;  /* Saved value of the last insert ROWID */
   575    596   #ifdef VDBE_PROFILE
   576    597     u64 start;                 /* CPU clock count at start of opcode */
   577    598     int origPc;                /* Program counter at start of opcode */
   578    599   #endif
          600  +  int nHit = 0;              /* Cache hits for this call */
          601  +  int nMiss = 0;             /* Cache misses for this call */
   579    602     /*** INSERT STACK UNION HERE ***/
   580    603   
   581    604     assert( p->magic==VDBE_MAGIC_RUN );  /* sqlite3_step() verifies this */
   582    605     sqlite3VdbeEnter(p);
          606  +  vdbeCacheStats(p, &nHit, &nMiss);
   583    607     if( p->rc==SQLITE_NOMEM ){
   584    608       /* This happens if a malloc() inside a call to sqlite3_column_text() or
   585    609       ** sqlite3_column_text16() failed.  */
   586    610       goto no_mem;
   587    611     }
   588    612     assert( p->rc==SQLITE_OK || p->rc==SQLITE_BUSY );
   589    613     p->rc = SQLITE_OK;
................................................................................
  6108   6132     }
  6109   6133   
  6110   6134     /* This is the only way out of this procedure.  We have to
  6111   6135     ** release the mutexes on btrees that were acquired at the
  6112   6136     ** top. */
  6113   6137   vdbe_return:
  6114   6138     db->lastRowid = lastRowid;
         6139  +
         6140  +  /* Update the statement and database cache hit/miss statistics. */
         6141  +  nHit  = -nHit;
         6142  +  nMiss = -nMiss;
         6143  +  vdbeCacheStats(p, &nHit, &nMiss);
         6144  +  p->aCounter[SQLITE_STMTSTATUS_CACHE_HIT-1] += nHit;
         6145  +  p->aCounter[SQLITE_STMTSTATUS_CACHE_MISS-1] += nMiss;
         6146  +  db->aHitMiss[0] += nHit;
         6147  +  db->aHitMiss[1] += nMiss;
         6148  +
  6115   6149     sqlite3VdbeLeave(p);
  6116   6150     return rc;
  6117   6151   
  6118   6152     /* Jump to here if a string or blob larger than SQLITE_MAX_LENGTH
  6119   6153     ** is encountered.
  6120   6154     */
  6121   6155   too_big:

Changes to src/vdbeInt.h.

   306    306     u8 usesStmtJournal;     /* True if uses a statement journal */
   307    307     u8 readOnly;            /* True for read-only statements */
   308    308     u8 isPrepareV2;         /* True if prepared with prepare_v2() */
   309    309     int nChange;            /* Number of db changes made since last reset */
   310    310     yDbMask btreeMask;      /* Bitmask of db->aDb[] entries referenced */
   311    311     yDbMask lockMask;       /* Subset of btreeMask that requires a lock */
   312    312     int iStatement;         /* Statement number (or 0 if has not opened stmt) */
   313         -  int aCounter[3];        /* Counters used by sqlite3_stmt_status() */
          313  +  int aCounter[5];        /* Counters used by sqlite3_stmt_status() */
   314    314   #ifndef SQLITE_OMIT_TRACE
   315    315     i64 startTime;          /* Time when query started - used for profiling */
   316    316   #endif
   317    317     i64 nFkConstraint;      /* Number of imm. FK constraints this VM */
   318    318     i64 nStmtDefCons;       /* Number of def. constraints when stmt started */
   319    319     char *zSql;             /* Text of the SQL statement that generated this */
   320    320     void *pFree;            /* Free this when deleting the vdbe */

Changes to src/vdbeblob.c.

    11     11   *************************************************************************
    12     12   **
    13     13   ** This file contains code used to implement incremental BLOB I/O.
    14     14   */
    15     15   
    16     16   #include "sqliteInt.h"
    17     17   #include "vdbeInt.h"
           18  +#include "btreeInt.h"
    18     19   
    19     20   #ifndef SQLITE_OMIT_INCRBLOB
    20     21   
    21     22   /*
    22     23   ** Valid sqlite3_blob* handles point to Incrblob structures.
    23     24   */
    24     25   typedef struct Incrblob Incrblob;
................................................................................
   380    381       ** already been invalidated. Return SQLITE_ABORT in this case.
   381    382       */
   382    383       rc = SQLITE_ABORT;
   383    384     }else{
   384    385       /* Call either BtreeData() or BtreePutData(). If SQLITE_ABORT is
   385    386       ** returned, clean-up the statement handle.
   386    387       */
          388  +    Pager *pPager = p->pCsr->pBt->pPager;
          389  +    int nHit = 0;
          390  +    int nMiss = 0;
          391  +
   387    392       assert( db == v->db );
   388    393       sqlite3BtreeEnterCursor(p->pCsr);
          394  +    sqlite3PagerCacheStats(pPager, &nHit, &nMiss);
   389    395       rc = xCall(p->pCsr, iOffset+p->iOffset, n, z);
          396  +    db->aHitMiss[0] -= nHit;
          397  +    db->aHitMiss[1] -= nMiss;
          398  +    sqlite3PagerCacheStats(pPager, &db->aHitMiss[0], &db->aHitMiss[1]);
   390    399       sqlite3BtreeLeaveCursor(p->pCsr);
   391    400       if( rc==SQLITE_ABORT ){
   392    401         sqlite3VdbeFinalize(v);
   393    402         p->pStmt = 0;
   394    403       }else{
   395    404         db->errCode = rc;
   396    405         v->rc = rc;

Added test/stmtstatus.test.

            1  +# 2011 September 20
            2  +#
            3  +# The author disclaims copyright to this source code.  In place of
            4  +# a legal notice, here is a blessing:
            5  +#
            6  +#    May you do good and not evil.
            7  +#    May you find forgiveness for yourself and forgive others.
            8  +#    May you share freely, never taking more than you give.
            9  +#
           10  +#***********************************************************************
           11  +#
           12  +# Tests for the sqlite3_stmt_status() function
           13  +#
           14  +
           15  +set testdir [file dirname $argv0]
           16  +source $testdir/tester.tcl
           17  +
           18  +set ::testprefix stmtstatus
           19  +
           20  +do_execsql_test 1.0 {
           21  +  PRAGMA page_size = 1024;
           22  +  PRAGMA auto_vacuum = 0;
           23  +
           24  +  CREATE TABLE t1(a PRIMARY KEY, b);
           25  +  INSERT INTO t1 VALUES(1, randomblob(600));
           26  +  INSERT INTO t1 VALUES(2, randomblob(600));
           27  +  INSERT INTO t1 VALUES(3, randomblob(600));
           28  +}
           29  +
           30  +proc stmt_hit_miss {stmt {reset 0}} {
           31  +  list [sqlite3_stmt_status $stmt SQLITE_STMTSTATUS_CACHE_HIT $reset]  \
           32  +       [sqlite3_stmt_status $stmt SQLITE_STMTSTATUS_CACHE_MISS $reset] 
           33  +}
           34  +
           35  +do_test 1.1 {
           36  +  db close
           37  +  sqlite3 db test.db
           38  +  expr {[file size test.db] / 1024}
           39  +} 6
           40  +
           41  +do_test 1.2 {
           42  +  set ::stmt [sqlite3_prepare_v2 db "SELECT b FROM t1 WHERE a=2" -1 dummy]
           43  +  stmt_hit_miss $::stmt
           44  +} {0 0}
           45  +
           46  +breakpoint
           47  +do_test 1.3 { 
           48  +  sqlite3_step $::stmt  
           49  +  sqlite3_reset $::stmt  
           50  +} SQLITE_OK
           51  +do_test 1.4 { stmt_hit_miss $::stmt } {1 3}
           52  +do_test 1.5 { 
           53  +  sqlite3_step $::stmt  
           54  +  sqlite3_reset $::stmt  
           55  +} SQLITE_OK
           56  +do_test 1.6 { stmt_hit_miss $::stmt } {5 3}
           57  +do_test 1.7 { stmt_hit_miss $::stmt 0 } {5 3}
           58  +do_test 1.8 { stmt_hit_miss $::stmt 1 } {5 3}
           59  +do_test 1.9 { stmt_hit_miss $::stmt 0 } {0 0}
           60  +do_test 1.10 { sqlite3_finalize $::stmt } SQLITE_OK
           61  +
           62  +do_test 1.11 { sqlite3_db_status db CACHE_HIT  0 } {0 6 0}
           63  +do_test 1.12 { sqlite3_db_status db CACHE_MISS 0 } {0 3 0}
           64  +do_test 1.13 { sqlite3_db_status db CACHE_HIT  1 } {0 6 0}
           65  +do_test 1.14 { sqlite3_db_status db CACHE_MISS 1 } {0 3 0}
           66  +do_test 1.15 { sqlite3_db_status db CACHE_HIT  0 } {0 0 0}
           67  +do_test 1.16 { sqlite3_db_status db CACHE_MISS 0 } {0 0 0}
           68  +
           69  +do_test 1.17 {
           70  +  set fd [db incrblob main t1 b 1]
           71  +  set len [string length [read $fd]]
           72  +  close $fd
           73  +  set len
           74  +} 600
           75  +do_test 1.18 { sqlite3_db_status db CACHE_HIT  0 } {0 2 0}
           76  +do_test 1.19 { sqlite3_db_status db CACHE_MISS 0 } {0 1 0}
           77  +
           78  + 
           79  +finish_test