Chinaunix首页 | 论坛 | 博客
  • 博客访问: 236055
  • 博文数量: 39
  • 博客积分: 2108
  • 博客等级: 大尉
  • 技术积分: 432
  • 用 户 组: 普通用户
  • 注册时间: 2009-12-03 16:02
文章存档

2010年(38)

2009年(1)

我的朋友

分类: Oracle

2010-04-02 00:14:39

自动SGA管理下如何调整和查看SGA各组件大小

一直不太明白,SGA动态自动调整下如何修改SGA各组件的设置。
今天终于大概明白了!

修改:可以通过alter system直接修改,但只能修改v$sgainfo表中RESIZEABLE列为Yes的组件。
查看:可以通过show parameter来查看,也可以通过隐含参数来查看其中的变化。

但还是有点不明白具体的设置,如下面的测试。
作个测试,通过以下几种方式查看变化:

1,show sga
2,show parameter
3,select * from v$sgainfo;
4,通过eygle的脚本查看隐藏参数
set linesize 100
col name for a25
col value for a15
col describ for a40
select x.ksppinm name,y.ksppstvl value,x.ksppdesc describ
from sys.x$ksppi x,sys.x$ksppcv y
where x.inst_id=userenv('instance')
  and y.inst_id=userenv('instance')
  and x.indx=y.indx
  and x.ksppinm like '%&par%'
/

==============以下为测试内容==============

--
SQL> show sga

Total System Global Area 268435456 bytes
Fixed Size 1248476 bytes
Variable Size 117441316 bytes
Database Buffers 146800640 bytes
Redo Buffers 2945024 bytes
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 5M
shared_pool_size big integer 64M
SQL> show parameter cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 4M
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 8M
db_8k_cache_size big integer 0
db_cache_size big integer 52M
db_keep_cache_size big integer 8M
db_recycle_cache_size big integer 12M
SQL> select * from v$sgainfo;

NAME BYTES RES
------------------------------ ---------- ---
Fixed SGA Size 1248476 No
Redo Buffers 2945024 No
Buffer Cache Size 146800640 Yes
Shared Pool Size 104857600 Yes
Large Pool Size 8388608 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 268435456 No
Startup overhead in Shared Poo 37748736 No
l

NAME BYTES RES
------------------------------ ---------- ---
                                                                                                                        
Free SGA Memory Available 0

已选择11行。

SQL> @c:\getp.sql
输入 par 的值: shared_pool
原值 6: and x.ksppinm like '%&par%'
新值 6: and x.ksppinm like '%shared_pool%'

NAME VALUE DESCRIB
------------------------- --------------- ----------------------------------------
__shared_pool_size 104857600 Actual size in bytes of shared pool
shared_pool_size 67108864 size in bytes of shared pool
shared_pool_reserved_size 5242880 size in bytes of reserved area of shared
                                           pool
                                                                                                    
_shared_pool_reserved_pct 5 percentage memory of the shared pool all
                                          ocated for the reserved area
                                                                                                    
_shared_pool_reserved_min 4400 minimum allocation size in bytes for res
_alloc erved area of shared pool
                                                                                                    
_io_shared_pool_size 4194304 Size of I/O buffer pool from SGA
_shared_pool_max_size 0 shared pool maximum size when auto SGA e
                                          nabled
                                                                                                    
_dm_max_shared_pool_pct 1 max percentage of the shared pool to use
                                           for a mining model
                                                                                                    

已选择8行。

SQL> @c:\getp.sql
输入 par 的值: db_cache_size
原值 6: and x.ksppinm like '%&par%'
新值 6: and x.ksppinm like '%db_cache_size%'

NAME VALUE DESCRIB
------------------------- --------------- ----------------------------------------
__db_cache_size 113246208 Actual size of DEFAULT buffer pool for s
                                          tandard block size buffers
                                                                                                    
db_cache_size 54525952 Size of DEFAULT buffer pool for standard
                                           block size buffers
                                                                                                    

SQL> host echo ==============This is the value of start=============

SQL> alter system set shared_pool_size=56M;

系统已更改。

SQL> alter system set db_cache_size=36M;

系统已更改。

SQL> host echo =================check cache change===============

SQL> show sga

Total System Global Area 268435456 bytes
Fixed Size 1248476 bytes
Variable Size 117441316 bytes
Database Buffers 146800640 bytes
Redo Buffers 2945024 bytes
                                            
SQL> show parameter shared_pool

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_reserved_size big integer 5M
shared_pool_size big integer 56M

SQL> show parameter cache_size

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size big integer 4M
db_2k_cache_size big integer 0
db_32k_cache_size big integer 0
db_4k_cache_size big integer 8M
db_8k_cache_size big integer 0
db_cache_size big integer 36M
db_keep_cache_size big integer 8M
db_recycle_cache_size big integer 12M
SQL>
                                            
SQL> select * from v$sgainfo;

NAME BYTES RES
------------------------- ---------- ---
Fixed SGA Size 1248476 No
Redo Buffers 2945024 No
Buffer Cache Size 146800640 Yes
Shared Pool Size 104857600 Yes
Large Pool Size 8388608 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 268435456 No
Startup overhead in Share 37748736 No
d Pool
                                                                                                    
Free SGA Memory Available 0

已选择11行。

SQL> @c:\getp.sql
输入 par 的值: shared_pool
原值 6: and x.ksppinm like '%&par%'
新值 6: and x.ksppinm like '%shared_pool%'

NAME VALUE DESCRIB
------------------------- --------------- ----------------------------------------
__shared_pool_size 104857600 Actual size in bytes of shared pool
shared_pool_size 58720256 size in bytes of shared pool
shared_pool_reserved_size 5242880 size in bytes of reserved area of shared
                                           pool
                                                                                                    
_shared_pool_reserved_pct 5 percentage memory of the shared pool all
                                          ocated for the reserved area
                                                                                                    
_shared_pool_reserved_min 4400 minimum allocation size in bytes for res
_alloc erved area of shared pool
                                                                                                    
_io_shared_pool_size 4194304 Size of I/O buffer pool from SGA
_shared_pool_max_size 0 shared pool maximum size when auto SGA e
                                          nabled
                                                                                                    
_dm_max_shared_pool_pct 1 max percentage of the shared pool to use
                                           for a mining model
                                                                                                    

已选择8行。

SQL> @c:\getp.sql
输入 par 的值: db_cache_size
原值 6: and x.ksppinm like '%&par%'
新值 6: and x.ksppinm like '%db_cache_size%'

NAME VALUE DESCRIB
------------------------- --------------- ----------------------------------------
__db_cache_size 113246208 Actual size of DEFAULT buffer pool for s
                                          tandard block size buffers
                                                                                                    
db_cache_size 37748736 Size of DEFAULT buffer pool for standard
                                           block size buffers


==============以上为测试内容==============

总结:
               shared_pool_size   db_cache_size
v$parameter    64M                52M
v$sgainfo      100M=104857600     140M=146800640
隐藏参数       64M=67108864       52M=54525952

修改----       56M                36M

v$parameter    56M                36M
v$sgainfo      100M=104857600     140M=146800640
隐藏参数       56M=58720256       36M=37748736

其中,对v$sgainfo中的数据有一点不太理解:
1,
--v$sgainfo表中:
Buffer Cache Size          146800640 Yes   =140M
--v$parameter表中:
db_16k_cache_size                    big integer 4M
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 8M
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 36M
db_keep_cache_size                   big integer 8M
db_recycle_cache_size                big integer 12M

一共只有4+8+36+8+12=68M
不知道V$sgainfo中的140-68=72M是用在了哪里!

2,
--v$sgainfo表中:
Shared Pool Size           104857600 Yes   =100M
--v$parameter表中:
shared_pool_size            big integer 56M

其余的46M呢?

--隐藏参数表中:
__shared_pool_size        104857600       Actual size in bytes of shared pool         =100M
                    
shared_pool_size          58720256        size in bytes of shared pool                =56M
shared_pool_reserved_size 5242880         size in bytes of reserved area of shared    =5M
                                          pool
                                                                                                    
_shared_pool_reserved_pct 5               percentage memory of the shared pool all                  
                                          ocated for the reserved area                              
                                                                                                    
_shared_pool_reserved_min 4400            minimum allocation size in bytes for res                  
_alloc                                    erved area of shared pool                                 
                                                                                                    
_io_shared_pool_size      4194304         Size of I/O buffer pool from SGA            =4M

还是有一些内存不知道哪里去了……

不知道这样比较是不是有意义……

转帖请注明:
本文源自TTT BLOG,原文地址:http://blog.chinaunix.net/u3/107265/showart_2201600.html
阅读(3532) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~