Skip to main content

%SYS.PTools.SQLStats

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

SQL Table Name: %SYS_PTools.SQLStats

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Starting with ver '2.0' of the PTools application, this class is considered to be DEPRECATED and is replaced by the %SYS.PTools.StatsSQL class. This class is still usable under extreme circumstances when invoking the 'SetSQLStatsSaveFlag()' method and passing to the (saveType) parameter the following value: 2 = Original (backward-compatible) -- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --

Class: %SYS.PTools.SQLStats Replaced By: %SYS.PTools.StatsSQL Purpose: This class can be used to collect statistical information on SQL queries run on a give InterSystems IRIS system. 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. The following commands demonstrate how to perform these tasks: >DO $SYSTEM.SQL.Purge() >DO $SYSTEM.SQL.SetSQLStatsJob(3) NOTE: To see the definition and details on how to use these methods, either view this information in the Class Reference documentation for the %SYSTEM.SQL class, or review the help via the following method invocations: >DO $SYSTEM.SQL.Help("Purge") >DO $SYSTEM.SQL.Help("SetSQLStatsJob") When you're finished collectinging statistics, disable SQLStats by using the following command: >DO $SYSTEM.SQL.SetSQLStatsJob(0) NOTE: There is no need to Purge cached queries after disabling SQLStats, because the small number of additional code-lines generated should not affect query performance. All of the PTools statistical and query informational data is stored in the %SYS.PTools.SQLQuery and '%SYS.PTools.SQLStats' classes. The %SYS.PTools.SQLQuery class holds the text of the SQL Statement, the routine name, and the module information. The '%SYS.PTools.SQLStats' class, on the other hand, holds the statistical details for each run. You can query these tables from any directory on the system, or use xDBC to view this information. The view, SQLStatsView, has been defined to make looking at the pertinent data in these classes easier. For Example:

        #INCLUDE %msql
        &SQL(DECLARE cur1 CURSOR FOR
             SELECT RoutineName, ModuleName, ModuleCount, GlobalRefs, DiskWait
                    LinesOfCode, TimeSpent, 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.TimeSpent),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 Storage: ^%sqlcq($NAMESPACE,"PTools","Error"[...])=$LIST Info

Property Inventory

Method Inventory

Properties

property ChildSub as %Integer [ InitialExpression = $$$bumpSYSPToolsSQLStatsChildSub , 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

deprecated classmethod Export(file="", delim="", ByRef conds As %RawString, ByRef ptInfo As %RawString) as %String [ SQLProc = SQLStats_Export ]
Projected as the stored procedure: SQLStats_Export

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: Export [SQL: SQLStats_Export] Replaces By: Export [SQL: StatsSQL_Export] (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility. See exportSQLStats(...) Purpose: This method generates a delimited file containing the data from the '%SYS.PTools.SQLStats' class Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLStats).Export(...) SQL: CALL %SYS_PTools.SQLStats_Export(...) SELECT %SYS_PTools.SQLStats_Export(...) NOTE: This 'SqlProc' method can be invoked with either the CALL-interface or the SELECT-interface. Both interfaces execute the method: the CALL-interface does not return any resulting value, while the SELECT-interface does. Examples: The following examples shows the use of this method: #1 Display this export file to the screen via a InterSystems IRIS Terminal: set io=##class(%SYS.PTools.SQLStats).Export($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.SQLStats_Export('$IO') Where: '$IO' = Output to the current device NOTE: '$IO' can be omitted, as it is the default 'file' when invoked via the CALL interface Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}") Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.SQLStats class: (e.g. C:\exportFile.txt) NOTE: $IO = Outputs the data to the screen '$IO' = Output the data to the current device when invoked from the CALL/SELECT-interface '$DEF' = Ouput the data to the DEFAULT file when invoked from the CALL/SELECT-interface [DEFAULT: {MGR-Directory}\{computerName}_{ConfigName}_YYYYMMDD_HHMMSS_StatsSQL.psql] delim - The delimiter by which to delimit the data of the exported file [DEFAULT: $C(9) // TAB] conds - A string/array of possible conditions by which to restrict the output of the Export file, in the following format: conds= OR conds(0)=pos Count (WHERE pos = {1...n}) conds(pos)=$LIST() Pieces: 1) [] (Assumed Default: &&) 2) [{Heading}] (Omit for override cond) 3) | WHERE: := := {&& | ||} (&& = AND | || = OR) @* := @ := @ = Contains no references to {Heading} * = Contains no references to {*} EXAMPLE: conds="("",INFO,MAIN,""[("",""_{Module}_"",""))||({GlobalRefs}>20)" conds(0)=3 conds(1)=$LB(,"Module","=""INFO""") conds(2)=$LB("||","Module","=""MAIN""") conds(3)=$LB("||","GlobalRefs",">20") conds(0)=2 conds(1)=$LB(,"Module",""",INFO,MAIN,""[("",""_{*}_"","")") conds(2)=$LB("||","GlobalRefs","{*}>20") WHERE: {*} = value substitution for the 'Module' & 'GlobalRefs' fields NOTE: Conditions that don't contain any references to {Heading} fields, often called s, can be included as s, as in the following example: conds(pos)=$LB(,,"$g(^zAction(""runIt""))=1") NOTE: All conditions must be satisfied for the conds() to be considered true and for the row to be exported [PASS BY REFERENCE] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo(variable)=value ptInfo("outputFile")=The canonical name of the Export/Reporting file RETURN Value: The output locations of the exported data; Otherwise, return the error status if one occurred

deprecated classmethod ExportAll(filePrefix="", delim="", exportPlan=0, silent=0, rtnName="", modName="", ByRef conds As %RawString, ByRef ptInfo As %RawString) as %String [ SQLProc = SQLStats_ExportAll ]
Projected as the stored procedure: SQLStats_ExportAll

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: ExportAll [SQL: SQLStats_ExportAll] Replaced By: ExportAll [SQL: StatsSQL_ExportAll] (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility. See exportSQLStats(...) Purpose: This method generates two Performance Tool files containing the data from both the '%SYS.PTools.SQLQuery' & '%SYS.PTools.SQLStats' classes & return a $LIST of the output locations Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLStats).ExportAll(...) SQL: CALL %SYS_PTools.SQLStats_ExportAll(...) SELECT %SYS_PTools.SQLStats_ExportAll(...) NOTE: This 'SqlProc' method can be invoked with either the CALL-interface or the SELECT-interface. Both interfaces execute the method: the CALL-interface does not return any resulting value, while the SELECT-interface does. Examples: The following examples shows the use of this method: #1 Display this export file to the screen via a InterSystems IRIS Terminal: set io=##class(%SYS.PTools.SQLStats).ExportAll($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.SQLStats_ExportAll('$IO') Where: '$IO' = Output to the current device NOTE: '$IO' can be omitted, as it is the default 'file' when invoked via the CALL interface Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}") Parameters: filePrefix - The path in which to create and store the data from the '%SYS.PTools.SQLQuery' & '%SYS.PTools.SQLStats' classes (e.g. C:\) NOTE: $IO = Outputs the data to the screen '$IO' = Output the data to the current device when invoked from the CALL/SELECT-interface '$DEF' = Ouput the data to the DEFAULT file when invoked from the CALL/SELECT-interface [DEFAULT: {Current-Directory}\SQLQuery.{ext} & {Current-Directory}\StatsSQL.{ext}] WHERE {Current-Directory} = The directory of the Namespace in which this method is invoked (e.g. $ZU(12,"")) delim - The delimiter by which to delimit the data of the exported file [DEFAULT: $C(9) // TAB] exportPlan - 0 - Export the SQL Query Text 1 - Export the SQL Query Plan [DEFAULT: 0] silent - 0 - Display all messages during the running of this method 1 - Don't display any messages during the running of this method rtnName - If specified, return only the rows where the 'RoutineName' matches the value of this parameter; Otherwise, return rows for all 'RoutineName' values modName - If specified, return only the rows where the 'ModuleName' matches the value of this parameter; Otherwise, return rows for all 'ModuleName' values conds - A string/array of possible conditions by which to restrict the output of the Export file, in the following format: conds= OR conds(0)=pos Count (WHERE pos = {1...n}) conds(pos)=$LIST() Pieces: 1) [] (Assumed Default: &&) 2) [{Heading}] (Omit for override cond) 3) | WHERE: := := {&& | ||} (&& = AND | || = OR) @* := @ := @ = Contains no references to {Heading} * = Contains no references to {*} EXAMPLE: conds="("",INFO,MAIN,""[("",""_{Module}_"",""))||({GlobalRefs}>20)" conds(0)=3 conds(1)=$LB(,"Module","=""INFO""") conds(2)=$LB("||","Module","=""MAIN""") conds(3)=$LB("||","GlobalRefs",">20") conds(0)=2 conds(1)=$LB(,"Module",""",INFO,MAIN,""[("",""_{*}_"","")") conds(2)=$LB("||","GlobalRefs","{*}>20") WHERE: {*} = value substitution for the 'Module' & 'GlobalRefs' fields NOTE: Conditions that don't contain any references to {Heading} fields, often called s, can be included as s, as in the following example: conds(pos)=$LB(,,"$g(^zAction(""runIt""))=1") NOTE: All conditions must be satisfied for the conds() to be considered true and for the row to be exported [PASS BY REFERENCE] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo(method,variable)=value ptInfo(method,"outputFile")=The canonical name of the Export/Reporting file RETURN Value: A $LIST of the output locations of the exported data; Otherwise, return the error status if one occurred $LB(filePrefix_"SQLStats_Qry.txt",filePrefix_"SQLStats_Stats.txt")

deprecated classmethod GetLastSQLStats(dumpResults=0) as %Integer [ SQLProc = GetLastSQLStats ]
Projected as the stored procedure: GetLastSQLStats

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: GetLastSQLStats [SQL: GetLastSQLStats] Replaced By: GetLastSQLStats [SQL: StatsSQL_GetLastSQLStats] (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility Purpose: This method return the SQLStats Result Set from the last invoked SQL Statement NOTE: This method makes use of a local variable '%sqlcontext' that gets set when the stats data is saved. This only works if you are running in the same process. Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.StatsSQL).GetLastSQLStats(...) SQL: CALL %SYS_PTools.GetLastSQLStats(...) SELECT %SYS_PTools.GetLastSQLStats(...) NOTE: This 'SqlProc' method can be invoked with either the CALL-interface or the SELECT-interface. Both interfaces execute the method: the CALL-interface does not return any resulting value, while the SELECT-interface does. Examples: The following examples shows the use of this method: #1 To see the SQLStats Result Set from the last invoked SQL Statement, invoke one of the following methods: // First invoke a query with SQLStats turned on, such that the following // array value gets set: %SQLStats("LastRow") set tSC=##class(%SYS.PTools.SQLStats).GetLastSQLStats() do %sqlcontext.DumpResults() OR set tSC=##class(%SYS.PTools.SQLStats).GetLastSQLStats(1) // Automatically invokes DumpResults() OR set tSC=##class(%SQL.Statement).%ExecDirect(,"CALL %SYS_PTools.GetLastSQLStats(1)") %sqlcontext: The variable containing the instantiated object of the class %Library.SQLProcContext when a stored procedure is called. %sqlcontext consists of several properties, including an Error object, the SQLCODE error status, the SQL row count, and an error message. This variable is reset before each execution. Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}") Parameters: dumpResults - 1 = Automatically invoke the 'DumpResults()' if '%sqlcontext' is set to a valid value RETURN Value: The status of this method's execution

deprecated classmethod GetStats(ByRef SQLText As %String = 0, mod As %String, showStats As %Integer = 0) as %String

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: GetStats [SQL: N/A] Replaced By: GetStats (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility Purpose: Returns the Stats for a given module of a given SQL statement Invoked by the Show Plan code when Stats are to be included Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.StatsSQL).GetStats(...) SQL: N/A Examples: The following examples shows the use of this method: #1 Get the stats for the 'MAIN' module of the following SQL Statement from the 'SAMPLES' namespace: zn "SAMPLES" kill sql set sql($i(sql))="SELECT ID, Name FROM Sample.Person" set statsList=##class(%SYS.PTools.StatsSQL).GetStats(.sql,"MAIN",1) for pos=1:1:$LL(statsList) write !?3,$LTS($LG(statsList,pos)) ModuleName,Module,MAIN TimeSpent,Time,0.00999 GlobalRefs,Globals,1,201 LinesOfCode,Commands,41,311 DiskWait,Disk Wait,1 RowCount,Row Count,400 ModuleCount,Mod Execs,1 Counter,Run Count,1 Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}") Parameters: SQLText - An array of the SQL Statement in the following format: SQLText={ln# counter} SQLText({ln#})={SQL Statement} mod - SQL Module being processed showStats - 0 = Don't show any stats with the query plan 1 = Execute a query and get/show stats with the query plan [FROM: Query Test or ShowPlan^%apiSQL] 2 = Retrieve existing stats and show the averages with the query plan [NOTE: Query Info from ^mqh($UserName,"id") set in ##class(%CSP.UI.Portal.SQL.QButtons.RuntimeStats).PrepareShowPlan(...) [FROM: SQL Runtime Statistics->{View Stats}->Show Plan] 3 = Generate and show stats for an alternate Show Plan with the query plan RETURN Value: Query Stats as a $LIST with the following format: 1) $LIST({Property},{Header},{Value}) ... n) $LIST({Property},{Header},{Value})

deprecated classmethod GlobalSave(ns, rou, cur, mod, rows)

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: GlobalSave [SQL: N/A] Replaced By: saveSQLStats^%SYS.PTools() Status: [DEPRECATED] Purpose: Invoked by the Report() method to store all of the runtime data in the '%SYS.PTools.SQLStats' OR %SYS.PTools.StatsSQL table Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}")

deprecated classmethod ImportSchema(CN As %String, Rtn As %String, Internal As %Boolean = 0) as %String

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: ImportSchema [SQL: N/A] Replaced By: getImportSchema (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility Purpose: This method retrieves the Import Schema given the parameters: 'CN', 'Rtn', & 'Internal' Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLStats).ImportSchema(...) SQL: N/A Examples: The following examples shows the use of this method: #1 Retrieve the SQL Text for any of the data rows retrieved from invoking the 'exportSQLStats()' method: CALL %SYS_PTools.PT_exportSQLStats() set iSchema=##class(%SYS.PTools.SQLStats).ImportSchema("SQLStats0","PToolsSQLStats"_$JOB) Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}") Parameters: CN - The cursor name from which to retrieve the Import Schema Rtn - The routine from which to retrieve the Import Schema Internal - 0 = Return the External Query Text [DEFAULT] 1 = Return the Internal Query Text Return: Return either the Internal or External Import Schema; Otherwise, return the Error Status if an error occurs.

deprecated classmethod Init(NS As %String, Rou As %String, Cur As %String, ByRef Parms As %RawString)

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: Init [SQL: N/A] Replaced By: startSQLStats^%SYS.PTools() Status: [DEPRECATED] Purpose: Invoked by the OPEN Cursor to initializes local variables and calles Start() for the MAIN loop to start collecting stats Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}")

classmethod Purge(ns As %String = "", rtn As %String = "", ph3 As %Integer = 0, clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %RawString) as %Status [ SQLProc = Purge ]
Projected as the stored procedure: Purge
Backward-compatible Entry Point: NOTE: See the clearSQLStats() method above for more details.
deprecated classmethod QueryText(CN As %String, Rtn As %String, Internal As %Boolean = 0) as %String

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: QueryText [SQL: N/A] Replaced By: getQueryText (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility Purpose: This method retrieves the SQL Query Text given the parameters: 'CN', 'Rtn', & 'Internal' Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLStats).QueryText(...) SQL: N/A Examples: The following examples shows the use of this method: #1 Retrieve the SQL Text for any of the data rows retrieved from invoking the 'exportSQLStats()' method: CALL %SYS_PTools.PT_exportSQLStats() set sql=##class(%SYS.PTools.SQLStats).QueryText("SQLStats0","PToolsSQLStats"_$JOB) Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}") Parameters: CN - The cursor name from which to retrieve the SQL Query Text Rtn - The routine from which to retrieve the SQL Query Text Internal - 0 = Return the External Query Text [DEFAULT] 1 = Return the Internal Query Text Return: Return either the Internal or External SQL Query Text; Otherwise, return the Error Status if an error occurs.

deprecated classmethod Report(NS As %String, Rou As %String, Cur As %String, RowCount As %Integer = 0) as %String

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: Report [SQL: N/A] Replaced By: stopSQLStats^%SYS.PTools() Status: [DEPRECATED] Purpose: Invoked by the CLOSE Cursor to tidy up statistics collection OR %SYS.PTools.StatsSQL table Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}")

deprecated classmethod SetSQLStats(actionFlag As %Integer = 0) as %Integer [ SQLProc = SetSQLStats ]
Projected as the stored procedure: SetSQLStats

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: SetSQLStats [SQL: SetSQLStats] Replaced By: SetSQLStats [SQL: StatsSQL_SetSQLStats] (%SYS.PTools.StatsSQL) Status: New functionality added for Backward-Compatibility Purpose: This method sets the flag that controls whether or not the System collects SQL Statistics about each run of a query You can invoke different levels of SQL Statistics collection by setting the SQLStats-flag, for the System with this method. The SQLStats-flag controls whether or not SQL Statistics are collected for each SQL Query execution, and which performance statistics to collect. Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLStats).SetSQLStats(...) SQL: SELECT %SYS_PTools.SetSQLStats(...) Examples: The following examples shows the use of this method: #1 Turn PTools ON to collects stats for all SQL modules (System): set oldStats=##class(%SYS.PTools.SQLStats).SetSQLStats(3) Data Storage: N/A Parameters: actionFlag - This parameter can have one of the following values: 0 = Query Compilation: Don't generator SQLStats collection code for any Query Modules Query Execution: Don't collect SQLStats for any Query Modules 1 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Don't collect SQLStats for any Query Modules 2 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Collect SQLStats for MAIN Query Module (Start & Stop) 3 = Query Compilation: Generator SQLStats collection code for ALL Query Modules Query Execution: Collect SQLStats for ALL Query Modules RETURN Value: The value of the SQLStats-flag before setting it to the new value; Otherwise, return an error message if an error occurred

deprecated classmethod SetSQLStatsJob(actionFlag As %Integer = 0) as %Integer [ SQLProc = SetSQLStatsJob ]
Projected as the stored procedure: SetSQLStatsJob

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: SetSQLStatsJob [SQL: SetSQLStatsJob] Replaced By: SetSQLStatsJob [SQL: StatsSQL_SetSQLStatsJob] (%SYS.PTools.StatsSQL) SetSQLStatsFlagJob [SQL: StatsSQL_SetSQLStatsFlagJob] (%SYS.PTools.StatsSQL) Status: Maintained for Backward-Compatibility Purpose: This method sets the flag that controls whether or not this Process/Job collects SQL Statistics about each run of a query You can invoke different levels of SQL Statistics collection by setting the SQLStats-flag, for your current process/job with this method. The SQLStats-flag controls whether or not SQL Statistics are collected for each SQL Query execution, and which performance statistics to collect. Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLStats).SetSQLStatsJob(...) SQL: SELECT %SYS_PTools.SetSQLStatsJob(...) Examples: The following examples shows the use of this method: #1 Turn PTools ON to collects stats for all SQL modules: set oldStats=##class(%SYS.PTools.SQLStats).SetSQLStatsJob(3) Data Storage: N/A Parameters: actionFlag - This parameter can have one of the following values: -1 = Query Compilation: Turn SQLStats Off for this Job Query Execution: Turn SQLStats Off for this Job 0 = Query Compilation: Don't generator SQLStats collection code for any Query Modules Query Execution: Don't collect SQLStats for any Query Modules 1 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Don't collect SQLStats for any Query Modules 2 = Query Compilation: Generator SQLStats collection code for MAIN Query Module (Start & Stop) Query Execution: Collect SQLStats for MAIN Query Module (Start & Stop) 3 = Query Compilation: Generator SQLStats collection code for ALL Query Modules Query Execution: Collect SQLStats for ALL Query Modules RETURN Value: The value of the SQLStats-flag before setting it to the new value; Otherwise, return an error message if an error occurred

deprecated classmethod Start(NS As %String, Rou As %String, Cur As %String, ModString As %String)

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: Start [SQL: N/A] Replaced By: %statsStart^%SYS.PTools() Status: [DEPRECATED] Purpose: Invoked every time you enter a module to collect statistics Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}") -------------------------------------------------------------------------------

deprecated classmethod Stop(NS As %String, Rou As %String, Cur As %String, ModString As %String)

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: Stop [SQL: N/A] Replaced By: %statsStop^%SYS.PTools() Status: [DEPRECATED] Purpose: Invoked every time you exit a module to collect statistics Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}")

classmethod TriggerOnDelete(timing As %Library.String, ns As %Library.String, rtn As %Library.String, curs As %Library.String, sgrp As %Library.String, mod As %Library.String)
Perform the OnDelete Trigger operations for this class...
deprecated classmethod clearSQLStats(ns As %String = "", rtn As %String = "", ph3 As %Integer = 0, clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %RawString) as %Status [ SQLProc = PT_clearSQLStats ]
Projected as the stored procedure: PT_clearSQLStats

-- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED --- DEPRECATED -- Method: clearSQLStats [SQL: PT_clearSQLStats] Replaced BY: clearStatsSQL [SQL: PT_clearStatsSQL] (%SYS.PTools.StatsSQL) Status: New Entry Point added to mimic Purge() for Backward-Compatibility Purpose: Delete all of the data stored in the '%SYS.PTools.SQLStats' class, based on the specified parameters... Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLStats).clearSQLStats(...) SQL: CALL %SYS_PTools.PT_clearSQLStats(...) SELECT %SYS_PTools.PT_clearSQLStats(...) NOTE: This 'SqlProc' method can be invoked with either the CALL-interface or the SELECT-interface. Both interfaces execute the method: the CALL-interface does not return any resulting value, while the SELECT-interface does. Examples: The following examples shows the use of this method: #1 Delete all of the data stored in the '%SYS.PTools.SQLStats' class in the 'SAMPLES' namespace: set stats=##class(%SYS.PTools.SQLStats).clearSQLStats("SAMPLES") Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}") Parameters: ns - The namespace in which to clear SQL Statistics If none provided, use the current namespaces where SQL Stats collected [DEFAULT: Current Namespace] rtn - The routine for which to clear SQL Statistics If none provided, clear all routines in the given 'ns' [OPTIONAL] ph3 - Placeholder Parameter for future extensibility clearAll - 0 = Perform a normal clear, but leave the 'INFO' rows 1 = Clear everything including the 'INFO' rows [DEFAULT: 0] clearErrs - 0 = Don't delete the 'PTools' Application Errors 1 = Delete all of the 'PTools' Application Errors [DEFAULT: 0] returnType - 0 = Return a %Status code of either $$$OK or $$$ERROR() 1 = Return the number of Stats rows deleted from the following class: %SYS.PTools.SQLStats [DEFAULT: 0] ptInfo - A Pass By Reference information array that returns detailed information to the user in the following format: ptInfo(category,variable)=value OR $LB(val1,...,valn) Where category = { "cnt" | "curr" | "term" | ... } variable = A var corresponding to the given 'category' Example: ptInfo("cnt","clearSQLStats")=The number of rows deleted via this method [Routine Method] ptInfo("cnt","Purge")=The number of rows deleted via this method [Class Method] RETURN Value: Based on the value of the 'returnType' parameter, return one of the following: 0: Return a %Status code of either $$$OK or $$$ERROR() 1: Return the number of Stats rows deleted from the following class; Otherwise, return an error message if an error occurred: %SYS.PTools.SQLStats

deprecated classmethod exportSQLStats(file="", format="P", silent=0, rtnName="", modName="", ByRef conds As %RawString, ByRef ptInfo As %RawString, bcFlag="") as %Status [ SQLProc = PT_exportSQLStats ]
Projected as the stored procedure: PT_exportSQLStats

Method: exportSQLStats [SQL: PT_exportSQLStats] Replaces: ExportAll [SQL: SQLStats_ExportAll] & Export [SQL: SQLStats_Export] [DEPRECATED] Replaced By: exportStatsSQL [SQL: PT_exportStatsSQL] (%SYS.PTools.StatsSQL) Status: New Method to replicate Backward-Compatible Method it 'Replaces' Purpose: Create a Performance Tool file containing the data from the '%SYS.PTools.SQLStats' and return the output location... By default, the file will be created in the current directory of the InterSystems IRIS instance. NOTE: Current directory can be obtained in the following way, from a InterSystems IRIS Terminal: >write $ZU(12,"") You can pass a different value for the 'file' parameters if you wish to override the default location and file name. Invocation: This method can be invoked in the following ways: ObjectScript: ##class(%SYS.PTools.SQLStats).exportSQLStats(...) SQL: CALL %SYS_PTools.PT_exportSQLStats(...) SELECT %SYS_PTools.PT_exportSQLStats(...) NOTE: This 'SqlProc' method can be invoked with either the CALL-interface or the SELECT-interface. Both interfaces execute the method: the CALL-interface does not return any resulting value, while the SELECT-interface does. Examples: The following examples shows the use of this method: #1 Display this export file to the screen via a InterSystems IRIS Terminal: set tSC=##class(%SYS.PTools.SQLStats).exportSQLStats($IO) #2 Display this export file via the SQL Query Page of the Management Portal: CALL %SYS_PTools.PT_exportSQLStats('$IO','H') Where: '$IO' = Output to the current device NOTE: '$IO' can be omitted, as it is the default 'file' when invoked via the CALL interface 'H' = Output the format in HTML format NOTE: 'H' can be omitted, as it is the default 'format' when invoked via the CALL interface Data Storage: ^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStats{C|D|I|S|Z}") Parameters: file - The path and file in which to create and store the data from the %SYS.PTools.SQLStats class: (e.g. C:\exportFile.txt) NOTE: $IO = Outputs the data to the screen '$IO' = Output the data to the current device when invoked from the CALL/SELECT-interface '$DEF' = Ouput the data to the DEFAULT file when invoked from the CALL/SELECT-interface [DEFAULT: {Current-Directory}\PT_SQLStats_ExportSQLStats_YYYYMMDD_HHMMSS.{ext}] WHERE {Current-Directory} = The directory of the Namespace in which this method is invoked (e.g. $ZU(12,"")) NOTE 2: If Passed By Reference, then 'file' will be returned as the canonical name of the Export/Reporting file format - The output format of the Export/Reporting file This parameter has the following structure of constituent elements: format=[:$LB(,,)] Where the constituent elements have the following values: - The format of the Export/Reporting file: - P = Printable/Viewable report file (.txt file, no pagination) - D = Comma-delimited data file (.csv file) which can be read into a spreadsheet - X = Microsoft Excel XML markup file suitable for import into Excel (.xml file) - H = HTML page file (.html file) - Z = User-defined delimiter "P" formatted file (.dlm file) NOTE: The element is required or defaulted to $C(9) (TAB) Optional Elements: - User-defined delimiter [DEFAULT: $C(9) (TAB)] NOTE: This is generally used for ="Z" - .csv file header for ="D": - 0 = Don't add non-standard information header to file [DEFAULT] - 1 = Add non-standard information header to file - .csv file footer for ="D": - 0 = Don't add non-standard information footer to file [DEFAULT] - 1 = Add non-standard information footer to file EXAMPLES: Valid 'format' values: - "H" // HTML file - "D" // CSV file with No information header or footer - "D:"_$LB(,1,1) // CSV file with information header & footer - "Z:"_$LB("^") // User-defined delimiter file (delim="^") silent - 0 - Display all messages during the running of this method 1 - Don't display any messages during the running of this method rtnName - If specified, return only the rows where the 'RoutineName' matches the value of this parameter; Otherwise, return rows for all 'RoutineName' values modName - If specified, return only the rows where the 'ModuleName' matches the value of this parameter; Otherwise, return rows for all 'ModuleName' values conds - A string/array of possible conditions by which to restrict the output of the Export file, in the following format: conds= OR conds(0)=pos Count (WHERE pos = {1...n}) conds(pos)=$LIST() Pieces: 1) [] (Assumed Default: &&) 2) [{Heading}] (Omit for override cond) 3) | WHERE: := := {&& | ||} (&& = AND | || = OR) @* := @ := @ = Contains no references to {Heading} * = Contains no references to {*} EXAMPLE: conds="("",INFO,MAIN,""[("",""_{Module}_"",""))||({GlobalRefs}>20)" conds(0)=3 conds(1)=$LB(,"Module","=""INFO""") conds(2)=$LB("||","Module","=""MAIN""") conds(3)=$LB("||","GlobalRefs",">20") conds(0)=2 conds(1)=$LB(,"Module",""",INFO,MAIN,""[("",""_{*}_"","")") conds(2)=$LB("||","GlobalRefs","{*}>20") WHERE: {*} = value substitution for the 'Module' & 'GlobalRefs' fields NOTE: Conditions that don't contain any references to {Heading} fields, often called s, can be included as s, as in the following example: conds(pos)=$LB(,,"$g(^zAction(""runIt""))=1") NOTE: All conditions must be satisfied for the conds() to be considered true and for the row to be exported [PASS BY REFERENCE] ptInfo - A Pass By Reference information array that returns to the user the following details: ptInfo(variable)=value ptInfo("outputFile")=The canonical name of the Export/Reporting file bcFlag - This is a Backward-Compatibility flag used for [Internal Purposes ONLY] RETURN Value: The status from the invocation of this method

classmethod newSQLStats(SQLText, SQLType, ns, rtn, curs, compTime=0, SQLIndexHash="")
Stub: For the 'newSQLStats()' method in the '%SYS.PTools.MAC' routine
classmethod saveSQLStats(ns, rtn, curs, mod, rowCount, SQLIndexHash)
Stub: For the 'saveSQLStats()' method in the '%SYS.PTools.MAC' routine
classmethod startSQLStats(ns, rtn, curs, params, SQLIndexHash, flag, collect)
Stub: For the 'startSQLStats()' method in the '%SYS.PTools.MAC' routine
classmethod stopSQLStats(ns, rtn, curs, rowCount=0, SQLIndexHash, flag, collect)
Stub: For the 'stopSQLStats()' method in the '%SYS.PTools.MAC' routine
classmethod version() as %String [ SQLProc = SQLStats_version ]
Projected as the stored procedure: SQLStats_version
Provide the current version for the %SYS.PTools.SQLStats class/section of the Performance Tools (PTools) Application

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.CommandsExecuted, S.DiskWait, S.TimeSpent, 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.TimeSpent),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
Backward-Compatible Query
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(TimeSpent),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
Backward-Compatible Query

Indexes

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

Triggers

trigger OnDelete (AFTER event DELETE);

Inherited Members

Inherited Properties

Inherited Methods

Storage

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

^%sqlcq($NAMESPACE,"PTools","RunTime","SQLStatsD")(ID)
=
%%CLASSNAME
RoutineInfo
ModuleName
RowCount
GlobalRefs
CommandsExecuted
TimeSpent
TimeToFirstRow
StartTime
UserName
IPAddress
MachineName
ExeName
ModuleCount
Pid
DiskWait
SQLStats
NameSpace
RoutineName
CursorName
StatsGroup
StatsMarker
FeedbackOpens in a new tab