|
%SYS
|
|
|
%SYS
|
|
<style type="text/css"> DEP { color: red; font-weight: bold; } BC { color: green; font-weight: bold; } PROP { display: block; white-space: pre; font-size: 16px; font-family: monospace; } .dep-head { color: red; white-space: pre; font-family: 'Courier New'; } .section-head { color: blue; white-space: pre; font-family: 'Courier New'; } .info-head { white-space: pre; font-family: 'Courier New'; } </style> More...
Static Public Member Functions | |
| _.Library.Status | clearSQLAnalysisDB (_.Library.String ns, _.Library.Integer clearErrs, _.Library.Integer returnType, _.Library.RawString ptInfo) |
| <style type="text/css"> DEP { color: red; font-weight: bold; } BC { color: green; font-weight: bold; } PROP { display: block; white-space: pre; font-size: 16px; font-family: monospace; } .dep-head { color: red; white-space: pre; font-family: 'Courier New'; } .section-head { color: blue; white-space: pre; font-family: 'Courier New'; } .info-head { white-space: pre; font-family: 'Courier New'; } </style> More... | |
| _.Library.String | version () |
| Provide the current version for the UtilSQLAnalysisDB class/section of the Performance Tools (PTools) Application. | |
Public Attributes | |
| BiasQueriesAsOutlier | |
| <PROP> More... | |
| BlockCount | |
| The approximate numbers of map blocks used by each SQL map. More... | |
| Counter | |
| <PROP> More... | |
| DataValues | |
| $LISTBUILD of the Properties that will be the data node of the {IndexName} More... | |
| ExtentSize | |
| Set by the follow methods: tableScans(), tempIndices(), joinIndices(), outlierIndices() More... | |
| IndexFields | |
| $LISTBUILD of the Properties that will make up the Subscripts of the {IndexName} More... | |
| IndexName | |
| More... | |
| JoinFields | |
| <PROP> More... | |
| JoinIndexFlag | |
| <PROP> More... | |
| MapType | |
| Set by the following methods: tableScans(), tempIndices(), joinIndices(), outlierIndices() More... | |
| ModuleName | |
| Set by the TableScan class method. More... | |
| OptionName | |
| <PROP> More... | |
| OutlierCondFields | |
| <PROP> More... | |
| OutlierField | |
| More... | |
| OutlierIndexFlag | |
| <PROP> More... | |
| OutlierSelectivity | |
| <PROP> More... | |
| OutlierValue | |
| <PROP> More... | |
| SQLPointer | |
| <PROP> More... | |
| SchemaName | |
| The schema of the table being queried in the SQL Statement. More... | |
| TableName | |
| The table being queried in the SQL Statement. More... | |
| UsageCount | |
| Used by both IndexUsage() and TempIndices() class methods. More... | |
| WhereCondition | |
| More... | |
| WhereOperator | |
| More... | |
| WhereValue | |
| More... | |
<style type="text/css"> DEP { color: red; font-weight: bold; } BC { color: green; font-weight: bold; } PROP { display: block; white-space: pre; font-size: 16px; font-family: monospace; } .dep-head { color: red; white-space: pre; font-family: 'Courier New'; } .section-head { color: blue; white-space: pre; font-family: 'Courier New'; } .info-head { white-space: pre; font-family: 'Courier New'; } </style>
Class: SYS.PTools.UtilSQLAnalysisDB Replaces: <class>SYS.PTools.SQLUtilResults</class> <DEP>[DEPRECATED]</DEP> Purpose:
This class is used by the following Methods and Queries, defined in the abstract interface class <class>SYS.PTools.UtilSQLAnalysis</class>, to Analyze and Store the SQL Analysis details collected against the SQL Statements stored in the <class>SYS.PTools.UtilSQLStatements</class> class:
Methods Queries
Data Storage: ^sqlcq("SAMPLES","PTools","util","SQLAnlsys","{C|D|I|S}")
</PROP>
|
static |
<style type="text/css"> DEP { color: red; font-weight: bold; } BC { color: green; font-weight: bold; } PROP { display: block; white-space: pre; font-size: 16px; font-family: monospace; } .dep-head { color: red; white-space: pre; font-family: 'Courier New'; } .section-head { color: blue; white-space: pre; font-family: 'Courier New'; } .info-head { white-space: pre; font-family: 'Courier New'; } </style>
Class: SYS.PTools.UtilSQLAnalysisDB Replaces: <class>SYS.PTools.SQLUtilResults</class> <DEP>[DEPRECATED]</DEP> Purpose:
This class is used by the following Methods and Queries, defined in the abstract interface class <class>SYS.PTools.UtilSQLAnalysis</class>, to Analyze and Store the SQL Analysis details collected against the SQL Statements stored in the <class>SYS.PTools.UtilSQLStatements</class> class:
Methods Queries
Data Storage: ^sqlcq("SAMPLES","PTools","util","SQLAnlsys","{C|D|I|S}")
</PROP>
Method: clearSQLAnalysisDB Replaces: ClearResults (<class>SYS.PTools.SQLUtilities</class>) <DEP>[DEPRECATED]</DEP> 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
| BiasQueriesAsOutlier |
<PROP>
Set this flag { 0 | 1 } in the following location of the 'Storage' section of the class: <Property name="fieldName"> <BiasQueriesAsOutlier>{BiasQueriesAsOutlier}</BiasQueriesAsOutlier> </Property> 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. </PROP>
| BlockCount |
| Counter |
<PROP>
2nd piece of the IDKey
A unique ID counter which comprises the data row's IDKey </PROP>
| DataValues |
$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
| ExtentSize |
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
| IndexFields |
$LISTBUILD of the Properties that will make up the Subscripts of the {IndexName}
Every Index will have ID as the final subscript
| IndexName |
| JoinFields |
<PROP>
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 </PROP>
| JoinIndexFlag |
<PROP>
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 </PROP>
| MapType |
Set by the following methods: tableScans(), tempIndices(), joinIndices(), outlierIndices()
One of the following Map Types: { "master map " | "extent bitmap " | "bitmap index " | "index map " }
Set by the TableScan class method.
Name of the Module that is doing the Table Scan
| OptionName |
<PROP>
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' </PROP>
| OutlierCondFields |
<PROP>
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 </PROP>
| OutlierField |
| OutlierIndexFlag |
<PROP>
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 </PROP>
| OutlierSelectivity |
<PROP>
Set this value in the following location of the 'Storage' section of the class or via $SYSTEM.SQLTuneTable(...): <Property name="fieldName"> <OutlierSelectivity>{OutlierSelectivity}:{OutlierValue}</OutlierSelectivity> </Property> 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 </PROP>
| OutlierValue |
<PROP>
Set this value in the following location of the 'Storage' section of the class or via $SYSTEM.SQLTuneTable(...): <Property name="fieldName"> <OutlierSelectivity>{OutlierSelectivity}:{OutlierValue}</OutlierSelectivity> </Property> This is the value that should be used to compare against for <where-clause> conditions. If this Outlier Value is <NULL> (represented in this context as the empty string "") and the <where-clause> conditions is <outlier-field> IS NULL, then the Query Optimizer will use the {OutlierSelectivity} when constructing the Query Plan. </PROP>
| SQLPointer |
<PROP>
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 </PROP>
| SchemaName |
The schema of the table being queried in the SQL Statement.
| TableName |
The table being queried in the SQL Statement.
| UsageCount |
Used by both IndexUsage() and TempIndices() class methods.
Number of times the index was used in the sql.
| WhereCondition |
| WhereOperator |
| WhereValue |
1.9.1