通向架構師的道路（第二十四天）之Oracle性能調優-朝拜先知之旅前言一、安裝Oracle前的準備確定你是用的是root帳号，執行下列步驟二、開始在Linux下安裝Oracle三、Oracle安裝後的一些設定四、Oracle的性能調優

前言

這次，我們将在Linux下來動手完成Oracle資料庫的安裝與使用。

Oracle本身是可以免費下載下傳的包括它的企業版以及被它收購的Weblogic和Sun中的幾乎任何東西你都可以拿來下載下傳和使用，不像IBM和Tibco一些其它廠商，隻有“試用版”給你下載下傳，Oracle的東西沒有時間限制，你拿來做練習，搭實驗環境都是沒有任何的問題的。

但是，如果你出了問題，需要用到Oracle的更新檔或者是Oracle的技術支援，這就開始收費。

Oracle就是這種“賣Service”的模式。

還有就是你安裝了Oracle後，你的應用是給另一家企業用的或者是帶有商業用途，那對不起Oracle也會問你來收費。

在linux下安裝oracle是一件令人生畏的事情，其複雜程度遠遠超過安裝linux作業系統本身。如果能夠進行成功的安裝oracle,那麼同時也就順便掌握了linux一些技術。

本文介紹在redhat linux 下安裝oracle 10g 的方法。在這裡說明一下，Oracle 10g的g是grid 的縮寫，意為網格，目前較為前沿的網絡計算技術。

一、安裝Oracle前的準備

這次我們将在Linux環境下安裝Oracle，對Oracle支援最好的莫過于SuSe Linux，但是随着後來RedHat走向了商業化後，RedHat與Oracle公司開始形成一種密切的關系，是以如果你手上有RedHat As 5.5x及以下版本或者是Fedora14及以上版本話那是最好不過了。

Oracle下載下傳位址：進入下載下傳

確定你是用的是root帳号，執行下列步驟

1.1在Linux上先安裝相應的JDK

下載下傳jdk1.6 for Linux，請注意32位與64位的區分，需要和你的作業系統對應上哦！

打開一個Terminal視窗

進入到你的jdk下載下傳的目錄下并輸入：

./jdk-6u19-linux-i586-rpm.bin

我們預設将jdk安裝于“/usr/java/jdk1.6.0_19”目錄吧。

接下來我們需要修改系統環境變量，在terminal視窗中鍵入“vi /etc/profile”

在這個profile檔案内加入兩行：

export JAVA_HOME=/usr/java/jdk1.6.0_19

export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH

1.2測試我們的jdk安裝是否正确

打開一個terminal視窗并輸入

看到正确的jdk版本資訊後即代表我們的jdk在linux下安裝正确了

1.3Oracle安裝前的環境變量配置

還是編輯那個profile檔案并加入如下幾行：

export JAVA_HOME=/usr/java/jdk1.6.0_19<br />
export PATH=$PATH:$JAVA_HOME/bin:$JAVA_HOME/jre/bin<br />
export ORACLE_BASE=/opt/oracle<br />
export ORACLE_HOME=$ORACLE_BASE/product/10<br />
export ORACLE_SID=ktdb<br />
export ORACLE_TERM=xterm<br />
export NLS_LANG=AMERICAN_AMERICA.UTF8<br />
export  LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib<br />

通過上述環境變量我們可以得知，我們的oracle裝安裝在/opt/oracle/product/10目錄下，是以

我們的ORACLE_BASE為: /opt/oracle
我們的ORACLE_HOME即為: /opt/oracle/product/10了。

這些變量是Oracle在安裝過程中需要讀取的，在windows下安裝oracle是不需要設這些東西的。

1.4 建立Oracle安裝時使用的使用者與組

打開一個Terminal視窗輸入下列指令

groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle

1.5建立Oracle的安裝路徑并将此路徑的讀寫權限賦予Oracle這個“組”

我們安裝Oracle時一般是不會用root使用者的，是以我們需要增加一個角色（group），并在這個角色中增加一個使用者（oracle）然後用這個使用者登入我們的Linux并且執行安裝。

mkdir -p /opt/oracle/ product/10
chown -R oracle.oinstall /opt/oracle/

1.6 在profile中設定圖形顯示參數

打開一個terminal視窗，然後編輯/etc/profile，在其中加入

xhost +

DISPLAY=<machine-name>:0.0; export DISPLAY

它代表使得所有的使用者可以使用圖形化界面來運作相關的圖形化應用程式，因為Linux的安全機制相當的嚴格，root是最高權限，除去root以外的其它使用者如果需要擁有root才能執行的權限就需要授予權

1.7 在Linux系統中安裝Oracle安裝時需要的系統lib庫

一般來講，Oracle主要需要下面的這些Lib庫

gcc-3.2.3-2
make-3.79
binutils-2.11
openmotif-2.2.2-16
setarch-1.3-1
compat-gcc-7.3-2.96.122
compat-gcc-c++-7.3-2.96.122
compat-libstdc++-7.3-2.96.122
compat-libstdc++-devel-7.3-2.96.122

但是。。。Linux下的Lib庫也是有依賴關系的，我給初學都的建議是你可以在剛開始安裝Linux時就選customer install，然後選中相應的lib庫，除去open-jdk（這個不能裝，裝完後sun的jdk就不起作用了，你到時還要卸），把dev相關的lib, gcc相關的lib都選上，還有gnome相關，KDE相關的lib庫，有時全選上後回過頭來要去安裝的package裡手工check，有沒有java相關的被安裝了，如果安裝了就一定要把安裝項前的勾選項，去掉,一定不能讓Linux安裝自帶的open jdk。

二、開始在Linux下安裝Oracle

Oracle10g在Linux下有版本檢查的限制，如果你的Linux RedHat的版本低于5，那麼你可以在Oracle的安裝盤disk1下，直接運作如下指令調出圖形化安裝界面

./runInstaller

如果你的Linux的版本是AS5.5及以上（目前最新的Linux），那麼請你使用下面這條指令來安裝Oracle

/runInstaller –ignoreSysPrereqs

這條指令将跳過Oracle安裝對于Linux系統核心的檢查。

主安裝界面出現

按照上述步驟一步步把Oracle裝上，注意安裝時字元集永遠選用AL32UTF-8，這樣你的Oracle才能支援多語言。

三、Oracle安裝後的一些設定

3.1 設定Oracle的服務開機自動啟動

編輯 /etc/rc.local/檔案

以下是dbstart.sh檔案的内容

存盤後每次你重新開機Linux，Oracle就會随着你的Linux的開機而自動運作起來了（還有更專業的設定，将在以後的教程中傳授,對于初學者想自己搭個環境的選用這個，嘿嘿）。

3.2 設定Oracle的processes, session, Maximum Open Cursor

其們在使用sys使用者連上Oracle後可以使用這條指令來顯示這三個兄弟

show parameter processes;

一般安裝好後，這個processes預設為150，網上有很多人說如果碰到使用者的session不夠就去用alter指令改這個session number，其實是不對的。

session與processes是綁定的，用下面的公式：

sessions=1.1*processes + 5

是以你隻有改這個processes，session才會自動調整，我們可以使用下面這條指令去改變系統中的processes

alter system set processes=500 scope = spfile;

這個processes不是亂設的，是要和你系統的核心設定去綁定的。

四、Oracle的性能調優

Client Configuration
User Role Privilege
SGA
Table Space
Import
Oracle And OS Kernel
Oracle Under 32 bits OS
SQL Plan
Table Analyze
Partition Table
Performance Monitor

我們的Oracle性能調優主要用圍繞上述幾個章節來做介紹，我們不介紹太高深的莫明奇妙的理論，在這邊我們對這幾個方面做一個統用的解釋和實際應用場景，如果是新手，你在看過這篇教程後應該知道一個oracle的性能主要從幾個方面（Common）去着手，對一個熟手來說上述每個小節的具體内容都是可以在Oracle的DBA手冊中找到更詳細的内容。

4.1 用戶端的配置：TNS（Client Configuration: TNS）

配置主機名：

Oracle的連接配接服務是基于主機名的，我們需要設定裝Oracle這台主機的唯一主機名，這樣用戶端才能通過TNS來連上Oracle的服務

請更改Oracle所在伺服器上的 /etc/hosts檔案

# Do not remove the following line, or various programs

# that require network functionality will fail.


192.168.1.3	myoracleserver

127.0.0.1	localhost.localdomain localhost

這邊的192.168.1.3就是我們的主機ip，後面的myoracleserver就是主機名。

配置用戶端連接配接

一個用戶端如果需要連接配接Oracle必須安裝Oracle Client，可以去Oracle網站上下載下傳進入下載下傳。 Oracle11g開始後不再提供圖形化的Oracle用戶端下載下傳，但盡管是這樣你也必須在客戶機上尤其是那些支援TNS連接配接的第三方用戶端時都是必須安裝Oracle用戶端的。

、

我們選擇“管理者"模式進行安裝。安裝完後，你會在你的”啟動“菜單裡找到這樣的一個菜單項

更改用戶端的字型使其可以支援中文的正常顯示

編輯系統資料庫

把這個NLS_LANG改成上圖中所示，這樣你的Oracle的用戶端上可以直接通過用戶端工具或者是第三方用戶端工具進行中文的正常錄入了而不是亂碼了。

4.2 在Oracle中建立帳号與配置設定權限

使用system帳号登入

建立使用者之前先要建立表空間

一般我們建立一個表空間和一個臨時表空間

然後我們就可以開始建立使用者了

配置設定角色

對于一個j2ee的應用連接配接Oracle來說，這個連接配接使用者的角色隻需要具有上述兩個權限就夠用了，有些人喜歡給這個使用者DBA權限是太絕對了，當然這樣給角色使用起來友善，什麼權限都有了，不需要我在開發時再進行細化的設定了。

可是，你有沒有想到過，一旦你的應用被人sql injection後，而你的應用上連接配接着oracle的使用者是dba權限，那麼你連整個資料庫是不是都會被洩密啊？

配置設定系統角色

配置設定完角色後不是說等于結束了，還有一個”系統“角色要你配置設定，給下圖所列的一項就夠用了。

設定Oracle連接配接使用者最大可登入次數

一個Oracle内的使用者預設可以讓你重複登入10次，10次的限制一到，這個使用者就會被鎖住。

可以用下列語句檢視一個Oracle使用者重複幾次後會被鎖住的設定參數

SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='FAILED_LOGIN_ATTEMPTS';

當然這樣做是為了安全考慮。

可是，在我們的開發環境中，有時往往因為一個循環，一個登入器，一個線程寫錯而導緻Oracle連接配接使用者重複需要連接配接Oracle 數次，是以經常開發人員會向DBA抱怨說 “我的使用者又被鎖住了”。

是以，在開發環境我建議你把這個“重複連接配接次數”去掉，設成下面這樣

ALTER PROFILE DEFAULT LIMIT FAILED_LOGIN_ATTEMPTS UNLIMITED

4.3 Oracle SGA

SGA：System Global Area是Oracle Instance的基本組成部分，在執行個體啟動時配置設定;系統全局域SGA主要由三部分構成：共享池、資料緩沖區、日志緩沖區。
共享池：Shared Pool用于緩存最近被執行的SQL語句和最近被使用的資料定義，主要包括：Library cache（共享SQL區）和Data dictionary cache（資料字典緩沖區）。共享SQL區是存放使用者SQL指令的區域，資料字典緩沖區存放資料庫運作的動态資訊。
緩沖區高速緩存：Database Buffer Cache用于緩存從資料檔案中檢索出來的資料塊，可以大大提高查詢和更新資料的性能。
大型池：Large Pool是SGA中一個可選的記憶體區域，它隻用于shared server環境。
Java池：Java Pool為Java指令的文法分析提供服務。
PGA：Process Global Area是為每個連接配接到Oracle database的使用者程序保留的記憶體。

關于SGA這塊的調整，網上有太多優秀的文章。

一般可以通過修改$ORACLE_HOME/dbs目錄下的init.ora檔案來自行調整，但是。。。。。。

如果你要調整一個init.ora檔案，請使用下面的步驟可確定你的修改不會造成Oracle啟動不了

第一步：修改前請先作一份init.ora檔案的安全拷貝

在Linux下使用下面這條指令

strings spfilektdb.ora > init.ora.bak

一個init*.ora對應一個spfile*.ora

第二步：對于這個init.ora.bak檔案内的SGA值進行調整

第三步：讓Oracle直接用這個被修改後的init.ora.bak來啟動資料庫執行個體，請使用sys使用者以指令行的方式登入oracle

sqlplus sys/[email protected] as sysdba

登入成功後運作下面兩條指令

shutdown immediate
startup pfile='/opt/oracle/product/10/dbs/init.ora.bak‘

如果Oracle能夠正常啟動和支援用戶端的連接配接說明我們的改動沒有損壞到Oracle已安裝的執行個體

第四步：讓更改生效使得Oracle每次啟動都使用我們修改過後的SGA的值

create spfile from pfile='/opt/oracle/product/10/dbs/init.ora.bak';
startup force

如果上述這兩條指令沒有啟動，那麼你的Oracle一旦重新開機後它将還是繼續使用原有oracle的SGA配置，而非改動後的配置。

4.4 Oracle表空間管理

Oracle的表空間檔案都放在$ORACLE_HOME/oradata如/opt/oracle/product/10/oradata這樣的目錄中的

Oracle的表空間支援”熱插撥“

即在Oracle運作時發覺表空間不夠時可以直接打開Oracle的管理界面來動态給它劃一塊硬碟空間，或者甚至你又裝了一塊硬碟進服務後，Oracle可以把表空間在運作時擴充到新插入的磁盤中。

表空間管理

ORACLE的表空間劃分将影響ORACLE的資料通路速度。

對于表空間來說，最重要的是如何把要連續通路的段放在一起，但是由于oracle 不能提供基于段的統計資訊，是以對資料的實體模型設計和通路模式的詳細了解對表空間的規劃有非常大的好處。然後基于這些原則，我們就可以制定我們的表空間劃分原則了：

僅在表空間級指定INITIAL、NEXT參數，在建立資料段時不要指定這些參數；
對每個表空間上的段使用相同的區片尺寸；段參數INITIAL＝NEXT，PCTINCREASE＝0；可以通過使用Create Tablespace 的‘ MINIMUM EXTENT’ 子句來確定配置設定的區片是此參數的倍數；

區片的大小根據段大小來确定，原則是均衡順序掃描的效率和空間的使用率，同時確定段的區片數目控制在1024之下；根據此原則，在進行相應測試之後，确定以下區片選取規則：

段大小	區片大小
128M	128K
128M-4G	4M
4G及以上	128M

Oracle9i引入了本地管理表空間，它在管理和性能上都優于傳統的字典管理表空間，它已融合了規則1、2、3 ；要使用此特性，在CREATE TABLESPACE語句中指定EXTENT MANAGEMENT LOCAL子句；
段的區片數目上限應在4096之下，DML操作在此區片數目範圍内不會有明顯的性能差異；但某些DDL操作的速度則與區片的數目關系較大；是以合理的區片數目應保持在1024之下；對于持續不斷擴充的段，應監控區片數目，在必要時移至其它表空間；
對于特别大的資料段應控制在4G－128G（Oracle7為5G－160G）之間，它們應存放到單獨的表空間上，同時對于這些特大段應考慮使用分區拉提高性能；
使用者的臨時表空間應使用TEMPORARY類型；
當系統的事務規模比較均衡時可以對復原段使用OPTIMAL參數，否則應避免制定OPTIMAL參數，而定期監控復原段的大小，并在必要時重建；
臨時段和復原段絕對不要将使用者資料存放到SYSTEM表空間，它是專為永遠不會Drop和Truncate的系統資料對象而設計的；
建立表空間時指定資料檔案的大小應＝區片整數倍＋1資料塊，對于Local Managed Tablespace則為區片整數倍＋64K；
當表空間使用統一的區片大小時，不要對其進行空間整理，重整的結果不僅耗費精力而且可能會使性能變差；對于未使用統一的區片尺寸的表空間應通過Export/Import重整；
提供了Alter Table …Move [Tablespace…]指令可用于快速重整表，Alter Index …Rebuild…[Tablespace…] 指令可用于快速重建索引；

4.5 Import （導入）

當傳統的導入導出遇到了海量資料時~

我在以前一個工程碰到過一個真實的案例，48-50張左右的表，每張表最大資料量為1200萬，最小的在280-300萬左右的資料，占用硬碟空間在14-16GB左右的一個.dmp包。不定期會進行導入導出操作。

于是我們的資料庫負責人員就使用傳統的imp/exp指令了。

每一次imp都需要耗費達4-6個小時，有時一旦出錯。。。完蛋了，這個效率太低，大家不要看1200萬這個數量，大家會說：喲，都是幾百萬的資料，可是這點資料其實還不算是真正的大資料量，imp一次要4-6小時，這是絕對不合理的。

如何讓你的imp飛起來

傳統的imp指令在導入時，如果隻是僅處理資料，千萬條資料對Oracle的處理來說根本就是”毛毛雨“啦，關鍵是在它導入了資料後，而要對每個表重新做一次索引。

一邊導一邊索引，一邊一條條commit，就好比你用一個循環來insert 1萬條資料和你改用statemenet.addBatch(query);的效率的對比一樣，一定是後者更快更高效。

是以，在碰到這種情況下我們建議對imp導入指令做下面的折分：

分段式送出
設定緩沖
先導資料再導索引

這樣，我們原來的imp指令就變成下面這樣的樣子了：

imp user2/pwd fromuser=user1 touser=user2 file=file commit=y feedback=10000 buffer=10240000 ignore=y rows=y indexes=n

imp user2/pwd fromuser=user1 touser=user2 file=file commit=y feedback=10000 buffer=10240000 ignore=y rows=n indexes=y

看到沒有，先導資料rows=y indexes=n，再導索引rows=n indexes=y

這兩條指令是先後發起的，在隻導資料時對于上述的14gb左右的一個.dmp包在同樣軟硬體環境中隻用了15-20分鐘，後一條建索引語句隻用了25-27分鐘。

這其中，提高了幾倍？大家想想。

4.6 Oracle與Linux系統的幾個主要核心參數關系

limits.conf檔案

編輯這個 /etc/security/limits.conf

* soft nofile 1024
 * hard nofile 1024

一般這個檔案的預設值為1024

它代表Linux系統下最大打開檔案數，如java裡面的jdbc connection操作，new File操作都是一個檔案打開操作，1024這個值是很少的。

是以我們把它改成

* soft nofile 300000
* hard nofile 300000

繼續修改

oracle soft memlock 1048576  
oracle hard memlock 1048576

這個值如果有的話直接改後面的數字，如果沒有的話需要把這兩行增加入limits.conf檔案中，其中：

memlock’s value=Oracle share_pool_size (gb)*1024*1024

是以你的Oracle中的SGA裡的share_pool_size的大小是受到這個值的限制的。

改完後重新開機Linux系統，然後我們可以使用下面的指令來看我們修改的效果。

ulimit –a

這個指令可以檢視Linux系統目前的最大打開檔案數。

使用Oracle使用者登入

su – oracle

然後鍵入

ulimit -l

就可以看到oracle soft memlock的相關修改效果了。

關于kernel.*的參數的配置

主要是在/etc/sysctl.conf檔案中

kernel.shmall = 2097152 
kernel.shmmax = 2147483648 
kernel.shmmni = 4096 
kernel.sem = 250         32000    100          128
                        
                       #SEMMSL     SEMMNS       SEMOPM  SEMMNI

其中最後一行帶”#“是我加的，代表這幾個數值的”含義“，那麼光有含義，沒有解釋？下面給出解釋

SEMMSL=max processes+10

SEMMNS=SEMMSL*SEMMNI

看到沒有。。。。。。是以說Oracle中這個processes不是亂設的，是要和你系統的核心設定去綁定的。

4.7Oracle在32位作業系統下的瓶勁與如何突破

我去年寫過篇博文，那篇博文是用于記錄如何在實際的一個項目中在32位Linux作業系統下讓Oracle的SGA突破2GB記憶體這個限制，詳細可見：oracle在32位的Linux環境下SGA如何突破2GB記憶體限制的最終解決方案

在這邊，我再做一下補充。

一些客戶，這主要是客戶關系，如果換成我我一定要買支援64位作業系統的伺服器。

一些使用32位作業系統的客戶安裝Oracle，大家知道Oracle自從8.0後開始全面轉成Java，是以它也受到JAVA虛拟機在32位作業系統下最多隻能使用到2GB實體記憶體這個限制。

是以，當你的機器實體記憶體有32GB時，但因為你用了32位的作業系統，是以你的Oracle在建立Database時即customer（定義）這個資料庫記憶體分匹時你的SGA是超不過2048MB的。

SGA中有一個很重要的名額即：shared_pool_size，這個值的大小會直接影響性能，關于shared_pool_size有很多更深入的理論性的探讨，這邊告訴初學者或者新手，這個值相當于”遊戲推薦配置“，不滿足，遊戲運作暴卡，超過這個值，遊戲運作流暢。

但是，我現在有台伺服器，實體記憶體32GB，用的是32位的Linux，我Oracle的SGA想要突破這個2GB大小的限制，又不能重裝（客戶環境不是你說要重裝就要重裝的）怎麼辦？隻有想辦法：

讓作業系統支援PAE模式（目前隻有Linux AS3及以上和win 2003 advance server+sp2更新檔及windows 2008）可以支援真正的PAE模式
修改/etc/sysctl.conf檔案中的值（前面提到過了）
讓Linux作業系統中的打開檔案數為最大（前面也提到過了）
更改你的Oracle的SGA

具體做法還是請見我那篇 oracle在32位的Linux環境下SGA如何突破2GB記憶體限制的最終解決方案的博文吧。這是我在32位的RedHat 5.5上把Oracle的SGA突破了2GB這個限制後的真實記憶體分布圖，大家看看現在我的SGA是多少了？

查詢效率一下提升20多倍，其實一點不值得驕傲，為什麼？廢話，誰讓客戶不懂沒來事先問我的意見，在64位作業系統下，你機器這麼多記憶體根本就不用這麼麻煩

4.8 使用SQL執行計劃幫助你定位瓶勁

一般我喜歡用第三方工具如：PLSQL Developer，它可以圖形化的展現你的Oracle的執行計劃，關于執行計劃怎麼看，大家可以參考這篇博文 oracle 執行計劃（explain plan）說明

在執行計劃中使用hits來改變和調整SQL執行的性能

Oracle Hints是一種機制，用來告訴優化器按照我們的告訴它的方式生成執行計劃。我們可以用Oracle Hints來實作：

使用的優化器的類型
基于代價的優化器的優化目标，是all_rows還是first_rows
表的通路路徑，是全表掃描，還是索引掃描，還是直接利用rowid
表之間的連接配接類型
表之間的連接配接順序
語句的并行程度

除了”RULE”提示外，一旦使用的别的提示，語句就會自動的改為使用CBO優化器，此時如果你的資料字典中沒有統計資料，就會使用預設的統計資料。是以建議大家如果使用CBO或Hints提示，則最好對表和索引進行定期的分析。

如何使用Hints:

Hints隻應用在它們所在sql語句塊(statement block，由select、update、delete關鍵字辨別)上，對其它SQL語句或語句的其它部分沒有影響。如：對于使用union操作的2個 sql語句，如果隻在一個sql語句上有Hints，則該Hints不會影響另一個sql語句。

我們可以使用注釋(comment)來為一個語句添加Hints，一個語句塊隻能有一個注釋，而且注釋隻能放在SELECT, UPDATE, or DELETE關鍵字的後面

使用Oracle Hints的文法：

{DELETE|INSERT|SELECT|UPDATE} /*+ hint [text] [hint[text]]... */


or


{DELETE|INSERT|SELECT|UPDATE} --+ hint [text] [hint[text]]...

注解：

DELETE、INSERT、SELECT和UPDATE是辨別一個語句塊開始的關鍵字，包含提示的注釋隻能出現在這些關鍵字的後面，否則提示無效。
“+”号表示該注釋是一個Hints，該加号必須立即跟在”/*”的後面，中間不能有空格。
hint是下面介紹的具體提示之一，如果包含多個提示，則每個提示之間需要用一個或多個空格隔開。
text 是其它說明hint的注釋性文本

如果你沒有正确的指定Hints，Oracle将忽略該Hints，并且不會給出任何錯誤。

/*+ALL_ROWS*/
/*+FIRST_ROWS*/
/*+CHOOSE*/
/*+RULE*/
/*+FULL(TABLE)*/
/*+ROWID(TABLE)*/
/*+USE_HASH(BSEMPMS,BSDPTMS)*/

這些常用的hits可以供大家參考，還有更多的hints可以參考oracle dba相關手冊

4.9 Table Analyze-Oracle的表分析

什麼是表分析，為什麼需要表分析？

這要從Oracle的優化器談起。Oracle的優化器有兩種優化方式： Oracle的優化器有兩種優化方式：

基于規則的優化方式：Rule-Based Optimization（RBO）
基于成本或者統計資訊的優化方式(Cost-Based Optimization：CBO)

RBO方式：優化器在分析SQL語句時,所遵循的是Oracle内部預定的一些規則。比如我們常見的，當一個where子句中的一列有索引時去走索引。

CBO方式：CBO是在ORACLE7 引入，但到ORACLE8i 中才成熟。ORACLE 已經聲明在ORACLE9i之後的版本中，RBO将不再支援。它是看語句的代價(Cost),這裡的代價主要指Cpu和記憶體。CPU Costing的計算方式現在預設為CPU+I/O兩者之和.可通過DBMS_XPLAN.DISPLAY_CURSOR觀察更為詳細的執行計劃。優化器在判斷是否用這種方式時,主要參照的是表及索引的統計資訊。統計資訊給出表的大小、有少行、每行的長度等資訊。這些統計資訊起初在庫内是沒有的，是做analyze後才出現的，很多的時侯過期統計資訊會令優化器做出一個錯誤的執行計劃,因些應及時更新這些資訊。按理，CBO應該自動收集，實際卻不然，有時候在CBO情況下，還必須定期對大表進行分析。

ANALYZE TABLE ktdb.T_CD_CODE COMPUTE STATISTICS;

ANALYZE TABLE ktdb.T_CD_CODE COMPUTE STATISTICS  for all indexed columns;

對一個表進行全表分析，就必須對這個表運作上述兩條語句。

比如說，你的資料庫每天有幾十萬條資料進進出出，過了1周，資料庫裡原先一些查詢已經不走Oracle預設的優化引擎了，本來是該走索引的，結果你用SQL分析器看出來它走的是full scan，這時就要做表分析了，一旦表分析做完後，你的資料庫又會按照你原有的計劃去走最優的路線了，這無疑中會提高你的資料庫通路性能。但是。。。如果我有100，200個表，我豈不是每一個表都要寫這麼兩條語句？也真有這樣的傻子寫了幾百條這樣的重複語句，下面傳授給大家一個用程式一次性生成所有的表的分析語句吧。我們使用oracle的sql語句來做：

先生成所有的表分析語句

spool 'd:\analyzetable.txt‘
select   *   from   user_tables;
spool off

再生成所有的表的索引分析語句

spool 'd:\analyzeindex.txt‘
select   *   from   user_indexes ;
spool off

最後使用批處理腳本來執行目前連接配接資料庫中所有的表的分析語句，比如說我們建立一個dbAnalyze.sh檔案。

export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10
export ORACLE_SID=ktdb
$ORACLE_HOME/bin/sqlplus "sys/[email protected] as sysdba" <<eof
@/home/oracle/analyzetable.txt;
exit
eof

由于資料庫的表分析很費時，一般我們都會選擇在零晨或者是在周末這兩天進行一次表分析，這樣保證，每次在工作日時我的資料庫中的SQL始終走的是最适合的優化器.

這邊說一個真實的CASE，3年前一個項目，項目剛開始導入了80GB的資料，開發了一段時間大概3-4個月時，第一階段進入到性能測試，發覺這個報表的一些sql很慢，30秒，40秒。然後我過去後問了一下情況，先不分析這個SQL和JAVA代碼先運作一下表分析器，直接所有的報表的SQL從原來的平均35秒一下變成了2.93秒。那剩下來的事呢，再來排摸這個SQL語句寫的是否最優和再來看JAVA代碼等等等問題。

在這邊提這個CASE的目的在于告訴大家，有表分析這麼一個事存在，碰到資料庫經常資料做遷移或者是進進出出的次數多時，定期執行你的table analyze是非常有必要的，而且能夠幫助你提升性能。

4.10 Partition Table（分區表）

Oracle的表分區功能通過改善可管理性、性能和可用性，進而為各式應用程式帶來了極大的好處。通常，分區可以使某些查詢以及維護操作的性能大大提高。此外,分區還可以極大簡化常見的管理任務，分區是建構千兆位元組資料系統或超高可用性系統的關鍵工具。

分區功能能夠将表、索引或索引組織表進一步細分為段，這些資料庫對象的段叫做分區。每個分區有自己的名稱，還可以選擇自己的存儲特性。從資料庫管理者的角度來看，一個分區後的對象具有多個段，這些段既可進行集體管理，也可單獨管理，這就使資料庫管理者在管理分區後的對象時有相當大的靈活性。但是，從應用程式的角度來看，分區後的表與非分區表完全相同，使用 SQL DML 指令通路分區後的表時，無需任何修改。

什麼時候使用分區表：

表的大小超過2GB
表中包含曆史資料，新的資料被增加都新的分區中。

表分區有以下優點：

改善查詢性能：對分區對象的查詢可以僅搜尋自己關心的分區，提高檢索速度。
增強可用性：如果表的某個分區出現故障，表在其他分區的資料仍然可用；
維護友善：如果表的某個分區出現故障，需要修複資料，隻修複該分區即可；
均衡I/O：可以把不同的分區映射到磁盤以平衡I/O，改善整個系統性能。

缺點：

分區表相關：已經存在的表沒有方法可以直接轉化為分區表。不過 Oracle 提供了線上重定義表的功能。

其實表分區也是一種”反範式“的表設計，舉個例子吧，電信為例。

它的資料庫是怎麼存的？不是一個table裡有幾個主鍵來區分一下就完了的，想一下電信的日用場景：

某客戶來到電信營業廳，說：我查3天内的通話記錄客服人員：請稍侯過了一會，客服人員告訴客戶近3天的通話記錄。

過幾天，又來了一個客戶說：我要查近3年内的通話記錄客服人員：你過幾天來

為什麼，為什麼這邊客服人員要讓客戶過幾天來查而不是馬上把結果告訴客戶？因為這的這個資料庫裡的表名就是按照年月日來進行實體分區的，當客戶要查詢過大的資料時，由于已經實體分區了，是以這個資料庫就可以存到錄音帶機上，當客戶需要知道一個曆史較長時間的記錄時，電信的IT人員需要把曆史的錄音帶機如：1997年XXX上海市楊浦區XXX的XXX客戶的存檔.dat檔案所屬的這卷錄音帶裝上電腦再查詢，這是需要時間的。

那為什麼不直接把這些資料都存在一張表用一個DATE字段區分來查詢一下不就完了？想想上億的資料都存在一張表，你再怎麼優化也優不到哪裡去，此時就要引入反範式了。

比如說我按照時間來分區，本來是1億條記錄，按照時間分成10萬條記錄一個實體區，這樣當我要查詢的内容正好落在1區或者是2區時，我需要查詢的結果的”分母“隻有20萬，而原本我不分區時的查詢時的分母是”一億“，是不是這個查詢速度會得到顯著的提高啊？

下面來看幾種Oracle中分區的用法吧

Range Partition（根據範圍來分區）

PARTITION BY RANGE (CUSTOMER_ID)
(
　PARTITION CUS_PART1 VALUES LESS THAN (100000) TABLESPACE    
   CUS_TS01, PARTITION CUS_PART2 VALUES LESS THAN (200000)  
   TABLESPACE CUS_TS02
)

Hash partition（HASH分區）這個最傻瓜了最好用了，不需要指定分區的條件的

CREATE TABLE emp
(
　empno NUMBER (4),ename VARCHAR2 (30),sal NUMBER
)PARTITION BY HASH (empno) PARTITIONS 8
STORE IN (emp1,emp2,emp3,emp4,emp5,emp6,emp7,emp8);

Component Partition

create table dinya_test
(
　　transaction_id number primary key,item_id number(8) not null,transaction_date date
) partition by range(transaction_date)subpartition by hash(transaction_id) subpartitions 
  3 store in (dinya_space01,dinya_space02,dinya_space03)
　(
　　partition part_01 values less than(to_date(‘2006-01-01’,’yyyy-mm-dd’)),
　　partition part_02 values less than(to_date(‘2010-01-01’,’yyyy-mm-dd’)),
　　partition part_03 values less than(maxvalue)
);

分區表和表分析一樣需要在資料庫沒有交易甚至是需要斷開所有的連接配接時才能安全有效的去做的一個動作，但是oracle從9i後開始提供了一種線上分區，對于擁有百萬級資料的一張表來說進行分區即可以保證并發通路的安全同時速度又快-僅幾秒種時間就可以完成。它就是：DBMS_REDEFINITION包

線上重定義分區表

即：

不動原表結結，建立和原表結構一樣的表，并進行分區，此時新表是擁有分區結構的，且資料為空（空表）
利用線上重定義把原表的資料整個copy到新的分區表中去
删去原表

以下是Oracle 中使用線上重定義的例子。

EXEC DBMS_REDEFINITION.CAN_REDEF_TABLE('schema名', '你将要被切換的表名', DBMS_REDEFINITION.CONS_USE_PK); 
EXEC DBMS_REDEFINITION.START_REDEF_TABLE('schema名','你将要被切換的表名', '擁分區結構的新表');
EXEC DBMS_REDEFINITION.sync_interim_table('schema名', '你将要被切換的表名', '擁分區結構的新表');  
EXEC DBMS_REDEFINITION.FINISH_REDEF_TABLE('schema名', '你将要被切換的表名', '擁分區結構的新表');

上述四條語句依次執行，幾秒鐘内你源有的表立記得就變成了新的分區表了. 如果在上述執行過程中出現了錯誤，你的原表照樣還是被保護的好好的，因為你這時會得到Oracle的錯誤提示，壞也是壞在NEW表上，是以把NEW表剁掉并且和原表間的”線上“關系斷開即可,使用下列語句：

exec DBMS_REDEFINITION.ABORT_REDEF_TABLE('schema名','你将要被切換的表名','擁分區結構的新表');

然後你檢查一下錯誤，重新把那四條線上重定義分區表語句再依次運作一下即可。

4.11 Oracle性能監控

Oracle的用戶端中的Oracle Enterprise Manager Console已經可以完成基本的監控任務。

Oracle11g後的用戶端中不再提供Oracle Enterprise Manager Console，是以你要麼安裝11g的用戶端後使用如第三方的Oracle客戶：toad或者是PL SQL Developer要麼就在Oracle11g的服務端打開dbconsole這個服務，然後在前台用：https://localhost:1158/em/console這個位址在用戶端進行基于網頁的Oracle用戶端圖形化管理吧。

如果你的服務端的Oracle是10G，那麼請在服務端開啟dbconsole這個服務時，用戶端需要連接配接時使用這個位址：http://ip:1158