oracle體系結(jié)構(gòu),事務(wù)機(jī)制,sql優(yōu)化培訓(xùn)講義

1、ORACLE培訓(xùn)講義提要：首先介紹oracle的組成結(jié)構(gòu)，然后講解oracle事務(wù)機(jī)制原理，最后介紹針對sql優(yōu)化的基本理論與注意事項(xiàng)。一ORACLE體系結(jié)構(gòu)1.1物理數(shù)據(jù)庫結(jié)構(gòu)：包括好幾種文件結(jié)構(gòu)，這里只列出經(jīng)常涉及到的幾種。1）數(shù)據(jù)文件 data file 所有數(shù)據(jù)均存儲在數(shù)據(jù)文件中；一個數(shù)據(jù)文件只與一個數(shù)據(jù)庫有關(guān)，具有自動擴(kuò)展特性；一個或多個數(shù)據(jù)文件組成一個邏輯單元表空間；數(shù)據(jù)文件的數(shù)據(jù)在正常數(shù)據(jù)庫操作中被讀取并被緩存到oracle內(nèi)存緩沖中。更改或新的數(shù)據(jù)并不需要馬上寫入到數(shù)據(jù)文件中。為了減少磁盤訪問提高性能，數(shù)據(jù)在內(nèi)存緩沖，由oracle使用DBWR進(jìn)程將數(shù)據(jù)一次性寫入到數(shù)據(jù)文件中。

2、相關(guān)數(shù)據(jù)字典：v$tablespace,v$datafile2）控制文件 control file 控制文件是數(shù)據(jù)庫的心臟，它包含以下信息：屬于數(shù)據(jù)庫的數(shù)據(jù)文件和重做日志文件信息、數(shù)據(jù)庫中的數(shù)據(jù)應(yīng)該以何種字符集存儲的信息、數(shù)據(jù)庫中每個數(shù)據(jù)文件的狀態(tài)和版本信息、以及其他的重要信息。包含在控制文件中的大部分參數(shù)是在數(shù)據(jù)庫創(chuàng)建過程中設(shè)定的，不是經(jīng)常改變的。控制文件采用二進(jìn)制格式存儲特點(diǎn)：一個數(shù)據(jù)庫最少需要一個控制文件，但一般情況下都會有復(fù)用/副本。相關(guān)數(shù)據(jù)字典：v$controlfile3）重做日志文件 redo log file 重作日志文件是存放聯(lián)機(jī)重做日志(Online Redo Log)的文

3、件。重做日志 (Redo Log)也稱作事務(wù)日志( Transaction Log)，保存針對數(shù)據(jù)庫進(jìn)行的修改操作或事務(wù)。因?yàn)樗械奶幚矶加涗浽诼?lián)機(jī)重做日志中，因此數(shù)據(jù)庫系統(tǒng)可以使用這些事務(wù)記錄進(jìn)行恢復(fù)操作。重做日志以循環(huán)方式工作，對每一個 ORACLE數(shù)據(jù)庫都要求至少具有兩個聯(lián)機(jī)重做日志。如果數(shù)據(jù)庫運(yùn)行在ARCHIVELOG模式下，所有的事務(wù)重做日志都將保存。這意味著對數(shù)據(jù)庫進(jìn)行的所有事務(wù)都留有一個備份，盡管重做日志以循環(huán)方式工作，但在一個重做日志被覆蓋前均將為其建立一個拷貝。在這種方式下，如果在拷貝完成之前，數(shù)據(jù)庫要求交換重做日志(發(fā)生Log Switch)，則在重做日志拷貝工作完成之前，

4、ORACLE將停止一切新的操作，在舊的事務(wù)記錄完成之前ORACLE不對其進(jìn)行覆蓋。有了所有事務(wù)的拷貝，數(shù)據(jù)庫就可以從所有類型的失敗中恢復(fù)，包括用戶錯誤或磁盤崩潰。這是一種最安全的數(shù)據(jù)庫工作方式。相關(guān)數(shù)據(jù)字典：V$log,v$logfileTips:如何開啟/關(guān)閉歸檔模式如果開啟歸檔，建議保證參數(shù)log_archive_start=true，即開啟自動歸檔，否則只能手工歸檔，如果是關(guān)閉了歸檔，則設(shè)置該參數(shù)為false1.開啟歸檔 a. 關(guān)閉數(shù)據(jù)庫shutdown immediate b. startup mount c. alter database archivelog d. alter da

5、tabase opne2、禁止歸檔 a. 關(guān)閉數(shù)據(jù)庫shutdown immediate b. startup mount c. alter database noarchivelog d. alter database open注：查詢當(dāng)前數(shù)據(jù)庫是否是位于歸檔模式，以及詳細(xì)的歸檔信息可以通過如下語句查看(需要以dba權(quán)限登陸) SQL> archive log list或者通過下列sql語句進(jìn)行查詢：select log_mode from v$database;4）初始化參數(shù)文件 parameter file Oracle9i之前，參數(shù)文件為文本格式pfile,從Oracle9i開始

6、化引入了spfile。 在 9i以前，Oracle 使用 pfile存儲初始化參數(shù)設(shè)置，這些參數(shù)在實(shí)例啟動時被讀取，任何修改需要重起實(shí)例才能生效；使用 spfile 你可以使用 ALTER SYSTEM或者 ALTER SESSION來動態(tài)修改那些可動態(tài)修改的參數(shù)，所有更改可以立即生效，你可以選擇使更改只應(yīng)用于當(dāng)前實(shí)例還是同時應(yīng)用到 spfile。SPFILE 是一個二進(jìn)制文件。PFILE與SPFILE的相互轉(zhuǎn)換使用pfile創(chuàng)建spfileCREATE SPFILE=SPFILE-NAME FROM PFILE=PFILE-NAME 例： SQL> create spfile from

7、 pfile;使用spfile創(chuàng)建pfileCREATE PFILE=SPFILE-NAME FROM SPFILE 例： SQL> create pfile from spfile;spfile模式下如何修改系統(tǒng)參數(shù)語法：alter system set 參數(shù)名=值 scope=both/memory/pflie其中看出，scope選項(xiàng)有三個含義，分別表示：MEMORY: 只改變當(dāng)前實(shí)例運(yùn)行 SPFILE: 只改變 SPFILE 的設(shè)置 BOTH: 改變實(shí)例及 SPFILE如何判斷當(dāng)前使用的是spfile還是pfile管理模式判斷是否使用了 SPFILE，可以使用以下方法： 1查詢 v

8、$parameter動態(tài)視圖，如果以下查詢返回空值，那么你在使用 pfile. SELECT name,value FROM v$parameter WHERE name='spfile' 2使用 SHOW 命令 SQL> SHOW PARAMETER spfile 3查詢 v$spparameter視圖 SQL> SELECT COUNT(*) FROM v$spparameter WHERE value IS NOT NULL; 5）歸檔日志文件archived log file 數(shù)據(jù)庫位于歸檔模式運(yùn)行時，日志切換時對于已寫滿的重做日志文件產(chǎn)生的拷貝。1.2邏輯

9、數(shù)據(jù)庫結(jié)構(gòu)數(shù)據(jù)塊空間分配的單位是block，extent與segment。oracle以extent為單位給segment分配空間。由于extent是按需分配，因此段的擴(kuò)展在磁盤上可能不連續(xù)。在表空間內(nèi)，segment可以跨多個數(shù)據(jù)文件，但是extent只能在某個數(shù)據(jù)文件內(nèi)。1）Oracle數(shù)據(jù)塊,blockOracle在最小的粒度級別上以數(shù)據(jù)塊的形式存儲數(shù)據(jù)。一個數(shù)據(jù)塊的大小由db_block_size決定。數(shù)據(jù)塊的分類主要包括：表塊、索引快、簇集塊等。不管是table block還是index block，數(shù)據(jù)塊的存儲格式是相似的。Tips: PCTFREE,PCTUSED,Freelis

10、t這是三個同數(shù)據(jù)塊空間使用密切相關(guān)的3個術(shù)語PCTFREE和PCTUSED是應(yīng)用于段的兩個存儲參數(shù)，Oracle使用PCTFREE和PCTUSED參數(shù)的組合確定塊有沒有足夠的空間接受新的記錄。 PCTFREE：該參數(shù)用于指定在向塊中插入新行時應(yīng)該保留的自由空間的百分?jǐn)?shù)，該保留空間用于修改已包含在該塊中的行時使用。比如在建表CREATE TABLE 語句中指定該參數(shù)為：PCTFREE=20，則在向該表插入新的數(shù)據(jù)行時，其每個數(shù)據(jù)塊空間最多只能使用80%，一旦達(dá)到80%，就不能再向該塊插入數(shù)據(jù)行。留下的20%空間留作此后修改該塊中的行時使用。 PCTUSED：該參數(shù)是一個限定值。當(dāng)通過刪除行或更新

11、行（減少了塊的存儲使用量）而使數(shù)據(jù)庫塊的使用百分?jǐn)?shù)低于pctused時，ORACLE又許可向該塊插入新的數(shù)據(jù)行。PCTFREE與PCTUSED兩參數(shù)之和要小于或等于100。 Freelist維護(hù)了當(dāng)前段中可用塊的列表,存放在表或者索引的第一個塊中，這個塊也被稱為段頭（segment header）。 pctfree和pctused 參數(shù)的唯一目的就是為了控制塊如何在freelists中進(jìn)出。從oracle9i開始引入了ASSM(Auto Segment Space Management)自動段空間管理機(jī)制后，PCTUSED與FreeList就不再起作用了。ORACLE宣稱ASSM可以有效降低因

12、為并發(fā)DML等原因引起Freelist爭用而引起的性能問題。2）擴(kuò)展（extents）一個extents是特定數(shù)目的連續(xù)數(shù)據(jù)塊。3）段（Segment）段是分配給特定對象的extents的集合，主要分為：i).數(shù)據(jù)段：每一個普通表均有一個數(shù)據(jù)段，對分區(qū)表來說，每個分區(qū)有一個數(shù)據(jù)段ii).索引段：每一索引均有一索引段。對分區(qū)索引來說每個分區(qū)有一個索引段iii).臨時段：iv).回滾段：回滾段（rollback segment）存儲在數(shù)據(jù)庫事務(wù)中發(fā)生改變的原始數(shù)據(jù)塊。它們用于提供數(shù)據(jù)的已經(jīng)改變但尚未提交的讀一致性視圖。當(dāng)做出數(shù)據(jù)改變時，原始數(shù)據(jù)被拷到回滾段中，而且在內(nèi)存緩沖區(qū)中對數(shù)據(jù)塊做出更改。如

13、果其他用戶會話要求同樣的數(shù)據(jù)，那么存儲在回滾段中的原始數(shù)據(jù)就會被返回。1.3內(nèi)存結(jié)構(gòu)ORACLE存在兩種類型的內(nèi)存結(jié)構(gòu)：一種是系統(tǒng)全局區(qū)(System Global Area)，也稱SGA；另一種是程序全局區(qū)(ProgramGlobalArea)，也稱PGA。1.3.1 系統(tǒng)全局區(qū)SGA系統(tǒng)全局區(qū)( SGA )是ORACLE數(shù)據(jù)庫存放系統(tǒng)信息的一塊內(nèi)存區(qū)域，作用相當(dāng)于所有用戶進(jìn)程的一個共享區(qū)域或通訊器，所有的用戶進(jìn)程和服務(wù)器進(jìn)程都可以訪問這個內(nèi)存結(jié)構(gòu)SGA組成如下：1) 數(shù)據(jù)高速緩沖區(qū)(Data Buffer Cache)在數(shù)據(jù)高速緩沖區(qū)中存放著 ORACLE系統(tǒng)最近使用過的數(shù)據(jù)庫數(shù)據(jù)塊。換句

14、話說， DataBuffer Cache就是用戶的數(shù)據(jù)高速緩沖區(qū)。當(dāng)把信息放入數(shù)據(jù)庫時，它以數(shù)據(jù)塊的方式存儲。Data Buffer Cache是ORACLE放置這些數(shù)據(jù)塊以使用戶進(jìn)程訪問可見到它的內(nèi)存區(qū)域。用戶進(jìn)程查看的數(shù)據(jù)必須首先駐留在 Data Buffer Cache中。Data Buffer Cache的容量受物理容量限制。因此如果ORACLE已將它填滿，它將在此高速緩沖區(qū)中保留最常用的數(shù)據(jù)塊，去除不常用的數(shù)據(jù)塊。注：如果客戶進(jìn)程需要的信息不在此高速緩沖區(qū)中，那么 ORACLE將查找物理磁盤驅(qū)動器，讀取所要的數(shù)據(jù)塊，然后將它放入 Data Buffer Cache中。這樣，所有其他客

15、戶和服務(wù)器進(jìn)程均可以訪問這些從物理磁盤中讀出的數(shù)據(jù)。DataBufferCache中的緩沖有兩個列表管理：寫列表與LRU列表。前者保存已修改但還沒有寫到磁盤上的塊；后者保存空閑緩存、pinned緩存與還沒有移動到寫列表上的緩存。2) 重做日志緩沖區(qū)(Redo Log Buffer )重做日志緩沖區(qū)用于在內(nèi)存中存儲未被刷新寫入聯(lián)機(jī)重做日志文件的重做信息。它是循環(huán)使用的緩沖區(qū)，這意味著從頂端到底端填充信息，然后又返回到緩沖區(qū)的起始點(diǎn)。重做日志緩沖區(qū)內(nèi)容刷新到redo log file的條件：1每3秒2Redo log buffer 1/3滿或者已有1MB的重做內(nèi)容3任何事務(wù)發(fā)出commit指令3)

16、 共享S Q L池(Shared SQL Pool)共享SQL池(SharedSQLPool)相當(dāng)于程序高速緩沖區(qū)，所有的用戶程序都存放在共享SQL池中。這個高速緩沖區(qū)中存放所有通過SQL語法分析、準(zhǔn)備執(zhí)行的SQL語句。一下幾種情況會將一條sql從共享池中清理出去：1.對象的統(tǒng)計(jì)信息發(fā)生變化2.語句參考的模式對象被修改（發(fā)生了DDL）3.手工清除 alter system flush shared_pool1.3.2 程序全局區(qū)PGAPGA(Program Global Area, PGA)是單個ORACLE進(jìn)程使用的內(nèi)存區(qū)域。程序全局區(qū)不能共享，它含有單個進(jìn)程工作時需要的數(shù)據(jù)和控制信息,如s

17、ql的綁定變量，排序操作與hash連接使用的內(nèi)存等。 1.4系統(tǒng)后臺進(jìn)程后臺進(jìn)程有很多，這里只列出必須了解的幾個。1） 系統(tǒng)監(jiān)控和進(jìn)程監(jiān)控系統(tǒng)監(jiān)控和進(jìn)程監(jiān)控都是自動解決數(shù)據(jù)庫系統(tǒng)問題的后臺進(jìn)程。進(jìn)程監(jiān)控（ PMON）自動清除中斷或失敗的進(jìn)程，包括清除非正常中斷的進(jìn)程留下的孤兒會話、回滾未提交事務(wù)、釋放被斷開連接的進(jìn)程占有的鎖、釋放被失敗進(jìn)程占有的系統(tǒng)全局區(qū)（ SGA）資源，它同時監(jiān)控服務(wù)器和調(diào)度進(jìn)程，如果它們失敗則自動重啟它們。系統(tǒng)監(jiān)控（SMON）主要同實(shí)例恢復(fù)有關(guān)。SMON也是管理某些數(shù)據(jù)庫段的進(jìn)程，收回不再使用的臨時段空間，并自動合并在數(shù)據(jù)文件中相鄰的自由空間塊。SMON和PMON是兩個必

18、需的后臺進(jìn)程。如果它們之中的任意一個在數(shù)據(jù)庫啟動時失敗，數(shù)據(jù)庫將不能啟動。2） 數(shù)據(jù)庫寫數(shù)據(jù)庫寫進(jìn)程（DBWR）負(fù)責(zé)將緩沖區(qū)中臟的數(shù)據(jù)塊寫入到數(shù)據(jù)文件中。 DBWR不是在每一數(shù)據(jù)塊被修改后立即寫入數(shù)據(jù)文件，而是一直等待，直到滿足一定標(biāo)準(zhǔn)后，才成批地讀臟列表，并將在臟列表中發(fā)現(xiàn)的所有塊刷新寫入數(shù)據(jù)文件。這提供了高級別的性能，并最小化數(shù)據(jù)庫輸入/輸出約束的范圍。當(dāng)下列情況發(fā)生時，數(shù)據(jù)庫刷新臟的塊：1）發(fā)生一個檢查點(diǎn)。2）臟列表的長度達(dá)到DB_BLOCK_WRITE_BATCH參數(shù)值的一半。3）使用的緩沖區(qū)數(shù)量達(dá)到DB_BLOCK_MAX_SCAN參數(shù)值。4）DBWR后臺進(jìn)程發(fā)生超時（大約每3秒）。

19、3） 日志寫日志寫（LGWR）是第四個也是最后一個必須的后臺進(jìn)程。LGWR是將在系統(tǒng)全局區(qū)中重做日志緩沖區(qū)的重做日志條目寫入到聯(lián)機(jī)重做日志文件的進(jìn)程。前面講過，LGWR執(zhí)行寫入操作的條件是：發(fā)生提交commit、到達(dá)LGWR非活動時限(3秒)、重做日志緩沖區(qū)滿度達(dá)到三分之一值得注意的重要一點(diǎn)是：直到ORACLE在LGWR完成將重做信息從重做緩沖區(qū)刷新到聯(lián)機(jī)重做日志文件之后，ORACLE才認(rèn)為一個事務(wù)已完成。在LGWR成功地將重做日志項(xiàng)寫入聯(lián)機(jī)重做文件時（并不是改變數(shù)據(jù)文件中的數(shù)據(jù)時），將認(rèn)為一個事務(wù)已經(jīng)提交。LGWR進(jìn)程處理的次要任務(wù)是，執(zhí)行實(shí)施數(shù)據(jù)庫檢查點(diǎn)所需要的操作。除非檢查點(diǎn)進(jìn)程被激活，

20、否則LGWR進(jìn)程完成這一任務(wù)。Tips:檢查點(diǎn)進(jìn)程的介紹1什么是checkpoint?檢查點(diǎn)是一個數(shù)據(jù)庫內(nèi)部事件。該事件被觸發(fā)以后，數(shù)據(jù)庫寫進(jìn)程DBWR會將數(shù)據(jù)庫緩沖區(qū)Database Buffer Cache中所有臟數(shù)據(jù)塊刷新輸出到數(shù)據(jù)文件中。2Checkpoint的有什么作用？1）.保證數(shù)據(jù)庫的一致性。 意思是，將臟數(shù)據(jù)庫刷新到數(shù)據(jù)文件后，保證內(nèi)存與硬盤上的數(shù)據(jù)一致。2）.縮短實(shí)例的恢復(fù)時間。 實(shí)例恢復(fù)的過程中，需要把實(shí)例異常關(guān)閉時沒有寫出到硬盤上的臟數(shù)據(jù)通過日志進(jìn)行恢復(fù)。如果臟塊很多則實(shí)例恢復(fù)的時間也相應(yīng)變長。檢查點(diǎn)的發(fā)生有助于減少臟塊的數(shù)量，從而達(dá)到提高實(shí)例恢復(fù)速度的目的。4） 歸檔進(jìn)

21、程歸檔進(jìn)程（ARCH）負(fù)責(zé)將全部聯(lián)機(jī)重做日志復(fù)制到歸檔重做日志文件。這僅在數(shù)據(jù)庫運(yùn)行在歸檔模式（ARCHIVELOG）下才發(fā)生。當(dāng)ARCH正在復(fù)制歸檔重做日志時，沒有其他進(jìn)程能夠?qū)懭脒@個重做日志。這一點(diǎn)非常重要，因?yàn)橹刈鋈罩臼前错樞蜓h(huán)使用的。如果數(shù)據(jù)庫需要轉(zhuǎn)換重做日志，但是ARCH還正在按其順序復(fù)制下一個日志，所有數(shù)據(jù)庫的活動將終止，直到ARCH完成。還要注意如果歸檔由于某些原因不能完成復(fù)制日志，它將等待直到引起不能寫入的錯誤得到解決為止。非常值得注意的是在init.ora文件中ARCHIVE_LOG_START參數(shù)必須設(shè)置為TRUE，當(dāng)數(shù)據(jù)庫啟動時，才會自動開始?xì)w檔。設(shè)置數(shù)據(jù)庫處于歸檔模式

22、并不足以導(dǎo)致ARCH自動啟動。如果設(shè)置了歸檔模式但不自動啟動ARCH，當(dāng)所有聯(lián)機(jī)重做日志寫滿時，數(shù)據(jù)庫將會掛起，等待你手工歸檔聯(lián)機(jī)日志。5） 檢查點(diǎn)進(jìn)程檢查點(diǎn)進(jìn)程（CKPT）是可選的后臺進(jìn)程，執(zhí)行LGWR進(jìn)程通常會執(zhí)行的檢查點(diǎn)任務(wù)即用當(dāng)前版本信息更新數(shù)據(jù)文件和控制文件頭。當(dāng)有經(jīng)常性的檢查點(diǎn)發(fā)生、頻繁的日志切換或在數(shù)據(jù)庫中有多個數(shù)據(jù)文件時，啟用這個進(jìn)程來減少LGWR的工作量。小結(jié)：由于后臺進(jìn)程與相關(guān)的內(nèi)存結(jié)構(gòu)關(guān)系非常緊密，因此以以下圖示總體說明一下：后臺進(jìn)程與SGA，data files，redo log files等結(jié)構(gòu)的關(guān)系圖二并發(fā)與多版本,事務(wù)機(jī)制2.1介紹多版本一致讀(MVRC)首先看一

23、個例子.(以下代碼全部在sqlplus中執(zhí)行)Session1>Create table tAs Select * from all_users;Session1>Variable x refcursorSession1>BeginOpen :x for select * from t;End;/Session2>Delete from t;Session2>Commit;Session1>Pint x;對于oracle而言，一個查詢的結(jié)果集在查詢開始時就已經(jīng)確定了。Oracle內(nèi)部通過系統(tǒng)改變號SCN，對數(shù)據(jù)塊的數(shù)據(jù)改變的時候會把該改變鎖對應(yīng)的SCN記錄在

24、塊中。假設(shè)查詢開始的時候的SCN為T，則在查詢所掃描的數(shù)據(jù)塊中，如果數(shù)據(jù)的Commit SCN小于等于T，則查詢接受該數(shù)據(jù)。如果COMMIT SCN大于T或者還沒有產(chǎn)生COMMIT SCN，則查詢會嘗試去回滾段中查找數(shù)據(jù)，這樣就保證了數(shù)據(jù)讀取在同一時間點(diǎn)的一致性，所以叫一致讀(read-consistent)。而實(shí)現(xiàn)一致讀的途徑，是ORACLE依賴回滾段對同一個存在修改的塊同時“物化”了多個版本的數(shù)據(jù)，這就是多版本(Multi-Version)的含義。2.2介紹Redo與undoDML語句會產(chǎn)生重做(redo)信息與撤銷(undo)信息。以insert為例，產(chǎn)生的undo包含足夠的信息使新插入

25、信息“消失”，產(chǎn)生的重做信息包含足夠的信息使插入“再次發(fā)生”。Undo信息存儲在回滾段中，并且也受到redo的保護(hù)（就是說往回滾段中寫入的信息，與象表、索引段中寫入的信息一樣，都會記錄重做日志，這個概念很重要，在系統(tǒng)崩潰時可以看到這樣做的作用）。回滾只是將數(shù)據(jù)庫還原的一個邏輯操作。2.3 commit與rollback的內(nèi)部操作在事務(wù)需要Commit之前，困難的工作基本上都已經(jīng)做了，這些工作包括：1).已經(jīng)在SGA中產(chǎn)生了undo塊2).已經(jīng)在SGA中產(chǎn)生了已修改數(shù)據(jù)塊3).已經(jīng)在SGA中產(chǎn)生了對應(yīng)前兩項(xiàng)的緩存redo4).如果事務(wù)運(yùn)行時間較長，或者前三項(xiàng)的數(shù)量較多，這些數(shù)據(jù)可能已經(jīng)部分或全部

26、刷新輸出到了磁盤5).得到了事務(wù)修改所需要的全部鎖。Commit真正發(fā)生時，只需要進(jìn)行如下工作：1).為本次事務(wù)產(chǎn)生一個SCN.2).LGWR將剩余的緩存重做日志條目寫至磁盤，并將SCN記錄到在線重做日志文件。這一步是真正的commit，如果這一步進(jìn)行完畢就認(rèn)為已經(jīng)提交。事務(wù)條目將從v$transaction中刪除.3).本次事務(wù)持有的鎖將(記載在V$lock中)被釋放。等待這些鎖的其他事務(wù)將被喚醒。4).如果事務(wù)修改的某些塊可能還在緩沖區(qū)緩存中，則執(zhí)行快速塊清除(block cleanout)。塊清除的含義是清除存儲在數(shù)據(jù)塊首部的事務(wù)信息。這其中，耗時最長的可能是LGWR執(zhí)行的步驟，因?yàn)樗⑿?/p>

27、重做日志是磁盤IO。因此commit時必須等待尚未刷新到磁盤上的重做日志緩沖全部刷新完畢，才認(rèn)為commit已經(jīng)完成，也就是說，對LGWR的調(diào)用是同步(Synchronous)的。如果不是commit，而是發(fā)生了roolback，則步驟如下：1).撤銷所有已做的修改。過程如下：從uodo段讀回?cái)?shù)據(jù)，然后逆向執(zhí)行前面所做的操作，并將undo條目記為已用。2).會話持有的鎖全部釋放，所有等待這些鎖的隊(duì)列將被喚醒。Tips：本章參考材料1) 測量redo量的腳本 使用兩個腳本mystat.sql與mystat2.sql-mystat.sqlset verify offcolumn value new

28、_val Vdefine S="&1"set autotrace offselect ,b.valuefrom v$statname a,v$mystat bwhere a.statistic#=b.statistic# and lower() like '%'|lower('&S')|'%'/-mystat2.sqlset verify offcol name for a20select ,b.value value2 ,to_char(b.value -&V,&#

29、39;999,999,999,999') difffrom v$statname a,v$mystat bwhere a.statistic#=b.statistic# and lower() like '%'|lower('&S')|'%'/2) 如何減少redo量使用nologging提示以減少以下操作產(chǎn)生的redo量： 索引創(chuàng)建與重建。 使用create table as select 語句創(chuàng)建表。Nologging僅僅在數(shù)據(jù)庫運(yùn)行在archivelog 模式時才有比較明顯的效果。如果數(shù)據(jù)庫運(yùn)行在noarchiv

30、elog模式下，上述操作使用nologging與否對于redo量的產(chǎn)生并沒有多大影響。注：可以使用alter table t nologging;與alter index inx_name nologging;子句來指定表與索引的nologging屬性，該屬性可以通過dba_tables視圖查詢得到。在archivelog模式下，對表的nologging不起作用，索引的nologging屬性起作用。3) ORA-01555 Snapshot too old介紹當(dāng)Oracle處理一個查詢時，它利用一致讀機(jī)制來進(jìn)行查詢，當(dāng)Oracle數(shù)據(jù)讀取一個塊來回應(yīng)你的查詢時，它可能還需要讀取一些UNDO信息

31、。在你的查詢的正常處理中，Oracle讀取一些UNDO信息。當(dāng)回應(yīng)查詢所需要的某些UNDO信息因?yàn)橐呀?jīng)被重寫而不再存在時，就會產(chǎn)生ORA-01555錯誤。UNDO信息只有在生成它的事務(wù)仍然活動時才被保存。一旦該事務(wù)被標(biāo)記為已提交，Oracle便可能重寫該UNDO，重新利用該空間。如果你正確設(shè)置了你的UNDO表空間（回滾段）的大小，Oracle將不會立即重寫這一信息，而是暫時保持一段時間。這使需要使用該UNDO信息的查詢能夠訪問它。ORA-01555錯誤的出現(xiàn)是因?yàn)樗O(shè)定的UNDO空間的大小不能滿足系統(tǒng)所做工作的需要而造成的。經(jīng)常引起ORA-01555的原因之一是在游標(biāo)FOR LOOP循環(huán)中的錯

32、誤提交，如下面這樣：For x in ( select * from emp )loop . process record . update emp set . where empno = x.empno; commit;End loop;通過這一更新，根據(jù)EMP表生成了UNDO。同時，通過在更新后正確提交，相當(dāng)于告訴Oracle“可以隨時重新使用undo空間了”。問題是你EMP的查詢需要UNDO信息，以獲得EMP表的讀一致視圖。通過在循環(huán)中進(jìn)行提交，釋放了開始代碼的cursor所需要的資源(通過使用commit聲明不再需要保留undo信息)，此類代碼就有可能產(chǎn)生ORA-01555錯誤。三Sq

33、l優(yōu)化基本方法介紹前言：系統(tǒng)優(yōu)化原則，以及與單純sql優(yōu)化的關(guān)系最根本的原則：對sql優(yōu)化在整個系統(tǒng)優(yōu)化中有比較清醒的認(rèn)識。Sql優(yōu)化不是萬能的，務(wù)必避免陷入唯sql優(yōu)化論的誤區(qū)。對一個現(xiàn)有系統(tǒng)進(jìn)行優(yōu)化是一個全局工程，要考慮的因素可能包括很多方面，sql優(yōu)化僅僅是其中一個方面。按照重要性以及固定工作量換取的性能提升比來看，一般的工作步驟建議如下：1優(yōu)化業(yè)務(wù)規(guī)則比如核心平臺中確認(rèn)單據(jù)不自動記帳的業(yè)務(wù)模式。2優(yōu)化數(shù)據(jù)庫設(shè)計(jì)數(shù)據(jù)庫設(shè)計(jì)階段通常要經(jīng)歷規(guī)范化階段，此時需要對數(shù)據(jù)進(jìn)行分析以降低數(shù)據(jù)冗余。除了主鍵以外，任何數(shù)據(jù)元素都應(yīng)當(dāng)在數(shù)據(jù)庫中只存儲一次。當(dāng)在數(shù)據(jù)規(guī)范化以后，處于性能考慮，有時又需要打破這

34、種規(guī)范化形式。還有一些其他的例子，比如aged系統(tǒng)中用到的si.emp_join中的dfjgbh與yhzh。3優(yōu)化應(yīng)用程序設(shè)計(jì)這一步中包括程序邏輯結(jié)構(gòu)的調(diào)整，也可能包括具體sql的調(diào)整。具體sql調(diào)整的基礎(chǔ)知識與常用方法是接下來要講解的主要內(nèi)容。4優(yōu)化數(shù)據(jù)庫的邏輯結(jié)構(gòu)如建立必要的輔助索引來提高某些具體程序的性能。例如有個地方的退休審批程序中要求檢測新增加的退休人員所錄入的銀行帳號是否在系統(tǒng)中已經(jīng)存在。這個時候，為了避免對si.emp_join表進(jìn)行全表掃描，在yhzh列上建一個索引是值得的。5優(yōu)化內(nèi)存分配，優(yōu)化I/O和物理結(jié)構(gòu)例如，如果性能問題確實(shí)出現(xiàn)在SGA太小，或者DBWR,LGWR寫出太

35、慢等，就需要用到這一步驟。對這一步感興趣的，請多參考o(jì)racle管理方面的書籍，在此不作為主要內(nèi)容討論。一) SQL優(yōu)化基本理論0.一些基本術(shù)語：1)共享sql語句通過共享sql，可以減少相同類型sql的解析次數(shù)。2)Rowid的概念：索引節(jié)點(diǎn)中存儲了rowid，因此通過索引可以快速檢索到數(shù)據(jù)行。3)Recursive SQL概念為執(zhí)行一個用戶發(fā)出的sql，oracle在后臺需要額外執(zhí)行的sql.常見的情況有：DDL語句（要修改數(shù)據(jù)字典，若數(shù)據(jù)字典沒有在data buffer中緩存，就引發(fā)recursive calls將字典信息讀入磁盤）。4)Row Source(行源)在一個查詢中，由子操作

36、提供的數(shù)據(jù)集，可能是一個表的全部或部分?jǐn)?shù)據(jù)，也可能是表連接后得到的結(jié)果集。5)Predicate(謂詞)一個查詢中的WHERE限制條件6)組合索引(concatenated index)由多個列組成的一個索引。涉及到引導(dǎo)列(leading column)的重要概念7)可選擇性(selectivity)：一個列所有不同值的總數(shù)/行數(shù) 的比率，這個值越接近1說明不同值越多，選擇性就越高1 SQL語句的執(zhí)行過程了解SQL語句的處理過程是基本的要求，每種類型的語句都需要如下階段：1) Create a Cursor 創(chuàng)建游標(biāo)這一步一般是自動的。2) Parse the Statement 分析語句*)

37、 翻譯SQL語句，驗(yàn)證它是合法的語句，即書寫正確*) 實(shí)現(xiàn)數(shù)據(jù)字典的查找，以驗(yàn)證是否符合表和列的定義*) 在所要求的對象上獲取語法分析鎖，使得在語句的語法分析過程中不改變這些對象 的定義*) 驗(yàn)證為存取所涉及的模式對象所需的權(quán)限是否滿足*) 決定此語句最佳的執(zhí)行計(jì)劃*) 將它裝入共享SQL區(qū)*) 對分布的語句來說，把語句的全部或部分路由到包含所涉及數(shù)據(jù)的遠(yuǎn)程節(jié)點(diǎn)以上任何一步出現(xiàn)錯誤，都將導(dǎo)致語句報(bào)錯，中止執(zhí)行。這里涉及到使用共享SQL的問題，即使用綁定變量(bind variable)。3) Describe Results of a Query 描述查詢的結(jié)果集(Select Only)4)

38、 Define Output of a Query 定義查詢的輸出數(shù)據(jù)(Select Only)5) Bind Any Variables 綁定變量6) Parallelize the Statement 并行執(zhí)行語句(并行查詢)7) Run the Statement 運(yùn)行語句8) Fetch Rows of a Query 取查詢出來的行(Select Only)9) Close the Cursor 關(guān)閉游標(biāo)上述步驟中，標(biāo)記有select only的步驟只有查詢語句才會用到（這里的查詢語句不僅僅包含一般意義上的select語句，也包括帶有where條件的update,insert sel

39、ect,create table as select等語句，因?yàn)檫@些語句中都包含對于數(shù)據(jù)的查詢）2 解讀SQL的執(zhí)行計(jì)劃執(zhí)行計(jì)劃的閱讀順序：一般是按照從里到外,從上到下的次序解讀分析的結(jié)果.最內(nèi)部的操作將被最先解讀, 如果兩個操作處于同一層中,帶有最小操作號的將被首先執(zhí)行.有一個例外是nested loop，對nested loop提供數(shù)據(jù)的操作中，操作號最小的將被最先處理.Tips 1 優(yōu)化器介紹Oracle提供了兩種優(yōu)化器：基于規(guī)則的優(yōu)化器Rule-based Optimizer，我們將簡寫為RBO, 基于代價的優(yōu)化器 Cost-based Optimizer，我們將簡寫為CBO基于代價的

40、優(yōu)化器 The cost-based optimizer CBO 相對于RBO更加地靈活并能更好地適應(yīng)數(shù)據(jù)的變化。為了得到一條 語句的最佳執(zhí)行路徑，CBO不是單單使用固化的教條式的規(guī)則，而是會利用數(shù)據(jù)庫的很多資源信息，如表的大小，表中的行數(shù)，鍵值的分布情況等等。一旦一個表通過ANALYZE 命令或是使用 DBMS_STATS 工具收集了統(tǒng)計(jì)信息，這些統(tǒng)計(jì)信息就可以用在CBO對最優(yōu)執(zhí)行路徑的判斷中。如果將對沒有收集統(tǒng)計(jì)信息的表根據(jù)數(shù)據(jù)字典中的數(shù)據(jù)推測其統(tǒng)計(jì)信息。在如下情況下將默認(rèn)使用 在1.在實(shí)例級別或者Session級別設(shè)置了OPTIMIZER_MODE = CHOOSE，而且語句中訪問到的表

41、中至少有一個已收集了統(tǒng)計(jì)信息。 2.在會話級別中調(diào)用了alter session set optimizer_mode=FIRST_ROWS (or ALL_ROWS),且語句中訪問的表中至少有一個已經(jīng)收集了統(tǒng)計(jì)信息。3.SQL語句中使用了FIRST_ROWS 或是ALL_ROWS 優(yōu)化提示。使用CBO時，對于沒有收集統(tǒng)計(jì)信息的表，ORACLE將根據(jù)數(shù)據(jù)字典中的數(shù)據(jù)推測其統(tǒng)計(jì)信息。Tips 2 三種表連接方式介紹1）Sort-Merge Join(SMJ)在merge join操作中，連接的兩個輸入(以下分別稱為row source 1,row source 2)分別處理、分類和連接。如果ro

42、w source已經(jīng)在連接關(guān)聯(lián)列上被排序，則該連接操作就不需要再進(jìn)行sort操作，這樣可以大大提高這種連接操作的連接速度，因?yàn)榕判蚴莻€極其費(fèi)資源的操作，特別是對于較大的表。 預(yù)先排序的row source包括已經(jīng)被索引的列(如a.col3或b.col4上有索引)或row source已經(jīng)在前面的步驟中被排序了。2）Nested Loops(NL)在NESTED LOOPS連接中，Oracle讀取row source1中的每一行，然后在row source 2中檢查是否有匹配的行，所有被匹配的行都被放到結(jié)果集中，然后處理row source1中的下一行。這個過程一直繼續(xù)，直到row source

43、1中的所有行都被處理。這是從連接操作中可以得到第一個匹配行的最快的方法之一，這種類型的連接可以用在需要快速響應(yīng)的語句中，以響應(yīng)速度為主要目標(biāo)。在這里，稱Row source1為驅(qū)動表(Driving Table)或外部表。Row Source2被稱為被探查表(Probe table)或內(nèi)部表。如果driving row source(外部表)比較小，并且在inner row source(內(nèi)部表)上有唯一索引或高選擇性非唯一索引時，使用這種方法可以得到較好的效率。NESTED LOOPS有其它連接方法沒有的的一個優(yōu)點(diǎn)是：可以先返回已經(jīng)連接的行，而不必等待所有的連接操作處理完才返回?cái)?shù)據(jù)，這可以實(shí)

44、現(xiàn)快速的響應(yīng)時間。3）Hash Join理論上來說比NL與SMJ更高效，而且只用在CBO優(yōu)化器中。較小的row source被用來構(gòu)建hash table與bitmap，第2個row source被用來被hansed，并與第一個row source生成的hash table進(jìn)行匹配，以便進(jìn)行進(jìn)一步的連接。Bitmap被用來作為一種比較快的查找方法，來檢查在hash table中是否有匹配的行。特別的，當(dāng)hash table比較大而不能全部容納在內(nèi)存中時，這種查找方法更為有用。這種連接方法也有NL連接中所謂的驅(qū)動表的概念，被構(gòu)建為hash table與bitmap的表為驅(qū)動表，當(dāng)當(dāng)被構(gòu)建的has

45、h table與bitmap能被容納在內(nèi)存中時，這種連接方式的效率極高。對三種連接方式的適用總結(jié)：1。排序 - 合并連接(Sort Merge Join, SMJ)：1) 如果在關(guān)聯(lián)的列上都有索引，效果更好。2) 對于將2個較大的row source做連接，該連接方法比NL連接要好一些。3) 但是如果sort merge返回的row source過大，則因?yàn)檫^多的I/O，效果會下降較多。2。嵌套循環(huán)(Nested Loops, NL)：1) 如果driving row source(外部表)比較小，并且在inner row source(內(nèi)部表)上有唯一索引，或有高選擇性非唯一索引時，使用這種

46、方法可以得到較好的效率。2) NESTED LOOPS有其它連接方法沒有的的一個優(yōu)點(diǎn)是：可以先返回已經(jīng)連接的行，而不必等待所有的連接操作處理完才返回?cái)?shù)據(jù)，這可以實(shí)現(xiàn)快速的響應(yīng)時間。3。哈希連接(Hash Join, HJ)：1) 理論上來說，其效率應(yīng)該好于其它2種連接，但是這種連接只能用在CBO優(yōu)化器中，而且需要設(shè)置合適的hash_area_size參數(shù)，才能取得較好的性能。2) 在2個較大的row source之間連接時會取得相對較好的效率，在一個row source較小時則能取得更好的效率(只得是driving row source小到可以完全放到內(nèi)存中時，此時HJ的連接效率極高)。Tip

47、s 3 解讀SQL的執(zhí)行計(jì)劃時的重點(diǎn)關(guān)注事項(xiàng)：一:How do i decide which query is better depending on the values of the each parameter in statistics? 1:Important parameters: db blocks gets,consistent gets :logical reads or memory usage. physical reads is disk I/O Depending on this we can estimate the memory usage. 2:secondly

48、,recursive calls,redo size,sorts(memory),sorts(disk) 3:thirdly:bytes sent via SQL&Net from client ,bytes received via SQL&Net from client,SQL&Net roundtrips to/from client 二:What these parameters really mean? Recursive calls The number of recursive calls to the database. This type of cal

49、l occurs for a few reasons misses in the dictionary cache, dynamic storage extension, and when PL/SQL statements are executed. Generally, if the number of recursive calls is more than 4 per process, you should check the dictionary hit cache ratio. Recursive Calls These occur because of cache misses

50、and dynamic storage extension. If the dictionary data is found in cache, a recursive call is not made and the data is read from cache directly. In general, if recursive calls are greater than four per process, the data dictionary cache should be optimized and segments should be rebuilt with storage

51、clauses to have a few large extents. Segments include tables, indexes, and rollback segments.Recursive calls should be fewer than user calls (less than one-tenth). Where there is an imbalance, the aim should be to reduce parsing. High levels of recursive SQL may also be attributable to significant u

52、se of PL/SQL. For each SQL statement in a PL/SQL block, on each iteration, there are recursive calls to do the equivalent of bind and define. DB Block gets The number of blocks in the buffer cache that were accessed for INSERT, UPDATE, DELETE and SELECT for UPDATE statements. Consistent gets The num

53、ber of blocks accessed in the buffer cache for queries without the SELECT FOR UPDATE clause. The value for this statistic plus the value of the “db block gets” statistic constitute what is referred to as a logical read. Physical Reads the number of data blocks that were read from disks to satisfy a

54、SELECT, SELECT FOR UPDATE, INSERT, UPDATE or DELETE. Data Cache Hit Ratio Hit Ratio = (Logical Reads Physical Reads) / Logical Reads Redo Size the size in bytes of the amount of redo information that was written to the redo logs. This information can be used to help size the redo logs and the LOG_SM

55、ALL_ENTRY_MAX_SIZE parameter in the init.ora. Sorts(memory) the number of sorts that were performed in memory Sorts(disk) the number of sorts that were unable to be performed in memory and therefore required the creation of a temp segment in the temporary tablespace. This statistic divided by the so

56、rts(memory) should not be above the 5 percent. If it is, you should increase the SORT_AREA_SIZE parameter in the init.ora. Tips 4 四種索引掃描類型介紹根據(jù)索引的類型與where限制條件的不同，有4種類型的索引掃描：索引唯一掃描(index unique scan)索引范圍掃描(index range scan)索引全掃描(index full scan)索引快速掃描(index fast full scan)(1) 索引唯一掃描(index unique scan)

57、通過唯一索引查找一個數(shù)值經(jīng)常返回單個ROWID。如果該唯一索引有多個列組成(即組合索引)，則至少要有組合索引的引導(dǎo)列參與到該查詢中，如創(chuàng)建一個索引：create index idx_test on emp(ename, deptno, loc)。則select ename from emp where ename = JACK and deptno = DEV語句可以使用該索引。如果該語句只返回一行，則存取方法稱為索引唯一掃描。而select ename from emp where deptno = DEV語句則不會使用該索引，因?yàn)閣here子句種沒有引導(dǎo)列。如果存在UNIQUE 或PRIMARY KEY 約束（它保證了語句只存取單行）的話，Oracle經(jīng)常實(shí)現(xiàn)唯一性掃描。(2) 索引范圍掃描(index range scan)使用index rang scan的3種情況：(a) 在唯一索引列上使用了range操作符(> < <> >= <= be