The database server uses distribution statistics generated by the UPDATE STATISTICS statement in the MEDIUM or HIGH mode to determine the query plan with the lowest cost. When the database server accesses the distribution statistics for a specific column the first time, it reads the distribution statistics from the sysdistrib system catalog table on disk and stores the statistics in the data-distribution cache. These statistics can then be read for the optimization of subsequent queries that access the column.
Performance improves if these statistics are efficiently stored and accessed from the data-distribution cache. You can configure the size of the data-distribution cache with the DS_HASHSIZE and DS_POOLSIZE configuration parameters. For information about changing the default size of the data-distribution cache, refer to the chapter on queries and the query optimizer in your IBM Informix Dynamic Server Performance Guide.
When a session executes an SQL statement that requires access to a system catalog table, the database server reads data from the system catalog tables. The database server stores the catalog data for each queried table in structures that it can access more efficiently during subsequent queries on that table. These structures are created in the virtual portion of shared memory for use by all sessions. These structures constitute the dictionary cache.
You can configure the size of the dictionary cache with the DD_HASHSIZE and DD_HASHMAX configuration parameters. For more information about these parameters, refer to the chapter on configuration effects on memory in your IBM Informix Dynamic Server Performance Guide.
The SQL statement cache reduces memory usage and preparation time for queries. The database server uses the SQL statement cache to store parsed and optimized SQL statements that a user executes. When users execute a statement stored in the SQL statement cache, the database server does not parse and optimize the statement again, so performance improves.
For more information, see Setting SQL Statement Cache Parameters. For details on how these parameters affect the performance of the SQL statement cache, refer to the IBM Informix Dynamic Server Performance Guide.
The following database operations can use large amounts of the virtual portion of shared memory to sort data:
The amount of virtual shared memory that the database server allocates for a sort depends on the number of rows to be sorted and the size of the row, along with other factors.
For information on parallel sorts, refer to your IBM Informix Dynamic Server Performance Guide.
The database server converts an SPL routine to executable format and stores the routine in the UDR cache, where it can be accessed by any session.
When a session needs to access an SPL routine or other user-defined routine for the first time, the database server reads the definition from the system catalog tables and stores the definition in the UDR cache.
You can configure the size of the UDR cache with the PC_HASHSIZE and PC_POOLSIZE configuration parameters. For information about changing the default size of the UDR cache, refer to the chapter on queries and the query optimizer in your IBM Informix Dynamic Server Performance Guide.
The global pool stores structures that are global to the database server. For example, the global pool contains the message queues where poll threads for network communications deposit messages from clients. The sqlexec threads pick up the messages from the global pool and process them.
For more information, see the sections on network buffer pools and virtual portion of shared memory in your IBM Informix Dynamic Server Performance Guide.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]