SAP Knowledge Base Article - Preview

2770344 - High SSQLCACHE_SPIN in spinlock contention when insert into a column with numeric datatype - ASE

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.