通过例子和解析计划，本文展示了在Microsoft SQL Server上提高查询效率有效的一些技巧。在编程中有很多小提示和技巧。了解这些技巧可以扩展你在性能优化上的可用机能。在这部分里我们所有的例子都选择使用Microsoft SHOWPLAN_ALL输出，因为它更紧凑并且展示典型的信息。（Sybase的查询计划基本与此相同，可能包含其它一些信息）大部分的例子都是要么基于PUBS数据库，要么基于标准系统表的。我们在PUBS数据库中对用到的表进行了很大扩充，对很多表增加了好几万行。

      子查询优化

      一条好的值得称赞的规则是尽量用连接代替所有的子查询。优化器有时可以自动将子查询“扁平化”，并且用常规或外连接代替。但那样也不总是有效。明确的连接对选择表的顺序和找到最可能的计划给出了更多的选项。当你优化一个特殊查询时，了解一下是否去掉自查询可产生很大的差异。

      示例
      下面查询选择了pubs数据库中所有表的名字，以及每个表的聚集索引（如果存在）。如果没有聚集索引，表名仍然显示在列表中，在聚集索引列中显示为虚线。两个查询返回同样的结果集，但第一个使用了一个子查询，而第二个使用一个外连接时。比较Microsoft SQL Server产生的查询计划

SUBQUERY SOLUTION       ----------------------       SQL Server parse and compile time:           CPU time = 28 ms           elapsed time = 28 ms       SQL Server Execution Times:           CPU time = 145 ms           elapsed time = 145 ms       Table 'big_sales'. Scan count 14, logical reads       1884, physical reads 0, read-ahead reads 0.       Table 'stores'. Scan count 12, logical reads 24,       physical reads 0, read-ahead reads 0.

JOIN SOLUTION ---------------------- SQL Server parse and compile time:     CPU time = 50 ms     elapsed time = 54 ms SQL Server Execution Times:     CPU time = 109 ms     elapsed time = 109 ms Table 'big_sales'. Scan count 14, logical reads 966, physical reads 0, read-ahead reads 0. Table 'stores'. Scan count 12, logical reads 24, physical reads 0, read-ahead reads 0.

      不必更深探索，我们可以看到在CPU和总的实耗时间方面连接更快，仅需要子查询方案逻辑读的一半。此外，这两种情况伴随着相同的结果集，虽然排序的顺序不同，这是因为连接查询（由于它的GROUP BY子句）有一个隐含的ORDER BY:

Store Books Sold       -------------------------------------------------       Barnum's 154125       Bookbeat 518080       Doc-U-Mat: Quality Laundry and Books 581130       Eric the Read Books 76931       Fricative Bookshop 259060       News & Brews 161090       (6 row(s) affected)       Store Books Sold       -------------------------------------------------       Eric the Read Books 76931       Barnum's 154125       News & Brews 161090       Doc-U-Mat: Quality Laundry and Books 581130       Fricative Bookshop 259060       Bookbeat 518080       (6 row(s) affected)

|--Compute Scalar(DEFINE:([Expr1006]=isnull([Expr1004], 0)))       |--Nested Loops(Left Outer Join, OUTER REFERENCES:([st].[stor_id]))       |--Nested Loops(Inner Join, OUTER REFERENCES:([big_sales].[stor_id]))          | |--Stream Aggregate(GROUP BY:([big_sales].[stor_id]))             | | |--Clustered Index Scan(OBJECT:([pubs].[dbo].[big_sales].             [UPKCL_big_sales]), ORDERED FORWARD)          | |--Clustered Index Seek(OBJECT:([pubs].[dbo].[stores].[UPK_storeid]       AS [st]),       SEEK:([st].[stor_id]=[big_sales].[stor_id]) ORDERED FORWARD)        |--Stream Aggregate(DEFINE:([Expr1004]=SUM([bs].[qty])))       |--Clustered Index Seek(OBJECT:([pubs].[dbo].[big_sales].         [UPKCL_big_sales] AS [bs]),       SEEK:([bs].[stor_id]=[st].[stor_id]) ORDERED FORWARD)

|--Stream Aggregate(GROUP BY:([st].[stor_name])         DEFINE:([Expr1004]=SUM([partialagg1005])))       |--Sort(ORDER BY:([st].[stor_name] ASC))       |--Nested Loops(Left Semi Join, OUTER REFERENCES:([st].[stor_id]))       |--Nested Loops(Inner Join, OUTER REFERENCES:([bs].[stor_id]))         | |--Stream Aggregate(GROUP BY:([bs].[stor_id])           DEFINE:([partialagg1005]=SUM([bs].[qty])))              | | |--Clustered Index Scan(OBJECT:([pubs].[dbo].[big_sales].             [UPKCL_big_sales] AS [bs]), ORDERED FORWARD)         | |--Clustered Index Seek(OBJECT:([pubs].[dbo].[stores].             [UPK_storeid] AS [st]),         SEEK:([st].[stor_id]=[bs].[stor_id]) ORDERED FORWARD)       |--Clustered Index Seek(OBJECT:([pubs].[dbo].[big_sales].           [UPKCL_big_sales]),         SEEK:([big_sales].[stor_id]=[st].[stor_id]) ORDERED FORWARD)

      UNION vs UNION ALL

      无论何时尽可能用UNION ALL 代替UNION。其中的差异是因为UNION有排除重复行并且对结果进行排序的副作用，而UNION ALL不会做这些工作。选择无重复行的结果需要建立临时工作表，用它排序所有行并且在输出之前排序。（在一个select distinct 查询中显示查询计划将发现存在一个流聚合，消耗百分之三十多的资源处理查询）。当你确切知道你得需要时，可以使用UNION。但如果你估计在结果集中没有重复的行，就使用UNION ALL吧。它只是从一个表或一个连接中选择，然后从另一个表中选择，附加在第一条结果集的底部。UNION ALL不需要工作表和排序（除非其它条件引起的）。在大部分情况下UNION ALL更具效率。一个有潜在危险的问题是使用UNION会在数据库中产生巨大的泛滥的临时工作表。如果你期望从UNION查询中获得大量的结果集时，这就可能发生。

      示例
      下面的查询是选择pubs数据库中的表sales的所有商店的ID，也选择表big_sales中的所有商店的ID，这个表中我们加入了70，000多行数据。在这两个方案间不同之处仅仅是UNION 与UNION ALL的使用比较。但在这个计划中加入ALL关键字产生了三大不同。第一个方案中，在返回结果集给客户端之前需要流聚合并且排序结果。第二个查询更有效率，特别是对大表。在这个例子中两个查询返回同样的结果集，虽然顺序不同。在我们的测试中有两个临时表。你的结果可能会稍有差异。

UNION SOLUTION       -----------------------	UNION ALL SOLUTION       -----------------------
SELECT stor_id FROM big_sales       UNION       SELECT stor_id FROM sales       ----------------------------	SELECT stor_id FROM big_sales       UNION ALL       SELECT stor_id FROM sales       ----------------------------
|--Merge Join(Union)        |--Stream Aggregate(GROUP BY:       ([big_sales].[stor_id]))       | |--Clustered Index Scan       (OBJECT:([pubs].[dbo].       [big_sales].       [UPKCL_big_sales]),       ORDERED FORWARD)       |--Stream Aggregate(GROUP BY:       ([sales].[stor_id]))        |--Clustered Index Scan        (OBJECT:([pubs].[dbo].        [sales].[UPKCL_sales]),        ORDERED FORWARD)	|--Concatenation       |--Index Scan       (OBJECT:([pubs].[dbo].        [big_sales].[ndx_sales_ttlID]))       |--Index Scan       (OBJECT:([pubs].[dbo].       [sales].[titleidind]))

      函数和表达式约束索引

      示例

      你应该帮助SQL Server移除任何在索引数值列周围的表达式。下面的查询是从表jobs通过唯一的聚集索引的唯一键值选择出的一行。如果你在这个列上使用表达式，这个索引就不起作用了。但一旦你将条件’job_id-2=0’ 该成‘job_id=2’，优化器将在聚集索引上执行seek操作。

      下面表中列出了多种不同类型查询示例，其被禁止使用列索引，同时给出改写的方法，以获得更优的性能。

      SET NOCOUNT ON

      为做个例子，一个文件含T-SQL批处理，其在big_sales表插入了9999行。

      -- Assumes the existence of a table called BIG_SALES, a copy of pubs..sales

      SET NOCOUNT ON

      DECLARE @separator VARCHAR(25),

      @message VARCHAR(25),

      @counter INT,

      @ord_nbr VARCHAR(20),

      @order_date DATETIME,

      @store_nbr INT,

      @qty_sold INT,

      @terms VARCHAR(12),

      @title CHAR(6),

      @starttime DATETIME

      SET @STARTTIME = GETDATE()

      SELECT @counter = 0,

      @separator = REPLICATE( '-', 25 )

      WHILE @counter < 9999

      BEGIN

      SET @counter = @counter + 1

      SET @ord_nbr = 'Y' + CAST(@counter AS VARCHAR(5))

      SET @order_date = DATEADD(hour, (@counter * 8), 'Jan 01 1999')

      SET @store_nbr =

      CASE WHEN @counter < 999 THEN '6380'

      WHEN @counter BETWEEN 1000 AND 2999 THEN '7066'

      WHEN @counter BETWEEN 3000 AND 3999 THEN '7067'

      WHEN @counter BETWEEN 4000 AND 6999 THEN '7131'

      WHEN @counter BETWEEN 7000 AND 7999 THEN '7896'

      WHEN @counter BETWEEN 8000 AND 9999 THEN '8042'

      ELSE '6380'

      END

      SET @qty_sold =

      CASE WHEN @counter BETWEEN 0 AND 2999 THEN 11

      WHEN @counter BETWEEN 3000 AND 5999 THEN 23

      ELSE 37

      END

      SET @terms =

      CASE WHEN @counter BETWEEN 0 AND 2999 THEN 'Net 30'

      WHEN @counter BETWEEN 3000 AND 5999 THEN 'Net 60'

      ELSE 'On Invoice'

      END

      -- SET @title = (SELECT title_id FROM big_sales WHERE qty = (SELECT MAX(qty)

      FROM big_sales))

      SET @title =

      CASE WHEN @counter < 999 THEN 'MC2222'

      WHEN @counter BETWEEN 1000 AND 1999 THEN 'MC2222'

      WHEN @counter BETWEEN 2000 AND 3999 THEN 'MC3026'

      WHEN @counter BETWEEN 4000 AND 5999 THEN 'PS2106'

      WHEN @counter BETWEEN 6000 AND 6999 THEN 'PS7777'

      WHEN @counter BETWEEN 7000 AND 7999 THEN 'TC3218'

      ELSE 'PS1372'

      END

      -- PRINT @separator

      -- SELECT @message = STR( @counter, 10 ) -- + STR( SQRT( CONVERT( FLOAT,

      @counter ) ), 10, 4 )

      -- PRINT @message

      BEGIN TRAN

      INSERT INTO [pubs].[dbo].[big_sales]([stor_id], [ord_num], [ord_date],

      [qty], [payterms], [title_id])

      VALUES(@store_nbr, CAST(@ord_nbr AS CHAR(5)), @order_date, @qty_sold,

      @terms, @title)

      COMMIT TRAN

      END

      SET @message = CAST(DATEDIFF(ms, @starttime, GETDATE()) AS VARCHAR(20))

      PRINT @message

      /*

      TRUNCATE table big_sales

      INSERT INTO big_sales

      SELECT * FROM sales

      SELECT title_id, sum(qty)

      FROM big_sales

      group by title_id

      order by sum(qty)

      SELECT * FROM big_sales

      */

      TOP 和 SET ROWCOUNT

      SELECT 语句中的TOP子句限制单个查询返回的行数，而SET ROWCOUNT限制所有后续查询影响的行数。在很多编程任务中这些命令提供了高效率。
      SET ROWCOUNT在SELECT，INSERT，UPDATE OR DELETE语句中设置可以被影响的最大行数。这些设置在命令执行时马上生效并且只影响当前的会话。为了移除这个限制执行SET ROWCOUNT 0。
一些实际的任务用TOP or SET ROWCOUNT比用标准的SQL命令对编程是更有效率的。让我们在几个例子中证明：

      TOP n

      在几乎所有的数据库中最流行的一个查询是请求一个列表中的前N项。在 pubs数据库案例中，我们可以查找销售最好CD的前五项。比较用TOP，SET ROWCOUNT和使用ANSI SQL的三种方案。

      纯 ANSI SQL:

      Select title,ytd_sales

      From titles a

      Where (select count(*)

      From titles b

      Where b.ytd_sales>a.ytd_sales

      )<5

      Order by ytd_sales DESC

      这个纯ANSI SQL方案执行一个效率可能很低的关联子查询，特别的在这个例子中，在ytd_sales上没有索引支持。另外，这个纯的标准SQL命令没有过滤掉在ytd_sales的空值,也没有区别多个CD间有关联的情况。

      使用 SET ROWCOUNT:

      SET ROWCOUNT 5

      SELECT title, ytd_sales

      FROM titles

      ORDER BY ytd_sales DESC

      SET ROWCOUNT 0

      使用 TOP n:

      SELECT TOP 5 title, ytd_sales

      FROM titles

      ORDER BY ytd_sales DESC

      第二个方案使用SET ROWCOUNT来停止SELECT查询，而第三个方案是当它找到前五行时用TOP n来停止。在这种情况下，在获得结果之前我们也要有一个ORDER BY子句强制对整个表进行排序。两个查询的查询计划实际上是一样的。然而，TOP优于SET ROWCOUNT的关键点是SET必须处理ORDER BY子句所需的工作表，而TOP 不用。

      在一个大表上，我们可以在ytd_sales上创建一个索引以避免排序。查询将使用该索引找到前5行并停止。与第一个方案相比较，其扫描了整个表，并对每一行执行了一个关联子查询。在小表上，性能的差异是很小的。但是在一个大表上，第一个方案的处理时间可能是数个小时，而后两个方法是数秒。

      当确定查询需要时，请考虑是否只需要其中几行，如果是，使用TOP子句将节约大量时间。

     （北京铸锐数码科技有限公司 www.InnovateDigital.com）

Toad快速入门
          www.InnovateDigital.com 整理

  一、 Toad功能综述

     在Oracle 应用程序的开发过程中，访问数据库对象和编写SQL程序是一件乏味且耗费时间的工作，对数据库进行日常管理也是需要很多SQL脚本才能完成的。Quest Software为此提供了高效的Oracle应用开发工具-Toad（Tools of Oracle Application Developers）。在Toad的新版本中，还加入了DBA模块，可以帮助DBA完成许多日常管理工作。它最大的特点就是简单易用，访问速度快。使用 Toad，我们可以通过一个图形化的用户界面快速访问数据库，完成复杂的SQL和PL/SQL代码编辑和测试工作。Toad由Oracle开发专家专门为开发人员而设计，是一个功能强大、结构紧凑的专业化PL/SQL开发环境。

     Toad 主要具有如下特点：

  模式浏览：

     模式浏览功能可以让我们快速访问数据字典，浏览数据库中的表、索引、存储过程。Toad 提供对数据库的快速访问，使用极为方便，用户界面简洁，结构安排合理。当我们点击一个单独的数据库对象，Toad立即显示此对象的详细信息。例如，当我们点一个数据库的表，所有和此表相关的索引、约束、存储过程、SQL语句以及和其他表的相互引用关系都在同一界面显示出来。为了简化操作，用户可以在浏览窗口操作数据库对象。

  SQL 编辑器：

     SQL 编辑器的主要功能是编辑、运行和调整SQL语句。TOAD 的高级编辑窗口包括众多的特性来提高开发人员编写SQL语句的产品化程度。例如，简单地生成代码模板，在编写SQL前自动发现包的内容和列的名字等等。

     SQL 编辑器包括一个编辑窗口和运行结果窗口，允许开发人员在编辑的过程中测试运行结果。SQL编辑器中不仅包括标准的编辑命令，也包括一些增强的功能，如快速查询表中的字段、将SQL语句的内容格式化等等。这个窗口可以处理大到4GB 的内容，对大的开发项目来说非常有用。便捷的书签可以让开发人员非常容易地找到相关位置。在运行结果窗口可提供用户定义的配置功能，支持LONG 和LONG RAW列，可以将数据卸出到磁盘、打印数据、编辑数据等等。

  存储过程编辑器：

     存储过程编辑器的主要功能是编辑、编译、测试、调试存储过程和触发器。TOAD提供语法标识、错误标识和其他很多易于使用的功能，如在弹出窗口显示表名、列名和Oracle函数。和其他的 PL/SQL 编辑工具不同，TOAD 允许在一个文件中操作多个数据库对象，可以编译一个对象、编译多个对象、编译到当前光标、从光标开始编译。在运行出现错误时，存储过程停止到有问题的语句。用户可以使用快捷方式或模板来快速编写PL/SQL,也可以根据需要生成自己的模板。使用Toad可以非常方便地进行编辑工作，可如设置书签、取消注释、格式化SQL语句等等。

  PL/SQL Debugger选项：

     Toad 提供简单易用的PL/SQL 调试功能，可以节省开发人员在大型项目中用于开发和测试的宝贵时间，提高应用开发的质量。在存储过程开发的过程中，Toad可以逐行编辑、调试和运行代码。运行时可以根据需要输入参数，观察相关参数的变化来检查存储过程的正确性。在调式过程中，Toad 可以通过窗口显示所有的断点、参数, 调用堆栈和输出参数。使用Toad，非常容易检测到存储过程的错误，开发人员可以一步一步运行PL/SQL语句来识别问题。调试会话可以和其他程序会话同时进行。

  SQLab Xpert Option：

     帮助开发人员优化SQL，为他们提供各种优化模式下SQL执行计划，并且能够给出优化的建议，能够比较各种模式下实际的SQL运行结果，帮助开发人员真正高速地开发高效地代码。

     Toad 还可以外挂一些别的产品，比如PL/Formatter, RevealNet Knowledge Base , SQL Impact等，这些都能够和Toad紧密集成，共同提供了一个完美的集成开发环境。新版本还新增加了DBA模块，更加拓广了Toad这个产品的适用范围。

  二、系统要求：

     客户端：

• Windows 95 / 98 / NT/2000中英文均可
• 完整安装的SQL*Net 2.0或者Net8
• 磁盘：一般安装需要20M左右的空间，根据所选择模块的不同和安装方式的不同，最少也要5M的磁盘空间（从网络服务器读取）
• 内存：Windows 9x/Me:16M 内存；Windows NT/2000: Min 32M]
• Tuning模块需要额外的25M空间

     服务器端：

• Oracle 7.3 或者更高，在任何硬件平台上。
• 需要一个DBA用户帐号

  三．安装：

     1． 三种安装方式的选择：

     a) TOAD and Personal Configuration Files to PC 默认安装选择，完成一个完整的Client端的安装，把所有需要的文件拷贝到本地Toad目录。绝大多数用户应该选择使用这个安装方式。

     b) Personal Config Files to PC, read TOAD from Network Server 从网络服务器读取Toad程序，在本地计算机安装一些自定义的文件，并且创建到服务器文件的快捷方式，一般不推荐使用这个方式，而且要求网络服务器已经配置。

     c) TOAD to Network Server

     把Toad安装到网络服务器上，以便这样别的用户能够进行Personal Config Files to PC, read TOAD from Network Server这种方式的安装。

     2． 根据需要使用到的特性，需要运行一些脚本(都在Toad\temps目录下)

• 如果需要用Toad来查看执行计划，必须运行脚本PrepToad.sql或者notoad.sql
• 如果希望专门建立一个Toad 用户来存放Toad需要使用的对象的话，就运行preptoad.sql。
• 如果希望在当前连接用户模式下建立toad需要使用的对象的话，就运行notoad.sql。
• 注意，如果是选择了专门建立toad这个用户的话，需要先修改一下脚本，指定用户的默认表空间和临时表空间。
• 需要使用Oracle8i 的Profile analyzer，必须运行ToadProfiler.sql
• 需要加强Toad的安全性，必须运行ToadSecurity.sql

  四、Toad使用快速入门

  1． Schema Browser：

     Schema browser是Toad的极为有特色的模块。在schema browser的帮助下，可以方便的浏览数据库内部的各类数据库对象，比如表，索引，序列，存储过程等，而且可以方便的倒出DDL语句和进行各种修改，重建工作。定位到某一类对象上，这列对象允许的操作都会自动列出来。

     特色：

• 支持Oracle数据库里面所有对象的新建、查看、修改，集成了几乎对所有数据库对象的管理所需要的功能。
• 按照 模式->对象类别 ->对象->对象可操作属性 -> 对象本身的详细信息和对象相关的信息 来组织，非常有条理，容易定位对象，也能够看到所有你需要的对象的相关信息。
• 对于表：
  • 完备的建表向导，不用输入冗长的语法，更加全面地替你考虑。
  • 可以修改表的各种逻辑和物理属性，修改列的属性，增加列，删除列(在Oracle8i里面)，修改表的物理存储属性，Truncate，Drop等。
  • 可以生成建表的完整的DDl语句，包括存储信息，权限信息，约束，索引和触发器等，而这些手工很难做到。
  • 可以方便的对表进行分析，查看表地所有信息，可以有条件的倒出表里面的数据，并且保存为sql语句。
  • 可以方便的对表进行重组，可以完成exp/imp作不到的事情，而且更少地人工参与，更加安全。

• 对于视图，存储过程：
  • 可以保存建立视图、存储过程的语句为文本，
  • 重新编译失效的视图，存储过程，修改视图的定义语句等。方便的查看存储过程的代码，相互依赖关系，权限信息等保存为文本等。

• 回滚段
  • 简洁明了的online，offline状态，方便地online/offline操作
  • 详细的回滚段物理信息和当前状态。
  • 可以方便的修改物理存储属性

• 表空间：
  • Tablespace Map；直观的显示数据物理的在表空间上的分布
  • 直观的查看各个表空间的利用率、剩余空间、破碎情况等信息
  • 可以进行各种alter操作：online, offline, 增加数据文件，改变数据文件大小，改变物理存储属性等

• 对其他数据库对象也有完备的操作支持。

  2． SQL Editor:

• a) 激活SQL Editor的三种方式：
  • 连接到数据库之后，Toad自动打开SQL Editor这个窗口
  • 点击总工具栏上最左边的那个按钮
  • 从菜单栏：Database -> SQL Editor

• b) 特点：
  • 允许同时打开多个编辑窗口，同时编辑多个SQL语句
  • 支持同时连接到多个数据库，同时对多个数据库进行操作
  • 允许编辑SQL，PLSQL，JAVA，HTML和文本
  • 使用书签功能，可以在大量SQL语句里，快速定位某个语句所在位置
  • SQL Editor本身具有强大的编辑功能，非常方便编辑SQL语句。
  • 强大的快捷键支持和工具栏快速按钮的支持，更加高效编写(很多快捷键可以自己定义，在View ->options ->SQL Editor里面)
  • 能够根据前面的条件，智能推测需要输入的代码，可以做到某种程度的代码智能填充
  • 把鼠标定位到表/视图/存储过程名称之上，按F4，可以打开对象描述窗口，方便的查看表和视图的定义，存储过程的源代码，
  • 非常容易对SQL语句的分析其执行计划：单击工具栏上的 按钮就可以看到Explain Plan的结果
  • 支持部分SQL*Plus命令，可以方便的执行大量的为SQL*Plus编写的数据库脚本
  • 可以通过SQLab xpert来对当前SQL语句进行全面的分析，给出各种合理的调优意见，只需要单击工具栏上的Tuning按钮
  • 可以方便的保存，打开和运行SQL脚本
  • 支持对查询结果进行修改
  • 可以把查询结果以多种格式保存起来，可以保存为HTML，纯文本，XLS等多种格式
  • 很容易的得到SQL函数的列表和用法描述（Show SQL help Window 按钮）
  • 可以查看SQL语句的执行情况：通过下方的AutoTrace窗口 ，看到SQL语句的实际执行结果和执行效率。
  • 支持历史SQL(F8) ：可以方便的查询以前执行过的SQL，不用反复重新输入。
  • 可以执行匿名块，存储过程，从下方的窗口看到DBMS_OUTPUT的结果

  3． Stored Procedure Editor

• a) 打开存储过程编写器的几个途径：
  • 单击总菜单栏上的第三个按钮
  • 点击Database - > Procedure Editor
  • 可以通过Schema browser来调用

• b) 一些特点和优点：
  • 强大的自定义设置：从保留字的显示方式，到自动替换，到各种快捷键的定义等等，用得越熟越顺手。
  • 自动用不同颜色显示SQL和PL/SQL关键字，语法清晰明了，可根据自己的习惯来具体定制各类关键字的具体显示方式。可以自定义存储过程得模板，在新建存储过程的时候，自动生成程序的框架可以方便的调用Schema Browser，把鼠标定位于某个对象上，F4，Schema Browser打开该对象的详细描述，帮助顺利快速开发程序。支持代码自动更正，如输入ndf，自动替换成NO_DATA_FOUND，输入the自动替换成the，类似的自动替换还可以自己修改和添加。
  • 方便的左边行号显示，单击左边行号就可以实现调试断点的增加/删除
  • 对各种快捷键的支持，不但包括常见的编辑操作，还可以有自己定义的PLSQL块的支持（自己定义快捷方式）。如可以定义CTRL+SHIFT+D:弹出所有PLSQL Block的类型，可以自己选择。
  • 可以在一个集成开发环境里面，一边编码，一边调试，不用多处切换
  • 方便的同时打开多个存储过程，通过页面快速切换
  • 快速编译存储过程，快速定位错误所在，自己选择是否同时编译依赖的存储过程
  • 内置对PL/Formatter，可以用PL/Formatter对存储过程进行格式化，并且可以提供存储过程的概要分析和修改建议；PL/Formatter可以对现有的存储过程进行高速的格式化，使所有的存储过程都符合同样的编写标准，这样既有利于提高程序的可读性，避免不同人写的模块的风格上的很差异；PL/Formatter 还有助于提高程序的模块化，增强程序的结构清晰度，增强SQL的共用性；PL/Formatter 还可以帮助完成PL/SQL和Oracle版本之间的管理。
  • 内置RevealNet Knowledge Base，单击鼠标右键就可以打开，快速得到技术上的支持，解决技术难题
    • PL/SQL
    • 包含整个开发过程中可能遇到问题的答案
    • Code Library:大量的严谨的PL/SQL代码例子和现成过程，完全源代码提供
    • 各种错误处理模块可供参考，各类字符日期函数
    • 包含1400多个主题
    • Admin
    • 解答DBA日常工作中80%的常见问题，覆盖2400多个主题
    • 从网络到性能优化，从备份恢复到Error message,一应俱全

• 如何用SQLab Xpert对存储过程进行调优？
  • 需要Toad单独的Debug Option(Standard Version 没有这个选项)
  • 要用Toad对存储过程进行debug，必须安装Oracle的系统包：dbms_debug，而且必须安装Oracle Probe API v2.0 或者更高的版本。
  • 集成在Stored Precedure Editor里面，可以一边编写一边调试，大大提高开发效率
  • 打开Procedure Editor后，Debug菜单激活，可以开始进行Debug工作。
  • 允许对存储过程进行逐行调试、断点调试,也支持对触发器进行调试。
  • 可以有断点支持，支持Watch变量，允许运行时改变变量的值
  • 允许对多层调用的Debug支持

     调试步骤具体举例：

     1． 点击菜单栏的第三个按钮： 打开Procedure Editor

     2． 点击Procedure Editor的菜单栏的 按钮，调出需要调试的存储过程

     3． 单击工具栏上的按钮 ，输入需要传入的参数，如果不需要传入参数，可以直接单击工具栏上的按钮： ，直接开始调试，如下图输入传入参数：

     4． 单击菜单栏的按钮： ，可以单步跟踪，也可以定义一个断点，直接运行到断点在单步执行，在存储过程运行的过程中，当前语句会高亮度显示，并且把鼠标指针定位到变量上，Toad自动显示该变量的当前值。

     5． 可以随时中止程序的执行，只要点Debug ->Halt Execution，或者菜单栏上的按钮 ，就可以中止程序的执行

     6． 当碰到一个存储过程调用另外一个存储过程的时候，可以选择跳过(Step Over) ，这样就直接运行那个被调用的存储过程，返回调用结果，继续这个存储过程的调试，也可以选择Trace Into ，这样就打开被调用的存储过程，进一步深入调试那个被调用的子存储过程。如果需要保持Debug信息，在编译的时候应该选择Compile Dependencies with Debug Information，就是Procedure Editor工具栏最右边的那个按钮 。

     7． 支持对变量实时追踪：使用Watch：可以在运行前，或者运行过程中对某个变量进行实时跟踪：Add Watch，把鼠标定位到该变量上，然后点击Debug ->Add Watch At Cursor，该变量就会出现在Debug状态窗口上，而且实时反应该变量的当前值。如上图中的变量v_empname。可以运行时刻修改

     8． 断点支持：单击存储过程需要加断点的地方的行号，该行颜色变红，输入参数，单击执行按钮 ，可以直接运行到断点处，可以不必逐行追踪，更加高效率的进行调试。

     Debug Option还支持条件断点，只有当满足条件的时候才会在那个地方停止，否则就好像不存在这个断点一样继续运行。

     注意DBMS_OUTPUT.PUT_LINE的结果只有当程序执行完成以后才会出现，在程序执行过程中是没有结果的。

  4． SQLab Xpert

     我们知道，Oralce数据库的优化，最主要的步骤就在于SQL的优化。而在没有专门的工具（如Toad）的帮助下，进行SQL优化是一件非常困难和乏味的事情，并且需要对Oracle的深入理解和高超的Oracle调优技巧。使用Toad的SQLab Xpert Tuning模块，可以使我们普通开发人员也能够非常快速，精确的对SQL进行专家级的分析

     在SQL Editor和Procedure Editor里面，选中需要调试的SQL语句（SQLab Xpert只能调试Select,Insert, Update,Delete语句），复制到SQL Editor里面，然后点击工具栏上的调优按钮 ，Toad会自动打开SQLab xpert 窗口，捕获当前的SQL语句，并且把它转化为符合explain plan格式的语句。

     第一次打开Xpert的时候，Toad会提示你，需要安装一些Server端的表、视图和包等对象，可以把表存储到某个指定的表空间(如Tools)里面。可以简单的按照提示一步一步做就可以了。如果系统设定不允许Job的话（Job_queue_process=0）,Toad提示有些部件不会现在安装，可以忽略。安装完成以后，可以选择让某些非DBA用户也可以使用Toad的SQLab Xpert调优功能。

     安装完成以后，Toad会提示你一些关于优化目标方面的问题，你可以选择每次优化都可以重新设定，或者一次设定，以后一直使用这个设定。当然如果需要，还是可以更改的，在SQLab Option xpert的窗口的Administration和Preference下面重新设定。

     例子：

     下面这个SQL语句涉及到多表之间的关联，有一定的复杂性，我们可以借助Toad的SQLab Xpert Option来帮助分析：

     select a.name. b.name, c.emp_name from dep a, manager b,emp c where a.dep_no=c.DEP_NO and b.manag_id=c.manag_id and c.emp_no=3

     我们把这个SQL复制到SQL Editor里面，单击上面的调优按钮，Toad打开SQLab Xpert Option窗口。

     从上面的图中我们可以方面的看到：在各种优化模式下，该SQL语句的执行计划分别是什么样的，Toad给出了每一种执行计划的具体每一个步骤，每个步骤的成本，该表涉及的相关的数据等，如果SQL存取的表还没有经过分析，Toad会以红色警告显示，并且很方便的帮助你对该表进行分析，收集统计数据，重建索引等，以有助于优化器作出最好的选择。可以把当前的分析结果保存到它的资料库里边，以备以后再次调用之需。

     我们还可以求助与SQLab 的自动优化功能：点击Original SQL下面的auto tune按钮，Toad会对执行结果按照tkprof进行分析（当然，这需要你在initsid.ora里面设置好utl_file_dir，如果服务器是在Unix机器上的话，还需要通过FTP方式得到Trace文件，有一些小小的设置）。

     自动优化非常高效的对所有执行计划进行实际运行比较，并且给出实际执行的时间的对比，如下图：

      我们还可以让Toad给出实际的优化建议：点击Original SQL,单击下面的Advice按钮，Toad根据对SQL的分析和执行结果给出合理的建议：

     1． 把表和索引分别放到两个不同的表空间，因为我们在创建表，加上约束的时候没有指定USING INDEX子句，所以表和索引都建立在了用户的默认表空间上了。

     2． 对表EMP，DEP和MANAGER进行分析，收集最新的统计数据，有利于CBO得到最优的执行计划

     3． 在合适的列上创建索引：EMP的manag_id和dep_no。当表中存在大量的数据的时候，这样可以大大减少由于全表扫描带来的成本。

  5． SQL Modeler：

     SQL Modeler可以帮助开发人员很容易的写出各种复杂的SQL语句，只需通过简单的拖动鼠标，就能够写出复杂的SQL语句来。

     打开SQL Modeler的几个途径：

• a) 打开存储过程编写器的几个途径：
  • 菜单栏 Database -> SQL Modeler ，打开窗口
  • 单击工具栏第四个按钮 ，打开SQL Modeler窗口

       窗口由三个主要部分组成：

  • a) 打开存储过程编写器的几个途径：
    • 表选择区域：最右边的那个部分，用来选择From子句中用到的表。拖动到左边的区域或者双击就选择了该表。
    • 表模型区域：用来图形化的显示选择的表和表之间的关联关系。
    • 生成SQL区域：这里显示用图形化方式创建的查询对应的SQL语句

         在SQL Modeler里面，还支持对生成的SQL进行Explain Plan，调试SQL的性能，运行SQL，看是否得到了希望的结果，保存生成的语句，或者把语句粘贴到SQL Editor里面。

         有了SQL Modeler的帮助，即使是初级的程序员也能编写出复杂而又高效的SQL查询来。比如下面的例子

         生成步骤：

         1． 选出表emp, dep, manager

         2． 用鼠标拖住emp的emp_name和emp_no, dep的name,manager的name

         3． 双击criteria窗口的emp_no condition列，弹出窗口，输入查询条件，比如emp_no=3

         4． 由于结果里面不希望emp_no出现，双击emp_no列的visible，设置为false

         5． 这样就出来了需要的查询，现在可以运行查询得到结果，对查询SQL进行分析，或者在SQL Editor里面打开这个SQL，或者保存为SQL Model文件，或者再进一步处理，比如加排序， 汇总等。

         DBA方面的功能：

         Toad在DBA日常管理方面的能力是非常强大的，可以帮助DBA安全快速地完成许多日常工作，还有Toad特有的一些小工具能完成一些普通方法很难做到的功能。下面大概讲述了各个常用模块的主要功能：

         a) Database Monitor

         提供直观的Oracle数据库运行状况，包括下面这些详细信息：可以看到逻辑IO，物理IO，各个状态的session数目，SGA的利用率，共享池的使用状况，各类事件的等待状况，以及各类事件随着时间的变化情况

         b) Instance Monitor

    • 可以用来检查本地tnsnames.ora文件里面定义的所有节点、Listener和数据库的运行状态和版本等相关信息。
    • 可以远程启动、关闭数据库（如果设置了password验证方式）

         c) Unix Monitor：

  • 可以监控Unix机器的运行状况比如详细的机器启动时间，CPU利用率，主要的进程以及这些进程占用的资源，主要的磁盘IO等。

         d) Trace/Kill session

    • 方便的查看所有的连接用户相关信息：比如终端，程序，连接时间等
    • 对某个用户进行跟踪，查看用户执行的SQL语句，查看语句的执行计划
    • 方便的查阅系统的Lock对象，有没有什么等待别的事务释放锁的情况，并能够轻易查出等待什么用户的什么事件结束，那个用户执行的是什么SQL。
    • 查看回滚段的使用状态，什么用户，什么语句，使用的什么回滚段，多少读写量等

         e) Oracle Parameter

  • 可以方便的查看Oracle系统的参数：是否默认值，参数作用的简要描述，是否可以用alter session/alter system来动态改变，对于那些可以动态改变的参数，可以双击鼠标来进行修改

         f) NLS_PARAMETER:

    • 方便的查看session，instance，database一级的NLS参数值
    • 可以动态修改session的NLS参数值

         g) Extents

    • 可以查看某个用户下的表和索引的详细的存储情况
    • 发现那些占用大量空间的表和索引，可以考虑使用相应的对策，比如分区
    • 发现那些即将达到max_extents的表和索引并可以及时纠正
    • 发现那些索引和表处于同一个表空间的情况，并可以及时纠正。

         h) Tablespace

    • 直观的查看每个表空间的利用率，总空间，已使用空间，剩余可是用空间以及默认存储参数，和表空间是否需要整理等信息
    • 查看每个表空间的状态，组成的数据文件， 以及每个数据文件的详细信息
    • 查看每个表空间里面存在的对象和这些对象的详细存储特性

         i) Tablespace Map：

  • 图形化的显示表空间里面各个表/索引所占用的空间，各个segment的大小，segment是否连续等信息，非常直观

         j) Server Statistics:

    • 可以非常直观的看到系统总体运行情况，迅速定位系统瓶颈所在
    • 可以查看系统范围内各类等待事件的状况，查看每个连接会话的状况

         k) Pinned Code：

    • 我们知道，频繁执行的大的存储过程最好Pin起来，这样可以避免被Oracle在内存不足的情况下换出内存，避免动态加载，也避免flush shared pool的时候被清出内存。
    • 根据每个模块的大小，执行次数，决定是否要把某个存储过程PIN起来。一个按钮就搞定了。

         l) Control Files:

         可以查看当前系统有哪几个控制文件，以及控制文件内部记录的详细信息。（实际上就是V$CONTROLFILE, V$CONTROLFILE_RECORD_SECTION）

         m) Redo Log Manager:

    • 可以方便的查看LOG的组，当前日志，每个组里面的成员名字，大小，方便的增加、删除log组，切换当前日志，对日志归档进行具体的控制。
    • 可以在这个界面里完成几乎所有的对redo log的操作。

         n) Repair chained rows:

  • Chained rows影响系统性能，处于系统性能的考虑，需要把chained rows修复成普通的表的行。使用repaie chained rows,就可以比较容易的做到这一点。选择需要分析的表，然后分析一下，看有没有chained rows，如果有，点击repair，Toad就帮你把那些chained rows给修复了。

         o) Identify Space Deficits

         如果系统出现1659,1653错误，就是一个不合格的DBA。但是要即时预见这些错误，还是有一定的难度的。有了Toad的这个小工具之后，一点某个表空间，就知道这个表空间里面的剩余自由空间是否允许任何表能够扩展到下一个extent。并能够在发现问题之后，直接改正问题。

         p) Estimate Table/Index Size

    • 数据库的物理规划中，表/索引的存储子句的定义是非常重要的，影响系统。但是没有什么工具的话，要为表定义合理的存储子句也不容易。
    • 在Toad里面，专门有工具帮助你合理定义表/索引的存储情况：

           1. 自己可以定义将来表中需要存储的数据的数目

           2. 多种估计方式：

      • 根据对已有的数据的分析来定义表的大小（最精确，但是比较慢）
      • 根据表定义数据类型来定义表的大小
      • 根据数据字典中以前分析过的数据来估计大小（需要近期分析过该表）

         q) Analyze All Tables

    • 基于CBO的SQL需要CBO提供最优化的执行路径来提高程序的执行效率，但是假如对表进行大量的DML操作之后，不及时更新表的统计数据，这时候CBO就不能选择最为有效的执行路径，导致程序执行效率低。
    • 手工分析表比较麻烦，一个一个做。
    • 用Toad的Analyze Alltables这个工具，可以一次分析整个模式下面的所有表，有丰富的控制选项，包括是采用估计还是完全，是否同时分析索引，估计的话，采用多大的百分比等。

         r) Rebuild Table

    • 当表中的数据被反复大量的DML之后，而且PCTFREE和PCTUSED定义的不合理的情况下，表可能会变得非常破碎，占用大量的extent，索引也会相当破碎，导致效率相当低下。
    • 传统的方式是通过exp/imp来解决这个问题，但是步骤实在比较麻烦，手工操作也有一定的危险性。
    • 使用Rebuild Table工具，轻松搞定，而且可以手工修改一些通过EXP/IMP无法修改的参数，比如freelists ,freelists group, tablespace，initial等，还能够更改列的名字。

         s) Relocate Index

    • 我们知道，表和索引一般都需要分开存放到不同的表空间，这样比较便于管理，性能上也会有一定的提高，因为可以对表和索引同时存取。
    • 但是实际上很多时候，由于这样那样的原因，常常忽略了索引单独存放这个基本概念，比如建立主键约束的时候没有加USING INDEX子句，后来disable/enable了主键约束/唯一性约束等，都会导致把索引直接存放到和表同一个表空间里面。
    • 手工发现这些索引，再逐个移动非常麻烦，容易出错，在Toad里面，就很容易根据用户/表空间来发现那些不恰当存放的索引，可以批量移动这些索引，并且允许重新定义索引的存储子句。

         t) Generate Schema Scripts：

    • 可以用来生成整个用户下所有/部分对象的DDL语句。可以把结果保存为sql语句或者保存为TOAD的自定义格式。
    • 可以用来复制表结构，（EXP/IMP有非常严重的缺陷，就是从一个大的数据库倒出的表结构，无法导入到一个小的数据库里面，因为里面包含了原来表的存储信息，即使选择rows=N，也要在导入数据库里面占用相当大的空间）

         u) Compare Schema：

    • 在开发的时候，有时候同时在几个开发库上开发，但是最后两个库的结构不完全一样，很难比较
    • 用TOAD的这个功能，可以对两个数据库的模式（用户）下面的所有对象（包括存储过程）进行比较，可以选择具体的比较内容。
    • 可以通过直接打开两个到各自数据库的连接，或者根据Toad generate schame scripts生成的文件进行对比。
    • 可以直接生成同步两个数据库的sql脚本，给出汇总的区别简要和详细报告等。

    
    

Java性能的基础----内存管理

        任何Java应用，单机的或J2EE的性能基础都可归结到你的应用是如何管理内存的问题。Java的内存管理包括两个重要任务：内存的分配和内存的回收。在内存的分配中，目标是要减少需要创建的对象。 内存回收是导致性能下降的普遍原因。也就是说，内存中的对象越多，垃圾回收越困难。所以我们对创建对象的态度应该越保守越好。

        在J2EE应用中常见的两个内存有关的问题是：游离的对象（也被称为内存泄露）和对象循环（指大量频繁创建和删除-在Java中体现为解除引用---对象）。

        我们应注意确保所有可到达的对象实际是活的，即这些对象不但在内存中，而且也要在执行的代码中是存在的。当对象在应用中已经没有用了，而我们却忘记了删除对该对象的引用时，游离的对象就出现了。

        我们知道垃圾回收会占用CPU时间。短期对象的大量创建增加了垃圾回收的频率会造成性能下降。

不要在Servlet中实现业务逻辑

        在构建J2EE应用时，架构工程师通常会使用到J2EE的基本部分，Servlet。

        如果架构师不使用Session Beans, Entity Beans, 或 Message Beans, 那么改进性能的方法就很少。只能采用增加CPU或更多的物理服务器等方法。EJB使用了缓存（cache）和资源池等方法可以提高性能和扩展性。

尽可能使用本地接口访问EJB

        在早期的J2EE （遵循EJB1.X规范）应用中，访问EJB是`通过RMI使用远程接口实现的。随着EJB2.0的出现，可以通过本地接口访问EJB，不再使用RMI，在同一个JVM中使用远程方法已经少多了。但是现在还是有一些使用EJB1.X实现的应用和不知道使用本地接口的一些EJB新手。为说明这点，我们作个比较：

        1. 客户端应用调用本地Stub

        2. 该Stub装配参数

        3. 该Stub传到skeleton

        4. 该skeleton分解参数

        5. 该skeleton调用EJB对象

        6. EJB对象执行容器服务

        7. EJB对象调用企业BEAN实例

        8. 企业BEA执行操作

        9. 执行组装/分解步骤然后返回

        与远程接口处理相比较，本地接口的EJB方法是：

                1. 客户端调用本地对象

                2. 本地对象执行容器服务

                3. 本地对象调用企业Bean实例

                4. 企业Bean实例执行操作

                5. 没有其他返回步骤

        如果你不需要从远程的客户端访问一个特殊EJB，就应该使用本地方法。

在实现Session Bean的服务中封装对实体EJB的访问

        从Servlet访问实体EJB不但效率低而且难于维护。使用Session Fa?ade（会话外观）模式可把对实体EJB的访问封装在会话EJB中，在该会话EJB中通过使用本地接口访问实体EJB而避免过多的远程调用。

        这项技术会有额外的性能和扩展方面的好处，这是因为会话和实体EJB可以使用缓存和资源池技术来进行改进。另外，由于负载的需要，会话和实体EJB可被扩展部署到其他硬件设备上，这比将Servlet层复制扩展到其他硬件设备上要简单的多。

尽量粗粒度访问远程EJB

         当访问远程EJB时，调用set/get方法将产生过多的网络请求，同时也导致远程接口处理的过载。为避免这种情况，可考虑将数据属性集中在一个对象中，这样通过一次对远程EJB的调用就可以传递所有数据。这项技术就是数据传输对象（Data Transfer Object）模式。

优化SQL

        J2EE的架构设计工程师和开发人员通常不是SQL专家或经验丰富的数据库管理员。首先应该确保SQL使用了数据库提供的索引支持。在某些情况下，将数据库的索引和数据分开存放会提高性能。但要知道，增加额外的索引可以提高SELECT性能但也会降低INSERT的性能。对于某些数据库，关联表之间的排序会严重影响性能。可以多向数据库管理员咨询。

避免在实体EJB中过多执行SQL

        有时候，通过实体EJB访问数据会执行多个SQL语句。根据J2EE 规范，第一步，将调用实体Bean的find(发现)方法；第二步，在第一次调用实体EJB的业务方法时，容器会调用ejbLoad()从数据库中获得信息。

        很多CMP(容器管理持久性)在调用发现方法时就缓存了实体数据，所以在调用ejbLoad()时就不再访问数据库了。应该避免使用BMP(Bean管理的持久性)或者自己实现缓存算法避免二次访问数据库。

使用Fast Lane Reader 模式访问只读数据

        J2EE应用经常要以只读方式访问大量长时间不变的数据，而不是访问单个实体，例如浏览在线产品目录。在这种只读情况下，使用实体EJB访问数据会导致严重过载并且实现很麻烦。实体EJB 适合于对单个实体的粗粒度访问，访问大量的列表只读数据时效率不高。不管是使用CMP还是BMP，一定需要编写代码操作多个实体EJB及其关联。这将导致访问多个数据库并存在大量的也是不必要的事务开销。

利用Java Messaging Servce(消息服务)

        J2EE规范在JMS中提供了内置的异步处理服务。当涉及到系统需求时，应该了解在什么情况下应该采用JMS进行异步处理的设计。一旦确定要执行一些异步处理，那么同步处理的任务就应该越少越好，将数据库密集的操作安排在稍后的异步处理中完成。

缓存JNDI Lookup查找

        很多操作在进行JNDI查找时要消耗大量资源。通常应该缓存JNDI资源避免网络调用和某些处理的过载。可以缓存的JNDI查找包括：

        EJB Home Interfaces

        Data Sources

        JMS Connection Factories

        JMS Destinations/Topics

        一些JNDI包实现了缓存功能。但是调用对EJB主接口的narrow方法时，这种功能作用有限。

        缓存查找的设计应该使用共享的IntialContext实例，尽管构建它很麻烦。这是因为需要访问多种数据源，包括应用资源文件JNDI.properties,系统属性的各项参数,传入到构造函数的各项参数。

（北京铸锐数码科技有限公司 www.InnovateDigital.com）