/ Check-in [b0b0c8f8]
Login

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

Overview
Comment:Add the SQLITE_STMTSTATUS_REPREPARE and SQLITE_STMTSTATUS_RUN options to sqlite3_stmt_status(). Use this for two new columns in the stmts virtual table.
Downloads: Tarball | ZIP archive | SQL archive
Timelines: family | ancestors | descendants | both | stmts-vtab
Files: files | file ages | folders
SHA3-256: b0b0c8f8d548ef78584ab714ab120b01c1b83fc0d8ae2fd7626b970bab9fca58
User & Date: drh 2017-06-29 12:49:18
Context
2017-06-29
13:13
Add the "stmts" virtual table to the amalgamation, activated when compiled using -DSQLITE_ENABLE_STMTSVTAB. Add the SQLITE_STMTSTATUS_REPREPARE and SQLITE_STMTSTATUS_RUN statistics outputs from sqlite3_stmt_status() and add corresponding columns to the stmts virtual table. Change the numeric value of SQLITE_STMTSTATUS_MEMUSED to get it out of the way of counter values. check-in: 88976ae3 user: drh tags: trunk
12:49
Add the SQLITE_STMTSTATUS_REPREPARE and SQLITE_STMTSTATUS_RUN options to sqlite3_stmt_status(). Use this for two new columns in the stmts virtual table. Closed-Leaf check-in: b0b0c8f8 user: drh tags: stmts-vtab
2017-06-28
15:47
Build the "stmts" virtual table into the amalgamation. It is active only when compiled using SQLITE_ENABLE_STMTSVTAB. That option is supplied to the command-line shell. check-in: 0ff057d8 user: drh tags: stmts-vtab
Changes
Hide Diffs Side-by-Side Diffs Ignore Whitespace Patch

Changes to ext/misc/stmts.c.

    88     88     sqlite3_vtab **ppVtab,
    89     89     char **pzErr
    90     90   ){
    91     91     stmts_vtab *pNew;
    92     92     int rc;
    93     93   
    94     94   /* Column numbers */
    95         -#define STMTS_COLUMN_PTR   0    /* Numeric value of the statement pointer */
    96         -#define STMTS_COLUMN_SQL   1    /* SQL for the statement */
    97         -#define STMTS_COLUMN_NCOL  2    /* Number of result columns */
    98         -#define STMTS_COLUMN_RO    3    /* True if read-only */
    99         -#define STMTS_COLUMN_BUSY  4    /* True if currently busy */
   100         -#define STMTS_COLUMN_NSCAN 5    /* SQLITE_STMTSTATUS_FULLSCAN_STEP */
   101         -#define STMTS_COLUMN_NSORT 6    /* SQLITE_STMTSTATUS_SORT */
   102         -#define STMTS_COLUMN_NAIDX 7    /* SQLITE_STMTSTATUS_AUTOINDEX */
   103         -#define STMTS_COLUMN_NSTEP 8    /* SQLITE_STMTSTATUS_VM_STEP */
   104         -#define STMTS_COLUMN_MEM   9    /* SQLITE_STMTSTATUS_MEMUSED */
           95  +#define STMTS_COLUMN_PTR     0   /* Numeric value of the statement pointer */
           96  +#define STMTS_COLUMN_SQL     1   /* SQL for the statement */
           97  +#define STMTS_COLUMN_NCOL    2   /* Number of result columns */
           98  +#define STMTS_COLUMN_RO      3   /* True if read-only */
           99  +#define STMTS_COLUMN_BUSY    4   /* True if currently busy */
          100  +#define STMTS_COLUMN_NSCAN   5   /* SQLITE_STMTSTATUS_FULLSCAN_STEP */
          101  +#define STMTS_COLUMN_NSORT   6   /* SQLITE_STMTSTATUS_SORT */
          102  +#define STMTS_COLUMN_NAIDX   7   /* SQLITE_STMTSTATUS_AUTOINDEX */
          103  +#define STMTS_COLUMN_NSTEP   8   /* SQLITE_STMTSTATUS_VM_STEP */
          104  +#define STMTS_COLUMN_REPREP  9   /* SQLITE_STMTSTATUS_REPREPARE */
          105  +#define STMTS_COLUMN_RUN    10   /* SQLITE_STMTSTATUS_RUN */
          106  +#define STMTS_COLUMN_MEM    11   /* SQLITE_STMTSTATUS_MEMUSED */
   105    107   
   106    108   
   107    109     rc = sqlite3_declare_vtab(db,
   108         -     "CREATE TABLE x(ptr,sql,ncol,ro,busy,nscan,nsort,naidx,nstep,mem)");
          110  +     "CREATE TABLE x(ptr,sql,ncol,ro,busy,nscan,nsort,naidx,nstep,"
          111  +                    "reprep,run,mem)");
   109    112     if( rc==SQLITE_OK ){
   110    113       pNew = sqlite3_malloc( sizeof(*pNew) );
   111    114       *ppVtab = (sqlite3_vtab*)pNew;
   112    115       if( pNew==0 ) return SQLITE_NOMEM;
   113    116       memset(pNew, 0, sizeof(*pNew));
   114    117       pNew->db = db;
   115    118     }
................................................................................
   182    185       case STMTS_COLUMN_RO: {
   183    186         sqlite3_result_int(ctx, sqlite3_stmt_readonly(pCur->pStmt));
   184    187         break;
   185    188       }
   186    189       case STMTS_COLUMN_BUSY: {
   187    190         sqlite3_result_int(ctx, sqlite3_stmt_busy(pCur->pStmt));
   188    191         break;
          192  +    }
          193  +    case STMTS_COLUMN_MEM: {
          194  +      i = SQLITE_STMTSTATUS_MEMUSED + 
          195  +            STMTS_COLUMN_NSCAN - SQLITE_STMTSTATUS_FULLSCAN_STEP;
          196  +      /* Fall thru */
   189    197       }
   190    198       case STMTS_COLUMN_NSCAN:
   191    199       case STMTS_COLUMN_NSORT:
   192    200       case STMTS_COLUMN_NAIDX:
   193    201       case STMTS_COLUMN_NSTEP:
   194         -    case STMTS_COLUMN_MEM: {
          202  +    case STMTS_COLUMN_REPREP:
          203  +    case STMTS_COLUMN_RUN: {
   195    204         sqlite3_result_int(ctx, sqlite3_stmt_status(pCur->pStmt,
   196    205                         i-STMTS_COLUMN_NSCAN+SQLITE_STMTSTATUS_FULLSCAN_STEP, 0));
   197    206         break;
   198    207       }
   199    208     }
   200    209     return SQLITE_OK;
   201    210   }

Changes to src/sqlite.h.in.

  7143   7143   ** [[SQLITE_STMTSTATUS_VM_STEP]] <dt>SQLITE_STMTSTATUS_VM_STEP</dt>
  7144   7144   ** <dd>^This is the number of virtual machine operations executed
  7145   7145   ** by the prepared statement if that number is less than or equal
  7146   7146   ** to 2147483647.  The number of virtual machine operations can be 
  7147   7147   ** used as a proxy for the total work done by the prepared statement.
  7148   7148   ** If the number of virtual machine operations exceeds 2147483647
  7149   7149   ** then the value returned by this statement status code is undefined.
         7150  +**
         7151  +** [[SQLITE_STMTSTATUS_REPREPARE]] <dt>SQLITE_STMTSTATUS_REPREPARE</dt>
         7152  +** <dd>^This is the number of times that the prepare statement has been
         7153  +** automatically regenerated due to schema changes or change to 
         7154  +** [bound parameters] that might affect the query plan.
         7155  +**
         7156  +** [[SQLITE_STMTSTATUS_RUN]] <dt>SQLITE_STMTSTATUS_RUN</dt>
         7157  +** <dd>^This is the number of times that the prepared statement has
         7158  +** been run.  A single "run" for the purposes of this counter is one
         7159  +** or more calls to [sqlite3_step()] followed by a call to [sqlite3_reset()].
         7160  +** The counter is incremented on the first [sqlite3_step()] call of each
         7161  +** cycle.
  7150   7162   **
  7151   7163   ** [[SQLITE_STMTSTATUS_MEMUSED]] <dt>SQLITE_STMTSTATUS_MEMUSED</dt>
  7152   7164   ** <dd>^This is the approximate number of bytes of heap memory
  7153   7165   ** used to store the prepared statement.  ^This value is not actually
  7154   7166   ** a counter, and so the resetFlg parameter to sqlite3_stmt_status()
  7155   7167   ** is ignored when the opcode is SQLITE_STMTSTATUS_MEMUSED.
  7156   7168   ** </dd>
  7157   7169   ** </dl>
  7158   7170   */
  7159   7171   #define SQLITE_STMTSTATUS_FULLSCAN_STEP     1
  7160   7172   #define SQLITE_STMTSTATUS_SORT              2
  7161   7173   #define SQLITE_STMTSTATUS_AUTOINDEX         3
  7162   7174   #define SQLITE_STMTSTATUS_VM_STEP           4
  7163         -#define SQLITE_STMTSTATUS_MEMUSED           5
         7175  +#define SQLITE_STMTSTATUS_REPREPARE         5
         7176  +#define SQLITE_STMTSTATUS_RUN               6
         7177  +#define SQLITE_STMTSTATUS_MEMUSED           99
  7164   7178   
  7165   7179   /*
  7166   7180   ** CAPI3REF: Custom Page Cache Object
  7167   7181   **
  7168   7182   ** The sqlite3_pcache type is opaque.  It is implemented by
  7169   7183   ** the pluggable module.  The SQLite core has no knowledge of
  7170   7184   ** its size or internal structure and never deals with the

Changes to src/test1.c.

  2135   2135       const char *zName;
  2136   2136       int op;
  2137   2137     } aOp[] = {
  2138   2138       { "SQLITE_STMTSTATUS_FULLSCAN_STEP",   SQLITE_STMTSTATUS_FULLSCAN_STEP   },
  2139   2139       { "SQLITE_STMTSTATUS_SORT",            SQLITE_STMTSTATUS_SORT            },
  2140   2140       { "SQLITE_STMTSTATUS_AUTOINDEX",       SQLITE_STMTSTATUS_AUTOINDEX       },
  2141   2141       { "SQLITE_STMTSTATUS_VM_STEP",         SQLITE_STMTSTATUS_VM_STEP         },
         2142  +    { "SQLITE_STMTSTATUS_REPREPARE",       SQLITE_STMTSTATUS_REPREPARE       },
         2143  +    { "SQLITE_STMTSTATUS_RUN",             SQLITE_STMTSTATUS_RUN             },
         2144  +    { "SQLITE_STMTSTATUS_MEMUSED",         SQLITE_STMTSTATUS_MEMUSED         },
  2142   2145     };
  2143   2146     if( objc!=4 ){
  2144   2147       Tcl_WrongNumArgs(interp, 1, objv, "STMT PARAMETER RESETFLAG");
  2145   2148       return TCL_ERROR;
  2146   2149     }
  2147   2150     if( getStmtPointer(interp, Tcl_GetString(objv[1]), &pStmt) ) return TCL_ERROR;
  2148   2151     zOpName = Tcl_GetString(objv[2]);

Changes to src/vdbe.c.

  7075   7075     if( pOp->p1>=sqlite3GlobalConfig.iOnceResetThreshold ){
  7076   7076       for(i=1; i<p->nOp; i++){
  7077   7077         if( p->aOp[i].opcode==OP_Once ) p->aOp[i].p1 = 0;
  7078   7078       }
  7079   7079       pOp->p1 = 0;
  7080   7080     }
  7081   7081     pOp->p1++;
         7082  +  p->aCounter[SQLITE_STMTSTATUS_RUN]++;
  7082   7083     goto jump_to_p2;
  7083   7084   }
  7084   7085   
  7085   7086   #ifdef SQLITE_ENABLE_CURSOR_HINTS
  7086   7087   /* Opcode: CursorHint P1 * * P4 *
  7087   7088   **
  7088   7089   ** Provide a hint to cursor P1 that it only needs to return rows that

Changes to src/vdbeInt.h.

   389    389     bft runOnlyOnce:1;      /* Automatically expire on reset */
   390    390     bft usesStmtJournal:1;  /* True if uses a statement journal */
   391    391     bft readOnly:1;         /* True for statements that do not write */
   392    392     bft bIsReader:1;        /* True for statements that read */
   393    393     bft isPrepareV2:1;      /* True if prepared with prepare_v2() */
   394    394     yDbMask btreeMask;      /* Bitmask of db->aDb[] entries referenced */
   395    395     yDbMask lockMask;       /* Subset of btreeMask that requires a lock */
   396         -  u32 aCounter[5];        /* Counters used by sqlite3_stmt_status() */
          396  +  u32 aCounter[7];        /* Counters used by sqlite3_stmt_status() */
   397    397     char *zSql;             /* Text of the SQL statement that generated this */
   398    398     void *pFree;            /* Free this when deleting the vdbe */
   399    399     VdbeFrame *pFrame;      /* Parent frame */
   400    400     VdbeFrame *pDelFrame;   /* List of frame objects to free on VM reset */
   401    401     int nFrame;             /* Number of frames in pFrame list */
   402    402     u32 expmask;            /* Binding to these vars invalidates VM */
   403    403     SubProgram *pProgram;   /* Linked list of all sub-programs used by VM */

Changes to src/vdbeaux.c.

    83     83     pA->pPrev = pB->pPrev;
    84     84     pB->pPrev = pTmp;
    85     85     zTmp = pA->zSql;
    86     86     pA->zSql = pB->zSql;
    87     87     pB->zSql = zTmp;
    88     88     pB->isPrepareV2 = pA->isPrepareV2;
    89     89     pB->expmask = pA->expmask;
           90  +  memcpy(pB->aCounter, pA->aCounter, sizeof(pB->aCounter));
           91  +  pB->aCounter[SQLITE_STMTSTATUS_REPREPARE]++;
           92  +
    90     93   }
    91     94   
    92     95   /*
    93     96   ** Resize the Vdbe.aOp array so that it is at least nOp elements larger 
    94     97   ** than its current size. nOp is guaranteed to be less than or equal
    95     98   ** to 1024/sizeof(Op).
    96     99   **