Skip to Content
0
Former Member
Aug 15, 2014 at 04:05 AM

procedure cache is reaching 100%

1142 Views

One of Sybase sever running on older version having this abnormal issue.procedure cache size usage is growing gradually upto 100% suddenly in every 2-3 days and server got hung finally. application updation is managed by batch job in every end of day.

We didn't find anything abnormal and this was happened sometime in non-business hours when there is no activity running on server also

Any advice on this type of problem

1> sp_monitorconfig "all"

2>

Name Num_free Num_active Pct_act Max_Used Num_Reuse

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

additional network memory 472860 1563904 76.78 1563904 0

audit queue size 100 0 0.00 0 0

disk i/o structures 0 10382 100.00 10485 0

heap memory per user 4096 0 0.00 0 0

max cis remote connection 3200 0 0.00 0 0

max memory 351520 8037088 95.81 8037088 0

max number network listen 4 1 20.00 1 0

max online engines 0 8 100.00 8 0

memory per worker process 796 228 22.27 228 0

number of alarms 50 10 16.67 14 0

number of aux scan descri 400 0 0.00 50 0

number of devices 16 14 46.67 14 0

number of dtx participant 500 0 0.00 0 0

number of java sockets 817 0 0.00 0 0

number of large i/o buffe 6 0 0.00 1 0

number of locks 298624 1376 0.46 29781 0

number of mailboxes 128 128 50.00 128 0

number of messages 64 0 0.00 0 0

number of open databases 9 16 64.00 16 0

number of open indexes 7807 4193 34.94 4228 0

number of open objects 32826 8134 19.86 13600 0

number of remote connecti 20 0 0.00 3 0

number of remote logins 20 0 0.00 3 0

number of remote sites 9 1 10.00 2 0

number of sort buffers 32767 0 0.00 99542 0

number of user connection 590 210 26.25 257 0

number of worker processe 128 0 0.00 0 0

partition groups 1024 0 0.00 0 0

permission cache entries 25 7 21.88 10 870651

procedure cache size 2335419 2664581 53.29 2676618 0

size of global fixed heap 300 0 0.00 0 0

size of process object he 3000 0 0.00 0 0

size of shared class heap 3072 0 0.00 0 0

size of unilib cache 200816 720 0.36 720 0

txn to pss ratio 12800 0 0.00 0 0

(return status = 0)

Here is cache details as below

1> sp_helpcache

2>

Cache Name Config Size Run Size Overhead

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

default data cache 4096.00 Mb 4096.00 Mb 432.16 Mb

Memory Available For Memory Configured

Named Caches To Named Caches

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

4096.01 Mb 4096.00 Mb

------------------ Cache Binding Information: ------------------

Cache Name Entity Name Type Index Name Status

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

(return status = 0)

1> sp_cacheconfig

2>

Cache Name Status Type Config Value Run Value

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

default data cache Active Default 4096.00 Mb 4096.00 Mb

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

Total 4096.00 Mb 4096.00 Mb

==========================================================================

Cache: default data cache, Status: Active, Type: Default

Config Size: 4096.00 Mb, Run Size: 4096.00 Mb

Config Replacement: strict LRU, Run Replacement: strict LRU

Config Partition: 8, Run Partition: 8

IO Size Wash Size Config Size Run Size APF Percent

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

2 Kb 491520 Kb 0.00 Mb 3072.00 Mb 10

16 Kb 209664 Kb 1024.00 Mb 1024.00 Mb 10

1> sp_configure "procedure"

2>

Parameter Name Default Memory Used Config Value Run Value Unit Type

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

allow procedure grouping 1 0 1 1 switch dynamic

procedure cache size 3271 10898890 5000000 5000000 memory pages(2k) dynamic

(1 row affected)

(return status = 1)

Thanks

April