Oracle OCP笔记(22)性能管理 - 内存管理

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