Oracle 內(nèi)存分配和調(diào)優(yōu) 總結(jié)

Oracle 內(nèi)存分配和調(diào)優(yōu) 總結(jié)
一直都想總結(jié)一下oracle內(nèi)存調(diào)整方面的知識(shí)士复，最近正好優(yōu)化一個(gè)數(shù)據(jù)庫內(nèi)存參數(shù)暇矫，查找一些資料并且google很多下≌崞椋現(xiàn)在記錄下來我擂，做下備份衬以。

概述：

Oracle 的內(nèi)存可以按照共享和私有的角度分為系統(tǒng)全局區(qū)和進(jìn)程全局區(qū)，也就是 SGA和 PGA(process global area or private global area)校摩。對于 SGA 區(qū)域內(nèi)的內(nèi)存來說看峻，是共享的全局的，在 UNIX 上衙吩，必須為 oracle 設(shè)置共享內(nèi)存段（可以是一個(gè)或者多個(gè)）互妓，因?yàn)?oracle 在UNIX 上是多進(jìn)程；而在 WINDOWS 上 oracle 是單進(jìn)程（多個(gè)線程）坤塞，所以不用設(shè)置共享內(nèi)存段冯勉。PGA 是屬于進(jìn)程（線程）私有的區(qū)域。在 Oracle 使用共享服務(wù)器模式下（MTS）,PGA中的一部分摹芙，也就是 UGA 會(huì)被放入共享內(nèi)存 large_pool_size 中灼狰。
發(fā)張圖Oracle內(nèi)存架構(gòu)組成，按照圖上面的顯示可以一目了然關(guān)鍵的參數(shù)和參數(shù)名稱：

Oracle內(nèi)存架構(gòu)

對于 SGA 部分浮禾，我們通過 sqlplus 中查詢可以看到：

SQL> select * from v$sga; 
 
NAME                      VALUE
-------------------- ----------
Fixed Size              1380840
Variable Size        1241517592
Database Buffers     1325400064
Redo Buffers            9613312

SQL> select * from v$sga; 
NAME                      VALUE
-------------------- ----------
Fixed Size              2188768
Variable Size        2603824182
Database Buffers     2872259379
Redo Buffers           20566016

  • Fixed Size:
    Oracle 的不同平臺(tái)和不同版本下可能不一樣交胚，但對于確定環(huán)境是一個(gè)固定的值，里面存儲(chǔ)了 SGA 各部分組件的信息盈电，可以看作引導(dǎo)建立 SGA 的區(qū)域蝴簇。

  • Variable Size :
    包含了 shared_pool_size、java_pool_size挣轨、large_pool_size 等內(nèi)存設(shè)置

  • Database Buffers : 指數(shù)據(jù)緩沖區(qū):
    在 8i 中包 含 db_block_buffer*db_block_size军熏、buffer_pool_keep、buffer_pool_recycle 三 部 分內(nèi) 存 卷扮。
    在 9i 中 包 含 db_cache_size 荡澎、db_keep_cache_size、db_recycle_cache_size晤锹、db_nk_cache_size摩幔。

  • Redo Buffers :
    指日志緩沖區(qū)，log_buffer鞭铆。在這里要額外說明一點(diǎn)的是或衡，對于 v$parameter焦影、v$sgastat、v$sga 查詢值可能不一樣封断。v$parameter 里面的值斯辰，是指用戶在初
    始化參數(shù)文件里面設(shè)置的值，v$sgastat 是 Oracle 實(shí)際分配的日志緩沖區(qū)大衅绿邸（因?yàn)榫彌_區(qū)的分配值實(shí)際上是離散的彬呻，也不是以 block 為最小單位進(jìn)行分配的），
    v$sga 里面查詢的值柄瑰，是在 Oracle 分配了日志緩沖區(qū)后闸氮，為了保護(hù)日志緩沖區(qū)，設(shè)置了一些保護(hù)頁教沾，通常我們會(huì)發(fā)現(xiàn)保護(hù)頁大小大約是 11k(不同環(huán)境可能不一樣)蒲跨。

Oracle 內(nèi)存調(diào)優(yōu)辦法

當(dāng)項(xiàng)目的生產(chǎn)環(huán)境出現(xiàn)性能問題，我們?nèi)绾瓮ㄟ^判斷那些參數(shù)需要調(diào)整呢授翻？

檢查ORACLE實(shí)例的Library Cache命中率:

標(biāo)準(zhǔn):一般是大于99％

檢查方式:

SQL> select 1-(sum(reloads)/sum(pins)) "Library cache Hit Ratio" from v$librarycache;
 
Library cache Hit Ratio
-----------------------
      0.995500931581541

處理措施:
如果Library cache Hit Ratio的值低于99%或悲，應(yīng)調(diào)高shared_pool_size的大小。通過sqlplus連接數(shù)據(jù)庫執(zhí)行如下命令藏姐，調(diào)整shared_pool_size的大新÷帷：

SQL>alter system flush shared_pool;
SQL>alter system set shared_pool_size=設(shè)定值 scope=spfile;

檢查ORACLE實(shí)例的Data Buffer（數(shù)據(jù)緩沖區(qū)）命中率

標(biāo)準(zhǔn):一般是大于90%

檢查方式:

SQL> select 1 - (phy.value / (cur.value + con.value)) "HIT RATIO"
  2  from v$sysstat cur, v$sysstat con, v$sysstat phy
  3  where cur.name = 'db block gets'
  4  and con.name = 'consistent gets'
  5   and phy.name = 'physical reads';
 
 HIT RATIO
----------
0.97678192

處理措施:
如果HIT RATIO的值低于90%，應(yīng)調(diào)高db_cache_size的大小羔杨。通過sqlplus連接數(shù)據(jù)庫執(zhí)行如下命令捌臊，
調(diào)整db_cache_size的大小

SQL>alter system set db_cache_size=設(shè)定值 scope=spfile

檢查ORACLE實(shí)例的Dictionary Cache命中率

標(biāo)準(zhǔn):一般是大于95%

檢查方式:

SQL> select 1 - (sum(getmisses) / sum(gets)) "Data Dictionary Hit Ratio"
  2    from v$rowcache;
 
Data Dictionary Hit Ratio
-------------------------
        0.999701677408011

處理措施:
如果Data Dictionary Hit Ratio的值低于95%，應(yīng)調(diào)高shared_pool_size的大小兜材。通過sqlplus連接數(shù)據(jù)庫執(zhí)行如下命令理澎，調(diào)整shared_pool_size的大小：

SQL>alter system flush shared_pool;
SQL>alter system set shared_pool_size=設(shè)定值 scope=spfile;

檢查ORACLE實(shí)例的Log Buffer命中率

標(biāo)準(zhǔn):一般是小于1%

檢查方式:

SQL> select (req.value * 5000) / entries.value "Ratio"
  2    from v$sysstat req, v$sysstat entries
  3   where req.name = 'redo log space requests'
  4     and entries.name = 'redo entries';
 
     Ratio
----------
0.08082257

處理措施:
如果Ratio高于1％曙寡，應(yīng)調(diào)高log_buffer的大小糠爬。通過sqlplus連接數(shù)據(jù)庫執(zhí)行如下命令，調(diào)整log_buffer的大芯偈：

SQL>alter system set log_buffer=設(shè)定值 scope=spfile;

檢查undo_retention

標(biāo)準(zhǔn):undo_retention 的值必須大于max(maxquerylen)的值

檢查方式:

SQL> col undo_retention format a30
SQL> select value "undo_retention" from v$parameter where name='undo_retention';
 
undo_retention
------------------------------
900
SQL> select max(maxquerylen) From v$undostat Where begin_time>sysdate-(1/4);
 
MAX(MAXQUERYLEN)
----------------
            1253

處理措施:
如果不滿足要求执隧，需要調(diào)高undo_retention 的值。通過sqlplus 連接數(shù)據(jù)庫執(zhí)行如下命
令户侥，調(diào)整undo_retention 的大卸屏稹：

SQL>alter system set undo_retention= 設(shè)定值 scope=spfile;

SQL> alter system set undo_retention=2048 scope=spfile;
 System altered

簡單查看ORACLE的連接情況和內(nèi)存使用情況

查看連接Oracle的所有機(jī)器的連接數(shù)

-- 查看連接Oracle的所有機(jī)器的連接數(shù) 
SELECT MACHINE,COUNT(*) FROM V$SESSION GROUP BY MACHINE;

SQL> SELECT MACHINE,COUNT(*) FROM V$SESSION GROUP BY MACHINE;
 
MACHINE                                                            COUNT(*)
---------------------------------------------------------------- ----------
WIN-Q9I6NL3EJCU                                                          28
USER-20151208SP\USER-20151208SP                                          14
WORKGROUP\WIN-Q9I6NL3EJCU                                                 6
WORKGROUP\USER-20151208SP                                                 7
IIS APPPOOL\WIN-Q9I6NL3EJCU                                              14

查看連接Oracle的所有機(jī)器的連接數(shù)和狀態(tài)

-- 查看連接Oracle的所有機(jī)器的連接數(shù)和狀態(tài)
SELECT MACHINE,STATUS,COUNT(*) FROM V$SESSION GROUP BY MACHINE,STATUS ORDER BY STATUS;

SQL> SELECT MACHINE,STATUS,COUNT(*) FROM V$SESSION GROUP BY MACHINE,STATUS ORDER BY STATUS;
 
MACHINE                                                          STATUS     COUNT(*)
---------------------------------------------------------------- -------- ----------
WIN-Q9I6NL3EJCU                                                  ACTIVE           26
WORKGROUP\USER-20151208SP                                        ACTIVE            1
WORKGROUP\WIN-Q9I6NL3EJCU                                        ACTIVE            1
IIS APPPOOL\WIN-Q9I6NL3EJCU                                      INACTIVE         13
USER-20151208SP\USER-20151208SP                                  INACTIVE         14
WIN-Q9I6NL3EJCU                                                  INACTIVE          4
WORKGROUP\USER-20151208SP                                        INACTIVE          6
WORKGROUP\WIN-Q9I6NL3EJCU                                        INACTIVE          5
 
8 rows selected

Oracle的PGA、SGA和process count

-- Oracle的PGA蕊唐、SGA和process count
SELECT 'SGA' AS NAME,ROUND(SUM(VALUE)/1024/1024,2)||'M' AS "SIZE(M)" FROM V$SGA
UNION
SELECT 'PGA' AS NAME,ROUND(VALUE/1024/1024,2)||'M' AS "SIZE(M)" FROM V$PGASTAT WHERE NAME='TOTAL PGA ALLOCATED'
UNION
SELECT 'TOTAL' AS NAME,((SELECT ROUND(SUM(VALUE)/1024/1024,2) FROM V$SGA)+(SELECT ROUND(VALUE/1024/1024,2) FROM V$PGASTAT WHERE NAME='TOTAL PGA ALLOCATED'))||'M' AS "SIZE(M)" FROM DUAL
UNION
SELECT NAME,TO_CHAR(VALUE) FROM V$PGASTAT WHERE NAME='PROCESS COUNT';

SQL> SELECT 'SGA' AS NAME,ROUND(SUM(VALUE)/1024/1024,2)||'M' AS "SIZE(M)" FROM V$SGA
  2  UNION
  3  SELECT 'PGA' AS NAME,ROUND(VALUE/1024/1024,2)||'M' AS "SIZE(M)" FROM V$PGASTAT WHERE NAME='TOTAL PGA ALLOCATED'
  4  UNION
  5  SELECT 'TOTAL' AS NAME,((SELECT ROUND(SUM(VALUE)/1024/1024,2) FROM V$SGA)+(SELECT ROUND(VALUE/1024/1024,2) FROM V$PGASTAT WHERE NAME='TOTAL PGA ALLOCATED'))||'M' AS "SIZE(M)" FROM DUAL
  6  UNION
  7  SELECT NAME,TO_CHAR(VALUE) FROM V$PGASTAT WHERE NAME='PROCESS COUNT';
 
NAME                                                             SIZE(M)
---------------------------------------------------------------- -----------------------------------------
SGA                                                              52245.7M
TOTAL                                                            M
最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
  • 人面猴
    序言：七十年代末屋摔，一起剝皮案震驚了整個(gè)濱河市，隨后出現(xiàn)的幾起案子替梨，更是在濱河造成了極大的恐慌钓试，老刑警劉巖装黑，帶你破解...
    沈念sama閱讀 218,525評論 6贊 507
  • 死咒
    序言：濱河連續(xù)發(fā)生了三起死亡事件，死亡現(xiàn)場離奇詭異弓熏，居然都是意外死亡恋谭，警方通過查閱死者的電腦和手機(jī)，發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 93,203評論 3贊 395
  • 救了他兩次的神仙讓他今天三更去死
    文/潘曉璐 我一進(jìn)店門硝烂，熙熙樓的掌柜王于貴愁眉苦臉地迎上來箕别，“玉大人铜幽，你說我怎么就攤上這事滞谢。” “怎么了除抛？”我有些...
    開封第一講書人閱讀 164,862評論 0贊 354
  • 道士緝兇錄：失蹤的賣姜人
    文/不壞的土叔 我叫張陵狮杨，是天一觀的道長。 經(jīng)常有香客問我到忽，道長橄教，這世上最難降的妖魔是什么？ 我笑而不...
    開封第一講書人閱讀 58,728評論 1贊 294
  • ?港島之戀（遺憾婚禮）
    正文 為了忘掉前任喘漏，我火速辦了婚禮护蝶，結(jié)果婚禮上，老公的妹妹穿的比我還像新娘翩迈。我一直安慰自己持灰，他們只是感情好，可當(dāng)我...
    茶點(diǎn)故事閱讀 67,743評論 6贊 392
  • 惡毒庶女頂嫁案：這布局不是一般人想出來的
    文/花漫 我一把揭開白布负饲。 她就那樣靜靜地躺著堤魁，像睡著了一般。 火紅的嫁衣襯著肌膚如雪返十。 梳的紋絲不亂的頭發(fā)上妥泉，一...
    開封第一講書人閱讀 51,590評論 1贊 305
  • 城市分裂傳說
    那天，我揣著相機(jī)與錄音洞坑，去河邊找鬼盲链。 笑死，一個(gè)胖子當(dāng)著我的面吹牛迟杂，可吹牛的內(nèi)容都是我干的刽沾。 我是一名探鬼主播，決...
    沈念sama閱讀 40,330評論 3贊 418
  • 雙鴛鴦連環(huán)套：你想象不到人心有多黑
    文/蒼蘭香墨 我猛地睜開眼逢慌，長吁一口氣：“原來是場噩夢啊……” “哼悠轩！你這毒婦竟也來了？” 一聲冷哼從身側(cè)響起攻泼，我...
    開封第一講書人閱讀 39,244評論 0贊 276
  • 萬榮殺人案實(shí)錄
    序言：老撾萬榮一對情侶失蹤火架，失蹤者是張志新（化名）和其女友劉穎鉴象，沒想到半個(gè)月后，有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體何鸡，經(jīng)...
    沈念sama閱讀 45,693評論 1贊 314
  • ?護(hù)林員之死
    正文 獨(dú)居荒郊野嶺守林人離奇死亡纺弊，尸身上長有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 37,885評論 3贊 336
  • ?白月光啟示錄
    正文 我和宋清朗相戀三年，在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了骡男。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片淆游。...
    茶點(diǎn)故事閱讀 40,001評論 1贊 348
  • 活死人
    序言：一個(gè)原本活蹦亂跳的男人離奇死亡，死狀恐怖隔盛，靈堂內(nèi)的尸體忽然破棺而出犹菱，到底是詐尸還是另有隱情，我是刑警寧澤吮炕，帶...
    沈念sama閱讀 35,723評論 5贊 346
  • ?日本核電站爆炸內(nèi)幕
    正文 年R本政府宣布腊脱，位于F島的核電站，受9級(jí)特大地震影響龙亲，放射性物質(zhì)發(fā)生泄漏陕凹。R本人自食惡果不足惜，卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 41,343評論 3贊 330
  • 男人毒藥：我在死后第九天來索命
    文/蒙蒙 一鳄炉、第九天 我趴在偏房一處隱蔽的房頂上張望杜耙。 院中可真熱鬧，春花似錦拂盯、人聲如沸佑女。這莊子的主人今日做“春日...
    開封第一講書人閱讀 31,919評論 0贊 22
  • 一樁弒父案磕仅，背后竟有這般陰謀
    文/蒼蘭香墨 我抬頭看了看天上的太陽珊豹。三九已至，卻和暖如春榕订，著一層夾襖步出監(jiān)牢的瞬間店茶，已是汗流浹背。 一陣腳步聲響...
    開封第一講書人閱讀 33,042評論 1贊 270
  • 情欲美人皮
    我被黑心中介騙來泰國打工劫恒， 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留贩幻，地道東北人。 一個(gè)月前我還...
    沈念sama閱讀 48,191評論 3贊 370
  • 代替公主和親
    正文 我出身青樓两嘴，卻偏偏與公主長得像丛楚，于是被迫代替她去往敵國和親。 傳聞我的和親對象是個(gè)殘疾皇子憔辫，可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 44,955評論 2贊 355