Oracle培訓(xùn)ppt課件

二、Oracle體系結(jié)構(gòu),五、OracleSQL編寫規(guī)范,四、典型案例分析暨優(yōu)化要點(diǎn),三、OracleSQL優(yōu)化基礎(chǔ),一、培訓(xùn)目的,培訓(xùn)總目錄,培訓(xùn)目的,培訓(xùn)目標(biāo),培訓(xùn)內(nèi)容,課堂交互,本次課程大約需要3個小時，課后，我們會留半個小時與大家進(jìn)行探討，希望在座各位在培訓(xùn)過程中記錄下自己的問題；,Oracle體系結(jié)構(gòu)；,OracleSQL優(yōu)化及編寫規(guī)范；,了解Oracle體系架構(gòu)；掌握OracleSQL優(yōu)化基本方法；,OracleSQL優(yōu)化典型案例分析；,二、Oracle體系結(jié)構(gòu),五、OracleSQL編寫規(guī)范,四、典型案例分析暨優(yōu)化要點(diǎn),三、OracleSQL優(yōu)化基礎(chǔ),一、培訓(xùn)目的,培訓(xùn)總目錄,Oracle服務(wù)器架構(gòu),Oracle內(nèi)存體系架構(gòu),Oracle進(jìn)程體系架構(gòu),用戶進(jìn)程:在數(shù)據(jù)庫用戶或批處理進(jìn)程連接到OracleDB時啟動服務(wù)器進(jìn)程:連接到Oracle實例，在用戶建立會話時啟動后臺進(jìn)程:在Oracle實例啟動時啟動,Oracle存儲體系架構(gòu),Oracle高可用性_雙機(jī)熱備,優(yōu)點(diǎn)管理簡單相對較為穩(wěn)定缺點(diǎn)切換間存在停頓備機(jī)空閑無擴(kuò)展性,Oracle高可用性_雙機(jī)熱備切換,Oracle高可用性_RAC,優(yōu)點(diǎn)良好的可伸縮性、可擴(kuò)展性高可用性新進(jìn)的CacheFusion技術(shù)負(fù)載均衡缺點(diǎn)管理復(fù)雜對網(wǎng)絡(luò)要求高,Oracle高可用性_RAC,將應(yīng)用和用戶自動而透明地重新連接到另一個系統(tǒng)登錄的上下文可被保持應(yīng)用查詢不會中斷,OracleRAC負(fù)載均衡,數(shù)據(jù)庫啟動時，向監(jiān)聽進(jìn)程注冊節(jié)點(diǎn)向監(jiān)聽進(jìn)程報告CPU的使用情況當(dāng)建立連接時，監(jiān)聽進(jìn)程選擇最空閑的節(jié)點(diǎn)處理請求,二、Oracle體系結(jié)構(gòu),五、OracleSQL編寫規(guī)范,四、典型案例分析暨優(yōu)化要點(diǎn),三、OracleSQL優(yōu)化基礎(chǔ),一、培訓(xùn)目的,培訓(xùn)總目錄,14,性能優(yōu)化概述,誰來調(diào)整系統(tǒng)應(yīng)用設(shè)計人員、應(yīng)用開發(fā)人員數(shù)據(jù)庫管理人員什么時候調(diào)整系統(tǒng)設(shè)計、系統(tǒng)開發(fā)過程系統(tǒng)運(yùn)行過程,15,應(yīng)用系統(tǒng)類型,聯(lián)機(jī)事務(wù)處理（OLTP）高吞吐量增、刪、改多決策支持（DSS）數(shù)據(jù)量大主要用于查詢,16,SQL語句處理過程,查詢語句處理DML語句處理（insert、delete、update）DDL語句處理（create、drop、alter）,17,SQL語句處理各個階段,18,SQL語句處理必經(jīng)階段,第1步:CreateaCursor創(chuàng)建游標(biāo)第2步:ParsetheStatement分析語句第5步:BindAnyVariables綁定變量第7步:RuntheStatement運(yùn)行語句第9步:ClosetheCursor關(guān)閉游標(biāo),19,SQL語句處理可選階段,并行功能第6步：ParallelizetheStatement并行執(zhí)行語句查詢語句第3步：DescribeResultsofaQuery描述查詢的結(jié)果集第4步：DefineOutputofaQuery定義查詢的輸出數(shù)據(jù)第8步：FetchRowsofaQuery取查詢出來的行,20,DDL語句處理過程,不同于DML語句和查詢語句的執(zhí)行DDL成功后需要對數(shù)據(jù)字典表進(jìn)行修改分析階段還包括分析、查找數(shù)據(jù)字典信息和執(zhí)行,21,Oracle優(yōu)化器,優(yōu)化:選擇最有效的執(zhí)行計劃來執(zhí)行SQL語句的過程優(yōu)化器:選擇執(zhí)行計劃的數(shù)據(jù)庫引擎基于規(guī)則（RBO）基于代價（CBO）,22,Oracle統(tǒng)計信息,影響CBO執(zhí)行計劃最關(guān)鍵的因素分析的方法analyze命令dbms_stat包分析的頻率Oracle10g自動分析,23,共享SQL語句,儲存于共享池（shared_pool）判斷SQL語句是否與共享池中某一SQL相同的步驟（CURSOR_SHARING）對所發(fā)出語句的文本串進(jìn)行hashed將所發(fā)出語句的文本串進(jìn)行比較將SQL中涉及的對象進(jìn)行比較,24,基本概念,ROWID偽列，唯一。定位數(shù)據(jù)的最快方法索引創(chuàng)建時會記錄ROWID值DrivingTable（驅(qū)動表）:外層表，用于嵌套以及hash連接中可選擇性:“唯一鍵的數(shù)量/表中的行數(shù)”的比值,25,OracleSQL執(zhí)行計劃,SQL語句:SELECTename,job,sal,dnameFROMemp,deptWHEREemp.deptno=dept.deptnoANDNOTEXISTS(SELECT*FROMsalgradeWHEREemp.salBETWEENlosalANDhisal);,26,OracleSQL執(zhí)行計劃圖,27,OracleSQL執(zhí)行計劃圖,28,訪問路徑,全表掃描（FTS）通過rowid的表存取（TableAccessbyrowid）索引掃描（IndexScan）索引唯一掃描(indexuniquescan)索引范圍掃描(indexrangescan)索引全掃描(indexfullscan)索引快速掃描(indexfastfullscan),29,表連接,表連接JOIN:將兩個表結(jié)合在一起，一次只能連接2個表，表連接也可以被稱為表關(guān)聯(lián)排序合并連接（SortMergeJoin）嵌套循環(huán)（NestedLoops）哈希連接（HashJoin）,30,排序-合并連接圖,31,嵌套循環(huán)連接圖,32,連接適用情況,排序合并連接非等值連接、關(guān)聯(lián)列都有索引嵌套連接驅(qū)動表較小哈希連接等值連接,33,生成執(zhí)行計劃,sqlsetautotraceonsqlexplainplanforselect用dbms_system存儲過程生成執(zhí)行計劃用PL/SQLDEVELOPER（F5）,34,干預(yù)執(zhí)行計劃_訪問路徑,FULL/*+FULL(table)*/指定該表使用FTSINDEX/*+INDEX(tableindex)*/使用該表上指定的索引對表進(jìn)行索引掃描INDEX_FFS/*+INDEX_FFS(tableindex)*/使用快速全索引掃描NO_INDEX/*+NO_INDEX(tableindex)*/不使用該表上指定的索引進(jìn)行存取，仍然可以使用其它的索引進(jìn)行索引掃描,35,干預(yù)執(zhí)行計劃_表連接,USE_NL/*+USE_NL(tab,tab,.)*/使用嵌套連接USE_MERGE/*+USE_MERGE(tab,tab,.)*/使用排序-合并連接USE_HASH/*+USE_HASH(tab,tab,.)*/使用HASH連接,二、Oracle體系結(jié)構(gòu),五、OracleSQL編寫規(guī)范,四、典型案例分析暨優(yōu)化要點(diǎn),三、OracleSQL優(yōu)化基礎(chǔ),一、培訓(xùn)目的,培訓(xùn)總目錄,37,典型案例_1,系統(tǒng)名稱：綜合集中維護(hù)支撐系統(tǒng)故障現(xiàn)象：壓力測試系統(tǒng)CPU資源100%故障分析：數(shù)據(jù)庫實際的數(shù)據(jù)量僅為6GB左右。但是每天卻產(chǎn)生大概150GB左右的REDOLOG使用LOGMNR分析ORACLE日志，發(fā)現(xiàn)基本上都為JMSSTORE表的增、刪操作。該表為WEBLOGIC的消息表（CLOB字段）。,38,典型案例_1,系統(tǒng)名稱：綜合集中維護(hù)支撐系統(tǒng)故障處理：運(yùn)行相關(guān)查詢業(yè)務(wù)，在數(shù)據(jù)庫中查詢v$sqlarea，JMSSTORE表相關(guān)操作的頻率對應(yīng)用進(jìn)行跟蹤調(diào)試，查找每筆查詢業(yè)務(wù)所運(yùn)行的SQL最終定位問題產(chǎn)生原因：應(yīng)用程序在每筆查詢后面都附帶了消息傳輸（10次）將該SQL屏蔽，問題得到解決,39,典型案例_2,系統(tǒng)名稱：OA系統(tǒng)故障現(xiàn)象：SQL語句執(zhí)行速度慢（5S）selectdistinctb.fd_workflowname,a.fd_fileid,a.fd_c_filetitle,a.fd_c_enddatefromtb_model_workflowb,tb_documentc,tb_engine_filecommona,tb_engine_filetachegwherea.fd_c_workflowid=b.fd_workflowidandg.fd_fileid=a.fd_fileidanda.fd_c_fileno=c.idanda.fd_c_isend=1andb.fd_workflowtype=1and(c.security_level_code=1or(c.security_level_code=2andg.fd_ft_staffid=2)or(c.security_level_code=3andg.fd_ft_staffid=2)anda.fd_c_enddate=(sysdate-30)anda.fd_c_enddate=(sysdate+1)and(pany_id=1orc.send_company=1)orderbya.fd_c_enddatedesc;,40,典型案例_2,系統(tǒng)名稱：OA系統(tǒng)故障處理：查看該sql的執(zhí)行計劃tb_engine_filecommon表為FTS（fulltablescan），而且cost大,41,典型案例_2,系統(tǒng)名稱：OA系統(tǒng)故障處理：查詢該表fd_c_enddate的可選擇性，發(fā)現(xiàn)很高，因此對該字段建索引，執(zhí)行計劃變?yōu)槿缦?，?zhí)行時間縮短為0.3S左右,42,典型案例_3,系統(tǒng)名稱：商務(wù)領(lǐng)航系統(tǒng)故障現(xiàn)象：出賬速度慢故障處理：查找V$SESSION中正在長時間運(yùn)行的SQLupdatezjxc.BIZ_CUST_BALANCEtsett.warefee=0wheret.cusidin(_cusid_temp_0401b)andt.year_month=201003;表biz_cusid_temp_0401為FTS，但cusid字段建有索引,43,典型案例_3,系統(tǒng)名稱：商務(wù)領(lǐng)航系統(tǒng)故障處理：改寫上述SQL語句_cust_balancetsett.warefee=0whereexists(_cusid_temp_0401bwheret.cusid=b.cusidandt.year_month=201003);表biz_cusid_temp_0401的訪問從FTS改變?yōu)樗饕ㄒ粧呙鑃QL執(zhí)行時間縮短為12秒,44,典型案例_4,系統(tǒng)名稱：CRM系統(tǒng)故障現(xiàn)象：過多的latchfree鎖影響性能故障處理：查找latchfree對應(yīng)的相關(guān)語句，都為SELECTCOL_VALUEFROMPUB_COLUMN_REFERENCEWHEREREFER_ID=:B1而該SQL語句都為函數(shù)GET_COLUMN_VALUE調(diào)用所產(chǎn)生查詢調(diào)用該函數(shù)的相關(guān)可疑SQL語句,45,典型案例_4,系統(tǒng)名稱：CRM系統(tǒng)故障處理：定位SQL有相關(guān)子查詢調(diào)用了該函數(shù)對該SQL進(jìn)行trace跟蹤，發(fā)現(xiàn)該SQL每執(zhí)行一次，將會調(diào)用該函數(shù)38355次，也就是說latchfree相應(yīng)的SQL將被執(zhí)行38355次修改相關(guān)應(yīng)用（cache相關(guān)刷新機(jī)制）,46,OracleSQL優(yōu)化要點(diǎn),使用執(zhí)行計劃來分析SQL性能進(jìn)而進(jìn)行優(yōu)化使用HINTS來干預(yù)執(zhí)行計劃最大限度地避免全表掃描（FTS）減少子查詢的使用規(guī)范SQL語句的編寫，使得SQL語句能夠被共享減少SQL解析的次數(shù)：使用綁定變量,47,OracleSQL優(yōu)化要點(diǎn),用索引提高效率避免在索引列上使用計算避免在索引列上使用函數(shù)轉(zhuǎn)換避免改變索引列的類型避免在索引列上使用ISNULL和ISNOTNULL避免在索引列上使用like%AAA,48,OracleSQL優(yōu)化要點(diǎn),盡量避免SQL語句不必要的排序操作UNION操作要用UNIONALL來代替ORDERBY中的字段最好建立索引盡量避免使用DISTINCT語句盡量避免使用(NOT)IN，而使用（NOT）EXISTS來代替對表超過10%以上的delete、update操作要重建索引。,五、OracleSQL編寫規(guī)范,二、Oracle體系結(jié)構(gòu),四、典型案例分析暨優(yōu)化要點(diǎn),三、OracleSQL優(yōu)化基礎(chǔ),一、培訓(xùn)目的,培訓(xùn)總目錄,50,SQL編寫規(guī)范_書寫格式,縮進(jìn)對于存儲過程文件，縮進(jìn)為8個空格對于JavaSource里的SQL字符串，不可有縮進(jìn)，即每一行字符串不以空格開頭空格SQL內(nèi)算數(shù)運(yùn)算符、邏輯運(yùn)算符連接的兩個元素之間必須用空格分隔逗號之后必須接一個空格關(guān)鍵字、保留字和左括號間必有一個空格,51,SQL編寫規(guī)范_書寫格式,換行_1Select/From/Where/Orderby/Groupby等子句必須另起一行寫Select子句內(nèi)容如果只有一項，與Select同行寫Select子句內(nèi)容如果多于一項，每一項單獨(dú)占一行，在對應(yīng)Select的基礎(chǔ)上向右縮進(jìn)8個空格（Javasource無縮進(jìn)）,52,SQL編寫規(guī)范_書寫格式,換行_2From子句內(nèi)容如果只有一項，與From同行From子句內(nèi)容如果多于一項，每一項單獨(dú)占一行，在對應(yīng)From的基礎(chǔ)上向右縮進(jìn)8個空格（Javasource無縮進(jìn)）Where子句的條件如果有多項，每一個條件占一行，以AND開頭，且無縮進(jìn),53,SQL編寫規(guī)范_書寫格式,換行_3Insert子句內(nèi)