Symptom
- Observed very high SSQLCACHE_SPIN spinlock contention as well as slow performance and high CPU.
- dbcc prsqlcache show duplicate update statements in the same hash bucket.
- Each supdate statement has different precision and scale of the numeric column along with a mixture of nulls for some columns.
- Here is a portion of dbcc prsqlcache output:
Bucket# 5655, Entry Count: 4666, address 0x0x2aaec1b821c0
Empty Bucket Ct: 7810, Maximum Entries In Bucket# 5655: 4666, Avg. Entries Per Bucket: 0.735686, Std. Dev.: 51.829462
The same insert statement looks like the following are all in the same bucket:
SQL TEXT: insert stmt_cache_test(pkey, col_01, col_02, col_03, col_04, col_05, col_06, col_07, col_08, col_09, col_10)
values (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10)
ssql_obdcl: (@p0 INT output, @p1 NUMERIC(10, 6) output, @p2 NUMERIC(9, 2)
output, @p3 NUMERIC(9, 1) output, @p4 NUMERIC(10, 4)
output, @p5 NUMERIC(13, 6) output, @p6 NUMERIC(7, 3)
output, @p7 NUMERIC(3, 1) output, @p8 NUMERIC(13, 3)
output, @p9 NUMERIC(19, 10) output, @p10 NUMERIC(17, 8) output)
Read more...
Environment
- SAP Adaptive Server enterprise 15.7
- SAP Adaptive Server Enterprise 16
Product
SAP Adaptive Server Enterprise 16.0
Keywords
CR812823 CR818327 insert update numeric procedure cache dynamic sql DYNAMIC_PREPARE , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Bug Filed
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.