Monitor and tune the SQL statement cache

The SQL statement cache stores optimized SQL statements so that multiple users who run the same SQL statement can achieve some performance improvements.

These performance improvements are:

For more information about the effect of the SQL statement cache on the performance of individual queries, see Optimize queries with the SQL statement cache.

Database server actions when using the SQL statement cache shows how the database server accesses the SQL statement cache for multiple users.
  • When the database server runs an SQL statement for User 1 for the first time, the database server checks whether the same exact SQL statement is in the SQL statement cache. If it is not in the cache, the database server parses the statement, determines the optimal query plan, and runs the statement.
  • When User 2 runs the same SQL statement, the database server finds the statement in the SQL statement cache and does not optimize the statement.
  • Similarly, if User 3 and User 4 run the same SQL statement, the database server does not optimize the statement. Instead, it uses the query plan in the SQL statement cache in memory.
    Figure 1: Database server actions when using the SQL statement cache

    This figure is described in the surrounding text.