Oracle內(nèi)存調(diào)優(yōu)及優(yōu)化思路

1、Oracle DBA 實(shí)戰(zhàn)攻略O(shè)racle內(nèi)存調(diào)優(yōu)及優(yōu)化思路 Oracle本質(zhì)上是一個(gè)軟件Oracle停止當(dāng)前版本支持時(shí)間Oracle體系結(jié)構(gòu)Oracle實(shí)例組成Oracle連接過(guò)程監(jiān)聽的作用監(jiān)聽是連接客戶端和Oracle數(shù)據(jù)庫(kù)的“橋梁”，遠(yuǎn)程客戶端進(jìn)程只有 通過(guò)服務(wù)端的監(jiān)聽認(rèn)證、轉(zhuǎn)發(fā)才能連接到數(shù)據(jù)庫(kù)。續(xù)：注意：監(jiān)聽和實(shí)例是多對(duì)多的關(guān)系。一個(gè)監(jiān)聽可以服務(wù)于多個(gè)實(shí)例。重啟主機(jī)前一定要檢查主機(jī)上是否運(yùn)行著多少個(gè)監(jiān)聽。監(jiān)聽串行處理客戶端連接的會(huì)話。監(jiān)聽的認(rèn)證和轉(zhuǎn)發(fā)只作用于會(huì)話連接的一瞬間。監(jiān)聽的啟停與關(guān)閉對(duì)已連接的會(huì)話不受影響。Oracle內(nèi)存組成Oracles memory structure c

2、onsists of two memory areas known as:System Global Area (SGA): Allocated at instance startup, and is a fundamental component of an Oracle InstanceProgram Global Area (PGA): Allocated when the server process is startedSGA組成Buffer Cache：主要緩存數(shù)據(jù)塊Shared Pool：緩存Cursor相關(guān)的信息和數(shù)據(jù)字典Log Buffer：緩存重做日志條目提示：Log Bu

3、ffer不能實(shí)現(xiàn)在線動(dòng)態(tài)調(diào)整（續(xù)）SGA的組成：SGA的一些重要屬性在Unix/Linux系統(tǒng)中，可以通過(guò)ipcs命令可以在操作系統(tǒng)級(jí)別觀察共 享內(nèi)存段是數(shù)據(jù)庫(kù)實(shí)例的重要組成部分，正常訪問(wèn)表數(shù)據(jù)的通道屬于計(jì)算性內(nèi)存內(nèi)存碎片內(nèi)存交換Buffer Cache的作用數(shù)據(jù)庫(kù)（數(shù)據(jù)文件）存放在磁盤中，buffer cache作為數(shù)據(jù)庫(kù)的緩存可以緩存不同大小的數(shù)據(jù)塊，總體使用LRU算法為主淘汰無(wú)用的數(shù)據(jù)塊數(shù)據(jù)塊的并發(fā)性訪問(wèn)受Latch保護(hù)提示：多重緩存不僅帶來(lái)浪費(fèi)內(nèi)存，而且會(huì)帶來(lái)額外的開銷，如內(nèi)核調(diào) 用。所以我們建議使用裸設(shè)備、ASM存放數(shù)據(jù)文件。一般可以提高5% 左右的讀取性能。Buffer Cache

4、的使用進(jìn)程前臺(tái)進(jìn)程：從磁盤中讀數(shù)據(jù)塊至buffer cache中DBWR進(jìn)程：從buffer cache寫數(shù)據(jù)塊至磁盤中提示：前臺(tái)進(jìn)程和DBWR進(jìn)程可以有多個(gè)，嚴(yán)格來(lái)講，同一時(shí)刻 只能由一個(gè)進(jìn)程更改數(shù)據(jù)塊（可以有多個(gè)進(jìn)程讀取數(shù)據(jù)塊），所 以Buffer Cache中需要有l(wèi)atch來(lái)保護(hù)并發(fā)性訪問(wèn)數(shù)據(jù)塊從BUFFER CACHE中讀取數(shù)據(jù)塊一般需耗時(shí)100ns從磁盤讀取數(shù)據(jù)塊一般需耗時(shí)10ms左右（假設(shè)緩存沒(méi)有命中），所以我們一般建議數(shù)據(jù)塊從buffer cache中讀取。談?wù)凚UFFER CACHE的命中率問(wèn)題：Buffer Cache的命中率越高越好？BUFFER CACHE的命中率并不是

5、越高越好，避免無(wú)效的數(shù)據(jù) 塊讀取才是數(shù)據(jù)庫(kù)優(yōu)化之根本（續(xù)）BUFFER CACHE的命中率高，并不意味著數(shù)據(jù)庫(kù)性能良好。執(zhí)行計(jì)劃出錯(cuò)時(shí)，邏輯讀高，但效率低下“熱”塊爭(zhēng)用時(shí)，邏輯讀低，但效率低下（續(xù)）Flash cache特性Buffer Cache種類Default buffer cache：數(shù)據(jù)塊的默認(rèn)緩沖池，與db_block_size參數(shù)有關(guān)Keep buffer cache：緩存熱塊Recycle buffer cache：緩存冷塊提示：BUFFER CACHE中沒(méi)有內(nèi)存碎片這一說(shuō)法ALTER SYSTEM SET DB_CACHE_SIZE = 9600M;BUFFER CACHE內(nèi)

6、存不足的優(yōu)化思路加大buffer cache內(nèi)存調(diào)整糟糕的SQL語(yǔ)句寫法或執(zhí)行計(jì)劃DBWR進(jìn)程數(shù)不足或者存儲(chǔ)性能緩慢提示：適當(dāng)加大buffer cache內(nèi)存是沒(méi)有壞處的BUFFER CACHE中數(shù)據(jù)塊爭(zhēng)用的優(yōu)化思路重新設(shè)計(jì)應(yīng)用讓數(shù)據(jù)塊中的數(shù)據(jù)盡可能地分散使用不同塊大小的數(shù)據(jù)塊修改數(shù)據(jù)塊PCTFREE參數(shù)表中添加固定大小的列，增加冗余數(shù)據(jù)使用HASH分區(qū)表和HASH 簇表使用反轉(zhuǎn)鍵索引（REVERSE INDEX）增大BUFFER CACHE合理使用KEEP POOL和RECYCLE POOL使用ASSM（自動(dòng)段空間管理）加大數(shù)據(jù)塊INTRANS參數(shù)使用直接路徑讀寫（DIRECT PATH I

7、/O）CPU緊張的系統(tǒng)中，適當(dāng)減小BUFFER CACHERAC系統(tǒng)中，提高本地節(jié)點(diǎn)的BUFFER CACHE命中率CPU、內(nèi)存資源充足的情況下，增加buffer cache容量沒(méi)有壞處，但前提是不要產(chǎn)生換頁(yè)（續(xù)）Shared PoolThe shared pool is used to store the most recently executed SQL statements and the most recently used data definitions.It consists of two key performance-related memory structures:Li

8、brary cacheData dictionary cacheSized by the parameterSHARED_POOL_SIZE.Shared poolLibrary cacheData dictionarycacheALTER SYSTEM SETSHARED_POOL_SIZE = 64M;內(nèi)存碎片化：分割連續(xù)的內(nèi)存導(dǎo)致的內(nèi)存碎片（chunk大小不一致），如下圖：注意：同一個(gè)BUCKET中的chunk不嚴(yán)格排序內(nèi)存碎片是不可避免的，相鄰的碎片會(huì)定時(shí)合并（續(xù)）合并shared pool碎片的方法：SQL alter system flush shared_pool;/dbathi

9、nker（續(xù)）注意： flush shared pool之后可能會(huì)導(dǎo)致：實(shí)例hangsequence（cache屬性）不連續(xù)過(guò)量的硬解析導(dǎo)致latch:shared pool爭(zhēng)用Library CacheThe library cache stores information about the most recently used SQL and PL/SQL statements. The library cache:Enables the sharing of commonly used statementsIs managed by a least recently used (LRU

10、) algorithmConsists of two structures:Shared SQL areaShared PL/SQL areaHas its size determined by the shared pool sizing硬解析如果一個(gè)新的SQL被發(fā)起，但是又不在shared pool里面的話， 它將被完整的解析一次。例如：Oracle必須在shared pool中分 配內(nèi)存，檢查句法和語(yǔ)義等等（續(xù)）產(chǎn)生多種執(zhí)行計(jì)劃的可能性：如果一個(gè)session發(fā)起一個(gè)已經(jīng)在shared pool中的SQL語(yǔ)句并且 它可以使用一個(gè)當(dāng)前存在的版本，那么這個(gè)過(guò)程被稱為一個(gè) soft parse

11、。對(duì)于應(yīng)用來(lái)說(shuō)，它只需請(qǐng)求解析這個(gè)語(yǔ)句。軟解析軟解析通過(guò)設(shè)置SESSION_CACHED_CURSORS參數(shù)將某個(gè)會(huì)話中常用的SQL放 入U(xiǎn)GA的會(huì)話緩沖區(qū)中，當(dāng)會(huì)話發(fā)起相同的SQL時(shí)，可以快速地從 UGA（User Global Area）取得CURSOR信息，從而減少共享池的爭(zhēng)用當(dāng)一個(gè)CURSOR被解析3次以上（包括3次）時(shí)就會(huì)放入U(xiǎn)GA會(huì)話緩沖區(qū)中當(dāng)一個(gè)語(yǔ)句被parse的時(shí)候，Oracle會(huì)首先檢查session的私有緩存中 指向的語(yǔ)句，如果有可被共享的語(yǔ)句版本的話，它就可以被使用Data Dictionary CacheThe data dictionary cache is a co

12、llection of the most recently used definitions in the database.It includes information about database files, tables, indexes, columns,users, privileges, and other database objects.During the parse phase, the server process looks at the data dictionaryfor information to resolve object names and valid

13、ate access.Caching the data dictionary information into memory improves response time on queries.Size is determined by the shared pool sizing.DICTIONARY CACHE主要用于緩存Oracle數(shù)據(jù)字典以行（Row）為單位存放，不是以塊（Block）為單位，主要存放以下內(nèi)容（dc_xxx對(duì)象）：TablespaceTableIndexSegmentViewUser以下情況需要訪問(wèn)Dictionary Cache：DDL語(yǔ)句Sequence.nextv

14、al需要訪問(wèn)對(duì)象、用戶、空間等數(shù)據(jù)字典信息（續(xù)）SHARED POOL上的優(yōu)化思路需要確保SHARED POOL的大小足夠。對(duì)于內(nèi)存，夠用就行。同樣，內(nèi)存使用 率并不是越低越好注意減少SQL硬解析注意內(nèi)存碎片重要cursor盡量避免失效（ Invalidations ）和重新加載，如將cursor keep進(jìn) library cache合理使用SUB POOL，提高并發(fā)性盡可能使用軟解析、軟軟解析。一次分配，多次使用。注意綁定變量窺視（BIND PEEKING）注意SQL高版本（HIGH VERSION COUNT）不要收集多余的柱狀圖/索引，給優(yōu)化器太多的選擇也不好設(shè)置合理的數(shù)據(jù)庫(kù)參數(shù)SES

15、SION_CACHED_CURSORS設(shè)置合理的數(shù)據(jù)庫(kù)參數(shù)CURSOR_SHARING注意Oracle bug：Bug Issues Known to Affect the Shared Pool (文檔ID 102305.1)（續(xù)）LOG BUFFERRedo conceptLGWR進(jìn)程寫redo log的觸發(fā)條件3秒1/3滿或者1MB提交(commit)或者回滾（rollback）DBWR進(jìn)程觸發(fā)（續(xù)）在生產(chǎn)庫(kù)（Oracle 10g以上）設(shè)置COMMIT_WRITE = BATCH,NOWAIT（續(xù)）數(shù)據(jù)庫(kù)性能優(yōu)化是個(gè)大命題，其主要特點(diǎn)為性能出問(wèn)題時(shí)不 報(bào)錯(cuò)（所以每個(gè)磚家的優(yōu)化觀點(diǎn)還不同）。需要優(yōu)化者比較 扎實(shí)的數(shù)據(jù)庫(kù)功底，全面的IT知識(shí)。其優(yōu)化視角和優(yōu)化思路 往往決定了優(yōu)化項(xiàng)目能否實(shí)施成功。數(shù)據(jù)庫(kù)有性能問(wèn)題時(shí)往往趨向于I/O資源緊張、Oracle資源（latch，hot block）沖突、多進(jìn)程交互或者程序異步機(jī)制設(shè)計(jì)有問(wèn)題。除此之外，引