ASA SQL User's Guide
Monitoring and Improving Performance
The database cache is an area of memory used by the database server to store database pages for repeated fast access. The more pages that are accessible in the cache, the fewer times the database server needs to read data from disk. As reading data from disk is a slow operation, the amount of cache available is often a key factor in determining performance.
You can control the size of the database cache on the database server command line when the database is started.
Adaptive Server Anywhere provides automatic resizing of the database cache. The capabilities are different on different operating systems. On Windows NT/2000/XP, Windows 95/98/Me, and UNIX operating systems, the cache grows and shrinks. Details are provided in the following sections.
Full dynamic cache sizing helps to ensure that the performance of your database server is not impacted by allocating inadequate memory. The cache grows when the database server can usefully use more, as long as memory is available, and shrinks when cache memory is required by other applications, so that the database server does not unduly impact other applications on the system. The effectiveness of dynamic cache sizing is limited, of course, by the physical memory available on your system.
Generally, dynamic cache sizing assesses cache requirements at the rate of approximately once per minute. However, after a new database is started or when a file grows significantly, statistics are sampled and the cache may be resized every five seconds for thirty seconds. After the initial thirty second period, the sampling rate drops back down to once per minute. Significant growth of a file is defined as a 1/8 growth since the database started or since the last growth that triggered an increase in the sampling rate. This change improves performance further, by adapting the cache size more quickly when databases are started dynamically and when a lot of data is inserted.
Dynamic cache sizing removes the need for explicit configuration of database cache in many situations, making Adaptive Server Anywhere even easier to use.
There is no dynamic cache resizing on Windows CE or Novell NetWare. When an Address Windowing Extensions (AWE) cache is used, dynamic cache sizing is disabled.
For more information about AWE caches, see -cw server option.
Limiting the memory used by the cache
Dynamic cache sizing (Windows NT/2000/XP, Windows 95/98/Me)
Dynamic cache sizing (UNIX)
Monitoring cache size