Symptom
- You observe performance issue, high CPU caused by application SAPLFIS_ACDOCA_BIW in SAP HANA database.
- Collected run time dump (RTE) shows the following in HANA CPU Load Analysis section.
...
Conclusion
System is overloaded by a single query (i.e. over 50% of the threads are running the same query) with SQL: SELECT /* FDA WRITE */ DISTINCT ACDOCA . * FROM ACDOCA , ? AS t_00 (C_0 NVARCHAR(2), C_1 NVARCHAR(4), C_2 NVARCHAR(4), C_3 NVARCHAR(10), C_4 NVARCHAR(6)) WHERE ACDOCA . RCLNT = ? AND ACDOCA . RLDNR = t_00 . C_0 AND ACDOCA . RBUKRS = t_00 . C_1 AND ACDOCA . GJAHR = t_00 . C_2 AND ACDOCA . BELNR = t_00 . C_3 AND ACDOCA . DOCLN = t_00 . C_4 with statement hash: dd12c9e30a0ad46127fe6057cf20dda5
... - Further investigation shows the same execution performing bad when OLAP engine involved. Reference: 3327150 - SQL Statement Collection: "HANA_SQL_StatementHash_DataCollector" report for SAP HANA
...
|****************** |
|* STATEMENT TEXT * |
|****************** |
| |
|SELECT /* FDA WRITE */ DISTINCT "ACDOCA" . * FROM "ACDOCA" , ? AS "t_00" ("C_0" |
|NVARCHAR(2), "C_1" NVARCHAR(4), "C_2" NVARCHAR(4), "C_3" NVARCHAR(10), "C_4" NVARCHAR(6)) |
|WHERE "ACDOCA" . "RCLNT" = ? AND "ACDOCA" . "RLDNR" = "t_00" . "C_0" |
|AND "ACDOCA" . "RBUKRS" = "t_00" . "C_1" AND "ACDOCA" . "GJAHR" = "t_00" . "C_2" |
|AND "ACDOCA" . "BELNR" = "t_00" . "C_3" AND "ACDOCA" . "DOCLN" = "t_00" . "C_4" |
| |
|************* |
|* SQL CACHE * |
|************* |
| |
|SNAPSHOT_TIME HOST PLAN_ID ENG EXECUTIONS RECORDS REC_PER_EXEC ELAPSED_MS ELA_PER_EXEC_MS ELA_PER_REC_MS PREPARES PREPARE_MS LOCK_MS |
|=================== ========= ============= === ============== ============== ============== ================= =============== ============== =========== ============== ============== |
|CURRENT hostname_ 49221910003 CR 87 1326 15.24 1883 21.63 1.41 7 538 0 |
|2024/10/28 15:19:15 hostname_ 49221910003 CR 37 156 4.21 1029 27.81 6.59 7 538 0 |
|2024/10/28 14:19:14 hostname_ 49025570003 CR 69 996 14.43 1149 16.64 1.15 0 0 0 |
|2024/10/28 13:19:14 hostname_ 49025570003 CR 129 1580 12.24 1897 14.70 1.20 0 0 0 |
|2024/10/28 12:19:14 hostname_ 49025570003 CR 42 2718 64.71 857 20.39 0.31 3 178 0 |
|2024/10/28 11:19:15 hostname_ 48864730003 CR 83 616 7.42 1301 15.67 2.11 0 0 0 |
|2024/10/28 10:19:14 hostname_ 48864730003 CR 87 370 4.25 1570 18.04 4.24 7 395 0 |
|2024/10/28 09:19:14 hostname_ 48852410003 H 2 4 2.00 189 94.60 47.30 2 187 0 |
|2024/10/28 01:19:14 hostname_ 48583410003 CR 10 20 2.00 103 10.28 5.14 0 0 0 |
|2024/10/27 20:19:14 hostname_ 48583410003 CR 10 110 11.00 231 23.12 2.10 0 0 0 |
|2024/10/27 19:19:14 hostname_ 48583410003 CR 8 92 11.50 132 16.53 1.43 0 0 0 |
|2024/10/27 18:19:14 hostname_ 48583410003 CR 4 68 17.00 92 22.98 1.35 0 0 0 |
|2024/10/27 13:19:14 hostname_ 48583410003 CR 3 62 20.66 64 21.28 1.02 0 0 0 |
|2024/10/27 12:19:14 hostname_ 48583410003 CR 1 26 26.00 26 26.32 1.01 0 0 0 |
|2024/10/27 11:19:14 hostname_ 48583410003 CR 9 22 2.44 97 10.79 4.41 0 0 0 |
|2024/10/27 10:19:14 hostname_ 48583410003 CR 267 694 2.59 2858 10.70 4.11 2 89 0 |
|2024/10/26 23:19:14 hostname_ 48207790003 CR 1 8 8.00 27 27.38 3.42 0 0 0 |
|2024/10/26 22:19:14 hostname_ 48207790003 CR 9 190 21.11 176 19.60 0.92 0 0 0 |
|2024/10/26 21:19:14 hostname_ 48207790003 CR 10 688 68.80 231 23.07 0.33 0 0 0 |
|2024/10/26 20:19:14 hostname_ 48207790003 CR 10 48 4.80 166 16.56 3.45 0 0 0 |
|2024/10/26 19:19:14 hostname_ 48207790003 CR 4 2420 605.00 178 44.42 0.07 0 0 0 |
| |
|SNP_TIME HOST PLAN_ID ENG EXECUTIONS RECORDS REC_PER_EXEC ELAPSED_MS ELA_PER_EXEC_MS ELA_PER_REC_MS PREPARES PREPARE_MS LOCK_MS |
|========== ========= ============= === ============== ============== ============== ================= =============== ============== =========== ============== ============== |
|2024/10/28 hostname_ 49221910003 CR 37 156 4.21 1029 27.81 6.59 7 538 0 |
|2024/10/28 hostname_ 49025570003 CR 240 5294 22.05 3902 16.25 0.73 3 178 0 |
|2024/10/28 hostname_ 48864730003 CR 170 986 5.80 2870 16.88 2.91 7 395 0 |
|2024/10/28 hostname_ 48852410003 H 2 4 2.00 189 94.60 47.30 2 187 0 |
|2024/10/28 hostname_ 48583410003 CR 10 20 2.00 103 10.28 5.14 0 0 0 |
|2024/10/27 hostname_ 48583410003 CR 302 1074 3.55 3501 11.59 3.25 2 89 0 |
|2024/10/26 hostname_ 48207790003 CR 105 5740 54.66 2420 23.04 0.42 3 156 0 |
|2024/10/26 hostname_ 48136600003 CR 57 416 7.29 921 16.15 2.21 2 95 0 |
|2024/10/25 hostname_ 47939940003 CR 46 618 13.43 969 21.05 1.56 2 89 0 |
|2024/10/25 hostname_ 47347460003 CHR 440 3636 8.26 6730 15.29 1.85 3 200 0 |
|2024/10/25 hostname_ 47218480003 CR 9 20 2.22 122 13.51 6.08 0 0 0 |
|2024/10/24 hostname_ 47218480003 CR 38 404 10.63 881 23.17 2.17 3 280 0 |
|2024/10/24 hostname_ 46977480003 CR 256 4574 17.86 3845 15.01 0.84 3 185 0 |
|2024/10/24 hostname_ 46869470003 CR 91569 183188 2.00 639660 6.98 3.49 12 978 0 |
|2024/10/24 hostname_ 46577330003 HOR 11243 202358 17.99 34421083 3061.55 170.09 3 204 0 |
|2024/10/24 hostname_ 46371890003 CR 3 8 2.66 47 15.60 5.85 0 0 0 |
|2024/10/23 hostname_ 46371890003 CR 64 1278 19.96 1163 18.16 0.90 2 139 0 |
|2024/10/23 hostname_ 46242810003 CR 456 2218 4.86 5588 12.25 2.51 3 254 0 |
|2024/10/23 hostname_ 45934400003 CR 1452 5724 3.94 15161 10.44 2.64 3 290 0 |
|2024/10/23 hostname_ 45598500003 CR 13 28 2.15 166 12.78 5.93 0 0 0 |
|2024/10/22 hostname_ 45598500003 CR 87705 547162 6.23 743974 8.48 1.35 3 157 0 |
|2024/10/22 hostname_ 45329410003 CR 1075 3264 3.03 11786 10.96 3.61 3 227 0 |
|2024/10/22 hostname_ 45107030003 CR 219 2278 10.40 2575 11.75 1.13 3 236 0 |
|2024/10/22 hostname_ 44929810003 CR 472 1964 4.16 5264 11.15 2.68 3 178 0 |
|2024/10/22 hostname_ 44753620003 CR 38 236 6.21 656 17.26 2.78 2 134 0 |
|2024/10/22 hostname_ 44363000003 CR 22 48 2.18 239 10.86 4.97 0 0 0 |
|2024/10/21 hostname_ 44363000003 CR 199 2138 10.74 2873 14.43 1.34 2 95 0 |
|2024/10/21 hostname_ 44214920003 CR 453 1460 3.22 1872 4.13 1.28 3 196 0 |
|2024/10/21 hostname_ 43918860003 CHR 165 938 5.68 2181 13.21 2.32 3 179 0 |
|2024/10/21 hostname_ 43721580003 CR 8 16 2.00 67 8.35 4.17 0 0 0 |
|2024/10/20 hostname_ 43721580003 CR 144 728 5.05 1607 11.16 2.20 2 140 0 |
|2024/10/20 hostname_ 43379580003 CR 4 38 9.50 82 20.56 2.16 0 0 0 |
|2024/10/19 hostname_ 43379580003 CHR 494 3352 6.78 4820 9.75 1.43 4 306 0 |
|2024/10/19 hostname_ 43113090003 CR 1 12 12.00 30 29.79 2.48 0 0 0 |
|2024/10/18 hostname_ 43113090003 CR 63 538 8.53 1029 16.32 1.91 2 97 0 |
|2024/10/18 hostname_ 42949110003 CR 56406 136490 2.41 364134 6.45 2.66 2 120 0 |
|2024/10/18 hostname_ 42783460003 CR 22021 45000 2.04 144327 6.55 3.20 2 118 0 |
|2024/10/18 hostname_ 42705020003 CR 265 936 3.53 2524 9.52 2.69 2 92 0 |
|2024/10/18 hostname_ 42519580003 CR 58 128 2.20 550 9.48 4.29 0 0 0 |
|2024/10/17 hostname_ 42299350003 CR 635 15216 23.96 7906 12.45 0.51 3 200 0 |
|2024/10/17 hostname_ 41967730003 CHR 28779 123496 4.29 227896 7.91 1.84 3 225 0 |
|2024/10/17 hostname_ 41720260003 CR 9 20 2.22 105 11.71 5.27 0 0 0 |
| |
...
Read more...
Product
Keywords
SAP, HANA, database, db, performance, issue, high, CPU, SAPLFIS_ACDOCA_BIW, dd12c9e30a0ad46127fe6057cf20dda5, OLAP, eng, engine, NO_USE_OLAP_PLAN , KBA , HAN-DB-PERF , SAP HANA Database Performance , Problem
About this page
This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP for Me (Login required).Search for additional results
Visit SAP Support Portal's SAP Notes and KBA Search.