Oracle 9i效能調整 [ZT]

Oracle 9i效能調整
/* *
作者:歐朝敬
QQ:35712069
手機:13873195792
請轉載者不要更改原版內容
 */

1、設定合適的SGA
　　常常有人抱怨伺服器硬體很好，但是Oracle就是很慢。很可能是記憶體分配不合理造成的。
　　(1)假設
記憶體有512M，這通常是小型應用。建議Oracle的SGA大約240M，其中：共享池（SHARED_POOL_SIZE）可以設定60M到80M，
根據實際的使用者數、查詢等來定。資料塊緩衝區可以大致分配120M-150M，8i下需要設定
DB_BLOCK_BUFFERS，DB_BLOCK_BUFFER*DB_BLOCK_SIZE等於資料塊緩衝區大小。9i
下的資料緩衝區可以用db_cache_size來直接分配。
　　(2)假設記憶體有1G，Oracle 的SGA可以考慮分配500M：共享池分配100M到150M，資料緩衝區分配300M到400M。
　　(3)記憶體2G，SGA可以考慮分配1.2G，共享池300M到500M，剩下的給資料塊緩衝區。
(4)
記憶體2G以上：共享池300M到500M就足夠啦，再多也沒有太大幫助；(Biti_rainy有專述)資料緩衝區是儘可能的大，但是一定要注意兩個問
題：一是要給作業系統和其他應用留夠記憶體，二是對於32位的作業系統，Oracle的SGA有1.75G的限制。有的32位作業系統上可以突破這個限制，
方法還請看Biti的大作吧。
建議在設定引數的同時，init 中使用 lock_sga ，在不同的平臺上可能有不同的方式，使得SGA 鎖定在實體記憶體中而不被放入 SWAP 中，這樣對效率有好處。
lock_sga = true 的問題
由
於幾乎所有的作業系統都支援虛擬記憶體，所以即使我們使用的記憶體小於實體記憶體，也不能避免作業系統將SGA
換到虛擬記憶體（SWAP）。所以我們可以嘗試使得SGA
鎖定在實體記憶體中不被換到虛擬記憶體中，這樣減少頁面的換入和換出，從而提高效能。但在這裡遺憾的是，windows 是無法避免這種情況的。
WINDOWS
不能設定lock_sga=true(一個根據平臺而定的引數, 如果該引數為 TRUE, 將把所有 SGA 頁裝載到記憶體中,
以便使該例程迅速達到最佳效能狀態。這將增加例程啟動和使用者登入的時間,
但在記憶體充足的系統上能減少缺頁故障的出現。),可以通過設定pre_page_sga=true,使得資料庫啟動的時候就把所有記憶體頁裝載，這樣可能起
到一定的作用。
警告：
當你把DB_CACHE_ADVICE設定為ON(預設值)時，Oracle會從共享的池中“竊取”RAM頁面，這往往嚴重影響到了庫cache。例如，如果設定DB_CACHE_SIZE為500m，Oracle就會從共享池中竊取相當多數量的RAM空間。
為了避免這個問題，我們應該在INIT.ORA檔案中把DBA設定為DB_CACHE_ADVICE=READY。這樣，Oracle會在資料庫啟動時預分配RAM記憶體。
SGA的各個組成部分
事實上，通常我們更習慣通過直觀的公式化來表達這樣的問題：
OS
使用記憶體+SGA+併發執行程式數(使用Select Value From V$parameter Where Name Like
'processes%'檢視其值,
session預設為1.1*processes+5)*(sort_area_size+hash_ara_size+2M) <
0.7*總記憶體 (公式是死的，系統是活的，實際應用的調整不必框公式，這不過是一個參考建議)
shared_pool_size共享池 + data buffer緩衝區快取記憶體 +large_pool_size 大型池+ java_pool_size Java池=SGA
併發執行程式數*(sort_area_size+hash_ara_size+2M) =PGA(在Oracle9i中)

可以看出，設定SGA時基本上應該掌握的原則是：
    data buffer 一般可以儘可能的大
    shared_pool_size 應該適度
    log buffer 在 1MB 以內就可以了

還有2 個重要引數我們需要注意：
sort_area_size
hash_area_size
這兩個引數在非MTS 下都是屬於PGA ，不屬於SGA。它是為每個session 單獨分配的，在我們的伺服器上除了OS + SGA，一定要考慮這兩部分。

一般的記憶體分配原則：
聯機事物處理OLTP：SGA ：65%；PGA：15%；OS：20%
資料倉儲和決策支援DSS：SGA：30%；PGA：50%；OS：20%
另一種分配方式：SGA：50%；PGA：35%；OS：15%
本文中的分配方式：SGA：50%；PGA：30%；OS：20%
其中SGA中的分配方式：
Buffer Cache：80% ；Share Pool：15% ；Other：5%或
Buffer Cache：80% ；Share Pool：12% ；Other：8%

例如：2g的windows的平臺，os 300m，sag 1.2g,  pga 500m
原則：SGA+PGA+OS使用記憶體A、如果512M RAM  建議 shared_pool_size = 50M, data buffer = 200M
B、如果1G RAM   建議 shared_pool_size = 100M , data buffer = 400M
C、如果2G RAM   建議 shared_pool_size = 200M , data buffer= 800M

假定oracle是 32 bit ,伺服器RAM大於2G ，注意你的PGA的情況，,則建議
再具體化，如果512M RAM 建議 shared_pool_size = 50M, data buffer = 200M
如果1G RAM shared_pool_size = 100M , data buffer = 500M
如果2G RAM shared_pool_size = 150M ,data buffer = 1.2G
實體記憶體再大已經跟引數沒有關係了
假定64 bit ORACLE
記憶體4G  shared_pool_size = 200M , data buffer = 2.5G
記憶體8G  shared_pool_size = 300M , data buffer = 5G
記憶體 12G shared_pool_size = 300M-----800M , data buffer = 8G

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 的不同平臺和不同版本下可能不一樣，但對於確定環境是一個固定的值，裡面儲存了SGA 各部分元件的資訊，可以看作引導建立SGA的區域。
Variable Size
        包含了shared_pool_size、java_pool_size、large_pool_size 等記憶體設定
Database Buffers
       
指資料緩衝區，在8i
中包含db_block_buffer*db_block_size、buffer_pool_keep、buffer_pool_recycle
三部分記憶體。在9i 中包含db_cache_size、db_keep_cache_size、db_recycle_cache_size、
db_nk_cache_size。
Redo Buffers
      
指日誌緩衝區，log_buffer。在這裡要額外說明一點的是，對於v$parameter、v$sgastat、v$sga查詢值可能不一樣。v$
parameter 裡面的值，是指使用者在初始化引數檔案裡面設定的值，v$sgastat是oracle
實際分配的日誌緩衝區大小（因為緩衝區的分配值實際上是離散的，也不是以block 為最小單位進行分配的），v$sga
裡面查詢的值，是在oracle 分配了日誌緩衝區後，為了保護日誌緩衝區，設定了一些保護頁，通常我們會發現保護頁大小是8k(不同環境可能不一樣)。
對於一個Oracle應用系統來說，SGA決不是越大越好，這就需要尋找一個系統優化的平衡點。
關
於資料庫有多少併發連線，這實際上關係到PGA
的大小（共享伺服器模式（MTS）下還有large_pool_size）。事實上這個問題應該說還跟OLTP 型別或者OLAP
型別相關。對於OLTP型別oracle 傾向於可使用MTS,對於OLAP 型別使用獨立模式，同時OLAP
還可能涉及到大量的排序操作的查詢，這些都影響到我們記憶體的使用。那麼所有的問題綜合起來，實際上主要反映在UGA(使用者全域性區)的大小上。UGA主要包
含以下部分記憶體設定
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>
在這部分記憶體中我們最關注的通常是sort_area_size，這是當查詢需要排序的時候，資料庫會話將使用這部分記憶體進行排序，當記憶體大小不足的時候，使用臨時表空間進行磁碟排序。由於磁碟排序效率和記憶體排序效率相差好幾個數量級，所以這個引數的設定很重要。
當
出現大量排序時的磁碟I/O操作時，可以考慮增加sort_area_size的值。sort_area_size是Oracle用於一次排序所需的最大
記憶體數，在排序結束但是結果列返回之前，Oracle會釋放sort_area_size大小的記憶體，但是會保留
sort_area_retained_size大小的記憶體，知道最後一行結果列返回以後，才釋放所有的記憶體。
會導致排序的語句有 SELECT DISTINCT , MINUS , INTERSECT , UNION 和 min()、max()、count() 操作；而不會導致排序的語句有 UPDATE , 帶BETWEEN子句的SELECT 等等。
排序是一項花銷很大的操作，而且對效能的影響程度也較大，因此使大部分排序在記憶體中完成，而不是在磁碟上進行，這是至關重要的。
識別排序量的大小，就是要確定記憶體中排序的量和磁碟上排序的量，可用如下語句查詢：
SELECT NAME,VALUE FROM V$SYSSTAT WHERE Name IN ('sorts (memory)', 'sorts (disk)');
其
中“sorts(memory)”選項表示不需要磁碟I/O，選項“sorts(disk)”
表示需要磁碟I/O。如果使用者認為在磁碟上的排序意義較大，可以增加init.ora檔案SORT_AREA_SIZE引數的設定值。一般不調整該引數，
除非排序量很大時才調整。
這四個引數都是針對會話進行設定的，是單個會話使用的記憶體的大小，而不是整個資料庫使用的。偶爾會看見有人誤解了這個參
數以為是整個資料庫使用的大小，這是極其嚴重的錯誤。假如設定了MTS，則UGA(使用者全域性區)被分配在large_pool_size，也就是說放在了
共享記憶體裡面，不同程式（執行緒）之間可以共享這部分記憶體。
在這個基礎上，我們假設資料庫存在併發執行server process 為100
個，根據上面我們4 個引數在oracle8.1.7 下的預設值，我們來計算獨立模式下PGA
的大致大小。由於會話並不會經常使用create_bitmap_area_size
、bitmap_merge_area_size，所以我們通常不對四個引數求和。在考慮到除這四個引數外會話所儲存的變數、堆疊等資訊，我們估計為
2M，則200 個程式最大可能使用200M 的PGA。
現在，根據上面這些假定，我們來看SGA 實際能達到多少記憶體。在1G
的記憶體的伺服器上，我們能分配給SGA 的記憶體大約為400—500M。若是2G 的記憶體，大約可以分到1G的記憶體給SGA，8G
的記憶體可以分到5G的記憶體給SGA。當然我們這裡是以預設的排序部分記憶體sort_area_size=64k進行衡量的，假如我們需要調大該引數和
hash_area_size等引數，然後我們應該根據併發的程式的數量，來衡量考慮這個問題。

各個引數的設定
如果SGA_MAX_SIZE>128M建議以下各值都是16的整數倍，SGA_MAX_SIZE<128M，則是4的整數倍。
那麼SGA中的各個引數具體應該按照什麼樣的原則來設定呢，下面進行討論：
log_buffer
對於日誌緩衝區的大小設定，通常我覺得沒有過多的建議，因為參考LGWR寫的觸發條件之後，我們會發現通常超過3M意義不是很大。作為一個正式系統，可能考慮先設定這部分為log_buffer=1—3M 大小，然後針對具體情況再調整。
large_pool_size大型池
對
於大緩衝池的設定，如果不設定MTS，通常在 RMAN 、OPQ 會使用到，但是在10M --50M 應該差不多了。假如設定 MTS，則由於
UGA (使用者全域性區)放到large_pool_size的緣故，這個時候依據 session 最大數量和 sort_ares_size
等引數設定，必須增大large_pool_size 的設定，可以考慮為 session * (sort_area_size +
2M)。這裡要提醒一點，不是必須使用MTS，我們都不主張使用MTS，尤其同時線上使用者數小於500 的情況下。
java_pool_size Java池
假如資料庫沒有使用JAVA，我們通常認為保留10—20M大小足夠了。事實上可以更少，甚至最少只需要32k，但具體跟安裝資料庫的時候的元件相關(比如http server)。
shared_pool_size共享池
這
是迄今為止最具有爭議的一部分記憶體設定。按照很多文件的描述，這部分內容應該幾乎和資料緩衝區差不多大小。但實際上情況卻不是這樣的。首先我們要考究一個
問題，那就是這部分記憶體的作用，它是為了快取已經被解析過的SQL，而使其能被重用，不再解析。這樣做的原因是因為，對於一個新的SQL
（shared_pool
裡面不存在已經解析的可用的相同的SQL），資料庫將執行硬解析，這是一個很消耗資源的過程。而若已經存在，則進行的僅僅是軟分析（在共享池中尋找相同
SQL），這樣消耗的資源大大減少。所以我們期望能多共享一些SQL，並且如果該引數設定不夠大，經常會出現ora-04031錯誤，表示為了解析新的
SQL，沒有可用的足夠大的連續空閒空間，這樣自然我們期望該引數能大一些。但是該引數的增大，卻也有負面的影響，因為需要維護共享的結構，記憶體的增大也
會使得SQL 的老化的代價更高，帶來大量的管理的開銷，所有這些可能會導致CPU 的嚴重問題。
在一個充分使用繫結變數的比較大的系統
中，shared_pool_size 的開銷通常應該維持在300M 以內。除非系統使用了大量的儲存過程、函式、包，比如oracle erp
這樣的應用，可能會達到500M甚至更高。於是我們假定一個1G記憶體的系統，可能考慮設定該引數為100M，2G 的系統考慮設定為150M,8G
的系統可以考慮設定為200—300M。
對於一個沒有充分使用或者沒有使用繫結變數系統，這可能給我們帶來一個嚴重的問題。所謂沒有使用bind var 的SQL，我們稱為Literal SQL。也就是比如這樣的兩句SQL我們認為是不同的SQL,需要進行2 次硬解析：
select * from EMP where name = ‘TOM’;
select * from EMP where name = ‘JERRY’;
假
如把 ’TOM’ 和 ’JERRY’ 換做變數V，那就是使用了bind var，我們可以認為是同樣的SQL 從而能很好地共享。共享SQL
本來就是shared_pool_size 這部分記憶體存在的本意，oracle的目的也在於此，而我們不使用bind var
就是違背了oracle
的初衷，這樣將給我們的系統帶來嚴重的問題。當然，如果通過在作業系統監控，沒有發現嚴重的cpu問題，我們如果發現該共享池命中率不高可以適當的增加
shred_pool_size。但是通常我們不主張這部分記憶體超過800M（特殊情況下可以更大）。
事實上，可能的話我們甚至要想辦法避免軟分析，這在不同的程式語言中實現方式有差異。我們也可能通過設定session_cached_cursors 引數來獲得幫助（這將增大PGA）
關於使用繫結變數的話題，在下面的應用優化中繼續討論。
Data buffer緩衝區快取記憶體
現
在我們來談資料緩衝區，在確定了SGA
的大小並分配完了前面部分的記憶體後，其餘的，都分配給這部分記憶體。通常，在允許的情況下，我們都嘗試使得這部分記憶體更大。這部分記憶體的作用主要是快取
DB BLOCK，減少甚至避免從磁碟上獲取資料，在8i中通常是由db_block_buffers*db_block_size
來決定大小的。如果我們設定了buffer_pool_keep 和buffer_pool_recycle，則應該加上後面這兩部分記憶體的大小。

32bit 與 64bit 對SGA的影響
為什麼在上面SGA大小設定的經驗規則中要分 32bit Oracle 和 64bit
Oracle
呢，是因為這關係到SGA大小的上限問題。在32bit的資料庫下，通常oracle只能使用不超過1.7G的記憶體，即使我們擁有12G的記憶體，但是我們
卻只能使用1.7G，這是一個莫大的遺憾。假如我們安裝64bit的資料庫,我們就可以使用很大的記憶體，幾乎不可能達到上限。但是64bit
的資料庫必須安裝在64bit 的作業系統上，可惜目前windows上只能安裝32bit的資料庫

9i中相關引數的變化
oracle的版本的更新，總是伴隨著引數的變化，並且越來越趨向於使得引數的設定更簡單，因為複雜的引數設定使得
DBA們經常焦頭爛額。關於記憶體這部分的變化，我們可以考察下面的引數。事實上在9i中資料庫本身可以給出一組適合當前執行系統的SGA相關部分的引數調
整值（參考V$
DB_CACHE_ADVICE、V$SHARED_POOL_ADVICE），關於PGA也有相關檢視V$PGA_TARGET_ADVICE 等。
Data buffer
9i
中保留了8i中的引數，如設定了新的引數，則忽略舊的引數。9i中用db_cache_size來取代8i中的db_block_buffers
*db_block_size， 用db_keep_cache_size 取代buffer_pool_keep,
用db_recycle_cache_size 取代buffer_pool_recycle；這裡要注意9i
中設定的是實際的快取大小而不再是塊的數量。另外9i新增加了db_nk_cache_size，這是為了支援在同一個資料庫中使用不同的塊大小而設定
的。對於不同的表空間，可以定義不同的資料塊的大小，而緩衝區的定義則依靠該引數的支援。其中n 可以為2、4、6、8、16
等不同的值。在這裡順便提及的一個引數就是db_block_lru_latches，該引數在9i中已經成為了保留引數，不推薦手工設定。
PGA
在9i
裡面這部分也有了很大的變化。在獨立模式下，9i已經不再主張使用原來的UGA相關的引數設定，而代之以新的引數。假如
workarea_size_policy=AUTO（預設），則所有的會話的UGA 共用一大塊記憶體，該記憶體由
pga_aggregate_target 設定。在我們根據前面介紹的方法評估了所有程式可能使用的最大PGA
記憶體之後，我們可以通過在初始化引數中設定這個引數，從而不再關心其他 ”*_area_size” 引數。
在Oracle 9i,PGA 被邏輯地分成兩個部分：
可調整區域，即SQL工作區，等等
不可調整區域，比如說sort area等等
為了調整PGA，我們需要介紹兩個新的引數：WORKAREA_SIZE_POLICY 和 PGA_AGGREGATE_TARGET。
WORKAREA_SIZE_POLICY
的值可以是AUTO 或者
MANUAL，該引數意味著是使用以前的方法管理PGA的使用（如，sort_area_size，hash_area_size等等），還是使用新的方
法來管理PGA的使用。如果沒有設定引數PGA_AGGREGATE_TARGET，則WORKAREA_SIZE_POLICY的預設值是
MANUAL。
PGA_AGGREGATE_TARGET引數意味著可以劃分到SGA中的最大的數量的記憶體(也就是Oracle想在所有會話中間
分配的會話PGA記憶體的總量)，它包括所有session的可調整和不可調整的部分，這個引數沒有預設值，當我們將
WORKAREA_SIZE_POLICY 設定為 AUTO之前，必須先設定PGA_AGGREGATE_TARGET引數。
我們可以通過下面的方式檢視PGA的使用：
SQL>SELECT SUM(PGA_USED_MEM), SUM(PGA_ALLOC_MEM), SUM(PGA_MAX_MEM) FROM V$PROCESS;
SUM(PGA_USED_MEM) SUM(PGA_ALLOC_MEM) SUM(PGA_MAX_MEM)
----------------- ------------------ ----------------
         14569292           28619756         28619756
其中：
PGA_USED_MEM：該程式當前使用的PGA大小；
    PGA_ALLOC_MEM：當前分配給該程式的總的PGA大小；
    PGA_MAX_MEM：曾經分配給該程式的最大的PGA大小
PGA參考值的設定為
select min(pga_target_for_estimate)  PGA的值 from v$pga_target_advice where estd_pga_cache_hit_percentage>95 /*pga快取記憶體命中率*/

SGA_MAX_SIZE
在9i中若設定了SGA_MAX_SIZE，則在總和小於等於這個值內，可以動態的調整資料緩衝區和共享池的大小

庫緩衝區的調整
庫緩衝區中包含私用和共享SQL和PL/SQL區，通過比較庫緩衝區的命中率決定它的大小。要調整庫緩衝區，必須首先了解該
庫緩衝區的活動情況，庫緩衝區的活動統計資訊保留在動態效能表v$librarycache資料字典中，可通過查詢該表來了解其活動情況，以決定如何調
整。
　　SELECT SUM(PINS), SUM(RELOADS),SUM(RELOADS)/SUM(PINS) FROM V$LIBRARYCACHE;
Pins
列給出SQL語句，PL/SQL塊及被訪問物件定義的總次數；Reloads列給出SQL
和PL/SQL塊的隱式分析或物件定義重灌載時在庫程式緩衝區中發生的錯誤。如果sum(reloads)/sum(pins)
≈0，則庫緩衝區的命中率合適；若sum(reloads)/sum(pins)>1, 則需調整初始化引數
shared_pool_size來重新調整分配給共享池的記憶體量。

資料字典緩衝區的調整
資料字典緩衝區包含了有關資料庫的結構、使用者、實體資訊。資料字典的命中率，對系統效能影響極大。資料字典緩衝區的使用情況記錄在動態效能表v$librarycache中，可通過查詢該表來了解其活動情況，以決定如何調整。
　　SELECT SUM(GETS), SUM(GETMISSES),SUM(GETMISSES)/SUM(GETS) FROM V$ROWCACHE;
　
　Gets列是對相應項請求次數的統計；Getmisses
列是引起緩衝區出錯的資料的請求次數。對於頻繁訪問的資料字典緩衝區，sum(getmisses)/sum(gets)<10%～15%。若大於
此百分數，則應考慮增加資料字典緩衝區的容量，即需調整初始化引數shared_pool_size來重新調整分配給共享池的記憶體量。

緩衝區快取記憶體的調整
使用者程式所存取的所有資料都是經過緩衝區快取記憶體來存取，所以該部分的命中率，對效能至關重要。緩衝區快取記憶體的使用情況記錄在動態效能表v$sysstat中，可通過查詢該表來了解其活動情況，以決定如何調整。
SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME IN ('db block gets', 'consistent gets', 'physical reads');
db
block gets(資料庫塊獲取)和consistent gets(一致性獲取)的值是請求資料緩衝區中讀的總次數(邏輯讀)。physical
reads的值是請求資料時引起從盤中讀檔案的次數(物理讀)。從緩衝區快取記憶體中讀的可能性的高低稱為緩衝區的命中率，計算公式：
　　Hit Ratio=1-(physical reds/(dbblock gets+consistent gets))
　
　如果Hit
Ratio<60%～70%，則應增大db_block_buffers的引數值。db_block_buffers可以調整分配給緩衝區快取記憶體
的記憶體量，即db_block_buffers可設定分配緩衝區快取記憶體的資料塊的個數。緩衝區快取記憶體的總位元組數=db_block_buffers的
值*db_block_size的值。 db_block_size 的值表示資料塊大小的位元組數，可查詢 v$parameter 表：
select name,value from v$parameter where name='db_block_size';
直接計算命中率使用以下語句
SELECT
SUM(DECODE(NAME, 'physical reads', VALUE, 0)) "physical reads",
SUM(DECODE(NAME, 'db block gets', VALUE, 0)) "db block gets",
SUM(DECODE(NAME,'consistent gets', VALUE,0)) "consistent gets",(1 -
SUM(DECODE(NAME, 'physical reads', VALUE, 0)) /(SUM(DECODE(NAME, 'db
block gets', VALUE, 0)) + SUM(DECODE(NAME, 'consistent gets', VALUE,
0))) )*100 "命中率%" FROM (SELECT 1 NO, NAME, VALUE FROM V$SYSSTAT WHERE
NAME IN ('db block gets', 'consistent gets', 'physical reads')) A GROUP
BY A.NO
　　在修改了上述資料庫的初始化引數以後，必須先關閉資料庫，在重新啟動資料庫後才能使新的設定起作用。

2、選用適合的ORACLE優化器
ORACLE的優化器共有3種:
a. RULE (基於規則) b. COST (基於成本) c. CHOOSE (選擇性)
設定預設的優化器,可以通過對init.ora檔案中OPTIMIZER_MODE引數的各種宣告,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS . 你當然也在SQL句級或是會話(session)級對其進行覆蓋.
為了使用基於成本的優化器(CBO,Cost-BasedOptimizer),你必須經常執行analyze命令,以增加資料庫中的物件統計資訊(objectstatistics)的準確性.
如果資料庫的優化器模式設定為選擇性(CHOOSE),那麼實際的優化器模式將和是否執行過analyze命令有關. 如果table已經被analyze過, 優化器模式將自動成為CBO , 反之,資料庫將採用RULE形式的優化器.
Oracle
預設優化模式是CHOOSE，在這種情況下，如果表沒有經過分析，經常導致查詢使用全表掃描，而不使用索引。這通常導致磁碟I/O太多，而導致查詢很慢。
如果沒有使用執行計劃穩定性，則應該把表和索引都分析一下，這樣可能直接會使查詢速度大幅提升。分析表命令可以用ANALYZE TABLE
分析索引可以用ANALYZE
INDEX命令。對於少於100萬的表，可以考慮分析整個表，對於很大的表，可以按百分比來分析，但是百分比不能過低，否則生成的統計資訊可能不準確。可
以通過DBA_TABLES字典的LAST_ANALYZED列來檢視錶是否經過分析或分析時間，索引可以通過DBA_INDEXES字典的
LAST_ANALYZED列。CBO是ORACLE推薦使用的優化方式，要想使用好CBO，使SQL語句發揮最大效能，必須保證統計資料的及時性。統計
資訊的生成可以有完全計演算法和抽樣估演算法。
ANALYZE_SCHEMA用於對某個使用者擁有的所有TABLE，INDEX和CLUSTER的分析統計。ANALYZE_DATABASE用於對整個資料庫進行分析統計。
例：EXECUTE DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE',null,100);其中SCOTT改成相應的使用者名稱,100%的抽樣百分比。(最好用下面兩種方式)
或exec dbms_stats.gather_schema_stats(ownname => 'DONGAN',estimate_percent =>100, cascade=> TRUE);估算統計資訊(100%分析)，連索引一起分析
或exec dbms_stats.gather_schema_stats(ownname => 'DONGAN', cascade =>true);由計算統計,加cascade =>true則連索引一起分析，否則只分析表
一般現在使用dbms_stats取代dbms_utility包進行資料庫統計資訊的分析。
以下兩個引數對於CBO的巨大影響：
OPTIMIZER_INDEX_CACHING
調
整基於成本的優化程式的假定值,這個初始化引數代表一個百分比,取值範圍在0到99之間.預設值是0，代表當CBO使用索引訪問資料時，在記憶體中發現資料
的比率是0%，這意味著通過索引訪問資料將需要產生物理讀取，代價昂貴。如果使用預設設定，Oracle評估成本的時候，很多時候就會錯誤的選擇全表掃
描。
OPTIMIZER_INDEX_COST_ADJ
這個初始化引數代表一個百分比,取值範圍在1到10000之間.
該參數列示索引掃描和全表掃描成本的表較。預設值100表示索引掃描成本等於全表掃描。optimizer_index_cost_adj引數值設小，使系統傾向於使用索引
這些引數對於CBO的執行具有重大影響，其預設值對於資料庫來說通常需要調整。
一般來說對於OPTIMIZER_INDEX_CACHING可以設定為90左右
對於大多數OLTP系統，OPTIMIZER_INDEX_COST_ADJ可以設定在10到50之間。對於資料倉儲和DSS系統，可能不能簡單的把OPTIMIZER_INDEX_COST_ADJ設定為50，通常我們需要反覆調整取得一個合理值.
OPTIMIZER_MAX_PERMUTATIONS
對
於多表連線查詢，如果採用基於成本優化(CBO)，ORACLE會計算出很多種執行方案，從中選擇出最優方案。這個引數就是設定oracle究竟從多少種
方案來選擇最優。如果設定太大，那麼計算最優方案過程也是時間比較長的。Oracle805和8i預設是80000，8建議改成2000。對於9i，已經
預設是2000了。
影響全表掃描的引數
 DB_FILE_MULTIBLOCK_READ_COUNT
 在涉及一個完全連續掃描的一次 I/O 操作過程中讀取的塊的最大數量
當
DB_FILE_MULTIBLOCK_READ_COUNT(一般為作業系統I/O大小除以DB_BLOCK_SIZE的值,現作業系統的I/O一般為
64KB，有的達到了1M)改大時(DB_FILE_MULTIBLOCK_READ_COUNT*DB_BLOCK_SIZE為作業系統I/O的整數
倍)，或檢視執行計劃表，明明有索引而不使用索引時，OPTIMIZER_INDEX_COST_ADJ建議改成10。
3、訪問Table的方式
ORACLE 採用兩種訪問表中記錄的方式:
a. 全表掃描
全表掃描就是順序地訪問表中每條記錄. ORACLE採用一次讀入多個資料塊(database block)的方式優化全表掃描.
b. 通過ROWID訪問表
你
可以採用基於ROWID的訪問方式情況,提高訪問表的效率, ,
ROWID包含了表中記錄的物理位置資訊..ORACLE採用索引(INDEX)實現了資料和存放資料的物理位置(ROWID)之間的聯絡.
通常索引提供了快速訪問ROWID的方法,因此那些基於索引列的查詢就可以得到效能上的提高.

4、共享SQL語句
為了不重複解析相同的SQL語句,在第一次解析之後,
ORACLE將SQL語句存放在記憶體中.這塊位於系統全域性區域SGA(system global area)的共享池(shared buffer
pool)中的記憶體可以被所有的資料庫使用者共享. 因此,當你執行一個SQL語句(有時被稱為一個遊標)時,如果它和之前的執行過的語句完全相同,
ORACLE就能很快獲得已經被解析的語句以及最好的執行路徑.
ORACLE的這個功能大大地提高了SQL的執行效能並節省了記憶體的使用.可惜的是ORACLE只對簡單的表提供高速緩衝(cache
buffering) ,這個功能並不適用於多表連線查詢.
資料庫管理員必須在init.ora中為這個區域設定合適的引數,當這個記憶體區域越
大,就可以保留更多的語句,當然被共享的可能性也就越大了.當你向ORACLE
提交一個SQL語句,ORACLE會首先在這塊記憶體中查詢相同的語句.這裡需要註明的是,ORACLE對兩者採取的是一種嚴格匹配,要達成共享,SQL語
句必須完全相同(包括空格,換行等).
共享的語句必須滿足三個條件:
A. 字元級的比較: 當前被執行的語句和共享池中的語句必須完全相同. 例如:
SELECT * FROM EMP;
和下列每一個都不同
SELECT * from EMP;   Select * From Emp;   SELECT * FROM EMP;
B. 兩個語句所指的物件必須完全相同: 例如:
使用者   物件名   如何訪問
Jack   sal_limit  private synonym
   Work_city  public synonym
   Plant_detail  public synonym
Jill    sal_limit  private synonym
   Work_city  public synonym
   Plant_detail  table owner
考慮一下下列SQL語句能否在這兩個使用者之間共享.
SQL 能否共享  原因
select max(sal_cap) from sal_limit;
不能     每個使用者都有一個private synonym - sal_limit , 它們是不同的物件
select count(*) from work_city where sdesc like 'NEW%';
能       兩個使用者訪問相同的物件public synonym - work_city
select a.sdesc,b.location from work_city a , plant_detail b where a.city_id = b.city_id
不能 使用者jack 通過private synonym訪問plant_detail 而jill 是表的所有者,物件不同.
C. 兩個SQL語句中必須使用相同的名字的繫結變數(bind variables) 例如：
第一組的兩個SQL語句是相同的(可以共享),而第二組中的兩個語句是不同的(即使在執行時,賦於不同的繫結變數相同的值)
a.
select pin , name from people where pin = :blk1.pin;
select pin , name from people where pin = :blk1.pin;
b.
select pin , name from people where pin = :blk1.ot_ind;
select pin , name from people where pin = :blk1.ov_ind;

5、調整資料庫時用到的相關操作
重建當前使用者下的所有索引
SELECT ' ALTER INDEX ' || T.INDEX_NAME || ' REBUILD;' FROM USER_INDEXES T;
SELECT ' ALTER INDEX ' || T.INDEX_NAME || ' REBUILD TABLESPACE 新表間名;' FROM USER_INDEXES T;
線上重建索引(online)
SELECT ' ALTER INDEX ' || T.INDEX_NAME || ' REBUILD ONLINE;' FROM USER_INDEXES T;
移動索引到新的表空間TBI_LONGHUI中
SELECT 'ALTER INDEX '|| T.INDEX_NAME ||' REBUILD TABLESPACE TBI_LONGHUI;' FROM USER_INDEXES T;
移動索引分割槽到新表空間
select t.index_name,t.partition_name,t.tablespace_name from user_ind_partitions t; 檢視索引分割槽
ALTER INDEX 索引名 REBUILD  PARTITION  分割槽名 TABLESPACE 新表空間;

移動表到新的表空間TBS_LONGHUI
ALTER TABLE  表名 MOVE TABLESPACE TBS_LONGHUI;
移動表分割槽到新的表空間
select segment_name,segment_type,tablespace_name  from dba_segments  where wner='SRD'; 檢視段所在的表空間
select t.table_name,t.partition_name,t.tablespace_name from user_tab_partitions t;檢視分割槽表所在的表空間
ALTER TABLE  表名 MOVE PARTITION 表分割槽名 TABLESPACE 新表空間;

重新編譯指定使用者下的所有儲存過程、函式和程式包，引數為使用者模式名
execute dbms_utility.compile_schema('SCOTT');

監控指定使用者下的所有索引
SELECT 'alter index ' || owner || '.' || index_name || ' monitoring usage;' FROM dba_indexes WHERE wner = 'SCOTT';
檢查使用狀態：select * from v$object_usage;
停止監控指定使用者下的所有索引
SELECT 'alter index ' || owner || '.' || index_name || ' nomonitoring usage;' FROM dba_indexes WHERE wner = 'SCOTT';

ANALYZE_SCHEMA用於對某個使用者擁有的所有TABLE，INDEX和CLUSTER的分析統計。
execute
dbms_utility.analyze_schema('LONGHUI','ESTIMATE',null,100);
100%的抽樣百分比(9i)。或exec dbms_stats.gather_schema_stats(ownname =>
'LONGHUI', cascade =>true);(8i)

analyze index 索引名 validate
structure;分析一個索引後在同一個會話中查詢index_stats才有資料select
(del_lf_rows_len/lf_rows_len)*100 浪費空間 from
index_stats;浪費空間大於20%的索引Oracle建議重建。

索引的分析
 使用ROWID獲取行的成本依賴於索引聚集因子(clustering
factor),儘管聚集因子是索引的一個屬性，它實際也關係到表資料塊中被索引的欄位值。一較低的聚集因子表明行被集中在表的少數塊裡，相反一個較高的
聚集因子表明行被隨機分散到表的資料塊中。因此，聚集因子過高意味著通過範圍掃描用ROWID獲取行成本會較高，因為需要訪問表中過多的塊才能返回資料。
視
圖USER_INDEXES有一列clustering_factor,我們稱為聚集因子，該列反映了資料相對於已索引的列是否有序。如果
clustering_factor列的值接近於索引中的樹葉塊(leaf
block)的數目，表中的資料就越有序，如果列的值接近於表中的行數(num_rows)，則表中的資料則不是很有序。高
clustering_factor 的數值達到表中的行數 (num_rows)，表明這些行的順序與索引中的順序不同，索引範圍掃描將會需要額外的
I/O。
BLEVEL 二元高度每增加一個級別,都會增加DML操作的效能開銷,重建索引來減小二元高度
Select i.index_name,i.blevel,i.clustering_factor,i.leaf_blocks,i.num_rows,i.last_analyzed 最近分析日期 from USER_INDEXES i;
 使用Oracle的Create Table As Select (CTAS) 語法來拷貝表格, 降低聚集因子
 使用create table tablename as select * from oldtable order by a,c 
Oracle在評估使用索引的代價（cost）時有兩個重要的資料：CF(Clustering factor) 和 FF(Filtering factor)。
CF： 所謂 CF， 可以理解為每讀入一個索引塊要對應讀入多少個資料塊。
FF： 所謂 FF， 就是SQL語句所選擇的結果集佔總的資料量的百分比。
一
般的估算公式是：FF * (CF + 索引塊個數) [備註：toms 說"the formula used by the CBO to
compute the cost is blevel + FF * leaf_blocks + FF *
clustering_factor"]由此估計出一個查詢如果使用某個索引會需要讀入的資料塊塊數。需要讀入的資料塊越多，則 cost
越大，Oracle 也就越有可能不選擇使用 index。
（全表掃描需要讀入的資料塊數等於該表的實際資料塊數）
其核心就是，CF可能會比實際的資料塊數量大。CF受到索引中資料的排列方式影響，通常在索引剛建立時，索引中的記錄與表中的記錄有良好的對應關係，CF 都很小；在表經過大量的插入/修改操作後，這種對應關係越來越亂，CF也越來越大。這個時候就需要DBA重建該索引。
如果某個SQL語句以前一直使用某個索引，突然有一天，你發現系統慢的不行了，檢查發現該SQL語句的某個索引用不上了：其中一個很大的可能就是 CF 已經變得太大，需要重新整理該索引了。
FF 則是Oracle 根據分析所做的估計。比如某表有50多萬行，其主鍵的最小值是1，最大值是500000，考慮以下sql 語句：
Select * from table_name where keyid>=1; 和
Select * from table_name where keyid>=500000;
這兩個表面看上去一樣的sql語句，對Oracle而言卻有巨大的差別。因為前者的FF是100%，而後者的FF可能只有 1%。如果它的CF大於實際的資料塊數，則Oracle可能會選擇完全不同的優化方式。

在可用的硬碟之間分佈關鍵資料檔案
    要特別注意的檔案：system表空間、TEMPORARY表空間、回滾段或UNDO表空間、聯機重做日誌檔案（最好放在最快的磁碟上）、放在ORACLE_HOME資料夾下的關鍵Oracle檔案、經常被訪問的表的資料檔案、經常被訪問的索引的資料檔案
    經驗總結：把上面提到的資料檔案分佈在各個可用的磁碟上
              把資料檔案和索引檔案分開放置
              對於經常連線的表，把他們的資料和索引表空間分開
              把控制檔案的多個備份儲存到不同的磁碟和控制器上
避免I/O磁碟爭用，用下面的查詢確定檔案的I/O問題
系統表：
v$datafile：儲存資料庫中資料檔案的資訊
v$filestat：儲存系統中訪問資料檔案的統計資訊
SELECT
DF.NAME 檔名, FS.PHYRDS 讀次數, FS.PHYWRTS 寫次數, (FS.READTIM /
DECODE(FS.PHYRDS, 0, -1, FS.PHYRDS)) 讀時間, (FS.WRITETIM
/DECODE(FS.PHYWRTS,0,-1,FS.PHYWRTS)) 寫時間
FROM V$DATAFILE DF, V$FILESTAT FS WHERE DF.FILE# = FS.FILE# ORDER BY FS.READTIM DESC  或者
SELECT B.NAME, A.PHYRDS, A.PHYWRTS, A.READTIM, A.WRITETIM
FROM V$FILESTAT A, V$DBFILE B WHERE A.FILE# = B.FILE#
ORDER BY READTIM DESC
    說明：在磁碟上的物理寫入和讀取次數上如果出現很大的差別，就表明肯定有哪個磁碟負載過多！
    如果出現磁碟負載不平衡，可以通過移動資料檔案來均衡檔案I/O：
         alter tablespace tablespace_name offline;
         $cp /disk1/a.dbf /disk2/a.dbf;
         alter tablespace tablespace_name rename datafile '/disk1/a.dbf' to '/disk2/a.dbf';
         alter tablespace tablespace online;
         $rm /disk1/a.dbf

檢視錶和記錄大小
SELECT A.OWNER, A.TABLE_NAME, A.NUM_ROWS, A.BLOCKS,
A.BLOCKS * C.VALUE / 1024 / 1024 "Size M", A.BLOCKS * C.VALUE /NUM_ROWS
"LEN(Byte)", A.EMPTY_BLOCKS, A.LAST_ANALYZED
FROM DBA_TABLES A,
(SELECT B.NAME, B.VALUE FROM V$PARAMETER B WHERE B.NAME =
'db_block_size') C WHERE A.TABLE_NAME = 'TB_PAY_AMOUNT'

檢視錶空間的名稱及大小
SELECT T.TABLESPACE_NAME, ROUND(SUM(BYTES / (1024 * 1024)), 0) "TS_SIZE M" 
FROM DBA_TABLESPACES T, DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME = D.TABLESPACE_NAME
GROUP BY T.TABLESPACE_NAME ORDER BY "TS_SIZE M" DESC

檢視錶空間物理檔案的名稱及大小
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, ROUND(BYTES / (1024 * 1024), 0) TOTAL_SPACE
FROM DBA_DATA_FILES ORDER BY TABLESPACE_NAME;

檢視錶空間的使用情況
SELECT A.TABLESPACE_NAME, A.BYTES TOTAL, B.BYTES USED,
C.BYTES FREE, (B.BYTES * 100) / A.BYTES "% USED", (C.BYTES * 100)
/ A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;