oracle 内存调整,Oracle 内存调整-CSDN博客

名词解释 SGA： System Global Area是Oracle Instance的基本组成部分，在实例启动时分配；系统全局域SGA主要由三部分构成：共享池、数据缓冲区、日志缓冲区。共享池：Shared Pool用于缓存最近被执行的SQL语句和最近被使用的数据定义，主要包括：Library cache(共享SQL区)和Data dictionary cache(数据字典缓冲区) 共享SQL区是存放用户SQL命令的区域，数据字典缓冲区存放数据库运行的动态信息缓冲区高速缓存：Database Buffer Cache用于缓存从数据文件中检索出来的数据块，可以大大提高查询和更新数据的性能大型池：Large Pool是SGA中一个可选的内存区域，它只用于shared server环境 Java池：java Pool为Java命令的语法分析提供服务 PGA：Program Global Area是为每个连接到Oracle database的用户进程保留的内存。查询SGA和PGA 获得sga_max_size和sga_target的值 SQL> show parameter sga; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ lock_sga boolean FALSE pre_page_sga boolean FALSE sga_max_size big integer 580M sga_target big integer 580M 获得pga_aggregate_target的值 SQL> show parameter pga; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ pga_aggregate_target big integer 192M SGA空闲空间 SQL> select pool,name, round(sgasize/1024/1024,2) "Allocated(M)", 　　　 round(bytes/1024,2) "自由空间(K)", round(bytes/sgasize*100, 2) "自由空间百分比(%)" from (select sum(bytes) sgasize from sys.v_$sgastat) s, sys.v_$sgastat f where f.name = 'free memory'; POOL NAME Allocated(M) 自由空间(K) 自由空间百分比(%) ------------ -------------------------- ------------ --------------- ------------------------ shared pool free memory 578.35 426.5 0.07 large pool free memory 578.35 3214.98 0.54 java pool free memory 578.35 4096 0.69 查看 Oracle SGA 命中率数据缓冲区高速缓存 SELECT physical_reads, db_block_gets, consistent_gets, NAME, 100 * ( 1 - ( physical_reads / (consistent_gets + db_block_gets - physical_reads))) "Data Buffer Hit Ratio" FROM v$buffer_pool_statistics; PHYSICAL_READS DB_BLOCK_GETS CONSISTENT_GETS NAME Data Buffer Hit Ratio -------------- ------------- --------------- -------------------- --------------------- 3673479 173945154 1543853168 DEFAULT 99.7856936141494 重做日志缓冲区 SELECT a.VALUE redo_entries, b.VALUE redo_buffer_allocation_retries, ROUND ((1 - b.VALUE / a.VALUE) * 100, 4) log_buffer_ratio FROM v$sysstat a, v$sysstat b WHERE a.NAME = 'redo entries' AND b.NAME = 'redo buffer allocation retries'; REDO_ENTRIES REDO_BUFFER_ALLOCATION_RETRIES LOG_BUFFER_RATIO ------------ ------------------------------ ---------------- 88910432 5581 99.9937 共享池缓冲区命中率建议大于90% SELECT SUM (pinhits) / SUM (pins) * 100 "hit radio" FROM v$librarycache; hit radio ---------- 96.7194470 数据字典高速缓存命中率建议大于90% SELECT TO_CHAR (ROUND ((1 - SUM (getmisses) / SUM (gets)) * 100, 1)) || '%' "Dictionary Cache Hit Ratio" FROM v$rowcache; Dictionary Cache Hit Ratio ----------------------------------------- 99.5% 排序 SELECT a.VALUE disk_sort, b.VALUE memory_sort, ROUND ((1 - a.VALUE / (a.VALUE + b.VALUE)) * 100, 4) sort_ratio FROM v$sysstat a, v$sysstat b WHERE a.NAME = 'sorts (disk)' AND b.NAME = 'sorts (memory)'; DISK_SORT MEMORY_SORT SORT_RATIO ---------- ----------- ---------- 0 6034093 100 修改参考值对于32 BIT系统，有SGA 1.7G限制现场服务器是8G内存，SGA设置为4G，PGA设置为1G 现场服务器是16G内存，SGA设置为10G，PGA设置为2G 现场服务器是32G内存，SGA设置为20G，PGA设置为4G 修改方法 1、更改之前，需要备份，修改之后，很容易启动不了数据库。 pfile sga_max_size=4G sga_target=4G pga_aggregate_target=1G spfile SQL> alter system set sga_max_size=4G scope=spfile; SQL> alter system set sga_target=4G scope=spfile; SQL> alter system set pga_aggregate_target=1G scope=spfile;