Skip to main content

How to set data cache,procedure cahe in ASE

1> sp_cacheconfig "default data cache","1G"
2> go
Msg 10879, Level 16, State 1:
Server 'SPICE_BIAPPS', Procedure 'sp_cacheconfig', Line 1167:
The current 'max memory' value '58368', is not sufficient to change the size of
cache 'default data cache' to '1G' (1048576 KB). 'max memory' should be greater
than 'total logical memory' '579786' required for the configuration.
(return status = 1)
1> sp_configure "max memory"
2> go
Parameter Name Default Memory Used Config Value
Run Value Unit Type
------------------------------ ----------- ----------- ------------
------------ -------------------- ----------
max memory 58368 116736 58368
58368 memory pages(2k) dynamic

(1 row affected)
(return status = 0)
1> sp_configure "max memory","1G"
2> go
Msg 257, Level 16, State 1:
Server 'SPICE_BIAPPS', Procedure 'sp_configure':
Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the
CONVERT function to run this query.
(return status = -6)
1> sp_configure "max memory",512000
2> go
Parameter Name Default Memory Used Config Value
Run Value Unit Type
------------------------------ ----------- ----------- ------------
------------ -------------------- ----------
max memory 58368 1024000 512000
512000 memory pages(2k) dynamic

(1 row affected)
Configuration option changed. ASE need not be rebooted since the option is
dynamic.
Changing the value of 'max memory' to '512000' reduces the amount of memory ASE
uses by 14 K. The reduced memory may be reused when this configure value
changes, but will not be released until ASE restarts.
(return status = 0)
1> sp_cacheconfig "default data cache","600M"
2> go
606208 Kilobytes added successfully to named cache (default data cache). Change
takes effect immediately.
(return status = 0)
1> sp_cacheconfig "p
2>
3> reset
1> sp_configure "procedure cache
2> "
(1 row affected)
(return status = 1)
1> sp_configure "procedure cache"
2> go
Parameter Name Default Memory Used Config Value
Run Value Unit Type
------------------------------ ----------- ----------- ------------
------------ -------------------- ----------
procedure cache size 7000 15254 7000
7000 memory pages(2k) dynamic

(1 row affected)
(return status = 0)
1> sp_configure "procedure cache",204800
2> go
Msg 5861, Level 16, State 2:
Server 'SPICE_BIAPPS', Procedure 'sp_configure', Line 860:
The current 'max memory' value '512000', is not sufficient to change the
parameter 'procedure cache size' to '204800'. 'max memory' should be greater
than 'total logical memory' '572584' required for the configuration.
(return status = 1)
1> sp_configure "procedure cache"179200
2> go
Msg 102, Level 15, State 1:
Server 'SPICE_BIAPPS', Line 1:
Incorrect syntax near '179200'.
1> sp_configure "procedure cache",179200
2> go
Msg 5861, Level 16, State 2:
Server 'SPICE_BIAPPS', Procedure 'sp_configure', Line 860:
The current 'max memory' value '512000', is not sufficient to change the
parameter 'procedure cache size' to '179200'. 'max memory' should be greater
than 'total logical memory' '544684' required for the configuration.
(return status = 1)
1> sp_configure "max memory",512000
2> go
Parameter Name Default Memory Used Config Value
Run Value Unit Type
------------------------------ ----------- ----------- ------------
------------ -------------------- ----------
max memory 58368 1024000 512000
512000 memory pages(2k) dynamic

(1 row affected)
Resulting configuration value and memory use have not changed from previous
values: new configuration value 512000, previous value 512000.
(return status = 0)
1> sp_configure "max memory",512000
2> go
Parameter Name Default Memory Used Config Value
Run Value Unit Type
------------------------------ ----------- ----------- ------------
------------ -------------------- ----------
max memory 58368 1024000 512000
512000 memory pages(2k) dynamic

(1 row affected)
Resulting configuration value and memory use have not changed from previous
values: new configuration value 512000, previous value 512000.
(return status = 0)
1> sp_configure "max memory",1024000
2> go
Parameter Name Default Memory Used Config Value
Run Value Unit Type
------------------------------ ----------- ----------- ------------
------------ -------------------- ----------
max memory 58368 2048000 1024000
1024000 memory pages(2k) dynamic

(1 row affected)
Configuration option changed. ASE need not be rebooted since the option is
dynamic.
Changing the value of 'max memory' does not increase the amount of memory
Adaptive Server uses.
(return status = 0)
1> sp_configure "procedure cache",204800
2> go
Parameter Name Default Memory Used Config Value
Run Value Unit Type
------------------------------ ----------- ----------- ------------
------------ -------------------- ----------
procedure cache size 7000 446474 204800
204800 memory pages(2k) dynamic

(1 row affected)
Configuration option changed. ASE need not be rebooted since the option is
dynamic.
Changing the value of 'procedure cache size' to '204800' increases the amount of
memory ASE uses by 431246 K.
(return status = 0)
1>

Comments

Post a Comment

Popular posts from this blog

Replication Server Quick Ref Guide

Replication Server Guide : • Replication Server Commands • ASE commands and system stored procedures • ASE RSSD stored procedures • RSSD tables • Replication Server System Functions • Troubleshooting Tips Abbreviations Some abbreviations which are used in this Quick Reference Guide: ASE Adaptive Server Enterprise DSI DataServer Interface - RS thread for a connection to a replicate database DIST Distributor - RS thread per primary database LTM Log Transfer Manager; synonym for RepAgent RS, RepServer Replication Server RepAgent Replication Agent (in primary dataserver) RepDef Replication Definition RSI Replication Server Interface - RS thread related to a route to another RepServer RSM Replication Server Manager (a DBA tool normally used in combination with Sybase Central) RSSD Replication Server System Database SQM Stable Queue Manager - RS thread managing a queue SQT Stable Queue Manager - RS thread assembling transactions Replication Server Commands The following commands can only be ...

Sybase IQ material - Quick Ref

Contents IQ 12.6 Commands IQ 12.6 Datatypes IQ 12.6 Functions IQ 12.6 System Procedures IQ 12.6 Catalogue Procedures IQ 12.6 Multiplex System Procedures IQ 12.6 ASE Compatibility System Procedures IQ 12.6 ASE Compatibility Catalogue Procedures IQ 12.6 Extended Procedures IQ 12.6 General Database Options IQ 12.6 T-SQL Compatibility Options IQ 12.6 DBISL Options IQ 12.6 Server Properties IQ 12.6 Database Properties IQ 12.6 Commands Two styles of commenting; the same as in ASE :- /* a comment between delimiters on one or more lines */ -- a comment until the end of this line ALLOCATE DESCRIPTOR descriptor-name [ WITH MAX { integer | host-variable } ] ^ALTER DBSPACE dbspace-name { READWRITE | READONLY | RELOCATE | SIZE dbspace-size [ KB | MB | GB | TB | PAGES ] | ADD dbspace-size [ KB | MB | GB | TB | PAGES ] } ALTER EVENT event-name [ DELETE TYPE | TYPE event-type ] { WHERE { trigger-condition | NULL } | { ADD | [ MODIFY ] | DELETE } SCHEDULE schedule-spec } [ ENABLE | DISABLE ] [ [ MODIFY...

Interesting Notes on ASE

Some hidden things about ASE Different States of SLEEP When all processes are shown as SLEEPING by sp_who except the one, which issued the sp_who command, how can a user tell the difference between hangings versus running processes? Definitions In pre-4.9.2 SQL Servers, the output of sp_who could be difficult to interpret. Processes showed only one type of SLEEP status, "sleeping". In System 10, and 4.9.2 Rollup 2115 and above, sp_who shows four types of sleep along with the other possible statuses: Value Meaning Infected The server erred with a stack trace, and the process got an error that would normally kill it. The process is infected instead of killed. Background This process is in the background recv sleep The process is waiting for input from the client Send sleep The process is waiting for a write to the client to complete alarm sleep The process is waiting for an alarm (usually means the process is waiting for a wait for command to complete). Lock sleep The process i...