This is documentation for Caché & Ensemble.

For information on converting to InterSystems IRIS, see the InterSystems IRIS Adoption Guide and the InterSystems IRIS In-Place Conversion Guide, both available on the WRC Distributions page (login required).

Home > Class Reference > %SYS namespace > %SYS.PTools.SQLStats


persistent class %SYS.PTools.SQLStats extends %Library.Persistent, %SYS.PTools.Stats

SQL Table Name: %SYS_PTools.SQLStats

This class can be used to gather statistical information on SQL queries run on a give Caché system.

You can invoke different levels of the Stats gathering with the following command:

DO $SYSTEM.SQL.SetSQLStats(flag)

Where flag can be:

The Stats gathered are:

It is also possible to invoke Stats gathering for just the current process. That command is:

DO $SYSTEM.SQL.SetSQLStatsJob(flag)

The modules can be nested in one another. The outer module will display inclusive numbers, so the Module MAIN will be the overall results for the full query.

Upon first enabling SQLStats you should Purge Cached Queries in order to force code regeneration. When you stop gathering Stats using the following command:


You do not need to Purge Cached Queries. The small number of additional lines of code should not affect performance.

All of the data is stored in %SYS.PTools.SQLQuery and %SYS.PTools.SQLStats. SQLQuery holds the text of the SQL Statement, the routine name, and the module info. SQLStats holds the stats for each run.

You can query the tables from any directory on the system or use xDBC.

A view, %SYS_PTools.SQLStatsView, has been defined to make looking at the data easier.

For Example:

  #include %msql
  SELECT RoutineName, ModuleName, ModuleCount, GlobalRefs, DiskWait
         LinesOfCode, TotalTime, RowCount, QueryType, QueryText 
  FROM %SYS_PTools.SQLStatsView 
  WHERE Namespace= 'SAMPLES')
  &SQL(OPEN cur1)
  For  &SQL(FETCH cur1 INTO :A,:B,:C,:D,:E,:F,:G,:H,:I) QUIT:SQLCODE'=0  WRITE !,A,"  ",B,"  ",C,"  ",D,"  ",E,"  ",F,"  ",G,"  ",H,"  ",I
  &SQL(CLOSE cur1)

or for a more condensed output:

  #include %msql
  SELECT  V.RoutineName, V.QueryText,
          (SELECT COUNT(*) 
  		 FROM %SYS_PTools.SQLStatsView Sub
  		 WHERE Sub.ModuleName = 'Main' AND Sub.RoutineName = V.RoutineName 
  		 GROUP BY Sub.CursorName, Sub.ModuleName) as RunCount, 
          {fn round(avg(V.RowCount),2)} as AvgRows,  
          {fn round(avg(V.GlobalRefs),2)} as AvgGlorefs,    
          {fn round(avg(V.LinesOfCode),2)} as AvgLines, 
          {fn round(avg(V.DiskWait),2)} as AvgDiskWait,  
         {fn round(avg(V.TotalTime),5)} as AvgTime 
  FROM %SYS_PTools.SQLStatsView V
  GROUP BY V.RoutineName, V.CursorName)
  &SQL(OPEN cur2)
  For  &SQL(FETCH cur2 INTO :A,:B,:C,:D,:E,:F,:G) QUIT:SQLCODE'=0  WRITE !,A,"  ",B,"  ",C,"  ",D,"  ",E,"  ",F,"  ",G
  &SQL(CLOSE cur2)

Error information is stored in ^%sqlcq(NAMESPACE,"PTools","Error",$J).

Property Inventory

Method Inventory


property ChildSub as %Integer [ InitialExpression = $I(^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStatsC")) , Required ];
Property methods: ChildSubDisplayToLogical(), ChildSubGet(), ChildSubGetStored(), ChildSubIsValid(), ChildSubLogicalToDisplay(), ChildSubNormalize(), ChildSubSet()
property Counter as %Integer [ Required ];
This is the number of times the query has been run since the last compile.
Property methods: CounterDisplayToLogical(), CounterGet(), CounterGetStored(), CounterIsValid(), CounterLogicalToDisplay(), CounterNormalize(), CounterSet()
relationship SQLQueryPointer as %SYS.PTools.SQLQuery [ Required , Inverse = SQLStatsPointer , Cardinality = parent ];
Property methods: SQLQueryPointerCheck(), SQLQueryPointerDelete(), SQLQueryPointerGet(), SQLQueryPointerGetObject(), SQLQueryPointerGetObjectId(), SQLQueryPointerGetStored(), SQLQueryPointerGetSwizzled(), SQLQueryPointerIsValid(), SQLQueryPointerNewObject(), SQLQueryPointerOnDelete(), SQLQueryPointerRClose(), SQLQueryPointerRExec(), SQLQueryPointerRFetch(), SQLQueryPointerRelate(), SQLQueryPointerSQLCompute(), SQLQueryPointerSet(), SQLQueryPointerSetObject(), SQLQueryPointerSetObjectId(), SQLQueryPointerUnRelate(), SQLQueryPointerUnSwizzle()


classmethod Export(FileName="", delim="") as %String


Path and name of the file you want to export to.
Default value $c(9) (tab) Delimiter for the columns of the export file.
This method is used to export data from the %SYS.PTools.SQLStats class to a delimited text file.
classmethod ExportAll(FilePrefix="", delim="", ExportPlan=0)


Path and name of the file you want to export to.
Default value $c(9) (tab) Delimiter for the columns of the export file.
Optional. Default value = 0 if 1 then the SQL Show Plan will be included as part of the export
This method is used to export data from %SYS.PTools.SQLQuery and %SYS.PTools.SQLStats classes to 2 delimited text file.
classmethod GetLastSQLStats() as %Integer [ SQLProc = GetLastSQLStats ]
This method will return the SQLStats from the last query called. It makes use of a local variable that gets set when the stats data is saved. This only works if you are running in the same process. From terminal to see the data you can call d %sqlcontext.DumpResults()
classmethod GetStats(ByRef sql As %String, Mod As %Integer, showstats As %Integer = 0) as %String
This method returns the Stats for a given module of a given SQL statement It is called from the Show Plan code when Stats are to be included
classmethod GlobalSave(ns, rou, cur, mod, rows)
This is the method that stores all of the runtime data in the %SYS_PTools.SQLStats table
classmethod ImportSchema(CN As %String, Rtn As %String, Internal As %Boolean = 0) as %String
classmethod Init(NS As %String, Rou As %String, Cur As %String, ByRef Parms As %ArrayOfDataTypes)
This method is called once from the cursor open code, initializes local variables, and called Start() for the MAIN loop.
classmethod LogHeader(File, version, delim)
Inherited description: This is the header row that will be in the output file. The columns should be comma delimited
classmethod Purge(NameSpace As %String = "", RoutineName As %String = "") as %Status [ SQLProc = Purge ]
This method is called to remove data from the %SYS.PTools.SQLStats table. It does not remove data from %SYS_PTools.SQLQuery, those rows are cleaned up when a query is compiled it takes two parameters: NameSpace - The NameSpace that you want to delete stats from, default is the current NameSpaces RoutineName - the name of the Routine that you want to delete Stats for, should be used with NameSpace, default is all routines
classmethod QueryText(CN As %String, Rtn As %String, Internal As %Boolean = 0) as %String
classmethod Report(NS As %String, Rou As %String, Cur As %String, RowCount As %Integer = 0)
This method is called from the cursor close code. It is called once for each query run. It calls the GlobalSave method that adds data to the %SYS_PTools.SQLStats table.
classmethod SetSQLStatsJob(Flag As %Integer = 0) as %Integer [ SQLProc = SetSQLStatsJob ]
classmethod Start(NS As %String, Rou As %String, Cur As %String, ModString As %String)
This method is called every time you enter a module.
classmethod Stop(NS As %String, Rou As %String, Cur As %String, ModString As %String)
This method is called every time you exit a module.
classmethod mac(line As %String, rtn As %String)


query SQLStatsView()
SQL Query as view "SQLStatsView":
SELECT S.ID, Q.NameSpace, Q.RoutineName, Q.CursorName, Q.CompileTime, Q.ImportSchema, S.StartTime, S.Counter, S.ModuleName, S.ModuleCount, S.GlobalRefs, S.LinesOfCode, S.DiskWait, S.TotalTime, S.TimeToFirstRow, S.RowCount, S.UserName, S.ExeName, S.MachineName, S.IPAddress, S.Pid, Q.QueryType, Q.QueryText, Q.Details, Q.RunCount FROM %SYS_PTools.SQLQuery Q LEFT OUTER JOIN %SYS_PTools.SQLStats S ON Q.ID = S.SQLQueryPointer
This query def joins %SYS_PTools.SQLQuery and %SYS.PTools.SQLStats to make SQL reporting on the data easier.
query ViewStats(NameSpace As %String)
Selects RoutineName As %String, QueryText As %String, RunCount As %Integer, AvgRows As %Integer, AvgGlobalRefs As %Integer, AvgDiskWait As %Integer, AvgCommands As %Integer, AvgTime As %Integer, Details As %Integer, CursorName As %Integer, RoutineCursor As %Integer, ImportSchema As %String
SQL Query:
SELECT Main.RoutineName, Main.QueryText, RunCount, {fn round(avg(Main.RowCount),2)} as AvgRows, {fn round(avg(Main.GlobalRefs),2)} as AvgGlorefs, {fn round(avg(Main.DiskWait),2)} as AvgDiskWait, {fn round(avg(Main.LinesOfCode),2)} as AvgCommands, {fn round(avg(Main.TotalTime),5)} as AvgTime, Details, CursorName,RoutineName||'^'||CursorName, ImportSchema FROM %SYS_PTools.SQLStatsView Main WHERE Main.Namespace= :NameSpace AND RoutineName <> 'source lines' AND ModuleName = 'Main' GROUP BY Main.RoutineName, Main.CursorName
query ViewStatsDetails(CursorName As %String, RoutineName As %String)
Selects QueryText As %String, RunCount As %Integer, AvgRows As %Integer, AvgGlobalRefs As %Integer, AvgDiskWait As %Integer, AvgCommands As %Integer, AvgTime As %Integer, ModuleName As %String, AvgModCount As %Integer
SQL Query:
SELECT QueryText, RunCount, {fn round(avg(RowCount),2)} as AvgRows, {fn round(avg(GlobalRefs),2)} as AvgGlorefs, {fn round(avg(DiskWait),2)} as AvgDiskWait, {fn round(avg(LinesOfCode),2)} as AvgCommands, {fn round(avg(TotalTime),5)} as AvgTime, ModuleName, {fn round(avg(ModuleCount),2)} as AvgModCount FROM %SYS_PTools.SQLStatsView WHERE CursorName= :CursorName AND RoutineName = :RoutineName GROUP BY CursorName, ModuleName


index (MasterIndex on Counter,ChildSub) [IdKey, Type = key, Unique];
Index methods: MasterIndexCheck(), MasterIndexDelete(), MasterIndexExists(), MasterIndexOpen(), MasterIndexSQLCheckUnique(), MasterIndexSQLExists(), MasterIndexSQLFindPKeyByConstraint(), MasterIndexSQLFindRowIDByConstraint()

Inherited Members

Inherited Properties

Inherited Methods


Storage Model: CacheStorage (%SYS.PTools.SQLStats)