Class: %SYS.PTools.UtilSQLAnalysisDB
Replaces: %SYS.PTools.SQLUtilResults[DEPRECATED]
Purpose:
This class is used by the following Methods and Queries, defined in the
abstract interface class %SYS.PTools.UtilSQLAnalysis, to Analyze
and Store the SQL Analysis details collected against the SQL Statements
stored in the %SYS.PTools.UtilSQLStatements class:
Methods Queries
------------------------------- -----------------------------------
- indexUsage - indexUsage
- tableScans - tableScans
- tempIndices - tempIndices
- joinIndices - joinIndices
- outlierIndices - outlierIndices
- exportSQLUtilStmts
- exportSQLUtilStmtRslts
- exportSQLUtilities
- clearSQLAnalysisDB
Data Storage: ^%sqlcq("SAMPLES","PTools","util","SQLAnlsys","{C|D|I|S}")
Error Storage: ^%sqlcq($NAMESPACE,"PTools","Error"[...])=$LIST Info
-------------------------------------------------------------------------------
property BiasQueriesAsOutlier as %Library.Boolean [ InitialExpression = 0 ];
Set this flag { 0 | 1 } in the following location of the 'Storage' section of the class:
{BiasQueriesAsOutlier}
If this flag is set to zero (0), the query optimizer assumes that queries will not normally
select on a outlier value. If this flag is set to one (1), the query optimizer will assume
that queries selecting on a outlier are not rare.
property DataValues as list of %String (MAXLEN = 2000);
$LISTBUILD of the Properties that will be the data node of the {IndexName}
The extra data is stored as part of the index to avoid reading from the Data Map
Set by the follow methods: tableScans(), tempIndices(), joinIndices(), outlierIndices()
The Number of Rows in the Table Based on TuneTable Data or set by the user
property JoinFields as list of %String (MAXLEN = 2000);
Set by joinIndices() Method
$LISTBUILD of the Properties/Fields that comprise the Join Conditions
This $LIST is used as a comparison against the the {IndexFields} $LIST in order
to determine the viability of the specified {IndexName} and represented by
the {JoinIndexFlag} field
Set by joinIndices() method to one of the following values:
Flag has 5 values:
4 = Exact Match: An index exists where its fields match all fields from the JOIN conditions
3 = Leading Match: An index exists where its leading fields match all fields from the JOIN conditions, but there are additional fields in the index
2 = Contains Match: An index exists where its fields contains all fields from the JOIN conditions, but not the leading fields
1 = Partial Match: An index exists where its fields contains some of the fields from the JOIN conditions, but not the leading field
0 = No Match: No index exists to support the fields from the JOIN conditions
Set by the following methods: tableScans(), tempIndices(), joinIndices(), outlierIndices()
One of the following Map Types: { "master map " | "extent bitmap " | "bitmap index " | "index map " }
property OptionName as %String (VALUELIST = ",IU,TS,TI,JI,OI,") [ Required ];
1st piece of the IDKey
The value for this property indicates the SQL Statement Analysis Method producinc the result row:
IndexUsage() => 'IU'
TableScans() => 'TS'
TempIndices() => 'TI'
JoinIndices() => 'JI'
OutlierIndices() => 'OI'
property OutlierCondFields as list of %String (MAXLEN = 2000);
Set by outlierIndices() Method
$LISTBUILD of the Properties/Fields that comprise the Outlier Conditions
This $LIST is used as a comparison against the the {IndexFields} $LIST in order
to determine the viability of the specified {IndexName} and represented by
the {OutlierIndexFlag} field
Set by outlierIndices() Method
Flag has 5 values:
4 = Exact Match: An index exists where its fields match all fields from the Outlier conditions
3 = Leading Match: An index exists where its leading fields match all fields from the Outlier conditions, but there are additional fields in the index
2 = Contains Match: An index exists where its fields contains all fields from the Outlier conditions, but not the leading fields
1 = Partial Match: An index exists where its fields contains some of the fields from the Outlier conditions, but not the leading field
0 = No Match: No index exists to support the fields from the Outlier conditions
Set this value in the following location of the 'Storage' section of the class
or via $SYSTEM.SQLTuneTable(...):
{OutlierSelectivity}:{OutlierValue}
This is a percentage for a single property value that appears much more frequently than
the other data values within the whole set of rows. Normal selectivity is still the
percentage of each non-outlier data value within the whole set of rows.
For example, if the outlier selectivity is 80% and the regular selectivity is 1%, then
in addition to the outlier value, you can expect to find about 20 ((10-.80)/.01)
additional non-outlier values.
NOTE: This value represented the percentage of outliers, but without the percent sign.
Hence, if the outlier value is 75%, then it should be represented at .75
Set this value in the following location of the 'Storage' section of the class
or via $SYSTEM.SQLTuneTable(...):
{OutlierSelectivity}:{OutlierValue}
This is the value that should be used to compare against for conditions.
If this Outlier Value is (represented in this context as the empty string "")
and the conditions is IS NULL, then the Query Optimizer
will use the {OutlierSelectivity} when constructing the Query Plan.
Set in the following methods: tableScans(), tempIndices(), joinIndices(), outlierIndices()
This is a Pointer Reference to the %SYS.PTools.UtilSQLStatements class which contains the
SQL Statements found within InterSystems IRIS
classmethod clearSQLAnalysisDB(ns As %String = "", clearErrs As %Integer = 0, returnType As %Integer = 0, ByRef ptInfo As %RawString) as %Status [ SQLProc = UtilSQLAnalysisDB_clearSQLAnalysisDB ]
Projected as the stored procedure: UtilSQLAnalysisDB_clearSQLAnalysisDB
Method: clearSQLAnalysisDB
Replaces: ClearResults (%SYS.PTools.SQLUtilities) [DEPRECATED]
Status: New Method to replicate Backward-Compatible Method it 'Replaces'
Purpose: Delete all of the data stored in the '%SYS.PTools.UtilSQLAnalysisDB'
class, based on the specified parameters...
Invocation: This method can be invoked in the following ways:
ObjectScript: ##class(%SYS.PTools.UtilSQLAnalysisDB).clearSQLAnalysisDB(...)
SQL: CALL %SYS_PTools.UtilSQLAnalysisDB_clearSQLAnalysisDB(...)
SELECT %SYS_PTools.UtilSQLAnalysisDB_clearSQLAnalysisDB(...)
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.UtilSQLAnalysisDB'
class in the current namespace:
set status=##class(%SYS.PTools.UtilSQLAnalysisDB).clearSQLAnalysisDB()
Data Storage: ^%sqlcq($NAMESPACE,"PTools","util","SQLAnlsys")
Parameters:
ns - The namespace in which to clear SQL Index/Analysis Statstics
[DEFAULT: Current Namespace]
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.UtilSQLAnalysisDB
[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","clearSQLAnalysisDB")=The number of rows deleted
via this 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.UtilSQLAnalysisDB
classmethod version() as %String [ SQLProc = UtilSQLAnalysisDB_version ]
Projected as the stored procedure: UtilSQLAnalysisDB_version
Provide the current version for the UtilSQLAnalysisDB class/section of the Performance Tools (PTools) Application
Queries
query UtilSQLAnalysisDBView()
SQL Query as view "UtilSQLAnalysisDBView": SELECT ID, OptionName, Counter,
SQLPointer, SchemaName, TableName, ModuleName, MapType, ExtentSize, BlockCount,
IndexName, UsageCount, $LISTTOSTRING(IndexFields,',') AS IndexFields, $LISTTOSTRING(DataValues,',') AS DataValues,
JoinIndexFlag, $LISTTOSTRING(JoinFields,',') AS JoinFields,
OutlierField, BiasQueriesAsOutlier, OutlierSelectivity, OutlierValue, OutlierIndexFlag, $LISTTOSTRING(OutlierCondFields,',') AS OutlierCondFields,
WhereOperator, WhereValue, WhereCondition
FROM %SYS_PTools.UtilSQLAnalysisDB
Query/View: UtilSQLAnalysisDBView
Replaces: SQLUtilStmtResultsView (%SYS.PTools.SQLUtilResults) [DEPRECATED]
Status: New Query/View to replicate Backward-Compatible Query/View it
'Replaces'
Purpose: Class View which returns all of the SQL Statements information
stored in the '%SYS.PTools.UtilSQLAnalysisDB' class/table
Indexes
index (Master on OptionName,Counter) [IdKey, Type = key];
Index methods: MasterCheck(), MasterDelete(), MasterExists(), MasterOpen(), MasterSQLCheckUnique(), MasterSQLExists(), MasterSQLFindPKeyByConstraint(), MasterSQLFindRowIDByConstraint()
