SQL Statement Cache

Before the database server executes an SQL statement, it must first parse and optimize the statement. These steps can be time consuming, depending on the size of the SQL statement.

The database server can store the parsed and optimized SQL statement in the virtual portion of shared memory, in an area called the SQL statement cache. The SQL statement cache (SSC) can be accessed by all users, and it allows users to bypass the parse and optimize steps before they execute the query. This capability can result in the following significant performance improvements:

• Reduced response times when users are executing the same SQL statements.

  SQL statements that take longer to optimize (usually because they include many tables and many filters in the WHERE clause) execute faster from the SQL statement cache because the database server does not have to parse or optimize the statement.

• Reduced memory usage because the database server shares query data structures among users.

  Memory reduction with the SQL statement cache is greater when a statement has many column names in the select list.

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