ORACLE數(shù)據(jù)庫(kù)性能優(yōu)化技術(shù)

1、.：.；作為全球第一大數(shù)據(jù)庫(kù)廠商，ORACLE數(shù)據(jù)庫(kù)在國(guó)內(nèi)外獲得了諸多勝利運(yùn)用，據(jù)統(tǒng)計(jì)，全球93%的上市公司、65家“財(cái)富全球100強(qiáng)企業(yè)不約而同地采用Oracle數(shù)據(jù)庫(kù)來(lái)開(kāi)展電子商務(wù)。Oracle在國(guó)內(nèi)勝利的案例包括多媒體數(shù)據(jù)庫(kù)及信息效力系統(tǒng)、中國(guó)銀行、建立銀行、清華大學(xué)信息系統(tǒng)等。隨著網(wǎng)絡(luò)運(yùn)用和電子商務(wù)的不斷開(kāi)展，各個(gè)站點(diǎn)的訪問(wèn)量越來(lái)越大，如何運(yùn)用有限的計(jì)算機(jī)系統(tǒng)資源為更多的用戶效力？如何保證用戶的呼應(yīng)速度和效力質(zhì)量？這些問(wèn)題都屬于效力器性能優(yōu)化的范疇。ORACLE數(shù)據(jù)庫(kù)性能優(yōu)化概述實(shí)踐上，為了保證ORACLE數(shù)據(jù)庫(kù)運(yùn)轉(zhuǎn)在最正確的性能形狀下，在信息系統(tǒng)開(kāi)發(fā)之前就應(yīng)該思索數(shù)據(jù)庫(kù)的優(yōu)化戰(zhàn)略。優(yōu)

2、化戰(zhàn)略普通包括效力器操作系統(tǒng)參數(shù)調(diào)整、ORACLE數(shù)據(jù)庫(kù)參數(shù)調(diào)整、網(wǎng)絡(luò)性能調(diào)整、運(yùn)用程序SQL語(yǔ)句分析及設(shè)計(jì)等幾個(gè)方面，其中運(yùn)用程序的分析與設(shè)計(jì)是在信息系統(tǒng)開(kāi)發(fā)之前完成的。分析評(píng)價(jià)ORACLE數(shù)據(jù)庫(kù)性能主要有數(shù)據(jù)庫(kù)吞吐量、數(shù)據(jù)庫(kù)用戶呼應(yīng)時(shí)間兩項(xiàng)目的。數(shù)據(jù)庫(kù)吞吐量是指單位時(shí)間內(nèi)數(shù)據(jù)庫(kù)完成的SQL語(yǔ)句數(shù)目；數(shù)據(jù)庫(kù)用戶呼應(yīng)時(shí)間是指用戶從提交SQL語(yǔ)句開(kāi)場(chǎng)到獲得結(jié)果的那一段時(shí)間。數(shù)據(jù)庫(kù)用戶呼應(yīng)時(shí)間又可以分為系統(tǒng)效力時(shí)間和用戶等待時(shí)間兩項(xiàng)，即：數(shù)據(jù)庫(kù)用戶呼應(yīng)時(shí)間系統(tǒng)效力時(shí)間 用戶等待時(shí)間上述公式通知我們，獲得稱心的用戶呼應(yīng)時(shí)間有兩個(gè)途徑：一是減少系統(tǒng)效力時(shí)間，即提高數(shù)據(jù)庫(kù)的吞吐量；二是減少用戶等待時(shí)間，

3、即減少用戶訪問(wèn)同一數(shù)據(jù)庫(kù)資源的沖突率。數(shù)據(jù)庫(kù)性能優(yōu)化包括如下幾個(gè)部分：1、調(diào)整數(shù)據(jù)構(gòu)造的設(shè)計(jì)。這一部分在開(kāi)發(fā)信息系統(tǒng)之前完成，程序員需求思索能否運(yùn)用ORACLE數(shù)據(jù)庫(kù)的分區(qū)功能，對(duì)于經(jīng)常訪問(wèn)的數(shù)據(jù)庫(kù)表能否需求建立索引等。2、調(diào)整運(yùn)用程序構(gòu)造設(shè)計(jì)。這一部分也是在開(kāi)發(fā)信息系統(tǒng)之前完成，程序員在這一步需求思索運(yùn)用程序運(yùn)用什么樣的體系構(gòu)造，是運(yùn)用傳統(tǒng)的Client/Server兩層體系構(gòu)造，還是運(yùn)用Browser/Web/Database的三層體系構(gòu)造。不同的運(yùn)用程序體系構(gòu)造要求的數(shù)據(jù)庫(kù)資源是不同的。3、調(diào)整數(shù)據(jù)庫(kù)SQL語(yǔ)句。運(yùn)用程序的執(zhí)行最終將歸結(jié)為數(shù)據(jù)庫(kù)中的SQL語(yǔ)句執(zhí)行，因此SQL語(yǔ)句的執(zhí)行效率

4、最終決議了ORACLE數(shù)據(jù)庫(kù)的性能。ORACLE公司引薦運(yùn)用ORACLE語(yǔ)句優(yōu)化器Oracle Optimizer和行鎖管理器row-level manager來(lái)調(diào)整優(yōu)化SQL語(yǔ)句。4、調(diào)整效力器內(nèi)存分配。內(nèi)存分配是在信息系統(tǒng)運(yùn)轉(zhuǎn)過(guò)程中優(yōu)化配置的，數(shù)據(jù)庫(kù)管理員可以根據(jù)數(shù)據(jù)庫(kù)運(yùn)轉(zhuǎn)情況調(diào)整數(shù)據(jù)庫(kù)系統(tǒng)全局區(qū)SGA區(qū)的數(shù)據(jù)緩沖區(qū)、日志緩沖區(qū)和共享池的大??；還可以調(diào)整程序全局區(qū)PGA區(qū)的大小。需求留意的是，SGA區(qū)不是越大越好，SGA區(qū)過(guò)大會(huì)占用操作系統(tǒng)運(yùn)用的內(nèi)存而引起虛擬內(nèi)存的頁(yè)面交換，這樣反而會(huì)降低系統(tǒng)。5、調(diào)整硬盤I/O，這一步是在信息系統(tǒng)開(kāi)發(fā)之前完成的。數(shù)據(jù)庫(kù)管理員可以將組成同一個(gè)表空間的數(shù)據(jù)文

5、件放在不同的硬盤上，做到硬盤之間I/O負(fù)載平衡。6、調(diào)整操作系統(tǒng)參數(shù)，例如：運(yùn)轉(zhuǎn)在UNIX操作系統(tǒng)上的ORACLE數(shù)據(jù)庫(kù)，可以調(diào)整UNIX數(shù)據(jù)緩沖池的大小，每個(gè)進(jìn)程所能運(yùn)用的內(nèi)存大小等參數(shù)。實(shí)踐上，上述數(shù)據(jù)庫(kù)優(yōu)化措施之間是相互聯(lián)絡(luò)的。ORACLE數(shù)據(jù)庫(kù)性能惡化表現(xiàn)根本上都是用戶呼應(yīng)時(shí)間比較長(zhǎng)，需求用戶長(zhǎng)時(shí)間的等待。但性能惡化的緣由卻是多種多樣的，有時(shí)是多個(gè)要素共同呵斥了性能惡化的結(jié)果，這就需求數(shù)據(jù)庫(kù)管理員有比較全面的計(jì)算機(jī)知識(shí)，可以敏感地覺(jué)察到影響數(shù)據(jù)庫(kù)性能的主要緣由所在。另外，良好的數(shù)據(jù)庫(kù)管理工具對(duì)于優(yōu)化數(shù)據(jù)庫(kù)性能也是很重要的。ORACLE數(shù)據(jù)庫(kù)性能優(yōu)化工具常用的數(shù)據(jù)庫(kù)性能優(yōu)化工具有：1、O

6、RACLE數(shù)據(jù)庫(kù)在線數(shù)據(jù)字典，ORACLE在線數(shù)據(jù)字典可以反映出ORACLE動(dòng)態(tài)運(yùn)轉(zhuǎn)情況，對(duì)于調(diào)整數(shù)據(jù)庫(kù)性能是很有協(xié)助 的。2、操作系統(tǒng)工具，例如UNIX操作系統(tǒng)的vmstat，iostat等命令可以查看到系統(tǒng)系統(tǒng)級(jí)內(nèi)存和硬盤I/O的運(yùn)用情況，這些工具對(duì)于管理員弄清出系統(tǒng)瓶頸出如今什么地方有時(shí)候很有用。3、SQL言語(yǔ)跟蹤工具SQL TRACE FACILITY，SQL言語(yǔ)跟蹤工具可以記錄SQL語(yǔ)句的執(zhí)行情況，管理員可以運(yùn)用虛擬表來(lái)調(diào)整實(shí)例，運(yùn)用SQL語(yǔ)句跟蹤文件調(diào)整運(yùn)用程序性能。SQL言語(yǔ)跟蹤工具將結(jié)果輸出成一個(gè)操作系統(tǒng)的文件，管理員可以運(yùn)用TKPROF工具查看這些文件。4、ORACLE En

7、terprise ManagerOEM，這是一個(gè)圖形的用戶管理界面，用戶可以運(yùn)用它方便地進(jìn)展數(shù)據(jù)庫(kù)管理而不用記住復(fù)雜的ORACLE數(shù)據(jù)庫(kù)管理的命令。5、EXPLAIN PLANSQL言語(yǔ)優(yōu)化命令，運(yùn)用這個(gè)命令可以協(xié)助 程序員寫出高效的SQL言語(yǔ)。ORACLE數(shù)據(jù)庫(kù)的系統(tǒng)性能評(píng)價(jià)信息系統(tǒng)的類型不同，需求關(guān)注的數(shù)據(jù)庫(kù)參數(shù)也是不同的。數(shù)據(jù)庫(kù)管理員需求根據(jù)本人的信息系統(tǒng)的類型著重思索不同的數(shù)據(jù)庫(kù)參數(shù)。1、在線事務(wù)處置信息系統(tǒng)OLTP，這種類型的信息系統(tǒng)普通需求有大量的Insert、Update操作，典型的系統(tǒng)包括民航機(jī)票出賣系統(tǒng)、銀行儲(chǔ)蓄系統(tǒng)等。OLTP系統(tǒng)需求保證數(shù)據(jù)庫(kù)的并發(fā)性、可靠性和最終用戶的速

8、度，這類系統(tǒng)運(yùn)用的ORACLE數(shù)據(jù)庫(kù)需求主要思索下述參數(shù)：l 數(shù)據(jù)庫(kù)回滾段能否足夠？l 能否需求建立ORACLE數(shù)據(jù)庫(kù)索引、聚集、散列？l 系統(tǒng)全局區(qū)SGA大小能否足夠？l SQL語(yǔ)句能否高效？2、數(shù)據(jù)倉(cāng)庫(kù)系統(tǒng)Data Warehousing，這種信息系統(tǒng)的主要義務(wù)是從ORACLE的海量數(shù)據(jù)中進(jìn)展查詢，得到數(shù)據(jù)之間的某些規(guī)律。數(shù)據(jù)庫(kù)管理員需求為這種類型的ORACLE數(shù)據(jù)庫(kù)著重思索下述參數(shù)：l 能否采用B*-索引或者bitmap索引？l 能否采用并行SQL查詢以提高查詢效率？l 能否采用PL/SQL函數(shù)編寫存儲(chǔ)過(guò)程？l 有必要的話，需求建立并行數(shù)據(jù)庫(kù)提高數(shù)據(jù)庫(kù)的查詢效率SQL語(yǔ)句的調(diào)整原那么SQ

9、L言語(yǔ)是一種靈敏的言語(yǔ)，一樣的功能可以運(yùn)用不同的語(yǔ)句來(lái)實(shí)現(xiàn)，但是語(yǔ)句的執(zhí)行效率是很不一樣的。程序員可以運(yùn)用EXPLAIN PLAN語(yǔ)句來(lái)比較各種實(shí)現(xiàn)方案，并選出最優(yōu)的實(shí)現(xiàn)方案?？偟脕?lái)講，程序員寫SQL語(yǔ)句需求滿足思索如下規(guī)那么：1、盡量運(yùn)用索引。試比較下面兩條SQL語(yǔ)句：語(yǔ)句A：SELECT dname, deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp);語(yǔ)句B：SELECT dname, deptno FROM dept WHERE NOT EXISTS(SELECT deptno FROM emp WHERE dep

10、t.deptno = emp.deptno);這兩條查詢語(yǔ)句實(shí)現(xiàn)的結(jié)果是一樣的，但是執(zhí)行語(yǔ)句A的時(shí)候，ORACLE會(huì)對(duì)整個(gè)emp表進(jìn)展掃描，沒(méi)有運(yùn)用建立在emp表上的deptno索引，執(zhí)行語(yǔ)句B的時(shí)候，由于在子查詢中運(yùn)用了結(jié)合查詢，ORACLE只是對(duì)emp表進(jìn)展的部分?jǐn)?shù)據(jù)掃描，并利用了deptno列的索引，所以語(yǔ)句B的效率要比語(yǔ)句A的效率高一些。2、選擇結(jié)合查詢的結(jié)合次序。思索下面的例子：SELECT stuff FROM taba a, tabb b, tabc c WHERE a.acol between :alow and :ahigh AND b.bcol between :blow

11、and :bhigh AND c.ccol between :clow and :chigh AND a.key1 = b.key1 AMD a.key2 = c.key2;這個(gè)SQL例子中，程序員首先需求選擇要查詢的主表，由于主表要進(jìn)展整個(gè)表數(shù)據(jù)的掃描，所以主表應(yīng)該數(shù)據(jù)量最小，所以例子中表A的acol列的范圍應(yīng)該比表B和表C相應(yīng)列的范圍小。3、在子查詢中慎重運(yùn)用IN或者NOT IN語(yǔ)句，運(yùn)用where (NOT) exists的效果要好的多。4、慎重運(yùn)用視圖的結(jié)合查詢，尤其是比較復(fù)雜的視圖之間的結(jié)合查詢。普通對(duì)視圖的查詢最好都分解為對(duì)數(shù)據(jù)表的直接查詢效果要好一些。5、可以在參數(shù)文件中設(shè)置SH

12、ARED_POOL_RESERVED_SIZE參數(shù)，這個(gè)參數(shù)在SGA共享池中保管一個(gè)延續(xù)的內(nèi)存空間，延續(xù)的內(nèi)存空間有益于存放大的SQL程序包。6、ORACLE公司提供的DBMS_SHARED_POOL程序可以協(xié)助 程序員將某些經(jīng)常運(yùn)用的存儲(chǔ)過(guò)程“釘在SQL區(qū)中而不被換出內(nèi)存，程序員對(duì)于經(jīng)常運(yùn)用并且占用內(nèi)存很多的存儲(chǔ)過(guò)程“釘?shù)絻?nèi)存中有利于提高最終用戶的呼應(yīng)時(shí)間。CPU參數(shù)的調(diào)整CPU是效力器的一項(xiàng)重要資源，效力器良好的任務(wù)形狀是在任務(wù)頂峰時(shí)CPU的運(yùn)用率在90以上。假設(shè)空閑時(shí)間CPU運(yùn)用率就在90以上，闡明效力器缺乏CPU資源，假設(shè)任務(wù)頂峰時(shí)CPU運(yùn)用率依然很低，闡明效力器CPU資源還比較富余。

13、運(yùn)用操作一樣命令可以看到CPU的運(yùn)用情況，普通UNIX操作系統(tǒng)的效力器，可以運(yùn)用sar u命令查看CPU的運(yùn)用率，NT操作系統(tǒng)的效力器，可以運(yùn)用NT的性能管理器來(lái)查看CPU的運(yùn)用率。數(shù)據(jù)庫(kù)管理員可以經(jīng)過(guò)查看v$sysstat數(shù)據(jù)字典中“CPU used by this session統(tǒng)計(jì)項(xiàng)得知ORACLE數(shù)據(jù)庫(kù)運(yùn)用的CPU時(shí)間，查看“OS User level CPU time統(tǒng)計(jì)項(xiàng)得知操作系統(tǒng)用戶態(tài)下的CPU時(shí)間，查看“OS System call CPU time統(tǒng)計(jì)項(xiàng)得知操作系統(tǒng)系統(tǒng)態(tài)下的CPU時(shí)間，操作系統(tǒng)總的CPU時(shí)間就是用戶態(tài)和系統(tǒng)態(tài)時(shí)間之和，假設(shè)ORACLE數(shù)據(jù)庫(kù)運(yùn)用的CPU時(shí)間

14、占操作系統(tǒng)總的CPU時(shí)間90以上，闡明效力器CPU根本上被ORACLE數(shù)據(jù)庫(kù)運(yùn)用著，這是合理，反之，闡明效力器CPU被其它程序占用過(guò)多，ORACLE數(shù)據(jù)庫(kù)無(wú)法得到更多的CPU時(shí)間。數(shù)據(jù)庫(kù)管理員還可以經(jīng)過(guò)查看v$sesstat數(shù)據(jù)字典來(lái)獲得當(dāng)前銜接ORACLE數(shù)據(jù)庫(kù)各個(gè)會(huì)話占用的CPU時(shí)間，從而得知什么會(huì)話耗用效力器CPU比較多。出現(xiàn)CPU資源缺乏的情況是很多的：SQL語(yǔ)句的重解析、低效率的SQL語(yǔ)句、鎖沖突都會(huì)引起CPU資源缺乏。1、數(shù)據(jù)庫(kù)管理員可以執(zhí)行下述語(yǔ)句來(lái)查看SQL語(yǔ)句的解析情況：SELECT * FROM V$SYSSTATWHERE NAME IN (parse time cpu,

15、 parse time elapsed, parse count (hard);這里parse time cpu是系統(tǒng)效力時(shí)間，parse time elapsed是呼應(yīng)時(shí)間，用戶等待時(shí)間waite time = parse time elapsed parse time cpu由此可以得到用戶SQL語(yǔ)句平均解析等待時(shí)間waite time / parse count。這個(gè)平均等待時(shí)間應(yīng)該接近于0，假設(shè)平均解析等待時(shí)間過(guò)長(zhǎng)，數(shù)據(jù)庫(kù)管理員可以經(jīng)過(guò)下述語(yǔ)句SELECT SQL_TEXT, PARSE_CALLS, EXECUTIONS FROM V$SQLAREAORDER BY PARSE_CA

16、LLS;來(lái)發(fā)現(xiàn)是什么SQL語(yǔ)句解析效率比較低。程序員可以優(yōu)化這些語(yǔ)句，或者添加ORACLE參數(shù)SESSION_CACHED_CURSORS的值。2、數(shù)據(jù)庫(kù)管理員還可以經(jīng)過(guò)下述語(yǔ)句：SELECT BUFFER_GETS, EXECUTIONS, SQL_TEXT FROM V$SQLAREA;查看低效率的SQL語(yǔ)句，優(yōu)化這些語(yǔ)句也有助于提高CPU的利用率。3、數(shù)據(jù)庫(kù)管理員可以經(jīng)過(guò)v$system_event數(shù)據(jù)字典中的“l(fā)atch free統(tǒng)計(jì)項(xiàng)查看ORACLE數(shù)據(jù)庫(kù)的沖突情況，假設(shè)沒(méi)有沖突的話，latch free查詢出來(lái)沒(méi)有結(jié)果。假設(shè)沖突太大的話，數(shù)據(jù)庫(kù)管理員可以降低spin_count參數(shù)

17、值，來(lái)消除高的CPU運(yùn)用率。內(nèi)存參數(shù)的調(diào)整內(nèi)存參數(shù)的調(diào)整主要是指ORACLE數(shù)據(jù)庫(kù)的系統(tǒng)全局區(qū)SGA的調(diào)整。SGA主要由三部分構(gòu)成：共享池、數(shù)據(jù)緩沖區(qū)、日志緩沖區(qū)。1、 共享池由兩部分構(gòu)成：共享SQL區(qū)和數(shù)據(jù)字典緩沖區(qū)，共享SQL區(qū)是存放用戶SQL命令的區(qū)域，數(shù)據(jù)字典緩沖區(qū)存放數(shù)據(jù)庫(kù)運(yùn)轉(zhuǎn)的動(dòng)態(tài)信息。數(shù)據(jù)庫(kù)管理員經(jīng)過(guò)執(zhí)行下述語(yǔ)句：select (sum(pins - reloads) / sum(pins) Lib Cache from v$librarycache;來(lái)查看共享SQL區(qū)的運(yùn)用率。這個(gè)運(yùn)用率應(yīng)該在90以上，否那么需求添加共享池的大小。數(shù)據(jù)庫(kù)管理員還可以執(zhí)行下述語(yǔ)句：select (

18、sum(gets - getmisses - usage - fixed) / sum(gets) Row Cache from v$rowcache;查看數(shù)據(jù)字典緩沖區(qū)的運(yùn)用率，這個(gè)運(yùn)用率也應(yīng)該在90以上，否那么需求添加共享池的大小。2、 數(shù)據(jù)緩沖區(qū)。數(shù)據(jù)庫(kù)管理員可以經(jīng)過(guò)下述語(yǔ)句：SELECT name, value FROM v$sysstat WHERE name IN (db block gets, consistent gets,physical reads);來(lái)查看數(shù)據(jù)庫(kù)數(shù)據(jù)緩沖區(qū)的運(yùn)用情況。查詢出來(lái)的結(jié)果可以計(jì)算出來(lái)數(shù)據(jù)緩沖區(qū)的運(yùn)用命中率1 - ( physical reads / (db block gets + consistent gets) )。這個(gè)命中率應(yīng)該在90以上，否那么需求添加數(shù)據(jù)緩沖區(qū)