oracle10g sga手工管理mmm之一

wisdomone1發表於2015-09-21
Oracle

背景

     oracle10g隨著提供了SGA自動管理功能，不用DBA去手工依次配置各個子元件的大小，交由ORACLE動態管理，但是，在一些特定場合下，仍需要
我們禁用SGA自動管理功能，使用SGA手工管理功能。




結論

1,sga手工管理，僅需配置sga_target為0或不配置此引數
2,sga手工管理模式下，其子元件必須手工指定值，否則會報錯
3,sga手工管理模式下，共享池大小配置情況如下：
   A,必須要考慮用於管理SGA各元件且儲存於共享池中的固定記憶體大小這塊的成本開銷
   B,到於A的資訊可從v$sgainfo獲取
   C,ORACLE11G R1前共享池真正的大小是由2部分組成,shared_pool_size + 管理SGA各元件且儲存於共享池中的固定記憶體大小這塊的成本開銷
     而從oracle11g r1開始，管理SGA各元件且儲存於共享池中的固定記憶體大小這塊的成本開銷已經納入shared_pool_size中，則共享池大小=shared_pool_size
4,如果以PFILE啟動ORACLE，不配置共享池，ORACLE會預設使用一個值
5,如果以PFILE啟動ORACLE，如果配置共享池過小，會報ora-431錯誤
6,關於SGA當前還有多少可用空閒記憶體可以從v$sgainfo的Free SGA Memory Available看出來


測試

1，啟用SGA手工管理，只要配置sga_target為0或不配置此引數即可，此時你必須手工指定SGA各個子元件的大小


 ---SGA自動管理情況下的引數配置資訊
SQL> show parameter sga_


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 1104M
sga_target                           big integer 944M
SQL> 
SQL> 
SQL> show parameter _cache_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 16M
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 512M
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0
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 64M
streams_pool_size                    big integer 0
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                             30 No
Buffer Cache Size                       736 Yes
Shared Pool Size                        112 Yes
Large Pool Size                          16 Yes
Java Pool Size                           16 Yes
Streams Pool Size                        32 Yes
Granule Size                             16 No
Maximum SGA Size                       1104 No
Startup overhead in Shared Pool          48 No
Free SGA Memory Available               160


11 rows selected.


SQL> 


--SGA手工管理的引數配置資訊
SQL> alter system set sga_target=0;


System altered.


SQL> show parameter sga_


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 1104M
sga_target                           big integer 0


SQL> show parameter _cache_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 16M
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 720M
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0




SQL> show parameter _pool_size


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




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                             30 No
Buffer Cache Size                       736 Yes
Shared Pool Size                        112 Yes
Large Pool Size                          16 Yes
Java Pool Size                           16 Yes
Streams Pool Size                        32 Yes
Granule Size                             16 No
Maximum SGA Size                       1104 No
Startup overhead in Shared Pool          48 No
Free SGA Memory Available               160


11 rows selected.




SQL> alter system set shared_pool_size=220m;


System altered.


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                             30 No
Buffer Cache Size                       736 Yes
Shared Pool Size                        224 Yes
Large Pool Size                          16 Yes
Java Pool Size                           16 Yes
Streams Pool Size                        32 Yes
Granule Size                             16 No
Maximum SGA Size                       1104 No
Startup overhead in Shared Pool          48 No
Free SGA Memory Available                48


在啟用SGA手工管理情況下，SGA各子元件不能指定為0
SQL> alter system set shared_pool_size=0;
alter system set shared_pool_size=0
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04034: unable to shrink pool to specified size


在啟用SGA手工管理情況下，如果指定共享池過大，也不行，因為可能會超過sga_max_size
SQL> alter system set shared_pool_size=1220m;
alter system set shared_pool_size=1220m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04033: Insufficient memory to grow pool






SQL> alter system set db_cache_size=0;
alter system set db_cache_size=0
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00383: DEFAULT cache for blocksize 8192 cannot be reduced to zero




SQL> alter system set db_cache_size=0 scope=spfile;
alter system set db_cache_size=0 scope=spfile
*
ERROR at line 1:
ORA-32017: failure in updating SPFILE
ORA-00383: DEFAULT cache for blocksize 8192 cannot be reduced to zero


java_pool就是配置為0，實際還是有值
SQL> show parameter _pool_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size             string
java_pool_size                       big integer 16M
large_pool_size                      big integer 16M
olap_page_pool_size                  big integer 0
shared_pool_size                     big integer 128M
streams_pool_size                    big integer 32M
SQL> alter system set java_pool_size=0;


System altered.


SQL> show parameter _pool_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
global_context_pool_size             string
java_pool_size                       big integer 16M
large_pool_size                      big integer 16M
olap_page_pool_size                  big integer 0
shared_pool_size                     big integer 128M
streams_pool_size                    big integer 32M


--關於SGA還有多少可用空閒記憶體可以從v$sgainfo的Free SGA Memory Available看出來
SQL> alter system set shared_pool_size=120m;


System altered.


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                             30 No
Buffer Cache Size                       736 Yes
Shared Pool Size                        128 Yes
Large Pool Size                          16 Yes
Java Pool Size                           16 Yes
Streams Pool Size                        32 Yes
Granule Size                             16 No
Maximum SGA Size                       1104 No
Startup overhead in Shared Pool          48 No
Free SGA Memory Available               144


11 rows selected.


2,在SGA手工管理模式下，如何配置共享池大小


---檢視shared_pool_size及為了管理SGA各個元件且駐存於共享池中的固定成本記憶體，可見2者分別為128m和48m
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                             30 No
Buffer Cache Size                       736 Yes
Shared Pool Size                        128 Yes
Large Pool Size                          16 Yes
Java Pool Size                           16 Yes
Streams Pool Size                        32 Yes
Granule Size                             16 No
Maximum SGA Size                       1104 No
Startup overhead in Shared Pool          48 No
Free SGA Memory Available               144


11 rows selected.


SQL> show parameter shared_pool_size


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size                     big integer 128M


檢視隱含引數也可獲在共享池的實際大小
NAME_1                         VALUE_1                                                                                              DESC1
------------------------------ ---------------------------------------------------------------------------------------------------- --------------------------------------------------
__shared_pool_size             134217728                                                                                            Actual size in bytes of shared pool




不能把共享池大小配置過小，否則會報錯
SQL> alter system set shared_pool_size=90m;
alter system set shared_pool_size=90m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04034: unable to shrink pool to specified size




SQL> alter system set shared_pool_size=100m;


System altered.


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                             30 No
Buffer Cache Size                       736 Yes
Shared Pool Size                        112 Yes
Large Pool Size                          16 Yes
Java Pool Size                           16 Yes
Streams Pool Size                        32 Yes
Granule Size                             16 No
Maximum SGA Size                       1104 No
Startup overhead in Shared Pool          48 No
Free SGA Memory Available               160


11 rows selected.




ORACLE 11G R1之前，共享池大小=shared_pool_size + Startup overhead in Shared Pool 


而從oracle 11g r1之後，共享池大小=shared_pool_size,因為Startup overhead in Shared Pool已經包括在shared_pool_size中。
但不管如何配置，真正可用的共享池大小一定要去除Startup overhead in Shared Pool 
而Startup overhead in Shared Pool 可以從v$sgainfo獲知。






如果在SGA手工管理模式下，shared_pool_size配置過小，會報ora-371錯誤
SQL> show parameter spfile


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /home/ora10g/product/10.2.0/db
                                                 _1/dbs/spfileora10g.ora


SQL> create pfile='/home/ora10g/sga_manual.ora' from spfile;


File created.




[ora10g@seconary ~]$ more /home/ora10g/sga_manual.ora|grep -i --color shared_pool_size
ora10g.__shared_pool_size=117440512
*.shared_pool_size=117440512


讞整前共享池為112M
SQL> select 117440512/1024/1024 mb from dual;


        MB
----------
       112


調整共享池後，發現共享池大小配置為96M，可見不配置shared_pool_size引數，預設配置為96M
[ora10g@seconary ~]$ more /home/ora10g/sga_manual.ora|grep -i --color shared_pool_size
ora10g.__shared_pool_size=117440512
#*.shared_pool_size=117440512




SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/home/ora10g/sga_manual.ora
ORACLE instance started.


Total System Global Area 1157627904 bytes
Fixed Size                  2095800 bytes
Variable Size             352322888 bytes
Database Buffers          771751936 bytes
Redo Buffers               31457280 bytes
Database mounted.
Database opened.
SQL> show parameter sga_


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 1104M
sga_target                           big integer 0
SQL> show parameter shared_pool


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size            big integer 5033164
shared_pool_size                     big integer 96M


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                             30 No
Buffer Cache Size                       736 Yes
Shared Pool Size                         96 Yes
Large Pool Size                          16 Yes
Java Pool Size                           16 Yes
Streams Pool Size                        32 Yes
Granule Size                             16 No
Maximum SGA Size                       1104 No
Startup overhead in Shared Pool          48 No
Free SGA Memory Available               176


11 rows selected.




如果配置shared_pool_size過小，又會如何,會報ora-431錯誤,當然這種情況只會出現於SGA手工管理方式了喲
[ora10g@seconary ~]$ more /home/ora10g/sga_manual.ora|grep -i --color shared_pool_size
ora10g.__shared_pool_size=117440512
*.shared_pool_size=20m


SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup pfile='/home/ora10g/sga_manual.ora
ORA-00371: not enough shared pool memory, should be atleast 148397977 bytes
SQL> 


可見共享池最小配置為141M左右
SQL> select 148397977/1024/1024 from dual;


148397977/1024/1024
-------------------
         141.523339






3,在SGA手工管理模式下，如何配置大池，首先大池是可選引數，前提是你必須使用大池的功能


SQL> show parameter large_


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 16M         


個人簡介


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/




來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-1805448/，如需轉載，請註明出處，否則將追究法律責任。

相關文章