对于SGA,可以用sqlplus查询:
SQL>select * from v$sga;
Fixed Size
Variable Size
包含了shared_pool_size、java_pool_size、large_pool_size等内存设置和管理内存缓冲区等内存结构的hash table,块头信息等
DataBase Buffers
指数据缓冲区,在8i中有default pool 、default_pool_keep、defaule_pool _recyle三部分内存,在9i中有db_cache_size,db_keep_cache_size,db_reycle_cache_size, db_cn_cache_size等几部分内存, Oracle8i三部分内存的总和是:db_block_buffer* db_ block_size .
Redo buffers
指日志缓冲区,log_buffer,额外一点:对于v$parameter,v$sgastat,v$sga查询值可能不一样。V$parameter是用户在初始化的时候设置的值,v$sgastat是实际分配缓冲区的大小(因为实际分配的缓冲区是离散的,也不是以block为单位的),v$sga是在分配了日志缓冲区以后,为了保护日之缓冲区,设置了一些保护页,通常我们发现保护页的大小大约为11k左右。
SQL>select substr(name,1,10) name,substr(value,1,10) value from v$parameter where name=’log_buffer’;
SQL>select * from v$sgastat;
SQL>select * from v$sga;
8iSGA
ORACLE 8.0.X 版本
SGA=((db_block_buffers*blocksize)+(shared_pool_size+large_pool_size+ log_buffers) +1MB
ORACLE 8.1.X 版本
SGA=(db_block_buffers*blocksize)+(share_pool_size+large_pool_size+java_pool_size+log_buffers)+1MB
理论上SGA可占用os系统物理内存的1/2——1/3,
推荐:SGA=0.45*(os RAM)
关于数据库有多少个并发连接,这实际上关系到PGA的大小(MTS下还有large_pool_size).事实上这个问题应该说还跟OLTP类型或者OLAP类型相关,对于OLTP类型oracle倾向于可使用的MTS,对于OLAP类型使用独立模式,同时OLAP还可能涉及到大量的排序操作的查询,这些都影响到我们内存的使用,那么所有的问题综合起来,实际上主要反映在UGA的大小上,UGA主要包含以下部分内存设置
SQL>show parameters area_size
NAME TYPE VALUE
------------------------------------ ---------------------------
bitmap_merge_area_size integer 1048576
create_bitmap_area_size integer 388608
hash_area_size integer 1048576
sort_area_size integer 524288
workarea_size_policy string AUTO
通常我们习惯通过直观的公式来表达内存分配的问题:
Os使用内存+sga+并发执行进程数*(sort_area_size+2M)<0.7*总内存
(公式是死的,系统是活得,实际应用的调整不必框公式,这不过是一个参考建议)
在我们实际应用中,假如采用的是裸设备,我们可适当的增大SGA(如果需要的话)。由于目前几乎所有的操作系统都使用虚拟缓存,所以实际上如果就算SGA设置的比较大也不会导致错误,而是可能出现频繁的内存页的换入换出(page in/out)。在操作系统一级如果观察这个现象,那么我们就需要调整内存的设置。
SGA内存参数设置:
Log_buffer
对于日志缓冲区的大小设置,通常我觉得没有过多的建议,因为参考LGWR写的触发条件之后,我们会发现通常超过3M意义不是很大,作为一个正式系统,可能考虑先设置这部分log_buffer=1—3M大小,然后对具体情况在调整
Large_pool_size
对于大缓冲池的设置,假如不使用MTS,加以在20--30M足够了,这部分主要用来保存并行查询时候的一些信息,还有就是RMAN在备份的时候可能使用到,如果设置了MTS,则由于UGA部分要移入到这里,则需要具体根据server process数量和相关会话内存参数的设置来综合考虑这部分大小的设置。
Java_pool_size
假如数据库没有使用java,我们通常认为保留10M--20M大小足够,甚至更小到32K,但是具体安装数据库的时候的组件相关。
Shared_pool_size
这是迄今为止最有争议的一部分内存设置,这部分是为了缓存已经被解析过的SQL,而是其能被重用,不再解析,share_pool里面不存在已经解析的可用的相同的SQL,对于一个新的,数据库将执行硬解析,这过程很耗资源,若已经存在,则进行的仅仅是软分析(在共享池中寻找相同的SQL),这样消耗的资源大大的减少,所以期望共享一些SQL,并且如果参数设置不大,经常出现ora-04031错误,表示为了解析新的sql,没有足够大的连续空闲空间,这样自然我们期望参数能大一些,但是太大,由于需要维护共享的结构,内存的增大也使得sql的老化代价更高,带来大量的管理的开销,可能导致cpu的严重问题。
一般,在一个较大的系统中,share_pool_size的开销通常应该维持在300M,除非使用大量的存储过程、函数、包、比如oracle erp 这样的应用,可能会达到500M甚至更高。
Data buffer
在确定了SGA的大小并分配玩了前面部分内存后,其余的都可以分配给这部分内存,通常,在允许的情况下,我们都尝试这是这部分内存更大,这部分内存的作用主要是缓存DB BLOCK,减少甚至避免从磁盘上获取数据,在8i中通常是由db_blocke_buffers* db_block_size来决定大小的,如果设置了buffer_pool_keep和buffer_pool_reycle,则应该加上后边这两部分内存的大小。
9i下部分参数变化
8i 9i
Buffer_pool_keep-----------------------------------------àdb_keep_cache_size
Buffer_pool_recycle--------------------------------------àdb_recycle_cache_size
Db_block_buffers-----------------------------------------àdb_cache_size
查看SGA_MAX_SIZE
SQL>show parameters sga_max_size
SQL>alter system set db_cache_size=128000000
SQL>alter system set shared_pool_size=300000000
关于内存的参数调整:
缓冲区命中率:
SQL>select value from v$sysstat where name=’physical reads’;
SQL>select value from v$sysstat where name=’physical reads direct’;
SQL>select value from v$sysstat where name=’physical reads direct (lob)’;
SQL>select value from v$sysstat where name=’consistent gets’;
SQL>select value from v$sysstat where name=’db block gets’;
命中率的计算应该是
X=physical reads direct + physical reads direct (lob)
命中率=100-(physical reads –x)/(consistent gets + db block gets –x)*100
共享池的命中率
SQL>select sum(pinhits)/sum(pins)*100 “hit radio” from v$librarycache;
关于排序部分
SQL>select name,value from v$sysstat where name like ‘%sort%’;
关于log_buffer
SQL>select name,value from v$sysstat where name in(‘redo entries’,’redo buffer allocation retries’);
从11G开始推介使用内存自动管理,不再具体作内存具体分配。