概念
Latch是简单的低层次的序列化技术用以保护SGA中的共享数据结构比如并发用户列表和buffer cache里的blocks信息一个服务器进程或后台进程在开始操作或寻找一个共享数据结构之前必须获得对应的latch在完成以后释放latch不必对latch本身进行优化如果latch存在竞争表明SGA的一部分正在经历不正常的资源使用
）Latch的作用
A序列化访问保护SGA中的共享数据结构保护共享内存的分配
B序列化执行避免同时执行某些关键代码避免互相干扰
）Latch请求的两种类型
Awillingtowait请求的进程经过短时间的等待后再次发出请求直到获得latch
Bimmediate如果没有获得latch请求的进程不等待而是继续处理其他指令
检查Latch竞争
检查latch free是不是主要的wait event
Select * from v$system_event order by time_waited;
检查latch的使用情况
Select * from v$latch:
与willingtowait请求有关的列getsmissessleepswait_timecwait_timespin_gets
与immediate请求有关的列immediate_getsimmediate_misses
Gets: number of successful willingtowait requests for a latch;
Misses: number of times an initial wilingtowait request was unsuccessful;
Sleeps: number of times a process waited after an initial willingtowait request;
Wait_time: number of milliseconds waited after willingtowait request;
Cwait_time: a measure of the cumulative wait time including the time spent spinning and sleepingthe 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= order by upper(sql_text);
B共享sql被重新编译考虑library cache的大小是否需要调整
SELECT sql_textparse_callsexecutions FROM v$sqlarea where parse_calls>;
Clibrary cache不够大
