1、 二、二、Oracle Oracle 體系結構體系結構 五、五、Oracle SQLOracle SQL編寫規范編寫規范 四、典型案例分析暨優化要點四、典型案例分析暨優化要點 三、三、Oracle SQLOracle SQL優化基礎優化基礎 一、培訓目的一、培訓目的培訓總目錄培訓總目錄培訓目的培訓目的培訓目標培訓內容課堂交互 本次課程大約需要3個小時，課后，我們會留半個小時與大家進行探討，希望在 座各位在培訓過程中記錄下自己的問題； Oracle 體系結構； Oracle SQL優化及編寫規范； 了解Oracle 體系架構； 掌握Oracle SQL優化基本方法； Oracle SQL優化典型

2、案例分析； 二、二、Oracle Oracle 體系結構體系結構 五、五、Oracle SQL Oracle SQL 編寫規范編寫規范 四、典型案例分析暨優化要點四、典型案例分析暨優化要點 三、三、Oracle SQL Oracle SQL 優化基礎優化基礎 一、培訓目的一、培訓目的培訓總目錄培訓總目錄OracleOracle服務器架構服務器架構OracleOracle內存體系架構內存體系架構OracleOracle進程體系架構進程體系架構 用戶進程用戶進程:在數據庫用戶或批處理進程連接到在數據庫用戶或批處理進程連接到 Oracle DB 時啟動時啟動 服務器進程服務器進程:連接到連接到Ora

3、cle 實例，在用戶建立實例，在用戶建立 會話時啟動會話時啟動 后臺進程后臺進程:在在Oracle 實例啟動時啟動實例啟動時啟動OracleOracle存儲體系架構存儲體系架構OracleOracle高可用性高可用性_ _雙機熱備雙機熱備優點優點 管理簡單管理簡單 相對較為穩定相對較為穩定缺點缺點 切換間存在停頓切換間存在停頓 備機空閑備機空閑 無擴展性無擴展性OracleOracle高可用性高可用性_ _雙機熱備切換雙機熱備切換OracleOracle高可用性高可用性_RAC_RAC優點優點 良好的可伸縮性、可擴展性良好的可伸縮性、可擴展性 高可用性高可用性 新進的新進的Cache Fusi

4、on技術技術 負載均衡負載均衡缺點缺點 管理復雜管理復雜 對網絡要求高對網絡要求高OracleOracle高可用性高可用性_RAC_RAC將應用和用戶自動而透明地重新連接到將應用和用戶自動而透明地重新連接到另一個系統另一個系統登錄的上下文可被保持登錄的上下文可被保持應用查詢不會中斷應用查詢不會中斷當集群內節點當集群內節點 A A 失失敗敗, , 用戶被轉移用戶被轉移OracleOracle RACRAC負載均衡負載均衡 數據庫啟動時，向監聽進程注冊數據庫啟動時，向監聽進程注冊 節點向監聽進程報告節點向監聽進程報告CPU 的使用情況的使用情況 當建立連接時，監聽進程選擇最空閑的節點當建立連接時，

5、監聽進程選擇最空閑的節點 處理請求處理請求 二、二、Oracle Oracle 體系結構體系結構 五、五、Oracle SQL Oracle SQL 編寫規范編寫規范 四、典型案例分析暨優化要點四、典型案例分析暨優化要點 三、三、Oracle SQL Oracle SQL 優化基礎優化基礎 一、培訓目的一、培訓目的培訓總目錄培訓總目錄性能優化概述性能優化概述14 誰來調整系統誰來調整系統 應用設計人員、應用開發人員應用設計人員、應用開發人員 數據庫管理人員數據庫管理人員 什么時候調整什么時候調整 系統設計、系統開發過程系統設計、系統開發過程 系統運行過程系統運行過程 應用系統類型應用系統類型1

6、5 聯機事務處理（聯機事務處理（OLTPOLTP） 高吞吐量高吞吐量 增、刪、改多增、刪、改多 決策支持（決策支持（DSSDSS） 數據量大數據量大 主要用于查詢主要用于查詢 SQLSQL語句處理過程語句處理過程16 查詢語句處理查詢語句處理 DMLDML語句處理語句處理 （insertinsert、deletedelete、updateupdate） DDLDDL語句處理語句處理 （createcreate、dropdrop、alteralter）SQLSQL語句處理各個階段語句處理各個階段17SQLSQL語句處理必經階段語句處理必經階段18 第第1 1步步: Create a Cursor

7、 : Create a Cursor 創建游標創建游標 第第2 2步步: Parse the Statement : Parse the Statement 分析語句分析語句 第第5 5步步: Bind Any Variables : Bind Any Variables 綁定變量綁定變量 第第7 7步步: Run the Statement : Run the Statement 運行語句運行語句 第第9 9步步: Close the Cursor : Close the Cursor 關閉游標關閉游標SQLSQL語句處理可選階段語句處理可選階段19 并行功能并行功能 第第6 6步：步：Pa

8、rallelize the Statement Parallelize the Statement 并行執行語并行執行語句句 查詢語句查詢語句 第第3 3步：步：Describe Results of a Query Describe Results of a Query 描述查描述查詢的結果集詢的結果集 第第4 4步：步：Define Output of a QueryDefine Output of a Query 定義查詢的定義查詢的輸出數據輸出數據 第第8 8步：步：Fetch Rows of a QueryFetch Rows of a Query 取查詢出來的行取查詢出來的行DDL

9、DDL語句處理過程語句處理過程20 不同于不同于DMLDML語句和查詢語句的執行語句和查詢語句的執行 DDLDDL成功后需要對數據字典表進行修改成功后需要對數據字典表進行修改 分析階段還包括分析、查找數據字典分析階段還包括分析、查找數據字典 信息和執行信息和執行OracleOracle優化器優化器21 優化優化: : 選擇最有效的執行計劃來執行選擇最有效的執行計劃來執行 SQL SQL語句的過程語句的過程 優化器優化器: : 選擇執行計劃的數據庫引擎選擇執行計劃的數據庫引擎 基于規則（基于規則（RBORBO） 基于代價（基于代價（CBOCBO） OracleOracle統計信息統計信息22 影

10、響影響CBOCBO執行計劃最關鍵的因素執行計劃最關鍵的因素 分析的方法分析的方法 analyzeanalyze命令命令 dbms_statdbms_stat包包 分析的頻率分析的頻率 Oracle 10gOracle 10g自動分析自動分析共享共享SQLSQL語句語句23 儲存于共享池（儲存于共享池（shared_poolshared_pool） 判斷判斷SQLSQL語句是否與共享池中某一語句是否與共享池中某一SQLSQL相相同的步驟（同的步驟（CURSOR_SHARINGCURSOR_SHARING） 對所發出語句的文本串進行對所發出語句的文本串進行hashedhashed 將所發出語句的文

11、本串進行比較將所發出語句的文本串進行比較 將將SQLSQL中涉及的對象進行比較中涉及的對象進行比較基本概念基本概念24 ROWIDROWID 偽列，唯一。定位數據的最快方法偽列，唯一。定位數據的最快方法 索引創建時會記錄索引創建時會記錄ROWIDROWID值值 Driving TableDriving Table（驅動表）（驅動表）: : 外層表，用于嵌套以及外層表，用于嵌套以及hashhash連接中連接中 可選擇性可選擇性: : “唯一鍵的數量唯一鍵的數量/ /表中的行數表中的行數”的比值的比值Oracle SQL Oracle SQL 執行計劃執行計劃25 SQLSQL語句語句: :SEL

12、ECT SELECT enameename, job, , job, salsal, , dnamedname FROM FROM empemp, , deptdept WHERE WHERE emp.deptnoemp.deptno = = dept.deptnodept.deptno AND NOT EXISTS AND NOT EXISTS ( SELECT ( SELECT * * FROM FROM salgradesalgrade WHERE WHERE emp.salemp.sal BETWEEN BETWEEN losallosal AND AND hisalhisal );

13、);Oracle SQL Oracle SQL 執行計劃圖執行計劃圖26Oracle SQL Oracle SQL 執行計劃圖執行計劃圖27訪問路徑訪問路徑28 全表掃描（全表掃描（FTSFTS） 通過通過rowidrowid的表存取（的表存取（Table Access by rowidTable Access by rowid） 索引掃描（索引掃描（Index ScanIndex Scan） 索引唯一掃描索引唯一掃描(index unique scan)(index unique scan) 索引范圍掃描索引范圍掃描(index range scan)(index range scan) 索

14、引全掃描索引全掃描(index full scan)(index full scan) 索引快速掃描索引快速掃描(index fast full scan)(index fast full scan)表連接表連接29 表連接表連接JOIN:JOIN: 將兩個表結合在一起，一次只能連接將兩個表結合在一起，一次只能連接2 2個表，個表， 表連接也可以被稱為表關聯表連接也可以被稱為表關聯 排序排序合并連接（合并連接（Sort Merge JoinSort Merge Join） 嵌套循環（嵌套循環（Nested LoopsNested Loops） 哈希連接（哈希連接（Hash JoinHash J

15、oin）排序排序- -合并連接圖合并連接圖30嵌套循環連接圖嵌套循環連接圖31連接適用情況連接適用情況32 排序排序合并連接合并連接 非等值連接、關聯列都有索引非等值連接、關聯列都有索引 嵌套連接嵌套連接 驅動表較小驅動表較小 哈希連接哈希連接 等值連接等值連接生成執行計劃生成執行計劃33 sql set autotrace onsql set autotrace on sql explain plan for select sql explain plan for select 用用dbms_systemdbms_system存儲過程生成執行計劃存儲過程生成執行計劃 用用PL/SQL DEV

16、ELOPERPL/SQL DEVELOPER（F5F5）干預執行計劃干預執行計劃_ _訪問路徑訪問路徑34 FULL FULL / /* *+ FULL ( table ) + FULL ( table ) * */ / 指定該表使用指定該表使用FTSFTS INDEX INDEX / /* *+ INDEX ( table index) + INDEX ( table index) * */ / 使用該表上指定的索引對表進行索引掃描使用該表上指定的索引對表進行索引掃描 INDEX_FFS / INDEX_FFS /* *+ INDEX_FFS ( table index) + INDEX_F

17、FS ( table index) * */ / 使用快速全索引掃描使用快速全索引掃描 NO_INDEX / NO_INDEX /* *+ NO_INDEX ( table index) + NO_INDEX ( table index) * */ / 不使用該表上指定的索引進行存取，仍然可以使用不使用該表上指定的索引進行存取，仍然可以使用 其它的索引進行索引掃描其它的索引進行索引掃描干預執行計劃干預執行計劃_ _表連接表連接35 USE_NL / USE_NL /* *+ USE_NL ( tab ,tab, . ) + USE_NL ( tab ,tab, . ) * */ / 使用嵌套連

18、接使用嵌套連接 USE_MERGE / USE_MERGE /* *+ USE_MERGE ( tab ,tab,.) + USE_MERGE ( tab ,tab,.) * */ / 使用排序使用排序- - -合并連接合并連接 USE_HASH / USE_HASH /* *+ USE_HASH ( tab ,tab, .) + USE_HASH ( tab ,tab, .) * */ / 使用使用HASHHASH連接連接 二、二、Oracle Oracle 體系結構體系結構 五、五、Oracle SQL Oracle SQL 編寫規范編寫規范 四、典型案例分析暨優化要點四、典型案例分析暨優

19、化要點 三、三、Oracle SQL Oracle SQL 優化基礎優化基礎 一、培訓目的一、培訓目的培訓總目錄培訓總目錄典型案例典型案例_1_137 系統名稱：綜合集中維護支撐系統系統名稱：綜合集中維護支撐系統 故障現象：壓力測試系統故障現象：壓力測試系統CPUCPU資源資源100%100% 故障分析：故障分析： 數據庫實際的數據量僅為數據庫實際的數據量僅為6GB6GB左右。但是每左右。但是每天卻產生大概天卻產生大概150GB150GB左右的左右的REDO LOGREDO LOG 使用使用LOGMNRLOGMNR分析分析ORACLEORACLE日志，發現基本上日志，發現基本上都為都為JMSS

20、TOREJMSSTORE表的增、刪操作。該表為表的增、刪操作。該表為WEBLOGICWEBLOGIC的消息表的消息表（CLOBCLOB字段）字段）。典型案例典型案例_1_138 系統名稱：綜合集中維護支撐系統系統名稱：綜合集中維護支撐系統 故障處理：故障處理： 運行相關查詢業務，在數據庫中查詢運行相關查詢業務，在數據庫中查詢v$sqlareav$sqlarea，JMSSTOREJMSSTORE表相關操作的頻率表相關操作的頻率 對應用進行跟蹤調試，查找每筆查詢業務對應用進行跟蹤調試，查找每筆查詢業務所運行的所運行的SQLSQL 最終定位問題產生原因：應用程序在每筆最終定位問題產生原因：應用程序在

21、每筆查詢后面都附帶了消息傳輸（查詢后面都附帶了消息傳輸（1010次）次） 將該將該SQLSQL屏蔽，問題得到解決屏蔽，問題得到解決典型案例典型案例_2_239 系統名稱：系統名稱：OAOA系統系統 故障現象：故障現象：SQLSQL語句執行速度慢（語句執行速度慢（5S5S）select distinct select distinct b.fd_workflowname,a.fd_fileid,a.fd_c_filetitle,a.fd_c_enddateb.fd_workflowname,a.fd_fileid,a.fd_c_filetitle,a.fd_c_enddatefrom from

22、tb_model_workflowtb_model_workflow b,tb_documentb,tb_document c,tb_engine_filecommonc,tb_engine_filecommon a,tb_engine_filetachea,tb_engine_filetache g gwhere where a.fd_c_workflowida.fd_c_workflowid= =b.fd_workflowidb.fd_workflowid and and g.fd_fileidg.fd_fileid= =a.fd_fileida.fd_fileid and and a.f

23、d_c_fileno=c.ida.fd_c_fileno=c.idand and a.fd_c_isenda.fd_c_isend=1and =1and b.fd_workflowtypeb.fd_workflowtype=1 and (=1 and (c.security_level_codec.security_level_code=1 =1 or(or(c.security_level_codec.security_level_code=2 and =2 and g.fd_ft_staffidg.fd_ft_staffid=2)or =2)or ( (c.security_level_c

24、odec.security_level_code=3 and =3 and g.fd_ft_staffidg.fd_ft_staffid=2)=2)and and a.fd_c_enddatea.fd_c_enddate=(sysdate-30)and =(sysdate-30)and a.fd_c_enddatea.fd_c_enddate=(sysdate+1)=(sysdate+1)and (and (pany_pany_id=1 or =1 or c.send_companyc.send_company=1) =1) order by order by a.fd_c_enddatea.

25、fd_c_enddate descdesc; ;典型案例典型案例_2_240 系統名稱：系統名稱：OAOA系統系統 故障處理：故障處理： 查看該查看該sqlsql的執行計劃的執行計劃 tb_engine_filecommontb_engine_filecommon表為表為FTSFTS（full full table scantable scan），而且），而且costcost大大典型案例典型案例_2_241 系統名稱：系統名稱：OAOA系統系統 故障處理：故障處理： 查詢該表查詢該表fd_c_enddatefd_c_enddate的可選擇性的可選擇性，發現發現很高，因此對該字段建索引，很高，因

26、此對該字段建索引，執行計劃變為執行計劃變為如下，執行時間縮短為如下，執行時間縮短為0.3S0.3S左右左右典型案例典型案例_3_342 系統名稱：商務領航系統系統名稱：商務領航系統 故障現象：出賬速度慢故障現象：出賬速度慢 故障處理：故障處理： 查找查找V$SESSIONV$SESSION中正在長時間運行的中正在長時間運行的SQLSQLupdate update zjxc.BIZ_CUST_BALANCEzjxc.BIZ_CUST_BALANCE t set t set t.warefeet.warefee = 0 where = 0 where t.cusidt.cusid in in (s

27、elect (select b.cusidb.cusid from _cusid_temp_0401 b) and from _cusid_temp_0401 b) and t.year_montht.year_month = 201003; = 201003; 表表biz_cusid_temp_0401biz_cusid_temp_0401為為FTSFTS，但，但cusidcusid字段建有索引字段建有索引典型案例典型案例_3_343 系統名稱：商務領航系統系統名稱：商務領航系統 故障處理：故障處理： 改寫上述改寫上述SQLSQL語句語句update upda

28、te _cust__cust_balance t set t set t.warefeet.warefee = 0 where = 0 where exists (select 1 from _cusid_temp_0401 b exists (select 1 from _cusid_temp_0401 b where where t.cusidt.cusid= =b.cusidb.cusid and and t.year_montht.year_month = 201003) ; = 201003) ; 表表bi

29、z_cusid_temp_0401biz_cusid_temp_0401的訪問從的訪問從FTSFTS改變改變為索引唯一掃描為索引唯一掃描 SQL SQL執行時間縮短為執行時間縮短為1212秒秒典型案例典型案例_4_444 系統名稱：系統名稱：CRMCRM系統系統 故障現象：過多的故障現象：過多的latch freelatch free鎖影響性能鎖影響性能 故障處理：故障處理： 查找查找latch freelatch free對應的相關語句，都為對應的相關語句，都為SELECT COL_VALUE FROM PUB_COLUMN_REFERENCE WHERE REFER_ID = :B1SEL

30、ECT COL_VALUE FROM PUB_COLUMN_REFERENCE WHERE REFER_ID = :B1 而該而該SQLSQL語句都為函數語句都為函數GET_COLUMN_VALUEGET_COLUMN_VALUE調用所產生調用所產生 查詢調用該函數的相關可疑查詢調用該函數的相關可疑SQLSQL語句語句典型案例典型案例_4_445 系統名稱：系統名稱：CRMCRM系統系統 故障處理：故障處理： 定位定位SQLSQL有相關子查詢調用了該函數有相關子查詢調用了該函數 對該對該SQLSQL進行進行tracetrace跟蹤，發現該跟蹤，發現該SQLSQL每執行每執行一次，將會調用該函數

31、一次，將會調用該函數3835538355次，也就是說次，也就是說latch freelatch free相應的相應的SQLSQL將被執行將被執行3835538355次次 修改相關應用（修改相關應用（cachecache相關刷新機制）相關刷新機制）Oracle SQLOracle SQL優化要點優化要點46 使用執行計劃來分析使用執行計劃來分析SQLSQL性能進而進行優化性能進而進行優化 使用使用HINTSHINTS來干預執行計劃來干預執行計劃 最大限度地避免全表掃描（最大限度地避免全表掃描（FTSFTS） 減少子查詢的使用減少子查詢的使用 規范規范SQLSQL語句的編寫，使得語句的編寫，使得S

32、QLSQL語句能夠被共享語句能夠被共享 減少減少SQLSQL解析的次數：使用綁定變量解析的次數：使用綁定變量Oracle SQLOracle SQL優化要點優化要點47 用索引提高效率用索引提高效率 避免在索引列上使用計算避免在索引列上使用計算 避免在索引列上使用函數轉換避免在索引列上使用函數轉換 避免改變索引列的類型避免改變索引列的類型 避免在索引列上使用避免在索引列上使用IS NULLIS NULL和和IS NOT IS NOT NULLNULL 避免在索引列上使用避免在索引列上使用like%AAAlike%AAAOracle SQLOracle SQL優化要點優化要點48 盡量避免盡量避

33、免SQLSQL語句不必要的排序操作語句不必要的排序操作 UNIONUNION操作要用操作要用UNION ALLUNION ALL來代替來代替 ORDER BY ORDER BY 中的字段最好建立索引中的字段最好建立索引 盡量避免使用盡量避免使用DISTINCTDISTINCT語句語句 盡量避免使用盡量避免使用(NOT) IN(NOT) IN，而使用（，而使用（NOTNOT） EXISTSEXISTS來代替來代替 對表超過對表超過10%10%以上的以上的deletedelete、updateupdate操作要重操作要重建索引。建索引。 五、五、Oracle SQL Oracle SQL 編寫規范

34、編寫規范 二、二、Oracle Oracle 體系結構體系結構 四、典型案例分析暨優化要點四、典型案例分析暨優化要點 三、三、Oracle SQL Oracle SQL 優化基礎優化基礎 一、培訓目的一、培訓目的培訓總目錄培訓總目錄SQLSQL編寫規范編寫規范_ _書寫格式書寫格式50 縮進縮進 對于存儲過程文件，縮進為對于存儲過程文件，縮進為8 8個空格個空格 對于對于Java SourceJava Source里的里的SQLSQL字符串，不可有字符串，不可有縮進，即每一行字符串不以空格開頭縮進，即每一行字符串不以空格開頭 空格空格 SQL SQL內算數運算符、邏輯運算符連接的兩個內算數運算符、邏輯運算符連接的兩個元素之間必須用空格分隔元素之間必須用空格分隔 逗號之后必須接一個空格逗號之后必須接一個空格 關鍵字、保留字和左括號間必有一個空格關鍵字、保留字和左括號間必有一個空格SQLSQL編寫規范編寫規范_ _書寫格式書寫格式51 換行換行_1_1 Select/From/Where/O