oracle11g memory management系列(一)
1,自oracle11g始,sga和pga管理可以由oracle完全自動管理
2,11g記憶體管理有2種方法:自動管理(指定一個記憶體總大小);
手工管理(想直接手工控制各個記憶體元件使用)
2,11g記憶體管理有2種方法:自動管理(指定一個記憶體總大小);
手工管理(想直接手工控制各個記憶體元件使用)
3,記憶體結構:
1,sga
a,sga由後臺程式和server程式共享
b,sga包括資料塊和控制結構
2,pga
a,pga排它性,僅由建立它的server process獨享
b,給後臺程式也會分配pga,比如lgwr,可以查閱v$process
3,flush cache
a,11g新記憶體元件
b,駐存在sga基礎之上
c,Database Smart Flash Cache (the flash cache),
d,為資料塊提供2級快取功能,尤其適用於讀密集型的oltp環境;大幅提升響度時間和整體吞吐量
e,flush cache基於一個或多個flash disk裝置,這些固態儲存裝置使用flash memory
(注:如何配置flush cache請參閱:)
f,須執行linux平臺
4,開啟自動記憶體管理(即同時管控sga和pga)
alter system set memory_max_target=ngb scope=spfile;--ngb是要設定的值,此引數為靜態引數,須重啟庫
alter system set memory_target=ngb;--動態引數,只要在memory_max_target範圍內即可
ALTER SYSTEM SET SGA_TARGET = 0;--關閉手工管理sga和pga
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;--同上
---重啟庫,生效上述引數
shutdown immediate
startup
--memory_max_target的值如何配置呢,oracle提供如下方法:
---查閱為pga分配的最大值
SQL> select * from v$pgastat ps where ps.name='maximum PGA allocated';
NAME VALUE UNIT
---------------------------------------------------------------- ---------- ------------
maximum PGA allocated 91763712 bytes
--memory_target(或memory_max_target)的值:
--如實體記憶體非常充足,可以考慮在其基礎上適當增加大小
memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated)
----監控oracle記憶體結構
1,sga
a,sga由後臺程式和server程式共享
b,sga包括資料塊和控制結構
2,pga
a,pga排它性,僅由建立它的server process獨享
b,給後臺程式也會分配pga,比如lgwr,可以查閱v$process
3,flush cache
a,11g新記憶體元件
b,駐存在sga基礎之上
c,Database Smart Flash Cache (the flash cache),
d,為資料塊提供2級快取功能,尤其適用於讀密集型的oltp環境;大幅提升響度時間和整體吞吐量
e,flush cache基於一個或多個flash disk裝置,這些固態儲存裝置使用flash memory
(注:如何配置flush cache請參閱:)
f,須執行linux平臺
4,開啟自動記憶體管理(即同時管控sga和pga)
alter system set memory_max_target=ngb scope=spfile;--ngb是要設定的值,此引數為靜態引數,須重啟庫
alter system set memory_target=ngb;--動態引數,只要在memory_max_target範圍內即可
ALTER SYSTEM SET SGA_TARGET = 0;--關閉手工管理sga和pga
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 0;--同上
---重啟庫,生效上述引數
shutdown immediate
startup
--memory_max_target的值如何配置呢,oracle提供如下方法:
---查閱為pga分配的最大值
SQL> select * from v$pgastat ps where ps.name='maximum PGA allocated';
NAME VALUE UNIT
---------------------------------------------------------------- ---------- ------------
maximum PGA allocated 91763712 bytes
--memory_target(或memory_max_target)的值:
--如實體記憶體非常充足,可以考慮在其基礎上適當增加大小
memory_target = sga_target + max(pga_aggregate_target, maximum PGA allocated)
----監控oracle記憶體結構
---動態查閱記憶體各元件的當前實時大小
SQL> select * from v$memory_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYPE LAST_OPER_MODE LAST_OPER_TIME GRANULE_SIZE
---------------------------------------------------------------- ------------ ---------- ---------- ------------------- ---------- -------------- -------------- -------------- ------------
shared pool 301989888 301989888 301989888 0 0 STATIC 16777216
large pool 16777216 16777216 16777216 0 0 STATIC 16777216
java pool 16777216 16777216 16777216 0 0 STATIC 16777216
streams pool 0 0 0 0 0 STATIC 16777216
SGA Target 2566914048 2566914048 2566914048 0 0 STATIC 16777216
DEFAULT buffer cache 2197815296 2197815296 2197815296 0 0 INITIALIZING 16777216
KEEP buffer cache 0 0 0 0 0 STATIC 16777216
RECYCLE buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 2K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 4K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 8K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 16K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 32K buffer cache 0 0 0 0 0 STATIC 16777216
Shared IO Pool 0 0 0 0 0 STATIC 16777216
PGA Target 1728053248 1728053248 1728053248 0 0 STATIC 16777216
ASM Buffer Cache 0 0 0 0 0 STATIC 16777216
16 rows selected
SQL>
---調節memory target的診斷工具
---memory_target_factor為1,表示當前memory_target所屬行
--其上和其下為memory_target的記憶體大小
--estd_db_time為不同記憶體大小完成工作所需要的db time
--此例中,增大和減少記憶體大小estd_db_time皆為138,因為當前資料庫未執行什麼任務
SQL> select * from v$memory_target_advice;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ ------------------- ----------
1024 0.25 138 1 0
2048 0.5 138 1 0
2560 0.625 138 1 0
3072 0.75 138 1 0
3584 0.875 138 1 0
4096 1 138 1 0
4608 1.125 138 1 0
5120 1.25 138 1 0
5632 1.375 138 1 0
6144 1.5 138 1 0
6656 1.625 138 1 0
7168 1.75 138 1 0
7680 1.875 138 1 0
8192 2 138 1 0
14 rows selected
SQL> select * from v$memory_dynamic_components;
COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYPE LAST_OPER_MODE LAST_OPER_TIME GRANULE_SIZE
---------------------------------------------------------------- ------------ ---------- ---------- ------------------- ---------- -------------- -------------- -------------- ------------
shared pool 301989888 301989888 301989888 0 0 STATIC 16777216
large pool 16777216 16777216 16777216 0 0 STATIC 16777216
java pool 16777216 16777216 16777216 0 0 STATIC 16777216
streams pool 0 0 0 0 0 STATIC 16777216
SGA Target 2566914048 2566914048 2566914048 0 0 STATIC 16777216
DEFAULT buffer cache 2197815296 2197815296 2197815296 0 0 INITIALIZING 16777216
KEEP buffer cache 0 0 0 0 0 STATIC 16777216
RECYCLE buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 2K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 4K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 8K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 16K buffer cache 0 0 0 0 0 STATIC 16777216
DEFAULT 32K buffer cache 0 0 0 0 0 STATIC 16777216
Shared IO Pool 0 0 0 0 0 STATIC 16777216
PGA Target 1728053248 1728053248 1728053248 0 0 STATIC 16777216
ASM Buffer Cache 0 0 0 0 0 STATIC 16777216
16 rows selected
SQL>
---調節memory target的診斷工具
---memory_target_factor為1,表示當前memory_target所屬行
--其上和其下為
--estd_db_time為不同記憶體大小完成工作所需要的db time
--此例中,增大和減少記憶體大小estd_db_time皆為138,因為當前資料庫未執行什麼任務
SQL> select * from v$memory_target_advice;
MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION
----------- ------------------ ------------ ------------------- ----------
1024 0.25 138 1 0
2048 0.5 138 1 0
2560 0.625 138 1 0
3072 0.75 138 1 0
3584 0.875 138 1 0
4096 1 138 1 0
4608 1.125 138 1 0
5120 1.25 138 1 0
5632 1.375 138 1 0
6144 1.5 138 1 0
6656 1.625 138 1 0
7168 1.75 138 1 0
7680 1.875 138 1 0
8192 2 138 1 0
14 rows selected
---sga各元件資訊,sga分配和釋放以granule單位進行
SQL> select * from v$sgainfo;
NAME BYTES RESIZEABLE
-------------------------------- ---------- ----------
Fixed SGA Size 2182592 No
Redo Buffers 12185600 No
Buffer Cache Size 2197815296 Yes
Shared Pool Size 301989888 Yes
Large Pool Size 16777216 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No ---16mb的粒度
Maximum SGA Size 4275781632 No
Startup overhead in Shared Pool 100663296 No
Free SGA Memory Available 1728053248
12 rows selected
SQL> select * from v$sgainfo;
NAME BYTES RESIZEABLE
-------------------------------- ---------- ----------
Fixed SGA Size 2182592 No
Redo Buffers 12185600 No
Buffer Cache Size 2197815296 Yes
Shared Pool Size 301989888 Yes
Large Pool Size 16777216 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No ---16mb的粒度
Maximum SGA Size 4275781632 No
Startup overhead in Shared Pool 100663296 No
Free SGA Memory Available 1728053248
12 rows selected
---如下測試說明sga_target>=1g,Granule為16mb
SQL> show parameter sga_target
SQL> show parameter sga_target
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
sga_target big integer
1G
SQL> select * from v$sgainfo;
------------------------------------ ----------------------
VALUE
------------------------------
sga_target big integer
1G
SQL> select * from v$sgainfo;
NAME BYTES RESIZE
---------------------------------------- ---------- ------
Fixed SGA Size 2182592 No
Redo Buffers 12185600 No
Buffer Cache Size 738197504 Yes
Shared Pool Size 268435456 Yes
Large Pool Size 16777216 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 4275781632 No
Startup overhead in Shared Pool 100663296 No
Free SGA Memory Available 3221225472
---------------------------------------- ---------- ------
Fixed SGA Size 2182592 No
Redo Buffers 12185600 No
Buffer Cache Size 738197504 Yes
Shared Pool Size 268435456 Yes
Large Pool Size 16777216 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 4275781632 No
Startup overhead in Shared Pool 100663296 No
Free SGA Memory Available 3221225472
12 rows selected.
SQL> show parameter memory
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
hi_shared_memory_address integer
0
memory_max_target big integer
4G
memory_target big integer
0
shared_memory_address integer
0
SQL> alter system set sga_target=2g scope=spfile;
------------------------------------ ----------------------
VALUE
------------------------------
hi_shared_memory_address integer
0
memory_max_target big integer
4G
memory_target big integer
0
shared_memory_address integer
0
SQL> alter system set sga_target=2g scope=spfile;
System altered.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 4275781632 bytes
Fixed Size 2182592 bytes
Variable Size 2449474112 bytes
Database Buffers 1811939328 bytes
Redo Buffers 12185600 bytes
Database mounted.
Database opened.
SQL> select * from v$sgainfo;
Fixed Size 2182592 bytes
Variable Size 2449474112 bytes
Database Buffers 1811939328 bytes
Redo Buffers 12185600 bytes
Database mounted.
Database opened.
SQL> select * from v$sgainfo;
NAME BYTES RESIZE
---------------------------------------- ---------- ------
Fixed SGA Size 2182592 No
Redo Buffers 12185600 No
Buffer Cache Size 1811939328 Yes
Shared Pool Size 268435456 Yes
Large Pool Size 16777216 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 4275781632 No
Startup overhead in Shared Pool 117440512 No
Free SGA Memory Available 2147483648
---------------------------------------- ---------- ------
Fixed SGA Size 2182592 No
Redo Buffers 12185600 No
Buffer Cache Size 1811939328 Yes
Shared Pool Size 268435456 Yes
Large Pool Size 16777216 Yes
Java Pool Size 16777216 Yes
Streams Pool Size 0 Yes
Shared IO Pool Size 0 Yes
Granule Size 16777216 No
Maximum SGA Size 4275781632 No
Startup overhead in Shared Pool 117440512 No
Free SGA Memory Available 2147483648
12 rows selected.
---換算粒度為mb
SQL> select 16777216/1024/1024 mb from dual;
SQL> select 16777216/1024/1024 mb from dual;
MB
----------
16
----------
16
---如下檢視可查oracle引數可配置的值
SQL> select num,name,ordinal,value,isdefault from v$parameter_valid_values where
name='statistics_level';
SQL> select num,name,ordinal,value,isdefault from v$parameter_valid_values where
name='statistics_level';
NUM NAME ORDINAL
---------- ---------------------------------------- ----------
VALUE
--------------------------------------------------------------------------------
---------- ---------------------------------------- ----------
VALUE
--------------------------------------------------------------------------------
ISDEFAULT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
1826 statistics_level 1
BASIC
FALSE
BASIC
FALSE
1826 statistics_level 2
TYPICAL
TRUE
TYPICAL
TRUE
1826 statistics_level 3
ALL
FALSE
ALL
FALSE
SQL> alter system set statistics_level=basic scope=spfile;
System altered.
SQL> show parameter sga_
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
sga_max_size big integer
2G
sga_target big integer
2G
SQL> show parameter statis
------------------------------------ ----------------------
VALUE
------------------------------
sga_max_size big integer
2G
sga_target big integer
2G
SQL> show parameter statis
NAME TYPE
------------------------------------ ----------------------
VALUE
------------------------------
optimizer_use_pending_statistics boolean
FALSE
statistics_level string
TYPICAL
timed_os_statistics integer
0
timed_statistics boolean
TRUE
SQL> alter system set statistics_level=basic scope=spfile;
------------------------------------ ----------------------
VALUE
------------------------------
optimizer_use_pending_statistics boolean
FALSE
statistics_level string
TYPICAL
timed_os_statistics integer
0
timed_statistics boolean
TRUE
SQL> alter system set statistics_level=basic scope=spfile;
System altered
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-753708/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Memory Management in RustRust
- Oracle Memory Management and HugePage （連載一）Oracle
- memory management unit (MMU)
- Linux Memory ManagementLinux
- Oracle11g中SQL Plan Management （一）OracleSQL
- Oracle Shared Pool Memory ManagementOracle
- Oracle Automatic PGA Memory ManagementOracle
- Oracle Memory Management and HugePage （連載二）Oracle
- Oracle Memory Management and HugePage （連載三）Oracle
- Oracle Memory Structure 1. Overview And ManagementOracleStructView
- Linux Memory Management or 'Why is there no free RAM?' (zt)Linux
- PGA Memory Management for Dedicated Mode (102)
- 12. 記憶體管理(Memory Management)記憶體
- Oracle11g中SQL Plan Management （二）OracleSQL
- Oracle11g中SQL Plan Management （三）OracleSQL
- Oracle11g修改MEMORY_TARGETOracle
- Linux memory management——(程式虛存空間的管理)(轉)Linux
- Oracle OCP 1Z0 Q418(Memory Management Manual)Oracle
- Oracle OCP 1Z0 053 Q413(Automatic Memory Management)Oracle
- SQL Server 2012 記憶體管理 (memory management) 改進SQLServer記憶體
- [筆記]Oracle9i Monitoring Automated SQL Execution Memory Management筆記OracleSQL
- Oracle OCP 1Z0 053 Q283(Automatic Memory Management)Oracle
- Oracle11g ORA-00845: MEMORY_TARGET not supported on this systemOracle
- 關於oracle11G的自動記憶體管理MEMORY_TARGET和MEMORY_MAX_TARGETOracle記憶體
- Oracle10g New Feature -- 13. Automatic Shared Memory ManagementOracle
- Oracle OCP 1Z0 053 Q66(Automatic Shared Memory Management)Oracle
- Oracle OCP 1Z0 053 Q420(Automatic PGA Memory Management)Oracle
- win10電腦藍屏終止程式碼memory management的解決方法Win10
- Oracle11g啟動報：ORA-00845: MEMORY_TARGET not supported on this systemOracle
- Win10系統經常藍色畫面提示memory management的解決方法Win10
- 如何啟用oracle11g的全自動記憶體管理以及計算memory_max_target及memory_targetOracle記憶體
- 如何基於pfile啟動oracle11g全自動記憶體管理與memory_max_target及memory_targetOracle記憶體
- oracle11g中SQL最佳化（SQL TUNING）新特性之SQL Plan Management(SPM)OracleSQL
- win10電腦藍色畫面終止程式碼memory management的解決方法Win10
- oracle11g表的高水位線hwm與dbms_space系列一Oracle
- 死磕 java同步系列之JMM（Java Memory Model）Java
- 密碼學系列之:memory-hard函式密碼學函式
- 密碼學系列之:memory-bound函式密碼學函式