[bbk5148] 第51集 - 第五章 管理内存 06
查看Oracle Database系统内存参数情况(as sysdba)
其他参数情况查看(as sysdba)
SQL> show parameter shared_pool_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ shared_pool_size big integer 0 SQL> show parameter db_cache_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_cache_size big integer 0 SQL> show parameter java_pool_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ java_pool_size big integer 0 SQL> show parameter large_pool_size NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ large_pool_size big integer 0
查看当前数据库启动后，各个组件的内存分配使用情况
SQL> select * from v$memory_dynamic_components; COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER_TIME GRANULE_SIZE ------------------------------ ------------ ---------- ---------- ------------------- ---------- ------------- --------- ------------------ ------------ shared pool 553648128 553648128 553648128 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 1962934272 1962934272 1962934272 0 0 STATIC 16777216 DEFAULT buffer cache 1342177280 1342177280 1342177280 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 COMPONENT CURRENT_SIZE MIN_SIZE MAX_SIZE USER_SPECIFIED_SIZE OPER_COUNT LAST_OPER_TYP LAST_OPER LAST_OPER_TIME GRANULE_SIZE ------------------------------ ------------ ---------- ---------- ------------------- ---------- ------------- --------- ------------------ ------------ 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 1308622848 1308622848 1308622848 0 0 STATIC 16777216 ASM Buffer Cache 0 0 0 0 0 STATIC 16777216 16 rows selected.
查看动态性能视图v$memory_resize_ops数据情况
SQL> select * from v$memory_resize_ops; COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME -------------------------- ------------- --------- ---------------------- ------------ ----------- ---------- --------- ------------------ ------------------ shared pool STATIC shared_pool_size 0 553648128 553648128 COMPLETE 26-MAY-13 26-MAY-13 PGA Target STATIC pga_aggregate_target 0 1308622848 1308622848 COMPLETE 26-MAY-13 26-MAY-13 java pool STATIC java_pool_size 0 16777216 16777216 COMPLETE 26-MAY-13 26-MAY-13 streams pool STATIC streams_pool_size 0 0 0 COMPLETE 26-MAY-13 26-MAY-13 SGA Target STATIC sga_target 0 1962934272 1962934272 COMPLETE 26-MAY-13 26-MAY-13 DEFAULT buffer cache INITIALIZING db_cache_size 1342177280 1342177280 1342177280 COMPLETE 26-MAY-13 26-MAY-13 ASM Buffer Cache STATIC db_cache_size 0 0 0 COMPLETE 26-MAY-13 26-MAY-13 DEFAULT buffer cache STATIC db_cache_size 0 1342177280 1342177280 COMPLETE 26-MAY-13 26-MAY-13 DEFAULT 2K buffer cache STATIC db_2k_cache_size 0 0 0 COMPLETE 26-MAY-13 26-MAY-13 DEFAULT 4K buffer cache STATIC db_4k_cache_size 0 0 0 COMPLETE 26-MAY-13 26-MAY-13 DEFAULT 8K buffer cache STATIC db_8k_cache_size 0 0 0 COMPLETE 26-MAY-13 26-MAY-13 COMPONENT OPER_TYPE OPER_MODE PARAMETER INITIAL_SIZE TARGET_SIZE FINAL_SIZE STATUS START_TIME END_TIME -------------------------- ------------- --------- ---------------------- ------------ ----------- ---------- --------- ------------------ ------------------ DEFAULT 16K buffer cache STATIC db_16k_cache_size 0 0 0 COMPLETE 26-MAY-13 26-MAY-13 DEFAULT 32K buffer cache STATIC db_32k_cache_size 0 0 0 COMPLETE 26-MAY-13 26-MAY-13 KEEP buffer cache STATIC db_keep_cache_size 0 0 0 COMPLETE 26-MAY-13 26-MAY-13 RECYCLE buffer cache STATIC db_recycle_cache_size 0 0 0 COMPLETE 26-MAY-13 26-MAY-13 large pool STATIC large_pool_size 0 16777216 16777216 COMPLETE 26-MAY-13 26-MAY-13 16 rows selected.
Oracle提供的内存分配建议情况
SQL> select * from v$memory_target_advice; MEMORY_SIZE MEMORY_SIZE_FACTOR ESTD_DB_TIME ESTD_DB_TIME_FACTOR VERSION ----------- ------------------ ------------ ------------------- ---------- 780 .25 231 1.0098 0 1560 .5 229 1 0 2340 .75 229 1 0 3120 1 229 1 0 3900 1.25 229 1 0 4680 1.5 229 1 0 5460 1.75 229 1 0 6240 2 229 1 0 8 rows selected.
图解memory_target参数与SGA_TARGET、PGA_TARGET之间的关系.
