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:
- Describe the main components of the Oracle SGA
- Describe the operation of the shared pool
- Describe the functions of the library cache
- Query the data dictionary cache
- List the UGA and session memory considerations
- Describe how the data buffer cache affects performance
- 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:
- Step 1: Connect to your Oracle Database instance as a user with DBA privileges.
sqlplus / as sysdba
- 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
- The PARAMETER column provides the name of the cache.
- GETS represents the total number of requests for information on this cache.
- GETMISSES is the number of these requests that resulted in cache misses.
- MODIFICATIONS indicates the number of modifications to the entries of this cache.
- 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
A high miss ratio may indicate a need for increasing the size of the shared pool.
- 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.
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.