Oracle數(shù)據(jù)庫性能調(diào)優(yōu)課件

Oracle數(shù)據(jù)庫性能調(diào)優(yōu)課件第一頁，共133頁。引入復(fù)雜體系結(jié)構(gòu)的原因資源不足有效地使用稀有資源第二頁，共133頁。內(nèi)存與外存的比較第三頁，共133頁。總體上要優(yōu)化社么？性能優(yōu)化的領(lǐng)域包括：應(yīng)用系統(tǒng)優(yōu)化： # 糟糕的SQL語句 #資源的順序使用 #糟糕的會話管理實(shí)例優(yōu)化：#內(nèi)存#數(shù)據(jù)庫#實(shí)例的配置操作系統(tǒng)優(yōu)化：#輸入/輸出（I/O）#交換區(qū)（swap）#操作系統(tǒng)參數(shù)第四頁，共133頁。在實(shí)例中優(yōu)化什么？內(nèi)存：#內(nèi)存不足#糟糕的內(nèi)存分配輸入/輸出（I/O)#帶寬不足#糟糕的磁盤分配#糟糕的數(shù)據(jù)庫配置實(shí)例配置#不適當(dāng)?shù)膶?shí)例參數(shù)#糟糕的恢復(fù)和可獲得性配置第五頁，共133頁。優(yōu)化方法優(yōu)化步驟：從上往下優(yōu)化

#在優(yōu)化應(yīng)用代碼之前優(yōu)化設(shè)計(jì)#在優(yōu)化實(shí)例之前優(yōu)化代碼優(yōu)化潛在效益最大的區(qū)域#標(biāo)識最長的等待#標(biāo)識最大的服務(wù)時(shí)間達(dá)到目標(biāo)時(shí)即刻停止優(yōu)化第六頁，共133頁。定義問題發(fā)現(xiàn)并定義問題：傾聽用戶的反饋檢查報(bào)警和追蹤文件中的錯誤檢查參數(shù)文件以發(fā)現(xiàn)不適當(dāng)?shù)膮?shù)配置檢查內(nèi)存、I/O、CPU的使用，標(biāo)識資源使用異常的進(jìn)程標(biāo)識并優(yōu)化消耗大量CPU或I/O的SQL語句收集實(shí)例和操作系統(tǒng)的統(tǒng)計(jì)信息第七頁，共133頁。設(shè)定優(yōu)化的先后次序選擇沖擊最大的問題根據(jù)服務(wù)時(shí)間相對等待時(shí)間來分析系統(tǒng)的性能判斷哪個部件消耗了最多的時(shí)間如果可能，向下挖掘并優(yōu)化相應(yīng)的部件第八頁，共133頁。普遍的優(yōu)化問題最普遍的優(yōu)化問題如下：SQL語句會話（鏈接）管理共享池的大小和競爭數(shù)據(jù)庫緩沖區(qū)的大小和競爭數(shù)據(jù)塊的競爭重做日志文件和重做日志緩沖區(qū)的優(yōu)化還原（回滾）的優(yōu)化輸入/輸出（I/O）問題（issues）鎖的問題（issues）第九頁，共133頁。在整個生命周期中優(yōu)化的步驟優(yōu)化設(shè)計(jì)優(yōu)化應(yīng)用程序優(yōu)化內(nèi)存優(yōu)化輸入/輸出(I/O)優(yōu)化競爭優(yōu)化操作系統(tǒng)第十頁，共133頁。應(yīng)用系統(tǒng)的設(shè)計(jì)與開發(fā)應(yīng)用系統(tǒng)的優(yōu)化可以從設(shè)計(jì)和開發(fā)階段就開始：檢查主要功能的規(guī)范化（normalization）檢查與數(shù)據(jù)結(jié)構(gòu)相對應(yīng)的訪問時(shí)間仔細(xì)檢查順序處理的部分優(yōu)化主要的報(bào)表優(yōu)化數(shù)據(jù)量大的處理第十一頁，共133頁。收集一組基線的統(tǒng)計(jì)信息一組基線的統(tǒng)計(jì)信息被用做：提供了一組系統(tǒng)運(yùn)行在正常范圍之內(nèi)的統(tǒng)計(jì)信息將當(dāng)前的統(tǒng)計(jì)信息與基線統(tǒng)計(jì)信息進(jìn)行比較建立有關(guān)系統(tǒng)變化的假設(shè)第十二頁，共133頁。性能與安全之間的折衷影響性能的因素：控制文件的多重映像在一個重做日志組中的成員的多重映像檢查點(diǎn)的頻率數(shù)據(jù)文件的備份數(shù)據(jù)庫的歸檔數(shù)據(jù)塊的校驗(yàn)（checksums）并行的用戶數(shù)和并行的交易（事務(wù)）數(shù)第十三頁，共133頁。系統(tǒng)體系架構(gòu)優(yōu)化兩層架構(gòu)？#優(yōu)點(diǎn)#缺點(diǎn)三層（多層）架構(gòu)？#優(yōu)點(diǎn)#缺點(diǎn)第十四頁，共133頁。兩層架構(gòu)的優(yōu)化數(shù)據(jù)庫服務(wù)器客戶端應(yīng)用程序？應(yīng)用程序？第十五頁，共133頁。三層（多層）架構(gòu)的優(yōu)化數(shù)據(jù)庫服務(wù)器客戶端應(yīng)用程序？應(yīng)用程序？應(yīng)用服務(wù)器應(yīng)用程序？第十六頁，共133頁。邏輯（E-R模式）設(shè)計(jì)的優(yōu)化數(shù)據(jù)庫系統(tǒng)是聯(lián)機(jī)事務(wù)處理系統(tǒng)嗎？數(shù)據(jù)庫系統(tǒng)是數(shù)據(jù)倉庫（決策支持）系統(tǒng)嗎？第十七頁，共133頁。聯(lián)機(jī)事務(wù)處理（OLTP）系統(tǒng)高吞吐量，

大量的插入和修改操作數(shù)據(jù)量大而且是持續(xù)增長許多用戶并行訪問優(yōu)化目標(biāo)是保證:可獲得性高速型大量并行可恢復(fù)性第十八頁，共133頁。數(shù)據(jù)倉庫(決策支持）系統(tǒng)

頻繁的在大數(shù)據(jù)量上查詢頻繁的使用全表掃描優(yōu)化目標(biāo)是保證:快速的響應(yīng)時(shí)間重點(diǎn)放在SQL語句

的優(yōu)化上

并行查詢的特性主要是為數(shù)據(jù)倉庫環(huán)境設(shè)計(jì)的Data第十九頁，共133頁。兩種系統(tǒng)邏輯設(shè)計(jì)的主要差別聯(lián)機(jī)事務(wù)處理（OLTP)一般為三范式表的列數(shù)比較少盡可能少的數(shù)據(jù)冗余一般不存儲導(dǎo)出數(shù)據(jù)數(shù)據(jù)倉庫（DataWarehouse)經(jīng)常帶達(dá)不到三范式表的列數(shù)經(jīng)常很多為了提高效率經(jīng)常會有人為的數(shù)據(jù)冗余為了提高效率存儲常用的導(dǎo)出數(shù)據(jù)第二十頁，共133頁。事務(wù)處理與數(shù)據(jù)倉庫系統(tǒng)的比較第二十一頁，共133頁。利用數(shù)據(jù)字典獲取系統(tǒng)的設(shè)計(jì)第二十二頁，共133頁。設(shè)計(jì)的考慮標(biāo)識活動表（ActiveTables）活動表應(yīng)該設(shè)計(jì)的越小越好

第二十三頁，共133頁。前期優(yōu)化的方法簡單的設(shè)計(jì)（Simpledesign）數(shù)據(jù)模型（Datamodeling）仔細(xì)設(shè)計(jì)表和索引利用視圖寫高效率的SQL語句游標(biāo)（Cursor）共享使用綁定變量選擇性地使用SQL或PL/SQL動態(tài)SQL第二十四頁，共133頁。簡化應(yīng)用的設(shè)計(jì)簡單的表好的SQL語句只在需要時(shí)創(chuàng)建索引只提前那些所需要的信息第二十五頁，共133頁。數(shù)據(jù)模型（DataModeling）精確地表示業(yè)務(wù)流程（做法）將重點(diǎn)放在最常見和最重要的營業(yè)事項(xiàng)上使用模型工具將數(shù)據(jù)規(guī)范化第二十六頁，共133頁。表的設(shè)計(jì)在適應(yīng)性與性能之間進(jìn)行折衷以規(guī)范化為主選擇性地去規(guī)范化使用Oracle提高性能的特性默認(rèn)值檢查（Check）約束物化視圖（Materializedviews）聚類(Clusters)將注意力放在對業(yè)務(wù)至關(guān)重要的表上第二十七頁，共133頁。索引的設(shè)計(jì)索引關(guān)鍵字主鍵唯一鍵外鍵只在經(jīng)常查詢的數(shù)據(jù)上加索引利用SQL語句來指導(dǎo)索引的設(shè)計(jì)第二十八頁，共133頁。使用視圖簡化應(yīng)用的設(shè)計(jì)對終端用戶使透明的可能造成執(zhí)行計(jì)劃優(yōu)化的問題第二十九頁，共133頁。SQL語句執(zhí)行的效率好的數(shù)據(jù)庫連接使用游標(biāo)最小化編譯使用綁定變量第三十頁，共133頁。怎樣寫共享游標(biāo)的SQL語句？使用如下的方法創(chuàng)建通用的代碼:使用存儲過程和軟件包使用數(shù)據(jù)庫觸發(fā)器使用其它的庫例程和過程書寫的格式標(biāo)準(zhǔn):大小寫空白符注釋對象的引用綁定變量第三十一頁，共133頁?？刂乒蚕碛螛?biāo)ControllingSharedCursorsCURSOR_SHARING

初始化參數(shù)可以設(shè)置為:EXACT(默認(rèn))SIMILAR(不推薦)FORCE第三十二頁，共133頁。性能優(yōu)化的清單設(shè)置初始化參數(shù)和存儲選項(xiàng)。核實(shí)SQL語句的資源使用

。驗(yàn)證中間件的連接。驗(yàn)證游標(biāo)的關(guān)系。驗(yàn)證所需的所有對象的遷移。確認(rèn)優(yōu)化器所需的統(tǒng)計(jì)信息是有效的和可獲得的。第三十三頁，共133頁。執(zhí)行計(jì)劃是什么？一個執(zhí)行計(jì)劃是：

在執(zhí)行一條SQL語句和進(jìn)行相關(guān)的操作時(shí)，優(yōu)化器所執(zhí)行的一組步驟。第三十四頁，共133頁。觀察執(zhí)行計(jì)劃的方法EXPLAINPLAN

SQLTraceStatspackAutomaticWorkloadRepositoryV$SQL_PLANSQL*PlusAUTOTRACE第三十五頁，共133頁。使用執(zhí)行計(jì)劃決定當(dāng)前的執(zhí)行計(jì)劃標(biāo)識索引的效果絕對存取的路徑驗(yàn)證索引的使用核實(shí)哪一個執(zhí)行計(jì)劃可以使用第三十六頁，共133頁。EXPLAIN

PLAN

命令產(chǎn)生一個優(yōu)化器所使用的執(zhí)行計(jì)劃將該執(zhí)行計(jì)劃存儲在PLAN

表中并不執(zhí)行語句本身第三十七頁，共133頁。EXPLAIN

PLAN

命令的語法SETSTATEMENT_ID

='text'EXPLAINPLANINTOyourplantableFORstatement第三十八頁，共133頁。EXPLAINPLAN

命令:例子EXPLAINPLANSETSTATEMENT_ID='demo01'FORSELECTe.last_name,d.department_name

FROMhr.employeese,hr.departmentsdWHEREe.department_id=d.department_id;Explained.注意:EXPLAINPLAN

命令并不真正地執(zhí)行這一語句。第三十九頁，共133頁。EXPLAINPLAN

命令的輸出Planhashvalue:2933537672-------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU|--------------------------------------------------------------------------------|0|SELECTSTATEMENT||106|2862|6(17||1|MERGEJOIN||106|2862|6(17||2|TABLEACCESSBYINDEXROWID|DEPARTMENTS|27|432|2(0||3|INDEXFULLSCAN|DEPT_ID_PK|27||1(0||*4|SORTJOIN||107|1177|4(25||5|TABLEACCESSFULL|EMPLOYEES|107|1177|3(0|--------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------4-access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")18rowsselected.SELECTPLAN_TABLE_OUTPUTFROMTABLE(DBMS_XPLAN.DISPLAY());

第四十頁，共133頁。編譯樹（ParseTree）0SELECTSTATEMENTSORTJOIN12435MERGEJOINFULLTABLESCAN

of

EMPLOYEESTABLEACCESSBYINDEXROWID

of

DEPARTMENTSINDEXFULLSCANDEPT_ID_PK第四十一頁，共133頁。查詢

V$SQL_PLANSQL_ID47ju6102uvq5q,childnumber0-------------------------------------SELECTe.last_name,d.department_nameFROMhr.employeese,hr.departmentsdWHEREe.department_id=d.department_idPlanhashvalue:2933537672--------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU|--------------------------------------------------------------------------------|0|SELECTSTATEMENT||||6(100||1|MERGEJOIN||106|2862|6(17||2|TABLEACCESSBYINDEXROWID|DEPARTMENTS|27|432|2(0||3|INDEXFULLSCAN|DEPT_ID_PK|27||1(0||*4|SORTJOIN||107|1177|4(25||5|TABLEACCESSFULL|EMPLOYEES|107|1177|3(0|--------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------4-access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")filter("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")24rowsselected.SELECTPLAN_TABLE_OUTPUTFROM

TABLE(DBMS_XPLAN.DISPLAY_CURSOR('47ju6102uvq5q'));第四十二頁，共133頁。SQL*PlusAUTOTRACE語法OFFTRACE[ONLY]EXPLAINSTATISTICSSHOWAUTOTRACESETAUTOTRACEON第四十三頁，共133頁。SQL*PlusAUTOTRACE:

例子利用AUTOTRACE開始追蹤語句:隱藏語句的輸出:只顯示執(zhí)行計(jì)劃:控制列設(shè)置的布局setautotraceonsetautotracetraceonlysetautotracetraceonlyexplain第四十四頁，共133頁。SQL*PlusAUTOTRACE:

統(tǒng)計(jì)信息setautotracetraceonlystatistics

SELECT*FROMproducts;Statistics------------------------------------------------------1recursivecalls0dbblockgets9consistentgets3physicalreads0redosize15028bytessentviaSQL*Nettoclient556bytesreceivedviaSQL*Netfromclient6SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)72rowsprocessed第四十五頁，共133頁。SpfileTempDatafileUndoOracleDatabaseArchitecturePMONSMONRECOMMONMMNLPSP0MMANDBWnLGWRCKPTCJQ0S000D000QMNCQnnnFMONARCnCTWRRVWRFixed

sizeLarge

poolJava

poolStreams

poolDefault

buffer

cacheKeep

buffer

cacheRecycle

buffer

cachenK

buffer

cachesRedo

log

bufferASH

bufferSort

extent

poolGlobal

context

poolSGAFlash

back

bufferInstanceFlashback

logsRedolog

filesArchive

logfilesControl

filesSYSTEMSYSAUXChange

tracking

filePassword

fileShared

pool第四十六頁，共133頁。動態(tài)SGA特性實(shí)現(xiàn)了一種基礎(chǔ)結(jié)構(gòu)，這種結(jié)構(gòu)允許服務(wù)器在不關(guān)閉實(shí)例的情況下改變SGA的配置SGA的大?。ǔ叽纾┦芟抻趨?shù)

SGA_MAX_SIZE:被用作在實(shí)例啟動時(shí)預(yù)留的虛擬內(nèi)存不能動態(tài)地改變允許一些確定的SGA組成部分動態(tài)地調(diào)整大小。SELECTbytes

FROMV$SGAINFO

WHEREname='FreeSGAMemoryAvailable';第四十七頁，共133頁。自動共享內(nèi)存管理:概述使用自動SGA和內(nèi)存指導(dǎo)（advisors）來自動適應(yīng)工作量的變化最大程度地利用內(nèi)存幫助消除內(nèi)存耗盡

的錯誤當(dāng)使用SPFILE時(shí)，

可以避免再學(xué)習(xí)OnlineusersBatchjobsBuffercacheLargepoolSharedpoolJavapoolBuffercacheLargepoolSharedpoolJavapoolOnlineusersBatchjobsStreamspoolStreamspool第四十八頁，共133頁。SGA大小（尺寸）:概述使用自動共享內(nèi)存管理（ASMM），五個重要的SGA組件（部分）將會自動調(diào)整。非默認(rèn)的緩沖（池）區(qū)不能自動調(diào)整。日志緩沖區(qū)是不能動態(tài)地調(diào)整的，但是它有一個好的默認(rèn)值。DB_KEEP_CACHE_SIZEDB_RECYCLE_CACHE_SIZEDB_nK_CACHE_SIZELOG_BUFFER_SIZESHARED_POOL_SIZEDB_CACHE_SIZELARGE_POOL_SIZEJAVA_POOL_SIZESTREAMS_POOL_SIZE自動調(diào)整的參數(shù)手工動態(tài)參數(shù)手工靜態(tài)參數(shù)SGA_TARGETSGA_MAX_SIZE第四十九頁，共133頁。自動共享內(nèi)存管理的好處DB_CACHE_SIZESHARED_POOL_SIZELARGE_POOL_SIZEJAVA_POOL_SIZESTREAMS_POOL_SIZETotalSGAsizeSGA_TARGET第五十頁，共133頁。

內(nèi)存代理體系結(jié)構(gòu)StatisticdeltasacrossdifferenttimeperiodsCircularSGAbufferofstatscapturedbyMMONMemoryBrokerPolicyModuleAdd

twogranules

toshared

pool.Output:resizerequestsTrade-offdifferentcomponentsbenefit/lostMMANtransfers

thememory.resizequeueMMANMMON第五十一頁，共133頁。手工動態(tài)重新調(diào)整系統(tǒng)

全局區(qū)（SGA）

參數(shù)對自動調(diào)整參數(shù)，手動調(diào)整大小:如果新值大于當(dāng)前值，該組件（部分）的大小立即自動調(diào)整如果新值小于當(dāng)前值，則修改尺寸的最小值手動調(diào)整參數(shù)會影響SGA的可調(diào)整部分。第五十二頁，共133頁。自動調(diào)整SGA參數(shù)的行為

當(dāng)沒有設(shè)置SGA_TARGET，或者它的值被設(shè)置為0時(shí):要顯式地設(shè)置自動調(diào)整參數(shù)注意:SHARED_POOL_SIZE

包括了內(nèi)部啟動的開銷。與之前的版本比較，其值可能會需要增加當(dāng)設(shè)置了SGA_TARGET

時(shí):所有自動調(diào)整參數(shù)的默認(rèn)值被設(shè)為零:非零值為下邊界通過使用如下方法可以以MB來顯示當(dāng)前值:SELECTcomponent,current_size/1024/1024FROMV$SGA_DYNAMIC_COMPONENTS;第五十三頁，共133頁。手動調(diào)整SGA參數(shù)的行為手動調(diào)整的部分（組件）是:KEEP

和

RECYCLE

緩沖區(qū)（buffercaches）非默認(rèn)數(shù)據(jù)塊的內(nèi)存緩沖區(qū)LOG_BUFFER手動調(diào)整的部分（組件）由用戶說明。手工調(diào)整

SGA_TARGET中的組件可以精確地控制SGA的尺寸。第五十四頁，共133頁。使用V$PARAMETER

視圖SGA_TARGET=8GSELECTname,value,isdefaultFROMV$PARAMETERWHEREnameLIKE'%size';DB_CACHE_SIZE=0JAVA_POOL_SIZE=0LARGE_POOL_SIZE=0SHARED_POOL_SIZE=0STREAMS_POOL_SIZE=0第五十五頁，共133頁。手動重新調(diào)整

SGA_TARGETSGA_TARGET初始化參數(shù):是動態(tài)的可以增加到最大為

SGA_MAX_SIZE當(dāng)所有的部分達(dá)到最小值時(shí)可以減少其大小這些變化只影響自動調(diào)整部分（組件）SGA中所包含的部分:固定SGA部分和其它內(nèi)部分配部分自動調(diào)整SGA部分手動SGA部分允許Oracle服務(wù)器精確地分配總的共享內(nèi)存的尺寸第五十六頁，共133頁。SGAsize=8GB關(guān)閉自動共享內(nèi)存管理將

SGA_TARGET

設(shè)置為零改變自動調(diào)整功能。將自動調(diào)整參數(shù)設(shè)置為她們的當(dāng)前值。SGA的大小作為整體不受影響，Parameters:SGA_TARGET=8GSHARED_POOL_SIZE=1GParameters:SGA_TARGET=0DB_CACHE_SIZE=4GSHARED_POOL_SIZE=1.5GLARGE_POOL_SIZE=512MJAVA_POOL_SIZE=512MSTREAMS_POOL_SIZE=512MSGAsize=8GBOriginalvaluesSGA_TARGET=0第五十七頁，共133頁。SGA指導(dǎo)（Advisor）第五十八頁，共133頁。設(shè)定共享池的尺寸使用自動共享內(nèi)存管理。當(dāng)數(shù)據(jù)有操作歷史時(shí)，使用共享池指導(dǎo)（Advisor）來確認(rèn)其它工具的診斷結(jié)果。當(dāng)沒有歷史數(shù)據(jù)時(shí)，使用40%的SGA作為起點(diǎn)。監(jiān)督并在需要時(shí)進(jìn)行調(diào)整。當(dāng)有可獲得的空閑內(nèi)存時(shí)，不用減少共享池的尺寸。SQL>SELECT*FROMV$SGASTAT2WHERENAME='freememory'3ANDPOOL='sharedpool';第五十九頁，共133頁。共享池指導(dǎo)（Advisory）SQL>SELECTshared_pool_size_for_estimateAS2pool_size,estd_lc_size,3estd_lc_time_saved4FROMV$SHARED_POOL_ADVICE;POOL_SIZEESTD_LC_SIZEESTD_LC_TIME_SAVED----------------------------------------328786840157868481778685617786864177868721778688017786888177868第六十頁，共133頁。動態(tài)數(shù)據(jù)緩沖區(qū)指導(dǎo)（Advisory）參數(shù)數(shù)據(jù)緩沖區(qū)指導(dǎo)（advisory）特性可以開啟和關(guān)閉收集預(yù)測不同緩沖區(qū)大小對系統(tǒng)性能影響的統(tǒng)計(jì)信息。使用這些統(tǒng)計(jì)所提供的信息來設(shè)定緩沖區(qū)的大小以優(yōu)化某一指導(dǎo)的工作量

。通過使用

DB_CACHE_ADVICE初始化參數(shù)來開啟數(shù)據(jù)緩沖區(qū)指導(dǎo)（advisory）:這一參數(shù)是動態(tài)的，它可以通過

ALTER

SYSTEM

命令來修改。該參數(shù)可以設(shè)定為以下三個值:OFF,ON，和READY。第六十一頁，共133頁。觀察數(shù)據(jù)緩沖區(qū)指導(dǎo)（Advisory）數(shù)據(jù)緩沖區(qū)指導(dǎo)（advisory）的信息被收集在V$DB_CACHE_SIZE

視圖中。該視圖包含了不同的數(shù)據(jù)行，這些數(shù)據(jù)行是從當(dāng)前緩沖區(qū)尺寸的10%到200%之間的不同緩沖區(qū)大小所對應(yīng)的物理讀的估算值。第六十二頁，共133頁。使用V$DB_CACHE_ADVICE

視圖SELECTsize_for_estimate,buffers_for_estimate,estd_physical_read_factor,estd_physical_readsFROMV$DB_CACHE_ADVICEWHEREname='DEFAULT'ANDadvice_status='ON'ANDblock_size=(SELECTvalueFROMV$PARAMETERWHEREname='db_block_size');

EstdPhysEstdPhysCacheSize(MB)BuffersReadFactorReads---------------------------------------------------(10%)303,80218.70192,317,943…24330,4161.3313,720,14927334,2181.1311,583,180(Current)30438,0201.0010,282,47533441,822.939,515,878…57772,238.676,895,122(200%)60876,040.666,739,731第六十三頁，共133頁。以企業(yè)管理器使用數(shù)據(jù)緩沖區(qū)指導(dǎo)（Advisory）第六十四頁，共133頁。共享池指導(dǎo)（Advisory）第六十五頁，共133頁。將查詢的結(jié)果緩存在內(nèi)存Oracle11g可將查詢的結(jié)果常駐內(nèi)存只要緩存的查詢結(jié)果是有效的，其他的語句和會話就可以共享它們第六十六頁，共133頁。Oracle11g

內(nèi)存參數(shù)第六十七頁，共133頁。使用索引指南只在需要時(shí)創(chuàng)建索引。為優(yōu)化某一語句所創(chuàng)建是索引可能會影響其它的一些語句。最好是刪除沒用的索引可以使用EXPLAIN

PLAN

來決定一個索引是否被優(yōu)化器所使用。第六十八頁，共133頁。索引的類型唯一和非唯一的索引復(fù)合索引索引存儲技術(shù):B*-tree–

正常（Normal）–

反轉(zhuǎn)關(guān)鍵字（Reversekey）–

遞減（Descending)–

基于函數(shù)（Functionbased）位圖（Bitmap）域索引（Domainindexes）關(guān)鍵字壓縮（Keycompression）第六十九頁，共133頁。什么時(shí)候使用索引IndexDoNotIndexKeysfrequentlyusedinsearchorqueryexpressionsKeysandexpressionswithfewdistinctvaluesexceptbitmapindexesindatawarehousingKeysusedtojointablesFrequentlyupdatedcolumnsHigh-selectivitykeysColumnsusedonlywith

functionsorexpressionsunlesscreatingfunction-basedindexesForeignkeysColumnsbasedonlyonquery

performance第七十頁，共133頁。DML操作對索引的影響插入操作會造成對相應(yīng)的（索引）塊進(jìn)行索引項(xiàng)的插入（可能會造成索引塊的分裂）。刪除數(shù)據(jù)行會造成索引項(xiàng)的刪除修改索引關(guān)鍵字列造成對索引的邏輯刪除和插入。第七十一頁，共133頁。索引與外鍵（ForeignKeys）并不自動創(chuàng)建索引。automatically.在主表（父表）上進(jìn)行DML操作會隱含地上鎖。CUSTOMERS#cust_idSALESPRODUCTS#prod_idCHANNELS#channel_id第七十二頁，共133頁。標(biāo)識沒用的索引Oracle數(shù)據(jù)庫提供了收集某一個索引使用情況的統(tǒng)計(jì)信息的能力。:避免空間的浪費(fèi)通過消除在DML操作期間不必要的額外開銷來改進(jìn)系統(tǒng)的性能。第七十三頁，共133頁。開啟和關(guān)閉索引使用的監(jiān)督開始監(jiān)督索一個引的使用:停止監(jiān)督索一個引的使用:V$OBJECT_USAGE

視圖包含了有關(guān)一個索引使用的信息ALTERINDEXcustomers_pkMONITORINGUSAGE;ALTERINDEXcustomers_pkNOMONITORINGUSAGE;第七十四頁，共133頁。INVISIBLE

Index不可見索引概述VISIBLE

IndexOptimizerviewpointDataviewpoint使用索引不使用索引修改索引修改索引修改表修改表OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE第七十五頁，共133頁。不可見索引的例子將索引更改為優(yōu)化器不可見（即不使用）:優(yōu)化器不會考慮以下的索引:優(yōu)化器將總會考慮以下的索引:創(chuàng)建一個不可見的索引:ALTERINDEXdog_name_idx

INVISIBLE;SELECT/*+index(TABn.INDXn)*/COLiFROMTABnWHERE…;ALTERINDEXdog_name_idxVISIBLE;CREATEINDEXdog_age_idxONdog(age)INVISIBLE;第七十六頁，共133頁。復(fù)合索引（CompositeIndexes）復(fù)合索引的一些特性如下。索引的前導(dǎo)列在列組合中:cust_last_name

cust_last_namecust_first_namecust_last_name

cust_first_name

cust_gender

索引的前導(dǎo)列不在列組合中:cust_first_name

cust_gendercust_first_namecust_genderCREATEINDEXcust_last_first_gender_idx

ONcustomers(cust_last_name,cust_first_name,cust_gender);第七十七頁，共133頁。復(fù)合索引指南在WHERE子句中經(jīng)常一起使用的那些鍵（列）上創(chuàng)建復(fù)合索引。創(chuàng)建復(fù)合索引以便WHERE子句中所使用的鍵組成前導(dǎo)部分。將哪些最經(jīng)常使用的查詢列放入復(fù)合索引的前導(dǎo)部分(leadingpart)。將哪些限制最多的列放入復(fù)合索引的前導(dǎo)部分(leadingpart)。第七十八頁，共133頁。BitmapIndex與正常的B*-tree索引相比，位圖索引更快并且使用更少的空間。每個位圖索引都是由一些被稱為位圖的小存儲區(qū)域所組成。每個位圖包含了每個索引列的特定值的信息。位圖是以B*-tree結(jié)構(gòu)存儲，但在存儲之前先進(jìn)行了壓縮。第七十九頁，共133頁。B-樹索引IndexentryheaderKeycolumnlengthKeycolumnvalueROWIDRootBranchLeafIndexentry第八十頁，共133頁。位圖索引第八十一頁，共133頁。B-樹索引和位圖索引的比較B-樹（B-tree） 適合于high-cardinality

列對關(guān)建字列的修改相對不

算昂貴使用謂詞OR/AND查詢效率不高

用于OLTP位圖（Bitmap）適合于low-cardinality

列對關(guān)建字列的修改非常昂貴使用謂詞OR/AND查詢效率高

用于datawarehousing（DSS）第八十二頁，共133頁。創(chuàng)建B-樹索引CREATEINDEXhr.employees_last_name_idxONhr.employees(last_name)PCTFREE30STORAGE(INITIAL200KNEXT200K PCTINCREASE0 MAXEXTENTS50)TABLESPACEindx;第八十三頁，共133頁。創(chuàng)建B-樹索引CREATEINDEXhr.employees_last_name_idxONhr.employees(last_name)PCTFREE30STORAGE(INITIAL200KNEXT200K PCTINCREASE0 MAXEXTENTS50)TABLESPACEindx;第八十四頁，共133頁。位圖索引的優(yōu)點(diǎn)當(dāng)使用適當(dāng)時(shí)，位圖索引將提供:減少許多專門查詢的響應(yīng)時(shí)間與其它的索引技術(shù)相比，將節(jié)省數(shù)量可觀的磁盤空間極大地提高性能第八十五頁，共133頁。位圖索引指南通過以下措施來減少位圖的存儲空間:盡可能將索引列聲明為非空（

NOT

NULL）盡可能將數(shù)據(jù)聲明為定長類型使用如下命令:

ALTERTABLE…MINIMIZERECORDS_PER_BLOCK通過增加PGA_AGGREGATE_TARGET的值來提高位圖索引的性能。第八十六頁，共133頁。位圖連接（BitmapJoin）索引SalesCustomersCREATEBITMAPINDEXcust_sales_bjiONsales(c.cust_city)FROMsaless,customerscWHEREc.cust_id=s.cust_id;第八十七頁，共133頁。位圖連接（BitmapJoin）索引不需要與CUSTOMERS表的連接。在執(zhí)行如下的查詢語句時(shí)，僅僅使用了位圖連接索引和

SALES表:54SELECTSUM(s.amount_sold)FROMsaless,customerscWHEREs.cust_id=c.cust_idANDc.cust_city='Sully';第八十八頁，共133頁。位圖連接索引的優(yōu)缺點(diǎn)優(yōu)點(diǎn)對連接查詢性能非常好，而且節(jié)省空間對星型模型中大的維表非常有用缺點(diǎn)需要更多的索引:可以達(dá)到維表的每個列一個索引，而不是

每個維表一個索引。維護(hù)成本較高:建立或刷新位圖連接索引時(shí)，需要進(jìn)行連接。第八十九頁，共133頁?；诤瘮?shù)的索引CREATEINDEXFBI_UPPER_LASTNAMEONCUSTOMERS(upper(cust_last_name));SELECT*

FROMcustomersWHEREUPPER(cust_last_name)='SMITH';ALTERSESSION

SETQUERY_REWRITE_ENABLED=TRUE;第九十頁，共133頁?；诤瘮?shù)的索引:使用基于函數(shù)的索引:適用于計(jì)算密集型的表達(dá)式方便與大小寫無關(guān)的搜尋提供了數(shù)據(jù)壓縮的簡單方式可以用于NLS排序索引第九十一頁，共133頁。KEYROWID------------------------12570000000F.0002.000128770000000F.0006.000145670000000F.0004.000166570000000F.0003.000189670000000F.0005.000196370000000F.0001.000199470000000F.0000.0001... ...EMPLOYEE_IDLAST_NAME...--------------------7499ALLEN7369SMITH7521WARD...7566JONES7654MARTIN7698BLAKE7782CLARK.........反轉(zhuǎn)關(guān)鍵字（ReverseKey）索引Indexonemployee_idcolumnemployeestable第九十二頁，共133頁。創(chuàng)建反轉(zhuǎn)關(guān)鍵字索引創(chuàng)建一個反轉(zhuǎn)關(guān)鍵字的唯一索引:創(chuàng)建一個唯一索引，然后將它改為反轉(zhuǎn)關(guān)鍵字索引:SQL>CREATEUNIQUEINDEXi1_t1ONt1(c1)2REVERSEPCTFREE303STORAGE(INITIAL200KNEXT200K4PCTINCREASE0MAXEXTENTS50)5TABLESPACEindx;SQL>CREATEUNIQUEINDEXi2_t1ONt1(c2);SQL>ALTERINDEXi2_t1REBUILDREVERSE;第九十三頁，共133頁。IndexedaccessontableROWID索引表（Index-OrganizedTables）:概述Accessingindex-organizedtableRowheaderNon-keycolumnsKeycolumn第九十四頁，共133頁。索引表的特點(diǎn)索引表（Index-organizedtables）:必須有主鍵（primarykey）不能包含LONG類型的列可以重建可以通過主鍵或前導(dǎo)列（leadingcolumns）存取第九十五頁，共133頁。索引表（IOTs）的優(yōu)缺點(diǎn)優(yōu)點(diǎn)（Advantages）

對于涉及精確匹配和范圍搜尋，IOTs提供了

基于使用關(guān)鍵字的快速存取。DML操作僅僅造成索引結(jié)構(gòu)的修改。存儲空間的需求減少。IOTs在如下的情況下很有用:–

應(yīng)用程序是基于主鍵來檢索數(shù)據(jù)的–

涉及基于目錄信息的應(yīng)用程序缺點(diǎn)（Disadvantages）

不適合于在判斷中沒有使用主鍵的查詢第九十六頁，共133頁。創(chuàng)建索引表SQL>CREATETABLEcountry2(country_idCHAR(2)3CONSTRAINTcountry_id_nnNOTNULL,4country_nameVARCHAR2(40),5currency_nameVARCHAR2(25),6currency_symbolVARCHAR2(3),7mapBLOB,8flagBLOB,9CONSTRAINTcountry_c_id_pk10PRIMARYKEY(country_id))11ORGANIZATIONINDEX12TABLESPACEindx13PCTTHRESHOLD2014OVERFLOWTABLESPACEusers;第九十七頁，共133頁。Segment=SYS_IOT_OVER_nIOT_type=IOT_OVERFLOWSegment_type=TABLESegment=COUNTRY_C_ID_PKIOT_type=IOTSegment_type=INDEXIndex_type=IOT-TOP索引表（IOT）行溢出（Overflow）RemainingpartoftherowRowswithinPCTTHRESHOLDindxtablespaceuserstablespace第九十八頁，共133頁。從dba_tables

查詢IOT的信息SQL>SELECTtable_name,iot_name,iot_type2FROMdba_tables;TABLE_NAMEIOT_NAMEIOT_TYPE-------------------------------------COUNTRYIOTSYS_IOT_OVER_2268COUNTRYIOT_OVERFLOW第九十九頁，共133頁。從dba_indexes

和dba_segments中查詢IOT的信息SQL>SELECTindex_name,index_type,2tablespace_name,table_name2FROMdba_indexes;INDEX_NAMEINDEX_TYPETABLESPACETABLE_NAME---------------------------------------------COUNTRY_C_ID_PKIOT-TOPINDXCOUNTRYSQL>SELECTsegment_name,tablespace_name,2segment_type3FROMdba_segments;SEGMENT_NAME TABLESPACE_NAMESEGMENT_TYPE----------------- ---------------------------SYS_IOT_OVER_2268USERTABLECOUNTRY_C_ID_PKINDXINDEX第一百頁，共133頁。進(jìn)程對文件的I/O操作第一百零一頁，共133頁。進(jìn)行I/O優(yōu)化的基本原則進(jìn)行I/O優(yōu)化的基本原則如下：盡量減少硬盤I/O操作將磁盤的I/O操作平衡（均勻）地分布到所有的硬盤和I/O控制器上使用合適的表空間第一百零二頁，共133頁。將數(shù)據(jù)分布到不同的硬盤上數(shù)據(jù)文件與重做日志文件放在不同的硬盤上歸檔日志文件與重做日志文件放在不同的硬盤上不同的重做日志（成員）文件放在不同的硬盤上不同的控制文件最好也分別放在不同的硬盤上不同的歸檔日志文件最好也放在不同的硬盤上不同表空間對應(yīng)的文件分別放在不同的硬盤上不同類型的數(shù)據(jù)存放在不同的表空間中減少與數(shù)據(jù)庫無關(guān)操作的磁盤I/O第一百零三頁，共133頁。表空間的使用system表空間只存sys用戶的數(shù)據(jù)字典對象使用本地管理表空間以避免磁盤空間管理問題表和索引應(yīng)該分別存放在不同的表空間中創(chuàng)建一個或多個單獨(dú)存放回滾段的還原表空間大對象（LOB）應(yīng)放在單獨(dú)的LOB表空間創(chuàng)建一個或多個臨時(shí)表空間第一百零四頁，共133頁。檢查I/O統(tǒng)計(jì)信息診斷工具ServerI/OutilizationSystemI/Outilization數(shù)據(jù)文件優(yōu)化工具v$v$tempstatv$datafile第一百零五頁，共133頁。使用v$視圖SQL>SELECTphyrds,phywrts,2FROMv$datafiled,v$f3WHEREd.4ORDERBY;PHYRDSPHYWRTSNAME--------------------------------------806116/…/u01/system01.dbf168675/…/u04/temp01.dbf88/…/u02/sample01.dbf26257/…/u02/undots01.dbf65012564/…/u03/users01.dbf88/…/u01/query01.dbf6rowsselected第一百零六頁，共133頁。輸入/輸出統(tǒng)計(jì)信息SQL>SELECTd.tablespace_nameTABLESPACE,2d.,f.phyrds,f.phywrts3FROMv$f,dba_data_filesd4WHEREf.file#=d.;TABLESPACEPHYRDSPHYWRTS-------------------------------------------UNDO1/u02/undots01.dbf26257SAMPLE/u02/sample01.dbf65012564USERS/u03/users01.dbf88SYSTEM/u01/system01.dbf806116TEMP/u04/temp01.dbf168675QUERY_DATA/u01/query01.dbf886rowsselected.第一百零七頁，共133頁?？臻g管理空間管理有三個層次:文件(OS,ASM,裸分區(qū))將磁盤空間分配給表空間。.區(qū)段被用來將文件的磁盤空間分配給表空間中的段。數(shù)據(jù)塊被用于在數(shù)據(jù)對象內(nèi)部組織磁盤空間。第一百零八頁，共133頁。區(qū)段（Extent）管理區(qū)段以兩種方式分配:數(shù)據(jù)字典管理僅僅為了支持向前兼容區(qū)段在

EXT$

和

FET$

數(shù)據(jù)字典表中管理使用遞歸（Recursive）SQL語句管理

本地管理區(qū)段在文件頭的位圖中管理在區(qū)段的操作中不產(chǎn)生還原數(shù)據(jù)有可能造成文件頭數(shù)據(jù)塊的競爭第一百零九頁，共133頁。本地管理的區(qū)段創(chuàng)建本地管理的表空間:默認(rèn)區(qū)段的管理為本的的。SQL>CREATETABLESPACEuser_data_12DATAFILE3‘/oracle11goradata/db1/lm_1.dbf’4SIZE100M5EXTENTMANAGEMENTLOCAL6UNIFORMSIZE2M;第一百一十頁，共133頁。大區(qū)段的贊成與反對的理由贊成（Pros）:動態(tài)擴(kuò)展到可能較少可以改進(jìn)一些性能能夠使服務(wù)器進(jìn)程以一次I/O讀入整個區(qū)段的位圖反對（Cons）:空閑磁盤空間可能無法獲得可能會包含沒用的磁盤空間第一百一十一頁，共133頁。將

SYSTEM

表空間遷移為本地管理的表空間

使用

DBMS_SPACE_ADMIN

軟件包:

遷移過程對本地管理的系統(tǒng)（SYSTEM）表空間強(qiáng)加了一些限制。只有數(shù)據(jù)庫系統(tǒng)是運(yùn)行在限制（RESTRICTED）模式并且除了系統(tǒng)表空間、還原表空間、和臨時(shí)表空間之外的所有表空間都處在只讀（READ

ONLY）模式時(shí)，遷移才有可能。一定要在系統(tǒng)表空間遷移之前將其它需要轉(zhuǎn)換的數(shù)據(jù)字典管理的表空間進(jìn)行遷移。SQL>EXECUTEDBMS_SPACE_ADMIN.-

2TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM');第一百一十二頁，共133頁。表是如何存儲的表空間（Tablespace）TableATableBSegmentSegmentRowsColumnsTableBlocksRowpiece區(qū)段（Extent）第一百一十三頁，共133頁。數(shù)據(jù)塊的解剖（Anatomy）BlockheaderFreespaceRowdataGrowth第一百一十四頁，共133頁。最小化數(shù)據(jù)塊的訪問通過一下方法最小化數(shù)據(jù)塊的訪問:

使用較大的塊尺寸

數(shù)據(jù)行存放的緊湊（Packingrowstightly）

防止數(shù)據(jù)行的遷移TablespaceBlocksSegmentsExtents第一百一十五頁，共133頁。DB_BLOCK_SIZE

參數(shù)數(shù)據(jù)塊的尺寸（大?。?由

DB_BLOCK_SIZE參數(shù)所定義是在創(chuàng)建數(shù)據(jù)庫時(shí)設(shè)置將成為表空間和內(nèi)存緩沖區(qū)默認(rèn)數(shù)據(jù)塊的尺寸是數(shù)據(jù)文件讀操作的最小I/O單位默認(rèn)值為8KB；對于絕大多數(shù)IT平臺最大可達(dá)32KB不能輕易改變應(yīng)該為操作系統(tǒng)塊尺寸的整數(shù)倍第一百一十六頁，共133頁。小數(shù)據(jù)塊尺寸的考慮好處（Advantages）:減少塊的競爭非常適合小的數(shù)據(jù)行非常適合隨機(jī)訪問短處（Disadvantages）:具有較大的磁盤空間的額外開銷Hasarelativelylarge