Oracle OCP笔记(22)性能管理 - 内存管理
一.决定数据库性能的因数
1.内存使用
Oracle实例的内存使用对性能起着至关重要的作用,如果未按最佳方式为各种结构分配内存量,性能将急转直下。
分配的内存量不能过少,也不能过多。
2.数据库对象的状态
如果无法使用索引,SQL语句的执行速度可能大大降低,并更密集地使用资源。
如果PL/SQL对象是无效的,则会对性能产生负面影响,也可能导致错误。应该识别、了解和修复所有不可用的对象和无效对象。
二.实例的内存
1.Oracle实例的内存使用分为两类:
PGA(Program Global Area,程序全局区域)
SGA(System Global Area,系统全局区域)
2.PGA: 非共享内存,在执行SQL语句时,服务器进程使用PGA来存储会话特有的数据,包括:
临时表
排序区(sort_area_size) -- size of in-memory sort work area
散列排序区(hash_area_size) -- size of in-memory hash work area
归并位图缓冲(bitmap_merge_area_size) -- maximum memory allow for BITMAP MERGE
位图索引缓冲(create_bitmap_area_size) -- size of create bitmap buffer for bitmap index
变量
调用堆栈
-- 当前分配的PGA实际大小:
select sum(value)
from v$sesstat natural join v$statname
where name='session pga memory';
-- 当前的pga性能.
select name,value from v$pgastat;
·aggregate PGA target parameter: 当前pga_aggregate_target的值
·aggregate PGA auto target: 当前可用于自动分配的PGA大小,应该比pga_aggregate_target小
·global memory bound: 自动模式下工作区域的最大大小,根据工作负荷自动调整
·over allocation count: 实例启动后发生的分配次数,大于0需要考虑增加PGA的值
·cache hit percentage: 命中率
-- process使用的pga空间
select sum(pga_used_mem)/1024/1024 from v$process;
-- 排序区性能
select name,value from v$sysstat where name like '%sort%';
-- 排序区大小
select name,value from v$parameter where name = 'sort_area_size';
-- 其他有关PGA的视图
v$sql_workarea_histogram
v$sql_workarea_active
v$sql_workarea
v$tempseg_usage
3.SGA: 共享内存,包含多个内存结构:
共享池(shared_pool_size) -- 包括库缓存,字典缓存等.
数据库高速缓冲区(db_cache_size)
大池(large_pool_size)
流池(streams_pool_size)
Java池(java_pool_size)
列式内存区(inmemory_size) -- 12c的新特性,默认不会启用
日志缓冲区(log_buffer) -- 大小一般是正确的,可将参数设为比默认值大,但往往会导致性能下降.
db_keep_cache_size
db_recycle_cache_size
db_nk_cache_size (n=2,4,8,16,32)
-- 当前分配的SGA实际大小:
select sum(bytes) from v$sgastat;
-- 共享池库缓存(Library cache)性能(命中率)
select sum(pins) pins,sum(reloads) reloads,sum(pins)/(sum(pins) + sum(reloads)) * 100 hit_percent from v$librarycache;
-- 共享池字典缓存(Data dictionary cache)性能(命中率)
select parameter,gets,getmisses,gets/(gets+getmisses)*100 percent from v$rowcache where gets + getmisses <> 0;
-- 数据库缓冲区使用情况
select name,value from v$sysstat where name in ('db block gets','consistent gets','physical reads') order by name;
select name,value from v$sysstat where name in ('parse time cpu','parse time elapsed','parse count (hard)');
-- 数据库缓冲区性能(命中率)
select (1 - (physical_reads / (db_block_gets + consistent_gets))) * 100
from (select sum(decode(name,'db block gets',value,0)) db_block_gets,
sum(decode(name,'consistent gets',value,0)) consistent_gets,
sum(decode(name,'physical reads',value,0)) physical_reads
from v$sysstat
where name in ('db block gets','consistent gets','physical reads'))
-- 重做日志缓冲区性能
select name,sum(gets),sum(misses),sum(immediate_gets),sum(immediate_misses)
from v$latch
where name like 'redo%'
group by name;
*** SQL语句的共享SQL区域在SGA的共享池中,专用的SQL区域在会话的PGA中.
三.PGA内存自动管理(pga_aggregate_target)
使用两个实例参数来启用PGA内存自动管理:
workarea_size_policy (默认值AUTO)
pga_aggregate_target (默认设置大于10M,或是SGA大小的20%,只是目标,并非硬性限制)
会根据需要将PGA分配给会话,同时力求将PGA分配总量保持在pga_aggregate_target范围以内.
对于很多系统而言,pga_aggregate_target的默认值过小,不能达到最佳性能,需要调整。
alter system set workarea_size_policy=auto scope=memory;
alter system set pga_aggregate_target=1000m scope=memory;
ORACLE给的建议是: OLTP系统PGA=(TotalMemory)*80%*20%。
四.SGA内存自动管理(sga_target)
自动SGA内存管理只会自动调整以下参数:
shared_pool_size
db_cache_size
large_pool_size
java_pool_size
streams_pool_size
必须手动调整以下参数:
log_buffer
db_keep_cache_size
db_recycle_cache_size
db_nk_cache_size (n=2,4,8,16,32)
要启用SGA自动管理,需要将手工管理的参数设为默认值或0,并设置参数sga_target:
alter system set db_cache_size=0 scope=memory;
alter system set shared_pool_size=0 scope=memory;
alter system set large_pool_size=0 scope=memory;
alter system set java_pool_size=0 scope=memory;
alter system set sga_target=2000m scope=memory;
sga_target的大小不能超过sga_max_size参数的值,sga_max_size是静态参数.
alter system set sga_max_size=3000m scope=spfile;
实例将监视各个SGA组件的内存需求,并根据需要将内存分配给组件,如有必要,会减少内存容量,以便将内存分配总量控制在目标范围内.
包含在目标范围的还有日志缓冲区,其大小使用log_buffer参数设置,是静态的.
log_buffer的默认大小可能是正确的,可将其设置得比默认值大,但往往会导致性能下降,如果将其设置得低于默认值,将忽略相应的设置。
-- 可以将热点表保持在buffer cache中(手动调整的参数db_keep_cache_size).
alter system set db_keep_cache_size=10m;
alter table scott.emp storage (buffer_pool keep);
-- SGA内存自动管理时,SGA实际大小:
select component,current_size from v$sga_dynamic_components
五.自动内存管理(memory_target)
使用自动内存管理(Automatic Memory Management),允许PGA和SGA之间内存转换,将自动PGA管理和自动SGA管理又向前推进一步.
设置自动内存管理的参数
memory_target -- 动态参数,但调整不能超过参数memory_max_target的值.
memory_max_target -- 静态参数,需要通过scope=spfile子句进行调整后重启实例.
-- 禁用自动内存管理
alter system set memory_target=0 scope=memory;
-- 启用自动内存管理
alter system set memory_target=5000M scope=memory
memory_target的大小不能超过memory_max_target参数的值,memory_max_target是静态参数.
alter system set memory_max_target=5000m scope=spfile;
当前分配的PGA实际大小:
select sum(value)
from v$sesstat natural join v$statname
where name='session pga memory';
当前分配的SGA实际大小:
select sum(bytes) from v$sgastat;
六.列式内存(inmemory)
12c数据库可以启用列式内存特性,大大提高OLAP查询性能。inmemory是SGA共享内存的组成部分。
alter system set inmemory_size=1280M scope=spfile; -- 指定内存列存的内存区域大小
alter system set inmemory_max_populate_servers=2 scope=spfile; -- 设置用于将数据加载到内存的后台进程数量
alter session set inmemory_clause_default='INMEMORY'; -- 所有表都启用In-Memory
alter session set inmemory_clause_default=''; -- 需修改表启用In-Memory
alter system set inmemory_force=default; -- 明确指定INMEMORY属性的表才放入In-Memory中
alter system set inmemory_force=on; -- 没有明确指定NO INMEMORY属性的表放入In-Memory中
alter system set inmemory_force=off; -- 没有表会被放入In-Memory中
alter session set inmemory_query=enable; -- 启用In-Memory查询
alter session set inmemory_query=disable; -- 停止In-Memory查询
alter table tablename inmemory; -- 启用表的In-memory特性
select * from v$inmemory_area;
七.内存顾问程序
Oracle实例收集大量有关活动和性能的信息,这些统计信息收集到内存中,并由MMON后台进程定期转储到自动工作负荷仓库(AWR)。
这些统计信息支持着内存顾问程序,内存顾问程序是计算更改SGA和PGA内存结构大小的效果的工具。
AWR工具使用顾问程序制定有关内存分配的决策,DBA可以通过各种视图和EM查看。
只有将参数statistics_level参数设置为TYPICAL或ALL,才能启用顾问程序。
-- PGA顾问程序(PGA预期大小设置,占实际大小设置的比例,评估的磁盘I/O量):
select pga_target_for_estimate,pga_target_factor,estd_extra_bytes_rw,
estd_pga_cache_hit_percentage,estd_overalloc_count
from v$pga_target_advice;
select * from v$pga_target_advice_histogram;
-- SGA顾问程序(SGA预期大小设置,占实际大小设置的比例,评估的执行SQL语句的总时间):
select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;
-- 共享池顾问程序:
select * from v$shared_pool_advice;
-- 数据库高速缓冲区顾问程序:
select name, block_size, size_for_estimate, buffers_for_estimate, estd_physical_read_factor, estd_physical_reads
from v$db_cache_advice
where block_size = 8192;
-- 内存顾问程序(给出总体内存分配量,SGA+PGA) (内存预期大小设置,占实际大小设置的比例,评估的执行SQL语句的总时间):
select memory_size,memory_size_factor,estd_db_time from v$memory_target_advice;
在Database Control使用顾问程序:
Related Links/Advisor Central/Memory Advisors