背景

10g啟動了SGA自動管理，即SGA內部各元件之共享池，DB CADHE，JAVA POOL等子池，由資料庫視負載自行調整，大為簡化了運維工作量。
但是由於10G中SGA自動管理，可能會引發很多效能問題，所以也可採用SGA手工管理方式，即手工指定SGA各元件DB CADHE，JAVA POOL等子池
的大小，防止抖動非常嚴重。
本文是基於ORACLE 10.2.0.5資料庫進行測試。

結論

1,sga自動記憶體管理即配置sga_target為非0
2,sga自動記憶體管理又分為2種方式，
第1種方式為SGA全自動記憶體管理方式，即sga_target配置為非0，且其sga的自動調整的元件全指定為0
這樣就是讓ORACLE全權去調整SGA各元件

第2種方式為SGA半自動管理方式（為了區別全自動記憶體管理方式，我換一個命名），即配置sga_target為非0，且其想控制的SGA自動調整子元件為非0的最小值
這樣就是讓SGA對應的子元件在動態調整時，不會小於這個指定的值，前提是你非常瞭解自己的ORACLE資料庫業務負載，否則會喪失SGA自動記憶體管理的優點
3，SGA子元件分為可以自動調整的元件，以及不能自動調整的元件，即固定大小
關於這塊資訊，請參考測試部分的第3點之3.1和3.2，或者檢視V$SGAINFO

4，sga_target不能大於sga_max_size
5, sga各個元件分配是以granule為單位進行分配以及回收的，關於granule請參考測試第1的內容
6, v$sgainfo及v$dynamic_sga_components會顯示SGA各子組當前實際分配的大小
7，本文僅測試SGA自動記憶體管理方式，至於SGA手工記憶體管理方式不在本文範圍

測試

1,ORACLE SGA它是一塊的大的連續的記憶體區域，由許多小的元件構成，比如:DB CACHE SIZE,JAVA POOL,LARGE POOL,SHARED POOL等組成；
這些元件每次分配以收回收記憶體是由GRANULES為單位進行，當然這個記憶體分配同由ORACLE底層控制，我們無法手工控制。

至於GRUNALE到底多大，由SGA總的大小決定；這麼說吧，在多數作業系統平臺，
如果SGA小於1G，則GRANULES為4M
如果SGA大於1G，則GRANULE為16M
也可能在WINDOW 32BIT上面，若SGA大於1G，則GRANULES為8M
當然具體可能還需要你檢視對應的官方手冊獲知對應資訊。

SQL> show parameter sga_max

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 348M
SQL> select name,bytes/1024/1024 as mb,resizeable from v$sgainfo where name='Granule Size';

NAME MB RES
-------------------------------- ---------- ---
Granule Size 4 No

SQL> show parameter sga_max

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1104M
SQL> select name,bytes/1024/1024 as mb,resizeable from v$sgainfo where name='Granule Size';

NAME MB RES
-------------------------------- ---------- ---
Granule Size 16 No

每個SGA元件分配是以GRANULE為單位分配的，如果指定的元件大小不是GRANULE整數倍，則四捨五入處理
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 5033164
shared_pool_size big integer 96M
SQL> alter system set shared_pool_size=100m;

System altered.

SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 5033164
shared_pool_size big integer 112M
SQL>

2,為了啟用SGA自動管理，statistics_level必須配置為typical或all
SQL> show parameter statistics_level

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
statistics_level string TYPICAL

SQL> select name,value,isdefault from v$parameter_valid_values where name='statistics_level';

NAME VALUE ISDEFAULT
-------------------------------------------------- ---------- ----------
statistics_level BASIC FALSE
statistics_level TYPICAL TRUE
statistics_level ALL FALSE

SQL> alter system set statistics_level='basic';
alter system set statistics_level='basic'
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00830: cannot set statistics_level to BASIC with auto-tune SGA enabled

3,啟用sga自動管理，只要配置sga_target=非0即可；
3.1，sga_target的值由下述子元件構成（自動調整）
---------------------------------------------------------------------------
sga元件對應的引數
---------------------------------------------------------------------------
固定sga及由用於其它方面的ORACLE內部分配的記憶體區域無

大池 large_pool_size

java池 java_pool_size

buffer cache db_cache_size

流池 streams_pool_size
---------------------------------------------------------------------------

3.2，下面的元件仍然使用sga_target（人工調整）
---------------------------------------------------------------------------
sga元件對應的引數
---------------------------------------------------------------------------
log buffer log_buffer
keep and recycle buffer caches db_keep_cache_size,db_recycle_cache_size
非標準塊的buffer caches db_nk_cache_size
---------------------------------------------------------------------------

4，開啟SGA自動管理功能，除了配置sga_target為非0，必須配置上述第3步的自動調整的子元件全為0，以開啟這些子元件全自動調整；
還有另一種方法，也可以配置上述第3步的自動調整的子元件為非0值，這樣起到什麼作用呢，即在SGA各元件自動調整時，每個元件調整時不會調整
到指定的值。

file:///Volumes/Untitled/作業系統介質/官方手冊/oracle10gr2官方文件/B19306_01/server.102/b14231/create.htm#sthref383

5,下面開始實施SGA全自動管理的具體過程
什麼叫作SGA全自動管理，即SGA各子元件完全由ORACLE自行控制其大小，根據業務負載動態調整，每個子元件沒有調整的最低限值。

6，為了實施SGA全自動管理，須先計算SGA_TARGET所需的值為多少

---當前SGA配置的值（關於這2個檢視請參考官方手冊）
SQL> select sum(value) from v$sga;

SUM(VALUE)
----------
1157627904

---當前SGA從未使用過的空閒記憶體
SQL> select current_size from v$sga_dynamic_free_memory;

CURRENT_SIZE
------------
100663296

--綜上可知SGA_target的計算公式為：
select ((select sum(value) from v$sga) - (select current_size from v$sga_dynamic_free_memory)
)/1024/1024
as "sga_target"
from dual;

所以要配置的sga_target則為
sga_target=1008m

或者sga_target的值也可以是sga各子元件之和，也可以是小於sga_max_size的值

7，現在開啟SGA全自動管理,即就是把SGA自動調整的子元件全部調整為0
--先檢視調整前的SGA各元件配置
SQL> show parameter sga_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1104M
sga_target big integer 1008M
SQL> show parameter _pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size string
java_pool_size big integer 16M
large_pool_size big integer 48M
olap_page_pool_size big integer 0
shared_pool_size big integer 112M
streams_pool_size big integer 64M

--調整SGA動態調整元件為0

SQL> select 1056964608/1024/1024 as sga_target from dual;

SGA_TARGET
----------
1008

SQL> alter system set sga_target=1008m;

System altered.

SQL> alter system set shared_pool_size=0;

System altered.

SQL> alter system set large_pool_size=0;

System altered.

SQL> alter system set java_pool_size=0;

System altered.

SQL> alter system set streams_pool_size=0;

System altered.

SQL>
SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 144M
SQL> alter system set db_cache_size=0;

System altered.

SQL>

8，重啟資料庫
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1157627904 bytes
Fixed Size 2095800 bytes
Variable Size 335545672 bytes
Database Buffers 805306368 bytes
Redo Buffers 14680064 bytes
Database mounted.
Database opened.

9，確認開啟SGA全自動記憶體管理
SQL> show parameter sga_

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sga_max_size big integer 1104M
sga_target big integer 1008M
SQL> show parameter _pool_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size string
java_pool_size big integer 0
large_pool_size big integer 0
olap_page_pool_size big integer 0
shared_pool_size big integer 0
streams_pool_size big integer 0
SQL> show parameter db_cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_size big integer 0
SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string OFF
db_cache_size big integer 0
SQL>

10,上次我們測試了SGA自動記憶體管理的具體過程，但如果你想控制某些SGA自動調整元件的最小大小，可以為其子元件配置最小值，這樣的好處就是在
SGA動態調整期間，其子元件調整值不會小於這個指定的值，防止抖動過於嚴重，引發嚴重效能問題。（建議生產環境採用這種方式）

11，哪麼如何配置這種SGA自動記憶體管理的方式，其實也很簡單，就是指定每個動態調整子元件為非0值即可。

---比如你想讓db_cache_size及shared_pool_size不能小於某個值，因為這2個元件非常重要
SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string OFF
db_cache_size big integer 0
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 5872025
shared_pool_size big integer 0

SQL> alter system set db_cache_size=500m;

System altered.

SQL> alter system set shared_pool_size=100m;

System altered.

SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string OFF
db_cache_size big integer 512M
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 5872025
shared_pool_size big integer 112M
SQL>

12,重啟資料庫再次進行驗證db_cache_size及shared_pool_size,這種方式其實是ORACLE不建議，除非你非常瞭解自己的應用業務特徵，否則就會損失動態調整的好處，以及有效利用資源的機會。
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1157627904 bytes
Fixed Size 2095800 bytes
Variable Size 335545672 bytes
Database Buffers 805306368 bytes
Redo Buffers 14680064 bytes
Database mounted.
Database opened.
SQL>
SQL> show parameter db_cache

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_cache_advice string OFF
db_cache_size big integer 512M
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 5872025
shared_pool_size big integer 112M
SQL>

13,可以透過如下檢視檢視sga各元件的當前實際大小

---v$sgainfo可以區分出SGA子元件分為動態調整和固定大小2種型別
SQL> select name,bytes/1024/1024 as mb,RESIZEABLE as mb from v$sgainfo;

NAME MB MB
-------------------------------- ---------- ---
Fixed SGA Size 1.99871063 No
Redo Buffers 14 No
Buffer Cache Size 768 Yes --這裡可以當前DB_CACHE_SIZE實際大小為768M，而db_cache_size指定的最小值為512M
Shared Pool Size 112 Yes
Large Pool Size 32 Yes
Java Pool Size 16 Yes
Streams Pool Size 64 Yes
Granule Size 16 No
Maximum SGA Size 1104 No
Startup overhead in Shared Pool 48 No
Free SGA Memory Available 96

11 rows selected.

個人簡介

8年oracle從業經驗，具備豐富的oracle技能，目前在國內北京某專業oracle服務公司從事高階技術顧問。

服務過的客戶：

中國電信

中國移動

中國聯通

中國電通

國家電網

四川達州商業銀行

湖南老百姓大藥房

山西省公安廳

中國郵政

北京302醫院

河北廊坊新奧集團公司

專案經驗:

中國電信3G專案AAA系統資料庫部署及最佳化
中國聯通4G資料庫效能分析與最佳化

中國聯通CRM資料庫效能最佳化

中國移動10086電商平臺資料庫部署及最佳化

湖南老百姓大藥房ERR資料庫sql最佳化專案

四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化

四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化

北京高鐵訊號監控系統RAC資料庫部署及最佳化

河南宇通客車資料庫效能最佳化

中國電信電商平臺核心採購模組表模型設計及最佳化

中國郵政儲蓄系統資料庫效能最佳化及sql最佳化

北京302醫院資料庫遷移實施

河北廊坊新奧data guard部署及最佳化

山西公安廳身份證審計資料庫系統故障評估

國家電網上海災備專案4 node rac+adg
貴州移動crm及客服資料庫效能最佳化專案
貴州移動crm及客服務資料庫sql稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案

聯絡方式：

手機：18201115468

qq : 305076427

qq微博: wisdomone1

新浪微博：wisdomone9

qq群：275813900

itpub部落格名稱：wisdomone1 http://blog.itpub.net/9240380/

開啟oracle 10g的sga自動管理-amm

背景

結論

測試

個人簡介

相關文章