通向架構師的道路（第二十四天）之 Oracle 性能調優

知識 03-25

（點擊

上方公眾號

，可快速關注）

來源：袁鳴凱，

blog.csdn.net/lifetragedy/article/details/8215312

前言

這次，我們將在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下載地址：

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

確保你是用的是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.3 Oracle安裝前的環境變數配置

還是編輯那個profile文件並加入如下幾行：

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

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

export ORACLE_BASE=/opt/oracle

export ORACLE_HOME=$ORACLE_BASE/product/10

export ORACLE_SID=ktdb

export ORACLE_TERM=xterm

export NLS_LANG=AMERICAN_AMERICA.UTF8

export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:/lib:/usr/lib:/usr/local/lib

通過上述環境變數我們可以得知，我們的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

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/password@databaseSID 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之下；根據此原則，在進行相應測試之後，確定以下區片選取規則：

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內存限制的最終解決方案

http://blog.csdn.net/lifetragedy/article/details/6212887

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

一些客戶，這主要是客戶關係，如果換成我我一定要買支持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）說明

http://blog.csdn.net/gybyylx/article/details/6907588

在執行計劃中使用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]]... */

{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/sys@ktdb 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客戶端圖形化管理吧。