Oracle優(yōu)化01-引起數(shù)據(jù)庫(kù)性能問題的因素

思維導(dǎo)圖

概述

一個(gè)數(shù)據(jù)庫(kù)是否存在性能問題，基本上在系統(tǒng)設(shè)計(jì)的時(shí)候就決定了，這個(gè)系統(tǒng)設(shè)計(jì)包括軟件的設(shè)計(jì)、數(shù)據(jù)庫(kù)的設(shè)計(jì)和硬件的設(shè)計(jì).其中更細(xì)節(jié)的分類參考目錄。

在一個(gè)系統(tǒng)的設(shè)計(jì)階段，其中任何一個(gè)環(huán)節(jié)存在設(shè)計(jì)不當(dāng)之處，都可能導(dǎo)致系統(tǒng)的性能下降，而系統(tǒng)的性能在多數(shù)情況下又反映為數(shù)據(jù)庫(kù)的性能問題。

軟件設(shè)計(jì)對(duì)數(shù)據(jù)庫(kù)的影響

軟件架構(gòu)設(shè)計(jì)對(duì)數(shù)據(jù)庫(kù)性能的影響

軟件系統(tǒng)的架構(gòu)對(duì)數(shù)據(jù)庫(kù)的影響是非常直接的。 比如一套并發(fā)量非常大的系統(tǒng)，一般都會(huì)采用一套軟件來搭建一個(gè)中間層，用來構(gòu)建緩沖池，在數(shù)據(jù)庫(kù)之前多大量的并發(fā)進(jìn)行處理，以便于每次只有少數(shù)的用戶連接到數(shù)據(jù)庫(kù)中，其他的用戶在緩沖池的隊(duì)列中等待，同時(shí)，很多這種中間件軟件還提供了負(fù)載均衡的功能。

Oracle自身也提供了一種MTS技術(shù)，很少用，大部分都是采用中間件服務(wù)。

軟件代碼的編寫對(duì)數(shù)據(jù)庫(kù)性能的影響

軟件代碼對(duì)數(shù)據(jù)庫(kù)的影響，通常指的是應(yīng)用程序中對(duì)數(shù)據(jù)庫(kù)操作的代碼部分對(duì)數(shù)據(jù)庫(kù)產(chǎn)生的影響。

具體來講就是SQL或者PL/SQL包。

SQL語(yǔ)句

SQL造成的影響

一種是 SQL語(yǔ)句本身在邏輯上就效率低下

另外一種SQL語(yǔ)句沒有綁定變量。

性能底下的SQL語(yǔ)句，比如使用不合適的Hint，不合適的外關(guān)聯(lián)，謂詞的隱含轉(zhuǎn)換，優(yōu)化器的選擇等等，特別是多表關(guān)聯(lián)的情況下，影響更是顯著。 它主要體現(xiàn)為SQL語(yǔ)句的執(zhí)行收到了人為的約束，比如數(shù)據(jù)的訪問方式（索引還是全表掃描），以及表關(guān)聯(lián)方式的選擇上.

對(duì)于高版本的數(shù)據(jù)庫(kù)(10g以上)，CBO（基于成本的優(yōu)化器）技術(shù)已經(jīng)比較成熟，我們還是應(yīng)該讓數(shù)據(jù)庫(kù)自己根據(jù)表、索引的統(tǒng)計(jì)分析信息來決定SQL的執(zhí)行計(jì)劃，因?yàn)楸碇械臄?shù)據(jù)是變化的，人為強(qiáng)制的干預(yù)，必然會(huì)在某個(gè)時(shí)候出現(xiàn)問題。

外連接是一個(gè)代價(jià)非常昂貴的執(zhí)行過程，如果業(yè)務(wù)需要，這種操作是必須的，但是有時(shí)候會(huì)出現(xiàn)人為的在SQL中使用不必要的外連接的情形，因?yàn)橛械拈_發(fā)者擔(dān)心遺漏一些數(shù)據(jù)而刻意使用它，讓SQL執(zhí)行計(jì)劃變的非常耗時(shí)。

栗子

--創(chuàng)建t create table t as select rownum a , rownum+100 b from dba_users u where rownum <10 ; --批量向t表中寫入10萬數(shù)據(jù)，每5000次提交一次 begin for i in 10 .. 100000 loop insert into t (a, b) values (i, i); if mod(i, 5000) = 0 then commit; end if; end loop; end; --創(chuàng)建t2 create table t2 as select decode(mod(rownum ,2),0,rownum) c ,rownum+1000 d from dba_users u where rownum<10;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

查詢

--左連接方式 select a, b, c, d from t, t2 where t.a = t2.c(+) and t2.d > 1000; 這個(gè)sql的含義是：得到t表上的所有行，然后用a和t2的c列關(guān)聯(lián)，同時(shí)t2的d>1000 --內(nèi)連接 select a, b, c, d from t, t2 where t.a = t2.c and t2.d > 1000;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

從結(jié)果集上看，這兩條SQL是等價(jià)的,在這情況下，外連接其實(shí)是沒有用的，是人為的在SQL里設(shè)定的限制。

不難發(fā)現(xiàn)t2.d>1000已經(jīng)明確的指出，在結(jié)果集中，t2表的任何一行，c列都應(yīng)該有值，也就是在這種情況下，根本不需要使用外連接，業(yè)務(wù)邏輯上講，外連接在這里是多余的。 對(duì)數(shù)據(jù)庫(kù)來講，有時(shí)候在執(zhí)行時(shí)可能會(huì)引起極大的性能差別。

通常對(duì)已一種功能單一的數(shù)據(jù)庫(kù)來講，在實(shí)例設(shè)置一個(gè)優(yōu)化器模式即可，

比如OLAP系統(tǒng)，絕大多數(shù)的時(shí)候運(yùn)行的是報(bào)表作業(yè)，執(zhí)行的基本上是聚合類的SQL操作，比如group by ,這個(gè)時(shí)候把優(yōu)化器模式設(shè)置為all_rows是恰當(dāng)?shù)摹?/p>

而對(duì)于一些分頁(yè)操作比較多的網(wǎng)站類數(shù)據(jù)庫(kù)，設(shè)置成 first_rows會(huì)更好一些。

但是有些情況比較復(fù)雜，比如數(shù)據(jù)庫(kù)上運(yùn)行的基本是一個(gè)OLAP系統(tǒng)，所以優(yōu)化器模式設(shè)置為ALL_ROWS，這樣利于報(bào)表的快速完成。

同時(shí)，數(shù)據(jù)庫(kù)上還有一些查詢業(yè)務(wù)，查詢的方式可以說是分頁(yè)的，針對(duì)這種情況，在開發(fā)階段就要考慮到這種情況，在SQL里通過Hint的方式將優(yōu)化模式轉(zhuǎn)換成ＦＩＲＳＴ＿ＲＯＷＳ，這樣既可以大大的提高數(shù)據(jù)的處理速度。

比如一次提取１０條記錄的分頁(yè)查詢：

select * from ( select /*+first_rows(10)*/ x.*, rownum rnum from (select /* +first_rows(10)*/ a, b from t order by a) x where rownum <= 10) where rnum >= 1;

1

2

3

4

5

6

7

8

9

盡管在ＳＱＬ中認(rèn)為的加入ｈｉｎｔ操作不是一個(gè)好主意，但是有的時(shí)候如果需要兼顧用戶的其他操作，可以考慮這樣的設(shè)定。　但前期要做一些測(cè)試工作，確保這樣的優(yōu)化能夠帶來性能上的提高，同時(shí)不會(huì)對(duì)數(shù)據(jù)庫(kù)造成其他方面的影響。　這是系統(tǒng)設(shè)計(jì)階段應(yīng)該仔細(xì)考慮的一個(gè)問題。

對(duì)于這個(gè)話題，很多人存在一個(gè)誤區(qū)，認(rèn)為不使用綁定變量系統(tǒng)就要出問題一樣，有時(shí)候綁定變量對(duì)性能的影響被夸大化了。

其實(shí)我們應(yīng)該分析我們當(dāng)前的系統(tǒng)的實(shí)際情況：比如數(shù)據(jù)庫(kù)的用戶連接用戶很少，每個(gè)用戶每天觸發(fā)的查詢操作也很少，同時(shí)我們的數(shù)據(jù)庫(kù)主機(jī)8G內(nèi)存，16個(gè)cpu, 硬解析對(duì)數(shù)據(jù)庫(kù)的性能影響的微乎其微，完全可以忽略掉，因?yàn)槲覀兊南到y(tǒng)是一個(gè)OLAP系統(tǒng)。

實(shí)際上，至少對(duì)于OLAP系統(tǒng)（在線分析系統(tǒng)，通常是指這樣的系統(tǒng)，數(shù)據(jù)庫(kù)中存放著海量的數(shù)據(jù)，連接的用戶很少，SQL基本上是用戶生成報(bào)表的大查詢）來說，即使沒有綁定變量對(duì)數(shù)據(jù)庫(kù)的影響也是有限的，甚至是完全沒有必要的，因?yàn)橹挥猩倭康挠脩艉蜕倭康膕ql操作，數(shù)據(jù)庫(kù)不需要花多少資源在SQL分析上面。

綁定變量的真正的用途是在OLTP系統(tǒng)，OLTP系統(tǒng)通常有這樣的特點(diǎn)：

用戶并發(fā)數(shù)很大，

用戶的請(qǐng)求十分密集，

并且這些sql大多數(shù)是可以重復(fù)使用的。

試想一下，這些成千上萬的SQL一遍又一遍的被數(shù)據(jù)庫(kù)進(jìn)行語(yǔ)法分析，予以分析，生成執(zhí)行計(jì)劃，數(shù)據(jù)庫(kù)的壓力該有多大？

如果一條SQL執(zhí)行一遍之后就被緩存到數(shù)據(jù)庫(kù)的內(nèi)存（實(shí)際上是共享池里），后續(xù)所有的用戶請(qǐng)求的同樣的SQL，都是用共享池中的數(shù)據(jù)，biubiubiu~~~效率是不是提高很多？

所以，當(dāng)你考察綁定變量對(duì)你的數(shù)據(jù)庫(kù)的影響有多大時(shí)，先確定你的系統(tǒng)是OLAP還是OLTP系統(tǒng)。 當(dāng)然現(xiàn)在很多數(shù)據(jù)庫(kù)同事承擔(dān)著這兩種劫色，那么就需要分析數(shù)據(jù)庫(kù)的性能情況了，比如做一個(gè)Statspack Report幫助你確定變量是否綁定，以及是否已對(duì)系統(tǒng)的性能構(gòu)成了嚴(yán)重的影響。

PL/SQL包

如果你的程序里有PL/SQL包，請(qǐng)考慮使用存過來代替它 。

存過是經(jīng)過成功編譯后存放在數(shù)據(jù)庫(kù)中的代碼，執(zhí)行起來的效率比程序代碼中的PL/SQL包的效率高很多，因?yàn)樗恍枰稣Z(yǔ)法和語(yǔ)義的分析。

語(yǔ)法分析：數(shù)據(jù)庫(kù)對(duì)sql進(jìn)行檢查，是否存在語(yǔ)法錯(cuò)誤

語(yǔ)義分析：查看語(yǔ)句執(zhí)行的對(duì)象是否存在，是否有操作權(quán)限等。

數(shù)據(jù)庫(kù)的設(shè)計(jì)

除了一些必要的對(duì)象創(chuàng)建之外，還應(yīng)該更多的考慮一些前瞻性的設(shè)計(jì)，以滿足系統(tǒng)生命周期里的各方面的需求，不至于發(fā)生大的修改或者升級(jí)。

基本上看來，前期數(shù)據(jù)庫(kù)的設(shè)計(jì)一個(gè)根基就是要弄清楚數(shù)據(jù)庫(kù)的類型。

OLTP（在線事物處理系統(tǒng)） 數(shù)據(jù)庫(kù)

OLTP更加強(qiáng)調(diào)數(shù)據(jù)庫(kù)的內(nèi)存效率，強(qiáng)調(diào)各種指標(biāo)的命中率，強(qiáng)調(diào)綁定變量，強(qiáng)調(diào)并發(fā)。

OLTP用戶并發(fā)數(shù)很多，數(shù)據(jù)庫(kù)側(cè)重對(duì)用戶操作的快速響應(yīng)，這是對(duì)數(shù)據(jù)庫(kù)最重要的性能要求。

對(duì)于一個(gè)OLTP系統(tǒng)而言，數(shù)據(jù)庫(kù)內(nèi)存設(shè)計(jì)顯得非常重要，如果數(shù)據(jù)都可以在內(nèi)存中處理，無疑性能會(huì)提高很多， 有些對(duì)數(shù)據(jù)處理速度很高的系統(tǒng)，比如計(jì)費(fèi)系統(tǒng)，已經(jīng)差用了一些內(nèi)存數(shù)據(jù)庫(kù)，比如ORACLE的Times Ten.

內(nèi)存設(shè)計(jì)通常是通過調(diào)整Oracle和內(nèi)存相關(guān)的初始化參數(shù)實(shí)現(xiàn)的。

SGA的大小（Data Buffer ,Shared Pool），PGA的大小（排序區(qū)，Hash區(qū)等）

這些參數(shù)在OLTP的系統(tǒng)中顯得至關(guān)重要。

OLTP數(shù)據(jù)塊的變化非常頻繁，SQL語(yǔ)句提交非常頻發(fā)，

對(duì)于數(shù)據(jù)塊來說，應(yīng)該盡可能的讓數(shù)據(jù)塊保存在內(nèi)存當(dāng)中

對(duì)于SQL來說，盡可能的使用綁定變量來達(dá)到ＳＱＬ的重用，減少物理IO和重復(fù)的SQL解析

關(guān)于初始化參數(shù)的設(shè)置，沒有一個(gè)絕對(duì)的標(biāo)準(zhǔn)，先給出一個(gè)經(jīng)驗(yàn)值 ，需要根據(jù)業(yè)務(wù)不斷的測(cè)試和調(diào)整，已達(dá)到最佳的性能。

除了內(nèi)存、沒有綁定變量的SQL， 熱塊同樣也會(huì)導(dǎo)致數(shù)據(jù)庫(kù)性能的下降。

當(dāng)一個(gè)塊被多個(gè)用戶同時(shí)讀取的時(shí)候，oracle為了維護(hù)數(shù)據(jù)的一致性，需要使用latch來串行化用戶的操作，當(dāng)一個(gè)用戶獲取到了這個(gè)latch之后，其他的用戶就需要被迫等待。 獲取這個(gè)數(shù)據(jù)塊的用戶越多，等待就越明顯，就造成了熱塊問題。

這種熱塊可能是數(shù)據(jù)塊，也可能是回滾段。

對(duì)于數(shù)據(jù)塊來講，通常是數(shù)據(jù)塊上的數(shù)據(jù)分部不均勻?qū)е拢绻撬饕臄?shù)據(jù)塊，可以考慮創(chuàng)建反向索引來達(dá)到重新分布數(shù)據(jù)的目的。

對(duì)于回滾段數(shù)據(jù)塊，可以適當(dāng)?shù)脑黾訋讉€(gè)回滾段來避免爭(zhēng)用。

OLAP（在線分析系統(tǒng)）/DSS（決策支持系統(tǒng)） 數(shù)據(jù)庫(kù)

OLAP著強(qiáng)調(diào)數(shù)據(jù)分析，強(qiáng)調(diào)SQL的執(zhí)行時(shí)長(zhǎng)，強(qiáng)調(diào)磁盤IO，強(qiáng)調(diào)分區(qū)等等

內(nèi)存的優(yōu)化對(duì)于OLAP的影響很小，因?yàn)楹Ａ康臄?shù)據(jù)全部在內(nèi)存中操作是很困難的，同時(shí)也是完全沒必要的，因?yàn)檫@些數(shù)據(jù)塊很少重用，緩存起來意義不大，倒是物理IO相當(dāng)大，這種系統(tǒng)的瓶頸往往在磁盤IO上。

SQL優(yōu)化

對(duì)于OLAP，SQL優(yōu)化顯得非常重要，打個(gè)比方，一個(gè)表只有幾千條數(shù)據(jù)，無論是全表掃描還是使用索引，對(duì)我們來講差異其實(shí)很小。

但是當(dāng)數(shù)據(jù)量達(dá)到幾億或者幾十億甚至更多的時(shí)候，全表掃索引可能導(dǎo)致極大的性能差異。因此SQL優(yōu)化非常重要。

分區(qū)

同樣的，分區(qū)技術(shù)在OLAP系統(tǒng)中也很重要。 這種重要性主要體現(xiàn)在數(shù)據(jù)的管理上。

至于分區(qū)在性能上的影響，不能一概而論，認(rèn)為分區(qū)的性能始終好于非分區(qū)，這個(gè)結(jié)論也是不成立的。

當(dāng)查詢范圍正好落在某個(gè)分區(qū)時(shí)候

這個(gè)時(shí)候，分區(qū)的效率自然是高于沒有分區(qū)的，因?yàn)镾QL咋有分區(qū)的表上只掃一個(gè)分區(qū)的數(shù)據(jù)，而對(duì)于沒有分區(qū)的數(shù)據(jù)，需要掃描整個(gè)表。

當(dāng)查詢范圍跨越幾個(gè)分區(qū)時(shí)

這個(gè)時(shí)候分區(qū)可能并不絕對(duì)是最優(yōu)的，分區(qū)索引并不一定比全局索引在任何時(shí)候都快，有時(shí)候反而會(huì)更慢

數(shù)據(jù)庫(kù)的硬件設(shè)計(jì)

數(shù)據(jù)庫(kù)的硬件設(shè)計(jì)在性能上主要體現(xiàn)在：

CPU

I/O

負(fù)載情況

存儲(chǔ)容量

占用空間的對(duì)象可以在DBA_SEGMENTS視圖中查找到，數(shù)據(jù)庫(kù)的空間分配是以段的形式分配的，凡是段對(duì)象都要占用空間，包括表 視圖 索引 物化視圖 其他一些大對(duì)象等。

存儲(chǔ)的物理設(shè)計(jì)

現(xiàn)在越來越多的數(shù)據(jù)庫(kù)選擇 ＳＡＮ結(jié)構(gòu)，這是一個(gè)擴(kuò)展性非常好的存儲(chǔ)設(shè)計(jì)。

維護(hù)人員不僅要懂得磁盤陣列的技術(shù)，同時(shí)還要掌握ＳＡＮ交換機(jī)的相關(guān)技術(shù)。

數(shù)據(jù)的安全

Data Guard 結(jié)構(gòu)

https://docs.oracle.com/cd/B19306_01/server.102/b14239/toc.htm

如果用戶對(duì)數(shù)據(jù)的安全性要求性非常高，并且對(duì)系統(tǒng)宕機(jī)的時(shí)間要求很高，可以考慮使用Data Guard設(shè)計(jì)結(jié)構(gòu)。

當(dāng)主數(shù)據(jù)庫(kù)出現(xiàn)故障時(shí)，維護(hù)人員可以最短時(shí)間啟用備用數(shù)據(jù)庫(kù)，確保業(yè)務(wù)的正常進(jìn)行。

RAC結(jié)構(gòu)

RAC結(jié)構(gòu)和Data Guard結(jié)構(gòu)分屬于不同級(jí)別的安全設(shè)計(jì)，

Data Guard 能夠保證數(shù)據(jù)不丟失或者盡可能少丟失，

Data Guard 的數(shù)據(jù)庫(kù)級(jí)別是一個(gè)冗余結(jié)構(gòu)。

而

RAC則是實(shí)例級(jí)的一個(gè)冗余結(jié)構(gòu)，它能夠保證數(shù)據(jù)庫(kù)在一個(gè)實(shí)例出現(xiàn)故障后，用戶可以無縫地由另外一個(gè)實(shí)例接管。

現(xiàn)在很多業(yè)務(wù)連續(xù)性很高的系統(tǒng)都采用RAC+Data Guard的 數(shù)據(jù)庫(kù)結(jié)構(gòu)設(shè)計(jì)。

Rman+歸檔方式

Rman+歸檔的備份方式，相對(duì)于RAC+Data Guard來看,

優(yōu)勢(shì)是成本低廉，并且能夠保證數(shù)據(jù)的完整，當(dāng)數(shù)據(jù)庫(kù)損壞時(shí)，可以恢復(fù)到備份的時(shí)間點(diǎn)。

缺點(diǎn)是 需要較長(zhǎng)的宕機(jī)時(shí)間。

exp/imp , expd/impdp

這兩種方式更像是數(shù)據(jù)傳遞或者數(shù)據(jù)保存，它不能保證數(shù)據(jù)的安全。

總結(jié)

首先弄清楚系統(tǒng)是OLAP還是OLTP

系統(tǒng)并發(fā)量，OLTP作為一個(gè)重要的因素

高并發(fā)可能導(dǎo)致

系統(tǒng)資源嚴(yán)重被使用，系統(tǒng)過負(fù)荷運(yùn)行

嚴(yán)重的等待事件，比如熱塊和鎖定

SQL代碼的編寫，SQL優(yōu)化的技巧

數(shù)據(jù)庫(kù)的設(shè)計(jì)

存儲(chǔ)的設(shè)計(jì)

Oracle SQL 數(shù)據(jù)庫(kù)

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。