ORACLE 等待事件latch: session allocation
操作系统：AIX 5.3ORACLE：10.2.0.4
前几天 客户反应 应用程序运行缓慢。我检查数据库AWR报告，发现以下等待事件latch: session allocation 非常消耗CPU资源，网上查找资料， 等待事件 latch：session allocation 是比较明显的建立会话时候出现的等待，然后观察 connection management call elapsed time 建立会话花费时间。这些都属实。但是 检查系统故障时的AWR报告 和 系统正常时的AWR 报告 Load profile 里的 logon参数 并没有太大的差异。 目前对于该问题 还是不太清楚，请刘大 帮忙分析，指出处理该问题的思路。不胜感激
附件
awr_2014_12_19_15_not ok.html 是系统故障时的 AWR报告；
awr_2014_12_23_10_ok.html 是系统正常时的AWR报告
该系统特征为 执行解析比极低 0.32 ，问题出现时 表现为大量解析等待
vent Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
CPU time 4,218 30.8
latch: shared pool 17,135 1,925 112 14.0 Concurrency
latch: library cache 17,984 1,614 90 11.8 Concurrency
cursor: pin S wait on X 71,119 721 10 5.3 Concurrency
latch free 1,288 168 130 1.2 Other
Event Waits Time(s) Avg Wait(ms) % Total Call Time Wait Class
latch free 198,917 57,588 290 79.4 Other
latch: library cache 150,216 41,739 278 57.5 Concurrency
latch: session allocation 119,104 34,834 292 48.0 Other
latch: shared pool 103,678 28,946 279 39.9 Concurrency
CPU time 3,476 4.8
硬解析不高大约为每秒4次
SGA breakdown difference
ordered by Pool, Name
N/A value for Begin MB or End MB indicates the size of that Pool/Name was insignificant, or zero in that snapshot
Pool Name Begin MB End MB % Diff
java free memory 26.30 26.30 0.00
java joxlod exec hp 5.52 5.52 0.00
large PX msg pool 1.03 1.03 0.00
large free memory 14.97 14.97 0.00
shared CCursor 1,351.69 1,328.34 -1.73
shared Cursor Stats 130.80 130.80 0.00
shared PCursor 911.14 895.63 -1.70
shared free memory 749.07 807.65 7.82
shared kglsim heap 124.88 124.88 0.00
shared kglsim object batch 169.13 169.13 0.00
shared library cache 682.37 668.86 -1.98
shared sql area 5,473.85 5,465.33 -0.16
buffer_cache 6,512.00 6,512.00 0.00
fixed_sga 2.02 2.02 0.00
log_buffer 13.98 13.98 0.00
sql area达到了5g SQL ordered by Version Count
Only Statements with Version Count greater than 20 are displayed
Version Count Executions SQL Id SQL Module SQL Text
56 157 5p8jryq7s0rkt td2soci@sstfsrv1 (TNS V1-V3) UPDATE PTS SET INR = :V0, OBJ...
49 2 5qfwxdxpvmdt4 td2soci@sstfsrv1 (TNS V1-V3) UPDATE CORPTY SET INR = :V0, ...
48 10,420 2wkqpwk877aaw td2soci@sstfsrv1 (TNS V1-V3) UPDATE WFE SET WFSINR = :V0, ...
24 60 bnfwkpwbvmg4u td2soci@sstfsrv1 (TNS V1-V3) INSERT INTO CMI ( INR, OWNREF...
个别SQL的Version Count 达到了10000以上 select sql_id, FORCE_MATCHING_SIGNATURE, sql_text
from v$SQL
where FORCE_MATCHING_SIGNATURE in
(select /*+ unnest */
FORCE_MATCHING_SIGNATURE
from v$sql
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 10)
给出如上语句的输出 Maclean Liu(刘相兵 发表于 2014-12-25 10:54 static/image/common/back.gif
select sql_id, FORCE_MATCHING_SIGNATURE, sql_text
from v$SQL
where FORCE_MATCHING_SIGNATURE in
刘大，你好。以上内容是数据库巡检时发现的问题，等下月巡检的时间，我把 结果发上来
页:
[1]