%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:

• 0 = Turn off Stats code generation
• 1 = Turn on Stats code generation but do NOT gather stats
• 2 = Turn on Stats code generation to gather stats at the Open and Close of a query
• 3 = Turn on Stats code generation to gather stats at each Module level of a query

The Stats gathered are:

• Number of Global References
• Number of Commands Executed
• Number of Times a Module is called
• Total Time in a Module
• Number of Rows returned by the query

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:

DO $SYSTEM.SQL.SetSQLStats(0)

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
  &SQL(DECLARE cur1 CURSOR FOR
  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
  &SQL(DECLARE cur2 CURSOR FOR
  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

• ChildSub
• Counter
• SQLQueryPointer

Method Inventory

• Export()
• ExportAll()
• GetLastSQLStats()
• GetStats()
• GlobalSave()
• ImportSchema()
• Init()
• LogHeader()
• Purge()
• QueryText()
• Report()
• SetSQLStatsJob()
• Start()
• Stop()
• mac()

Properties

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: SQLQueryPointerGet(), SQLQueryPointerGetObject(), SQLQueryPointerGetObjectId(), SQLQueryPointerGetStored(), SQLQueryPointerGetSwizzled(), SQLQueryPointerIsValid(), SQLQueryPointerNewObject(), SQLQueryPointerOnDelete(), SQLQueryPointerRClose(), SQLQueryPointerRExec(), SQLQueryPointerRFetch(), SQLQueryPointerRelate(), SQLQueryPointerSQLCompute(), SQLQueryPointerSet(), SQLQueryPointerSetObject(), SQLQueryPointerSetObjectId(), SQLQueryPointerUnRelate(), SQLQueryPointerUnSwizzle()

Methods

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

##class(%SYS.PTools.SQLStats).Export(FileName,delim)

FileName

Path and name of the file you want to export to.

delim

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)

##class(%SYS.PTools.SQLStats).ExportAll(FilePrefix,delim,ExportPlan)

FilePrefix

Path and name of the file you want to export to.

delim

Default value $c(9) (tab) Delimiter for the columns of the export file.

ExportPlan

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 ]

Projected as the stored procedure: 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 ]

Projected as the stored procedure: 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 ]

Projected as the stored procedure: 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)

Queries

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

Indexes

index (MasterIndex on Counter,ChildSub) [IdKey, Type = key, Unique];

Index methods: MasterIndexCheck(), MasterIndexDelete(), MasterIndexExists(), MasterIndexOpen(), MasterIndexSQLCheckUnique(), MasterIndexSQLExists(), MasterIndexSQLFindPKeyByConstraint(), MasterIndexSQLFindRowIDByConstraint()