oracle.Performance.Tuning筆記

oracle.Performance.Tuning筆記
第二章 The Method Behind the Madness
當出現(xiàn)性能問題，調(diào)查v$system_event,v$session_event,v$session_wait是個好的習(xí)慣。

在時間點1創(chuàng)建1個快照：create table BEGIN_SYS_EVENT as select from v$system_event; 在時間點2創(chuàng)建1個快照：create table END_SYS_EVENT as select from v$system_event; 然后通過下面查詢就可知道這段時間的主事件：
select T1.event,(T2.total_waits-T1.total_waits) “total waits”, (T2.total_timeouts-T1.total_timeouts) “total timeouts”,(T2.time_waited-T1.time_waited) “time waited”,(T2.average_wait-T1.average_wait) “average wait” form BEGIN_SYS_EVENT T1, END_SYS_EVENT T2 where T1.event = T2.event;所
通過上面的查詢，查看等待事件最多的事件，并把它們歸類。是基于I/O的事件？象db file scattered read、db file sequential read或者是free buffer waits。還是基于內(nèi)存的事件，象buffer busy waits。
然后進步下鉆，根據(jù)查詢v$system_event得來得有可能影響系統(tǒng)性能的系統(tǒng)級事件，從 v$session_event去查詢同樣的事件，和v$session聯(lián)合使用：
select S.username, S.program, SE.Total_Timeouts,SE.Time_Waited, SE.Average_Wait from v$session S, v$session_event SE where S.sid = SE.sid and SE.event not like ‘SQL*Net%’ and S.status = ‘ACTIVE’ and S.username is not null;
很有可能那些系統(tǒng)級的等待事件就是單一會話或幾個會話引起的。

對很大的表，或者resource比較緊張，用 estimate來收集統(tǒng)計信息比較合適。

對library cache里的分享SQL，上次parse過了，如果底層數(shù)據(jù)表改變結(jié)構(gòu)了，或者它們的statistics重新收集了，那么它們會標識成invalid，如果后來執(zhí)行，會重新parse.

當1個會話里重用同個SQL，不關(guān)閉cursor，also不需軟解析（soft parse）,減少軟解析和減少硬解析相同，對提高性能有好處。

第八章 Database Tuning(配置數(shù)據(jù)庫塊尺寸)
確定數(shù)據(jù)庫的BLOCK SIZE！最優(yōu)化的數(shù)據(jù)庫塊尺寸是數(shù)據(jù)庫中非常重要的1件事?。∮媱潝?shù)據(jù)塊尺寸時，考慮下面幾方面：
1． 1個數(shù)據(jù)塊的I/O處理花多長時間
2． 數(shù)據(jù)庫的buffer cache的尺寸和usage
3． 大的index塊對系統(tǒng)有何影響
4． 1行超過了1個數(shù)據(jù)塊有什么影響
5． 1個塊里剩下的空間較小有什么影響
6． 當update時增加了行的長度而影起行遷徙，有什么影響
7． 行遷徙、鏈接對I/O性能的影響

如果是象數(shù)據(jù)倉庫系統(tǒng)，主要提供讀的數(shù)據(jù)庫系統(tǒng)，應(yīng)該配置那個Oracle版本支持的最大塊尺寸！對其他系統(tǒng)，最好把數(shù)據(jù)塊尺寸和操作系統(tǒng)塊尺寸配置相同大小！

Initrans：對數(shù)據(jù)塊缺省是1，對index塊缺省是2。每個oracle block有塊頭區(qū)域，放置了1個表字典和1個行字典和事務(wù)槽（transaction slot）, 當1個事務(wù)要修改塊里的1行或多行前，事務(wù)用事務(wù)槽來確定該塊是否屬于自己。在oracle的行級鎖和讀一致視圖技術(shù)里，這些槽擔任主要角色！當事務(wù)要修改塊里的行時，他首先要在塊頭里的可用事務(wù)槽里“簽名”！然后事務(wù)修改的每行的鎖字節(jié)被設(shè)置，標識那些行被鎖定了。如果只1個槽或槽不夠用，當另外1個事務(wù)需修改同個塊里的其它行時（insert、update、delete），就需要去block的pctfree里借24字節(jié)來分配1個槽在block header area。如果這種情況經(jīng)常發(fā)生就會造成：
1． 動態(tài)的分配槽，會降低性能，因為oracle要去進行塊級別的空間管理，而不是僅僅修改塊里的數(shù)據(jù)。
2． 因為需到pctfree里借24字節(jié)去動態(tài)產(chǎn)生槽，如果很多事務(wù)產(chǎn)生這種情況，會占比較大的空間，因為借的空間不會釋放，pctfree的計算就會有誤，潛在產(chǎn)生行遷移。
maxtrans：定義1個塊可同時進行修改塊里數(shù)據(jù)的最多事務(wù)，缺省值是255。但不意味著每個塊能真正支持255個事務(wù)，當對塊的并發(fā)事務(wù)時，需要事務(wù)槽被分配。

Freelists：存在表所在的段頭（segment header）,可用的塊在這個鏈表里，如果塊滿了，就會成這里摘掉。基本上1個buffer busy waits event表示有freelists競爭發(fā)生。

表空間規(guī)劃

如果表空間里所有的的擴展相同，不會產(chǎn)生碎片！

1．對表空間，創(chuàng)造4個邏輯buckets：small、medium、large和x-large。
2．把數(shù)據(jù)庫里的對象歸到上面4個buckets里去。即哪些是小對象，大對象及很大的對象。
3． 對每個buckets定義合適的表空間缺省存儲參數(shù)（default storage），確保initial和next相同，pctincrease為0，用上面確定的缺省存儲參數(shù)（default storage）新建表空間。

4．把合適的對象建在對應(yīng)的表空間里，不用storage存儲參數(shù)，使那些對象接受表空間的缺省存儲參數(shù)。但對那些并發(fā)很高或者特別大的對象再考慮用storage存儲參數(shù)單獨定義。
5．重復(fù)上面的4步去定義每1類對象（table、index、cluster）。

1個700G數(shù)據(jù)庫的例子：

Bucker Size Limit
Small Less than 64M
Medium Large than small,but less than 256M
Large Large than medium,but less than 1024M
X-Large Large than 1024M

Bucker Size for initial and next
Small 256K
Medium 1M
Large 4M
X-Large 16M

第九章 Parallel Query Tuning

3個途徑設(shè)置parallelism級別：
1． 表和index級別
2． 查詢是用parallel 提示
3． Oracle Base cpu個數(shù)，缺省使用級別
Create table test
( column1 number(4),
column2 number(4) ) parallel (degree 4)
查看級別：select degree from user_tables where table_name=’TEST’;
修改級別：alter table test parallel(degree 6);

create index myindex on test(column) storage(initial 1M next 1M) parallel (degree 4)

用SQL：
select / + parallel (alias,6) / coluns from test alias;
select / + noparallel / coluns from test; //取消表上的并行級別

select statistic,value from v$pq_sysstat;
select * from v$pq_sesstat;

第十章 contention tuning
多版本讀一致
每個block的header area里的事務(wù)槽（transaction slots）也叫做interested transaction lists(ITL)，TIL包含3個重要的結(jié)構(gòu)：事務(wù)ID（transaction ID）－》TID，Undo Blcok Address（UBA，告訴before image存在哪個地方），如果事務(wù)提交了，還包含SCN。事務(wù)修改了塊里數(shù)據(jù)后，就把它自己的事務(wù)ID存在事務(wù)槽里，對應(yīng)的服務(wù)進程（servicing the transaction）然后把要修改的列的原來的值(before image) 拷貝到該事務(wù)指派的回滾段，一旦數(shù)據(jù)修改了，事務(wù)槽不馬上清除，這個工作留給以后讀這個塊的進程，這種也叫做塊延遲清除（delayed block cleanout）。
block的header area也包含一個system change number(不要和system commit number混淆)和一個序列號（sequence number），該序列號用來確定塊的版本。當進行了數(shù)據(jù)修改，獲得一個新SCN且序列號為一，每次行更新，該序列號加1，直到commit了或者序列號達到了254（序列號達到254，必須重新獲取SCN）。和數(shù)據(jù)塊的事務(wù)槽一樣，每個回滾段在段的首塊（the header block）保存了一個事務(wù)表，這個回滾段的事務(wù)表包含使用該回滾段的事務(wù)信息，也包含了用于那個事務(wù)的最后一個回滾塊的數(shù)據(jù)塊地址（DBA）。
當一個查詢開始時，服務(wù)進程獲得一個當前數(shù)據(jù)庫的SCN，當服務(wù)進程讀一個數(shù)據(jù)塊，它檢查在ITL里的system change number，確定該塊是否讀一致，如果這個塊的ITL包含一個更高的number，它就知道了在查詢開始后，這個塊已經(jīng)被改變且提交了，然后服務(wù)進程就必須要到回滾段里去找該塊的讀一致（和查詢開始時相同的SCN）版本。服務(wù)進程如果去找到那個一致版本？它從那個塊的ITL里得到一串transaction Id，然后根據(jù)該事務(wù)ID到對應(yīng)得一個或多個回滾段里（可能該塊有多個事務(wù)發(fā)生了）得到before image。事務(wù)ID很重要，所以不會從ITL里刪除。
另外一種情況是在ITL里得system change number比查詢時的SCN小，這表示在查詢開始時數(shù)據(jù)提交了，這時，服務(wù)進程就直接讀，它是current state。
最后一種情況是在ITL里面沒有system change number，服務(wù)進程讀回滾段頭發(fā)現(xiàn)所以事務(wù)都提交了，如果當前SCN是存在回滾段頭里的事務(wù)表里，Oracle把該SCN 拷貝到ITL里，然后再來讀該塊。但是如果沒有找到當前SCN，Oracle將用當前的數(shù)據(jù)塊版本和存儲在回滾段里的塊來制造一個數(shù)據(jù)塊的讀一致版本。存儲在回滾段里的before image用來重建那個塊的數(shù)據(jù)，oracle搜尋每個回滾段header block的事務(wù)表，查看有沒有ITL里的事務(wù)ID。如果因為一些原因，服務(wù)進程不能重建讀一致image（before image在回滾段里沒存在），查詢就會失敗，報“ORA-01555－Snapshot too old”錯誤。
注意：當多個事務(wù)同時讀和寫同一個數(shù)據(jù)塊，將會在buffer cache里有同一個數(shù)據(jù)塊的多個版本。Oracle可能必須要重建這個數(shù)據(jù)塊的before image幾次，這叫做塊克隆。Buffer cache里的數(shù)據(jù)塊通過hash table來訪問，同一個塊的多個克隆是同一個hash地址（無論有多少個克隆，塊的物理地址不會改變）。非常多的塊克隆會引起“cache buffers chains latch”。

回滾段競爭
每個事務(wù)寫回滾段里預(yù)分配的擴展是依次和循環(huán)進行的。當一個擴展已經(jīng)被回滾條目填滿了，Oracle繼續(xù)寫下一個可用的擴展。同一個事務(wù)寫before image達到擴展的邊界的次數(shù)就叫wrap，在V$ROLLSTAT里，列wraps就表示當事務(wù)寫before image時，跨越extent的次數(shù)。
寫回滾段也是在buffer cache里完成的，因為在回滾段能被操作前，需要把回滾段讀到內(nèi)存里去?；貪L段的首塊（header block）包含了事務(wù)表，這個header block里的事務(wù)表包含了段里所以活動的事務(wù)信息，會在內(nèi)存里駐留很長時間，如果事務(wù)很多，它就會被經(jīng)常訪問，修改，所以在這個回滾段頭塊就會產(chǎn)生競爭。
怎樣發(fā)現(xiàn)在數(shù)據(jù)庫里有回滾段的競爭？由于buffer cache里的回滾段也是來自于數(shù)據(jù)庫的buffer cache，所以如果有競爭，也反映的是“buffer busy waits”事件?？梢圆樵僾$system_event,v$session_event,v$session_wait聯(lián)合 dba_data_files，dba_extents和uet$去確定段名。查詢v$waitstat去檢測回滾塊等待：
select * from v$waitstat where class in (‘undo header’,‘undo block’);
class count time
undo header 42331 1922
undo block 32541 1132

過段時間再查詢，如果值都增加了，說明有競爭。

臨時段競爭
當排序操作開始時，它會首先創(chuàng)造臨時段，排序完后，就會刪除該臨時段。創(chuàng)造和刪除數(shù)據(jù)庫里的任何段，進程都需要獲取一個“space management transaction(ST) enqueue”，在數(shù)據(jù)庫里只有一個ST enqueue。所以會引發(fā)臨時段競爭。
查詢v$sort_segment可以幫助決定臨時表空間的大小。它可以報告數(shù)據(jù)庫里所有排序操作曾經(jīng)用過的最大空間。

Latches
Latch是一個被用來串行化訪問SGA里的分享數(shù)據(jù)的串行化鎖機制。3個需要關(guān)注的latch：cache buffers lru chain, redo copy, cache buffers chains。

第十一掌 I/O tuning
? RAID1+0：對于讀寫較大的都比較合適
? RAID5：對于讀較大的比較合適，但寫比較大的不合適
? 文件系統(tǒng)和RAW：Veritas的Quick I/O或者其他系統(tǒng)支持的direct I/O可以越過操作系統(tǒng)的緩存，直接對磁盤讀寫也都不比用 RAW差，并且易于管理。

第十二章 Operating System Tuning
配置內(nèi)存
對于主頻少于500MHz的cpu，每個cpu至少配512M內(nèi)存。如果大于500MHz，至少每個cpu配1G內(nèi)存。

配置內(nèi)核
兩個需要調(diào)整的重要的UNIX資源是：shared memory segments和semaphores。AIX不能調(diào)整shared memory segments和semaphores。下面列出可以調(diào)整的各種變量：
? shared memory segments：

SHMMAX：一個單一最大分享內(nèi)存段，單位是字節(jié)，Oracle首先去獲取充足大的分享內(nèi)存段以適合SGA，如果沒有，就請求多個分享內(nèi)存段做為SGA的每個組件。但是，必須要一個充足大的連續(xù)分享內(nèi)存段給最大的shared pool。

SHMMIN：最小的分享內(nèi)存段，這個參數(shù)可以忽略。

SHMMNI：設(shè)置分享內(nèi)存段的最多個數(shù)，一般缺省就可以了。

SHMSEG：設(shè)置一個處理器能使用的分享內(nèi)存段的最多個數(shù)。

? Semaphore：

SEMMNS：設(shè)置系統(tǒng)可用的最多semaphore個數(shù)。

SEMMNI：最大的semaphore集(set)，UNIX分配semaphore是以集為單位。

SEMMSL：semaphore set 里最多的semaphore個數(shù)。

? 其它需要配置的：(可能每種unix不同)

maxusers：OS級別的最多的用戶session。它會影響其它的參數(shù)，如：npro,nfile,maxuprc。

npro：內(nèi)核能支持的最多進程個數(shù)。包括用戶進程和oracle后臺進程。應(yīng)該把它設(shè)置的比SEMMNS要高。
             nfile：內(nèi)核能支持的最多打開文件的個數(shù)，可以把它設(shè)高點，因為它需要的內(nèi)存較小。
        maxuprc： 每個用戶能有的最多進程個數(shù)。一般，oracle相關(guān)的進程，后臺進程等都是屬于oracle用戶。

異步IO
asynchronous寫磁盤的時候，不等寫完就返回。操作系統(tǒng)設(shè)置DISK_ASYNCH_IO，如果設(shè)置了異步IO，把db_writer_processes設(shè)置成1，不要設(shè)置dbwr_io_slaves。Aix 可以用lsattr –E –l aio0來查看異步IO。