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

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

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

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

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

的優(yōu)化上

并行查詢的特性主要是為數(shù)據(jù)倉庫環(huán)境設(shè)計的Data兩種系統(tǒng)邏輯設(shè)計的主要差別聯(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ù)事務(wù)處理與數(shù)據(jù)倉庫系統(tǒng)的比較利用數(shù)據(jù)字典獲取系統(tǒng)的設(shè)計設(shè)計的考慮標(biāo)識活動表（ActiveTables）活動表應(yīng)該設(shè)計的越小越好

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

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

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

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

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

PLAN

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

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

PLAN

命令的語法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í)行這一語句。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());

編譯樹（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語法OFFTRACE[ONLY]EXPLAINSTATISTICSSHOWAUTOTRACESETAUTOTRACEONSQL*PlusAUTOTRACE:

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

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

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

FROMV$SGAINFO

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

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

可以避免再學(xué)習(xí)OnlineusersBatchjobsBuffercacheLargepoolSharedpoolJavapoolBuffercacheLargepoolSharedpoolJavapoolOnlineusersBatchjobsStreamspoolStreamspoolSGA大小（尺寸）:概述使用自動共享內(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自動共享內(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手工動態(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)整部分。自動調(diào)整SGA參數(shù)的行為

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

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

時:所有自動調(diào)整參數(shù)的默認(rèn)值被設(shè)為零:非零值為下邊界通過使用如下方法可以以MB來顯示當(dāng)前值:SELECTcomponent,current_size/1024/1024FROMV$SGA_DYNAMIC_COMPONENTS;手動調(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的尺寸。使用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手動重新調(diào)整

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

SGA_MAX_SIZE當(dāng)所有的部分達(dá)到最小值時可以減少其大小這些變化只影響自動調(diào)整部分（組件）SGA中所包含的部分:固定SGA部分和其它內(nèi)部分配部分自動調(diào)整SGA部分手動SGA部分允許Oracle服務(wù)器精確地分配總的共享內(nèi)存的尺寸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=0SGA指導(dǎo)（Advisor）設(shè)定共享池的尺寸使用自動共享內(nèi)存管理。當(dāng)數(shù)據(jù)有操作歷史時，使用共享池指導(dǎo)（Advisor）來確認(rèn)其它工具的診斷結(jié)果。當(dāng)沒有歷史數(shù)據(jù)時，使用40%的SGA作為起點。監(jiān)督并在需要時進(jìn)行調(diào)整。當(dāng)有可獲得的空閑內(nèi)存時，不用減少共享池的尺寸。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動態(tài)數(shù)據(jù)緩沖區(qū)指導(dǎo)（Advisory）參數(shù)數(shù)據(jù)緩沖區(qū)指導(dǎo)（advisory）特性可以開啟和關(guān)閉收集預(yù)測不同緩沖區(qū)大小對系統(tǒng)性能影響的統(tǒng)計信息。使用這些統(tǒng)計所提供的信息來設(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。觀察數(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)的物理讀的估算值。使用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é)果是有效的，其他的語句和會話就可以共享它們Oracle11g

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

PLAN

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

正常（Normal）–

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

遞減（Descending)–

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

functionsorexpressionsunlesscreatingfunction-basedindexesForeignkeysColumnsbasedonlyonquery

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

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

Index不可見索引概述VISIBLE

IndexOptimizerviewpointDataviewpoint使用索引不使用索引修改索引修改索引修改表修改表OPTIMIZER_USE_INVISIBLE_INDEXES=FALSE不可見索引的例子將索引更改為優(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;復(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索引相比，位圖索引更快并且使用更少的空間。每個位圖索引都是由一些被稱為位圖的小存儲區(qū)域所組成。每個位圖包含了每個索引列的特定值的信息。位圖是以B*-tree結(jié)構(gòu)存儲，但在存儲之前先進(jìn)行了壓縮。B-樹索引IndexentryheaderKeycolumnlengthKeycolumnvalueROWIDRootBranchLeafIndexentry位圖索引B-樹索引和位圖索引的比較B-樹（B-tree） 適合于high-cardinality

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

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

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

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

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

NOT

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

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

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

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

FROMcustomersWHEREUPPER(cust_last_name)='SMITH';ALTERSESSION

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

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

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

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

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

不適合于在判斷中沒有使用主鍵的查詢創(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)程對文件的I/O操作進(jìn)行I/O優(yōu)化的基本原則進(jìn)行I/O優(yōu)化的基本原則如下：盡量減少硬盤I/O操作將磁盤的I/O操作平衡（均勻）地分布到所有的硬盤和I/O控制器上使用合適的表空間將數(shù)據(jù)分布到不同的硬盤上數(shù)據(jù)文件與重做日志文件放在不同的硬盤上歸檔日志文件與重做日志文件放在不同的硬盤上不同的重做日志（成員）文件放在不同的硬盤上不同的控制文件最好也分別放在不同的硬盤上不同的歸檔日志文件最好也放在不同的硬盤上不同表空間對應(yīng)的文件分別放在不同的硬盤上不同類型的數(shù)據(jù)存放在不同的表空間中減少與數(shù)據(jù)庫無關(guān)操作的磁盤I/O表空間的使用system表空間只存sys用戶的數(shù)據(jù)字典對象使用本地管理表空間以避免磁盤空間管理問題表和索引應(yīng)該分別存放在不同的表空間中創(chuàng)建一個或多個單獨存放回滾段的還原表空間大對象（LOB）應(yīng)放在單獨的LOB表空間創(chuàng)建一個或多個臨時表空間檢查I/O統(tǒng)計信息診斷工具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)計信息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.空間管理空間管理有三個層次:文件(OS,ASM,裸分區(qū))將磁盤空間分配給表空間。.區(qū)段被用來將文件的磁盤空間分配給表空間中的段。數(shù)據(jù)塊被用于在數(shù)據(jù)對象內(nèi)部組織磁盤空間。區(qū)段（Extent）管理區(qū)段以兩種方式分配:數(shù)據(jù)字典管理僅僅為了支持向前兼容區(qū)段在

EXT$

和

FET$

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

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

SYSTEM

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

使用

DBMS_SPACE_ADMIN

軟件包:

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

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

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

使用較大的塊尺寸

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

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

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

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