SQL Statement Cache

The SQL statement cache stores parsed and optimized SQL statements so that multiple users who execute the same SQL statement can realize the following performance improvements:

• Reduced response times, because they bypass the parse and optimization steps, as Figure 10 shows
• Reduced memory usage, because the database server shares query data structures among users

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

Figure 10 shows how the database server accesses the SQL statement cache for multiple users.

• When the database server executes 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 executes the statement.
• When User 2 executes the same exact SQL statement, the database server finds the statement in the SQL statement cache and does not need to parse and optimize the statement.
• Similarly, if User 3 and User 4 execute the same exact SQL statement, the database server does not have to parse and optimize the statement. Instead, it uses the parse information and query plan in the SQL statement cache in memory.
  Figure 10. Database Server Actions When Using the SQL Statement Cache