By Jstiles-Oracle on Apr 28, 2015
Written by Ira Singer, Oracle DBA
Understanding the Shared Pool for Tuning, a recent webinar, I talked about parsing the library cache of the shared pool, how to cut down on hard parses, and AWR (Automatic Workload Repository) findings to help tune the library cache.
The main purpose of the shared pool is to share your SQL statements -- your execution plans, especially for online transaction processing. There are other areas in the shared pool that can be used, but there are some important areas of the shared pool that are helpful to understand.
The major components of the shared pool are:
• The library cache, which stores shared SQL and PL/SQL code and object metadata in areas distinguished by namespaces. You're aiming to have most of your code, in an OLTP operation, in the library cache. But, if it isn't in the library cache, you then have to do a hard parse.
• The data dictionary cache, which holds row images from the data dictionary tables -- also called the row cache.
• The results cache holds query result sets and query fragments, so subsequent queries can retrieve the result directly from the cache. If you have large SQL statements that are going to give you a smaller set of results, that will go into the results cache.
If we do the hard parse, we tend to slow down the overall performance. Instead, we’ll want to tune the library cache.
SQL Statement Processing Phases
SQL statements are in four phases: a parse, a bind, an execute, and a fetch.
The aspects that deal particularly with the shared pool are parse and bind:
- Parse phase is one of the stages in the processing of a SQL statement. When an application issues a SQL statement, the application makes a parse call to the Oracle Database. During the parse call, the Oracle Database checks the statement for syntactic and semantic validity, determines whether the process issuing the statement has privileges to run it, searches for an sharable match of the statement in the shared pool, and allocates a private SQL area for the statement.
- Bind phase checks the statements for the bind variable and assigns/reassigns a value to the bind variable. When bind variables are used properly, more soft parses are possible, thereby reducing hard parses and keeping parsed statements in the library cache for a longer period.
Now it’s good to look at the Automatic Workload Repository, or the Statspack indicators that you might be having problems with in your shared pool.
Please watch my webinar, because in it I show you these AWR reports in detail and how to use them for hints of problems. Specifically, we look at a load profile, instance efficiencies, top wait events, and the time model. I also provide several examples for how shared pools work. I hope you can join me for the full session.
Check out other webinars available through Oracle Learning Streams.