InterSystems 最佳实践系列---APM – 监控 SQL 查询性能
自 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 表提取数据并存储。
示例说明
该任务提取前一天的信息，因此应安排在午夜后不久执行。
你可以导出更多历史数据，只要其存在。 要提取过去 120 天的数据
Do ##class(DRL.MonitorSQL).Capture($h-120,$h-1)
该示例代码直接读取全局 ^rIndex，因为最早版本的统计信息未将日期公开给 SQL。
我所包括的变体将循环实例中的所有命名空间，但这并不总是合适的。
如何查询已提取的数据
提取数据后，您可以通过运行以下语句查找最繁重的查询
SELECT top 20S.RunDate,S.RoutineName,S.TotalHits,S.SumpTIme,S.Hash,t.QueryTextfrom DRL.MonitorSQL Sleft join DRL.MonitorSQLText T on S.Hash=T.Hashwhere RunDate='08/25/2019'order by SumpTime desc
此外，如果选择了开销大的查询的哈希，可以通过以下语句查看该查询的历史记录
SELECT S.RunDate,S.RoutineName,S.TotalHits,S.SumpTIme,S.Hash,t.QueryTextfrom DRL.MonitorSQL Sleft join DRL.MonitorSQLText T on S.Hash=T.Hashwhere S.Hash='CgOlfRw7pGL4tYbiijYznQ84kmQ='order by RunDate
今年早些时候，我获取了一个活跃站点的数据，然后查看了开销最大的查询。 有一个查询的平均时间不到 6 秒，但每天被调用 14000 次，加起来每天消耗的时间将近 24 小时。 实际上，一个核心完全被这个查询占用。 更糟糕的是，第二个查询要花一个小时，它是第一个查询的变体。
| 运行日期 |
| 03/16/2019 |
| 03/16/2019 |
| 03/16/2019 |
| 03/16/2019 |
| 03/16/2019 |
表 1：客户站点的实际结果
INFORMATION_SCHEMA 模式中的表
除了统计信息外，此模式中的表还会跟踪查询、列、索引等的使用位置。 通常，SQL 语句是起始表，它的连接方式类似于“Statements.Hash=OtherTable.Statement”。
直接访问这些表以查找一天中开销最大的查询，这一操作的等效查询是...
SELECT DS.Day,Loc.Location,DS.StatCount,DS.StatTotal,S.Statement,S.HashFROM INFORMATION_SCHEMA.STATEMENT_DAILY_STATS DSleft join INFORMATION_SCHEMA.STATEMENTS Son S.Hash=DS.Statementleft join INFORMATION_SCHEMA.STATEMENT_LOCATIONS Locon S.Hash=Loc.Statementwhere Day='08/26/2019'order by DS.stattotal desc
无论你是否考虑建立一个更系统的过程，我都建议每个使用 SQL 处理大型应用程序的人今天都运行这个查询。
如果某个特定查询显示为高开销，则可以通过运行以下语句获取历史记录
SELECT DS.Day,Loc.Location,DS.StatCount,DS.StatTotal,S.Statement,S.HashFROM INFORMATION_SCHEMA.STATEMENT_DAILY_STATS DSleft join INFORMATION_SCHEMA.STATEMENTS Son S.Hash=DS.Statementleft join INFORMATION_SCHEMA.STATEMENT_LOCATIONS Locon S.Hash=Loc.Statementwhere S.Hash='jDqCKaksff/4up7Ob0UXlkT2xKY='order by DS.Day
每日提取统计信息的代码示例
标准免责声明 - 此示例仅用于说明。 不对其提供支持，也不保证其有效。
<p>
</p>
<p>
<span style="font-size:11pt"><span style="line-height:107%"><span style=""><span style="color:navy">Class DRL.MonitorSQLTask Extends %SYS.Task.Definition</span><br /><span style="color:black">{</span><br /><span style="color:navy">Parameter </span><span style="color:black">TaskName = </span><span style="color:purple">"SQL Statistics Summary"</span><span style="color:black">;</span><br /><span style="color:navy">Method </span><span style="color:black">OnTask() </span><span style="color:navy">As %Status</span><br /><span style="color:black">{</span><br /> <span style="color:blue">set </span><span style="color:olive">tSC</span><span style="color:black">=</span><span style="color:blue">$$$OK</span><br /> <span style="color:blue">TRY </span><span style="color:purple">{</span><br /> <span style="color:blue">do </span><span style="color:navy">##class</span><span style="color:black">(</span><span style="color:teal">DRL.MonitorSQL</span><span style="color:black">).</span><span style="color:blue">Run</span><span style="color:black">()</span><br /> <span style="color:purple">}</span><br /> <span style="color:blue">CATCH </span><span style="color:olive">exp </span><span style="color:purple">{</span><br /> <span style="color:blue">set </span><span style="color:olive">tSC</span><span style="color:black">=</span><span style="color:blue">$SYSTEM</span><span style="color:teal">.Status</span><span style="color:black">.</span><span style="color:blue">Error</span><span style="color:black">(</span><span style="color:green">"Error in SQL Monitor Summary Task"</span><span style="color:black">)</span><br /> <span style="color:purple">}</span><br /> <span style="color:blue">quit </span><span style="color:olive">tSC</span><br /><span style="color:black"> }</span><br /><span style="color:black">}</span></span></span></span>
</p>
<p>
</p>
<p>
<span style="font-size:11pt"><span style="line-height:107%"><span style=""><span style="color:navy">Class DRL.MonitorSQLText Extends %Persistent</span><br /><span style="color:black">{</span><br /><span style="color:navy">/// Hash of query text</span><br /><span style="color:navy">Property </span><span style="color:black">Hash </span><span style="color:navy">As %String</span><span style="color:black">;</span></span></span></span><br />
</p>
<p>
<span style="font-size:11pt"><span style="line-height:107%"><span style=""><span style="color:navy">/// query text for hash</span><br /><span style="color:navy">Property </span><span style="color:black">QueryText </span><span style="color:navy">As %String</span><span style="color:black">(</span><span style="color:navy">MAXLEN </span><span style="color:black">= </span><span style="color:navy">9999</span><span style="color:black">);</span><br /><span style="color:navy">Index </span><span style="color:black">IndHash On Hash [ </span><span style="color:navy">IdKey</span><span style="color:black">, </span><span style="color:navy">Unique </span><span style="color:black">];</span><br /><span style="color:black">}</span></span></span></span>
</p>
<p>
<span style="font-size:11pt"><span style="line-height:107%"><span style=""><span style="color:navy">/// Summary of very low cost SQL query statistics collected in Cache 2017.1 and later. <br /></span><br /><span style="color:navy">/// Refer to documentation on "SQL Statement Details" for information on the source data. <br /></span><br /><span style="color:navy">/// Data is stored by date and time to support queries over time. <br /></span><br /><span style="color:navy">/// Typically run to summarise the SQL query data from the previous day.</span><br /><span style="color:navy">Class DRL.MonitorSQL Extends %Persistent</span><br /><span style="color:black">{</span><br /><span style="color:navy">/// RunDate and RunTime uniquely identify a run</span><br /><span style="color:navy">Property </span><span style="color:black">RunDate </span><span style="color:navy">As %Date</span><span style="color:black">;</span><br /><span style="color:navy">/// Time the capture was started</span><br /><span style="color:navy">/// RunDate and RunTime uniquely identify a run</span><br /><span style="color:navy">Property </span><span style="color:black">RunTime </span><span style="color:navy">As %Time</span><span style="color:black">;</span><br /><br /><span style="color:navy">/// Count of total hits for the time period for </span><br /><span style="color:navy">Property </span><span style="color:black">TotalHits </span><span style="color:navy">As %Integer</span><span style="color:black">;</span><br /><br /><span style="color:navy">/// Sum of pTime</span><br /><span style="color:navy">Property </span><span style="color:black">SumPTime </span><span style="color:navy">As %Numeric</span><span style="color:black">(</span><span style="color:navy">SCALE </span><span style="color:black">= </span><span style="color:navy">4</span><span style="color:black">);</span><br /><br /><span style="color:navy">/// Routine where SQL is found</span><br /><span style="color:navy">Property </span><span style="color:black">RoutineName </span><span style="color:navy">As %String</span><span style="color:black">(</span><span style="color:navy">MAXLEN </span><span style="color:black">= </span><span style="color:navy">1024</span><span style="color:black">);</span><br /><br /><span style="color:navy">/// Hash of query text</span><br /><span style="color:navy">Property </span><span style="color:black">Hash </span><span style="color:navy">As %String</span><span style="color:black">;</span><br /><br /><span style="color:navy">Property </span><span style="color:black">Variance </span><span style="color:navy">As %Numeric</span><span style="color:black">(</span><span style="color:navy">SCALE </span><span style="color:black">= </span><span style="color:navy">4</span><span style="color:black">);</span><br /><br /><span style="color:navy">/// Namespace where queries are run</span><br /><span style="color:navy">Property </span><span style="color:black">Namespace </span><span style="color:navy">As %String</span><span style="color:black">;</span><br /><br /><span style="color:navy">/// Default run will process the previous days data for a single day.</span><br /><span style="color:navy">/// Other date range combinations can be achieved using the Capture method.</span><br /><span style="color:navy">ClassMethod </span><span style="color:black">Run()</span><br /><span style="color:black">{</span><br /> <span style="color:green">//Each run is identified by the start date / time to keep related items together</span><br /> <br /> <span style="color:blue">set </span><span style="color:olive">h</span><span style="color:black">=</span><span style="color:blue">$h</span><span style="color:black">-1</span><br /> <span style="color:blue">do </span><span style="color:black">..</span><span style="color:blue">Capture</span><span style="color:black">(+</span><span style="color:olive">h</span><span style="color:black">,+</span><span style="color:olive">h</span><span style="color:black">)</span><br /><span style="color:black">}</span><br /><br /><span style="color:navy">/// Captures historic statistics for a range of dates</span><br /><span style="color:navy">ClassMethod </span><span style="color:black">Capture(</span><span style="color:fuchsia">dfrom</span><span style="color:black">, </span><span style="color:fuchsia">dto</span><span style="color:black">)</span><br /><span style="color:black">{</span><br /> <span style="color:blue">set </span><span style="color:olive">oldstatsvalue</span><span style="color:black">=</span><span style="color:blue">$system</span><span style="color:teal">.SQL</span><span style="color:black">.</span><span style="color:blue">SetSQLStatsJob</span><span style="color:black">(-1)</span><br /> <br /> <span style="color:blue">set </span><span style="color:olive">currNS</span><span style="color:black">=</span><span style="color:blue">$znspace</span><br /> <span style="color:blue">set </span><span style="color:olive">tSC</span><span style="color:black">=</span><span style="color:navy">##class</span><span style="color:black">(</span><span style="color:teal">%SYS.Namespace</span><span style="color:black">).</span><span style="color:blue">ListAll</span><span style="color:black">(.</span><span style="color:olive">nsArray</span><span style="color:black">)</span><br /> <span style="color:blue">set </span><span style="color:olive">ns</span><span style="color:black">=</span><span style="color:green">""</span><br /> <span style="color:blue">set </span><span style="color:olive">time</span><span style="color:black">=</span><span style="color:blue">$piece</span><span style="color:black">(</span><span style="color:blue">$h</span><span style="color:black">,</span><span style="color:green">","</span><span style="color:black">,2)</span><br /> <span style="color:blue">kill </span><span style="color:black">^||TMP.MonitorSQL</span><br /> <span style="color:blue">do </span><span style="color:purple">{</span><br /> <span style="color:blue">set </span><span style="color:olive">ns</span><span style="color:black">=</span><span style="color:blue">$o</span><span style="color:black">(</span><span style="color:olive">nsArray</span><span style="color:black">(</span><span style="color:olive">ns</span><span style="color:black">))</span><br /> <span style="color:blue">quit</span><span style="color:black">:</span><span style="color:olive">ns</span><span style="color:black">=</span><span style="color:green">""</span><br /> <span style="color:blue">use </span><span style="color:black">0 </span><span style="color:blue">write </span><span style="color:black">!,</span><span style="color:green">"processing namespace "</span><span style="color:black">,</span><span style="color:olive">ns</span><br /> <span style="color:blue">zn </span><span style="color:olive">ns</span><br /> <span style="color:blue">for </span><span style="color:maroon">dateh</span><span style="color:black">=</span><span style="color:fuchsia">dfrom</span><span style="color:black">:1:</span><span style="color:fuchsia">dto </span><span style="color:purple">{</span><br /> <span style="color:blue">set </span><span style="color:maroon">hash</span><span style="color:black">=</span><span style="color:green">""</span><br /> <span style="color:blue">set </span><span style="color:olive">purgedun</span><span style="color:black">=0</span><br /> <span style="color:blue">do </span><span style="color:purple">{</span><br /> <span style="color:blue">set </span><span style="color:maroon">hash</span><span style="color:black">=</span><span style="color:blue">$order</span><span style="color:black">(^rINDEXSQL(</span><span style="color:green">"sqlidx"</span><span style="color:black">,1,</span><span style="color:maroon">hash</span><span style="color:black">))</span><br /> <span style="color:blue">continue</span><span style="color:black">:</span><span style="color:maroon">hash</span><span style="color:black">=</span><span style="color:green">""</span><br /> <span style="color:blue">set </span><span style="color:olive">stats</span><span style="color:black">=</span><span style="color:blue">$get</span><span style="color:black">(^rINDEXSQL(</span><span style="color:green">"sqlidx"</span><span style="color:black">,1,</span><span style="color:maroon">hash</span><span style="color:black">,</span><span style="color:green">"stat"</span><span style="color:black">,</span><span style="color:maroon">dateh</span><span style="color:black">))</span><br /> <span style="color:blue">continue</span><span style="color:black">:</span><span style="color:olive">stats</span><span style="color:black">=</span><span style="color:green">""</span><br /> <span style="color:blue">set </span><span style="color:black">^||TMP.MonitorSQL(</span><span style="color:maroon">dateh</span><span style="color:black">,</span><span style="color:olive">ns</span><span style="color:black">,</span><span style="color:maroon">hash</span><span style="color:black">)=</span><span style="color:olive">stats</span><br /> <br /> <span style="color:purple">&SQL(</span><span style="color:blue">SELECT </span><span style="color:green">Location </span><span style="color:navy">into </span><span style="color:maroon">:tLocation </span><span style="color:navy">FROM </span><span style="color:green">INFORMATION_SCHEMA</span><span style="color:black">.</span><span style="color:green">STATEMENT_LOCATIONS </span><span style="color:navy">WHERE </span><span style="color:green">Statement</span><span style="color:black">=</span><span style="color:maroon">:hash</span><span style="color:purple">)</span><br /> <span style="color:blue">if </span><span style="color:maroon">SQLCODE</span><span style="color:black">'=0 </span><span style="color:blue">set </span><span style="color:olive">Location</span><span style="color:black">=</span><span style="color:green">""</span><br /> <span style="color:blue">set </span><span style="color:black">^||TMP.MonitorSQL(</span><span style="color:maroon">dateh</span><span style="color:black">,</span><span style="color:olive">ns</span><span style="color:black">,</span><span style="color:maroon">hash</span><span style="color:black">,</span><span style="color:green">"Location"</span><span style="color:black">)=</span><span style="color:maroon">tLocation</span><br /> <br /> <span style="color:purple">&SQL(</span><span style="color:blue">SELECT </span><span style="color:green">Statement </span><span style="color:navy">INTO </span><span style="color:maroon">:Statement </span><span style="color:navy">FROM </span><span style="color:green">INFORMATION_SCHEMA</span><span style="color:black">.</span><span style="color:green">STATEMENTS </span><span style="color:navy">WHERE </span><span style="color:green">Hash</span><span style="color:black">=</span><span style="color:maroon">:hash</span><span style="color:purple">)</span><br /> <span style="color:blue">if </span><span style="color:maroon">SQLCODE</span><span style="color:black">'=0 </span><span style="color:blue">set </span><span style="color:maroon">Statement</span><span style="color:black">=</span><span style="color:green">""</span><br /> <span style="color:blue">set </span><span style="color:black">^||TMP.MonitorSQL(</span><span style="color:maroon">dateh</span><span style="color:black">,</span><span style="color:olive">ns</span><span style="color:black">,</span><span style="color:maroon">hash</span><span style="color:black">,</span><span style="color:green">"QueryText"</span><span style="color:black">)=</span><span style="color:maroon">Statement</span><br /> <span style="color:purple">} </span><span style="color:blue">while </span><span style="color:maroon">hash</span><span style="color:black">'=</span><span style="color:green">""</span><br /> <br /> <span style="color:purple">}</span><br /> <span style="color:purple">} </span><span style="color:blue">while </span><span style="color:olive">ns</span><span style="color:black">'=</span><span style="color:green">""</span><br /> <span style="color:blue">zn </span><span style="color:olive">currNS</span><br /> <span style="color:blue">set </span><span style="color:maroon">dateh</span><span style="color:black">=</span><span style="color:green">""</span><br /> <span style="color:blue">do </span><span style="color:purple">{</span><br /> <span style="color:blue">set </span><span style="color:maroon">dateh</span><span style="color:black">=</span><span style="color:blue">$o</span><span style="color:black">(^||TMP.MonitorSQL(</span><span style="color:maroon">dateh</span><span style="color:black">))</span><br /> <span style="color:blue">quit</span><span style="color:black">:</span><span style="color:maroon">dateh</span><span style="color:black">=</span><span style="color:green">""</span><br /> <span style="color:blue">set </span><span style="color:olive">ns</span><span style="color:black">=</span><span style="color:green">""</span><br /> <span style="color:blue">do </span><span style="color:purple">{</span><br /> <span style="color:blue">set </span><span style="color:olive">ns</span><span style="color:black">=</span><span style="color:blue">$o</span><span style="color:black">(^||TMP.MonitorSQL(</span><span style="color:maroon">dateh</span><span style="color:black">,</span><span style="color:olive">ns</span><span style="color:black">))</span><br /> <span style="color:blue">quit</span><span style="color:black">:</span><span style="color:olive">ns</span><span style="color:black">=</span><span style="color:green">""</span><br /> <span style="color:blue">set </span><span style="color:maroon">hash</span><span style="color:black">=</span><span style="color:green">""</span><br /> <span style="color:blue">do </span><span style="color:purple">{</span><br /> <span style="color:blue">set </span><span style="color:maroon">hash</span><span style="color:black">=</span><span style="color:blue">$o</span><span style="color:black">(^||TMP.MonitorSQL(</span><span style="color:maroon">dateh</span><span style="color:black">,</span><span style="color:olive">ns</span><span style="color:black">,</span><span style="color:maroon">hash</span><span style="color:black">))</span><br /> <span style="color:blue">quit</span><span style="color:black">:</span><span style="color:maroon">hash</span><span style="color:black">=</span><span style="color:green">""</span><br /> <span style="color:blue">set </span><span style="color:olive">stats</span><span style="color:black">=</span><span style="color:blue">$g</span><span style="color:black">(^||TMP.MonitorSQL(</span><span style="color:maroon">dateh</span><span style="color:black">,</span><span style="color:olive">ns</span><span style="color:black">,</span><span style="color:maroon">hash</span><span style="color:black">))</span><br /> <span style="color:blue">continue</span><span style="color:black">:</span><span style="color:olive">stats</span><span style="color:black">=</span><span style="color:green">""</span><br /> <span style="color:green">// The first time through the loop delete all statistics for the day so it is re-runnable</span><br /> <span style="color:green">// But if we run for a day after the raw data has been purged, it will wreck eveything</span><br /> <span style="color:green">// so do it here, where we already know there are results to insert in their place.</span><br /> <span style="color:blue">if </span><span style="color:olive">purgedun</span><span style="color:black">=0 </span><span style="color:purple">{</span><br /> <span style="color:purple">&SQL(</span><span style="color:blue">DELETE </span><span style="color:navy">FROM </span><span style="color:green">websys</span><span style="color:black">.</span><span style="color:green">MonitorSQL </span><span style="color:navy">WHERE </span><span style="color:green">RunDate</span><span style="color:black">=</span><span style="color:maroon">:dateh </span><span style="color:purple">)</span><br /> <span style="color:blue">set </span><span style="color:olive">purgedun</span><span style="color:black">=1</span><br /> <span style="color:purple">}</span><br /> <br /> <span style="color:blue">set </span><span style="color:olive">tObj</span><span style="color:black">=</span><span style="color:navy">##class</span><span style="color:black">(</span><span style="color:teal">DRL.MonitorSQL</span><span style="color:black">).</span><span style="color:blue">%New</span><span style="color:black">()</span><br /><br /> <span style="color:blue">set </span><span style="color:olive">tObj</span><span style="color:black">.</span><span style="color:blue">Namespace</span><span style="color:black">=</span><span style="color:olive">ns</span><br /> <span style="color:blue">set </span><span style="color:olive">tObj</span><span style="color:black">.</span><span style="color:blue">RunDate</span><span style="color:black">=</span><span style="color:maroon">dateh</span><br /> <span style="color:blue">set </span><span style="color:olive">tObj</span><span style="color:black">.</span><span style="color:blue">RunTime</span><span style="color:black">=</span><span style="color:olive">time</span><br /> <span style="color:blue">set </span><span style="color:olive">tObj</span><span style="color:black">.</span><span style="color:blue">Hash</span><span style="color:black">=</span><span style="color:maroon">hash</span><br /> <span style="color:blue">set </span><span style="color:olive">tObj</span><span style="color:black">.</span><span style="color:blue">TotalHits</span><span style="color:black">=</span><span style="color:blue">$listget</span><span style="color:black">(</span><span style="color:olive">stats</span><span style="color:black">,1)</span><br /> <span style="color:blue">set </span><span style="color:olive">tObj</span><span style="color:black">.</span><span style="color:blue">SumPTime</span><span style="color:black">=</span><span style="color:blue">$listget</span><span style="color:black">(</span><span style="color:olive">stats</span><span style="color:black">,2)</span><br /> <span style="color:blue">set </span><span style="color:olive">tObj</span><span style="color:black">.</span><span style="color:blue">Variance</span><span style="color:black">=</span><span style="color:blue">$listget</span><span style="color:black">(</span><span style="color:olive">stats</span><span style="color:black">,3)</span><br /> <span style="color:blue">set </span><span style="color:olive">tObj</span><span style="color:black">.</span><span style="color:blue">Variance</span><span style="color:black">=</span><span style="color:blue">$listget</span><span style="color:black">(</span><span style="color:olive">stats</span><span style="color:black">,3)</span><br /> <br /> <span style="color:blue">set </span><span style="color:olive">queryText</span><span style="color:black">=^||TMP.MonitorSQL(</span><span style="color:maroon">dateh</span><span style="color:black">,</span><span style="color:olive">ns</span><span style="color:black">,</span><span style="color:maroon">hash</span><span style="color:black">,</span><span style="color:green">"QueryText"</span><span style="color:black">)</span><br /> <span style="color:blue">set </span><span style="color:olive">tObj</span><span style="color:black">.</span><span style="color:blue">RoutineName</span><span style="color:black">=^||TMP.MonitorSQL(</span><span style="color:maroon">dateh</span><span style="color:black">,</span><span style="color:olive">ns</span><span style="color:black">,</span><span style="color:maroon">hash</span><span style="color:black">,</span><span style="color:green">"Location"</span><span style="color:black">)</span><br /> <br /> <span style="color:purple">&SQL(</span><span style="color:blue">Select </span><span style="color:green">ID </span><span style="color:navy">into </span><span style="color:maroon">:TextID </span><span style="color:navy">from </span><span style="color:green">DRL</span><span style="color:black">.</span><span style="color:green">MonitorSQLText </span><span style="color:navy">where </span><span style="color:green">Hash</span><span style="color:black">=</span><span style="color:maroon">:hash</span><span style="color:purple">)</span><br /> <span style="color:blue">if </span><span style="color:maroon">SQLCODE</span><span style="color:black">'=0 </span><span style="color:purple">{</span><br /> <span style="color:blue">set </span><span style="color:olive">textref</span><span style="color:black">=</span><span style="color:navy">##class</span><span style="color:black">(</span><span style="color:teal">DRL.MonitorSQLText</span><span style="color:black">).</span><span style="color:blue">%New</span><span style="color:black">()</span><br /> <span style="color:blue">set </span><span style="color:olive">textref</span><span style="color:black">.</span><span style="color:blue">Hash</span><span style="color:black">=</span><span style="color:olive">tObj</span><span style="color:black">.</span><span style="color:blue">Hash</span><br /> <span style="color:blue">set </span><span style="color:olive">textref</span><span style="color:black">.</span><span style="color:blue">QueryText</span><span style="color:black">=</span><span style="color:olive">queryText</span><br /> <span style="color:blue">set </span><span style="color:olive">sc</span><span style="color:black">=</span><span style="color:olive">textref</span><span style="color:black">.</span><span style="color:blue">%Save</span><span style="color:black">()</span><br /> <span style="color:purple">} </span><br /> <span style="color:blue">set </span><span style="color:olive">tSc</span><span style="color:black">=</span><span style="color:olive">tObj</span><span style="color:black">.</span><span style="color:blue">%Save</span><span style="color:black">()</span><br /> <br /> <span style="color:green">//avoid dupicating the query text in each record because it can be very long. Use a lookup</span><br /> <span style="color:green">//table keyed on the hash. If it doesn't exist add it.</span><br /> <span style="color:blue">if $$$ISERR</span><span style="color:black">(</span><span style="color:olive">tSc</span><span style="color:black">) </span><span style="color:blue">do $system</span><span style="color:teal">.OBJ</span><span style="color:black">.</span><span style="color:blue">DisplayError</span><span style="color:black">(</span><span style="color:olive">tSc</span><span style="color:black">)</span><br /> <br /><br /> <span style="color:blue">if $$$ISERR</span><span style="color:black">(</span><span style="color:olive">tSc</span><span style="color:black">) </span><span style="color:blue">do $system</span><span style="color:teal">.OBJ</span><span style="color:black">.</span><span style="color:blue">DisplayError</span><span style="color:black">(</span><span style="color:olive">tSc</span><span style="color:black">)</span><br /> <span style="color:purple">} </span><span style="color:blue">while </span><span style="color:maroon">hash</span><span style="color:black">'=</span><span style="color:green">""</span><br /> <span style="color:purple">} </span><span style="color:blue">while </span><span style="color:olive">ns</span><span style="color:black">'=</span><span style="color:green">""</span><br /> <br /> <span style="color:purple">} </span><span style="color:blue">while </span><span style="color:maroon">dateh</span><span style="color:black">'=</span><span style="color:green">""</span><br /> <br /> <br /> <br /> <span style="color:blue">do $system</span><span style="color:teal">.SQL</span><span style="color:black">.</span><span style="color:blue">SetSQLStatsJob</span><span style="color:black">(0)</span><br /><span style="color:black">}</span><br /><br /><span style="color:navy">Query </span><span style="color:black">Export(</span><span style="color:fuchsia">RunDateH1 </span><span style="color:navy">As %Date</span><span style="color:black">, </span><span style="color:fuchsia">RunDateH2 </span><span style="color:navy">As %Date</span><span style="color:black">) </span><span style="color:navy">As %SQLQuery</span><br /><span style="color:black">{</span><br /><span style="color:blue">SELECT </span><span style="color:green">S</span><span style="color:black">.</span><span style="color:green">Hash</span><span style="color:black">,</span><span style="color:green">RoutineName</span><span style="color:black">,</span><span style="color:green">RunDate</span><span style="color:black">,</span><span style="color:green">RunTime</span><span style="color:black">,</span><span style="color:green">SumPTime</span><span style="color:black">,</span><span style="color:green">TotalHits</span><span style="color:black">,</span><span style="color:green">Variance</span><span style="color:black">,</span><span style="color:green">RoutineName</span><span style="color:black">,</span><span style="color:green">T</span><span style="color:black">.</span><span style="color:green">QueryText</span><br /> <span style="color:navy">FROM DRL.</span><span style="color:green">MonitorSQL S </span><span style="color:navy">LEFT JOIN DRL.</span><span style="color:green">MonitorSQLText T </span><span style="color:navy">on </span><span style="color:green">S</span><span style="color:black">.</span><span style="color:green">Hash</span><span style="color:black">=</span><span style="color:green">T</span><span style="color:black">.</span><span style="color:green">Hash</span><br /> <span style="color:navy">WHERE </span><span style="color:green">RunDate</span><span style="color:black">>=</span><span style="color:maroon">:RunDateH1 </span><span style="color:black">AND </span><span style="color:green">RunDate</span><span style="color:black"><=</span><span style="color:maroon">:RunDateH2</span><br /><span style="color:black">}</span><br /><span style="color:black">}</span></span></span></span>
</p>
<p>
</p>