第八章 解释SQL查询计划（二）

SQL语句的详细信息

有两种方式显示SQL语句的详细信息:

• 在SQL Statements选项卡中，通过单击左侧列中的Table/View/Procedure Name链接选择一个SQL Statement。
  这将在单独的选项卡中显示SQL语句详细信息。
  该界面允许打开多个选项卡进行比较。
  它还提供了一个Query Test按钮，用于显示SQL Runtime Statistics页面。
• 从表的Catalog Details选项卡(或SQL Statements选项卡)中，通过单击右边列中的Statement Text链接选择一个SQL语句。
  这将在弹出窗口中显示SQL语句详细信息。

可以使用“SQL语句详细信息”显示来查看查询计划，并冻结或解冻查询计划。

“SQL语句详细信息”提供冻结或解冻查询计划的按钮。
它还提供了一个Clear SQL Statistics按钮来清除性能统计，一个Export按钮来将一个或多个SQL语句导出到一个文件，以及一个Refresh和Close页面按钮。

SQL语句详细信息显示包含以下部分。
每个部分都可以通过选择部分标题旁边的箭头图标展开或折叠:

• 语句详细信息，其中包括性能统计
• 编译设置
• 语句在以下例程中定义
• 语句使用如下关系
• 语句文本和查询计划(在其他地方描述)

声明的细节部分

• 语句散列Statement hash:语句定义的内部散列表示形式，用作SQL语句索引的键(仅供内部使用)。
  有时，看起来相同的SQL语句可能具有不同的语句散列项。
  需要生成不同SQL语句的代码的设置/选项的任何差异都会导致不同的语句散列。
  这可能发生在支持不同内部优化的不同客户端版本或不同平台上。
• 时间戳Timestamp:最初，创建计划时的时间戳。
  这个时间戳会在冻结/解冻之后更新，以记录计划解冻的时间，而不是重新编译计划的时间。
  可能必须单击Refresh Page按钮来显示解冻时间戳。
  将Plan Timestamp与包含该语句的例程/类的datetime值进行比较，可以知道，如果再次编译该例程/类，它是否使用了相同的查询计划。
• 版本Version:创建计划的InterSystems IRIS版本。
  如果“计划”状态是“冻结/升级”，则这是InterSystems IRIS的早期版本。
  解冻查询计划时，“计划”状态变为“解冻”，“版本”变为当前的InterSystems IRIS版本。
• 计划状态Plan state:冻结/显式、冻结/升级、解冻、解冻/并行。
  Frozen/Explicit意味着该语句的计划已被显式用户操作冻结，无论生成此SQL语句的代码发生了什么变化，该冻结的计划都将是将要使用的查询计划。
  冻结/升级意味着该语句的计划已被InterSystems IRIS版本升级自动冻结。
  解冻意味着该计划目前处于解冻状态，可能被冻结。
  Unfrozen/Parallel表示该计划被解冻，并使用%Parallel处理，因此不能被冻结。
  NULL(空白)计划状态意味着没有关联的查询计划。
• 自然查询Natural query:一个布尔标志，指示该查询是否是“自然查询”。
  如果勾选此项，则该查询是自然查询，不会记录查询性能统计信息。
  如果不检查，性能统计可能会被记录;
  其他因素决定了统计数据是否真正被记录下来。
  自然查询被定义为嵌入式SQL查询，它非常简单，记录统计数据的开销会影响查询性能。
  将统计信息保存在自然查询上没有任何好处，因为查询已经非常简单了。
  一个很好的自然查询示例是SELECT Name INTO:n FROM Table WHERE %ID=?
  这个查询的WHERE子句是一个相等条件。
  此查询不涉及任何循环或任何索引引用。
  动态SQL查询(缓存查询)不会被标记为自然查询;
  缓存查询的统计数据可能被记录，也可能不被记录。
• 冻结计划不同Frozen plan different:冻结计划时，会显示该字段，显示冻结的计划与未冻结的计划是否不同。
  冻结计划时，语句文本和查询计划将并排显示冻结的计划和未冻结的计划，以便进行比较。

本节还包括五个查询性能统计字段，将在下一节中进行描述。

性能统计数据

执行查询会将性能统计数据添加到相应的SQL语句。
此信息可用于确定哪些查询执行得最慢，哪些查询执行得最多。
通过使用这些信息，您可以确定哪些查询将通过优化提供显著的好处。

除了SQL语句名称、计划状态、位置和文本之外，还为缓存查询提供了以下附加信息:
- 计数Count:运行此查询次数的整数计数。
如果对该查询产生不同的查询计划(例如向表中添加索引)，则将重置该计数。
- 平均计数Average count:每天运行此查询的平均次数。
- 总时间Total time:运行此查询所花费的时间(以秒为单位)。
- 平均时间Average time:运行此查询所花费的平均时间(以秒为单位)。
如果查询是缓存的查询，则查询的第一次执行所花费的时间很可能比从查询缓存中执行优化后的查询所花费的时间要多得多。
- 标准差Standard deviation:总时间和平均时间的标准差。
只运行一次的查询的标准偏差为0。
运行多次的查询通常比只运行几次的查询具有更低的标准偏差。
- 第一次看到的日期Date first seen:查询第一次运行(执行)的日期。
这可能与Last Compile Time不同，后者是准备查询的时间。

UpdateSQLStats任务会定期更新已完成的查询执行的查询性能统计数据。
这将最小化维护这些统计信息所涉及的开销。
因此，当前运行的查询不会出现在查询性能统计中。
最近完成的查询(大约在最近一个小时内)可能不会立即出现在查询性能统计中。

可以使用Clear SQL Statistics按钮清除这6个字段的值。

InterSystems IRIS不单独记录%PARALLEL子查询的性能统计数据。
%PARALLEL子查询统计信息与外部查询的统计信息相加。
由并行运行的实现生成的查询没有单独跟踪其性能统计信息。

InterSystems IRIS不记录“自然”查询的性能统计数据。
如果系统收集了统计信息，则会降低查询性能，而自然查询已经是最优的，因此没有进行优化的可能。

可以在“SQL语句”选项卡显示中查看多个SQL语句的查询性能统计信息。
您可以按任何列对SQL Statements选项卡列表进行排序。
这使得很容易确定，例如，哪个查询具有最大的平均时间。

还可以通过查询INFORMATION.SCHEMA.STATEMENTS类属性来访问这些查询性能统计数据，如查询SQL语句中所述。

编译设置部分

• 选择模式Select mode:编译语句时使用的SelectMode。
  对于DML命令，可以使用#SQLCompile Select;
  默认为Logical。
  如果#SQLCompile Select=Runtime，调用$SYSTEM.SQL.Util.SetOption()方法的SelectMode选项可以改变查询结果集的显示，但不会改变SelectMode值，它仍然是Runtime。
• 默认模式Default schema(s):编译语句时设置的默认模式名。
  这通常是在发出命令时生效的默认模式，尽管SQL可能使用模式搜索路径(如果提供的话)而不是默认模式名来解析非限定名称的模式。
  但是，如果该语句是嵌入式SQL中使用一个或多个#Import宏指令的DML命令，则#Import指令指定的模式将在这里列出。
• 模式路径Schema path:编译语句时定义的模式路径。
  如果指定，这是模式搜索路径。
  如果没有指定架构搜索路径，则此设置为空。
  但是，对于在#Import宏指令中指定搜索路径的DML Embedded SQL命令，#Import搜索路径显示在默认模式设置中，并且该模式路径设置为空白。
• 计划错误Plan Error:该字段仅在使用冻结计划时发生错误时出现。
  例如，如果一个查询计划使用一个索引，则该查询计划被冻结，然后该索引从表中删除，就会出现如下的计划错误:Map 'NameIDX' not defined in table 'Sample.Person', but it was specified in the frozen plan for the query.
  删除或添加索引将导致重新编译表，从而更改“最后编译时间”值。
  一旦导致错误的条件得到纠正，Clear Error按钮可用于清除Plan Error字段——例如，通过重新创建缺失的索引。
  在错误条件被纠正后使用“清除错误”按钮会导致“计划错误”字段和“清除错误”按钮消失。

例程和关系部分

语句在以下例程部分中定义：
- 例程Routine：与缓存查询关联的类名(对于动态SQL DML)，或者例程名(对于嵌入式SQL DML)。
- 类型：类方法或MAC例程(对于嵌入式SQL DML)。
- 上次编译时间Last Compile Time：例程的上次编译时间或准备时间。如果SQL语句解冻，重新编译MAC例程会同时更新此时间戳和Plan时间戳。如果SQL语句已冻结，则重新编译MAC例程仅更新此时间戳；在您解冻计划之前，Plan时间戳不会更改；然后Plan时间戳将显示计划解冻的时间。

语句使用以下关系部分列出了一个或多个用于创建查询计划的定义表。对于使用查询从另一个表提取值的INSERT，或者使用FROM子句引用另一个表的UPDATE或DELETE，这两个表都在此处列出。每个表都列出了下列值：

• 表或视图名称Table or View Name：表或视图的限定名称。
• 类型Type：表或视图。
• 上次编译时间Last Compile Time：表(持久化类)上次编译的时间。
• Classname：与表关联的类名。

本节包括用于重新编译类的编译类选项。如果重新编译解冻计划，则所有三个时间字段都会更新。如果重新编译冻结的计划，则会更新两个上次编译时间字段，但不会更新计划时间戳。解冻计划并单击刷新页面按钮后，计划时间戳将更新为计划解冻的时间。

查询SQL语句

可以使用SQLTableStatements()存储查询返回指定表的SQL语句。下面的示例显示了这一点：

/// w ##class(PHA.TEST.SQL).SQLTableStatements()
ClassMethod SQLTableStatements()
{
    SET mycall = "CALL %Library.SQLTableStatements('Sample','Person')"
    SET tStatement = ##class(%SQL.Statement).%New()
    SET qStatus=tStatement.%Prepare(mycall)
    IF qStatus'=1 {WRITE "%Prepare failed:" DO $System.Status.DisplayError(qStatus) QUIT}
    SET rset=tStatement.%Execute()
    IF rset.%SQLCODE '= 0 {WRITE "SQL error=",rset.%SQLCODE QUIT}
    DO rset.%Display()
}

DHC-APP>w ##class(PHA.TEST.SQL).SQLTableStatements()


Dumping result #1
SCHEMA  RELATION_NAME   PLAN_STATE      LOCATION        STATEMENT
SAMPLE  PERSON  0       %sqlcq.DHCdAPP.cls228.1 DECLARE C CURSOR FOR SELECT * INTO :%col(1) , :%col(2) , :%col(3) , :%col(4) , :%col(5) , :%col(6) , :%col(7) , :%col(8) , :%col(9) , :%col(10) , :%col(11) , :%col(12) , :%col(13) , :%col(14) , :%col(15) FROM SAMPLE . PERSON
SAMPLE  PERSON  0       Sample.Person.1 SELECT AGE , DOB , FAVORITECOLORS , HOME , NAME , OFFICE , SSN , SPOUSE , X__CLASSNAME , HOME_CITY , HOME_STATE , HOME_STREET , HOME_ZIP , OFFICE_CITY , OFFICE_STATE , OFFICE_STREET , OFFICE_ZIP INTO :%e ( ) FROM %IGNOREINDEX * SAMPLE . PERSON WHERE ID = :%rowid
...
CURSOR FOR SELECT P . NAME , P . AGE , E . NAME , E . AGE FROM %ALLINDEX SAMPLE . PERSON AS P LEFT OUTER JOIN SAMPLE . EMPLOYEE AS E ON P . NAME = E . NAME WHERE P . AGE > 21 AND %NOINDEX E . AGE < 65
SAMPLE  PERSON  0       PHA.TEST.SQL.1  SELECT NAME , SPOUSE INTO :name , :spouse FROM SAMPLE . PERSON WHERE SPOUSE IS NULL
SAMPLE  PERSON  0       PHA.TEST.ObjectScript.1 SELECT NAME , DOB , HOME INTO :n , :d , :h FROM SAMPLE . PERSON

70 Rows(s) Affected

可以使用INFORMATION_SCHEMA包表来查询SQL语句列表。InterSystems IRIS支持以下类：
- INFORMATION_SCHEMA.STATEMENTS：包含当前名称空间中的当前用户可以访问的SQL语句索引项。
- INFORMATION_SCHEMA.STATEMENT_LOCATIONS：包含调用SQL语句的每个例程位置：持久类名或缓存查询名。
- INFORMATION_SCHEMA.STATEMENT_RELATIONS：包含SQL语句使用的每个表或视图条目。

以下是使用这些类的一些示例查询：

下面的示例返回命名空间中的所有SQL语句，列出哈希值(唯一标识规范化SQL语句的计算ID)、冻结状态标志(值0到3)、准备语句和保存计划时的本地时间戳以及语句文本本身：

SELECT Hash,Frozen,Timestamp,Statement FROM INFORMATION_SCHEMA.STATEMENTS

以下示例返回所有冻结计划的SQL语句，指示冻结的计划是否与未冻结的计划不同。请注意，解冻语句可以是Frozen=0或Frozen=3。不能冻结的单行INSERT等语句在冻结列中显示NULL：

SELECT Frozen,FrozenDifferent,Timestamp,Statement FROM INFORMATION_SCHEMA.STATEMENTS
WHERE Frozen=1 OR Frozen=2

以下示例返回给定SQL表的所有SQL语句和语句所在的例程。(请注意，指定表名(SAMPLE.PERSON)时必须使用与SQL语句文本中相同的字母大小写：全部大写字母)：

SELECT Statement,Frozen,STATEMENT_LOCATIONS->Location AS Routine,STATEMENT_LOCATIONS->Type AS RoutineType
       FROM INFORMATION_SCHEMA.STATEMENTS 
       WHERE STATEMENT_RELATIONS->Relation='SAMPLE.PERSON'

以下示例返回当前命名空间中具有冻结计划的所有SQL语句：

SELECT Statement,Frozen,Frozen_Different,STATEMENT_LOCATIONS->Location AS Routine,STATEMENT_LOCATIONS->Type AS RoutineType
       FROM INFORMATION_SCHEMA.STATEMENTS 
       WHERE Frozen=1 OR Frozen=2

以下示例返回当前命名空间中包含COUNT(*)聚合函数的所有SQL语句。(请注意，指定语句文本(COUNT(*))时必须使用与SQL语句文本相同的空格)：

       SELECT Statement,Frozen,STATEMENT_LOCATIONS->Location AS Routine,STATEMENT_LOCATIONS->Type AS RoutineType
       FROM INFORMATION_SCHEMA.STATEMENTS 
       WHERE Statement [ ' COUNT ( * )

导出和导入SQL语句

可以将SQL语句作为XML格式的文本文件导出或导入。这使可以将冻结的计划从一个位置移动到另一个位置。SQL语句导出和导入包括关联的查询计划。

可以导出单个SQL语句，也可以导出命名空间中的所有SQL语句。

可以导入先前导出的包含一个或多个SQL语句的XML文件。

注意：将SQL语句作为XML导入不应与从文本文件导入和执行SQL DDL代码混淆。

导出SQL语句

导出单个SQL语句：

• 使用SQL语句详细资料页导出按钮。在管理门户系统资源管理器SQL界面中，选择SQL语句选项卡，然后单击语句以打开SQL语句详细信息页。选择导出按钮。这将打开一个对话框，允许选择将文件导出到服务器(数据文件)或浏览器。

  • 服务器(默认)：输入导出XML文件的完整路径名。第一次导出时，此文件的默认名称为statementexport.xml。当然，可以指定不同的路径和文件名。成功导出SQL语句文件后，上次使用的文件名将成为默认值。

  默认情况下，未选中在后台运行导出复选框。

  • Browser：将文件statementexport.xml导出到用户默认浏览器中的新页面。可以为浏览器导出文件指定其他名称，或指定其他软件显示选项。
• 使用$SYSTEM.SQL.Statement.ExportFrozenPlans()方法。

导出命名空间中的所有SQL语句：

• 使用管理门户中的导出所有对帐单操作。从管理门户系统资源管理器SQL界面中，选择操作下拉列表。从该列表中选择Export all Statements。这将打开一个对话框，允许您将命名空间中的所有SQL语句导出到服务器(数据文件)或浏览器。
  • 服务器(默认)：输入导出XML文件的完整路径名。第一次导出时，此文件的默认名称为statementexport.xml。当然，可以指定不同的路径和文件名。成功导出SQL语句文件后，上次使用的文件名将成为默认值。

默认情况下，在后台运行导出复选框处于选中状态。这是导出所有SQL语句时的建议设置。选中在后台运行导出时，系统会为提供一个查看后台列表页面的链接，可以在该页面中查看后台作业状态。

• Browser：将文件statementexport.xml导出到用户默认浏览器中的新页面。可以为浏览器导出文件指定其他名称，或指定其他软件显示选项。

使用$SYSTEM.SQL.Statement.ExportAllFrozenPlans()方法。

导入SQL语句

从先前导出的文件导入一条或多条SQL语句：

• 使用管理门户中的导入对帐单操作。从管理门户系统资源管理器SQL界面中，选择操作下拉列表。从该列表中选择Import Statements。这将打开一个对话框，允许指定导入XML文件的完整路径名。

默认情况下，在后台运行导入复选框处于选中状态。这是导入SQL语句文件时的推荐设置。选中在后台运行导入时，系统会为您提供一个查看后台列表页面的链接，可以在该页面中查看后台作业状态。

使用$SYSTEM.SQL.Statement.ImportFrozenPlans()方法。

查看和清除后台任务

在管理门户系统操作选项中，选择后台任务，查看导出和导入后台任务的日志。可以使用清除日志按钮清除此日志。