一套HP-UX上的10.2.0.4 RAC系统，其中一个节点出现大量的latch free:SQL memory manager latch等待事件， 相关ADDM性能信息：
SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ, To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE" FROM X$KSMSP GROUP BY KSMCHCLS; CLASS NUM SIZ AVG SIZE -------- ---------- ---------- ------------ R-freea 927 9481080 9.99k freeabl 1037478 2001975328 1.88k R-free 593 170107192 280.14k recr 515080 595805056 1.13k R-perm 498 35345056 69.31k R-recr 2 2224 1.09k perm 97848 1320357168 13.18k free 251577 161871664 .63k DETAILED ADDM REPORT FOR TASK 'TASK_21286' WITH ID 21286 -------------------------------------------------------- Analysis Period: 26-OCT-2009 from 10:00:52 to 11:00:12 Database ID/Instance: 2429423994/1 Database/Instance Names: VPROD/VPROD Host Name: crmdb1 Database Version: 10.2.0.4.0 Snapshot Range: from 10253 to 10254 Database Time: 420176 seconds Average Database Load: 118 active sessions ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ FINDING 1: 75% impact (313574 seconds) -------------------------------------- Database latches in the "Other" wait class were consuming significant database time. RECOMMENDATION 1: DB Configuration, 75% benefit (313574 seconds) RATIONALE: The latch "SQL memory manager latch" with latch id 289 was one of the top latches contributing to high "latch free" waits. RELEVANT OBJECT: database latch# 289 SYMPTOMS THAT LED TO THE FINDING: SYMPTOM: Wait class "Other" was consuming significant database time. (75% impact [313620 seconds]) FINDINGS ======== Load Profile Per Second Per Transaction Redo size: 534,863.07 8,586.10 Logical reads: 527,828.57 8,473.18 Block changes: 13,065.49 209.74 Physical reads: 3,999.96 64.21 Physical writes: 617.50 9.91 User calls: 9,869.10 158.43 Parses: 3,287.89 52.78 Hard parses: 27.90 0.45 Sorts: 1,564.66 25.12 Logons: 4.35 0.07 Executes: 5,423.19 87.06 COMMENTS ========== Load in terms of GETS is tremendous and no particular SQL is responsible for a large percentage of the load. It may simply be too much for the hardware/configuration. HOWEVER ========= I/O while moderate in volume is very slow. Tablespace IO Stats * ordered by IOs (Reads + Writes) desc Tablespace Reads Av Reads/s Av Rd(ms) Av Blks/Rd Writes Av Writes/s Buffer Waits Av Buf Wt(ms) ------------------------------------------------------------------------------------------------------ TBS_CRM_TS_S1 8,340,219 772 4.48 2.95 356,810 33 17,284,247 2.15 TBS_CRM_IDX_S1 1,643,104 152 7.89 1.22 298,265 28 22,684 4.34 TEMP 762,343 71 1.09 6.67 411,652 38 0 0.00 TBS_BAKDATA 358,137 33 6.00 4.11 93,672 9 335,632 6.32 TBS_RM_TS_S1 400,528 37 9.57 1.09 39,000 4 1,546 10.49 TBS_RM_IDX_S1 399,888 37 8.71 1.01 33,342 3 1,577 9.74 TBS_PROD 422,348 39 3.68 1.00 941 0 5 12.00 TBS_SP_TS_S1 341,104 32 6.26 12.19 504 0 44 5.68 TBS_BFBAKDATA 187,013 17 2.34 7.49 909 0 104,797 2.75 TBS_PF 141,810 13 8.76 1.79 8,571 1 563 11.39 TBS_BSS_SYS 118,965 11 2.26 14.72 2,171 0 7 0.00 So about 1/3 typical performance in terms of Av Rd(ms). SEGMENTS ========== Segments by Logical Reads * Total Logical Reads: 5,699,755,248 * Captured Segments account for 72.1% of Total Owner Tablespace Name Object Name Subobject Name Obj. Type Logical Reads %Total CRM TBS_CRM_TS_S1 PROD_2_TD TABLE 454,967,408 7.98 RM TBS_RM_TS_S1 PHONE_NUMBER TABLE 435,121,200 7.63 EVENT TBS_CRM_TS_S1 CUST_INTERACTION TABLE 386,838,512 6.79 CRM TBS_CRM_TS_S1 CO_2_TD TABLE 383,932,304 6.74 CRM TBS_CRM_TS_S1 PARTY_IDENTIFY_EXTEND TABLE 298,659,184 5.24 So 5 individual segments are the target of 72% of all gets. . ACTION PLAN ============ Please do: (1) Disk I/O ----------------- Please ask your SA why Disk I/O is so slow. (2) Segements --------------------- Please consider partitioning these 5 objects to spread out GETS and READS: Owner Tablespace Name Object Name --------------------------------------------- CRM TBS_CRM_TS_S1 PROD_2_TD RM TBS_RM_TS_S1 PHONE_NUMBER EVENT TBS_CRM_TS_S1 CUST_INTERACTION CRM TBS_CRM_TS_S1 CO_2_TD CRM TBS_CRM_TS_S1 PARTY_IDENTIFY_EXTEND
诊断发现shared pool共享池有较多的空闲内存，反倒是IO响应速度的Av Rd(ms)要慢于典型的1 ms to 5 ms。 需要SA系统管理员进一步确认存储IO是否存在性能问题。 文档《Note.457063.1 Slow I/O On HP Unix》介绍了HP-UX平台上IO性能瓶颈的一些解决路径，引用如下：
本文转自maclean_007 51CTO博客，原文链接:http://blog.51cto.com/maclean/1278207
