100% CPU usage - stored procedure recompilations and rproccache_spin.

[former_member197005]

Hello.

We are using Sybase ver 15.7 sp135HF1.

Our configuration for procedure cache size

Sp_configure 'procedure cache size'

procedure cache size 19398656

and we use -T753.

When monitoring one of the processes in the system,

noticed a strange use of memory in the procedure.

We have stored proc with plan cost = 13990KB

took it from:

select ObjectName,MemUsageKB ,PlanID,CompileDate

from master..monCachedProcedures

where ObjectName = 'ProcedureTest'

order by CompileDate Desc

- Then we will see queries execution in SP.

- Querie on line 665 starts from 4390KB memory usage and finished on 13994KB and this ≈ plan cost

procedure_name PlanID BatchID ContextID LineNumber CpuTime WaitTime MemUsageKB

ProcedureTest 6567928 19 2547 0 0 0 212

ProcedureTest 6567928 19 2547 665 16 0 4390

ProcedureTest 6567928 19 2547 665 38 0 9724

ProcedureTest 6567928 19 2547 665 60 0 13994

ProcedureTest 6567928 19 2547 665 78 0 13994

ProcedureTest 6567928 19 2547 665 99 0 13994

ProcedureTest 6567928 19 2547 665 128 0 13994

ProcedureTest 6567928 19 2547 665 161 0 13994

ProcedureTest 6567928 19 2547 665 182 0 13994

ProcedureTest 6567928 19 2547 665 199 0 13994

ProcedureTest 6567928 19 2547 665 227 0 13994

ProcedureTest 6567928 19 2547 665 250 0 13994

ProcedureTest 6567928 19 2547 665 272 0 13994

ProcedureTest 6567928 19 2547 665 289 0 13994

ProcedureTest 6567928 19 2547 665 339 0 13994

ProcedureTest 6567928 19 2547 665 377 0 13994

ProcedureTest 6567928 19 2547 665 399 0 13994

ProcedureTest 6567928 19 2547 665 420 0 13994

- After query on line 665 is completed, the PLANID has changed,

which means that the query plan has been recompiled.

- Do I understand correctly that when a query uses memory of approximately the same size as the SP plan, will the procedure plan be recompiled and PLANID changed.

ProcedureTest 6568139 19 2547 892 444 0 1450

ProcedureTest 6568139 19 2547 1680 467 0 2190

ProcedureTest 6568139 19 2547 2885 489 0 3406

ProcedureTest 6568139 19 2547 3544 505 0 3918

ProcedureTest 6568139 19 2547 4031 525 0 4642

ProcedureTest 6568139 19 2547 4635 540 0 5350

ProcedureTest 6568139 19 2547 5653 563 0 6432

ProcedureTest 6568139 19 2547 6109 585 0 6886

ProcedureTest 6568139 19 2547 6647 604 0 7452

ProcedureTest 6568139 19 2547 7297 631 0 8222

ProcedureTest 6568139 19 2547 8411 661 0 9300

ProcedureTest 6568139 19 2547 9131 694 0 10266

ProcedureTest 6568139 19 2547 9720 714 0 10588

ProcedureTest 6568139 19 2547 9720 733 0 10588

ProcedureTest 6568139 19 2547 9720 752 0 10588

- Can this be the cause of the spinlock on the procedure cache?

- If so, what can you recommend to solve high-level spinlocks on Procedure Cache.

- Cause at this time sp_monitorconfig 'all' shows usage of procedure cache size on ~26%

Name Num_free Num_active Pct_act Max_Used Reuse_cnt Instance_Name

procedure cache size 14229849 5168807 26.65 5476352 14 NULL

Next sample

procedure cache size 14239588 5159068 26.59 5476352 14 NULL

- And "sysmon" counters by spinlocks and Procedure Cache Management

Spinlock::Instance per sec per xact grabs waits

spins/wait contention

------------------------------------- ---------- ---------- ---------- ---------- -----

----- ----------

Resource->rproccache_spin::1058 136683.1 96.4 5691176 4100493 523.7 72.1%

Resource->rdesmgr_spin::996 11562.8 8.2 4514844 346883 216.9 7.7%

Resource->rlocksleeptask_spin::1479 3720.6 2.6 1761375 111618 82.1 6.3%

Resource->rpssmgr_spin::1012 7254.2 5.1 4233614 217626 72.5 5.1%

Procedure Cache Management per sec per xact count % of total

--------------------------- ------------ ------------ ---------- ----------

Procedure Requests 5365.3 3.8 160960 n/a

Procedure Reads from Disk 486.0 0.3 14579 9.1 %

Procedure Writes to Disk 0.0 0.0 0 0.0 %

Procedure Removals 972.0 0.7 29160 n/a

Procedure Recompilations 485.8 0.3 14574 n/a

Next Sample

Spinlock::Instance per sec per xact grabs waits

spins/wait contention

------------------------------------- ---------- ---------- ---------- ---------- -----

----- ----------

Resource->rproccache_spin::1058 173651.3 130.1 10506979 5209538 54.3 49.6%

Resource->rdesmgr_spin::996 21013.6 15.7 6053955 630408 158.0 10.4%

Resource->rlocksleeptask_spin::1479 10660.6 8.0 3379220 319819 82.3 9.5%

Resource->rpssmgr_spin::1012 23533.3 17.6 8362803 705998 78.2 8.4%

Procedure Cache Management per sec per xact count % of total

--------------------------- ------------ ------------ ---------- ----------

Procedure Requests 7362.0 5.5 220860 n/a

Procedure Reads from Disk 871.2 0.7 26137 11.8 %

Procedure Writes to Disk 0.0 0.0 0 0.0 %

Procedure Removals 1741.5 1.3 52244 n/a

Procedure Recompilations 870.2 0.7 26106 n/a

- Is there a monitoring that will show the specific reason for the appearance of the spinlock on the procedural cache?