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 ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.