ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 10g、11g内存自动管理

10g、11g内存自动管理

原创 Linux操作系统 作者:oracle_mao 时间:2011-12-14 15:08:45 0 删除 编辑

SGA自动管理:
1、如何查看SGA是否是自动管理?
在10G中,通过设置sga_target为零和非零值,可以完成手工和自动管理切换,如果sga_target非0,那么就是自动管理。
而在11G 中,查看sga是否是自动管理,可和sga_target没有关系了,因为11g新特性中出现了memory_target参数,memory_target结合和sga和pga的管理(Oracle9i引入pga_aggregate_target,可以自动对PGA进行调整;
Oracle10引入sga_target,可以自动对SGA进行调整。Oracle11g则对这两部分进行综合,引入memory_target,可以自动调整所有的内存)
2、10g查看sga自动管理
C:\Users\xiaomao>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on 星期三 12月 14 10:11:40 2
Copyright (c) 1982, 2005, Oracle.  All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> show parameter sga
NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
lock_sga                             boolean
FALSE
pre_page_sga                         boolean
FALSE
sga_max_size                         big integer
432M
sga_target                           big integer
432M
解析:这里sga_target是一个非0的值,因为这里看sga是自动管理的,而sga_target的值是小于等于sga_max_size的值得,如果我们手动将sga_target的值改大,是不可以的。
SQL> alter system set sga_target=440M;
alter system set sga_target=440M
*
第 1 行出现错误:
ORA-02097: 无法修改参数, 因为指定的值无效
ORA-00823: 指定的 sga_target 的值大于 sga_max_size

SQL> alter system set sga_target=440M scope=spfile;
系统已更改。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。
Total System Global Area  461373440 bytes
Fixed Size                  1248512 bytes
Variable Size             134218496 bytes
Database Buffers          318767104 bytes
Redo Buffers                7139328 bytes
数据库装载完毕。
数据库已经打开。
SQL> show parameter sga
NAME                                 TYPE
------------------------------------ ----------------------
VALUE
------------------------------
lock_sga                             boolean
FALSE
pre_page_sga                         boolean
FALSE
sga_max_size                         big integer
440M
sga_target                           big integer
440M
官方文档解释是说:
It is significant that SGA_TARGET includes the entire memory for the SGA, in contrast to earlier releases, in which memory for the internal and fixed SGA was added to the sum of the configured SGA memory parameters. Thus, SGA_TARGET gives you precise control over the size of the shared memory region allocated by the database. If SGA_TARGET is set to a value greater than SGA_MAX_SIZE at startup, then the latter is bumped up to accomodate SGA_TARGET. After startup, SGA_TARGET can be decreased or increased dynamically. However, it can not exceed the value of SGA_MAX_SIZE that was computed at startup
大概意思是说如果设置sga_target的值大于sga_max_size,在重启db时,sga_max_size的值就会被覆盖。

3、10g中,sga_target参数的官方解释

SGA_TARGETProperty Description
Parameter type Big integer
Syntax SGA_TARGET = integer [K | M | G]
Default value 0 (SGA autotuning is disabled)
Modifiable ALTER SYSTEM
Range of values 64 to operating system-dependent
Basic Yes
SGA_TARGET specifies the total size of all SGA components. If SGA_TARGET is specified, then the following memory pools are automatically sized:
•Buffer cache (DB_CACHE_SIZE)
•Shared pool (SHARED_POOL_SIZE)
•Large pool (LARGE_POOL_SIZE)
•Java pool (JAVA_POOL_SIZE)
•Streams pool (STREAMS_POOL_SIZE)
解析:这里说如果sga_target的值被指定,那么这些pool_size就被动态管理

SQL> select name,value from v$parameter where name like '%pool_size%' or name like 'db_cache_size' ;
 
NAME                 VALUE
-------------------- ----------
shared_pool_size     0
large_pool_size      0
java_pool_size       0
streams_pool_size    0
db_cache_size        0
然后我们现在如何更快的查看此时这些值的大小呢?那就要通过查看一些隐含参数了
SQL> select a.ksppinm name,b.ksppstvl value
  2  from x$ksppi a,x$ksppcv b
  3  where a.indx=b.indx
  4  and (a.ksppinm like '%pool_size%'
  5  or a.ksppinm like '%target%' or a.ksppinm like '%db_cache_size%')
  6  ;
 
NAME                                     VALUE
---------------------------------------- --------------------------------------------------
_NUMA_pool_size                          Not specified
__shared_pool_size                       130023424
shared_pool_size                         0
__large_pool_size                        4194304                 --这里就是large_pool_size的大小
large_pool_size                          0
__java_pool_size                         4194304              
java_pool_size                           0
__streams_pool_size                      0
streams_pool_size                        0
_io_shared_pool_size                     4194304
_backup_io_pool_size                     1048576
sga_target                               469762048
__db_cache_size                          322961408
db_cache_size                            0
_db_mttr_sim_target                     
archive_lag_target                       0
_fast_start_instance_recovery_target     0
_target_rba_max_lag_percentage           90
fast_start_io_target                     0
fast_start_mttr_target                   0
 
NAME                                     VALUE
---------------------------------------- --------------------------------------------------
db_flashback_retention_target            1440
global_context_pool_size                
pga_aggregate_target                     149946368
olap_page_pool_size                      0
_olap_page_pool_hit_target               100
 
25 rows selected
4、11g中查看sga是否自动管理
主要是查看memory_target这个参数。
SQL> show parameter mem

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
hi_shared_memory_address             integer     0
memory_max_target                    big integer 6464M
memory_target                        big integer 6464M
shared_memory_address                integer     0
而如果我们把memory_target的值擅自手动改大些,也会出现10g里sga_target的问题

5、11g中查看sga和pga大小
SQL> show parameter sga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
lock_sga                             boolean     FALSE
pre_page_sga                         boolean     FALSE
sga_max_size                         big integer 2336M
sga_target                           big integer 0
SQL> show parameter pga
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
pga_aggregate_target                 big integer 0
由于sga和pga都是动态管理了,所以要看大小的话,还是需要看隐含参数的
SQL> select a.ksppinm name,b.ksppstvl value
  2  from x$ksppi a,x$ksppcv b
  3  where a.indx=b.indx
  4  and (a.ksppinm like '%sga_target%'
  5  or a.ksppinm like '%pga%target%');

NAME                                                                             VALUE
-------------------------------------------------------------------------------- --------------------------------------------------
sga_target                                                                       0
__sga_target                                                                     2449473536
pga_aggregate_target                                                             0
__pga_aggregate_target                                                           4328521728

以上为我的简单理解~~如果误理解,请指正

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/24500180/viewspace-713299/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-28

  • 博文量
    94
  • 访问量
    746792