The DS_TOTAL_MEMORY configuration parameter and memory utilization

The DS_TOTAL_MEMORY configuration parameter places a ceiling on the amount of shared memory that a query can obtain. You can use this parameter to limit the performance impact of large, memory-intensive queries. The higher you set this parameter, the more memory a large query can use, and the less memory is available for processing other queries and transactions.

For OLTP applications, set DS_TOTAL_MEMORY to 20 - 50 percent of the value of SHMTOTAL, in KB. For applications that involve large decision-support (DSS) queries, increase the value of DS_TOTAL_MEMORY to 50 - 80 percent of SHMTOTAL. If you use your database server instance exclusively for DSS queries, set this parameter to 90 percent of SHMTOTAL.

A quantum unit is the minimum increment of memory that is allocated to a query. The Memory Grant Manager (MGM) allocates memory to queries in quantum units. The database server uses the value of DS_MAX_QUERIES with the value of DS_TOTAL_MEMORY to calculate a quantum of memory, according to the following formula:

quantum = DS_TOTAL_MEMORY / DS_MAX_QUERIES

The database server can adjust the size of the quantum dynamically when it grants memory. To allow for more simultaneous queries with smaller quanta each, increase the value of the DS_MAX_QUERIES configuration parameter.