Index: src/pager.c
==================================================================
--- src/pager.c
+++ src/pager.c
@@ -668,12 +668,12 @@
i64 journalSizeLimit; /* Size limit for persistent journal files */
char *zFilename; /* Name of the database file */
char *zJournal; /* Name of the journal file */
int (*xBusyHandler)(void*); /* Function to call when busy */
void *pBusyHandlerArg; /* Context argument for xBusyHandler */
+ int nHit, nMiss; /* Total cache hits and misses */
#ifdef SQLITE_TEST
- int nHit, nMiss; /* Cache hits and missing */
int nRead, nWrite; /* Database pages read/written */
#endif
void (*xReiniter)(DbPage*); /* Call this routine when reloading pages */
#ifdef SQLITE_HAS_CODEC
void *(*xCodec)(void*,void*,Pgno,int); /* Routine for en/decoding data */
@@ -4167,11 +4167,11 @@
** The doNotSpill flag inhibits all cache spilling regardless of whether
** or not a sync is required. This is set during a rollback.
**
** Spilling is also prohibited when in an error state since that could
** lead to database corruption. In the current implementaton it
- ** is impossible for sqlite3PCacheFetch() to be called with createFlag==1
+ ** is impossible for sqlite3PcacheFetch() to be called with createFlag==1
** while in the error state, hence it is impossible for this routine to
** be called in the error state. Nevertheless, we include a NEVER()
** test for the error state as a safeguard against future changes.
*/
if( NEVER(pPager->errCode) ) return SQLITE_OK;
@@ -5003,18 +5003,17 @@
if( (*ppPage)->pPager && !noContent ){
/* In this case the pcache already contains an initialized copy of
** the page. Return without further ado. */
assert( pgno<=PAGER_MAX_PGNO && pgno!=PAGER_MJ_PGNO(pPager) );
- PAGER_INCR(pPager->nHit);
+ pPager->nHit++;
return SQLITE_OK;
}else{
/* The pager cache has created a new page. Its content needs to
** be initialized. */
- PAGER_INCR(pPager->nMiss);
pPg = *ppPage;
pPg->pPager = pPager;
/* The maximum page number is 2^31. Return SQLITE_CORRUPT if a page
** number greater than this, or the unused locking-page, is requested. */
@@ -5046,10 +5045,11 @@
}
memset(pPg->pData, 0, pPager->pageSize);
IOTRACE(("ZERO %p %d\n", pPager, pgno));
}else{
assert( pPg->pPager==pPager );
+ pPager->nMiss++;
rc = readDbPage(pPg);
if( rc!=SQLITE_OK ){
goto pager_acquire_err;
}
}
@@ -6079,10 +6079,21 @@
a[9] = pPager->nRead;
a[10] = pPager->nWrite;
return a;
}
#endif
+
+/*
+** This function is used to access the cache hit/miss counts maintained
+** by the Pager object. Before returning, *pnHit is incremented by the
+** total number of cache-hits that have occurred since the pager was
+** created, and *pnMiss is incremented by the total number of misses.
+*/
+void sqlite3PagerCacheStats(Pager *pPager, int *pnHit, int *pnMiss){
+ *pnHit += pPager->nHit;
+ *pnMiss += pPager->nMiss;
+}
/*
** Return true if this is an in-memory pager.
*/
int sqlite3PagerIsMemdb(Pager *pPager){
Index: src/pager.h
==================================================================
--- src/pager.h
+++ src/pager.h
@@ -153,10 +153,11 @@
sqlite3_file *sqlite3PagerFile(Pager*);
const char *sqlite3PagerJournalname(Pager*);
int sqlite3PagerNosync(Pager*);
void *sqlite3PagerTempSpace(Pager*);
int sqlite3PagerIsMemdb(Pager*);
+void sqlite3PagerCacheStats(Pager *, int *, int *);
/* Functions used to truncate the database file. */
void sqlite3PagerTruncateImage(Pager*,Pgno);
#if defined(SQLITE_HAS_CODEC) && !defined(SQLITE_OMIT_WAL)
Index: src/sqlite.h.in
==================================================================
--- src/sqlite.h.in
+++ src/sqlite.h.in
@@ -5808,20 +5808,34 @@
** This parameter returns the approximate number of of bytes of heap
** and lookaside memory used by all prepared statements associated with
** the database connection.)^
** ^The highwater mark associated with SQLITE_DBSTATUS_STMT_USED is always 0.
**
+**
+** [[SQLITE_DBSTATUS_CACHE_HIT]] ^(SQLITE_DBSTATUS_CACHE_HIT
+** This parameter returns the number of pager cache hits that have
+** occurred. ^The highwater mark associated with SQLITE_DBSTATUS_CACHE_HIT
+** is always 0.
+**
+**
+** [[SQLITE_DBSTATUS_CACHE_MISS]] ^(SQLITE_DBSTATUS_CACHE_MISS
+** This parameter returns the number of pager cache misses that have
+** occurred. ^The highwater mark associated with SQLITE_DBSTATUS_CACHE_MISS
+** is always 0.
+**
**
*/
#define SQLITE_DBSTATUS_LOOKASIDE_USED 0
#define SQLITE_DBSTATUS_CACHE_USED 1
#define SQLITE_DBSTATUS_SCHEMA_USED 2
#define SQLITE_DBSTATUS_STMT_USED 3
#define SQLITE_DBSTATUS_LOOKASIDE_HIT 4
#define SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE 5
#define SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL 6
-#define SQLITE_DBSTATUS_MAX 6 /* Largest defined DBSTATUS */
+#define SQLITE_DBSTATUS_CACHE_HIT 7
+#define SQLITE_DBSTATUS_CACHE_MISS 8
+#define SQLITE_DBSTATUS_MAX 8 /* Largest defined DBSTATUS */
/*
** CAPI3REF: Prepared Statement Status
**
@@ -5872,15 +5886,25 @@
** were created automatically in order to help joins run faster.
** A non-zero value in this counter may indicate an opportunity to
** improvement performance by adding permanent indices that do not
** need to be reinitialized each time the statement is run.
**
+** [[SQLITE_STMTSTATUS_CACHE_HIT]] SQLITE_STMTSTATUS_CACHE_HIT
+** ^This is the number of pager cache hits encountered during execution of
+** the statement.
+**
+** [[SQLITE_STMTSTATUS_CACHE_MISS]] SQLITE_STMTSTATUS_CACHE_MISS
+** ^This is the number of pager cache misses encountered during execution
+** of the statement.
+**
**
*/
#define SQLITE_STMTSTATUS_FULLSCAN_STEP 1
#define SQLITE_STMTSTATUS_SORT 2
#define SQLITE_STMTSTATUS_AUTOINDEX 3
+#define SQLITE_STMTSTATUS_CACHE_HIT 4
+#define SQLITE_STMTSTATUS_CACHE_MISS 5
/*
** CAPI3REF: Custom Page Cache Object
**
** The sqlite3_pcache type is opaque. It is implemented by
Index: src/sqliteInt.h
==================================================================
--- src/sqliteInt.h
+++ src/sqliteInt.h
@@ -891,10 +891,11 @@
int nSavepoint; /* Number of non-transaction savepoints */
int nStatement; /* Number of nested statement-transactions */
u8 isTransactionSavepoint; /* True if the outermost savepoint is a TS */
i64 nDeferredCons; /* Net deferred constraints this transaction. */
int *pnBytesFreed; /* If not NULL, increment this in DbFree() */
+ int aHitMiss[2]; /* DBSTATUS_CACHEHIT and CACHEMISS stats */
#ifdef SQLITE_ENABLE_UNLOCK_NOTIFY
/* The following variables are all protected by the STATIC_MASTER
** mutex, not by sqlite3.mutex. They are used by code in notify.c.
**
Index: src/status.c
==================================================================
--- src/status.c
+++ src/status.c
@@ -215,10 +215,26 @@
*pHighwater = 0;
*pCurrent = nByte;
break;
}
+
+ /*
+ ** Set *pCurrent to the total cache hits or misses encountered by the
+ ** database connection since the last reset. *pHighwater is always set to
+ ** zero.
+ */
+ case SQLITE_DBSTATUS_CACHE_HIT:
+ case SQLITE_DBSTATUS_CACHE_MISS: {
+ assert( SQLITE_DBSTATUS_CACHE_MISS==SQLITE_DBSTATUS_CACHE_HIT+1 );
+ *pHighwater = 0;
+ *pCurrent = db->aHitMiss[op-SQLITE_DBSTATUS_CACHE_HIT];
+ if( resetFlag ){
+ db->aHitMiss[op-SQLITE_DBSTATUS_CACHE_HIT] = 0;
+ }
+ break;
+ }
default: {
rc = SQLITE_ERROR;
}
}
Index: src/test1.c
==================================================================
--- src/test1.c
+++ src/test1.c
@@ -2248,10 +2248,12 @@
int op;
} aOp[] = {
{ "SQLITE_STMTSTATUS_FULLSCAN_STEP", SQLITE_STMTSTATUS_FULLSCAN_STEP },
{ "SQLITE_STMTSTATUS_SORT", SQLITE_STMTSTATUS_SORT },
{ "SQLITE_STMTSTATUS_AUTOINDEX", SQLITE_STMTSTATUS_AUTOINDEX },
+ { "SQLITE_STMTSTATUS_CACHE_HIT", SQLITE_STMTSTATUS_CACHE_HIT },
+ { "SQLITE_STMTSTATUS_CACHE_MISS", SQLITE_STMTSTATUS_CACHE_MISS },
};
if( objc!=4 ){
Tcl_WrongNumArgs(interp, 1, objv, "STMT PARAMETER RESETFLAG");
return TCL_ERROR;
}
Index: src/test_malloc.c
==================================================================
--- src/test_malloc.c
+++ src/test_malloc.c
@@ -1323,15 +1323,17 @@
{ "CACHE_USED", SQLITE_DBSTATUS_CACHE_USED },
{ "SCHEMA_USED", SQLITE_DBSTATUS_SCHEMA_USED },
{ "STMT_USED", SQLITE_DBSTATUS_STMT_USED },
{ "LOOKASIDE_HIT", SQLITE_DBSTATUS_LOOKASIDE_HIT },
{ "LOOKASIDE_MISS_SIZE", SQLITE_DBSTATUS_LOOKASIDE_MISS_SIZE },
- { "LOOKASIDE_MISS_FULL", SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL }
+ { "LOOKASIDE_MISS_FULL", SQLITE_DBSTATUS_LOOKASIDE_MISS_FULL },
+ { "CACHE_HIT", SQLITE_DBSTATUS_CACHE_HIT },
+ { "CACHE_MISS", SQLITE_DBSTATUS_CACHE_MISS }
};
Tcl_Obj *pResult;
if( objc!=4 ){
- Tcl_WrongNumArgs(interp, 1, objv, "PARAMETER RESETFLAG");
+ Tcl_WrongNumArgs(interp, 1, objv, "DB PARAMETER RESETFLAG");
return TCL_ERROR;
}
if( getDbPointer(interp, Tcl_GetString(objv[1]), &db) ) return TCL_ERROR;
zOpName = Tcl_GetString(objv[2]);
if( memcmp(zOpName, "SQLITE_", 7)==0 ) zOpName += 7;
Index: src/vdbe.c
==================================================================
--- src/vdbe.c
+++ src/vdbe.c
@@ -516,10 +516,31 @@
p->zErrMsg = sqlite3DbStrDup(db, pVtab->zErrMsg);
sqlite3_free(pVtab->zErrMsg);
pVtab->zErrMsg = 0;
}
+/*
+** Call sqlite3PagerCacheStats() on all database pagers used by the VM
+** passed as the first argument, incrementing *pnHit and *pnMiss for
+** with each call.
+*/
+static void vdbeCacheStats(Vdbe *p, int *pnHit, int *pnMiss){
+ int i;
+ yDbMask mask;
+ sqlite3 *db;
+ Db *aDb;
+ int nDb;
+ if( p->lockMask==0 ) return; /* The common case */
+ db = p->db;
+ aDb = db->aDb;
+ nDb = db->nDb;
+ for(i=0, mask=1; ilockMask)!=0 && ALWAYS(aDb[i].pBt!=0) ){
+ sqlite3PagerCacheStats(sqlite3BtreePager(aDb[i].pBt), pnHit, pnMiss);
+ }
+ }
+}
/*
** Execute as much of a VDBE program as we can then return.
**
** sqlite3VdbeMakeReady() must be called before this routine in order to
@@ -574,14 +595,17 @@
i64 lastRowid = db->lastRowid; /* Saved value of the last insert ROWID */
#ifdef VDBE_PROFILE
u64 start; /* CPU clock count at start of opcode */
int origPc; /* Program counter at start of opcode */
#endif
+ int nHit = 0; /* Cache hits for this call */
+ int nMiss = 0; /* Cache misses for this call */
/*** INSERT STACK UNION HERE ***/
assert( p->magic==VDBE_MAGIC_RUN ); /* sqlite3_step() verifies this */
sqlite3VdbeEnter(p);
+ vdbeCacheStats(p, &nHit, &nMiss);
if( p->rc==SQLITE_NOMEM ){
/* This happens if a malloc() inside a call to sqlite3_column_text() or
** sqlite3_column_text16() failed. */
goto no_mem;
}
@@ -6110,10 +6134,20 @@
/* This is the only way out of this procedure. We have to
** release the mutexes on btrees that were acquired at the
** top. */
vdbe_return:
db->lastRowid = lastRowid;
+
+ /* Update the statement and database cache hit/miss statistics. */
+ nHit = -nHit;
+ nMiss = -nMiss;
+ vdbeCacheStats(p, &nHit, &nMiss);
+ p->aCounter[SQLITE_STMTSTATUS_CACHE_HIT-1] += nHit;
+ p->aCounter[SQLITE_STMTSTATUS_CACHE_MISS-1] += nMiss;
+ db->aHitMiss[0] += nHit;
+ db->aHitMiss[1] += nMiss;
+
sqlite3VdbeLeave(p);
return rc;
/* Jump to here if a string or blob larger than SQLITE_MAX_LENGTH
** is encountered.
Index: src/vdbeInt.h
==================================================================
--- src/vdbeInt.h
+++ src/vdbeInt.h
@@ -308,11 +308,11 @@
u8 isPrepareV2; /* True if prepared with prepare_v2() */
int nChange; /* Number of db changes made since last reset */
yDbMask btreeMask; /* Bitmask of db->aDb[] entries referenced */
yDbMask lockMask; /* Subset of btreeMask that requires a lock */
int iStatement; /* Statement number (or 0 if has not opened stmt) */
- int aCounter[3]; /* Counters used by sqlite3_stmt_status() */
+ int aCounter[5]; /* Counters used by sqlite3_stmt_status() */
#ifndef SQLITE_OMIT_TRACE
i64 startTime; /* Time when query started - used for profiling */
#endif
i64 nFkConstraint; /* Number of imm. FK constraints this VM */
i64 nStmtDefCons; /* Number of def. constraints when stmt started */
Index: src/vdbeblob.c
==================================================================
--- src/vdbeblob.c
+++ src/vdbeblob.c
@@ -13,10 +13,11 @@
** This file contains code used to implement incremental BLOB I/O.
*/
#include "sqliteInt.h"
#include "vdbeInt.h"
+#include "btreeInt.h"
#ifndef SQLITE_OMIT_INCRBLOB
/*
** Valid sqlite3_blob* handles point to Incrblob structures.
@@ -382,13 +383,21 @@
rc = SQLITE_ABORT;
}else{
/* Call either BtreeData() or BtreePutData(). If SQLITE_ABORT is
** returned, clean-up the statement handle.
*/
+ Pager *pPager = p->pCsr->pBt->pPager;
+ int nHit = 0;
+ int nMiss = 0;
+
assert( db == v->db );
sqlite3BtreeEnterCursor(p->pCsr);
+ sqlite3PagerCacheStats(pPager, &nHit, &nMiss);
rc = xCall(p->pCsr, iOffset+p->iOffset, n, z);
+ db->aHitMiss[0] -= nHit;
+ db->aHitMiss[1] -= nMiss;
+ sqlite3PagerCacheStats(pPager, &db->aHitMiss[0], &db->aHitMiss[1]);
sqlite3BtreeLeaveCursor(p->pCsr);
if( rc==SQLITE_ABORT ){
sqlite3VdbeFinalize(v);
p->pStmt = 0;
}else{
ADDED test/stmtstatus.test
Index: test/stmtstatus.test
==================================================================
--- /dev/null
+++ test/stmtstatus.test
@@ -0,0 +1,79 @@
+# 2011 September 20
+#
+# The author disclaims copyright to this source code. In place of
+# a legal notice, here is a blessing:
+#
+# May you do good and not evil.
+# May you find forgiveness for yourself and forgive others.
+# May you share freely, never taking more than you give.
+#
+#***********************************************************************
+#
+# Tests for the sqlite3_stmt_status() function
+#
+
+set testdir [file dirname $argv0]
+source $testdir/tester.tcl
+
+set ::testprefix stmtstatus
+
+do_execsql_test 1.0 {
+ PRAGMA page_size = 1024;
+ PRAGMA auto_vacuum = 0;
+
+ CREATE TABLE t1(a PRIMARY KEY, b);
+ INSERT INTO t1 VALUES(1, randomblob(600));
+ INSERT INTO t1 VALUES(2, randomblob(600));
+ INSERT INTO t1 VALUES(3, randomblob(600));
+}
+
+proc stmt_hit_miss {stmt {reset 0}} {
+ list [sqlite3_stmt_status $stmt SQLITE_STMTSTATUS_CACHE_HIT $reset] \
+ [sqlite3_stmt_status $stmt SQLITE_STMTSTATUS_CACHE_MISS $reset]
+}
+
+do_test 1.1 {
+ db close
+ sqlite3 db test.db
+ expr {[file size test.db] / 1024}
+} 6
+
+do_test 1.2 {
+ set ::stmt [sqlite3_prepare_v2 db "SELECT b FROM t1 WHERE a=2" -1 dummy]
+ stmt_hit_miss $::stmt
+} {0 0}
+
+breakpoint
+do_test 1.3 {
+ sqlite3_step $::stmt
+ sqlite3_reset $::stmt
+} SQLITE_OK
+do_test 1.4 { stmt_hit_miss $::stmt } {1 3}
+do_test 1.5 {
+ sqlite3_step $::stmt
+ sqlite3_reset $::stmt
+} SQLITE_OK
+do_test 1.6 { stmt_hit_miss $::stmt } {5 3}
+do_test 1.7 { stmt_hit_miss $::stmt 0 } {5 3}
+do_test 1.8 { stmt_hit_miss $::stmt 1 } {5 3}
+do_test 1.9 { stmt_hit_miss $::stmt 0 } {0 0}
+do_test 1.10 { sqlite3_finalize $::stmt } SQLITE_OK
+
+do_test 1.11 { sqlite3_db_status db CACHE_HIT 0 } {0 6 0}
+do_test 1.12 { sqlite3_db_status db CACHE_MISS 0 } {0 3 0}
+do_test 1.13 { sqlite3_db_status db CACHE_HIT 1 } {0 6 0}
+do_test 1.14 { sqlite3_db_status db CACHE_MISS 1 } {0 3 0}
+do_test 1.15 { sqlite3_db_status db CACHE_HIT 0 } {0 0 0}
+do_test 1.16 { sqlite3_db_status db CACHE_MISS 0 } {0 0 0}
+
+do_test 1.17 {
+ set fd [db incrblob main t1 b 1]
+ set len [string length [read $fd]]
+ close $fd
+ set len
+} 600
+do_test 1.18 { sqlite3_db_status db CACHE_HIT 0 } {0 2 0}
+do_test 1.19 { sqlite3_db_status db CACHE_MISS 0 } {0 1 0}
+
+
+finish_test