歡迎光臨
每天分享高質量文章
當前位置：知識星球 > 後端 > 正文

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

2018-03-20 分類：後端 閱讀(554) 評論(0)

（點選上方公眾號，可快速關註）


來源：袁鳴凱，

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=: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

  1. SGA：System Global Area是Oracle Instance的基本組成部分，在實體啟動時分配;系統全域性域SGA主要由三部分構成：共享池、資料緩衝區、日誌緩衝區。

  2. 共享池：Shared Pool用於快取最近被執行的SQL陳述句和最近被使用的資料定義，主要包括：Library cache（共享SQL區）和Data dictionary cache（資料字典緩衝區）。  共享SQL區是存放使用者SQL命令的區域，資料字典緩衝區存放資料庫執行的動態資訊。

  3. 緩衝區高速快取：Database Buffer Cache用於快取從資料檔案中檢索出來的資料塊，可以大大提高查詢和更新資料的效能。

  4. 大型池：Large Pool是SGA中一個可選的記憶體區域，它只用於shared server環境。

  5. Java池：Java Pool為Java命令的語法分析提供服務。

  6. 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 不能提供基於段的統計資訊，所以對資料的物理模型設計和訪問樣式的詳細瞭解對錶空間的規劃有非常大的好處。然後基於這些原則，我們就可以制定我們的表空間劃分原則了：

  1. 僅在表空間級指定INITIAL、NEXT引數，在建立資料段時不要指定這些引數；

  2. 對每個表空間上的段使用相同的區片尺寸；段引數INITIAL＝NEXT，PCTINCREASE＝0；可以透過使用Create Tablespace 的‘ MINIMUM EXTENT’ 子句來確保分配的區片是此引數的倍數；

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

  4. Oracle9i引入了本地管理表空間，它在管理和效能上都優於傳統的字典管理表空間，它已融合了規則1、2、3 ；要使用此特性，在CREATE TABLESPACE陳述句中指定EXTENT MANAGEMENT LOCAL子句；

  5. 段的區片數目上限應在4096之下，DML操作在此區片數目範圍內不會有明顯的效能差異；但某些DDL操作的速度則與區片的數目關係較大；因此合理的區片數目應保持在1024之下；對於持續不斷擴充套件的段，應監控區片數目，在必要時移至其它表空間；

  6. 對於特別大的資料段應控制在4G－128G（Oracle7為5G－160G）之間，它們應存放到單獨的表空間上，同時對於這些特大段應考慮使用分割槽拉提高效能；

  7. 使用者的臨時表空間應使用TEMPORARY型別；

  8. 當系統的事務規模比較均衡時可以對回滾段使用OPTIMAL引數，否則應避免制定OPTIMAL引數，而定期監控回滾段的大小，併在必要時重建；

  9. 臨時段和回滾段絕對不要將使用者資料存放到SYSTEM表空間，它是專為永遠不會Drop和Truncate的系統資料物件而設計的；

  10. 建立表空間時指定資料檔案的大小應＝區片整數倍＋1資料塊，對於Local Managed Tablespace則為區片整數倍＋64K；

  11. 當表空間使用統一的區片大小時，不要對其進行空間整理，重整的結果不僅耗費精力而且可能會使效能變差；對於未使用統一的區片尺寸的表空間應透過Export/Import重整；

  12. 提供了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大小的限制，又不能重灌（客戶環境不是你說要重灌就要重灌的）怎麼辦？只有想辦法：

  1. 讓作業系統支援PAE樣式（目前只有Linux AS3及以上和win 2003 advance server+sp2補丁及windows 2008）可以支援真正的PAE樣式

  2. 修改/etc/sysctl.conf檔案中的值（前面提到過了）

  3. 讓Linux作業系統中的開啟檔案數為最大（前面也提到過了）

  4. 更改你的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來實現：

  1. 使用的最佳化器的型別

  2. 基於代價的最佳化器的最佳化標的，是all_rows還是first_rows

  3. 表的訪問路徑，是全表掃描，還是索引掃描，還是直接利用rowid

  4. 表之間的連線型別

  5. 表之間的連線順序

  6. 陳述句的並行程度

除了”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]]…

註解：

  1. DELETE、INSERT、SELECT和UPDATE是標識一個陳述句塊開始的關鍵字，包含提示的註釋只能出現在這些關鍵字的後面，否則提示無效。

  2. “+”號表示該註釋是一個Hints，該加號必須立即跟在”/*”的後面，中間不能有空格。

  3. hint是下麵介紹的具體提示之一，如果包含多個提示，則每個提示之間需要用一個或多個空格隔開。

  4. 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” <

@/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 命令訪問分割槽後的表時，無需任何修改。

什麼時候使用分割槽表：

  1. 表的大小超過2GB

  2. 表中包含歷史資料，新的資料被增加都新的分割槽中。

表分割槽有以下優點：

  1. 改善查詢效能：對分割槽物件的查詢可以僅搜尋自己關心的分割槽，提高檢索速度。

  2. 增強可用性：如果表的某個分割槽出現故障，表在其他分割槽的資料仍然可用；

  3. 維護方便：如果表的某個分割槽出現故障，需要修複資料，只修複該分割槽即可；

  4. 均衡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包

線上重定義分割槽表

即：

  1. 不動原表結結，建立和原表結構一樣的表，併進行分割槽，此時新表是擁有分割槽結構的，且資料為空（空表）

  2. 利用線上重定義把原表的資料整個copy到新的分割槽表中去

  3. 刪去原表

以下是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

系列


看完本文有收穫？請轉發分享給更多人

關註「ImportNew」，提升Java技能

贊(0)
標籤：

相關推薦

熱門標籤

iOS (11238)微軟 (4955)Linux (4274)安全 (4180)Python (4161)效能 (3165)運維 (2774)最佳化 (2419).NET (2262)Google (2136)機器學習 (1795)併發 (1613)分散式 (1559)叢集 (1240)SQL (1174)Mysql (1060)區塊鏈 (1017)Docker (977)微服務 (922)面試 (919)Apache (743)NLP (725)Redis (719)Android (668)Git (640)架構師 (632)Nginx (630)Facebook (599)JVM (595)爬蟲 (476)

熱門文章

分享創造快樂