20.?????? 用表連接替換EXISTS

?

???? 通常來(lái)說(shuō) , 采用表連接的方式比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的情況下,前者的執(zhí)行路徑包括FILTER,后者使用NESTED LOOP)

?

21.?????? 用EXISTS替換DISTINCT

當(dāng)提交一個(gè)包含一對(duì)多表信息(比如部門表和雇員表)的查詢時(shí),避免在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 使查詢更為迅速,因?yàn)?/span>RDBMS核心模塊將在子查詢的條件一旦滿足后,立刻返回結(jié)果.

?

22.?????? 識(shí)別’低效執(zhí)行’的SQL語(yǔ)句

?

用下列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;

????

(譯者按: 雖然目前各種關(guān)于SQL優(yōu)化的圖形化工具層出不窮,但是寫出自己的SQL工具來(lái)解決問(wèn)題始終是一個(gè)最好的方法)

?

23.?????? 使用TKPROF 工具來(lái)查詢SQL性能狀態(tài)

?

SQL trace 工具收集正在執(zhí)行的SQL的性能狀態(tài)數(shù)據(jù)并記錄到一個(gè)跟蹤文件中. 這個(gè)跟蹤文件提供了許多有用的信息,例如解析次數(shù).執(zhí)行次數(shù),CPU使用時(shí)間等.這些數(shù)據(jù)將可以用來(lái)優(yōu)化你的系統(tǒng).

?

設(shè)置SQL TRACE在會(huì)話級(jí)別: 有效

?

? ALTER SESSION SET SQL_TRACE TRUE

?

設(shè)置SQL TRACE 在整個(gè)數(shù)據(jù)庫(kù)有效仿, 你必須將SQL_TRACE參數(shù)在init.ora中設(shè)為TRUE, USER_DUMP_DEST參數(shù)說(shuō)明了生成跟蹤文件的目錄

?

(譯者按: 這一節(jié)中,作者并沒(méi)有提到TKPROF的用法, 對(duì)SQL TRACE的用法也不夠準(zhǔn)確, 設(shè)置SQL TRACE首先要在init.ora中設(shè)定TIMED_STATISTICS, 這樣才能得到那些重要的時(shí)間狀態(tài). 生成的trace文件是不可讀的,所以要用TKPROF工具對(duì)其進(jìn)行轉(zhuǎn)換,TKPROF有許多執(zhí)行參數(shù). 大家可以參考ORACLE手冊(cè)來(lái)了解具體的配置. )

17.?????? 使用表的別名(Alias)

當(dāng)在SQL語(yǔ)句中連接多個(gè)表時(shí), 請(qǐng)使用表的別名并把別名前綴于每個(gè)Column上.這樣一來(lái),就可以減少解析的時(shí)間并減少那些由Column歧義引起的語(yǔ)法錯(cuò)誤.

?

(譯者注: Column歧義指的是由于SQL中不同的表具有相同的Column名,當(dāng)SQL語(yǔ)句中出現(xiàn)這個(gè)Column時(shí),SQL解析器無(wú)法判斷這個(gè)Column的歸屬)

?

18.?????? 用EXISTS替代IN

在許多基于基礎(chǔ)表的查詢中,為了滿足一個(gè)條件,往往需要對(duì)另一個(gè)表進(jìn)行聯(lián)接.在這種情況下, 使用EXISTS(或NOT EXISTS)通常將提高查詢的效率.

?

低效:

SELECT *

FROM EMP (基礎(chǔ)表)

WHERE EMPNO > 0

AND DEPTNO IN (SELECT DEPTNO

FROM DEPT

WHERE LOC = ‘MELB’)

???

高效:

SELECT *

FROM EMP (基礎(chǔ)表)

WHERE EMPNO > 0

AND EXISTS (SELECT ‘X’

FROM DEPT

WHERE DEPT.DEPTNO = EMP.DEPTNO

AND LOC = ‘MELB’)

?

?

?(譯者按: 相對(duì)來(lái)說(shuō),用NOT EXISTS替換NOT IN 將更顯著地提高效率,下一節(jié)中將指出)

?

?

19.?????? 用NOT EXISTS替代NOT IN

在子查詢中,NOT IN子句將執(zhí)行一個(gè)內(nèi)部的排序和合并. 無(wú)論在哪種情況下,NOT IN都是最低效的 (因?yàn)樗鼘?duì)子查詢中的表執(zhí)行了一個(gè)全表遍歷).? 為了避免使用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’);

?

(待續(xù))

13.?????? 計(jì)算記錄條數(shù)

???? 和一般的觀點(diǎn)相反, count(*) 比count(1)稍快 , 當(dāng)然如果可以通過(guò)索引檢索,對(duì)索引列的計(jì)數(shù)仍舊是最快的. 例如 COUNT(EMPNO)

?

(譯者按: 在CSDN論壇中,曾經(jīng)對(duì)此有過(guò)相當(dāng)熱烈的討論, 作者的觀點(diǎn)并不十分準(zhǔn)確,通過(guò)實(shí)際的測(cè)試,上述三種方法并沒(méi)有顯著的性能差別)

?

14.?????? 用Where子句替換HAVING子句

?

???? 避免使用HAVING子句, HAVING 只會(huì)在檢索出所有記錄之后才對(duì)結(jié)果集進(jìn)行過(guò)濾. 這個(gè)處理需要排序,總計(jì)等操作. 如果能通過(guò)WHERE子句限制記錄的數(shù)目,那就能減少這方面的開銷.

?

例如:

?

???? 低效:

???? 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 中的條件一般用于對(duì)一些集合函數(shù)的比較,如COUNT() 等等. 除此而外,一般的條件應(yīng)該寫在WHERE子句中)

?

15.?????? 減少對(duì)表的查詢

在含有子查詢的SQL語(yǔ)句中,要特別注意減少對(duì)表的查詢.

??

例如:

???? 低效

???? ?????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 多個(gè)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.?????? 通過(guò)內(nèi)部函數(shù)提高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;

?

通過(guò)調(diào)用下面的函數(shù)可以提高效率.

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;

?

(譯者按: 經(jīng)常在論壇中看到如’能不能用一個(gè)SQL寫出….’ 的貼子, 殊不知復(fù)雜的SQL往往犧牲了執(zhí)行效率. 能夠掌握上面的運(yùn)用函數(shù)解決問(wèn)題的方法在實(shí)際工作中是非常有意義的)

8.?????? 使用DECODE函數(shù)來(lái)減少處理時(shí)間

?

使用DECODE函數(shù)可以避免重復(fù)掃描相同記錄或重復(fù)連接相同的表.

?

例如:

?? 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函數(shù)高效地得到相同結(jié)果

?

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函數(shù)也可以運(yùn)用于GROUP BY 和ORDER BY子句中.

?

?

9.?????? 整合簡(jiǎn)單,無(wú)關(guān)聯(lián)的數(shù)據(jù)庫(kù)訪問(wèn)

?

如果你有幾個(gè)簡(jiǎn)單的數(shù)據(jù)庫(kù)查詢語(yǔ)句,你可以把它們整合到一個(gè)查詢中(即使它們之間沒(méi)有關(guān)系)

例如:

?

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個(gè)查詢可以被合并成一個(gè):

?

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

?

(譯者按: 雖然采取這種方法,效率得到提高,但是程序的可讀性大大降低,所以讀者?還是要權(quán)衡之間的利弊)

?

10.?????? 刪除重復(fù)記錄

最高效的刪除重復(fù)記錄方法 ( 因?yàn)槭褂昧?/span>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

當(dāng)刪除表中的記錄時(shí),在通常情況下, 回滾段(rollback segments ) 用來(lái)存放可以被恢復(fù)的信息. 如果你沒(méi)有COMMIT事務(wù),ORACLE會(huì)將數(shù)據(jù)恢復(fù)到刪除之前的狀態(tài)(準(zhǔn)確地說(shuō)是

恢復(fù)到執(zhí)行刪除命令之前的狀況)

?

而當(dāng)運(yùn)用TRUNCATE時(shí), 回滾段不再存放任何可被恢復(fù)的信息.當(dāng)命令運(yùn)行后,數(shù)據(jù)不能被恢復(fù).因此很少的資源被調(diào)用,執(zhí)行時(shí)間也會(huì)很短.

?

(譯者按: TRUNCATE只在刪除全表適用,TRUNCATE是DDL不是DML)

?

?

12.?????? 盡量多使用COMMIT

?

只要有可能,在程序中盡量多使用COMMIT, 這樣程序的性能得到提高,需求也會(huì)因?yàn)?/span>COMMIT所釋放的資源而減少:

?COMMIT所釋放的資源:

a.?????? 回滾段上用于恢復(fù)數(shù)據(jù)的信息.

b.?????? 被程序語(yǔ)句獲得的鎖

c.?????? redo log buffer 中的空間

d.?????? ORACLE為管理上述3種資源中的內(nèi)部花費(fèi)

?

(譯者按: 在使用COMMIT時(shí)必須要注意到事務(wù)的完整性,現(xiàn)實(shí)中效率和事務(wù)完整性往往是魚和熊掌不可得兼)

4. 選擇最有效率的表名順序(只在基于規(guī)則的優(yōu)化器中有效)

ORACLE的解析器按照從右到左的順序處理FROM子句中的表名,因此FROM子句中寫在最后的表(基礎(chǔ)表 driving table)將被最先處理. 在FROM子句中包含多個(gè)表的情況下,你必須選擇記錄條數(shù)最少的表作為基礎(chǔ)表.當(dāng)ORACLE處理多個(gè)表時(shí), 會(huì)運(yùn)用排序及合并的方式連接它們.首先,掃描第一個(gè)表(FROM子句中最后的那個(gè)表)并對(duì)記錄進(jìn)行派序,然后掃描第二個(gè)表(FROM子句中最后第二個(gè)表),最后將所有從第二個(gè)表中檢索出的記錄與第一個(gè)表中合適記錄進(jìn)行合并.

?

例如:

???? 表 TAB1 16,384 條記錄

???? 表 TAB2 1????? 條記錄

?

???? 選擇TAB2作為基礎(chǔ)表 (最好的方法)

???? select count(*) from tab1,tab2?? 執(zhí)行時(shí)間0.96秒

????

??? 選擇TAB2作為基礎(chǔ)表 (不佳的方法)

???? select count(*) from tab2,tab1?? 執(zhí)行時(shí)間26.09秒

?

如果有3個(gè)以上的表連接查詢, 那就需要選擇交叉表(intersection table)作為基礎(chǔ)表, 交叉表是指那個(gè)被其他表所引用的表.

?

例如:

?

?? 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子句,根據(jù)這個(gè)原理,表之間的連接必須寫在其他WHERE條件之前, 那些可以過(guò)濾掉最大數(shù)量記錄的條件必須寫在WHERE子句的末尾.

?

例如:

?

(低效,執(zhí)行時(shí)間156.3秒)

SELECT …

FROM EMP E

WHERE? SAL > 50000

AND??? JOB = ‘MANAGER’

AND??? 25 < (SELECT COUNT(*) FROM EMP

???????????? WHERE MGR=E.EMPNO);

?

(高效,執(zhí)行時(shí)間10.6秒)

SELECT …

FROM EMP E

WHERE 25 < (SELECT COUNT(*) FROM EMP

???????????? WHERE MGR=E.EMPNO)

AND??? SAL > 50000

AND??? JOB = ‘MANAGER’;

?

?

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

當(dāng)你想在SELECT子句中列出所有的COLUMN時(shí),使用動(dòng)態(tài)SQL列引用‘*’ 是一個(gè)方便的方法.不幸的是,這是一個(gè)非常低效的方法. 實(shí)際上,ORACLE在解析的過(guò)程中, 會(huì)將’*’ 依次轉(zhuǎn)換成所有的列名, 這個(gè)工作是通過(guò)查詢數(shù)據(jù)字典完成的, 這意味著將耗費(fèi)更多的時(shí)間.

???

?

7.???? 減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù)

當(dāng)執(zhí)行每條SQL語(yǔ)句時(shí), ORACLE在內(nèi)部執(zhí)行了許多工作: 解析SQL語(yǔ)句, 估算索引的利用率, 綁定變量 , 讀數(shù)據(jù)塊等等. 由此可見, 減少訪問(wèn)數(shù)據(jù)庫(kù)的次數(shù) , 就能實(shí)際上減少ORACLE的工作量.

?

例如,

??? 以下有三種方法可以檢索出雇員號(hào)等于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中重新設(shè)置ARRAYSIZE參數(shù), 可以增加每次數(shù)據(jù)庫(kù)訪問(wèn)的檢索數(shù)據(jù)量,建議值為200

?

(待續(xù))

?? ORACLE的優(yōu)化器共有3種:

?? a. ?RULE (基于規(guī)則)?? b. COST (基于成本)? c. CHOOSE (選擇性)

?? 設(shè)置缺省的優(yōu)化器,可以通過(guò)對(duì)init.ora文件中OPTIMIZER_MODE參數(shù)的各種聲明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你當(dāng)然也在SQL句級(jí)或是會(huì)話(session)級(jí)對(duì)其進(jìn)行覆蓋.

?? 為了使用基于成本的優(yōu)化器(CBO, Cost-Based Optimizer) , 你必須經(jīng)常運(yùn)行analyze 命令,以增加數(shù)據(jù)庫(kù)中的對(duì)象統(tǒng)計(jì)信息(object statistics)的準(zhǔn)確性.

??

?? 在缺省情況下,ORACLE采用CHOOSE優(yōu)化器, 為了避免那些不必要的全表掃描(full table scan) , 你必須盡量避免使用CHOOSE優(yōu)化器,而直接采用基于規(guī)則或者基于成本的優(yōu)化器.

??

????

ORACLE 采用兩種訪問(wèn)表中記錄的方式:

a.?????? 全表掃描

??????????? 全表掃描就是順序地訪問(wèn)表中每條記錄. ORACLE采用一次讀入多個(gè)數(shù)據(jù)塊(database block)的方式優(yōu)化全表掃描.

???

b.?????? 通過(guò)ROWID訪問(wèn)表

?????? 你可以采用基于ROWID的訪問(wèn)方式情況,提高訪問(wèn)表的效率, , ROWID包含了表中記錄的物理位置信息..ORACLE采用索引(INDEX)實(shí)現(xiàn)了數(shù)據(jù)和存放數(shù)據(jù)的物理位置(ROWID)之間的聯(lián)系. 通常索引提供了快速訪問(wèn)ROWID的方法,因此那些基于索引列的查詢就可以得到性能上的提高.

???

為了不重復(fù)解析相同的SQL語(yǔ)句,在第一次解析之后, ORACLE將SQL語(yǔ)句存放在內(nèi)存中.這塊位于系統(tǒng)全局區(qū)域SGA(system global area)的共享池(shared buffer pool)中的內(nèi)存可以被所有的數(shù)據(jù)庫(kù)用戶共享. 因此,當(dāng)你執(zhí)行一個(gè)SQL語(yǔ)句(有時(shí)被稱為一個(gè)游標(biāo))時(shí),如果它

和之前的執(zhí)行過(guò)的語(yǔ)句完全相同, ORACLE就能很快獲得已經(jīng)被解析的語(yǔ)句以及最好的

???? 可惜的是ORACLE只對(duì)簡(jiǎn)單的表提供高速緩沖(cache buffering) ,這個(gè)功能并不適用于多表連接查詢.

數(shù)據(jù)庫(kù)管理員必須在init.ora中為這個(gè)區(qū)域設(shè)置合適的參數(shù),當(dāng)這個(gè)內(nèi)存區(qū)域越大,就可以保留更多的語(yǔ)句,當(dāng)然被共享的可能性也就越大了.

?這里需要注明的是,ORACLE對(duì)兩者采取的是一種嚴(yán)格匹配,要達(dá)成共享,SQL語(yǔ)句必須

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

???? 共享的語(yǔ)句必須滿足三個(gè)條件:

A.????? 字符級(jí)的比較:

當(dāng)前被執(zhí)行的語(yǔ)句和共享池中的語(yǔ)句必須完全相同.

????? 例如:

????????? SELECT * FROM EMP;

????? 和下列每一個(gè)都不同

????????? SELECT * from EMP;

????????? Select * From Emp;

????????? SELECT????? *???? FROM EMP;

B.????? 兩個(gè)語(yǔ)句所指的對(duì)象必須完全相同:

例如:

?? 用戶???? 對(duì)象名?????????? 如何訪問(wèn)

Jack?????? sal_limit????????? private synonym

???????????? Work_city????? public synonym

???????????? Plant_detail???? public synonym

Jill???????? sal_limit????????? private synonym

???????????? Work_city????? public synonym

??? 考慮一下下列SQL語(yǔ)句能否在這兩個(gè)用戶之間共享.

? ?

?

C.????? 兩個(gè)SQL語(yǔ)句中必須使用相同的名字的綁定變量(bind variables)

例如：

第一組的兩個(gè)SQL語(yǔ)句是相同的(可以共享),而第二組中的兩個(gè)語(yǔ)句是不同的(即使在運(yùn)行時(shí),賦于不同的綁定變量相同的值)

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;

?? (待續(xù))

功能: 用于備份當(dāng)前用戶所擁有的所有PL/SQL objects (包括

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

?

原理: 對(duì)USER_SOURCE數(shù)據(jù)字典的調(diào)用,得到所有的

PL/SQL 代碼.

?

使用方法舉例:

1.???? 在C盤建立目錄C:EXPORT

2.???? 將EXPORT_SOURCE.SQL和EXTRACT_SOURCE.SQL拷貝到C盤根目錄.

3.???? 登陸SQLPLUS , CONNECT SCOTT/TIGER

4.???? 運(yùn)行@C:EXPORT_SOURCE.SQL

5.???? 執(zhí)行結(jié)束,所有的SCOTT擁有的PL/SQL object的代碼文件建立在C:EXPORT目錄里．

后綴名.PKS 表示PACKAGE

后綴名.PKB 表示PACKAGE BODY

后綴名.SQL 表示其他OBJECTS

?

?

備注：

如果想得到數(shù)據(jù)庫(kù)中各個(gè)SCHEMA的PL/SQL objects ,只需把工具代碼中的USER_SOURCE改成DBA_SOURCE,由SYSTEM

運(yùn)行即可.

?

?

?

?

工具代碼：

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做一些項(xiàng)目的過(guò)程中,有時(shí)會(huì)遇到一些頭疼的問(wèn)題.,比如想在執(zhí)行當(dāng)前一個(gè)由多個(gè)DML組成的transaction(事務(wù))時(shí),為每一步DML記錄一些信息到跟蹤表中,由于事務(wù)的原子性,這些跟蹤信息的提交將決定于主事務(wù)的commit或rollback. 這樣一來(lái)寫程序的難度就增大了, 程序員不得不把這些跟蹤信息記錄到類似數(shù)組的結(jié)構(gòu)中,然后在主事務(wù)結(jié)束后把它們存入跟蹤表.哎,真是麻煩!

有沒(méi)有一個(gè)簡(jiǎn)單的方法解決類似問(wèn)題呢？

ORACLE8i的AUTONOMOUS TRANSACTION(自治事務(wù)，以下AT)是一個(gè)很好的回答。

AT 是由主事務(wù)(以下MT)調(diào)用但是獨(dú)立于它的事務(wù)。在AT被調(diào)用執(zhí)行時(shí)，MT被掛起，在AT內(nèi)部，一系列的DML可以被執(zhí)行并且commit或rollback.

注意由于AT的獨(dú)立性，它的commit和rollback并不影響MT的執(zhí)行效果。在AT執(zhí)行結(jié)束后，主事務(wù)獲得控制權(quán)，又可以繼續(xù)執(zhí)行了。

見圖1：

?

圖1：

?

如何實(shí)現(xiàn)AT的定義呢？我們來(lái)看一下它的語(yǔ)法。其實(shí)非常簡(jiǎn)單。

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

1．? 頂級(jí)的匿名PL/SQL塊

2．? Functions 或 Procedure（獨(dú)立聲明或聲明在package中都可）

3．? SQL Object Type的方法

4．? 觸發(fā)器。

?

????

?

?

比如：

?

在一個(gè)獨(dú)立的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;

?

下面我們來(lái)看一個(gè)例子，（win2000 advanced server + oracle8.1.6 , connect as scott）

建立一個(gè)表:

create table msg (msg varchar2(120));

首先,用普通的事務(wù)寫個(gè)匿名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;

?

運(yùn)行結(jié)果(注意打開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??? -> 主匿名塊最后’看到’了所有的記錄.

?

?

從這個(gè)例子中,我們看到COMMIT和ROLLBACK的位置無(wú)論是在主匿名塊中或者在子程序中,都會(huì)影響到整個(gè)當(dāng)前事務(wù).

?

?

?

?

?

現(xiàn)在用AT改寫一下匿名塊中的procedure local:

...

?? procedure local is

???? ?pragma AUTONOMOUS_TRANSACTION;

?? begin

...

?

重新運(yùn)行(注意打開serveroutput)

local: # of rows is 0?? -> 子程序local中無(wú)法可以’看到’主匿名塊中的uncommitted記錄 (因?yàn)樗仟?dú)立的)

main: # of rows is 2??? -> 主匿名塊可以’看到’2條記錄,但只有一條是被commited.

local: # of rows is 1?? -> 子程序local中可以’看到’它前一次commit的記錄,但是主匿名塊中的記錄已經(jīng)被提前rollback了

local: # of rows is 3?? -> 子程序local 中可以’看到’3條記錄包括主匿名塊commit的記錄

main: # of rows is 4??? ->主匿名塊最后’看到’了所有的記錄.

?

很明顯,AT是獨(dú)立的,在它執(zhí)行時(shí),MT被暫停了. AT的COMMIT,ROLLBACK并不影響MT的執(zhí)行.

?

運(yùn)用AT時(shí),有一些注意事項(xiàng),簡(jiǎn)單列舉如下:

1.???? 在匿名PL/SQL塊中,只有頂級(jí)的匿名PL/SQL塊可以被設(shè)為AT

2.???? 如果AT試圖訪問(wèn)被MT控制的資源,可能有deadlock發(fā)生.

3.???? Package 不能被聲明為AT,只有package所擁有的function和procedure 才能聲明為AT

4.???? AT程序必須以commit 或rollback結(jié)尾,否則會(huì)產(chǎn)生Oracle錯(cuò)誤ORA-06519: active autonomous transaction detected and rolled back

?

在程序開發(fā)時(shí),如果充分運(yùn)用AUTONOMOUS TRANSACTION的特性,一定能取得事倍功半的效果.

?

?

參考資料:

metalink.oracle.com

oracle8i manual

?

1、最好做一個(gè)物理的庫(kù)的全備??????????????

2、使用sqlplus 啟動(dòng)庫(kù)至mount???????????????
?sqlplus /nolog??????????????????????
?sql>connect internal??????????????????????
?sql>startup mount??????????????????????
3、確保所有的數(shù)據(jù)文件都處于"END BACKUP"狀態(tài)??????????????????????
?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、試著打開數(shù)據(jù)庫(kù)??????????????????????
?sql>alter database open;??????????????????????
?如數(shù)據(jù)庫(kù)成功打開，余下的都不需要做了，到此為止??????????????????????
5、如果你在打開時(shí)被要求進(jìn)行恢復(fù)，使用"UNTIL CANCEL"這種進(jìn)行恢復(fù),然后再發(fā)出ALTER DATABASE OPEN RESETLOGS這個(gè)命令??????????????????????
? sql>recover database until cancel;??????????????????????
? sql>alter database open resetlogs;??????????????????????
6、如果數(shù)據(jù)庫(kù)仍不能打開，把庫(kù)down掉??????????????????????
? sql>shutdown immediate??????????????????????
7、在init<sid>.ora中加入如下參數(shù)??????????????????????
?? _allow_resetlogs_corruption=TRUE??????????????????????
8、執(zhí)行如下語(yǔ)句??????????????????????
?sql>connect internal??????????????????????
?sql>startup mount?????? ??????????????????????
?sql>@alter_df.sql??????????????????????
?sql>alter database open??????????????????????
9、如在alter database open時(shí)仍舊報(bào)錯(cuò)，使用until cancel恢復(fù)??????????????????????
?sql>recover database until cancel; ??????????????????????
?sql>alter database open resetlogs;??????????????????????
10、經(jīng)過(guò)"9",數(shù)據(jù)庫(kù)一定被打開了，數(shù)據(jù)庫(kù)被打開后，馬上執(zhí)行一個(gè)full export??????????????????????
11、down掉庫(kù),去掉_all_resetlogs_corrupt參數(shù) ??????????????????????
12、重建庫(kù)??????????????????????
13、import并完成恢復(fù)??????????????????????
14、建議執(zhí)行一下ANALYZE TABLE ...VALIDATE STRUCTURE CASCADE;?