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