Optimize queries with the SQL statement cache

Before the database server runs an SQL statement, it must first parse and optimize the statement. Optimizing statements can be time consuming, depending on the size of the SQL statement.

The database server can store the optimized SQL statement in the virtual portion of shared memory, in an area that is called the SQL statement cache. The SQL statement cache (SSC) can be accessed by all users, and it allows users to bypass the optimize step before they run the query. This capability can result in the following significant performance improvements:
  • Reduced response times when users are running the same SQL statements.

    SQL statements that take longer to optimize (usually because they include many tables and many filters in the WHERE clause) run faster from the SQL statement cache because the database server does not 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 Monitor and tune the SQL statement cache.