Latch hit % 低于98%
有套RAC，latch hit%一直以来都在99.5%以上，但是今天报了好几次低于98%，而且从晚上19:00开始持续了大约2小时。不懂latch，不知从何下手，所以请飞总帮忙分析一下，多谢多谢！附件的两份AWRDD是RAC的两个节点。
节点1的load profile
Load Profile
1st per sec2nd per sec%Diff1st per txn2nd per txn%Diff
DB time:0.40.2-54.10.00.0-100.0
CPU time:0.40.2-52.80.00.0-100.0
Redo size:139,635.9107,960.3-22.72,648.42,687.21.5
Logical reads:19,464.39,914.5-49.1369.2246.8-33.2
Block changes:1,045.8823.5-21.319.820.53.3
Physical reads:1.20.5-56.80.00.0-50.0
Physical writes:57.549.4-14.01.11.212.8
User calls:2,856.8510.7-82.154.212.7-76.5
Parses:25.422.8-10.10.50.618.8
Hard parses:0.10.0-37.50.00.00.0
W/A MB processed:217,371.48,092,909.83,623.14,122.8201,438.53,623.1
Logons:0.20.2-5.30.00.00.0
Executes:2,660.4355.1-86.750.58.8-82.5
Transactions:52.740.2-23.8
节点2的load profile
1st per sec2nd per sec%Diff1st per txn2nd per txn%Diff
DB time:0.40.2-56.40.00.0-100.0
CPU time:0.40.2-57.90.00.0-100.0
Redo size:132,491.998,638.0-25.62,639.32,669.61.1
Logical reads:20,023.09,325.8-53.4398.9252.4-36.7
Block changes:992.7760.1-23.419.820.64.0
Physical reads:1.10.5-53.10.00.0-50.0
Physical writes:55.046.5-15.51.11.314.5
User calls:3,324.6582.8-82.566.215.8-76.2
Parses:23.620.5-13.00.50.619.1
Hard parses:0.00.00.00.00.00.0
W/A MB processed:208,522.610,068,928.64,728.74,153.8272,509.34,728.7
Logons:0.10.10.00.00.00.0
Executes:3,139.1440.0-86.062.511.9-81.0
Transactions:50.236.9-26.4
两次对比awr 业务类型或者业务量相差很大，比较本身意义不大
节点1
% of Total SleepsGet RequestsMissesSpin GetsSleepsWait Time (cs)
Latch Name1st2ndDiff1st2nd1st2nd1st2nd1st2nd1st2ndDiff
process allocation39.7051.4511.7637,74037,62415215844501311241,051,2021,000,897-50,305
row cache objects29.7019.50-10.1937,003,9085,058,29579,68427,95079,59227,90698475,6063,273-2,333
节点2
% of Total SleepsGet RequestsMissesSpin GetsSleepsWait Time (cs)
Latch Name1st2ndDiff1st2nd1st2nd1st2nd1st2nd1st2ndDiff
row cache objects88.3472.03-16.3243,910,6585,895,36498,13230,92597,99430,82414410310,8472,600-8,247
process allocation3.079.796.7236,86336,7826121051440,344113,00872,664
从这里看，主要是row cache objects和process allocation相关latch 变化明显
本帖最后由 Johnliu 于 2014-12-10 12:17 编辑
请问飞总，
我在top sql order by gets中发现存在大量执行频繁、写法错误的SQL（因为应用程序分表的框架需要），产生大量的逻辑读，错误执行计划cost为153k，逻辑读为3468，消耗时间0.06秒，经过改写（去掉column='NULL')后cost为62，逻辑读为18，消耗时间0.01秒
SQL：SELECT * FROM t_project_ticket2 x WHERE rownum < 1000 AND x.PD_STATUS = 30 AND x.PD_LOTID=:1 AND x.PD_ADDTIME < :2 AND x.PD_ENDTIME > SYSDATE OR x.PD_USERNAME = :3 OR x.PD_ID='NULL' ORDER BY x.PD_ADDTIME
请问这个会不会是主因？ 低效的执行计划：
------------------------------------------------------------------------------------------------------ ---------------
| Id| Operation | Name | Rows| Bytes |TempSpc| Cost (%C PU)| Time |
------------------------------------------------------------------------------------------------------ ---------------
| 0 | SELECT STATEMENT | |2127K| 673M| | 153K (1)| 00:30:44 |
| 1 |SORT ORDER BY | |2127K| 673M| 874M| 153K (1)| 00:30:44 |
| 2 | COUNT | | | | | | |
|*3 | FILTER | | | | | | |
| 4 | TABLE ACCESS BY INDEX ROWID | T_PROJECT_TICKET1 |2127K| 673M| |2585 (1)| 00:00:32 |
| 5 | BITMAP CONVERSION TO ROWIDS | | | | | | |
| 6 | BITMAP OR | | | | | | |
| 7 | BITMAP AND | | | | | | |
| 8 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|*9 | INDEX RANGE SCAN | IDX_STATUS_1 | | | | 6 (0)| 00:00:01 |
|10 | BITMAP CONVERSION FROM ROWIDS| | | | | | |
|* 11 | INDEX RANGE SCAN | IDX_TICKET1_LOTID | | | |2436 (1)| 00:00:30 |
|12 | BITMAP CONVERSION FROM ROWIDS | | | | | | |
|* 13 | INDEX RANGE SCAN | IDX_TICKET1_USERNAME | | | | 3 (0)| 00:00:01 |
|14 | BITMAP CONVERSION FROM ROWIDS | | | | | | |
|* 15 | INDEX RANGE SCAN | PK_T_PROJECT_TICKET1 | | | | 2
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("X"."PD_STATUS"=30 AND "X"."PD_LOTID"=501 AND "X"."PD_ADDTIME"<SYSDATE@!-1 AND
"X"."PD_ENDTIME">SYSDATE@! AND ROWNUM<1000 OR "X"."PD_USERNAME"='aaa')
9 - access("X"."PD_STATUS"=30)
11 - access("X"."PD_LOTID"=501)
13 - access("X"."PD_USERNAME"='aaa')
高效的执行计划：
Execution Plan
----------------------------------------------------------
Plan hash value: 294223498
---------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 |5644 | 62 (2)| 00:00:01 |
| 1 |SORT ORDER BY | | 17 |5644 | 62 (2)| 00:00:01 |
|*2 | COUNT STOPKEY | | | | | |
|*3 | TABLE ACCESS BY INDEX ROWID| T_PROJECT_TICKET1 | 17 |5644 | 61 (0)| 00:00:01 |
|*4 | INDEX RANGE SCAN | IDX_STATUS_1 |1143 | | 6 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(ROWNUM<1000)
3 - filter("X"."PD_LOTID"=501 AND "X"."PD_ENDTIME">SYSDATE@! AND
"X"."PD_ADDTIME"<SYSDATE@!-1)
4 - access("X"."PD_STATUS"=30)
没下文了？
页:
[1]