Oracle Tuning的一些總結(jié).doc

Oracle Tuning的一些總結(jié) 關(guān)于Oracle的性能調(diào)整，一般包括兩個(gè)方面，一是指Oracle數(shù)據(jù)庫(kù)本身的調(diào)整，比如SGA、PGA的優(yōu)化設(shè)置，二是連接Oracle的應(yīng)用程序以及SQL語(yǔ)句的優(yōu)化。做好這兩個(gè)方面的優(yōu)化，就可以使一套完整的Oracle應(yīng)用系統(tǒng)處于良好的運(yùn)行狀態(tài)。 本文主要是把一些Oracle Tuning的文章作了一個(gè)簡(jiǎn)單的總結(jié)，力求以實(shí)際可操作為目的，配合講解部分理論知識(shí)，使大部分具有一般Oracle知識(shí)的使用者能夠?qū)racle Tuning有所了解，并且能夠根據(jù)實(shí)際情況對(duì)某些參數(shù)進(jìn)行調(diào)整。關(guān)于更加詳細(xì)的知識(shí)，請(qǐng)參見(jiàn)本文結(jié)束部分所提及的推薦書(shū)籍，同時(shí)由于該話題內(nèi)容太多且復(fù)雜，本文必定有失之偏頗甚至錯(cuò)誤的地方，請(qǐng)不吝賜教，并共同進(jìn)步。 1. SGA的設(shè)置 在Oracle Tuning中，對(duì)SGA的設(shè)置是關(guān)鍵。SGA，是指Shared Global Area , 或者是 System Global Area , 稱為共享全局區(qū)或者系統(tǒng)全局區(qū)，結(jié)構(gòu)如下圖所示。 對(duì)于SGA區(qū)域內(nèi)的內(nèi)存來(lái)說(shuō)，是共享的、全局的，在UNIX 上，必須為oracle 設(shè)置共享內(nèi)存段（可以是一個(gè)或者多個(gè)），因?yàn)閛racle 在UNIX上是多進(jìn)程；而在WINDOWS上oracle是單進(jìn)程（多個(gè)線程），所以不用設(shè)置共享內(nèi)存段。 1.1 SGA的各個(gè)組成部分 下面用 sqlplus 查詢舉例看一下 SGA 各個(gè)組成部分的情況： SQL select * from v$sga; NAME VALUE - - Fixed Size 104936 Variable Size 823164928 Database Buffers 1073741824 Redo Buffers 172032 或者 SQL show sga Total System Global Area 1897183720 bytes Fixed Size 104936 bytes Variable Size 823164928 bytes Database Buffers 1073741824 bytes Redo Buffers 172032 bytes Fixed Size oracle 的不同平臺(tái)和不同版本下可能不一樣，但對(duì)于確定環(huán)境是一個(gè)固定的值，里面存儲(chǔ)了SGA 各部分組件的信息，可以看作引導(dǎo)建立SGA的區(qū)域。 Variable Size 包含了shared_pool_size、java_pool_size、large_pool_size 等內(nèi)存設(shè)置 Database Buffers 指數(shù)據(jù)緩沖區(qū)，在8i 中包含db_block_buffer*db_block_size、buffer_pool_keep、buffer_pool_recycle 三部分內(nèi)存。在9i 中包含db_cache_size、db_keep_cache_size、db_recycle_cache_size、db_nk_cache_size。 Redo Buffers 指日志緩沖區(qū)，log_buffer。在這里要額外說(shuō)明一點(diǎn)的是，對(duì)于v$parameter、v$sgastat、v$sga查詢值可能不一樣。v$parameter 里面的值，是指用戶在初始化參數(shù)文件里面設(shè)置的值，v$sgastat是oracle 實(shí)際分配的日志緩沖區(qū)大?。ㄒ?yàn)榫彌_區(qū)的分配值實(shí)際上是離散的，也不是以block 為最小單位進(jìn)行分配的），v$sga 里面查詢的值，是在oracle 分配了日志緩沖區(qū)后，為了保護(hù)日志緩沖區(qū)，設(shè)置了一些保護(hù)頁(yè)，通常我們會(huì)發(fā)現(xiàn)保護(hù)頁(yè)大小是8k(不同環(huán)境可能不一樣)。參考如下內(nèi)容 SQL select substr(name,1,10) name,substr(value,1,10) value 2 from v$parameter where name = log_buffer; NAME VALUE - - log_buffer 163840 SQL select * from v$sgastat where pool is null; POOL NAME BYTES - - - fixed_sga 104936 db_block_buffers 1073741824 log_buffer 163840 SQL select * from v$sga; NAME VALUE - - Fixed Size 104936 Variable Size 823164928 Database Buffers 1073741824 Redo Buffers 172032 172032 163840 = 8192 （以上試驗(yàn)數(shù)據(jù)是在 HP B.11.11 + Oracle 環(huán)境下得到的） 1.2 SGA的大小設(shè)置 在對(duì)SGA的結(jié)構(gòu)進(jìn)行簡(jiǎn)單分析以后，下面是關(guān)于如何根據(jù)系統(tǒng)的情況正確設(shè)置SGA大小的問(wèn)題。 SGA是一塊內(nèi)存區(qū)域，占用的是系統(tǒng)物理內(nèi)存，因此對(duì)于一個(gè)Oracle應(yīng)用系統(tǒng)來(lái)說(shuō)，SGA決不是越大越好，這就需要尋找一個(gè)系統(tǒng)優(yōu)化的平衡點(diǎn)。 1.2.1 設(shè)置參數(shù)前的準(zhǔn)備 在設(shè)置SGA的內(nèi)存參數(shù)之前，我們首先要問(wèn)自己幾個(gè)問(wèn)題 一：物理內(nèi)存多大 二：操作系統(tǒng)估計(jì)需要使用多少內(nèi)存 三：數(shù)據(jù)庫(kù)是使用文件系統(tǒng)還是裸設(shè)備 四：有多少并發(fā)連接 五：應(yīng)用是OLTP 類(lèi)型還是OLAP 類(lèi)型 根據(jù)這幾個(gè)問(wèn)題的答案，我們可以粗略地為系統(tǒng)估計(jì)一下內(nèi)存設(shè)置。那我們現(xiàn)在來(lái)逐個(gè)問(wèn)題地討論，首先物理內(nèi)存多大是最容易回答的一個(gè)問(wèn)題，然后操作系統(tǒng)估計(jì)使用多少內(nèi)存呢？從經(jīng)驗(yàn)上看，不會(huì)太多，通常應(yīng)該在200M 以內(nèi)（不包含大量進(jìn)程PCB）。 接下來(lái)我們要探討一個(gè)重要的問(wèn)題，那就是關(guān)于文件系統(tǒng)和裸設(shè)備的問(wèn)題，這往往容易被我們所忽略。操作系統(tǒng)對(duì)于文件系統(tǒng)，使用了大量的buffer 來(lái)緩存操作系統(tǒng)塊。這樣當(dāng)數(shù)據(jù)庫(kù)獲取數(shù)據(jù)塊的時(shí)候，雖然SGA 中沒(méi)有命中，但卻實(shí)際上可能是從操作系統(tǒng)的文件緩存中獲取的。而假如數(shù)據(jù)庫(kù)和操作系統(tǒng)支持異步IO，則實(shí)際上當(dāng)數(shù)據(jù)庫(kù)寫(xiě)進(jìn)程DBWR寫(xiě)磁盤(pán)時(shí)，操作系統(tǒng)在文件緩存中標(biāo)記該塊為延遲寫(xiě)，等到真正地寫(xiě)入磁盤(pán)之后，操作系統(tǒng)才通知DBWR寫(xiě)磁盤(pán)完成。對(duì)于這部分文件緩存，所需要的內(nèi)存可能比較大，作為保守的估計(jì)，我們應(yīng)該考慮在 0.20.3 倍內(nèi)存大小。但是如果我們使用的是裸設(shè)備，則不考慮這部分緩存的問(wèn)題。這樣的情況下SGA就有調(diào)大的機(jī)會(huì)。 關(guān)于數(shù)據(jù)庫(kù)有多少并發(fā)連接，這實(shí)際上關(guān)系到PGA 的大?。∕TS 下還有l(wèi)arge_pool_size）。事實(shí)上這個(gè)問(wèn)題應(yīng)該說(shuō)還跟OLTP 類(lèi)型或者OLAP 類(lèi)型相關(guān)。對(duì)于OLTP類(lèi)型oracle 傾向于可使用MTS,對(duì)于OLAP 類(lèi)型使用獨(dú)立模式，同時(shí)OLAP 還可能涉及到大量的排序操作的查詢，這些都影響到我們內(nèi)存的使用。那么所有的問(wèn)題綜合起來(lái)，實(shí)際上主要反映在UGA的大小上。UGA主要包含以下部分內(nèi)存設(shè)置 SQL show parameters area_size NAME TYPE VALUE - - - bitmap_merge_area_size integer 1048576 create_bitmap_area_size integer 8388608 hash_area_size integer 131072 sort_area_size integer 65536 SQL 在這部分內(nèi)存中我們最關(guān)注的通常是sort_area_size，這是當(dāng)查詢需要排序的時(shí)候，數(shù)據(jù)庫(kù)會(huì)話將使用這部分內(nèi)存進(jìn)行排序，當(dāng)內(nèi)存大小不足的時(shí)候，使用臨時(shí)表空間進(jìn)行磁盤(pán)排序。由于磁盤(pán)排序效率和內(nèi)存排序效率相差好幾個(gè)數(shù)量級(jí)，所以這個(gè)參數(shù)的設(shè)置很重要。 當(dāng)出現(xiàn)大量排序時(shí)的磁盤(pán)I/O操作時(shí)，可以考慮增加sort_area_size的值。sort_area_size是Oracle用于一次排序所需的最大內(nèi)存數(shù)，在排序結(jié)束但是結(jié)果列返回之前，Oracle會(huì)釋放sort_area_size大小的內(nèi)存，但是會(huì)保留sort_area_retained_size大小的內(nèi)存，知道最后一行結(jié)果列返回以后，才釋放所有的內(nèi)存。 會(huì)導(dǎo)致排序的語(yǔ)句有 SELECT DISTINCT , MINUS , INTERSECT , UNION 和 min()、max()、count() 操作；而不會(huì)導(dǎo)致排序的語(yǔ)句有 UPDATE , 帶BETWEEN子句的SELECT 等等。 這四個(gè)參數(shù)都是針對(duì)會(huì)話進(jìn)行設(shè)置的，是單個(gè)會(huì)話使用的內(nèi)存的大小，而不是整個(gè)數(shù)據(jù)庫(kù)使用的。偶爾會(huì)看見(jiàn)有人誤解了這個(gè)參數(shù)以為是整個(gè)數(shù)據(jù)庫(kù)使用的大小，這是極其嚴(yán)重的錯(cuò)誤。假如設(shè)置了MTS，則UGA被分配在large_pool_size，也就是說(shuō)放在了共享內(nèi)存里面，不同進(jìn)程（線程）之間可以共享這部分內(nèi)存。在這個(gè)基礎(chǔ)上，我們假設(shè)數(shù)據(jù)庫(kù)存在并發(fā)執(zhí)行server process 為100 個(gè)，根據(jù)上面我們4 個(gè)參數(shù)在oracle8.1.7 下的默認(rèn)值，我們來(lái)計(jì)算獨(dú)立模式下PGA 的大致大小。由于會(huì)話并不會(huì)經(jīng)常使用create_bitmap_area_size 、bitmap_merge_area_size，所以我們通常不對(duì)四個(gè)參數(shù)求和。在考慮到除這四個(gè)參數(shù)外會(huì)話所保存的變量、堆棧等信息，我們估計(jì)為2M，則200 個(gè)進(jìn)程最大可能使用200M 的PGA。 1.2.2 一個(gè)經(jīng)驗(yàn)公式 現(xiàn)在，根據(jù)上面這些假定，我們來(lái)看SGA 實(shí)際能達(dá)到多少內(nèi)存。在1G 的內(nèi)存的服務(wù)器上，我們能分配給SGA 的內(nèi)存大約為400500M。若是2G 的內(nèi)存，大約可以分到1G的內(nèi)存給SGA，8G 的內(nèi)存可以分到5G的內(nèi)存給SGA。當(dāng)然我們這里是以默認(rèn)的排序部分內(nèi)存sort_area_size=64k進(jìn)行衡量的，假如我們需要調(diào)大該參數(shù)和hash_area_size等參數(shù)，然后我們應(yīng)該根據(jù)并發(fā)的進(jìn)程的數(shù)量，來(lái)衡量考慮這個(gè)問(wèn)題。 事實(shí)上，通常我們更習(xí)慣通過(guò)直觀的公式化來(lái)表達(dá)這樣的問(wèn)題： OS 使用內(nèi)存+SGA+并發(fā)執(zhí)行進(jìn)程數(shù)*(sort_area_size+hash_ara_size+2M) 0.7*總內(nèi)存 (公式是死的，系統(tǒng)是活的，實(shí)際應(yīng)用的調(diào)整不必框公式，這不過(guò)是一個(gè)參考建議) 在我們的實(shí)際應(yīng)用中，假如采用的是裸設(shè)備，我們可適當(dāng)?shù)脑龃骃GA(如果需要的話)。由于目前幾乎所有的操作系統(tǒng)都使用虛擬緩存，所以實(shí)際上如果就算SGA 設(shè)置的比較大也不會(huì)導(dǎo)致錯(cuò)誤，而是可能出現(xiàn)頻繁的內(nèi)存頁(yè)的換入與換出(page in/out)。在操作系統(tǒng)一級(jí)如果觀察到這個(gè)現(xiàn)象，那么我們就需要調(diào)整內(nèi)存的設(shè)置。 1.2.3 各個(gè)參數(shù)的設(shè)置 那么SGA中的各個(gè)參數(shù)具體應(yīng)該按照什么樣的原則來(lái)設(shè)置呢，下面進(jìn)行討論： log_buffer 對(duì)于日志緩沖區(qū)的大小設(shè)置，通常我覺(jué)得沒(méi)有過(guò)多的建議，因?yàn)閰⒖糒GWR寫(xiě)的觸發(fā)條件之后，我們會(huì)發(fā)現(xiàn)通常超過(guò)3M意義不是很大。作為一個(gè)正式系統(tǒng)，可能考慮先設(shè)置這部分為log_buffer=13M 大小，然后針對(duì)具體情況再調(diào)整。 large_pool_size 對(duì)于大緩沖池的設(shè)置，假如不使用MTS，建議在2030M 足夠了。這部分主要用來(lái)保存并行查詢時(shí)候的一些信息，還有就是RMAN 在備份的時(shí)候可能會(huì)使用到。如果設(shè)置了MTS，則由于UGA部分要移入這里，則需要具體根據(jù)session最大數(shù)量和 sort_ares_size 等相關(guān)會(huì)話內(nèi)存參數(shù)的設(shè)置來(lái)綜合考慮這部分大小的設(shè)置，一般可以考慮為 session * (sort_area_size + 2M)。這里要提醒一點(diǎn)，不是必須使用MTS，我們都不主張使用MTS，尤其同時(shí)在線用戶數(shù)小于500的情況下。 java_pool_size 假如數(shù)據(jù)庫(kù)沒(méi)有使用JAVA，我們通常認(rèn)為保留1020M大小足夠了。事實(shí)上可以更少，甚至最少只需要32k，但具體跟安裝數(shù)據(jù)庫(kù)的時(shí)候的組件相關(guān)(比如http server)。 shared_pool_size 這是迄今為止最具有爭(zhēng)議的一部分內(nèi)存設(shè)置。按照很多文檔的描述，這部分內(nèi)容應(yīng)該幾乎和數(shù)據(jù)緩沖區(qū)差不多大小。但實(shí)際上情況卻不是這樣的。首先我們要考究一個(gè)問(wèn)題，那就是這部分內(nèi)存的作用，它是為了緩存已經(jīng)被解析過(guò)的SQL，而使其能被重用，不再解析。這樣做的原因是因?yàn)椋瑢?duì)于一個(gè)新的SQL（shared_pool 里面不存在已經(jīng)解析的可用的相同的SQL），數(shù)據(jù)庫(kù)將執(zhí)行硬解析，這是一個(gè)很消耗資源的過(guò)程。而若已經(jīng)存在，則進(jìn)行的僅僅是軟分析（在共享池中尋找相同SQL），這樣消耗的資源大大減少。所以我們期望能多共享一些SQL，并且如果該參數(shù)設(shè)置不夠大，經(jīng)常會(huì)出現(xiàn)ora-04031錯(cuò)誤，表示為了解析新的SQL，沒(méi)有可用的足夠大的連續(xù)空閑空間，這樣自然我們期望該參數(shù)能大一些。但是該參數(shù)的增大，卻也有負(fù)面的影響，因?yàn)樾枰S護(hù)共享的結(jié)構(gòu)，內(nèi)存的增大也會(huì)使得SQL 的老化的代價(jià)更高，帶來(lái)大量的管理的開(kāi)銷(xiāo)，所有這些可能會(huì)導(dǎo)致CPU 的嚴(yán)重問(wèn)題。 在一個(gè)充分使用綁定變量的比較大的系統(tǒng)中，shared_pool_size 的開(kāi)銷(xiāo)通常應(yīng)該維持在300M 以內(nèi)。除非系統(tǒng)使用了大量的存儲(chǔ)過(guò)程、函數(shù)、包，比如oracle erp 這樣的應(yīng)用，可能會(huì)達(dá)到500M甚至更高。于是我們假定一個(gè)1G內(nèi)存的系統(tǒng)，可能考慮設(shè)置該參數(shù)為100M，2G 的系統(tǒng)考慮設(shè)置為150M,8G 的系統(tǒng)可以考慮設(shè)置為200300M。 對(duì)于一個(gè)沒(méi)有充分使用或者沒(méi)有使用綁定變量系統(tǒng)，這可能給我們帶來(lái)一個(gè)嚴(yán)重的問(wèn)題。所謂沒(méi)有使用bind var 的SQL，我們稱為L(zhǎng)iteral SQL。也就是比如這樣的兩句SQL我們認(rèn)為是不同的SQL,需要進(jìn)行2 次硬解析： select * from EMP where name = TOM; select * from EMP where name = JERRY; 假如把 TOM 和 JERRY 換做變量V，那就是使用了bind var，我們可以認(rèn)為是同樣的SQL 從而能很好地共享。共享SQL 本來(lái)就是shared_pool_size 這部分內(nèi)存存在的本意，oracle的目的也在于此，而我們不使用bind var 就是違背了oracle 的初衷，這樣將給我們的系統(tǒng)帶來(lái)嚴(yán)重的問(wèn)題。當(dāng)然，如果通過(guò)在操作系統(tǒng)監(jiān)控，沒(méi)有發(fā)現(xiàn)嚴(yán)重的cpu問(wèn)題，我們?nèi)绻l(fā)現(xiàn)該共享池命中率不高可以適當(dāng)?shù)脑黾觭hred_pool_size。但是通常我們不主張這部分內(nèi)存超過(guò)800M（特殊情況下可以更大）。 事實(shí)上，可能的話我們甚至要想辦法避免軟分析，這在不同的程序語(yǔ)言中實(shí)現(xiàn)方式有差異。我們也可能通過(guò)設(shè)置session_cached_cursors 參數(shù)來(lái)獲得幫助（這將增大PGA） 關(guān)于使用綁定變量的話題，在下面的應(yīng)用優(yōu)化中繼續(xù)討論。 Data buffer 現(xiàn)在我們來(lái)談數(shù)據(jù)緩沖區(qū)，在確定了SGA 的大小并分配完了前面部分的內(nèi)存后，其余的，都分配給這部分內(nèi)存。通常，在允許的情況下，我們都嘗試使得這部分內(nèi)存更大。這部分內(nèi)存的作用主要是緩存 DB BLOCK，減少甚至避免從磁盤(pán)上獲取數(shù)據(jù)，在8i中通常是由db_block_buffers*db_block_size 來(lái)決定大小的。如果我們?cè)O(shè)置了buffer_pool_keep 和buffer_pool_recycle，則應(yīng)該加上后面這兩部分內(nèi)存的大小。 可以看出，設(shè)置SGA時(shí)基本上應(yīng)該掌握的原則是： data buffer 一般可以盡可能的大 shared_pool_size 應(yīng)該適度 log buffer 在 1MB 以內(nèi)就可以了 假定oracle是 32 bit ,服務(wù)器RAM大于2G ，注意你的PGA的情況，,則建議 shared_pool_size + data buffer +large_pool_size + java_pool_size select * from v$version; BANNER - Oracle8i Enterprise Edition Release .0 - Production PL/SQL Release .0 - Production CORE .0 Production TNS for 32-bit Windows: Version .0 - Production NLSRTL Version .0 Production 在UNIX平臺(tái)下的顯示有所不同，明顯可以看出是 64bit Oracle ，比如在HP-UX平臺(tái)上： SQL select * from v$version; BANNER - Oracle8i Enterprise Edition Release .0 - 64bit Production PL/SQL Release .0 - Production CORE .0 Production TNS for HPUX: Version .0 - Production NLSRTL Version .0 Production 32bit的oracle無(wú)論跑在32bit或者64bit的平臺(tái)都有SGA的限制的，而對(duì)于32bit的平臺(tái)只能跑32bit的oracle，但是在特定的操作系統(tǒng)下，可能提供了一定的手段，使得我們可以使用超過(guò)1.7G 的內(nèi)存，達(dá)到2G 以上甚至更多。由于我們現(xiàn)在一般都使用64bit Oracle，因此關(guān)于如何在32bit平臺(tái)上擴(kuò)展SGA大小的問(wèn)題不再贅述。 1.4 9i中相關(guān)參數(shù)的變化 oracle的版本的更新，總是伴隨著參數(shù)的變化，并且越來(lái)越趨向于使得參數(shù)的設(shè)置更簡(jiǎn)單，因?yàn)閺?fù)雜的參數(shù)設(shè)置使得DBA們經(jīng)常焦頭爛額。關(guān)于內(nèi)存這部分的變化，我們可以考察下面的參數(shù)。事實(shí)上在9i中數(shù)據(jù)庫(kù)本身可以給出一組適合當(dāng)前運(yùn)行系統(tǒng)的SGA相關(guān)部分的參數(shù)調(diào)整值（參考V$DB_CACHE_ADVICE、V$SHARED_POOL_ADVICE），關(guān)于PGA也有相關(guān)視圖V$PGA_TARGET_ADVICE 等。 Data buffer 9i 中保留了8i中的參數(shù)，如設(shè)置了新的參數(shù)，則忽略舊的參數(shù)。9i中用db_cache_size來(lái)取代db_block_buffers ， 用db_keep_cache_size 取代buffer_pool_keep, 用db_recycle_cache_size 取代buffer_pool_recycle；這里要注意9i 中設(shè)置的是實(shí)際的緩存大小而不再是塊的數(shù)量。另外9i新增加了db_nk_cache_size，這是為了支持在同一個(gè)數(shù)據(jù)庫(kù)中使用不同的塊大小而設(shè)置的。對(duì)于不同的表空間，可以定義不同的數(shù)據(jù)塊的大小，而緩沖區(qū)的定義則依靠該參數(shù)的支持。其中n 可以為2、4、6、8、16 等不同的值。在這里順便提及的一個(gè)參數(shù)就是db_block_lru_latches，該參數(shù)在9i中已經(jīng)成為了保留參數(shù)，不推薦手工設(shè)置。 PGA 在9i 里面這部分也有了很大的變化。在獨(dú)立模式下，9i已經(jīng)不再主張使用原來(lái)的UGA相關(guān)的參數(shù)設(shè)置，而代之以新的參數(shù)。假如workarea_size_policy=AUTO（缺省），則所有的會(huì)話的UGA 共用一大塊內(nèi)存，該內(nèi)存由 pga_aggregate_target 設(shè)置。在我們根據(jù)前面介紹的方法評(píng)估了所有進(jìn)程可能使用的最大PGA 內(nèi)存之后，我們可以通過(guò)在初始化參數(shù)中設(shè)置這個(gè)參數(shù)，從而不再關(guān)心其他 ”*_area_size” 參數(shù)。 SGA_MAX_SIZE 在9i中若設(shè)置了SGA_MAX_SIZE，則在總和小于等于這個(gè)值內(nèi)，可以動(dòng)態(tài)的調(diào)整數(shù)據(jù)緩沖區(qū)和共享池的大小 SQL show parameters sga_max_size NAME TYPE VALUE - - - - sga_max_size unknown 193752940 SQL SQL alter system set db_cache_size = 30000000; System altered. SQL alter system set shared_pool_size = 20480000; System altered. 1.5 lock_sga = true 的問(wèn)題 由于幾乎所有的操作系統(tǒng)都支持虛擬內(nèi)存，所以即使我們使用的內(nèi)存小于物理內(nèi)存，也不能避免操作系統(tǒng)將SGA 換到虛擬內(nèi)存（SWAP）。所以我們可以嘗試使得SGA 鎖定在物理內(nèi)存中不被換到虛擬內(nèi)存中，這樣減少頁(yè)面的換入和換出，從而提高性能。但在這里遺憾的是，windows 是無(wú)法避免這種情況的。下面我們來(lái)參考在不同的幾個(gè)系統(tǒng)下怎么實(shí)現(xiàn)lock_sga AIX 5L（AIX 4.3.3 以上） logon aix as root cd /usr/samples/kernel ./vmtune (信息如下) v_pingshm已經(jīng)是1 ./vmtune -S 1 然后oracle用戶修改initSID.ora 中 lock_sga = true 重新啟動(dòng)數(shù)據(jù)庫(kù) HP UNIX Root身份登陸 Create the file /etc/privgroup: vi /etc/privgroup Add line dba MLOCK to file As root, run the command /etc/setprivgrp -f /etc/privgroup: $/etc/setprivgrp -f /etc/privgroup oracle用戶修改initSID.ora中l(wèi)ock_sga=true 重新啟動(dòng)數(shù)據(jù)庫(kù) SOLARIS (solaris2.6以上) 8i版本以上數(shù)據(jù)庫(kù)默認(rèn)使用隱藏參數(shù) use_ism = true ，自動(dòng)鎖定SGA于內(nèi)存中,不用設(shè)置lock_sga, 如果設(shè)置 lock_sga =true 使用非 root 用戶啟動(dòng)數(shù)據(jù)庫(kù)將返回錯(cuò)誤。 WINDOWS 不能設(shè)置lock_sga=true,可以通過(guò)設(shè)置pre_page_sga=true,使得數(shù)據(jù)庫(kù)啟動(dòng)的時(shí)候就把所有內(nèi)存頁(yè)裝載，這樣可能起到一定的作用。 2. 應(yīng)用優(yōu)化 下面我們從技術(shù)的角度入手，來(lái)探討數(shù)據(jù)庫(kù)優(yōu)化方面的問(wèn)題。通常作為優(yōu)化Oracle系統(tǒng)的人，或者是DBA，其實(shí)很多時(shí)候?qū)?yīng)用并不很了解甚至可以說(shuō)是完全不了解，更不要說(shuō)對(duì)應(yīng)用程序代碼的了解。事實(shí)上呢，一個(gè)系統(tǒng)運(yùn)行的快或者慢相信大家都明白，第一重要的是數(shù)據(jù)庫(kù)的設(shè)計(jì)，然后是應(yīng)用的設(shè)計(jì)，SQL語(yǔ)句的編寫(xiě)，最后才是數(shù)據(jù)庫(kù)參數(shù)的調(diào)整和硬件、網(wǎng)絡(luò)的問(wèn)題，等等。所以在我們不了解一個(gè)系統(tǒng)的時(shí)候來(lái)優(yōu)化數(shù)據(jù)庫(kù)應(yīng)用不是一個(gè)輕松的容易的事情。那么我們第一步應(yīng)該怎么做呢？ 通常有兩類(lèi)方法： 其中一個(gè)方法就是我們常用的，使用statspack來(lái)進(jìn)行診斷系統(tǒng)的瓶頸所在。在statspack中oracle給出了幾乎涵蓋oracle大部分重要內(nèi)容的信息。 另外一種方式，就是trace session。假如某個(gè)session運(yùn)行很慢或者某個(gè)用戶的某個(gè)查詢很慢，那么這個(gè)時(shí)候我們可以通過(guò)trace session的方式來(lái)診斷到底是慢在哪里，看究竟執(zhí)行計(jì)劃是怎樣的，然后在user_dump_dest下根據(jù)該session的進(jìn)程號(hào)或者線程號(hào)可以找到一個(gè)產(chǎn)生的trace文件。通過(guò)使用tkprof格式化文件之后我們就可以看見(jiàn)很多的統(tǒng)計(jì)信息，這里包括了執(zhí)行計(jì)劃、parse/fetch等步驟消耗cpu的時(shí)間。通常我們是觀察query模式下的consistent gets來(lái)首先看sql是否使用了索引，然后看執(zhí)行計(jì)劃是不是正常，是不是有調(diào)整的余地。當(dāng)然如果您沒(méi)有實(shí)際做過(guò)的話，這些內(nèi)容說(shuō)起來(lái)很抽象。這是在不了解應(yīng)用和程序下針對(duì)特定session的診斷和調(diào)整過(guò)程。 trace session的方式是一種自下而上的方法，從sql入手；而statspack是自頂向下的方法，也就是從宏觀上先診斷數(shù)據(jù)庫(kù)的瓶頸在哪里，然后從瓶頸入手來(lái)做調(diào)整，這個(gè)習(xí)慣上又可以稱為通過(guò)等待事件（wait event）入手的方法。 2.1 使用statspack statspack是一個(gè)性能診斷工具，首先發(fā)布于Oracle8.1.6版本，在8.1.7版本中功能得到加強(qiáng)。Statspack除了查找實(shí)例中的性能問(wèn)題外，還可以查找應(yīng)用程序中高負(fù)荷的SQL語(yǔ)句，很容易確定Oracle 數(shù)據(jù)庫(kù)的瓶頸所在，并且記錄數(shù)據(jù)庫(kù)性能狀態(tài)。 在數(shù)據(jù)庫(kù)中Statspack 的腳本位于$ORACLE_HOME/RDBMS/ADMIN 目錄下，對(duì)于ORACLE8.1.6,是一組以stat 開(kāi)頭的文件；對(duì)于ORACLE8.1.7,是一組以sp 開(kāi)頭的文件。 在Statspack 發(fā)布之前，我們通常能夠使用診斷數(shù)據(jù)庫(kù)的工具是兩個(gè)腳本UTLBSTAT.SQL 和UTLESTAT.SQL，BSTAT/ESTAT 是一個(gè)非常簡(jiǎn)單的性能診斷工具。UTLBSTAT 獲得開(kāi)始時(shí)很多V$視圖的快照，UTLESTAT 通過(guò)先前的快照和當(dāng)前視圖生成一個(gè)報(bào)表。 該報(bào)表實(shí)際上相當(dāng)于statspack 中的兩個(gè)采樣點(diǎn)。 Statspack 通過(guò)連續(xù)的采樣，能夠給我們提供至關(guān)重要的趨勢(shì)分析數(shù)據(jù)。這是一個(gè)巨大的進(jìn)步。能夠使用Statspack 的環(huán)境我們就盡量不要使用BSTAT/ESTAT 的方式來(lái)診斷數(shù)據(jù)庫(kù)問(wèn)題。 2.1.1 安裝statapack 步驟一： 為了能夠順利安裝和運(yùn)行Statspack ，首先需要設(shè)置以下兩個(gè)系統(tǒng)參數(shù)： 1. job_queue_processes 為了能夠建立自動(dòng)任務(wù)，執(zhí)行數(shù)據(jù)收集，該參數(shù)需要大于0。你可以在初試化參數(shù)文件中修改該參數(shù)(使該參數(shù)在重起后以然有效)。 該參數(shù)可以在系統(tǒng)級(jí)動(dòng)態(tài)修改(重起后失效)。 SQL alter system set job_queue_processes = 6; System altered 在Oracle9i 當(dāng)中，可以指定范圍，如 both,這樣該修改在當(dāng)前及之后保持有效(僅當(dāng)你使用spfile 時(shí)，如果在9i 中仍然使用pfile，那么更改方法同8i 相同): SQL alter system set job_queue_processes = 6 scope=both; System altered 2. timed_statistics 收集操作系統(tǒng)的計(jì)時(shí)信息，這些信息可被用來(lái)顯示時(shí)間等統(tǒng)計(jì)信息、優(yōu)化數(shù)據(jù)庫(kù)和 SQL 語(yǔ)句。要防止因從操作系統(tǒng)請(qǐng)求時(shí)間而引起的開(kāi)銷(xiāo)，請(qǐng)將該值設(shè)置為False。 使用statspack 收集統(tǒng)計(jì)信息時(shí)建議將該值設(shè)置為 TRUE，否則收集的統(tǒng)計(jì)信息大約只能起到10%的作用，將timed_statistics 設(shè)置為T(mén)rue 所帶來(lái)的性能影響與好處相比是微不足道的。 該參數(shù)使收集的時(shí)間信息存儲(chǔ)在在V$SESSTATS 和V$SYSSTATS 等動(dòng)態(tài)性能視圖中。 timed_statistics 參數(shù)也可以在實(shí)例級(jí)進(jìn)行更改 SQL alter system set timed_statistics = true; System altered 如果你擔(dān)心一直啟用timed_statistics 對(duì)于性能的影響，你可以在使用statspack 之前在system 更改，采樣過(guò)后把該參數(shù)動(dòng)態(tài)修改成false。 步驟二： 需要單獨(dú)為statspack創(chuàng)建一個(gè)存儲(chǔ)數(shù)據(jù)的表空間，如果采樣間隔較短，周期較長(zhǎng)，打算長(zhǎng)期使用，那么可能需要一個(gè)大一點(diǎn)的表空間，如果每個(gè)半個(gè)小時(shí)采樣一次，連續(xù)采樣一周，數(shù)據(jù)量是很大的。下面的例子中創(chuàng)建了一個(gè)500M 的測(cè)試表空間。 注意: 這里創(chuàng)建的表空間不能太小，如果太小的話創(chuàng)建對(duì)象會(huì)失敗，建議至少建立100M 表空間。 SQL create tablespace perfstat 2 datafile /oracle/oradata/oradata/res/perfstat.dbf 3 size 500M; Tablespace created。 步驟三： 在 sqlplus 中用internal 身份登陸，或者擁有SYSDBA(connect / as sysdba)權(quán)限的用戶登陸。 注: 在Oracle9i 中，不存在internal 用戶，可以使用sys 用戶以sysdba 身份連接。 先轉(zhuǎn)到$ORACLE_HOME/RDBMS/ADMIN 目錄，檢查安裝腳本是否存在，同時(shí)我們執(zhí)行腳本也可以方便些。 $ cd $ORACLE_HOME/rdbms/admin $ ls -l sp*.sql -rw-r-r- 1 oracle other 1774 Feb 18 2000 spauto.sql -rw-r-r- 1 oracle other 62545 Jun 15 2000 spcpkg.sql -rw-r-r- 1 oracle other 877 Feb 18 2000 spcreate.sql -rw-r-r- 1 oracle other 31193 Jun 15 2000 spctab.sql -rw-r-r- 1 oracle other 6414 Jun 15 2000 spcusr.sql -rw-r-r- 1 oracle other 758 Jun 15 2000 spdrop.sql -rw-r-r- 1 oracle other 3615 Jun 15 2000 spdtab.sql -rw-r-r- 1 oracle other 1274 Jun 15 2000 spdusr.sql -rw-r-r- 1 oracle other 6760 Jun 15 2000 sppurge.sql -rw-r-r- 1 oracle other 71034 Jul 12 2000 spreport.sql -rw-r-r- 1 oracle other 2191 Jun 15 2000 sptrunc.sql -rw-r-r- 1 oracle other 30133 Jun 15 2000 spup816.sql $ 接下來(lái)我們就可以開(kāi)始安裝Statspack 了。在Oracle8.1.6 版本中運(yùn)行statscre.sql; 在Oracle8.1.7 版本中運(yùn)行spcreate.sql。 這期間會(huì)提示你輸入缺省表空間和臨時(shí)表空間的位置,輸入我們?yōu)?perfstat 用戶創(chuàng)建的表空間和你的臨時(shí)表空間。安裝腳本會(huì)自動(dòng)創(chuàng)建perfstat 用戶。 $ sqlplus SQL*Plus: Release .0 - Production on Sat Jul 26 16:27:31 2003 (c) Copyright 2000 Oracle Corporation. All rights reserved. Enter user-name: internal Connected to: Oracle8i Enterprise Edition Release .0 - Production With the Partitioning option JServer Release .0 - Production SQL SQL spcreate . Installing Required Packages Package created. Grant succeeded. View created. Package body created. Package created. Synonym dropped. Synonym created. Specify PERFSTAT users default tablespace Enter value for default_tablespace: perfstat Using perfstat for the default tablespace User altered. User altered. Specify PERFSTAT users temporary tablespace Enter value for temporary_tablespace: temp Using temp for the temporary tablespace User altered. NOTE: SPCUSR complete. Please check spcusr.lis for any errors. 如果安裝成功，你可以接著看到如下的輸出信息： . Creating Package STATSPACK. Package created. No errors. Creating Package Body STATSPACK. Package body created. No errors. NOTE: SPCPKG complete. Please check spcpkg.lis for any errors. 可以查看.lis 文件查看安裝時(shí)的錯(cuò)誤信息。 步驟四： 如果安裝過(guò)程中出現(xiàn)錯(cuò)誤，那么可以運(yùn)行spdrop.sql 腳本來(lái)刪除這些安裝腳本建立的對(duì)象。然后重新運(yùn)行spcreate.sql來(lái)創(chuàng)建這些對(duì)象。 SQL spdrop Dropping old versions (if any) Synonym dropped. Sequence dropped. Synonym dropped. Table dropped. Synonym dropped. View dropped. NOTE: SPDUSR complete. Please check spdusr.lis for any errors. （以上的安裝過(guò)程描述是在 HP 11.11 + Oracle 8.1.7 平臺(tái)上得到的） 2.1.2 測(cè)試statspack 運(yùn)行statspack.snap 可以產(chǎn)生系統(tǒng)快照，運(yùn)行兩次，然后執(zhí)行spreport.sql 就可以生成一個(gè)基于兩個(gè)時(shí)間點(diǎn)的報(bào)告。 如果一切正常，說(shuō)明安裝成功。 SQLexecute statspack.snap PL/SQL procedure successfully completed. SQLexecute statspack.snap PL/SQL procedure successfully completed. SQLspreport.sql 可是有可能你會(huì)得到以下錯(cuò)誤： SQL exec statspack.snap; BEGIN statspack.snap; END; * ERROR at line 1: ORA-01401: inserted value too large for column ORA-06512: at PERFSTAT.STATSPACK, line 978 ORA-06512: at PERFSTAT.STATSPACK, line 1612 ORA-06512: at PERFSTAT.STATSPACK, line 71 ORA-06512: at line 1 這是Oracle 的一個(gè)Bug，Bug 號(hào)1940915。 該Bug 自 后修正。 這個(gè)問(wèn)題只會(huì)出現(xiàn)在多位的字符集, 需要修改spcpkg.sql 腳本，$ORACLE_HOME/rdbms/admin/spcpkg.sql，將substr 修改為 substrb，然后重新運(yùn)行該腳本。 該腳本錯(cuò)誤部分： select l_snap_id , p_dbid , p_instance_number , substr(sql_text,1,31) substr 會(huì)將多位的字符, 當(dāng)作一個(gè)byte.substrb 則會(huì)當(dāng)作多個(gè)byte。在收集數(shù)據(jù)時(shí)， statpack 會(huì)將 top10 的 sql 前 31 個(gè)字節(jié) 存入數(shù)據(jù)表中,若在SQL 的前31 個(gè)字有中文，就會(huì)出現(xiàn)此錯(cuò)誤。 注意：運(yùn)行 spcpkg.sql 也需要以 internal 用戶登錄 sqlplus 2.1.3 生成statspack報(bào)告 調(diào)用spreport.sql 可以生成分析報(bào)告： 當(dāng)調(diào)用spreprot.sql 時(shí)，系統(tǒng)首先會(huì)查詢快照列表，然后要求你選擇生成報(bào)告的開(kāi)始快照ID(begin_snap)和結(jié)束快照ID(end_snap),生成一個(gè)報(bào)告. 為了生成一個(gè)report,我們至少需要兩次采樣: SQL spreport DB Id DB Name Inst Num Instance - - - - 2749170756 RES 1 res Completed Snapshots Snap Snap Instance DB Name Id Snap Started Level