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