Class Reference
IRIS for UNIX 2019.2
InterSystems: The power behind what matters   
Documentation  Search
  [%SYS] >  [%SYS] >  [PTools] >  [UtilSQLAnalysisDB]
Private  Storage   

persistent class %SYS.PTools.UtilSQLAnalysisDB extends %Persistent

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

Inventory

Parameters Properties Methods Queries Indices ForeignKeys Triggers
24 2 1 1 1


Summary

Properties
%Concurrency BiasQueriesAsOutlier BlockCount Counter
DataValues ExtentSize IndexFields IndexName
JoinFields JoinIndexFlag MapType ModuleName
OptionName OutlierCondFields OutlierField OutlierIndexFlag
OutlierSelectivity OutlierValue SQLPointer SchemaName
TableName UsageCount WhereCondition WhereOperator
WhereValue

Methods
%%OIDGet %1Check %AddJrnObjToSyncSet %AddToSaveSet
%AddToSyncSet %BMEBuilt %BindExport %BuildIndices
%BuildIndicesSegment %BuildObjectGraph %CheckConstraints %CheckConstraintsForExtent
%ClassIsLatestVersion %ClassName %Close %ComposeOid
%ConstructClone %Delete %DeleteExtent %DeleteId
%DispatchClassMethod %DispatchGetModified %DispatchGetProperty %DispatchMethod
%DispatchSetModified %DispatchSetMultidimProperty %DispatchSetProperty %DowngradeConcurrency
%ExecuteAfterTriggers %ExecuteBeforeTriggers %Exists %ExistsId
%Extends %FileIndices %FileIndicesBuffered %GUID
%GUIDSet %GetLock %GetParameter %GetSwizzleObject
%Id %IncrementCount %InsertBatch %IsA
%IsModified %IsNull %JournalObject %KillExtent
%KillExtentData %LoadFromMemory %LockExtent %LockId
%New %NormalizeObject %ObjectIsNull %ObjectModified
%Oid %OnBeforeAddToSync %OnDetermineClass %Open
%OpenId %OriginalNamespace %PackageName %PhysicalAddress
%PurgeIndices %Reload %RemoveFromSaveSet %ResolveConcurrencyConflict
%RollBack %SQLAcquireLock %SQLAcquireTableLock %SQLAfterTriggers
%SQLBeforeTriggers %SQLBuildIndices %SQLBuildPurgeIndexForRow %SQLBuildPurgeIndices
%SQLCheckUnique %SQLCheckUniqueIndices %SQLCheckUniqueKeys %SQLCopyIcolIntoName
%SQLCopyNameIntoIcol %SQLCreateInsDelTables %SQLDefineiDjVars %SQLDelete
%SQLDeleteChildren %SQLDeleteTempStreams %SQLEExit %SQLExists
%SQLFKeyDelLock %SQLFastInsert %SQLFieldValidate %SQLGetLock
%SQLGetOld %SQLGetOldAll %SQLGetOldIndex %SQLInsert
%SQLInsertComputes %SQLInsertStreams %SQLInvalid %SQLInvalid2
%SQLMVDelete %SQLMVIndexDelete %SQLMVIndexInsert %SQLMVIndexUpdate
%SQLMVInsert %SQLMVUpdate %SQLMissing %SQLNormalizeCompFields
%SQLNormalizeFields %SQLPurgeIndices %SQLQuickBulkInsert %SQLQuickBulkLoad
%SQLQuickBulkSave %SQLQuickBulkUpdate %SQLQuickDelete %SQLQuickDeleteChildren
%SQLQuickFindPKeyByRowID %SQLQuickFindRowIDByPKey %SQLQuickInsert %SQLQuickLoad
%SQLQuickLoadChildren %SQLQuickLogicalToOdbc %SQLQuickOdbcToLogical %SQLQuickUpdate
%SQLReleaseLock %SQLReleaseTableLock %SQLStorageValidation %SQLTrigDelTab
%SQLTrigInsTab %SQLUnlock %SQLUnlock2 %SQLUnlockError
%SQLUnlockRef %SQLUpdate %SQLUpdateComputes %SQLUpdateStreams
%SQLValidateCompFields %SQLValidateFields %SQLicompView %SQLnBuild
%Save %SaveDirect %SaveIndices %SerializeObject
%SetModified %SortBegin %SortEnd %SyncObjectIn
%SyncTransport %UnlockExtent %UnlockId %UpgradeConcurrency
%ValidateIndices %ValidateObject clearSQLAnalysisDB version


Properties

• property BiasQueriesAsOutlier as %Library.Boolean [ InitialExpression = 0 ];
Set this flag { 0 | 1 } in the following location of the 'Storage' section of the class: {BiasQueriesAsOutlier} > {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 BlockCount as %Integer;
The approximate numbers of map blocks used by each SQL map NOTE: The size of all SQL map blocks is 2048 bytes (2K bytes).
• property Counter as %Integer [ InitialExpression = $I(^%sqlcq($NAMESPACE,"PTools","util","SQLAnlsys","D")) ];
2nd piece of the IDKey A unique ID counter which comprises the data row's IDKey
• 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
• property ExtentSize as %Integer;
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 IndexFields as list of %String(MAXLEN=2000);
$LISTBUILD of the Properties that will make up the Subscripts of the {IndexName} Every Index will have ID as the final subscript
• property IndexName as %String(MAXLEN=2000);
• 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
• property JoinIndexFlag as %Integer;
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
• property MapType as %String(MAXLEN=100);
Set by the follow methods: tableScans(), tempIndices(), joinIndices(), outlierIndices() One of the following Map Types: { "master map " | "extent bitmap " | "bitmap index " | "index map " }
• property ModuleName as %String(MAXLEN=2000);
Set by the TableScan class method Name of the Module that is doing the Table Scan
• property OptionName as %String(VALUELIST=",IU,TS,TI,JI,OI,");
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
• property OutlierField as %String(MAXLEN=2000);
• property OutlierIndexFlag as %Integer;
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
• property OutlierSelectivity as %Float;
Set this value in the following location of the 'Storage' section of the class or via $SYSTEM.SQLTuneTable(...): {OutlierSelectivity}:{OutlierValue} > {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
• property OutlierValue as %String(MAXLEN=1000);
Set this value in the following location of the 'Storage' section of the class or via $SYSTEM.SQLTuneTable(...): {OutlierSelectivity}:{OutlierValue} > {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.
• property SQLPointer as %SYS.PTools.UtilSQLStatements;
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
• property SchemaName as %String(MAXLEN=2000);
The schema of the table being queried in the SQL Statement
• property TableName as %String(MAXLEN=2000);
The table being queried in the SQL Statement
• property UsageCount as %Integer;
Used by both IndexUsage() and TempIndices() class methods Number of times the index was used in the sql.
• property WhereCondition as %String(MAXLEN=2000);
• property WhereOperator as %String(MAXLEN=100);
• property WhereValue as %String(MAXLEN=1000);

Methods

• classmethod clearSQLAnalysisDB() as %Status [ SQLProc = ]

------------------------------------------------------------------------------- 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... 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: N/A RETURN Value: The status of this method; Otherwise, return an error message if an error occurred -------------------------------------------------------------------------------

• classmethod version() as %String [ SQLProc = 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 -------------------------------------------------------------------------------


Indices

•index (Master on OptionName,Counter) [IdKey];

Triggers

•trigger OnDelete (AFTER event DELETE)


Copyright (c) 2019 by InterSystems Corporation. Cambridge, Massachusetts, U.S.A. All rights reserved. Confidential property of InterSystems Corporation.