Oracle培訓(xùn)課件優(yōu)選Oracle培訓(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)化要點

三、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)點管理簡單相對較為穩(wěn)定缺點切換間存在停頓備機(jī)空閑無擴(kuò)展性ComputerBComputerAOracle高可用性_雙機(jī)熱備切換Oracle高可用性_RAC優(yōu)點良好的可伸縮性、可擴(kuò)展性高可用性新進(jìn)的Cache Fusion技術(shù)負(fù)載均衡缺點管理復(fù)雜對網(wǎng)絡(luò)要求高Oracle高可用性_RAC將應(yīng)用和用戶自動而透明地重新連接到另一個系統(tǒng)登錄的上下文可被保持應(yīng)用查詢不會中斷節(jié)點A節(jié)點B節(jié)點A節(jié)點B當(dāng)集群內(nèi)節(jié)點A失敗,用戶被轉(zhuǎn)移Oracle

RAC負(fù)載均衡數(shù)據(jù)庫啟動時，向監(jiān)聽進(jìn)程注冊節(jié)點向監(jiān)聽進(jìn)程報告CPU的使用情況當(dāng)建立連接時，監(jiān)聽進(jìn)程選擇最空閑的節(jié)點

處理請求

二、Oracle體系結(jié)構(gòu)

五、OracleSQL編寫規(guī)范

四、典型案例分析暨優(yōu)化要點

三、OracleSQL優(yōu)化基礎(chǔ)

一、培訓(xùn)目的培訓(xùn)總目錄性能優(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ù)量大主要用于查詢16SQL語句處理過程查詢語句處理

DML語句處理

（insert、delete、update）

DDL語句處理

（create、drop、alter）17SQL語句處理各個階段18SQL語句處理必經(jīng)階段第1步:CreateaCursor創(chuàng)建游標(biāo)第2步:ParsetheStatement分析語句第5步:BindAnyVariables綁定變量第7步:RuntheStatement運(yùn)行語句第9步:ClosetheCursor關(guān)閉游標(biāo)19SQL語句處理可選階段并行功能第6步：ParallelizetheStatement并行執(zhí)行語句查詢語句第3步：DescribeResultsofaQuery描述查詢的結(jié)果集第4步：DefineOutputofaQuery定義查詢的輸出數(shù)據(jù)第8步：FetchRowsofaQuery取查詢出來的行20DDL語句處理過程

不同于DML語句和查詢語句的執(zhí)行

DDL成功后需要對數(shù)據(jù)字典表進(jìn)行修改分析階段還包括分析、查找數(shù)據(jù)字典

信息和執(zhí)行21Oracle優(yōu)化器優(yōu)化:選擇最有效的執(zhí)行計劃來執(zhí)行SQL語句的過程優(yōu)化器:選擇執(zhí)行計劃的數(shù)據(jù)庫引擎基于規(guī)則（RBO）基于代價（CBO）22Oracle統(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ù)”的比值25OracleSQL執(zhí)行計劃SQL語句:SELECTename,job,sal,dnameFROMemp,deptWHEREemp.deptno=dept.deptnoANDNOTEXISTS(SELECT*FROMsalgradeWHEREemp.salBETWEENlosalANDhisal);26OracleSQL執(zhí)行計劃圖27OracleSQL執(zhí)行計劃圖28訪問路徑全表掃描（FTS）通過rowid的表存?。═ableAccessbyrowid）索引掃描（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í)行計劃sql>setautotraceonsql>explainplanforselect……………用dbms_system存儲過程生成執(zhí)行計劃用PL/SQLDEVELOPER（F5）34干預(yù)執(zhí)行計劃_訪問路徑FULL

/*+FULL(table)*/

指定該表使用FTSINDEX

/*+INDEX(table[index])*/

使用該表上指定的索引對表進(jìn)行索引掃描INDEX_FFS/*+INDEX_FFS(table[index])*/

使用快速全索引掃描NO_INDEX/*+NO_INDEX(table[index])*/

不使用該表上指定的索引進(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連接36

二、Oracle體系結(jié)構(gòu)

五、OracleSQL編寫規(guī)范

四、典型案例分析暨優(yōu)化要點

三、OracleSQL優(yōu)化基礎(chǔ)

一、培訓(xùn)目的培訓(xùn)總目錄典型案例_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='1'andb.fd_workflowtype='1'and(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(selectb.cusidfrom_cusid_temp_0401b)andt.year_month='201003';

表biz_cusid_temp_0401為FTS，但cusid字段建有索引43典型案例_3系統(tǒng)名稱：商務(wù)領(lǐng)航系統(tǒng)故障處理：

改寫上述SQL語句update_cust_balancetsett.warefee=0whereexists(select1from_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ī)制）46OracleSQL優(yōu)化要點使用執(zhí)行計劃來分析SQL性能進(jìn)而進(jìn)行優(yōu)化使用HINTS來干預(yù)執(zhí)行計劃最大限度地避免全表掃描（FTS）減少子查詢的使用規(guī)范SQL語句的編寫，使得SQL語句能夠被共享減少SQL解析的次數(shù)：使用綁定變量47OracleSQL優(yōu)化要點用索引提高效率避免在索引列上使用計算避免在索引列上使用函數(shù)轉(zhuǎn)換避免改變索引列的類型避免在索引列上使用ISNULL和ISNOTNULL

避免在索引列上使用like‘%AAA’48OracleSQL優(yōu)化要點盡量避免SQL語句不必要的排序操作

UNION操作要用UNIONALL來代替

ORDERBY中的字段最好建立索引盡量避免使用DISTINCT語句盡量避免使用(NOT)IN，而使用（NOT）EX