文章

Hao Ma · 一月 10

阅读大约需 15 分钟

自 Caché 2017 以后，SQL 引擎包含了一些新的统计信息。这些统计信息记录了执行查询的次数以及运行查询所花费的时间。

对于想要对包含许多 SQL 语句的应用程序的性能进行监控和尝试优化的人来说，这是一座宝库，但访问数据并不像一些人希望的那么容易。

本文和相关的示例代码说明了如何使用这些信息，以及如何例行提取每日统计信息的摘要，并保存应用程序的 SQL 性能的历史记录。

记录了什么？

每次执行 SQL 语句时，都记录花费的时间。这是非常轻量的操作，无法关闭。为了最大程度地降低开销，统计信息保留在内存中并定期写入磁盘。数据包括一天中执行查询的次数以及所花费的平均时间和总时间。

数据不会立即写入磁盘，并且在写入之后，统计信息将由“更新 SQL 查询统计信息”任务更新，该任务通常计划为每小时运行一次。该任务可以手动触发，但是如果你希望在测试查询时实时查看统计信息，则整个过程需要一点耐心。

警告：在 InterSystems IRIS 2019 及更早版本中，不会针对已使用 %Studio.Project:Deploy 机制部署的类或例程中的嵌入式 SQL 收集这些统计信息。示例代码不会有任何中断，但这可能会使你产生误导（我被误导过），让你以为一切正常，因为没有查询显示为高开销。

如何查看信息？

你可以在管理门户中查看查询列表。转到 SQL 页面，点击“SQL 语句”选项卡。对于你正在运行并查看的新查询，这种方式很好；但是如果有数千条查询正在运行，则可能变得难以管理。

另一种方法是使用 SQL 搜索查询。信息存储在 INFORMATION_SCHEMA 模式的表中。该模式含有大量表，我在本文的最后附上了一些 SQL 查询示例。

何时删除统计信息？

每次重新编辑查询时会删除其数据。因此对于动态查询，这可能意味着清除缓存的查询时。对于嵌入式 SQL，则意味着重新编译在其中嵌入 SQL 的类或例程时。

在活跃的站点上，可以合理预期统计信息将保存超过一天，但是存放统计信息的表不能用作运行报告或长期分析的长期参考源。

如何汇总信息？

我建议每天晚上将数据提取到永久表中，这些表在生成性能报告时更易于使用。如果在白天编译类，可能会丢失一些信息，但这不太可能对慢速查询的分析产生任何实际影响。

下面的代码示例说明了如何将每个查询的统计信息提取到每日汇总中。它包括三个简短的类：

一个应在每晚运行的任务。
DRL.MonitorSQL 是主类，用于从 INFORMATION_SCHEMA 表提取数据并存储。
第三个类 DRL.MonitorSQLText 是一个优化类，它存储一次（可能很长的）查询文本，并且只将查询的哈希存储在每天的统计信息中。

示例说明

该任务提取前一天的信息，因此应安排在午夜后不久执行。

你可以导出更多历史数据，只要其存在。要提取过去 120 天的数据

Do ##class(DRL.MonitorSQL).Capture($h-120,$h-1)

该示例代码直接读取全局 ^rIndex，因为最早版本的统计信息未将日期公开给 SQL。

我所包括的变体将循环实例中的所有命名空间，但这并不总是合适的。

如何查询已提取的数据

提取数据后，您可以通过运行以下语句查找最繁重的查询

SELECT top 20
S.RunDate,S.RoutineName,S.TotalHits,S.SumpTIme,S.Hash,t.QueryText
from DRL.MonitorSQL S
left join DRL.MonitorSQLText T on S.Hash=T.Hash
where RunDate='08/25/2019'
order by SumpTime desc

此外，如果选择了开销大的查询的哈希，可以通过以下语句查看该查询的历史记录

SELECT S.RunDate,S.RoutineName,S.TotalHits,S.SumpTIme,S.Hash,t.QueryText
from DRL.MonitorSQL S
left join DRL.MonitorSQLText T on S.Hash=T.Hash
where S.Hash='CgOlfRw7pGL4tYbiijYznQ84kmQ='
order by RunDate

今年早些时候，我获取了一个活跃站点的数据，然后查看了开销最大的查询。有一个查询的平均时间不到 6 秒，但每天被调用 14000 次，加起来每天消耗的时间将近 24 小时。实际上，一个核心完全被这个查询占用。更糟糕的是，第二个查询要花一个小时，它是第一个查询的变体。

运行日期	例程名称	总命中次数	总时间	哈希	查询文本（有节略）
03/16/2019		14,576	85,094	5xDSguu4PvK04se2pPiOexeh6aE=	DECLARE C CURSOR FOR SELECT * INTO :%col(1) , :%col(2) , :%col(3) , :%col(4) …
03/16/2019		15,552	3,326	rCQX+CKPwFR9zOplmtMhxVnQxyw=	DECLARE C CURSOR FOR SELECT * INTO :%col(1) , :%col(2) , :%col(3) , :%col(4) , …
03/16/2019		16,892	597	yW3catzQzC0KE9euvIJ+o4mDwKc=	DECLARE C CURSOR FOR SELECT * INTO :%col(1) , :%col(2) , :%col(3) , :%col(4) , :%col(5) , :%col(6) , :%col(7) ,
03/16/2019		16,664	436	giShyiqNR3K6pZEt7RWAcen55rs=	DECLARE C CURSOR FOR SELECT * , TKGROUP INTO :%col(1) , :%col(2) , :%col(3) , ..
03/16/2019		74,550	342	4ZClMPqMfyje4m9Wed0NJzxz9qw=	DECLARE C CURSOR FOR SELECT …

表 1：客户站点的实际结果

INFORMATION_SCHEMA 模式中的表

除了统计信息外，此模式中的表还会跟踪查询、列、索引等的使用位置。通常，SQL 语句是起始表，它的连接方式类似于“Statements.Hash=OtherTable.Statement”。

直接访问这些表以查找一天中开销最大的查询，这一操作的等效查询是...

SELECT DS.Day,Loc.Location,DS.StatCount,DS.StatTotal,S.Statement,S.Hash
FROM INFORMATION_SCHEMA.STATEMENT_DAILY_STATS DS
left join INFORMATION_SCHEMA.STATEMENTS  S
on S.Hash=DS.Statement
left join INFORMATION_SCHEMA.STATEMENT_LOCATIONS  Loc
on S.Hash=Loc.Statement
where Day='08/26/2019'
order by DS.stattotal desc

无论你是否考虑建立一个更系统的过程，我都建议每个使用 SQL 处理大型应用程序的人今天都运行这个查询。

如果某个特定查询显示为高开销，则可以通过运行以下语句获取历史记录

SELECT DS.Day,Loc.Location,DS.StatCount,DS.StatTotal,S.Statement,S.Hash
FROM INFORMATION_SCHEMA.STATEMENT_DAILY_STATS DS
left join INFORMATION_SCHEMA.STATEMENTS  S
on S.Hash=DS.Statement
left join INFORMATION_SCHEMA.STATEMENT_LOCATIONS  Loc
on S.Hash=Loc.Statement
where S.Hash='jDqCKaksff/4up7Ob0UXlkT2xKY='
order by DS.Day

每日提取统计信息的代码示例

标准免责声明 - 此示例仅用于说明。不对其提供支持，也不保证其有效。

Class DRL.MonitorSQLTask Extends %SYS.Task.Definition
{
Parameter TaskName = "SQL Statistics Summary";
Method OnTask() As %Status
{
              set tSC=$$$OK
              TRY {
                             do ##class(DRL.MonitorSQL).Run()
              }
              CATCH exp {
                            set tSC=$SYSTEM.Status.Error("Error in SQL Monitor Summary Task")
              }
              quit tSC
}
}

Class DRL.MonitorSQLText Extends %Persistent
{
/// Hash of query text
Property Hash As %String;

/// query text for hash
Property QueryText As %String(MAXLEN = 9999);
Index IndHash On Hash [ IdKey, Unique ];
}

/// Summary of very low cost SQL query statistics collected in Cache 2017.1 and later.

/// Refer to documentation on "SQL Statement Details" for information on the source data.

/// Data is stored by date and time to support queries over time.

/// Typically run to summarise the SQL query data from the previous day.
Class DRL.MonitorSQL Extends %Persistent
{
/// RunDate and RunTime uniquely identify a run
Property RunDate As %Date;
/// Time the capture was started
/// RunDate and RunTime uniquely identify a run
Property RunTime As %Time;

/// Count of total hits for the time period for
Property TotalHits As %Integer;

/// Sum of pTime
Property SumPTime As %Numeric(SCALE = 4);

/// Routine where SQL is found
Property RoutineName As %String(MAXLEN = 1024);

/// Hash of query text
Property Hash As %String;

Property Variance As %Numeric(SCALE = 4);

/// Namespace where queries are run
Property Namespace As %String;

/// Default run will process the previous days data for a single day.
/// Other date range combinations can be achieved using the Capture method.
ClassMethod Run()
{
              //Each run is identified by the start date / time to keep related items together

              set h=$h-1
              do ..Capture(+h,+h)
}

/// Captures historic statistics for a range of dates
ClassMethod Capture(dfrom, dto)
{
              set oldstatsvalue=$system.SQL.SetSQLStatsJob(-1)

                set currNS=$znspace
                set tSC=##class(%SYS.Namespace).ListAll(.nsArray)
                set ns=""
      set time=$piece($h,",",2)
      kill ^||TMP.MonitorSQL
                do {
                               set ns=$o(nsArray(ns))
                               quit:ns=""
                               use 0 write !,"processing namespace ",ns
                               zn ns
                                           for dateh=dfrom:1:dto {
                                                          set hash=""
                                                          set purgedun=0
                                                          do {
                                                                        set hash=$order(^rINDEXSQL("sqlidx",1,hash))
                                                                        continue:hash=""
                                                                        set stats=$get(^rINDEXSQL("sqlidx",1,hash,"stat",dateh))
                                                                        continue:stats=""
                                                                        set ^||TMP.MonitorSQL(dateh,ns,hash)=stats

                                                          &SQL(SELECT  Location into :tLocation FROM INFORMATION_SCHEMA.STATEMENT_LOCATIONS WHERE Statement=:hash)
                                                                        if SQLCODE'=0 set Location=""
                                                                        set ^||TMP.MonitorSQL(dateh,ns,hash,"Location")=tLocation

                                                          &SQL(SELECT  Statement INTO :Statement FROM INFORMATION_SCHEMA.STATEMENTS WHERE Hash=:hash)
                                                                        if SQLCODE'=0 set Statement=""
                                                                        set ^||TMP.MonitorSQL(dateh,ns,hash,"QueryText")=Statement
                                                          } while hash'=""

                                           }
                } while ns'=""
                zn currNS
                set dateh=""
                do {
                               set dateh=$o(^||TMP.MonitorSQL(dateh))
                               quit:dateh=""
                               set ns=""
                               do {
                                             set ns=$o(^||TMP.MonitorSQL(dateh,ns))
                                             quit:ns=""
                                             set hash=""
                                             do {
                                                         set hash=$o(^||TMP.MonitorSQL(dateh,ns,hash))
                                                          quit:hash=""
                                                         set stats=$g(^||TMP.MonitorSQL(dateh,ns,hash))
                                                          continue:stats=""
                                                          // The first time through the loop delete all statistics for the day so it is re-runnable
                                                          // But if we run for a day after the raw data has been purged, it will wreck eveything
                                                          // so do it here, where we already know there are results to insert in their place.
                                                          if purgedun=0 {
                                                                        &SQL(DELETE FROM websys.MonitorSQL WHERE RunDate=:dateh )
                                                                        set purgedun=1
                                                          }

                                                          set tObj=##class(DRL.MonitorSQL).%New()

                                                          set tObj.Namespace=ns
                                                          set tObj.RunDate=dateh
                                                          set tObj.RunTime=time
                                                          set tObj.Hash=hash
                                                          set tObj.TotalHits=$listget(stats,1)
                                                          set tObj.SumPTime=$listget(stats,2)
                                                          set tObj.Variance=$listget(stats,3)
                                                          set tObj.Variance=$listget(stats,3)

                                                         set queryText=^||TMP.MonitorSQL(dateh,ns,hash,"QueryText")
                                                         set tObj.RoutineName=^||TMP.MonitorSQL(dateh,ns,hash,"Location")

                                                    &SQL(Select ID into :TextID from DRL.MonitorSQLText where Hash=:hash)
                                                          if SQLCODE'=0 {
                                                                        set textref=##class(DRL.MonitorSQLText).%New()
                                                                        set textref.Hash=tObj.Hash
                                                                        set textref.QueryText=queryText
                                                                        set sc=textref.%Save()
                                                          }
                                                          set tSc=tObj.%Save()

                                                          //avoid dupicating the query text in each record because it can be very long. Use a lookup
                                                          //table keyed on the hash. If it doesn't exist add it.
                                                          if $$$ISERR(tSc) do $system.OBJ.DisplayError(tSc)

                                                          if $$$ISERR(tSc) do $system.OBJ.DisplayError(tSc)
                                             } while hash'=""
                               } while ns'=""

                } while dateh'=""



              do $system.SQL.SetSQLStatsJob(0)
}

Query Export(RunDateH1 As %Date, RunDateH2 As %Date) As %SQLQuery
{
SELECT S.Hash,RoutineName,RunDate,RunTime,SumPTime,TotalHits,Variance,RoutineName,T.QueryText
FROM DRL.MonitorSQL S LEFT JOIN DRL.MonitorSQLText T on S.Hash=T.Hash
WHERE RunDate>=:RunDateH1 AND RunDate:RunDateH2
}
}

#监视 #SQL #性能 #Caché #InterSystems IRIS

3 0 0 48

APM – 监控 SQL 查询性能