Oracle Hint

在SQL中增加/*ordered */
會使查詢效能顯著提升
原來這叫做Oracle Hint

常見Oracle HINT的用法：
1. /*+ALL_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳吞吐量,使資源消耗最小化.
例如:
SELECT /*+ALL+_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

2. /*+FIRST_ROWS*/
表明對語句塊選擇基於開銷的優化方法,並獲得最佳響應時間,使資源消耗最小化.
例如:
SELECT /*+FIRST_ROWS*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

3. /*+CHOOSE*/
表明如果數據字典中有訪問表的統計資料,將基於開銷的優化方法,並獲得最佳的吞吐量;
表明如果數據字典中沒有訪問表的統計資料,將基於規則開銷的優化方法;
例如:
SELECT /*+CHOOSE*/ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

4. /*+RULE*/
表明對語句塊選擇基於規則的優化方法.
例如:
SELECT /*+ RULE */ EMP_NO,EMP_NAM,DAT_IN FROM BSEMPMS WHERE EMP_NO=’SCOTT’;

5. /*+FULL(TABLE)*/
表明對表選擇全局掃描的方法.
例如:
SELECT /*+FULL(A)*/ EMP_NO,EMP_NAM FROM BSEMPMS A WHERE EMP_NO=’SCOTT’;

6. /*+ROWID(TABLE)*/
提示明確表明對指定表根據ROWID進行訪問.
例如:
SELECT /*+ROWID(BSEMPMS)*/ * FROM BSEMPMS WHERE ROWID>=’AAAAAAAAAAAAAA’
AND EMP_NO=’SCOTT’;

7. /*+CLUSTER(TABLE)*/
提示明確表明對指定表選擇簇掃描的訪問方法,它只對簇對像有效.
例如:
SELECT /*+CLUSTER */ BSEMPMS.EMP_NO,DPT_NO FROM BSEMPMS,BSDPTMS
WHERE DPT_NO=’TEC304′ AND BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

8. /*+INDEX(TABLE INDEX_NAME)*/
表明對錶選擇索引的掃描方法.
例如:
SELECT /*+INDEX(BSEMPMS SEX_INDEX) USE SEX_INDEX BECAUSE THERE ARE FEWMALE BSEMPMS */ FROM BSEMPMS WHERE SEX=’M';

9. /*+INDEX_ASC(TABLE INDEX_NAME)*/
表明對錶選擇索引升序的掃描方法.
例如:
SELECT /*+INDEX_ASC(BSEMPMS PK_BSEMPMS) */ FROM BSEMPMS WHERE DPT_NO=’SCOTT’;

10. /*+INDEX_COMBINE*/
為指定表選擇位圖訪問路經,如果INDEX_COMBINE中沒有提供作為參數的索引,將選擇出位圖索引的布爾組合方式.
例如:
SELECT /*+INDEX_COMBINE(BSEMPMS SAL_BMI HIREDATE_BMI)*/ * FROM BSEMPMS
WHERE SAL<5000000 emp_no="SCOTT" sex="M" dpt_no="V.DPT_NO">V.AVG_SAL;

20. /*+NO_MERGE(TABLE)*/
對於有可合併的視圖不再合併.
例如:
SELECT /*+NO_MERGE(V) */ A.EMP_NO,A.EMP_NAM,B.DPT_NO FROM BSEMPMS A (SELECT DPT_NO,AVG(SAL) AS AVG_SAL FROM BSEMPMS B GROUP BY DPT_NO) V WHERE A.DPT_NO=V.DPT_NO AND A.SAL>V.AVG_SAL;

21. /*+ORDERED*/
根據表出現在FROM中的順序,ORDERED使ORACLE依此順序對其連接.
例如:
SELECT /*+ORDERED*/ A.COL1,B.COL2,C.COL3 FROM TABLE1 A,TABLE2 B,TABLE3 C WHERE A.COL1=B.COL1 AND B.COL1=C.COL1;

22. /*+USE_NL(TABLE)*/
將指定表與嵌套的連接的行源進行連接,並把指定表作為內部表.
例如:
SELECT /*+ORDERED USE_NL(BSEMPMS)*/ BSDPTMS.DPT_NO,BSEMPMS.EMP_NO,BSEMPMS.EMP_NAM FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

23. /*+USE_MERGE(TABLE)*/
將指定的表與其他行源通過合併排序連接方式連接起來.
例如:
SELECT /*+USE_MERGE(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

24. /*+USE_HASH(TABLE)*/
將指定的表與其他行源通過hash連接方式連接起來.
例如:
SELECT /*+USE_HASH(BSEMPMS,BSDPTMS)*/ * FROM BSEMPMS,BSDPTMS WHERE BSEMPMS.DPT_NO=BSDPTMS.DPT_NO;

25. /*+DRIVING_SITE(TABLE)*/
強制與ORACLE所選擇的位置不同的表進行查詢執行.
例如:
SELECT /*+DRIVING_SITE(DEPT)*/ * FROM BSEMPMS,DEPT@BSDPTMS WHERE BSEMPMS.DPT_NO=DEPT.DPT_NO;

26. /*+LEADING(TABLE)*/
將指定的表作為連接次序中的首表.

27. /*+CACHE(TABLE)*/
當進行 full scan 時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) CAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

28. /*+NOCACHE(TABLE)*/
當進行 full scan 時,CACHE提示能夠將表的檢索塊放置在緩衝區緩存中最近最少列表LRU的最近使用端
例如:
SELECT /*+FULL(BSEMPMS) NOCAHE(BSEMPMS) */ EMP_NAM FROM BSEMPMS;

29. /*+APPEND*/
直接插入到表的最後,可以提高速度.
insert /*+append*/ into test1 select * from test4 ;

30. /*+NOAPPEND*/
通過在插入語句生存期內停止並行模式來啟動常規插入.
insert /*+noappend*/ into test1 select * from test4 ;

source: http://iqloss.blogspot.com/2011/04/oracle-hint.html

五种提高 SQL 性能的方法

发布日期： 4/1/2004 | 更新日期： 4/1/2004

Johnny Papa

Data Points Archive

有时， 为了让应用程序运行得更快，所做的全部工作就是在这里或那里做一些很小调整。啊，但关键在于确定如何进行调整！迟早您会遇到这种情况：应用程序中的 SQL 查询不能按照您想要的方式进行响应。它要么不返回数据，要么耗费的时间长得出奇。如果它降低了报告或您的企业应用程序的速度，用户必须等待的时间过长，他们就会很不满意。就像您的父母不想听您解释为什么在深更半夜才回来一样，用户也不会听你解释为什么查询耗费这么长时间。（“对不起，妈妈，我使用了太多的 LEFT JOIN。”）用户希望应用程序响应迅速，他们的报告能够在瞬间之内返回分析数据。就我自己而言，如果在 Web 上冲浪时某个页面要耗费十多秒才能加载（好吧，五秒更实际一些），我也会很不耐烦。

为了解决这些问题，重要的是找到问题的根源。那么，从哪里开始呢？根本原因通常在于数据库设计和访问它的查询。在本月的专栏中，我将讲述四项技术，这些技术可用于提高基于 SQL Server? 的应用程序的性能或改善其可伸缩性。我将仔细说明 LEFT JOIN、CROSS JOIN 的使用以及 IDENTITY 值的检索。请记住，根本没有神奇的解决方案。调整您的数据库及其查询需要占用时间、进行分析，还需要大量的测试。这些技术都已被证明行之有效，但对您的应用程序而言，可能其中一些技术比另一些技术更适用。

本页内容

	从 INSERT 返回 IDENTITY
	内嵌视图与临时表
	避免 LEFT JOIN 和 NULL
	灵活使用笛卡尔乘积
	拾遗补零

从 INSERT 返回 IDENTITY

我决定从遇到许多问题的内容入手：如何在执行 SQL INSERT 后检索 IDENTITY 值。通常，问题不在于如何编写检索值的查询，而在于在哪里以及何时进行检索。在 SQL Server 中，下面的语句可用于检索由最新在活动数据库连接上运行的 SQL 语句所创建的 IDENTITY 值：

SELECT @@IDENTITY

这个 SQL 语句并不复杂，但需要记住的一点是：如果这个最新的 SQL 语句不是 INSERT，或者您针对非 INSERT SQL 的其他连接运行了此 SQL，则不会获得期望的值。您必须运行下列代码才能检索紧跟在 INSERT SQL 之后且位于同一连接上的 IDENTITY，如下所示：

INSERT INTO Products (ProductName) VALUES ('Chalk')  SELECT @@IDENTITY

在一个连接上针对 Northwind 数据库运行这些查询将返回一个名称为 Chalk 的新产品的 IDENTITY 值。所以，在使用 ADO 的 Visual Basic? 应用程序中，可以运行以下语句：

Set oRs = oCn.Execute("SET NOCOUNT ON;INSERT INTO Products _ (ProductName) VALUES ('Chalk');SELECT @@IDENTITY")  lProductID = oRs(0)

此代码告诉 SQL Server 不要返回查询的行计数，然后执行 INSERT 语句，并返回刚刚为这个新行创建的 IDENTITY 值。SET NOCOUNT ON 语句表示返回的记录集有一行和一列，其中包含了这个新的 IDENTITY 值。如果没有此语句，则会首先返回一个空的记录集（因为 INSERT 语句不返回任何数据），然后会返回第二个记录集，第二个记录集中包含 IDENTITY 值。这可能有些令人困惑，尤其是因为您从来就没有希望过 INSERT 会返回记录集。之所以会发生此情况，是因为 SQL Server 看到了这个行计数（即一行受到影响）并将其解释为表示一个记录集。因此，真正的数据被推回到了第二个记录集。当然您可以使用 ADO 中的 NextRecordset 方法获取此第二个记录集，但如果总能够首先返回该记录集且只返回该记录集，则会更方便，也更有效率。

此方法虽然有效，但需要在 SQL 语句中额外添加一些代码。获得相同结果的另一方法是在 INSERT 之前使用 SET NOCOUNT ON 语句，并将 SELECT @@IDENTITY 语句放在表中的 FOR INSERT 触发器中，如下面的代码片段所示。这样，任何进入该表的 INSERT 语句都将自动返回 IDENTITY 值。

CREATE TRIGGER trProducts_Insert ON Products FOR INSERT AS      SELECT @@IDENTITY  GO 

触发器只在 Products 表上发生 INSERT 时启动，所以它总是会在成功 INSERT 之后返回一个 IDENTITY。使用此技术，您可以始终以相同的方式在应用程序中检索 IDENTITY 值。

返回页首

内嵌视图与临时表

某些时候，查询需要将数据与其他一些可能只能通过执行 GROUP BY 然后执行标准查询才能收集的数据进行联接。例如，如果要查询最新五个定单的有关信息，您首先需要知道是哪些定单。这可以使用返回定单 ID 的 SQL 查询来检索。此数据就会存储在临时表（这是一个常用技术）中，然后与 Products 表进行联接，以返回这些定单售出的产品数量：

CREATE TABLE #Temp1 (OrderID INT NOT NULL, _                      OrderDate DATETIME NOT NULL)  INSERT INTO #Temp1 (OrderID, OrderDate) SELECT     TOP 5 o.OrderID, o.OrderDate FROM Orders o ORDER BY o.OrderDate DESC  SELECT     p.ProductName, SUM(od.Quantity) AS ProductQuantity FROM     #Temp1 t      INNER JOIN [Order Details] od ON t.OrderID = od.OrderID     INNER JOIN Products p ON od.ProductID = p.ProductID  GROUP BY p.ProductName ORDER BY p.ProductName  DROP TABLE #Temp1

这些 SQL 语句会创建一个临时表，将数据插入该表中，将其他数据与该表进行联接，然后除去该临时表。这会导致此查询进行大量 I/O 操作，因此，可以重新编写查询，使用内嵌视图取代临时表。内嵌视图只是一个可以联接到 FROM 子句中的查询。所以，您不用在 tempdb 中的临时表上耗费大量 I/O 和磁盘访问，而可以使用内嵌视图得到同样的结果：

SELECT p.ProductName,      SUM(od.Quantity) AS ProductQuantity FROM     (     SELECT TOP 5 o.OrderID, o.OrderDate     FROM     Orders o      ORDER BY o.OrderDate DESC     ) t      INNER JOIN [Order Details] od ON t.OrderID = od.OrderID     INNER JOIN Products p ON od.ProductID = p.ProductID  GROUP BY     p.ProductName ORDER BY     p.ProductName

此查询不仅比前面的查询效率更高，而且长度更短。临时表会消耗大量资源。如果只需要将数据联接到其他查询，则可以试试使用内嵌视图，以节省资源。

返回页首

避免 LEFT JOIN 和 NULL

当然，有很多时候您需要执行 LEFT JOIN 和使用 NULL 值。但是，它们并不适用于所有情况。改变 SQL 查询的构建方式可能会产生将一个花几分钟运行的报告缩短到只花几秒钟这样的天壤之别的效果。有时，必须在查询中调整数据的形态，使之适应应用程序所要求的显示方式。虽然 TABLE 数据类型会减少大量占用资源的情况，但在查询中还有许多区域可以进行优化。SQL 的一个有价值的常用功能是 LEFT JOIN。它可以用于检索第一个表中的所有行、第二个表中所有匹配的行、以及第二个表中与第一个表不匹配的所有行。例如，如果希望返回每个客户及其定单，使用 LEFT JOIN 则可以显示有定单和没有定单的客户。

此工具可能会被过度使用。LEFT JOIN 消耗的资源非常之多，因为它们包含与 NULL（不存在）数据匹配的数据。在某些情况下，这是不可避免的，但是代价可能非常高。LEFT JOIN 比 INNER JOIN 消耗资源更多，所以如果您可以重新编写查询以使得该查询不使用任何 LEFT JOIN，则会得到非常可观的回报（请参阅图 1 中的图）。

图 1：查询

加快使用 LEFT JOIN 的查询速度的一项技术涉及创建一个 TABLE 数据类型，插入第一个表（LEFT JOIN 左侧的表）中的所有行，然后使用第二个表中的值更新 TABLE 数据类型。此技术是一个两步的过程，但与标准的 LEFT JOIN 相比，可以节省大量时间。一个很好的规则是尝试各种不同的技术并记录每种技术所需的时间，直到获得用于您的应用程序的执行性能最佳的查询。

测试查询的速度时，有必要多次运行此查询，然后取一个平均值。因为查询（或存储过程）可能会存储在 SQL Server 内存中的过程缓存中，因此第一次尝试耗费的时间好像稍长一些，而所有后续尝试耗费的时间都较短。另外，运行您的查询时，可能正在针对相同的表运行其他查询。当其他查询锁定和解锁这些表时，可能会导致您的查询要排队等待。例如，如果您进行查询时某人正在更新此表中的数据，则在更新提交时您的查询可能需要耗费更长时间来执行。

避免使用 LEFT JOIN 时速度降低的最简单方法是尽可能多地围绕它们设计数据库。例如，假设某一产品可能具有类别也可能没有类别。如果 Products 表存储了其类别的 ID，而没有用于某个特定产品的类别，则您可以在字段中存储 NULL 值。然后您必须执行 LEFT JOIN 来获取所有产品及其类别。您可以创建一个值为“No Category”的类别，从而指定外键关系不允许 NULL 值。通过执行上述操作，现在您就可以使用 INNER JOIN 检索所有产品及其类别了。虽然这看起来好像是一个带有多余数据的变通方法，但可能是一个很有价值的技术，因为它可以消除 SQL 批处理语句中消耗资源较多的 LEFT JOIN。在数据库中全部使用此概念可以为您节省大量的处理时间。请记住，对于您的用户而言，即使几秒钟的时间也非常重要，因为当您有许多用户正在访问同一个联机数据库应用程序时，这几秒钟实际上的意义会非常重大。

返回页首

灵活使用笛卡尔乘积

对于此技巧，我将进行非常详细的介绍，并提倡在某些情况下使用笛卡尔乘积。出于某些原因，笛卡尔乘积 (CROSS JOIN) 遭到了很多谴责，开发人员通常会被警告根本就不要使用它们。在许多情况下，它们消耗的资源太多，从而无法高效使用。但是像 SQL 中的任何工具一样，如果正确使用，它们也会很有价值。例如，如果您想运行一个返回每月数据（即使某一特定月份客户没有定单也要返回）的查询，您就可以很方便地使用笛卡尔乘积。 图 2 中的 SQL 就执行了上述操作。

虽然这看起来好像没什么神奇的，但是请考虑一下，如果您从客户到定单（这些定单按月份进行分组并对销售额进行小计）进行了标准的 INNER JOIN，则只会获得客户有定单的月份。因此，对于客户未订购任何产品的月份，您不会获得 0 值。如果您想为每个客户都绘制一个图，以显示每个月和该月销售额，则可能希望此图包括月销售额为 0 的月份，以便直观标识出这些月份。如果使用 图 2 中的 SQL，数据则会跳过销售额为 0 美元的月份，因为在定单表中对于零销售额不会包含任何行（假设您只存储发生的事件）。

图 3 中的代码虽然较长，但是可以达到获取所有销售数据（甚至包括没有销售额的月份）的目标。首先，它会提取去年所有月份的列表，然后将它们放入第一个 TABLE 数据类型表 (@tblMonths) 中。下一步，此代码会获取在该时间段内有销售额的所有客户公司的名称列表，然后将它们放入另一个 TABLE 数据类型表 (@tblCus-tomers) 中。这两个表存储了创建结果集所必需的所有基本数据，但实际销售数量除外。 第一个表中列出了所有月份（12 行），第二个表中列出了这个时间段内有销售额的所有客户（对于我是 81 个）。并非每个客户在过去 12 个月中的每个月都购买了产品，所以，执行 INNER JOIN 或 LEFT JOIN 不会返回每个月的每个客户。这些操作只会返回购买产品的客户和月份。

笛卡尔乘积则可以返回所有月份的所有客户。笛卡尔乘积基本上是将第一个表与第二个表相乘，生成一个行集合，其中包含第一个表中的行数与第二个表中的行数相乘的结果。因此，笛卡尔乘积会向表 @tblFinal 返回 972 行。最后的步骤是使用此日期范围内每个客户的月销售额总计更新 @tblFinal 表，以及选择最终的行集。

如果由于笛卡尔乘积占用的资源可能会很多，而不需要真正的笛卡尔乘积，则可以谨慎地使用 CROSS JOIN。例如，如果对产品和类别执行了 CROSS JOIN，然后使用 WHERE 子句、DISTINCT 或 GROUP BY 来筛选出大多数行，那么使用 INNER JOIN 会获得同样的结果，而且效率高得多。如果需要为所有的可能性都返回数据（例如在您希望使用每月销售日期填充一个图表时），则笛卡尔乘积可能会非常有帮助。但是，您不应该将它们用于其他用途，因为在大多数方案中 INNER JOIN 的效率要高得多。

返回页首

拾遗补零

这里介绍其他一些可帮助提高 SQL 查询效率的常用技术。假设您将按区域对所有销售人员进行分组并将他们的销售额进行小计，但是您只想要那些数据库中标记为处于活动状态的销售人员。您可以按区域对销售人员分组，并使用 HAVING 子句消除那些未处于活动状态的销售人员，也可以在 WHERE 子句中执行此操作。在 WHERE 子句中执行此操作会减少需要分组的行数，所以比在 HAVING 子句中执行此操作效率更高。HAVING 子句中基于行的条件的筛选会强制查询对那些在 WHERE 子句中会被去除的数据进行分组。

另一个提高效率的技巧是使用 DISTINCT 关键字查找数据行的单独报表，来代替使用 GROUP BY 子句。在这种情况下，使用 DISTINCT 关键字的 SQL 效率更高。请在需要计算聚合函数（SUM、COUNT、MAX 等）的情况下再使用 GROUP BY。另外，如果您的查询总是自己返回一个唯一的行，则不要使用 DISTINCT 关键字。在这种情况下，DISTINCT 关键字只会增加系统开销。

您已经看到了，有大量技术都可用于优化查询和实现特定的业务规则，技巧就是进行一些尝试，然后比较它们的性能。最重要的是要测试、测试、再测试。在此专栏的将来各期内容中，我将继续深入讲述 SQL Server 概念，包括数据库设计、好的索引实践以及 SQL Server 安全范例。

http://www.microsoft.com/china/MSDN/library/data/sqlserver/FiveWaystoRevupYourSQLPerformanCE.mspx?mfr=true

source:

Combine Multiple Columns And Records In MSSQL

source: http://csharpdotnetfreak.blogspot.com/2009/07/mssql-combine-columns-comma-separated.html

In this example i am going to describe how to combine multiple columns and records in one column in MS SQL.

Here is the scenario

I have a table having Employees names and their respective Department names, now i want to show Employees names separated by comma into one column and respective Department name in another column.

My table schema is shown in the image below

And this is Data into table

I want output in following format

Department FirstName
IT amiT,Emp1,Emp5
Admin Shobhit, Emp3,Emp7

and so on

To get this desired result we need to write below mentioned query

SELECT DISTINCT Department, EmpNames = substring( ( SELECT ', ' + FirstName  FROM Employees e2 WHERE e2.Department = e1.Department FOR XML path(''), elements  ),2,500) FROM Employees e1 

And the output of this SQL Query would be

oracle查询效率积累

（1）选择最有效率的表名顺序(只在基于规则的优化器中有效)：

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名，FROM子句中写在最后的表(基础表 driving table)将被最先处理，在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表。如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

（2）WHERE子句中的连接顺序．：

ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.

（3）SELECT子句中避免使用 ‘ * ‘：

ORACLE在解析的过程中, 会将'*' 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间

（4）减少访问数据库的次数：

ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等；

（5）在SQL*Plus , SQL*Forms和Pro*C中重新设置ARRAYSIZE参数, 可以增加每次数据库访问的检索数据量 ,建议值为200

（6）使用DECODE函数来减少处理时间：

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.

（7）整合简单,无关联的数据库访问：

如果你有几个简单的数据库查询语句,你可以把它们整合到一个查询中(即使它们之间没有关系)

（8）删除重复记录：

最高效的删除重复记录方法 ( 因为使用了ROWID)例子：

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID)
FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

（9）用TRUNCATE替代DELETE：

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是恢复到执行删除命令之前的状况) 而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短. (译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)

（10）尽量多使用COMMIT：

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:
COMMIT所释放的资源:
a. 回滚段上用于恢复数据的信息.
b. 被程序语句获得的锁
c. redo log buffer 中的空间
d. ORACLE为管理上述3种资源中的内部花费

（11）用Where子句替换HAVING子句：

避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销. (非oracle中)on、where、having这三个都可以加条件的子句中，on是最先执行，where次之，having最后，因为on是先把不符合条件的记录过滤后才进行统计，它就可以减少中间运算要处理的数据，按理说应该速度是最快的，where也应该比having快点的，因为它过滤数据后才进行sum，在两个表联接时才用on的，所以在一个表的时候，就剩下where跟having比较了。在这单表查询统计的情况下，如果要过滤的条件没有涉及到要计算字段，那它们的结果是一样的，只是where可以使用rushmore技术，而having就不能，在速度上后者要慢如果要涉及到计算的字段，就表示在没计算之前，这个字段的值是不确定的，根据上篇写的工作流程，where的作用时间是在计算之前就完成的，而having就是在计算后才起作用的，所以在这种情况下，两者的结果会不同。在多表联接查询时，on比where更早起作用。系统首先根据各个表之间的联接条件，把多个表合成一个临时表后，再由where进行过滤，然后再计算，计算完后再由having进行过滤。由此可见，要想过滤条件起到正确的作用，首先要明白这个条件应该在什么时候起作用，然后再决定放在那里

（12）减少对表的查询：

在含有子查询的SQL语句中,要特别注意减少对表的查询.例子：

SELECT TAB_NAME FROM TABLES WHERE (TAB_NAME,DB_VER) = ( SELECT

TAB_NAME,DB_VER FROM TAB_COLUMNS WHERE VERSION = 604)

（13）通过内部函数提高SQL效率.：

复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的

（14）使用表的别名(Alias)：

当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上.这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误.

（15）用EXISTS替代IN、用NOT EXISTS替代NOT IN：

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率. 在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

例子：

（高效）SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND EXISTS (SELECT ‘X' FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = ‘MELB')

(低效)SELECT * FROM EMP (基础表) WHERE EMPNO > 0 AND DEPTNO IN(SELECT DEPTNO FROM DEPT WHERE LOC = ‘MELB')

（16）识别'低效执行'的SQL语句：

虽然目前各种关于SQL优化的图形化工具层出不穷,但是写出自己的SQL工具来解决问题始终是一个最好的方法：

SELECT EXECUTIONS , DISK_READS, BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,
SQL_TEXT
FROM V$SQLAREA
WHERE EXECUTIONS>0
AND BUFFER_GETS > 0
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8
ORDER BY 4 DESC;

（17） 用索引提高效率：

索引是表的一个概念部分,用来提高检索数据的效率，ORACLE使用了一个复杂的自平衡B-tree结构. 通常,通过索引查询数据比全表扫描要快. 当ORACLE找出执行查询和Update语句的最佳路径时, ORACLE优化器将使用索引. 同样在联结多个表时使用索引也可以提高效率. 另一个使用索引的好处是,它提供了主键(primary key)的唯一性验证.。那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率. 虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储,也需要定期维护, 每当有记录在表中增减或索引列被修改时, 索引本身也会被修改. 这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O . 因为索引需要额外的存储空间和处理,那些不必要的索引反而会使查询反应时间变慢.。定期的重构索引是有必要的.：

ALTER INDEX REBUILD

（18）用EXISTS替换DISTINCT：

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换, EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果. 例子：

(低效):
SELECT DISTINCT DEPT_NO,DEPT_NAME FROM DEPT D , EMP E

WHERE D.DEPT_NO = E.DEPT_NO
(高效):
SELECT DEPT_NO,DEPT_NAME FROM DEPT D WHERE EXISTS ( SELECT ‘X'
FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO);

（19）sql语句用大写的；因为oracle总是先解析sql语句，把小写的字母转换成大写的再执行

（20）在java代码中尽量少用连接符“＋”连接字符串！

source:

http://blog.csdn.net/dongdongsdo0310/article/details/6729698

Moving SQL NVarchar to ORACLE NVarchar2

之前用SSIS在做ORACLE與SQL間資料搬移時，最痛恨遇到NVarchar跟Unicode Code問題了。

明明是NVarchar(SQL)搬到NVarchar2(ORACLE)，兩邊都Support Unicode，但SSIS都老會要求指定Data Flow Destination的Code Page，接著又會嚷著SQL中得到的Unicode不能直接轉成Non-Unicode，所以就得很可笑地在Nvarchar與NVarchar2兩個Unicode欄位中加上一個Data Conversion。如下圖:

不過，苦難還沒有結束... 如果很不幸地，NVarchar中還真的放了Unicode字元，則在轉換時，會出現以下的錯誤訊息。

Error: 0xC02020C5 at Data Flow Task Failure Sample, Data Conversion [543]: Data conversion failed while converting column "UnicodeField" (505) to column "UnicodeFieldMapping" (557). The conversion returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Error: 0xC020902A at Data Flow Task Failure Sample, Data Conversion [543]: The "output column "UnicodeFieldMapping" (557)" failed because truncation occurred, and the truncation row disposition on "output column "UnicodeFieldMapping" (557)" specifies failure on truncation. A truncation error occurred on the specified object of the specified component.

這問題讓我很困擾，不過因為遇到時都是做些一次性的資料搬移，所以我都很鄉愿地繞路解決(最愛用的一招是回去改用SQL 2000 DTS，很奇怪，用DTS搬資料時，幾乎不曾為Enocding傷過腦筋)，未曾認真與它對決過。

最近同事想用SSIS來做重要的日常資料搬移，就卡在這個問題上，由於搬移是每天要跑的，繞路的成本就會高出許多，這回我總算被迫要收服這隻妖怪。東試西試之餘，忽然想到之前使用Query Express的經驗，Oracle的OLE DB Driver才能正確顯示Unicode，Microsft的OLE DB for Oracle反而不行，所以我試著把Data Flow Destination的OLE DB換成Oracle版。

美妙的事出現了!! 使用Oracle Provider for OLE DB後，連Data Conversion都不用了，直接對Unicode對Unicode，一次搞定。

看一下令人興奮的結果，SQL裡的六頭牛被搬到ORACLE裡了，NVarchar --> NVarchar2，萬牛奔騰的感覺豈是一個爽字可以形容，哈!!

source: http://blog.darkthread.net/post-2007-05-17-kb-ssis-moving-sql-nvarchar-to-oracle-nvarchar2.aspx

Oracle migrate to MySQL

一、從Oracle移植到MySQL主要有六個方面的內容需要移植，一是表Table，包括表結構和數據，二是觸發器Trigger，三是存儲過程Procedure，函數function和包Package，四是任務Job ，五是用戶等其他方面的移植，六是具體應用程序通過SQL語句訪問時的細節差異克服。筆者用來移植測試的數據庫是：Oracle 9i ，MySQL 6.0，Windows 2000環境。

二、經過映射轉換，比如number會轉換為double，date轉換為timestamp等，請小心處理日期字段的默認值等，表的主鍵，表的索引(Oracle的位圖索引會被轉成BTree索引，另外表和字段的註釋會丟失)等信息。需要特別注意的是，Oracle的自增字段的處理。提供字段自增屬性。
三、觸發器的移植首先，MySQL在6.0以後才支持觸發器！觸發器的移植沒有現成工具，因為兩者之間的語法差異較大，您只能通過手工對照著原來的邏輯一個一個添加。
四、存儲過程，函數和程序包的移植程序包是Oracle用來組織邏輯功能的一個Object，MySQL不支持，因此需要將包裡的存儲過程﹑函數等全部放到該數據庫公有過程和函數里面。

五、Job的移植Job是Oracle的定時任務實現的方法，MySQL6中用Event實現，具體語法請參考MySQL手冊。

六、用戶的移植Oracle的用戶管理和MySQL下有較大區別，請分別建立用戶，並賦予合適的權限。

七、應用程序的移植由於語法細節上的差異，導致很多SQL語句需要改寫。筆者記下了所有移植過程中碰到的SQL語句細節差異,列出來以供參考：
1）Oracle的to_char函數不能再使用，換用如CONCAT(14.3)的形式，為了提高應用程序兼容性，建議手工寫一個
2）Oracle的to_date函數不能再使用，建議手工寫一個添加到MySQL數據庫
eg:
to_char(sysdate,'yyyy-mm-dd')-->date_format(sysdate(),'%Y-%m-%d');
to_date(sysdate,'yyyy-mm-dd')-->STR_TO_DATE(sysdate(),'%Y-%m-%d');
to_char(add_months(to_date ('20000101','yyyymmdd'),1),'yyyy-mm-dd')-->date_add('2000-01-01',interval 1 month);

3）Oracle的decode函數不能再使用，換用SELECT CASE 1 WHEN 1 THEN 'one' WHEN 2 THEN 'two' ELSE 'more' END 的形式
4)nvl這樣的一些專用函數，MySQL是沒有的，可以把select nvl(to_char(num),'nothing') from t_equipment轉換成select case num when num then num else 'nothing' end from t_equipment
5）instr之類的函數，函數名相同，但參數個數不同
6）Oracle的sysdate要寫成sysdate()的形式
7）包的形式已經取消，所以原來以包的方式調用的過程如xx_pack.xxx要寫成xxx()
8）帶進製字符轉數字Oracle風格：TO_NUMBER(strTmp,'XX') TO_NUMBER（'9'）MySQL風格：CONV(strTmp,16,10) CONV('9',10,10) 如果字符串前後有加減操作，會隱含轉換成數字
9) 不能再有直接調用序列的形式，如果一定需要，可以模擬實現一個
10）日期直接加減的含義不同了，比如Oracle中sysdate + 1 變成了sysdate() + interval 1 day（注意如果寫成sysdate() + 1 語法還是正確的，但含義是錯誤的）查詢select sysdate() + 1 from dual 在MySQL得到比如20080223153234（= 20080223153233 + 1）的數而在Oracle中會得到第二天當前時刻。
11) MySQL單純的date類型只是日期不帶時間，DATETIME或TIMESTAMP帶有時間，用DATE_FORMAT函數可以控制顯示形式
12）select 'abc' || 'd' from dual 兩個數據執行的結果不同（語法都能通過），MySQL要寫成select concat('abc' , 'd')的形式
13) Oracle高級功能，如帶有暗示索引的select語句，MySQL是不支持的（語法可以通過）
14）有些MySQL的保留字不能直接用在SQL語句裡,要加表名或別名限制，如select RIGHT FROM XX要改成select a.RIGHT FROM XX a
15) Oracle的子查詢可以不起別名，但MySQL是必須的，比如下面的別名aa：select field1 from (select sysdate() as field1 from dual) as aa
16）很多系統表名都是不同的,比如，列出某個表的信息：select * from tab where TName='T_TEST'改成select table_name,table_type from information_schema.tables where table_schema = 'user' and table_name= ' T_TEST '
17）MySQL下update時不能有本身的子查詢update T_TEST set Flag = 0 where field1 in
(select distinct b.field1 from T_TEST b where b.flag=1)
18）Oracle下''和null等價，而MySQL則不然select 1 from dual where '' is null在Oracle下可以取到記錄，在MySQL下不能dual表的使用，substr、trim等函數的主要使用方式和Oracle類似

八、小結和建議看起來，Oracle移植到MySQL似乎挺麻煩，有沒有一鍵完成的簡單辦法？呵呵，我沒有找到，除非您只使用基本表，只使用基本SQL語句訪問它。

source: http://shshy39.javaeye.com/blog/342512

ORA-00031: session marked for kill 處理Oracle中殺不掉的鎖

一些ORACLE中的進程被殺掉後，狀態被置為"killed"，但是鎖定的資源很長時間不釋放，有時實在沒辦法，只好重啟數據庫。現在提供一種方法解決這種問題，那就是在ORACLE中殺不掉的，在OS一級再殺。

1.下面的語句用來查詢哪些對像被鎖：

select object_name,machine,s.sid,s.serial#
from v$locked_object l,dba_objects o ,v$session s
where l.object_id　=　o.object_id and l.session_id=s.sid;
2.下面的語句用來殺死一個進程：
alter system kill session '24,111'; (其中24,111分別是上面查詢出的sid,serial#)
【注】以上兩步，可以通過Oracle的管理控制台來執行。
3.如果利用上面的命令殺死一個進程後，進程狀態被置為"killed"，但是鎖定的資源很長時間沒有被釋放，那麼可以在os一級再殺死相應的進程（線程），首先執行下面的語句獲得進程（線程）號：
select p.spid, osuser, s.program
from v$session s,v$process p
where s.paddr=p.addr and s.sid=24 （24是上面的sid）
4.在OS上殺死這個進程（線程）：
1)在unix上，用root身份執行命令:
#kill -9 12345（即第3步查詢出的spid）
2)在windows（unix也適用）用orakill殺死線程，orakill是oracle提供的一個可執行命令，語法為：
orakill sid thread
其中：
sid：表示要殺死的進程屬於的實例名
thread：是要殺掉的線程號，即第3步查詢出的spid。
例：c:>orakill orcl 12345

ORA-00031: session marked for kill

Cause: The session specified in an ALTER SYSTEM KILL SESSION command cannot be killed immediately (because it is rolling back or blocked on a network operation), but it has been marked for kill. This means it will be killed as soon as possible after its current uninterruptible operation is done.
Action: No action is required for the session to be killed, but further executions of the ALTER SYSTEM KILL SESSION command on this session may cause the session to be killed sooner.

kill -9 12345



source: http://unixboy.javaeye.com/blog/168346
http://www.oracle-base.com/articles/misc/KillingOracleSessions.php

T-SQL While Loop

declare @Part_Id int
declare @Category_Id int
declare @Desc varchar(50)
create table PART (Part_Id int, Category_Id int, Description varchar(50))
set @Part_Id = 0
set @Category_Id = 0
while @Part_Id < 2
begin
set @Part_Id = @Part_Id + 1
while @Category_Id < 3
begin
set @Category_Id = @Category_Id + 1
set @Desc = 'Part_Id is ' + cast(@Part_Id as char(1)) +
' Category_Id ' + cast(@Category_Id as char(1))
insert into PART values(@Part_Id,
@Category_Id,
@Desc )
end
set @Category_Id = 0
end
select * from PART
drop table PART
Here is the output from the SELECT statement at the bottom of this nested WHILE loop example.

Part_Id Category_Id Description
----------- ----------- -----------------------------------------
1 1 Part_Id is 1 Category_Id 1
1 2 Part_Id is 1 Category_Id 2
1 3 Part_Id is 1 Category_Id 3
2 1 Part_Id is 2 Category_Id 1
2 2 Part_Id is 2 Category_Id 2
2 3 Part_Id is 2 Category_Id 3

SQL Server日期函數CONVERT範例

MS SQL Server中文版的預設日期datetime格式是yyyy-mm-dd hh:mm:ss.mmm

例如:
select getdate()

2011-01-05 11:06:08.177

SQL Server常用到日期格式轉換方式如下：

select CONVERT(varchar, getdate(), 120 )
2004-09-12 11:06:08

select replace(replace(replace(CONVERT(varchar, getdate(), 120 ),’-',”),’ ‘,”),’:',”)
20040912110608

select CONVERT(varchar(12) , getdate(), 111 )
2004/09/12



Text Convert to Datetime
SELECT convert(datetime, '09/02/2010', 103)
here 103->英國/法國標準

source: http://technet.microsoft.com/zh-tw/library/ms187928.aspx

make a new schema in Oracle

CREATE USER seasonal IDENTIFIED BY test;

grant resource, connect to seasonal;

Administration - Session

Show all connected users

select username
, sid || ',' || serial# "ID"
, status
, last_call_et "Last Activity"
from v$session
where username is not null
order by status desc
, last_call_et desc;

--------------------------------------------------

Time since last user activity

select username
, floor(last_call_et / 60) "Minutes"
, status
from v$session
where username is not null
order by last_call_et
--------------------------------------------------

Sessions sorted by logon time

select username
, osuser
, sid || ',' || serial# "ID"
, status
, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time
, last_call_et
from v$session
where username is not null
order by login_time;
--------------------------------------------------

Show user info including os pid

select s.sid || ',' || s.serial# "SID/SERIAL"
, s.username
, s.osuser
, p.spid "OS PID"
, s.program
from v$session s
, v$process p
Where s.paddr = p.addr
order by to_number(p.spid);
--------------------------------------------------

Show a users current sql

Select sql_text
from v$sqlarea
where (address, hash_value) in
(select sql_address, sql_hash_value
from v$session
where username like '&username');

--------------------------------------------------

Session status associated with the specified os process id

select s.username
, s.sid
, s.serial#
, p.spid
, last_call_et
, status
from V$SESSION s
, V$PROCESS p
where s.PADDR = p.ADDR
and p.spid='&pid';

--------------------------------------------------

All active sql

set feedback off
set serveroutput on size 9999
column username format a20
column sql_text format a55 word_wrapped
begin
for x in
(select username||'('||sid||','||serial#||') ospid = '|| process ||
' program = ' || program username,
to_char(LOGON_TIME,' Day HH24:MI') logon_time,
to_char(sysdate,' Day HH24:MI') current_time,
sql_address,
sql_hash_value
from v$session
where status = 'ACTIVE'
and rawtohex(sql_address) <> '00'
and username is not null ) loop
for y in (select sql_text
from v$sqlarea
where address = x.sql_address ) loop
if ( y.sql_text not like '%listener.get_cmd%' and
y.sql_text not like '%RAWTOHEX(SQL_ADDRESS)%' ) then
dbms_output.put_line( '--------------------' );
dbms_output.put_line( x.username );
dbms_output.put_line( x.logon_time || ' ' || x.current_time || ' SQL#=' || x.sql_hash_value);
dbms_output.put_line( substr( y.sql_text, 1, 250 ) );
end if;
end loop;
end loop;
end;


--------------------------------------------------

Display any long operations

select username
, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started
, time_remaining remaining
, message
from v$session_longops
where time_remaining = 0
order by time_remaining desc;

--------------------------------------------------

List open cursors per user

select sess.username
, sess.sid
, sess.serial#
, stat.value cursors
from v$sesstat stat
, v$statname sn
, v$session sess
where sess.username is not null
and sess.sid = stat.sid
and stat.statistic# = sn.statistic#
and sn.name = 'opened cursors current'
order by value;

Or alternatively...

select count(hash_value) cursors
, sid
, user_name
from v$open_cursor
group by
sid
, user_name
order by
cursors;



source: http://www.shutdownabort.com/dbaqueries/Administration_Session.php#List-open-cursors-per-user

Killing Oracle Sessions

detail http://www.oracle-base.com/articles/misc/KillingOracleSessions.php

Killing sessions can be very destructive if you kill the wrong session, so be very careful when identifying the session to be killed. If you kill a session belonging to a background process you will cause an instance crash.

Identify the offending session using the V$SESSION or GV$SESSION view as follows.

Identify the offending session using the V$SESSION or GV$SESSION view as follows.

SET LINESIZE 100 COLUMN spid FORMAT A10 COLUMN username FORMAT A10 COLUMN program FORMAT A45  SELECT s.inst_id,        s.sid,        s.serial#,        p.spid,        s.username,        s.program FROM   gv$session s        JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id WHERE  s.type != 'BACKGROUND';     INST_ID        SID    SERIAL# SPID       USERNAME   PROGRAM ---------- ---------- ---------- ---------- ---------- ---------------------------------------------          1         30         15 3859       TEST       sqlplus@oel5-11gr2.localdomain (TNS V1-V3)          1         23        287 3834       SYS        sqlplus@oel5-11gr2.localdomain (TNS V1-V3)          1         40        387 4663                  oracle@oel5-11gr2.localdomain (J000)          1         38        125 4665                  oracle@oel5-11gr2.localdomain (J001)  SQL>

The SID and SERIAL# values of the relevant session can then be substituted into the commands in the following sections.

ALTER SYSTEM KILL SESSION

The basic syntax for killing a session is shown below.

SQL> ALTER SYSTEM KILL SESSION 'sid,serial#';

Oracle (JOB) 更新next_date的探討

Source: http://blog.csdn.net/kamus/archive/2004/12/02/201377.aspx

要:本文通过实验和事件跟踪来分析Oracle Job执行过程中修改下次执行时间的机制。

有些人问，Oracle的JOB在设定完next_date和interval之后，到底是什么时候决定下一次运行时间的。可以归纳成以下几个问题。

1． 假设我们的JOB设定第一次运行的时间是12:00，运行的间隔是1小时，JOB运行需要耗时30分钟，那么第二次运行是在13:00还是13:30？

2． 如果是在13:00那是不是说明只要JOB一开始运行，next_date就被重新计算了？

3． JOB的下一次运行会受到上一次运行时间的影响吗？如果受到影响，如何可以避免这个影响而让JOB在每天的指定时刻运行？

4． 假设我们的JOB设定第一次运行的时间是12:00，运行的间隔是30分钟，JOB运行需要耗时1小时，那么第二次运行是在12:30还是13:00还是根本就会报错？

本文通过一些实验和跟踪来解释上面的所有问题。

首先我们选择一个测试用户，假设该用户名为kamus。

由于我们在实验用的存储过程中会用到dbms_lock包，所以需要由sys用户先授予kamus用户使用dbms_lock包的权限。

d:\Temp>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.5.0 - Production on 星期三 12月 1 23:56:32 2004

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

连接到:

Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.5.0 - Production

SQL> grant execute on dbms_lock to kamus;

授权成功。

然后用kamus用户登录数据库，创建我们测试使用的存储过程sp_test_next_date。

create or replace procedure sp_test_next_date as
p_jobno number;
P_nextdate date;
begin
--将调用此存储过程的job的next_date设置为30分钟以后
select job into p_jobno from user_jobs where what = 'sp_test_next_date;';
execute immediate 'begin dbms_job.next_date(' || to_char(p_jobno) || ',sysdate+1/48);commit;end;';
--修改完毕以后检查user_jobs视图，输出job目前的next_date
select next_date
into P_nextdate
from user_jobs
where what = 'sp_test_next_date;';
dbms_output.put_line('JOB执行中的next_date: ' ||
to_char(p_nextdate,'YYYY-MM-DD HH24:MI:SS'));
--等待10秒再退出执行
dbms_lock.sleep(seconds => 10);
end sp_test_next_date;

创建调用该存储过程的JOB，定义interval为每天一次，也就是这次执行以后，下次执行时间应该在1天以后。

SQL> variable jobno number;

SQL> BEGIN

2 DBMS_JOB.SUBMIT(job => :jobno,

3 what => 'sp_test_next_date;',

4 next_date => SYSDATE,

5 interval => 'SYSDATE+1');

6 COMMIT;

7 END;

8 /

PL/SQL 过程已成功完成。

jobno

---------

1

然后我们手工执行存储过程，执行完毕以后再手工从user_jobs视图中获得JOB的下次执行时间，可以看到在存储过程中修改的JOB的下次执行时间已经生效，变成了当前时间的30分钟以后，而不是默认的1天以后。

SQL> conn kamus

请输入口令:

已连接。

SQL> set serverout on

SQL> exec sp_test_next_date();

JOB执行中的next_date: 2004-12-02 00:44:11

PL/SQL 过程已成功完成。

SQL> col next_date for a20

SQL> select to_char(next_date,'YYYY-MM-DD HH24:MI:SS') next_date from user_jobs

where what = 'sp_test_next_date;';

NEXT_DATE

--------------------

2004-12-02 00:44:11

我们再手工运行JOB，看看这次的结果，可以发现JOB没有运行完毕以前被修改了的下次运行时间跟JOB运行完毕以后再次手工检索user_jobs视图获得的下次运行时间已经不相同了。由此我们可以得出一个结论，next_date是在JOB运行完毕以后被Oracle自动修改的，而不是在JOB刚开始运行的时候，因为我们在存储过程中修改的next_date在JOB运行结束之后又被修改为默认的1天以后了。

SQL> exec dbms_job.run(1);

JOB执行中的next_date: 2004-12-02 00:54:52

PL/SQL 过程已成功完成。

SQL> select to_char(next_date,'YYYY-MM-DD HH24:MI:SS') next_date from user_jobs

where what = 'sp_test_next_date;';

NEXT_DATE

--------------------

2004-12-03 00:24:52

现在我们再次修改存储过程，输出存储过程开始执行的时间，便于跟执行完毕以后的JOB下次执行时间进行比较。

create or replace procedure sp_test_next_date as
p_jobno number;
P_nextdate date;
begin
--输出JOB刚开始执行的时间
dbms_output.put_line(' JOB开始执行的时间: ' ||
to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS'));
--将调用此存储过程的job的next_date设置为30分钟以后
select job into p_jobno from user_jobs where what = 'sp_test_next_date;';
execute immediate 'begin dbms_job.next_date(' || to_char(p_jobno) || ',sysdate+1/48);commit;end;';
--修改完毕以后检查user_jobs视图，输出job目前的next_date
select next_date
into P_nextdate
from user_jobs
where what = 'sp_test_next_date;';
dbms_output.put_line(' JOB执行中的next_date: ' ||
to_char(p_nextdate,'YYYY-MM-DD HH24:MI:SS'));
--等待10秒再退出执行
dbms_lock.sleep(seconds => 10);
end sp_test_next_date;

重新进行测试，我们可以发现JOB的next_date是JOB开始执行时间的1天以后，而不是JOB结束时间的1天以后（因为JOB结束需要经过10秒钟）

SQL> exec dbms_job.run(1);

JOB开始执行的时间: 2004-12-02 00:38:24

JOB执行中的next_date: 2004-12-02 01:08:24

PL/SQL 过程已成功完成。

SQL> select to_char(next_date,'YYYY-MM-DD HH24:MI:SS') next_date from user_jobs

where what = 'sp_test_next_date;';

NEXT_DATE

--------------------

2004-12-03 00:38:24

至此，我们已经说明了两个问题。就是：JOB在运行结束之后才会更新next_date，但是计算的方法是JOB刚开始的时间加上interval设定的间隔。

下面我们通过trace来再次求证这个结论。

SQL> ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

会话已更改。

SQL> exec dbms_job.run(1);

PL/SQL 过程已成功完成。

SQL> ALTER SESSION SET EVENTS '10046 trace name context off';

会话已更改。

执行完毕以后在udump目录中查看生成的trace文件。如果我们用tkprof来格式化这个trace文件然后再查看格式化后的结果，我们会感到很诧异。因为在格式化完毕的SQL执行顺序中，更新job$表的语句出现在dbms_job.next_date语句之前，也就是看上去是Oracle先按照interval自动更新了JOB的next_date，然后才继续往下执行存储过程中定义的next_date更新语句，而这样显然无法解释我们在上面的实验中看到的结果。

但是当我们跳过tkprof而直接去查看生成的trace文件，就会恍然大悟，同时也印证了steve adams在ixora上提到的观点：tkprof格式化完的结果会省略一些信息，甚至在有时候会给我们错误的信息。

直接查看trace文件，我们可以看到如下的执行顺序：

1． parse cursor #10（oracle根据interval和先前保存的this_date字段值更新job$表的语句，包括更新failures, last_date, next_date, total等）

2． parse cursor #15（存储过程中的begin dbms_job.next_date语句）

3． binds cursor #15（将加上了30分钟的时间绑定到cursor #15上）

4． exec cursor #15（执行cursor #15）

5． wait cursor #11（经历一个PL/SQL lock timer事件，也就是存储过程中执行的dbms_lock.sleep方法）

6． binds cursor #10（将JOB刚开始执行时候的时间绑定到cursor #10上）

7． exec cursor #10（执行cursor #10）

也就是说虽然更新job$的语句被很早地解析过了，但是直到JOB运行结束时这个被解析过的游标才开始作变量绑定进而开始执行。

正是因为解析update sys.job$语句的时间早于解析begin dbms_job.next_date语句的时间，所以tkprof的结果将前者放在了前面。

接下来我们进入另外一个问题的探讨，本文最开始提到的第四个问题：

假设我们的JOB设定第一次运行的时间是12:00，运行的间隔是30分钟，JOB运行需要耗时1小时，那么第二次运行是在12:30还是13:00还是根本就会报错？

通过分析trace文件我们可以找到更新next_date的SQL语句是：

update sys.job$ set failures=0, this_date=null, flag=:1, last_date=:2, next_date=greatest(:3,sysdate),total=total+(sysdate - nvl(this_date, sysdate)) where job=:4

注意到更新next_date字段的公式是greatest(:3, sysdate)，此处的:3绑定的是job的this_date+interval。所以我们猜测实际上应该是有一个跟当前时间的比较机制，如果在执行完JOB之后的时间比按照this_date+interval计算出的时间更晚一些，那么next_date就更新为当前时间，也就是几乎会立刻再重新执行JOB。

同样这样的猜测我们也需要通过实验来验证一下。

创建一个新的存储过程sp_test_next_date1，简单地等待2分钟，但是我们将调用这个存储过程的JOB的interval设置为1分钟，看看会有什么情况。

为了更方便得比较，我们创建一个表用来记录每次JOB执行的开始时间。

SQL> create table t (cdate date);

Table created

创建存储过程的脚本

create or replace procedure sp_test_next_date1 as
begin
--输出JOB开始执行的时间
insert into t(cdate) values(sysdate);
commit;
--等待120秒退出
dbms_lock.sleep(seconds => 120);
end sp_test_next_date1;

创建调用此存储过程的JOB

SQL> variable jobno number;

SQL> BEGIN

2 DBMS_JOB.SUBMIT(job => :jobno,

3 what => 'sp_test_next_date1;',

4 next_date => SYSDATE,

5 interval => 'SYSDATE+1/1440');

6 COMMIT;

7 END;

8 /

PL/SQL 过程已成功完成。

jobno

---------

7

执行此JOB，然后过一段时间开始检查表t中的输出。

SQL> select * from t order by cdate;

CDATE

--------------------

2004-12-3 14:10:43

2004-12-3 14:12:47

2004-12-3 14:14:55

2004-12-3 14:16:59

2004-12-3 14:19:07

2004-12-3 14:21:11

6 rows selected

首先我们确认JOB每次都是成功执行了，并没有任何报错，然后检查cdate字段，发现时间间隔都是2分钟左右，也就是说因为JOB本身的interval设定比JOB本身的执行时间要长，所以Oracle将next_date设置为每次JOB结束的时间。

同时我们也注意到，每次开始的时间都有4秒到8秒的延迟，没有继续深究，不确认这是因为oracle本身计算的误差，还是内部比如启动Job Process需要的时长。

不论如何，到此我们也已经回答了第四个问题，即使interval的时长短于JOB执行的时间，整个作业仍然会继续进行，只是执行间隔变为了JOB真实运行的时长。

由于trace文件过长，所以不在本文中贴出了，如果有兴趣可以发邮件给我。我的邮件地址是：kamus@itpub.net

本文的最后一部分，解答本文开头提出的第三个问题，也就是：

JOB的下一次运行会受到上一次运行时间的影响吗？如果受到影响，如何可以避免这个影响而让JOB在每天的指定时刻运行？

JOB的下一次运行时间是会受上一次影响的，如果我们的interval仅仅是sysdate+1/24这样的形式的话，无疑，上次执行的时间再加上1小时就是下次执行的时间。那么如果JOB因为某些原因延迟执行了一次，这样就会导致下一次的执行时间也同样顺延了，这通常不是我们希望出现的现象。

解决方法很简单，只需要设定正确的interval就可以了。

比如，我们要JOB在每天的凌晨3:30执行而不管上次执行到底是几点，只需要设置interval为trunc(SYSDATE)+3.5/24+1即可。完整的SQL如下：

SQL> variable jobno number;

SQL> BEGIN

2 DBMS_JOB.SUBMIT(job => :jobno,

3 what => 'sp_test_next_date;',

4 next_date => SYSDATE,

5 interval => 'trunc(SYSDATE)+3.5/24+1');

6 COMMIT;

7 END;

8 /

BTW：在trace文件中发现虽然通过select rowid from table返回的结果已经是扩展ROWID格式（Data Object number + File + Block + ROW）了，但是oracle内部检索数据仍然在使用限制ROWID格式（Block number.Row number.File number）。

本文涉及到的额外知识可以参看我的其它技术文章：

1． 通过事件跟踪SQL执行的后台步骤

2． Oracle等待事件，比如本文提到的PL/SQL lock timer

3． ROWID格式

作者简介：

张乐奕，网名kamus

曾任ITPUB Oracle认证版版主,现任itpub Oracle管理版版主.

现任职于北京某大型软件公司，首席DBA，主要负责证券行业的全国十数处核心交易系统数据库管理及维护工作。

热切关注Oracle技术和其它相关技术，出没于各大数据库技术论坛，目前是中国最大的Oracle技术论坛www.itpub.net的数据库管理版版主，

阅读更多技术文章和随笔可以登录我的个人blog。
http://blog.cdsn.net/kamus。