oralce优化之10大要点三四

三、优化排序操作

    1、概念
    服务器首先在sort_area_size指定大小的内存区域里排序，如果所需的空间超过sort_area_size，排序会在临时表空间里进行。在专用服务器模式下，排序空间在PGA中，在共享服务器模式下，排序空间在UGA中。如果没有建立large pool，UGA处于shared pool中，如果建立了large pool，UGA就处于large pool中，而PGA不在sga中，它是与每个进程对应单独存在的。

     PGA：program global area,为单个进程（服务器进程或后台进程）保存数据和控制信息的内存区域。PGA与进程一一对应，且只能被起对应的进程读写，PGA在用户登录数据库创建会话的时候建立。

    有关排序空间自动管理的两个参数：
    Pga_aggregate_target: 10M-4000G，等于分配给oracle instance的所有内存减去SGA后的大小。
    Workarea_size_policy: auto/manual，只有Pga_aggregate_target已定义时才能设置为auto。
    这两个参数会取代所有的*_area_size参数。

    措施：

    尽可能避免排序；尽可能在内存中排序；分配合适的临时空间以减少空间分配调用。

    2、需要进行排序的操作：
    A、创建索引；
    B、涉及到索引维护的并行插入
    C、order by或者group by（尽可能对索引字段排序）
    D、Distinct
    E、union/intersect/minus
    F、sort-merge join
    G、analyze命令（仅可能使用estamate而不是compute）

    3、诊断和措施
    Select * from v$sysstat where name like ‘%sort%’;
    Sort(disk):要求Io去临时表空间的排序数目
    Sort(memory)：完全在memory中完成的排序数目
    Sort(rows)：被排序的行数合计

    Sort（disk）/ Sort（memory）<5%,如果超过5％，增加sort_area_size的值。
    SELECT disk.Value disk,mem.Value mem,(disk.Value/mem.Value)*100 ratio FROM v$sysstat disk,v$sysstat mem WHERE mem.NAME='sorts (memory)' AND disk.NAME='sorts (disk)';

    4、监控临时表空间的使用情况及其配置
    Select tablespace_name,current_users,total_extents,used_extents,extent_hits,max_used_blocks,max_sort_blocks FROM v$sort_segment ;

    Column Description
    CURRENT_USERS Number of active users
    TOTAL_EXTENTS Total number of extents
    USED_EXTENTS Extents currently allocated to sorts
    EXTENT_HITS Number of times an unused extent was found in the pool
    MAX_USED_BLOCKS Maximum number of used blocks
    MAX_SORT_BLOCKS Maximum number of blocks used by an individual sort

    临时表空间的配置：
    A、initial/next设置为sort_area_size的整数倍，允许额外的一个block作为segment的header
    B、pctincrease=0
    C、基于不同的排序需要建立多个临时表空间
    D、将临时表空间文件分散到多个磁盘上

1

四、诊断latch竞争

    1、概念
    Latch是简单的、低层次的序列化技术，用以保护SGA中的共享数据结构，比如并发用户列表和buffer cache里的blocks信息。一个服务器进程或后台进程在开始操作或寻找一个共享数据结构之前必须获得对应的latch，在完成以后释放latch。不必对latch本身进行优化，如果latch存在竞争，表明SGA的一部分正在经历不正常的资源使用。

    1）Latch的作用：
    A、序列化访问：保护SGA中的共享数据结构；保护共享内存的分配。
    B、序列化执行：避免同时执行某些关键代码；避免互相干扰。

    2）Latch请求的两种类型：
    A、willing-to-wait：请求的进程经过短时间的等待后再次发出请求，直到获得latch
    B、immediate：如果没有获得latch，请求的进程不等待，而是继续处理其他指令。
    2、检查Latch竞争
    检查latch free是不是主要的wait event：
    Select * from v$system_event order by time_waited;

    检查latch的使用情况：
    Select * from v$latch:
    与willing-to-wait请求有关的列：gets、misses、sleeps、wait_time、cwait_time、spin_gets
    与immediate请求有关的列：immediate_gets、immediate_misses

    Gets: number of successful willing-to-wait requests for a latch;
    Misses: number of times an initial wiling-to-wait request was unsuccessful;
    Sleeps: number of times a process waited after an initial willing-to-wait request;
    Wait_time: number of milliseconds waited after willing-to-wait request;
    Cwait_time: a measure of the cumulative wait time including the time spent spinning and sleeping,the overhead of context switches due to OS time slicing and page faults and interrupts;
    Spin_gets: gets that misses first try but succeed after spinning.

    Immediate_gets: number of successful immediate requests for each latch;
    Immediate_misss: number of unsuccessful immediate requests for each latch;

    一般无需调整latch，但是下列的措施是有用的：
    A、对处于竞争中的latch做进一步的调查
    B、如果竞争主要存在于shared pool和library cache中，可以考虑调整应用
    C、如果进一步的调查显示需要调整shared pool和buffer cache，就进行调整

    Select * from v$latch where name like ‘%shared pool%’ or name like ‘%library cache%’；

    如果竞争是在shared pool或library cache上，表示下列集中情况：
    A、不能共享的sql,应检查他们是否相似，考虑以变量代替sql中的常量：
    Select sql_text from v$sqlarea where executions=1 order by upper(sql_text);
    B、共享sql被重新编译，考虑library cache的大小是否需要调整：
    SELECT sql_text,parse_calls,executions FROM v$sqlarea where parse_calls>5;
    C、library cache不够大。

1