-- 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
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 --- 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 --- 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 --- 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 --- 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}")
-- 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 --- 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 --- 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}")
-------------------------------------------------------------------------------
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
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)
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()
