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

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

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

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

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

的優(yōu)化上

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

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

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

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

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

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

PLAN

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

表中并不執(zhí)行語(yǔ)句本身EXPLAIN

PLAN

命令的語(yǔ)法SETSTATEMENT_ID

='text'EXPLAINPLANINTOyourplantableFORstatementEXPLAINPLAN

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

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

命令并不真正地執(zhí)行這一語(yǔ)句。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());

編譯樹(shù)（ParseTree）0SELECTSTATEMENTSORTJOIN12435MERGEJOINFULLTABLESCAN

of

EMPLOYEESTABLEACCESSBYINDEXROWID

of

DEPARTMENTSINDEXFULLSCANDEPT_ID_PK查詢

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'));SQL*PlusAUTOTRACE語(yǔ)法OFFTRACE[ONLY]EXPLAINSTATISTICSSHOWAUTOTRACESETAUTOTRACEONSQL*PlusAUTOTRACE:

例子利用AUTOTRACE開(kāi)始追蹤語(yǔ)句:隱藏語(yǔ)句的輸出:只顯示執(zhí)行計(jì)劃:控制列設(shè)置的布局setautotraceonsetautotracetraceonlysetautotracetraceonlyexplainSQL*PlusAUTOTRACE:

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

SELECT*FROMproducts;Statistics------------------------------------------------------1recursivecalls0dbblockgets9consistentgets3physicalreads0redosize15028bytessentviaSQL*Nettoclient556bytesreceivedviaSQL*Netfromclient6SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)72rowsprocessedSpfileTempDatafileUndoOracleDatabaseArchitecturePMONSMONRECOMMONMMNLPSP0MMANDBWnLGWRCKPTCJQ0S000D000QMNCQnnnFMONARCnCTWRRVWRFixed

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動(dòng)態(tài)SGA特性實(shí)現(xiàn)了一種基礎(chǔ)結(jié)構(gòu)，這種結(jié)構(gòu)允許服務(wù)器在不關(guān)閉實(shí)例的情況下改變SGA的配置SGA的大?。ǔ叽纾┦芟抻趨?shù)

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

FROMV$SGAINFO

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

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

可以避免再學(xué)習(xí)OnlineusersBatchjobsBuffercacheLargepoolSharedpoolJavapoolBuffercacheLargepoolSharedpoolJavapoolOnlineusersBatchjobsStreamspoolStreamspoolSGA大?。ǔ叽纾?概述使用自動(dòng)共享內(nèi)存管理（ASMM），五個(gè)重要的SGA組件（部分）將會(huì)自動(dòng)調(diào)整。非默認(rèn)的緩沖（池）區(qū)不能自動(dòng)調(diào)整。日志緩沖區(qū)是不能動(dòng)態(tài)地調(diào)整的，但是它有一個(gè)好的默認(rèn)值。DB_KEEP_CACHE_SIZEDB_RECYCLE_CACHE_SIZEDB_nK_CACHE_SIZELOG_BUFFER_SIZESHARED_POOL_SIZEDB_CACHE_SIZELARGE_POOL_SIZEJAVA_POOL_SIZESTREAMS_POOL_SIZE自動(dòng)調(diào)整的參數(shù)手工動(dòng)態(tài)參數(shù)手工靜態(tài)參數(shù)SGA_TARGETSGA_MAX_SIZE自動(dòng)共享內(nèi)存管理的好處DB_CACHE_SIZESHARED_POOL_SIZELARGE_POOL_SIZEJAVA_POOL_SIZESTREAMS_POOL_SIZETotalSGAsizeSGA_TARGET

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

twogranules

toshared

pool.Output:resizerequestsTrade-offdifferentcomponentsbenefit/lostMMANtransfers

thememory.resizequeueMMANMMON手工動(dòng)態(tài)重新調(diào)整系統(tǒng)

全局區(qū)（SGA）

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

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

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

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

和

RECYCLE

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

SGA_TARGET中的組件可以精確地控制SGA的尺寸。使用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手動(dòng)重新調(diào)整

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

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

SGA_TARGET

設(shè)置為零改變自動(dòng)調(diào)整功能。將自動(dòng)調(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=0SGA指導(dǎo)（Advisor）設(shè)定共享池的尺寸使用自動(dòng)共享內(nèi)存管理。當(dāng)數(shù)據(jù)有操作歷史時(shí)，使用共享池指導(dǎo)（Advisor）來(lái)確認(rèn)其它工具的診斷結(jié)果。當(dāng)沒(méi)有歷史數(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';共享池指導(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動(dòng)態(tài)數(shù)據(jù)緩沖區(qū)指導(dǎo)（Advisory）參數(shù)數(shù)據(jù)緩沖區(qū)指導(dǎo)（advisory）特性可以開(kāi)啟和關(guān)閉收集預(yù)測(cè)不同緩沖區(qū)大小對(duì)系統(tǒng)性能影響的統(tǒng)計(jì)信息。使用這些統(tǒng)計(jì)所提供的信息來(lái)設(shè)定緩沖區(qū)的大小以優(yōu)化某一指導(dǎo)的工作量

。通過(guò)使用

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

ALTER

SYSTEM

命令來(lái)修改。該參數(shù)可以設(shè)定為以下三個(gè)值:OFF,ON，和READY。觀察數(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ū)大小所對(duì)應(yīng)的物理讀的估算值。使用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以企業(yè)管理器使用數(shù)據(jù)緩沖區(qū)指導(dǎo)（Advisory）共享池指導(dǎo)（Advisory）將查詢的結(jié)果緩存在內(nèi)存Oracle11g可將查詢的結(jié)果常駐內(nèi)存只要緩存的查詢結(jié)果是有效的，其他的語(yǔ)句和會(huì)話就可以共享它們Oracle11g

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

PLAN

來(lái)決定一個(gè)索引是否被優(yōu)化器所使用。索引的類型唯一和非唯一的索引復(fù)合索引索引存儲(chǔ)技術(shù):B*-tree–

正常（Normal）–

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

遞減（Descending)–

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

functionsorexpressionsunlesscreatingfunction-basedindexesForeignkeysColumnsbasedonlyonquery

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

視圖包含了有關(guān)一個(gè)索引使用的信息ALTERINDEXcustomers_pkMONITORINGUSAGE;ALTERINDEXcustomers_pkNOMONITORINGUSAGE;INVISIBLE

Index不可見(jiàn)索引概述VISIBLE

IndexOptimizerviewpointDataviewpoint使用索引不使用索引修改索引修改索引修改表修改表OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE不可見(jiàn)索引的例子將索引更改為優(yōu)化器不可見(jiàn)（即不使用）:優(yōu)化器不會(huì)考慮以下的索引:優(yōu)化器將總會(huì)考慮以下的索引:創(chuàng)建一個(gè)不可見(jiàn)的索引:ALTERINDEXdog_name_idx

INVISIBLE;SELECT/*+index(TABn.INDXn)*/COLiFROMTABnWHERE…;ALTERINDEXdog_name_idxVISIBLE;CREATEINDEXdog_age_idxONdog(age)INVISIBLE;復(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);復(fù)合索引指南在WHERE子句中經(jīng)常一起使用的那些鍵（列）上創(chuàng)建復(fù)合索引。創(chuàng)建復(fù)合索引以便WHERE子句中所使用的鍵組成前導(dǎo)部分。將哪些最經(jīng)常使用的查詢列放入復(fù)合索引的前導(dǎo)部分(leadingpart)。將哪些限制最多的列放入復(fù)合索引的前導(dǎo)部分(leadingpart)。BitmapIndex與正常的B*-tree索引相比，位圖索引更快并且使用更少的空間。每個(gè)位圖索引都是由一些被稱為位圖的小存儲(chǔ)區(qū)域所組成。每個(gè)位圖包含了每個(gè)索引列的特定值的信息。位圖是以B*-tree結(jié)構(gòu)存儲(chǔ)，但在存儲(chǔ)之前先進(jìn)行了壓縮。B-樹(shù)索引IndexentryheaderKeycolumnlengthKeycolumnvalueROWIDRootBranchLeafIndexentry位圖索引B-樹(shù)索引和位圖索引的比較B-樹(shù)（B-tree） 適合于high-cardinality

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

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

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

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

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

NOT

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

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

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

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

FROMcustomersWHEREUPPER(cust_last_name)='SMITH';ALTERSESSION

SETQUERY_REWRITE_ENABLED=TRUE;基于函數(shù)的索引:使用基于函數(shù)的索引:適用于計(jì)算密集型的表達(dá)式方便與大小寫(xiě)無(wú)關(guān)的搜尋提供了數(shù)據(jù)壓縮的簡(jiǎn)單方式可以用于NLS排序索引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創(chuàng)建反轉(zhuǎn)關(guān)鍵字索引創(chuàng)建一個(gè)反轉(zhuǎn)關(guān)鍵字的唯一索引:創(chuàng)建一個(gè)唯一索引，然后將它改為反轉(zhuǎn)關(guān)鍵字索引:SQL>CREATEUNIQUEINDEXi1_t1ONt1(c1)2REVERSEPCTFREE303STORAGE(INITIAL200KNEXT200K4PCTINCREASE0MAXEXTENTS50)5TABLESPACEindx;SQL>CREATEUNIQUEINDEXi2_t1ONt1(c2);SQL>ALTERINDEXi2_t1REBUILDREVERSE;IndexedaccessontableROWID索引表（Index-OrganizedTables）:概述Accessingindex-organizedtableRowheaderNon-keycolumnsKeycolumn索引表的特點(diǎn)索引表（Index-organizedtables）:必須有主鍵（primarykey）不能包含LONG類型的列可以重建可以通過(guò)主鍵或前導(dǎo)列（leadingcolumns）存取索引表（IOTs）的優(yōu)缺點(diǎn)優(yōu)點(diǎn)（Advantages）

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

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

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

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

不適合于在判斷中沒(méi)有使用主鍵的查詢創(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;Segment=SYS_IOT_OVER_nIOT_type=IOT_OVERFLOWSegment_type=TABLESegment=COUNTRY_C_ID_PKIOT_type=IOTSegment_type=INDEXIndex_type=IOT-TOP索引表（IOT）行溢出（Overflow）RemainingpartoftherowRowswithinPCTTHRESHOLDindxtablespaceuserstablespace從dba_tables

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

EXT$

和

FET$

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

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

SYSTEM

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

使用

DBMS_SPACE_ADMIN

軟件包:

遷移過(guò)程對(duì)本地管理的系統(tǒng)（SYSTEM）表空間強(qiáng)加了一些限制。只有數(shù)據(jù)庫(kù)系統(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');表是如何存儲(chǔ)的表空間（Tablespace）TableATableBSegmentSegmentRowsColumnsTableBlocksRowpiece區(qū)段（Extent）數(shù)據(jù)塊的解剖（Anatomy）BlockheaderFreespaceRowdataGrowth最小化數(shù)據(jù)塊的訪問(wèn)通過(guò)一下方法最小化數(shù)據(jù)塊的訪問(wèn):

使用較大的塊尺寸

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

防止數(shù)據(jù)行的遷移TablespaceBlocksSegmentsExtentsDB_BLOCK_SIZE

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

DB_BLOCK_SIZE參數(shù)所定義是在創(chuàng)建數(shù)據(jù)庫(kù)時(shí)設(shè)置將成為表空間和內(nèi)存緩沖區(qū)默認(rèn)數(shù)據(jù)塊的尺寸是數(shù)據(jù)文件讀操作的最小I/O單位默認(rèn)值為8KB；對(duì)于絕大多數(shù)IT平臺(tái)最大可達(dá)32KB不能輕易改變應(yīng)該為操作系統(tǒng)塊尺寸的整數(shù)倍小數(shù)據(jù)塊尺寸的考慮好處（Advantages）:減少塊的競(jìng)爭(zhēng)非常適合小的數(shù)據(jù)行非常適合隨機(jī)訪問(wèn)短處（Disadvantages）:具有較大的磁盤(pán)空間的額外開(kāi)銷Hasarelativelylargespaceoverhead每個(gè)數(shù)據(jù)庫(kù)只能存儲(chǔ)少量的數(shù)據(jù)行Hasasmallnumberofrowsperblock可能會(huì)引起較多的索引塊的讀操作大數(shù)據(jù)塊尺寸的考慮好處（Advantages）:較少的磁盤(pán)空間的額外開(kāi)銷適合于順序訪問(wèn)適合于非常大的數(shù)據(jù)行較好的索引讀性能短處（Disadvantages）:增加了數(shù)據(jù)塊的競(jìng)爭(zhēng)在內(nèi)存緩沖區(qū)中使用較多的內(nèi)存空間數(shù)據(jù)塊空間的管理每個(gè)段都有控制數(shù)據(jù)塊內(nèi)部空間使用的參數(shù)。對(duì)于表:PCTFREE:為修改操作預(yù)留的空間量PCTUSED:在一個(gè)數(shù)據(jù)塊被放入空閑列表之前該塊中空閑空間的最小量對(duì)于所有:PCTFREE:為創(chuàng)建新索引記錄所預(yù)留的空間量PCTUSED:對(duì)于索引總是為0利用空閑列表來(lái)管理的數(shù)據(jù)塊空間的管理InsertsInsertsInserts1324In