Class %SYS.PTools.SQLStats

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

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:

SELECT RoutineName, ModuleName, ModuleCount, GlobalRefs, LinesOfCode, TotalTime, RowCount, QueryType, QueryText

FROM %SYS_PTools.SQLStatsView

WHERE Namespace= 'SAMPLES'

or for a more condensed output:

SELECT RoutineName, QueryText,

count(*) as RunCount,

{fn round(avg(RowCount),2)} as AvgRows,

{fn round(avg(GlobalRefs),2)} as AvgGlorefs,

{fn round(avg(LinesOfCode),2)} as AvgLines,

{fn round(avg(TotalTime),5)} as AvgTime

FROM %SYS_PTools.SQLStatsView

WHERE Namespace= 'SAMPLES'

GROUP BY RoutineName, CursorName

These classes store data in globals that default to the %SYS NameSpace: ^%SYS.PTools.SQLQueryD, ^%SYS.PTools.SQLQueryC, and ^%SYS.PTools.SQLQueryI. If you are going to run this on a large system for some time you might want to create global mapping to move these globals out of the CACHESYS database into a different one.

Error information is stored in ^%SYS.PTools.SQLQuery("Error",$j)

Inventory

Parameters	Properties	Methods	Queries	Indices	ForeignKeys	Triggers
	2	12	1	1

Summary

Properties
ChildSub	Counter	ExeName	GlobalRefs
IPAddress	LinesOfCode	MachineName	ModuleCount
ModuleName	RoutineInfo	RowCount	SQLQueryPointer
StartTime	TimeToFirstRow	TotalTime	UserName

Methods
%%OIDGet	%AddToSaveSet	%AddToSyncSet	%BMEBuilt
%BuildIndices	%CheckUnique	%ClassIsLatestVersion	%ClassName
%ComposeOid	%ConstructClone	%Delete	%DeleteExtent
%DeleteId	%DispatchClassMethod	%DispatchGetModified	%DispatchGetProperty
%DispatchMethod	%DispatchSetModified	%DispatchSetMultidimProperty	%DispatchSetProperty
%Exists	%ExistsId	%Extends	%GUID
%GUIDSet	%GetLock	%GetParameter	%GetSwizzleObject
%Id	%InsertBatch	%IsA	%IsModified
%KillExtent	%KillExtentData	%LoadFromMemory	%LockExtent
%LockId	%New	%NormalizeObject	%ObjectModified
%Oid	%OnBeforeAddToSync	%OnDetermineClass	%Open
%OpenId	%OriginalNamespace	%PackageName	%PhysicalAddress
%PurgeIndices	%Reload	%RemoveFromSaveSet	%ResolveConcurrencyConflict
%RollBack	%Save	%SaveDirect	%SaveIndices
%SerializeObject	%SetModified	%SortBegin	%SortEnd
%SyncObjectIn	%SyncTransport	%UnlockExtent	%UnlockId
%ValidateObject	Export	GetLastSQLStats	GetStats
GlobalSave	Init	LogHeader	LogSave
Purge	Report	SetSQLStatsJob	Start
Stop	mac

Properties

• property ChildSub as %Integer [ InitialExpression = $I(^%SYS.PTools.SQLQueryC("SQLStatsPointer")) ];

• relationship SQLQueryPointer as %SYS.PTools.SQLQuery [ Inverse = SQLStatsPointer,Cardinality = parent ];

Methods

• classmethod Export(FileName="") as %String

This method is used to export data from the %SYS.PTools.SQLStats class to a comma delimited text file. Caché SQL Manager.

• classmethod GetLastSQLStats() as %Integer [ SQLProc ]

This method will return the SQLStats from the last query called. If 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) 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, Counter, rows)

This is the method that stores all of the runtime data in the %SYS_PTools.SQLStats table Caché SQL Manager.

• 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)

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 ]

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 all NameSpaces RoutineName - the name of the Routine that you want to delete Stats for, should be used with NameSpace, default is all routines

• 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. Caché SQL Manager.

• classmethod SetSQLStatsJob(Flag As %Integer = 0) as %Integer [ SQLProc ]

• 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,
S.StartTime, S.Counter, S.ModuleName, S.ModuleCount,
S.GlobalRefs, S.LinesOfCode, S.TotalTime, S.TimeToFirstRow, S.RowCount,
S.UserName, S.ExeName, S.MachineName, S.IPAddress, Q.QueryType, Q.QueryText
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. Caché SQL Manager.

Indices

• index (MasterIndex on Counter,ChildSub) [IdKey,Unique];


[BASEXML] > [%SYS] > [PTools] > [SQLStats]		Private Storage