20.       用表连接替换EXISTS

     通常来说 , 采用表连接的方式比EXISTS更有效率

      SELECT ENAME

      FROM EMP E

      WHERE EXISTS (SELECT ‘X’

                      FROM DEPT

                      WHERE DEPT_NO = E.DEPT_NO

                      AND DEPT_CAT = ‘A’);

     (更高效)

      SELECT ENAME

      FROM DEPT D,EMP E

      WHERE E.DEPT_NO = D.DEPT_NO

      AND DEPT_CAT = ‘A’ ;

(译者按: 在RBO的情况下,前者的执行路径包括FILTER,后者使用NESTED LOOP)

21.       用EXISTS替换DISTINCT

当提交一个包含一对多表信息(比如部门表和雇员表)的查询时,避免在SELECT子句中使用DISTINCT. 一般可以考虑用EXIST替换

例如:

低效:

    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);

  EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.

22.       识别’低效执行’的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;

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

23.       使用TKPROF 工具来查询SQL性能状态

SQL trace 工具收集正在执行的SQL的性能状态数据并记录到一个跟踪文件中. 这个跟踪文件提供了许多有用的信息,例如解析次数.执行次数,CPU使用时间等.这些数据将可以用来优化你的系统.

设置SQL TRACE在会话级别: 有效

  ALTER SESSION SET SQL_TRACE TRUE

设置SQL TRACE 在整个数据库有效仿, 你必须将SQL_TRACE参数在init.ora中设为TRUE, USER_DUMP_DEST参数说明了生成跟踪文件的目录

(译者按: 这一节中,作者并没有提到TKPROF的用法, 对SQL TRACE的用法也不够准确, 设置SQL TRACE首先要在init.ora中设定TIMED_STATISTICS, 这样才能得到那些重要的时间状态. 生成的trace文件是不可读的,所以要用TKPROF工具对其进行转换,TKPROF有许多执行参数. 大家可以参考ORACLE手册来了解具体的配置. )

17.       使用表的别名(Alias)

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

(译者注: Column歧义指的是由于SQL中不同的表具有相同的Column名,当SQL语句中出现这个Column时,SQL解析器无法判断这个Column的归属)

18.       用EXISTS替代IN

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.

低效:

SELECT *

FROM EMP (基础表)

WHERE EMPNO > 0

AND DEPTNO IN (SELECT DEPTNO

FROM DEPT

WHERE LOC = ‘MELB’)

高效:

SELECT *

FROM EMP (基础表)

WHERE EMPNO > 0

AND EXISTS (SELECT ‘X’

FROM DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

AND LOC = ‘MELB’)

 (译者按: 相对来说,用NOT EXISTS替换NOT IN 将更显著地提高效率,下一节中将指出)

19.       用NOT EXISTS替代NOT IN

在子查询中,NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下,NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历).  为了避免使用NOT IN ,我们可以把它改写成外连接(Outer Joins)或NOT EXISTS.

例如:

SELECT …

FROM EMP

WHERE DEPT_NO NOT IN (SELECT DEPT_NO

                         FROM DEPT

                         WHERE DEPT_CAT=’A’);

为了提高效率.改写为:

(方法一: 高效)

SELECT ….

FROM EMP A,DEPT B

WHERE A.DEPT_NO = B.DEPT(+)

AND B.DEPT_NO IS NULL

AND B.DEPT_CAT(+) = ‘A’

(方法二: 最高效)

SELECT ….

FROM EMP E

WHERE NOT EXISTS (SELECT ‘X’

                    FROM DEPT D

                    WHERE D.DEPT_NO = E.DEPT_NO

                    AND DEPT_CAT = ‘A’);

(待续)

13.       计算记录条数

     和一般的观点相反, count(*) 比count(1)稍快 , 当然如果可以通过索引检索,对索引列的计数仍旧是最快的. 例如 COUNT(EMPNO)

(译者按: 在CSDN论坛中,曾经对此有过相当热烈的讨论, 作者的观点并不十分准确,通过实际的测试,上述三种方法并没有显著的性能差别)

14.       用Where子句替换HAVING子句

     避免使用HAVING子句, HAVING 只会在检索出所有记录之后才对结果集进行过滤. 这个处理需要排序,总计等操作. 如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销.

例如:

     低效:

     SELECT REGION，AVG(LOG_SIZE)

     FROM LOCATION

     GROUP BY REGION

     HAVING REGION REGION != ‘SYDNEY’

     AND REGION != ‘PERTH’

     高效

     SELECT REGION，AVG(LOG_SIZE)

     FROM LOCATION

     WHERE REGION REGION != ‘SYDNEY’

     AND REGION != ‘PERTH’

     GROUP BY REGION

(译者按: HAVING 中的条件一般用于对一些集合函数的比较,如COUNT() 等等. 除此而外,一般的条件应该写在WHERE子句中)

15.       减少对表的查询

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

例如:

     低效

          SELECT TAB_NAME

          FROM TABLES

          WHERE TAB_NAME = ( SELECT TAB_NAME

                                FROM TAB_COLUMNS

                                WHERE VERSION = 604)

          AND　DB_VER= ( SELECT DB_VER

                           FROM TAB_COLUMNS

                           WHERE VERSION = 604)

     高效

          SELECT TAB_NAME

          FROM TABLES

          WHERE  (TAB_NAME,DB_VER)

 = ( SELECT TAB_NAME,DB_VER)

                   FROM TAB_COLUMNS

                   WHERE VERSION = 604)

     Update 多个Column 例子:

     低效:

           UPDATE EMP

           SET EMP_CAT = (SELECT MAX(CATEGORY) FROM EMP_CATEGORIES),

              SAL_RANGE = (SELECT MAX(SAL_RANGE) FROM EMP_CATEGORIES)

           WHERE EMP_DEPT = 0020;

     高效:

           UPDATE EMP

           SET (EMP_CAT, SAL_RANGE)

 = (SELECT MAX(CATEGORY) , MAX(SAL_RANGE)

 FROM EMP_CATEGORIES)

           WHERE EMP_DEPT = 0020;

16.       通过内部函数提高SQL效率.

     SELECT H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC,COUNT(*)

     FROM HISTORY_TYPE T,EMP E,EMP_HISTORY H

     WHERE H.EMPNO = E.EMPNO

AND H.HIST_TYPE = T.HIST_TYPE

GROUP BY H.EMPNO,E.ENAME,H.HIST_TYPE,T.TYPE_DESC;

通过调用下面的函数可以提高效率.

FUNCTION LOOKUP_HIST_TYPE(TYP IN NUMBER) RETURN VARCHAR2

AS

    TDESC VARCHAR2(30);

    CURSOR C1 IS 

        SELECT TYPE_DESC

        FROM HISTORY_TYPE

        WHERE HIST_TYPE = TYP;

BEGIN

    OPEN C1;

    FETCH C1 INTO TDESC;

    CLOSE C1;

    RETURN (NVL(TDESC,’?’));

END;

FUNCTION LOOKUP_EMP(EMP IN NUMBER) RETURN VARCHAR2

AS

    ENAME VARCHAR2(30);

    CURSOR C1 IS 

        SELECT ENAME

        FROM EMP

        WHERE EMPNO=EMP;

BEGIN

    OPEN C1;

    FETCH C1 INTO ENAME;

    CLOSE C1;

    RETURN (NVL(ENAME,’?’));

END;

SELECT H.EMPNO,LOOKUP_EMP(H.EMPNO),

H.HIST_TYPE,LOOKUP_HIST_TYPE(H.HIST_TYPE),COUNT(*)

FROM EMP_HISTORY H

GROUP BY H.EMPNO , H.HIST_TYPE;

(译者按: 经常在论坛中看到如’能不能用一个SQL写出….’ 的贴子, 殊不知复杂的SQL往往牺牲了执行效率. 能够掌握上面的运用函数解决问题的方法在实际工作中是非常有意义的)

8.       使用DECODE函数来减少处理时间

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

例如:

   SELECT COUNT(*)，SUM(SAL)

   FROM　EMP

   WHERE DEPT_NO = 0020

   AND ENAME LIKE　‘SMITH%’;

   SELECT COUNT(*)，SUM(SAL)

   FROM　EMP

   WHERE DEPT_NO = 0030

   AND ENAME LIKE　‘SMITH%’;

你可以用DECODE函数高效地得到相同结果

SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,

        COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,

        SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,

        SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL

FROM EMP WHERE ENAME LIKE ‘SMITH%’;

类似的,DECODE函数也可以运用于GROUP BY 和ORDER BY子句中.

9.       整合简单,无关联的数据库访问

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

例如:

SELECT NAME

FROM EMP

WHERE EMP_NO = 1234;

SELECT NAME

FROM DPT

WHERE DPT_NO = 10 ;

SELECT NAME

FROM CAT

WHERE CAT_TYPE = ‘RD’;

上面的3个查询可以被合并成一个:

SELECT E.NAME , D.NAME , C.NAME

FROM CAT C , DPT D , EMP E,DUAL X

WHERE NVL(‘X’,X.DUMMY) = NVL(‘X’,E.ROWID(+))

AND NVL(‘X’,X.DUMMY) = NVL(‘X’,D.ROWID(+))

AND NVL(‘X’,X.DUMMY) = NVL(‘X’,C.ROWID(+))

AND E.EMP_NO(+) = 1234

AND D.DEPT_NO(+) = 10

AND C.CAT_TYPE(+) = ‘RD’;

(译者按: 虽然采取这种方法,效率得到提高,但是程序的可读性大大降低,所以读者 还是要权衡之间的利弊)

10.       删除重复记录

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

DELETE FROM EMP E

WHERE E.ROWID > (SELECT MIN(X.ROWID)

                   FROM EMP X

                   WHERE X.EMP_NO = E.EMP_NO);

11.       用TRUNCATE替代DELETE

当删除表中的记录时,在通常情况下, 回滚段(rollback segments ) 用来存放可以被恢复的信息. 如果你没有COMMIT事务,ORACLE会将数据恢复到删除之前的状态(准确地说是

恢复到执行删除命令之前的状况)

而当运用TRUNCATE时, 回滚段不再存放任何可被恢复的信息.当命令运行后,数据不能被恢复.因此很少的资源被调用,执行时间也会很短.

(译者按: TRUNCATE只在删除全表适用,TRUNCATE是DDL不是DML)

12.       尽量多使用COMMIT

只要有可能,在程序中尽量多使用COMMIT, 这样程序的性能得到提高,需求也会因为COMMIT所释放的资源而减少:

 COMMIT所释放的资源:

a.       回滚段上用于恢复数据的信息.

b.       被程序语句获得的锁

c.       redo log buffer 中的空间

d.       ORACLE为管理上述3种资源中的内部花费

(译者按: 在使用COMMIT时必须要注意到事务的完整性,现实中效率和事务完整性往往是鱼和熊掌不可得兼)

4. 选择最有效率的表名顺序(只在基于规则的优化器中有效)

ORACLE的解析器按照从右到左的顺序处理FROM子句中的表名,因此FROM子句中写在最后的表(基础表 driving table)将被最先处理. 在FROM子句中包含多个表的情况下,你必须选择记录条数最少的表作为基础表.当ORACLE处理多个表时, 会运用排序及合并的方式连接它们.首先,扫描第一个表(FROM子句中最后的那个表)并对记录进行派序,然后扫描第二个表(FROM子句中最后第二个表),最后将所有从第二个表中检索出的记录与第一个表中合适记录进行合并.

例如:

     表 TAB1 16,384 条记录

     表 TAB2 1      条记录

     选择TAB2作为基础表 (最好的方法)

     select count(*) from tab1,tab2   执行时间0.96秒

    选择TAB2作为基础表 (不佳的方法)

     select count(*) from tab2,tab1   执行时间26.09秒

如果有3个以上的表连接查询, 那就需要选择交叉表(intersection table)作为基础表, 交叉表是指那个被其他表所引用的表.

例如:

   EMP表描述了LOCATION表和CATEGORY表的交集.

 SELECT *

FROM LOCATION L ,

      CATEGORY C,

      EMP E

WHERE E.EMP_NO BETWEEN 1000 AND 2000

AND E.CAT_NO = C.CAT_NO

AND E.LOCN = L.LOCN

将比下列SQL更有效率

SELECT *

FROM EMP E ,

LOCATION L ,

      CATEGORY C

WHERE  E.CAT_NO = C.CAT_NO

AND E.LOCN = L.LOCN

AND E.EMP_NO BETWEEN 1000 AND 2000

5.       WHERE子句中的连接顺序．

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

例如:

(低效,执行时间156.3秒)

SELECT …

FROM EMP E

WHERE  SAL > 50000

AND    JOB = ‘MANAGER’

AND    25 < (SELECT COUNT(*) FROM EMP

             WHERE MGR=E.EMPNO);

(高效,执行时间10.6秒)

SELECT …

FROM EMP E

WHERE 25 < (SELECT COUNT(*) FROM EMP

             WHERE MGR=E.EMPNO)

AND    SAL > 50000

AND    JOB = ‘MANAGER’;

6.     SELECT子句中避免使用‘ * ‘

当你想在SELECT子句中列出所有的COLUMN时,使用动态SQL列引用‘*’ 是一个方便的方法.不幸的是,这是一个非常低效的方法. 实际上,ORACLE在解析的过程中, 会将’*’ 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间.

7.     减少访问数据库的次数

当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.

例如,

    以下有三种方法可以检索出雇员号等于0342或0291的职员.

方法1 (最低效)

    SELECT EMP_NAME , SALARY , GRADE

    FROM EMP

    WHERE EMP_NO = 342;

    SELECT EMP_NAME , SALARY , GRADE

    FROM EMP

    WHERE EMP_NO = 291;

方法2 (次低效)

    DECLARE

        CURSOR C1 (E_NO NUMBER) IS

        SELECT EMP_NAME,SALARY,GRADE

        FROM EMP

        WHERE EMP_NO = E_NO;

    BEGIN

        OPEN C1(342);

        FETCH C1 INTO …,..,.. ;

        …..

        OPEN C1(291);

       FETCH C1 INTO …,..,.. ;

         CLOSE C1;

      END;

方法3 (高效)

    SELECT A.EMP_NAME , A.SALARY , A.GRADE,

            B.EMP_NAME , B.SALARY , B.GRADE

    FROM EMP A,EMP B

    WHERE A.EMP_NO = 342

    AND   B.EMP_NO = 291;

注意:

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

(待续)

   ORACLE的优化器共有3种:

   a.  RULE (基于规则)   b. COST (基于成本)  c. CHOOSE (选择性)

   设置缺省的优化器,可以通过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你当然也在SQL句级或是会话(session)级对其进行覆盖.

   为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须经常运行analyze 命令,以增加数据库中的对象统计信息(object statistics)的准确性.

   在缺省情况下,ORACLE采用CHOOSE优化器, 为了避免那些不必要的全表扫描(full table scan) , 你必须尽量避免使用CHOOSE优化器,而直接采用基于规则或者基于成本的优化器.

ORACLE 采用两种访问表中记录的方式:

a.       全表扫描

            全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描.

b.       通过ROWID访问表

       你可以采用基于ROWID的访问方式情况,提高访问表的效率, , ROWID包含了表中记录的物理位置信息..ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.

为了不重复解析相同的SQL语句,在第一次解析之后, ORACLE将SQL语句存放在内存中.这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它

和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的

     可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering) ,这个功能并不适用于多表连接查询.

数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了.

 这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须

完全相同(包括空格,换行等).

     共享的语句必须满足三个条件:

A.      字符级的比较:

当前被执行的语句和共享池中的语句必须完全相同.

      例如:

          SELECT * FROM EMP;

      和下列每一个都不同

          SELECT * from EMP;

          Select * From Emp;

          SELECT      *     FROM EMP;

B.      两个语句所指的对象必须完全相同:

例如:

   用户     对象名           如何访问

Jack       sal_limit          private synonym

             Work_city      public synonym

             Plant_detail     public synonym

Jill         sal_limit          private synonym

             Work_city      public synonym

    考虑一下下列SQL语句能否在这两个用户之间共享.

C.      两个SQL语句中必须使用相同的名字的绑定变量(bind variables)

例如：

第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)

a.

select pin , name from people where pin = :blk1.pin;

b.

select pin , name from people where pin = :blk1.ot_ind;

select pin , name from people where pin = :blk1.ov_ind;

   (待续)

功能: 用于备份当前用户所拥有的所有PL/SQL objects (包括

TYPE,TYPE BODY, PROCEDURE , FUNCTION, PACKAGE, PACKAGE BODY or JAVA SOURCE )

原理: 对USER_SOURCE数据字典的调用,得到所有的

PL/SQL 代码.

使用方法举例:

1.     在C盘建立目录C:EXPORT

2.     将EXPORT_SOURCE.SQL和EXTRACT_SOURCE.SQL拷贝到C盘根目录.

3.     登陆SQLPLUS , CONNECT SCOTT/TIGER

4.     运行@C:EXPORT_SOURCE.SQL

5.     执行结束,所有的SCOTT拥有的PL/SQL object的代码文件建立在C:EXPORT目录里．

后缀名.PKS 表示PACKAGE

后缀名.PKB 表示PACKAGE BODY

后缀名.SQL 表示其他OBJECTS

备注：

如果想得到数据库中各个SCHEMA的PL/SQL objects ,只需把工具代码中的USER_SOURCE改成DBA_SOURCE,由SYSTEM

运行即可.

工具代码：

export_source.sql

SET SERVEROUTPUT ON SIZE 1000000

SET ECHO OFF VERIFY OFF FEEDBACK OFF TRIMSPOOL ON PAGES 0 LINES 512

SET TERMOUT OFF

SET TERMOUT ON

PROMPT

PROMPT PL/SQL export utility

PROMPT

PROMPT This utilty exports all of the current schema&apos;s PL/SQL source code into

PROMPT a subdirectory called export.

PROMPT

PROMPT Exporting current user&apos;s source to folder ./export

SET TERMOUT OFF

SPOOL temp_source_extract.sql

PROMPT SET ECHO OFF VERIFY OFF FEEDBACK OFF TRIMSPOOL ON TERMOUT OFF PAGES 0 LINES 512

DECLARE

    /*

    || This cursor extracts each PL/SQL stored procedure&apos;s name and procedure type

    */

    CURSOR cur_source_programs

    IS

        SELECT    distinct us.name, us.type,

                  us.name || decode(us.type, &apos;PACKAGE&apos;,      &apos;.PKS&apos;,

                                             &apos;PACKAGE BODY&apos;, &apos;.PKB&apos;,

                                             &apos;.SQL&apos;) spool_file

        FROM      user_source us

        ORDER BY  us.name, us.type;

BEGIN

    FOR cur_source_programs_row IN cur_source_programs

    LOOP

        dbms_output.put_line(&apos;spool export&apos; || user || &apos;_&apos; || cur_source_programs_row.spool_file);

        dbms_output.put_line(&apos;@extract_source &apos; || cur_source_programs_row.name || &apos; "&apos; || cur_source_programs_row.type || &apos;"&apos;);

        dbms_output.put_line(&apos;spool off&apos;);

    END LOOP;

END;

/

SPOOL OFF

@temp_source_extract

SET FEEDBACK ON VERIFY ON TERMOUT ON

PROMPT Export complete!

PROMPT　

Extract_source.sql:

SET HEAD OFF VERIFY OFF

prompt --************************************************************************************--;

prompt --*;

prompt --*  SCRIPT:        &2 &1;

prompt --*;

prompt --*  AUTHOR:;

prompt --*;                

prompt --*;

prompt --*  PURPOSE:;

prompt --*;                

prompt --*;

prompt --*;                

prompt --*;               

prompt --*;

prompt --*  PARAMETERS:;

prompt --*;               

prompt --*;

prompt --*  DEPENDENCIES:  none;

prompt --*;

prompt --*  REVISIONS:;

prompt --*  Ver        Date        Author              Description;

prompt --*  ---------  ----------  ------------------  ------------------------------------;

prompt --* ;        

prompt --*;

prompt --*************************************************************************************--;

SELECT    DECODE(ROWNUM, 1, &apos;CREATE OR REPLACE &apos;|| RTRIM(RTRIM(us.text, CHR(10) )),

                            RTRIM(RTRIM(us.text, CHR(10) ))) text

FROM      user_source us

WHERE     us.name = &apos;&1&apos;

AND       us.type = &apos;&2&apos;

ORDER BY  us.line;

PROMPT /

PROMPT

在基于低版本的ORACLE做一些项目的过程中,有时会遇到一些头疼的问题.,比如想在执行当前一个由多个DML组成的transaction(事务)时,为每一步DML记录一些信息到跟踪表中,由于事务的原子性,这些跟踪信息的提交将决定于主事务的commit或rollback. 这样一来写程序的难度就增大了, 程序员不得不把这些跟踪信息记录到类似数组的结构中,然后在主事务结束后把它们存入跟踪表.哎,真是麻烦!

有没有一个简单的方法解决类似问题呢？

ORACLE8i的AUTONOMOUS TRANSACTION(自治事务，以下AT)是一个很好的回答。

AT 是由主事务(以下MT)调用但是独立于它的事务。在AT被调用执行时，MT被挂起，在AT内部，一系列的DML可以被执行并且commit或rollback.

注意由于AT的独立性，它的commit和rollback并不影响MT的执行效果。在AT执行结束后，主事务获得控制权，又可以继续执行了。

见图1：

图1：

如何实现AT的定义呢？我们来看一下它的语法。其实非常简单。

只需下列PL/SQL的声明部分加上PRAGMA AUTONOMOUS_TRANSACTION 就可以了。

1．  顶级的匿名PL/SQL块

2．  Functions 或 Procedure（独立声明或声明在package中都可）

3．  SQL Object Type的方法

4．  触发器。

比如：

在一个独立的procedure中声明AT

CREATE OR REPLACE PROCEDURE

   Log_error(error_msg IN VARCHAR2(100))

IS

   PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

   Insert into Error_log values ( sysdate,error_msg);

   COMMIT;

END;

下面我们来看一个例子，（win2000 advanced server + oracle8.1.6 , connect as scott）

建立一个表:

create table msg (msg varchar2(120));

首先,用普通的事务写个匿名PL/SQL块：

declare

   cnt  number := -1;   --} Global variables

   procedure local is

   begin

      select count(*) into cnt from msg;

      dbms_output.put_line(&apos;local: # of rows is &apos;||cnt);

      insert into msg values (&apos;New Record&apos;);

      commit;

   end;

   begin

      delete from msg ;

      commit;

      insert into msg values (&apos;Row 1&apos;);

      local;

      select count(*) into cnt from msg;

      dbms_output.put_line(&apos;main: # of rows is &apos;||cnt);

      rollback;

      local;

      insert into msg values (&apos;Row 2&apos;);

      commit;

      local;

      select count(*) into cnt from msg;

      dbms_output.put_line(&apos;main: # of rows is &apos;||cnt);

   end;

运行结果(注意打开serveroutput)

local: # of rows is 1   -> 子程序local中可以’看到’主匿名块中的uncommitted记录

main: # of rows is 2    -> 主匿名块可以’看到’2条记录(它们都是被local commit掉的)

local: # of rows is 2   -> 子程序local首先’看到’2条记录,然后又commit了第三条记录

local: # of rows is 4   -> 子程序local又’看到’了新增加的记录(它们都是被local commit掉的),然后又commit了第五条记录

main: # of rows is 5    -> 主匿名块最后’看到’了所有的记录.

从这个例子中,我们看到COMMIT和ROLLBACK的位置无论是在主匿名块中或者在子程序中,都会影响到整个当前事务.

现在用AT改写一下匿名块中的procedure local:

...

   procedure local is

      pragma AUTONOMOUS_TRANSACTION;

   begin

...

重新运行(注意打开serveroutput)

local: # of rows is 0   -> 子程序local中无法可以’看到’主匿名块中的uncommitted记录 (因为它是独立的)

main: # of rows is 2    -> 主匿名块可以’看到’2条记录,但只有一条是被commited.

local: # of rows is 1   -> 子程序local中可以’看到’它前一次commit的记录,但是主匿名块中的记录已经被提前rollback了

local: # of rows is 3   -> 子程序local 中可以’看到’3条记录包括主匿名块commit的记录

main: # of rows is 4    ->主匿名块最后’看到’了所有的记录.

很明显,AT是独立的,在它执行时,MT被暂停了. AT的COMMIT,ROLLBACK并不影响MT的执行.

运用AT时,有一些注意事项,简单列举如下:

1.     在匿名PL/SQL块中,只有顶级的匿名PL/SQL块可以被设为AT

2.     如果AT试图访问被MT控制的资源,可能有deadlock发生.

3.     Package 不能被声明为AT,只有package所拥有的function和procedure 才能声明为AT

4.     AT程序必须以commit 或rollback结尾,否则会产生Oracle错误ORA-06519: active autonomous transaction detected and rolled back

在程序开发时,如果充分运用AUTONOMOUS TRANSACTION的特性,一定能取得事倍功半的效果.

参考资料:

metalink.oracle.com

oracle8i manual

1、最好做一个物理的库的全备              

2、使用sqlplus 启动库至mount               
 sqlplus /nolog                      
 sql>connect internal                      
 sql>startup mount                      
3、确保所有的数据文件都处于"END BACKUP"状态                      
 sql>set pages 0 feedback off lines 132                      
 sql>spool alter_df.sql                      
 sql>SELECT &apos;alter database datafile &apos;||file_name||&apos; END BACKUP;&apos; from v$datafile;                       
 sql>spool off                      
 sql>@alter_df.sql                      
4、试着打开数据库                      
 sql>alter database open;                      
 如数据库成功打开，余下的都不需要做了，到此为止                      
5、如果你在打开时被要求进行恢复，使用"UNTIL CANCEL"这种进行恢复,然后再发出ALTER DATABASE OPEN RESETLOGS这个命令                      
  sql>recover database until cancel;                      
  sql>alter database open resetlogs;                      
6、如果数据库仍不能打开，把库down掉                      
  sql>shutdown immediate                      
7、在init<sid>.ora中加入如下参数                      
   _allow_resetlogs_corruption=TRUE                      
8、执行如下语句                      
 sql>connect internal                      
 sql>startup mount                             
 sql>@alter_df.sql                      
 sql>alter database open                      
9、如在alter database open时仍旧报错，使用until cancel恢复                      
 sql>recover database until cancel;                       
 sql>alter database open resetlogs;                      
10、经过"9",数据库一定被打开了，数据库被打开后，马上执行一个full export                      
11、down掉库,去掉_all_resetlogs_corrupt参数                       
12、重建库                      
13、import并完成恢复                      
14、建议执行一下ANALYZE TABLE ...VALIDATE STRUCTURE CASCADE;