Lesson 9

This module has served to be a high-level introduction of the major issues in tuning the Oracle SGA.
You should now be able to:

1. Describe the main components of the Oracle SGA
2. Describe the operation of the shared pool
3. Describe the functions of the library cache
4. Query the data dictionary cache
5. List the UGA and session memory considerations
6. Describe how the data buffer cache affects performance
7. Describe the performance issues related to the redo log buffer

Oracle's data dictionary cache, also known as the row cache, is a memory area in the shared pool that holds data dictionary information to reduce physical I/O on the data dictionary tables. You can query the contents of the data dictionary cache using Oracle's dynamic performance views. Here are the steps to query the data dictionary cache in Oracle 12c:

1. Step 1: Connect to your Oracle Database instance as a user with DBA privileges.
   

   sqlplus / as sysdba
   

2. Step 2: Query the V$ROWCACHE dynamic performance view. This view provides data about the usage of the data dictionary cache.
   Here is a basic query to get data from V$ROWCACHE:
   

   SELECT PARAMETER, GETS, GETMISSES, MODIFICATIONS
   FROM V$ROWCACHE;
   

   
   
   1. The PARAMETER column provides the name of the cache.
   2. GETS represents the total number of requests for information on this cache.
   3. GETMISSES is the number of these requests that resulted in cache misses.
   4. MODIFICATIONS indicates the number of modifications to the entries of this cache.
3. Step 3: Analyze the results. For example, calculate the 'miss ratio' for each cache as follows:
   

   SELECT PARAMETER, (GETMISSES / (GETS + GETMISSES)) * 100 AS MISS_RATIO
   FROM V$ROWCACHE;
   

   
   A high miss ratio may indicate a need for increasing the size of the shared pool.
4. Step 4: To get more detailed information, you can join V$ROWCACHE with other dynamic performance views such as V$DB_OBJECT_CACHE or V$SQLAREA.
   For example, to get information on how SQL statements are interacting with the data dictionary cache:
   

   SELECT s.SQL_TEXT, o.OBJD, o.TYPE, o.OWNER, o.NAME
   FROM V$DB_OBJECT_CACHE o, V$SQLAREA s
   WHERE s.SQL_TEXT LIKE '%' || o.NAME || '%' AND o.TYPE = 'TABLE';
   

The above SQL command lists all the SQL statements that are related to a specific table in the data dictionary cache. Remember, these are just examples, and there's much more information available in the dynamic performance views. Use the Oracle Database Reference for Oracle Database 12c for detailed information about each view and its columns.
When interpreting the results, remember that occasional misses on the data dictionary cache may not be problematic. However, if miss ratios remain consistently high, it might indicate a need for tuning the shared pool.

Here are some terms used in this module that you may need to review:

1. database buffer cache: An area in memory where Oracle keeps recently used data blocks so that they do not need to be constantly reread from disk.
2. data dictionary cache: The data dictionary cache is used to hold rows from the internal Oracle metadata tables, including SQL stored in packages.
3. dictionary cache miss: Oracle registers a dictionary cache miss when a package is not in memory when it is requested.
4. process memory: The SGA memory allocated on behalf of an Oracle process.
5. redo log buffer: An area in the SGA that Oracle uses to hold redo log entries until they can be written to the log files.
6. session memory: The SGA memory allocated on behalf of an individual Oracle session.
7. SGA: An abbreviation for System Global Area.
8. shared SQL area: The shared SQL area is used to keep and process SQL statements and PL/SQL code.

Before you go on, click the Quiz link below and check your knowledge of the major points in this module.
Oracle Instance Tuning - Quiz
The next module explores specific tuning techniques for the shared pool.